SQL Server notes continue to update

1.sp_spaceused:

sp_spaceused table_name

---------------------------------------------------------------------------------------------

Name rows reserved data index_size unused

table_name 16470592 36315696KB 35796160KB 519432KB 104KB

Name--Nature is the table name or object name

Rows-the number of rows of data in the table

Reserved-This table occupies a total size equal to data 35796160KB + index_size 519432KB + unused 104KB

Data--The total size of the page

Index_size--The total size of the index page

Unused--a little space left. Because the disk allocation space and other reasons to give a little more. It has nothing to do with fill factor.

2.sys.dm_db_partition_stats

Select B.name,a.row_count from Sys.dm_db_partition_stats A,
Sys.objects b
where a.object_id=b.object_id
and A.index_id<=1
and b.type= ' U '

Sys.dm_db_partition_stats it returns page and row count information for each partition in the current database, the three key columns in SQL above mean the following:
OBJECT_ID: The ID of the table or indexed view, so it can be used to match the object_id of the sys.objects table.
Row_count: The number of data in the table or indexed view is the result of the query.
INDEX_ID: The index ID of the table or indexed view. If the table has no indexes, there will be a row of index_id=0 records in Sys.dm_db_partition_stats, if there is a clustered index (and only one clustered index in a table), then in Sys.dm_db_partition_ There is a row of index_id=1 records in stats, and the corresponding Index_id=0 records are not available. If the table has multiple rows of index_id >1 Records in Sys.dm_db_partition_stats, then there are several nonclustered indexes on this table, and we will judge Index_id<=1 here, assuming that each table has a primary key and that the primary key is a clustered index. With the sys.objects table of type= ' U ', you can find out the number of data per user table.

3. Problems and workarounds for SQL Server performance degradation after deleting large amounts of data using delete

The cause of the problem arises:
1, when there is a clustered index on the table, the delete operation frees the empty page. However, when rows are deleted from the heap, the database engine can operate with row locks or page locks. As a result, the empty page caused by the delete operation continues to be assigned to the heap. When empty pages are not freed, other objects in the database will not be able to reuse the associated space.
2, although there is not a lot of data in the table, they have a large number of almost empty data pages. Scanning the table thus becomes very time-taking.

Solution:
To delete the rows in the heap and free the page, we can use one of the following methods.
1, specify the TABLOCK hint in the DELETE statement. Using the TABLOCK command causes the delete operation to get a shared lock on the table, not a row or page lock. This will allow the page to be freed.
2, if you want to remove all rows from the table, use TRUNCATE table instead of delete
3, create a clustered index on the heap before you delete the row. After you delete a row, you can delete the clustered index. This method is time-consuming and uses more temporary resources than the previous method.

4.

Possible values for logical expressions in SQL include True, false, and unknown, which are called three-valued logic.

We know that in other languages, there are only two types of logical expressions, either True or false. In SQL, there is a third logical expression, unknown, which is unique in SQL. From the literal meaning we can solve the meaning of the deserve value is: What do not know, is nothing. In general, when we compare any value (including null itself) to NULL, we will return unknown. In query expressions, such as where and have, unknown is considered false. So we figured out why the select * from table where field =null couldn't find the correct result.

But not all unknown will be treated as false, and in a check constraint, unknown is treated as true. This is why we can also insert a null value into the field if we set the value of a field to be greater than or equal to 0, because the logical result of null>=0 in the check constraint unknown is treated as true
It is important to note that in the grouping clause and the sort clause, SQL sees null as equal
That
1,group by Will divide all null values into a group.
2,order by will arrange all the null values together.