unique clustered index
But whenever possible have a unique clustered index. For example, on the state codes table, a unique clustered index on state_code would work just fine.i am reading as above.
what is meaning of unique clustered index and how it is different from regular index. please let me know advantages of using it.
please advise
ASKER
i read less the unique clustered index number slow at data access where as more the number slow down data modification which was not clear either.
please advise
please advise
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>"i read less the unique clustered index number slow at data access where as more the number slow down data modification which was not clear either."
A unique clustered index is fast for data access (if a query is able to use that index).
A table can only have one clustered index, but it can have many additional non-clustered indexes.
When a table has many indexes, inserts/updates into that table may get slower because the database needs to refresh many indexes.
Plus, as wilcoxon has already mentioned, a clustered index can also slow down inserts/updates too:
"... since the table is physically stored in the [clustered] index order, inserts or updates may require reordering the physical table."
Think of it this way:
To make a car faster we remove weight which can make it less safe.
There is a "trade-off" between speed and safety.
Indexes do help to make data access faster, but the "trade-off" can be slower inserts/updates.
A unique clustered index is fast for data access (if a query is able to use that index).
A table can only have one clustered index, but it can have many additional non-clustered indexes.
When a table has many indexes, inserts/updates into that table may get slower because the database needs to refresh many indexes.
Plus, as wilcoxon has already mentioned, a clustered index can also slow down inserts/updates too:
"... since the table is physically stored in the [clustered] index order, inserts or updates may require reordering the physical table."
Think of it this way:
To make a car faster we remove weight which can make it less safe.
There is a "trade-off" between speed and safety.
Indexes do help to make data access faster, but the "trade-off" can be slower inserts/updates.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Split the points between all people that replied with relevant information.
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described