August 16, 2013 at 2:10 am
Hi Team,
am having a table with id, emp_no in a table, and the two columns constraint type is Primary Key (non clustered) , and having 20000+ records in that table.
and the combination of the two columns is a unique value,
instead of non clustered index, if i changed it to clustered index.
if there any issues.
Please suggest.
August 16, 2013 at 2:19 am
Would you get any benefit if the data is sorted according to ID and empNo?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 16, 2013 at 2:44 am
Hi,
Definetely i'll get benefited if the data is sorted according to ID and empNo,
but my existing table contains primary key non cluster index, if i changed the same to primary key with clustered,
what are advantages / disadvantages.
Please...
August 16, 2013 at 3:03 am
Minnu (8/16/2013)
Hi,Definetely i'll get benefited if the data is sorted according to ID and empNo,
but my existing table contains primary key non cluster index, if i changed the same to primary key with clustered,
what are advantages / disadvantages.
Please...
How do you know you'll benefit? Any specific queries on ID and empNo?
The advantage of clustered vs non-clustered is that the data is logically sorted according to the keys of the index. Another advantage might be that you can avoid bookmark lookups if your non-clustered index is non-covering.
The disadvantage is that you have to rebuild your entire table, which might take some time.
edit: these are the things that came to mind first. There are some other people on this forum who know a lot more about indexing than I do.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 16, 2013 at 4:29 am
Hi,
Am having a non-cluster index on two columns, but the performance of that tables from my application is too slow, if i change the non-cluster index to cluster index, is there any difference i can see.
Please need your confirmation.
August 16, 2013 at 4:37 am
Change ID to Cluster Index so that index data will be sorted in order and query output will be faster.
August 16, 2013 at 7:05 am
Minnu (8/16/2013)
Hi,Am having a non-cluster index on two columns, but the performance of that tables from my application is too slow, if i change the non-cluster index to cluster index, is there any difference i can see.
Please need your confirmation.
No one can tell this without seeing actual query that you are running to this table from app.
Please post query and actual execution plan of query ran in your environment.
August 16, 2013 at 11:33 am
Balaji M R (8/16/2013)
Change ID to Cluster Index so that index data will be sorted in order and query output will be faster.
How do you know? Maybe the query doesn't need the columns from the index, or in a different sort order.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 16, 2013 at 2:56 pm
You shouldn't have any direct issues from doing that.
First, drop the existing nonclus index, then create the clus index.
Build the index ONLINE if at all possible, to minimize disruption to normal activity.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply