Setting indexed columns on a custom SQL table

Question

Hi,

I've read about primary, unique, clustered indexes etc. But I need to understand it via an example.

The image below is the auto-generated aspnet_Users table captured from SQL Server Web Admin Panel.

Taking this as a model; I will create a custom table called Companies and let's say the fields are: ID, Name, ShortName, Address, City, Country.. No values can be duplicate for ID, Name and ShortName fields.

What is your approach on creating indexes for this table? Which should be clustered or non-clustered? Are indexes below logical to you?

Index          Columns         Primary    Unique    Clustered    ign.Dup.Keys   Unique Key
------------------------------------------------------------------------------------------
PK_ID          ID              True       True      False        False          False
Comp_Index     Name,ShortName  False      True      True         False          False

regards..

Answer 1

Indexes are not about table structure, but about access patterns.

You need to see how you query the data in the table and create your indexes accordingly.

The rule of thumb is to consider defining indexes on fields that are commonly used in the WHERE clause.

See this blog post on the subject.

Update

You can only define a single clustered index on a table. This is normally done on the identity field of the table, as you have in your example.

Other indexes will be non-clustered.

In regards to the other (non-clustered) index - if you intend on only having queries that contain both fields in the WHERE clause and the ORDER BY will have a primary sort on Name (as opposed to a primary sort on ShortName). The reason for that is that this is how the index will be stored - first on Name, then on ShortName.

If however, you will use ShortName as primary sort or without Name in the WHERE clause, you are better off with two indexes, one for each.

Answer 2

Oded is right - indices (Clustered and non) are all about performance and needs intimate knowledge about the types of queries.

e.g. If both ShortName and Name are both queried independently, you might want to have separate Non Clustered indexes for ShortName and Name. If you need to enforce uniqueness, use UNIQUE INDEX (or add UNIQUE CONSTRAINTs to ShortName and Name). ID is already unique as it is the PK.

You can also change the Clustered Index (from its default of ID) if you know more about how data from your companies table will be fetched (e.g. Cluster on City if it is common practice to fetch all Companies in a City at once etc)

Answer 3

Go and get a quick overall understanding of SQL Server Indexes by reading Brad's Sure Guide to Indexes

Typically, having not performed any query analysis your starting point will be:

• The Primary Key column can make a good candidate for the Clustered Index (often dependant on data type used and key width).
• You should create Non-Clustered indexes on Foreign Key Columns.
• You should create Non-Clustered indexes on SARG columns from your queries.

Then take a look at these generic index tips.