SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop

SQL Server 2005 Enterprise Edition supports online index operations. Index operations are creating, rebuilding and dropping indexes. The question which I receive quite often – what is online operation? Is online operation is related to web, internet or local network? Online operation means when online operations are happening the database…
Read More

SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

Index Fragmentation: When a page of data fills to 100 percent and more data must be added to it, a page split occurs. To make room for the new data, SQL Server must move half of the data from the full page to a new page. The new page that…
Read More

SQL SERVER – Disable Index – Enable Index – ALTER Index

There are few requirements in real world when Index on table needs to be disabled and re-enabled afterwards. e.g. DTS, BCP, BULK INSERT etc. Index can be dropped and recreated. I prefer to disable the Index if I am going to re-enable it again. USE AdventureWorks GO ----Diable Index ALTER INDEX [IX_StoreContact_ContactTypeID]…
Read More

SQL SERVER – DBCC commands List – documented and undocumented

Database Consistency Checker (DBCC) commands can gives valuable insight into what’s going on inside SQL Server system. DBCC commands have powerful documented functions and many undocumented capabilities. Current DBCC commands are most useful for performance and troubleshooting exercises. To learn about all the DBCC commands run following script in query…
Read More
Exit mobile version