A SQL Server table can have only one identity column, and a second one fails with Msg 2744. This second identity column workaround compares a computed column with a sequence, shows what each does with gaps, rollbacks and large values, and explains when to pick which.
Partition Function Fanout: Count Partitions in SQL Server
Count partitions in SQL Server by reading the fanout of a partition function. This post reads fanout and modify_date from sys.partition_functions, watches SPLIT RANGE and MERGE RANGE change them, and finds functions that no table uses.
SQL SERVER – Rows Sampled – sys.dm_db_stats_properties
Here is the question asked in my yesterday’s Comprehensive Database Performance Health Check about rows sampled from sys.dm_db_stats_properties.
SQL SERVER – Statistics Modification Counter – sys.dm_db_stats_properties
The Statistics Modification Counter tracks changes relevant to each statistics object. I inspect it alongside the affected query and estimates.
Can a Database Have Multiple Files with Extension MDF? – Interview Question of the Week #279
Question: Can a Database Have Multiple Files with Extension MDF?
Answer: Yes. It is possible to have multiple files with extension MDF.
Memory Used by Each Table in the SQL Server Buffer Pool
Memory used by each table is the number of its pages in the buffer pool times 8 KB. This post adds them up with sys.dm_os_buffer_descriptors, shows why a plain division by 128 hides small tables, and compares the cached pages with the pages each table owns on disk.
Buffer Pool Pages for One Table in SQL Server
Buffer pool pages for one table show how much of one table sits in memory right now. This post lists them by index, page type and page level with sys.dm_os_buffer_descriptors, then shows which pages a seek, a scan and a large text read pull in. It uses the join that the documentation gives for each allocation unit type.






