Order of columns
-
First, the situation:
I have been contacted by some of our SQL developers who have taken over maintenance and development of an existing database. They came to me because they noticed some of the table structures were a bit unorthodox and performance was slow. One such table, for example, had a 6-column primary key including a datetime, 4 different char fields and an integer.
This table also had four multi-column indexes including one non-clustered index that exactly matched the clustered primary key index.
I set up a test environment for them, make a copy of the table without the primary key and with the addition of an ID column as the first column and no indexes.
After some tuning and creating some indexes they found that performance of the new table was much better than the original one.
Now, the question:
I'd rather not do the whole "create a temp table, copy the data from the original table, drop the original table, rename the temp table" routine when I can just as easily do all of the changes with alter, drop and create commands. (especially as some of these tables have tens of millions of rows)
The only difference is that the ID column would be the last column on the row and not the first.
Will this have any impact on queries or inserts? I don't think it will, but I wanted to see if anyone has a different viewpoint.
Thanks.
-- J.T.
"I may not always know what I'm talking about, and you may not either."
-
IMO the problem is that your primary key is CLUSTERING !
This also has an impact to _all_ NCI !
Drop and recreate your Primary key but make it non-clustering.
Create a clustering index on a better suitable column !
You might add an id-column, to avoid to drag these 6 columns to dependant tables, in that case you would still need to add the unique-constraint/index for the replacement of your current primary key.
It's position may not have that big a role, but I'd prefer putting it in front, just because it's a visual aid.
If you want to build your clustering index on this column, everything is rewritten automaticaly at createtime / dbreindex of the CIX. One of the guidelines for CIX is ... keep it as narrow as possible .
You're suffering the reason why...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Just to endorse Alzdba's comments....
1) No, the placement of the ID column at the end of the row will have no significant impact on your performance. Admittedly, it can seem easier to read the data if it's the leading column, but how often do you need to know the rowID? I'd happily leave it at the end.
2) Absolutely your clustered index is too wide! The clustered index columns are automatically added to EVERY non-clustered index (except columns that already exist in such an index), so the efficiency of your NC indexes is going to be way too low, and you'll be lucky if they're used at all, not to mention the duplication of data increasing the size of your database.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply