다음을 통해 공유

Indexing a SQL Table

  • 아티클

I found one of my old documents where I kind of summarized Indexes on a SQL Table, might help.

 Indexing SQL Table

Primary Key:

· Creates clustered index on the column

· Doesn't allow NULLS

 

 

Unique Key:

· Creates non-clustered index on the column

· Allows only one NULL

 

 

Index on a table in general:

· Good for:

Ø Columns that contains large number of distinct values. If there are very few distinct values such as 0 and 1, most queries will not use index because in this case table scan is more efficient

 

Clustered Index:

· Good for:

Ø Columns that returns a range of values using operators such as BETWEEN, >, >=, <, and <=

Ø Columns that are accessed sequentially

Ø Columns that are frequently accessed by queries involving join or GROUP BY clauses

Ø Environment where very fast single row lookup is required. Create a clustered index on Primary Key column on such environment

           

· Not Good for:

Ø Columns that undergo frequent changes - High volume transactional environment where data tends to be volatile

 

· Benefit:

Ø If you apply cluster index on the columns which is used by ORDER BY and GROUP BY clause, that will eliminate SQL Server to sort data because the rows are already sorted. That will enhance the performance.

 

Non-Clustered Index:

· Good For:

Ø Columns frequently involved in search conditions of query (WHERE clause) that returns exact matches.

Ø Applications where joins and grouping are frequently involved. Create multiple non-clustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

Comments

  • Anonymous
    January 01, 2003
    I am working in one scenario wherein I need to search on one field CustomerID. UI needs this to be displayed in varchar and I am storing this columnvalue in 2 fields one in int (for refering in FK) and varchar as CustomerID-('c0010') and CustomerSID-(1) as PK. Now if I search on CustomerID or CustomerSID which one will be faster. In record search if I have index on both then, using which column(int/varchar) I will get faster results. Let me know your views asap.