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.
| 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. |
 |
|
|
misken
Starting Member
8 Posts |
Posted - 2014-08-06 : 03:33:31
|
| gbritton could you elaborate?Sys.developer/accidental DBA |
|
|
|
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.aspxIn 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." |
|
|
|
fahad yaseen
Starting Member
8 Posts |
Posted - 2014-08-06 : 08:37:30
|
| thank you so much now i have got my answer! |
|
|
|
|
|
|
|
|
papi chulo