order of columns

Question

order of columns

coolchaitu

SSChampion

Points: 10315
More actions
November 25, 2018 at 10:16 pm

#382335

Good morning Experts,
Does the order of columns in a table matter? Also, does the order of columns in an index matter?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

coolchaitu - Sunday, November 25, 2018 10:16 PM
Good morning Experts,
Does the order of columns in a table matter? Also, does the order of columns in an index matter?

A leading column of index matters a most.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

coolchaitu - Sunday, November 25, 2018 10:16 PM
Good morning Experts,
Does the order of columns in a table matter? Also, does the order of columns in an index matter?

To the first question... basically, no. However... Let's say that you're trying to squeeze the last possible microsecond of performance. You've tuned every query. Your statistics are flawless. Your database design and indexing make MVPs weep with joy. You've spent every possible penny on hardware. Yet, you need a little more performance. If you've arrived at this extreme edge case (and no, you're not there, I personally guarantee it), then, you could look to store the data a little more efficiently. Basically, putting the columns in order such that all fixed length columns are first, followed by variable length columns, can arrive at a very insignificant, utter waste of time and resources in most circumstances, but actually measurable (in VERY tiny increments) performance enhancement. You'll be storing data on pages in a slightly more efficient way and you'll see a very tiny benefit from that. It's just not worth the time, at all.

To the second question... YES! Good gosh yes. Heck yes. <Insert very inappropriate language here> YES!

For starters, one of the driving factors for index use by the optimizer and how it determines row counts is the histogram in the statistics on that index. The histogram is only, ever, made from the leading column, the first column, frequently referred to as the leading edge of the index. So, you want that first column to create a histogram that both accurately reflects the data AND gives good and useful row counts for the optimizer. So, while a column of the bit data type can accurately reflect the data, it doesn't give you good row counts. Instead, you want to pick something with a much higher degree of selectivity. It doesn't have to be a unique column (although filtering on unique columns works well), but it should be something that provides a good data distribution within the histogram.

Also, it's not just the first column in the column order. Each subsequent column in the index key helps to define the density graph in the statistics. Again, leading with more selective columns as the second, third, etc., column in the index results in a density graph that is more highly selective, again, making it more attractive and useful to the optimizer (assuming, of course, you're actually filtering using the columns that you're putting into the index).

So, the TLDR for the two questions is: No. Yes, especially the first column, but all the rest too.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 3

coolchaitu - Sunday, November 25, 2018 10:16 PM
does the order of columns in an index matter?

Think of it this way, you are a commuter and you need to take few (number of columns) train journeys to go to work every day. Can you take the train from C to D before A to B?
😎
A query with will never touch an index unless the column order matches, index columns are like most businesses, first come first served 😉

Jason A. Long SSC-Insane Points: 23731 More actions · Answer 4

coolchaitu - Sunday, November 25, 2018 10:16 PM
Good morning Experts,
Does the order of columns in a table matter? Also, does the order of columns in an index matter?

Adding my two cents to the good answers that have already been posted...

Question 1: To SQL Server, no it doesn't matter. To human beings that have to query the table... Maybe... We all have our own levels of OCD. For example, my skin crawls when address columns (address1, address2, city, state, zip) are in a funky order and/or not grouped together. Also, I personally find it helpful when the PK columns are in the 1st ordinal position(s).

Question 2: Indexes have "key columns" and "included columns". As already stated, the order of key columns is VERY important. The order of the included columns, on the other hand, doesn't matter at all.