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

SQL SERVER – Find Current Identity of Table

June 20, 2008

Many times we need to know what is the current identity of the column. I have found one of my developer using aggregated function MAX() to find the current identity. USE AdventureWorks GO SELECT MAX(AddressID) FROM Person.Address GO However, I prefer following DBCC command to figure out current identity. USE AdventureWorks…

SQL SERVER – Order Of Column In Index

June 2, 2008

Pinal Dave

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

SQL Index, SQL Joins, SQL Scripts

I just found one of my Jr. DBA to create many indexes with lots of column in it. After talking with him I found out that he really does not understand how really Index works. He was under impression that if he has more columns in one index, that index…

SQL SERVER – Optimization Rules of Thumb – Best Practices

April 25, 2008

Pinal Dave

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

Best Practices, SQL Index

There are few rules for optimizing slow running query. Let us look at them one by one see how it can help. Rule # 1 : Always look at query plan first. I always start looking at query plan. There is always something which catches eyes. I pay special attention…

SQL SERVER – Introduction to Heap Structure – What is Heap?

March 30, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index

Sometime simple questions are very interesting. A day ago, jr. developer asked me question : What is Heap? In SQL Server 2005 data is stored within tables. Data within a table is grouped together into allocation unites based on their column data types, what it means is one kind of…

SQL SERVER – 2005 – List All Column With Identity Key In Specific Database

March 29, 2008

Pinal Dave

SQL Tips and Tricks

SQL Identity, SQL Index, SQL Scripts, SQL Server, SQL System Table

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

March 27, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts, SQL System Table

Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I…

SQL SERVER – 2005 – A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold

March 4, 2008

Pinal Dave

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

SQL Error Messages, SQL Index, SQL Scripts, SQL Server DBCC

Just a day ago, I received email from regular reader Rajiv Kayasthy about a script which demonstrates the A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold. He found this script on TechNet BOL and was attempting to run on SQL Server…