Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 papi chulo

Author  Topic 

fahad yaseen
Starting Member

8 Posts

Posted - 2014-08-05 : 03:36:43
??

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-05 : 08:50:54
If you are given the option to choose one indexing technique, the person giving the option doesn't understand relational databases.
Go to Top of Page

misken
Starting Member

8 Posts

Posted - 2014-08-06 : 03:33:31
gbritton could you elaborate?

Sys.developer/accidental DBA
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-06 : 07:56:13
For a full discussion, start here: http://msdn.microsoft.com/en-us/library/ms190457.aspx

In a nutshell, in SQL Server, a table that has no CI is stored as a heap. That means that rows are stored wherever they happen to fit. There is no logical or physical ordering imposed on the data. While there are some legitimate -- though limited -- uses for heaps in SQL Server, normally you want some kind of ordering. That's where clustered indexes come in. When you put a CI on a table, the data rows are sorted and stored in the table or view based on their key values. I think that you can see that there can be only one CI for a table.

The choice of a suitable key for a CI is the subject of many articles and discussions which I won't go into here. A popular choice is a column that is defined as an IDENTITY() integer value.

A non-clustered index or NCI is defined based on the needs of the queries executed against the table. If all the queries, or at least the most frequent and data-heavy ones, run optimally with only the the CI, then an NCI is not needed at all. That would be unusual but not inconceivable. For example, if your Employee table has a CI on the EmployeeID column (defined as an integer with the IDENTITY() property), and most queries look up rows by EmployeeID, then the CI is sufficient. However, often such a table is queried by EmployeeName or EmployeeAge or something else. To make those queries efficient, you'd want an index on those columns. With the CI in place, you'd define an NCI on the column the query uses for look ups. In SQL Server, an NCI entry refers to the actual row by its CI value, (unless there is no CI, in which case a pseudo-physical address is used instead).

So, to sum up, you almost always want a CI on a table and you will frequently want one or more NCIs as well. You are never forced to choose one or the other. That's why the original question makes no sense. (Perhaps it is a trick question?) However, if in some alternate universe I was forced to choose, I'd probably go with a CI on the column that is used most frequently by the heaviest queries that hit that table.

The safest answer though is, "It depends."
Go to Top of Page

fahad yaseen
Starting Member

8 Posts

Posted - 2014-08-06 : 08:37:30
thank you so much now i have got my answer!
Go to Top of Page
   

- Advertisement -