SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Richard Rozema / Using sys.index_columns / Latest Posts

RE: Using sys.index_columns

Iulian -207023 — Thu, 22 Dec 2011 04:12:06 GMT

Thank you for the questionIulian

RE: Using sys.index_columns

L' Eomot Inversé — Wed, 21 Dec 2011 04:01:16 GMT

Good question. The formatting wasn't an issue for me, I just pasted the answers into notepad and started to put line breaks and spaces where I would put them if writing SQL - and realised before I finished that I would be wasting time doing any more formatting as the four answer options were identical apart form a couple of column names, which made it all pretty clear (apart from remembering which of the column ids was the id in the parent table).I think the explanation is slightly wrong, though, as it seems to suggest that key_ordinal is the same as index_column_id in all entries in sys.index_columns for anything except columns of clustered indexes; but actually this can only be true for key columns, since for non-key columns key_ordinal is always 0.

RE: Using sys.index_columns

KWymore — Tue, 20 Dec 2011 13:12:41 GMT

Nice question. Keep 'em coming!

RE: Using sys.index_columns

ChrmnMAO — Tue, 20 Dec 2011 10:41:04 GMT

Nice question on something I don't believe I've ever thought much about. I learned something!Thanks, can't wait for your next!

RE: Using sys.index_columns

Kenneth.Fisher — Tue, 20 Dec 2011 10:32:31 GMT

Good question. The formatting didn't bother me a whole lot since I did what I usually do with this type of question. I ran along comparing them line by line to see what the differences were. Once I knew what the differences were I knew what columns you were testing on and could easily go from there. I never really tried to interpret the queries as a whole.I do love system table questions though. Always a lot of fun.

RE: Using sys.index_columns

OzYbOi d(-_-)b — Tue, 20 Dec 2011 09:19:10 GMT

tks - good question

RE: Using sys.index_columns

SQLRNNR — Tue, 20 Dec 2011 08:58:02 GMT

[quote][b]R.P.Rozema (12/20/2011)[/b][hr]:). Yes, that's how this question got to be: I did it wrong myself. And so far almost half of the people who tried, had it wrong too. To their avail, the ill-formatted answers don't help them... Once more, thanks for your assistance.[/quote]Meh - not too concerned about the answer format. Still a good question.

RE: Using sys.index_columns

SQLRNNR — Tue, 20 Dec 2011 08:57:12 GMT

[quote][b]GilaMonster (12/20/2011)[/b][hr]Hmm, this looks familiar. ;-)[/quote]Seemed familiar from a tweet conversation involving you.:cool:

RE: Using sys.index_columns

Carla Wilson-484785 — Tue, 20 Dec 2011 08:35:12 GMT

Several good tidbits in here, including demonstrating how important code formatting is for readability. :)Thanks for a good question.

RE: Using sys.index_columns

Cliff Jones — Tue, 20 Dec 2011 08:02:17 GMT

Great question, thanks.

RE: Using sys.index_columns

Stuart Davies — Tue, 20 Dec 2011 07:48:38 GMT

Good questionHad to come back to this one a couple of times before being sure I was right

RE: Using sys.index_columns

Britt Cluff — Tue, 20 Dec 2011 05:40:09 GMT

Tough question. I missed it but learned something today.

RE: Using sys.index_columns

R.P.Rozema — Tue, 20 Dec 2011 03:58:46 GMT

:). Yes, that's how this question got to be: I did it wrong myself. And so far almost half of the people who tried, had it wrong too. To their avail, the ill-formatted answers don't help them... Once more, thanks for your assistance.

RE: Using sys.index_columns

paul s-306273 — Tue, 20 Dec 2011 03:48:45 GMT

Good question.

RE: Using sys.index_columns

GilaMonster — Tue, 20 Dec 2011 03:18:27 GMT

Hmm, this looks familiar. ;-)

RE: Using sys.index_columns

Koen Verbeeck — Tue, 20 Dec 2011 01:25:56 GMT

[quote][b]R.P.Rozema (12/20/2011)[/b][hr]Yes, sorry for the formatting of the answers. This is my 1st QoD and I couldn't find how to format the answers. So I put them in as best as I could. But the end-result is indeed terrible. Lesson learnt for a next time: put the options in the text and in the answer fields just ask for #1, #2, #3 or #4.[/quote]That is indeed the best option. You can type in the answers in SSMS and take a screenshot. That way we all have the advantage of the color coding :-) (and it makes it a bit harder to just run the statements).Thanks again for the question, hopefully the first of many to come!

RE: Using sys.index_columns

Hugo Kornelis — Tue, 20 Dec 2011 00:53:48 GMT

[quote][b]R.P.Rozema (12/20/2011)[/b][hr]Yes, sorry for the formatting of the answers. This is my 1st QoD and I couldn't find how to format the answers. So I put them in as best as I could. But the end-result is indeed terrible. Lesson learnt for a next time: put the options in the text and in the answer fields just ask for #1, #2, #3 or #4.[/quote]That is indeed the best option. The formatting options for the question are adequate; for the answer options, formatting is non-existant.Thanks for submitting a great question, and I hope to see more questions coming from you in the future!

RE: Using sys.index_columns

R.P.Rozema — Tue, 20 Dec 2011 00:42:54 GMT

Yes, sorry for the formatting of the answers. This is my 1st QoD and I couldn't find how to format the answers. So I put them in as best as I could. But the end-result is indeed terrible. Lesson learnt for a next time: put the options in the text and in the answer fields just ask for #1, #2, #3 or #4.

RE: Using sys.index_columns

Koen Verbeeck — Tue, 20 Dec 2011 00:25:02 GMT

Nice question and I definately learned something, but quite an awful format :-)(I had to copy paste each answer into SSMS so I could read it better)

RE: Using sys.index_columns

Revenant — Tue, 20 Dec 2011 00:13:46 GMT

[quote][b]bitbucket-25253 (12/19/2011)[/b][hr]A lot of reading, but a nice question which will certainly teach those who answer incorrectly.Thanks[/quote]+1 - thanks!

RE: Using sys.index_columns

bitbucket-25253 — Mon, 19 Dec 2011 23:15:53 GMT

A lot of reading, but a nice question which will certainly teach those who answer incorrectly.Thanks

RE: Using sys.index_columns

SQLRNNR — Mon, 19 Dec 2011 22:20:33 GMT

nice question

RE: Using sys.index_columns

Henrico Bekker — Mon, 19 Dec 2011 21:47:43 GMT

Good one, thanks

Using sys.index_columns

R.P.Rozema — Mon, 19 Dec 2011 21:18:24 GMT

Comments posted to this topic are about the item [B]Using sys.index_columns[/B]