clustered index is 1 in table?then///
-
well i have read that ,clustered index is 1 in table ,they match the physical & logical records, so what nonclustered index is more than 1 in a table, How they work?? then they increase the congestion 2much? & y prefer 1 to another in what sense? there is confusion.. plz reply
-
well i have read that ,clustered index is 1 in table ,they match the physical & logical records, so what nonclustered index is more than 1 in a table, How they work?? then they increase the congestion 2much? & y prefer 1 to another in what sense? there is confusion.. plz reply
waqarnaeem2@hotmail.com wrote:
there is confusion
Indeed there is. I'm having some difficulty parsing your question.
waqarnaeem2@hotmail.com wrote:
clustered index is 1 in table
There can only be one clustered index in a table.
waqarnaeem2@hotmail.com wrote:
they match the physical & logical records
The physical rows kind of follow the clustered index. It isn't exact, but the general idea is that the rows are physically sequenced by the clustered index however, SQL Server performs many optimisations that means that it doesn't follow that except in the simplest of circumstances.
waqarnaeem2@hotmail.com wrote:
so what nonclustered index is more than 1 in a table
You can have many non-clustered indexes on a table.
waqarnaeem2@hotmail.com wrote:
How they work??
The same way as an index in a book. The non-clustered area consists of a number of pages which contain look up information that can be used to find the physical rows faster than you would be able to if you had to scan the whole table to find the same information.
waqarnaeem2@hotmail.com wrote:
then they increase the congestion 2much
What congestion? They will requires a little more IO for the lookup, but the idea is that the extra up-front work pays off as you can find the actual data much faster and using less IO overall. In some queries there may even be enough information in the index to never need to look at the physical rows at all. This makes everything much faster and less congested. However, when you insert, update or delete from a table with non-clustered indexes you have to remember that SQL Server will also have to update the indexes as well as the actual physical row.
waqarnaeem2@hotmail.com wrote:
y prefer 1 to another in what sense?
If you want more than one index on a table then you have no choice as you must use a non-clustered index at some point. Typically the clustered index is on your primary key
*Developer Day Scotland - Free community conference *Colin Angus