June 25, 2012 at 2:56 am
I had read anarticle in SQLSERVERCENTRAL.com comparing table variables and temporary tables. It said that table variables cannot have named constraints. Primary Key doesn't comes under named constarints?????
June 25, 2012 at 3:16 am
deepty21 (6/25/2012)
I had read anarticle in SQLSERVERCENTRAL.com comparing table variables and temporary tables. It said that table variables cannot have named constraints. Primary Key doesn't comes under named constarints?????
Depends on whether you name it...
This is OK:
PRIMARY KEY (colx) -- standalone
colx INT PRIMARY KEY -- inline
This is not OK:
CONSTRAINT PK_MyName PRIMARY KEY (colx) -- standalone
colx INT CONSTRAINT PK_MyName PRIMARY KEY -- inline
[Edit: quoted deepty21]
Best Regards,
Chris Büttner
June 25, 2012 at 3:44 am
I took a risk and made the correct assumptions, so guessed the right answer 🙂
But strictly speaking, the answer should be "false, false".
A Temporary Table can have primary keys ...
A Table Variable can have primary keys ...
A single table can have only one primary key 😉
June 25, 2012 at 4:04 am
I got it wrong as well, because I suppose I was thinking about "defining" in terms of a separate single statement (e.g. CREATE INDEX) giving something an actual name. However, if I'd thought about it for longer I could have worked out what the question was really asking.
Good follow-up discussion as always.
June 25, 2012 at 5:52 am
Christian Buettner-167247 (6/25/2012)
Poorly worded question. There is no way for the reader to identify if the author meant implicit or explicit index creation. And the fact that both Primary Keys AND Indexes were in question, it was more likely that explicit indexes were meant.
As a former professor, and taker of countless tests, I would say that failure to specify whether they meant implicit, explicit, or both, should mean that if any of those are true, then the question is true. I always felt, as a testor, it is my responsibility to clear up any areas of uncertainty in a question, or accept any reasonable answer given.
Now, exactly what constitutes "reasonable" is another discussion, and the subject of many pleadings during office hours. 🙂
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
June 25, 2012 at 6:54 am
I dislike questions that require analysis or over analysis in order to arrive at the correct answer.
A QOD question should be a simple test. A better question might have been:
Is it possible to have a table variable with a primary key ? (YES/NO)
This way, no hidden meanings or gramatical parsing is required. Just my opinion, YMMV.
BTW, I got it right because I over analyzed 😀
June 25, 2012 at 7:00 am
At least I agreed with the consensus "wrong" answer... 😎
June 25, 2012 at 7:13 am
I was always under the impression that there was no reason to create indexes on table variables because they exist only in memory.
Does anyone know what SQL Server actually does when you define a primary key in a table variable? Does it create an 'index' in memory?
June 25, 2012 at 7:16 am
Well, according to Hugo, it does in fact create an index, so an index does get created somewhere for it. However, it may not be in memory; I have seen multiple demonstrations to show that items get written into tempdb even for table variables, and that they do not exist only in memory.
June 25, 2012 at 7:21 am
sestell1 (6/25/2012)
I was always under the impression that there was no reason to create indexes on table variables because they exist only in memory.Does anyone know what SQL Server actually does when you define a primary key in a table variable? Does it create an 'index' in memory?
They don't exist solely in memory. The use tempdb just like anything else. This is a common myth about table variables.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2012 at 7:33 am
I chose the wrong answer (true, false) for all the reasons listed by others. I chose it knowing that there was a good possibility I'd be wrong because of how I interpreted the question.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 25, 2012 at 7:42 am
I see no problem with the wording. Can a table variable have indexes defined, yes or no? Yes, it can. That's all that was asked - what else you bring into the question is your problem. I see no way to change the answer to that question without adding conditions to it that were not in the original.
Can I post messages to SQLServerCentral.com forums? Yes I can. But I can't post to admin-only forums... therefore, what? Would you answer that question "no"?
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
June 25, 2012 at 7:58 am
ronmoses (6/25/2012)
I see no problem with the wording. Can a table variable have indexes defined, yes or no? Yes, it can. That's all that was asked - what else you bring into the question is your problem. I see no way to change the answer to that question without adding conditions to it that were not in the original.Can I post messages to SQLServerCentral.com forums? Yes I can. But I can't post to admin-only forums... therefore, what? Would you answer that question "no"?
ron
I beg to differ. You can't define indexes on table variables. You can define constraints that will cause SQL Server to create indexes under the hood, but that's a different kettle of fish.
June 25, 2012 at 8:11 am
Rune Bivrin (6/25/2012)
I beg to differ. You can't define indexes on table variables. You can define constraints that will cause SQL Server to create indexes under the hood, but that's a different kettle of fish.
On the contrary, I think that's exactly the kettle of fish the question asks about. "A Table Variable can have primary keys and indexes defined." I don't see the word "you" in there. Can indexes be defined on a table variable? Well, they can exist, and they're certainly not undefined, so the answer must be yes. If you want to clarify, you can say, "yes, by SQL Server when you define a constraint." Can you define them directly? No. But the question didn't ask that. A table variable can have indexes defined, regardless of whether or not you can explicitly define them. Seems like standard QotD material to me.
I can't dunk a basketball either. Doesn't mean a basketball can't be dunked. I can even hand off the ball to another player who can dunk it. Maybe he'll define an index while he's up there.
Though I suppose a system table is a better example. Can a system table contain rows? Can you add rows to a system table? (setting aside DAC for the moment)
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
June 25, 2012 at 8:46 am
David P Fisher (6/25/2012)
Hugo Kornelis (6/25/2012)
When I answered the question, I *knew* I was taking a gamble on the intended interpretation....... so I was looking for an answer option "true for temp tables; depends for table variables".
So was I - thanks Hugo
The same......
Viewing 15 posts - 16 through 30 (of 74 total)
You must be logged in to reply to this topic. Login to reply