April 17, 2010 at 10:56 am
Hugo Kornelis (4/16/2010)
Nice question, Ron. I do not agree with Jason about the importance of the information though - it is interesting to know, but absolutely irrelevant for our jobs. 😉
I think I agree; but it's fun anyway. A very nice question.
I do have three minor gripes about the question (sorry!)
First, the wording could have been improved. It took me a very long time before I realized that the "Field 1" and "Field 2" columns were not columns returned by some query, but fragments of the name. It would have been better if the text explicitly stated that the question is about the two hex fragments embedded in each auto_stats name.
I have to agree with that, but I regard it as not just minor but trivial.
Second, Field 1 is not actually the object id of the column, but rather the column id. Columns are identified by a combination of object id (of the table they are part of) and column id (number within the table; numbered from 1 upwards when table is created, though later ALTER TABLE statements can affect the order and cause gaps).
For me, this is a major gripe. A column doesn't have an object id, there is no such thing as the object id of a column, so one has to guess whether the writer of the question got it wrong and called the column is an object id or whether this was intentional and set as a trap for the unwary.
Third, the answer options were in some sort of random order, making it a challenge to not accidentally click wrong. Presenting all three options for Field 1 first, and all three for Field 2 next would have made it easier to get an overview of the options.
I agree with that, but as you say it is a minor point.
However, neither of these gripes is serious enough that they could cause people who know the answer to choose an incorrect answer, so please don't take this as an encouragement to award back any points.
I disagree about the seriousness of the second gripe (incidentally can you talk about "neither" of three things?) because to me it seems to reduce the chance of getting it correct to even or less than even (since to get it "right" one has to tick a box that is obviously not correct). However, I agree about awarding points back - totally unnecessary and pointless. And if one takes the question as requiring a box to be ticked for field 1 then one has to choose the least wrong option for that field, and that would lead to the "correct" answer.
A question: is the stats_column_id column of sys_stats the same column_id as the column_id in sys.columns? If so, why does it have a different name?
Tom
April 17, 2010 at 12:31 pm
Tom.Thomson (4/17/2010)
A question: is the stats_column_id column of sys_stats the same column_id as the column_id in sys.columns? If so, why does it have a different name?
Hi Tom,
I assume you meant stats_column_id in sys.stats_columns, as there is no such column in sys.stats 🙂
And I think the answer is no. According to BOL, stats_column_id is "1-based ordinal within set of stats columns." And there is also a column column_id in the same catalog view, so I expect them to be different.
I'm a bit short for time, otherwise I'd try to pull up a repro to prove this. Now I'lll have to leave that to you 😉
April 17, 2010 at 3:25 pm
Hugo Kornelis (4/17/2010)
Tom.Thomson (4/17/2010)
A question: is the stats_column_id column of sys_stats the same column_id as the column_id in sys.columns? If so, why does it have a different name?Hi Tom,
I assume you meant stats_column_id in sys.stats_columns, as there is no such column in sys.stats 🙂
And I think the answer is no. According to BOL, stats_column_id is "1-based ordinal within set of stats columns." And there is also a column column_id in the same catalog view, so I expect them to be different.
I'm a bit short for time, otherwise I'd try to pull up a repro to prove this. Now I'lll have to leave that to you 😉
Hi Hugo,
Yes, you are right - I didn't have my brain in gear when I said it was in sys.stats :doze:. And :ermm: as there is a column id in the same view it would be really strange if they were the same thing.
So I did some experimentation to see what it actually is and it appears to be very simple: if there is an index on n columns, there are n statistics for that index (one for each of the columns) and these are numbered 1 to n in stats_column_id. User created statistics have stats_column_id fixed by the same rule as for indexes. I guess if I played around some more I might find some exceptions to this, but it doesn't strike me as important enough to be worth the effort (and anyway failing to contradict it wouldn't confirm it :-)).
Tom
April 18, 2010 at 4:56 am
Hugo Kornelis (4/16/2010)
Second, Field 1 is not actually the object id of the column, but rather the column id. Columns are identified by a combination of object id (of the table they are part of) and column id (number within the table; numbered from 1 upwards when table is created, though later ALTER TABLE statements can affect the order and cause gaps).
I disagree that this is a minor point.
I fully knew the answer to this one, and I still managed to get it wrong because of the poor answer wording. Columns don't have object ids, so I went for the sequence number option - thinking that was closest to the right answer (column id). Having to guess at the answer the questioner was thinking of, but worded poorly, is becoming just as important as knowing the real correct answer these days 🙁
If you're going to pose a very technical question, at least get the answers technically correct!
Grrr.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 18, 2010 at 9:58 am
Paul White NZ (4/18/2010)
Hugo Kornelis (4/16/2010)
Second, Field 1 is not actually the object id of the column, but rather the column id. Columns are identified by a combination of object id (of the table they are part of) and column id (number within the table; numbered from 1 upwards when table is created, though later ALTER TABLE statements can affect the order and cause gaps).I disagree that this is a minor point.
I fully knew the answer to this one, and I still managed to get it wrong because of the poor answer wording. Columns don't have object ids, so I went for the sequence number option - thinking that was closest to the right answer (column id). Having to guess at the answer the questioner was thinking of, but worded poorly, is becoming just as important as knowing the real correct answer these days 🙁
If you're going to pose a very technical question, at least get the answers technically correct!
Grrr.
Paul
Hey, Paul, for once we agree that some bad wording is a bad thing, not a minor point. Is this a record?
Tom
April 18, 2010 at 6:05 pm
Tom.Thomson (4/18/2010)
Hey, Paul, for once we agree that some bad wording is a bad thing, not a minor point. Is this a record?
It might be 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2010 at 3:57 am
Hello Ron
I liked QOD... Really appreciate your efforts to bring hidden things to public. I feel question could have been formed better but that is absolutely managable. Though I also understand the description of table at second attempt :-P.
Thank you for interesting question. Which really peep into the sql server michanism.
Keep on writing....
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 21, 2010 at 3:40 pm
I agree about wording. There is no way you can select one column from a table (SELECT name) and get three columns/fields returned. I guess we are supposed to be mind readers too.
April 22, 2010 at 2:50 pm
I was confused when my answer was wrong, because the Field 1 is the 'column id' not 'object id'.
Hope to review the correct form on questions.
Thanks
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply