What is Heap? When table data has no logical order and no clustered index, SQL Server stores it as a Heap Structure. I explain pages and extents too.
SQL SERVER – 2005 – List All Column With Identity Key In Specific Database
Question I received in Email : How to list all the columns in the database which are used as identity key in my database?
SQL SERVER – 2005 – Find Index Fragmentation Details – Slow Index Performance
A slow index turned out to be heavily fragmented. My quick script uses sys.dm_db_index_physical_stats to show index fragmentation details for every index.
SQL SERVER – 2005 – A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold
The BOL script to defragment all indexes in a database above a declared threshold failed with Msg 2501. I fixed the missing schema prefix so it runs on SQL Server 2005.
SQL SERVER – 2005 – Find Unused Indexes of Current Database
A simple but accurate SQL Server 2005 script to find unused indexes in the current database, since unused indexes slow down INSERT and UPDATE statements.
SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table
How do you find Fragmentation Information for a table in SQL Server 2005? DBCC SHOWCONTIG has limits, so I use sys.dm_db_index_physical_stats instead.
SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script
Index Rebuild drops and recreates the index, while Index Reorganize reorders the leaf nodes. Rebuild above 40% fragmentation, reorganize between 10% and 40%.