SQL Index Archives - Page 42 of 47 - SQL Authority with Pinal Dave

SQL SERVER – 2005 – FIX: Error message when you run a query against a table that does not have a clustered index in SQL Server 2005: “A severe error occurred on the current command”

February 19, 2008

In SQL Server 2005 while testing Indexes I had created a table with one non clustered index only. I did not create any clustered index on table. After that I ran SELECT statement, it gave me following error. I was very surprised when I looked at error. It says Msg…

SQL SERVER – Index Reorganize or Index Rebuild

February 15, 2008

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index

Recently, I have received one question quite often about when to Index Reorganize and when to Index Rebuild. I have already written about this topic earlier but it seems that many are unable to search it. SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script…

SQL SERVER – 2005 – Find Unused Indexes of Current Database

February 11, 2008

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

Simple but accurate following script will give you list of all the indexes in the database which are unused. If indexes are not used they should be dropped as Indexes reduces the performance for INSERT/UPDATE statement. Indexes are only useful when used with SELECT statement. Script to find unused Indexes.…

SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table

January 10, 2008

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL DMV, SQL Index, SQL Scripts, SQL Server DBCC, SQL System Table

One of my friend involved with large business of medical transcript invited me for SQL Server improvement talk last weekend. I had great time talking with group of DBA and developers. One of the topic which was discussed was how to find out Fragmentation Information for any table in one…

SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

December 22, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

Index Rebuild : This process drops the existing Index and Recreates the index. USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REBUILD GO Index Reorganize : This process physically reorganizes the leaf nodes of the index. USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REORGANIZE GO Recommendation: Index should be…

SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact

December 21, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

While playing with Indexes I have found following interesting fact. I did some necessary tests to verify that it is true. When a clustered index is disabled, all the nonclustered indexes on the same tables are auto disabled as well. User do not need to disable non-clustered index separately. However,…

SQL SERVER – Get Information of Index of Tables and Indexed Columns

December 18, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

Knowledge of T-SQL inbuilt functions and store procedure can save great amount of time for developers. Following is very simple store procedure which can display name of Indexes and the columns on which indexes are created. Very handy stored Procedure. USE AdventureWorks; GO EXEC sp_helpindex 'Person.Address' GO Above SP will…