October 19, 2012 at 6:29 am
Hugo Kornelis (10/19/2012)
Very good question overall.Only (minor) issue - picking 6 out of 17 answer options is a task that appears daunting at first sight. The answer options were luckily well organized so that it soon becamse apparent that these were actually six different (but related) multiple choice questions. But it might have been better to create two or three seperate questions, each covering a subset of the answers.
Also: only 9% correct answers so far. With so many things to get right, I'd expect a high failure rate - but not this high!
I was expecting a pretty high failure rate (perhaps not quite as high as it has been so far), because there are a lot of myths about table variables; the myths about them being held in main store, not on disc, or not being held in tempd, and various associated rullbis have been addressed by questions from other question authors, and I thought it would be a good idea to address most of the myths about what constraints they have. Maybe it would have been better split into smaller questions, or maybe it's good to have it all in one place - I didn't actually flip a coin, but I did dither briefly before deciding to put everything in one place.
Tom
October 19, 2012 at 6:34 am
Nils Gustav Stråbø (10/19/2012)
Aaaaarg!!! Got it wrong because of the unclustered index options.I know that SQL Server enforces unique constraints by creating unique indexs, but I wasn't sure if this was what the author meant.
+1
This kind of gotcha is common on QotD, and it's always a guessing game what the author intended. I say "gotcha" not b/c I think it was a deliberate attempt by Tom to mislead, but b/c of the 2 different ways in which people can interpret this.
Good question though...
Rich
Edited to add hyperlink: There's a nice write-up about table variables at this link on SSC[/url]. Although that article's intent was to compare table variables to temp tables, I find the tabular summary presented there to be handy.
October 19, 2012 at 6:37 am
Excellent question. This took some time to research because I rarely use table variables.
Aigle de Guerre!
October 19, 2012 at 6:48 am
Hugo Kornelis (10/19/2012)
Very good question overall.Only (minor) issue - picking 6 out of 17 answer options is a task that appears daunting at first sight. The answer options were luckily well organized so that it soon becamse apparent that these were actually six different (but related) multiple choice questions. But it might have been better to create two or three seperate questions, each covering a subset of the answers.
Also: only 9% correct answers so far. With so many things to get right, I'd expect a high failure rate - but not this high!
Well I for one just picked an arbitrary line and clicked it, and I don't care to get over 4 digits worth of points here, I was interested in the discussions. I'm pretty confident that I don't know all the rules for table variables or even a nontrivial amount of facts regarding SQL server for that matter.
What this does bring up for me though is that if I'm not that interested in certification, what do I and other new DBA's do to find THE important things to know about SQL Server? For instance, while I'm sure knowing the table variable stuff would be good to know, if SQL rejected a construct I offered, I'd pretty much hit books on line and see what the rules are for this particular situation so no big deal. However I recently read that its not a good idea to resize a tempdb and this seems much more critical to know (and very unintuitive btw, if a systems coder shipped a storage system that couldn't dynamically (and correctly) manage utilization, I would have to have him fix it or find someone who could), yet this is another arbitrary bit of trivia an SQL user would NEED to know because it doesn't seem that Microsoft considers this a bug (while the programmer in me certainly does).
This is not an invite to argue, I'm just offering up an interesting perspective I've been viewing the SQL world from lately.
October 19, 2012 at 6:55 am
The index fooled me too. I was not thinking about the PK.. At least I learned more about table variables today. I use them quite often, especially if I need to use a cursor. :w00t:
October 19, 2012 at 7:09 am
Which of the following statements is true of table variables in SQL Server 2008, 2008 R2, and 2011? (select 6)
It's not possible to pick 6 true statements for all of these versions because one of them (2011) doesn't exist.
Just kidding Tom, I knew you meant 2012. :hehe:
Thanks for the question.
October 19, 2012 at 7:38 am
Wow.... I knew I had to have this one wrong. I answered it out of memory and didn't even use BOL.
Imagine my surprise when I was one of the 9% who got it correct?
October 19, 2012 at 8:08 am
There's something wrong with my brain this week--I did the research and then somehow ended up clicking that you could have multiple ROWGUIDs on a table variable, even though you can't have that on a *normal* table, much less a variable! I need to sleep all weekend and hope my wits have recovered by Monday morning, I think... 🙂
October 19, 2012 at 8:20 am
patrickmcginnis59 (10/19/2012)
For instance, while I'm sure knowing the table variable stuff would be good to know, if SQL rejected a construct I offered, I'd pretty much hit books on line and see what the rules are for this particular situation so no big deal.
I agree - generally I don't learn detail unless I use it now and again (when I've got it wrong often enough and looked it up each time to get it right I find I have learnt it). However, it's worth looking at enough to know what features exist - for example if you don't know that a table variable can have a check table constraint you won't try to write one in the first place.
However I recently read that its not a good idea to resize a tempdb and this seems much more critical to know
I don't think I believe that one. For example on my laptop when I installed 2008 R2 it came up with sizes and growth parameters for tempdb that I thought would not be good if I threw serious (well, as serious as I want to do on my laptop, not real serious) work at the system; log file growth by 10% from 512KB, to disc full, for example, risks ending up with an insane number of virtual logs even if it doesn't get to disc full. So the first thing I did was resize tempdb to use sensible (for my laptop) parameters, like this:
alter database tempdb MODIFY FILE ( NAME = 'tempdev', SIZE = 8MB , MAXSIZE = 1024MB, FILEGROWTH = 100% );
alter database tempdb MODIFY FILE ( NAME = 'templog', SIZE = 4MB , MAXSIZE = 512MB, FILEGROWTH = 100% ); and that certainly did me no harm.
(and very unintuitive btw, if a systems coder shipped a storage system that couldn't dynamically (and correctly) manage utilization, I would have to have him fix it or find someone who could),
Me too; and that's why most of the time there is no need to do anything about tempdb size except just after installation, or when adding some new workload that will have serious impact on the sizes needed. The only thing that anyone could reasonably complain about is that it doesn't watch for when it has too much space and then shrink big when the workload gets back to normal, and I'm not sure that automatic shrinking is actually sensible.
yet this is another arbitrary bit of trivia an SQL user would NEED to know because it doesn't seem that Microsoft considers this a bug (while the programmer in me certainly does).
Maybe some programmers, but other programmers consider the statement that SQL Server doesn't correctly manage the size of tempdb to be either incorrect or at least uncertain, so we don't consider it to be a bug.
This is not an invite to argue, I'm just offering up an interesting perspective I've been viewing the SQL world from lately.
I wouldn't disagree with your general idea here, so no real argument; but some of the detail may be worth a second thought.
Tom
October 19, 2012 at 8:26 am
Dave62 (10/19/2012)
Which of the following statements is true of table variables in SQL Server 2008, 2008 R2, and 2011? (select 6)
It's not possible to pick 6 true statements for all of these versions because one of them (2011) doesn't exist.
Just kidding Tom, I knew you meant 2012. :hehe:
Thanks for the question.
I blame my left ring finger - it's trying to usurp extra territory and keeps on pushing the little finger over. :w00t:
Tom
October 19, 2012 at 9:07 am
While I know you are not supposed to be able to add multiple GUIDs to any table, on the systems I have worked on I have found that with table variables it is possible. When I run the following I do not get any errors
declare @TableVar table(MyID INT IDENTITY PRIMARY KEY CLUSTERED
, NEXTFIELD varchar(10), nextfield2 varchar(10)
, ROWGUID UNIQUEIDENTIFIER
, ROWGUID2 UNIQUEIDENTIFIER
)
insert into @TableVar (NEXTFIELD, nextfield2, ROWGUID, ROWGUID2)
values ('Test1', 'TEST1', NEWID(), NEWID())
, ('Test2', 'TEST2', NEWID(), NEWID())
, ('TEST3', 'HOW', NEWID(), NEWID())
select * from @TableVar
October 19, 2012 at 9:09 am
Tom,
Except for the 2011 typo (which was so obvious that I hope all ignore it), this is a great question.
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 19, 2012 at 9:15 am
Wow! I got it right! It was worth spend an hour researching to get this two points. I'll be happy all the weekend. 😀
Thanks Tom!
October 19, 2012 at 9:20 am
arthur.teter (10/19/2012)
While I know you are not supposed to be able to add multiple GUIDs to any table
Why not? What if a table has to store foreign keys into multiple tables that each use a GUID for their primary key?
October 19, 2012 at 9:31 am
L' Eomot Inversé (10/19/2012)
Maybe some programmers, but other programmers consider the statementthat SQL Server doesn't correctly manage the size of tempdb to be either incorrect or at least uncertain, so we don't
consider it to be a bug.
Its a new heads up to me, I'm still digesting it and could be misinterpretting the whole situation. Here was the first heads up for me, posted by Sean Lange:
http://www.sqlservercentral.com/Forums/FindPost1369994.aspx
which refers to
http://support.microsoft.com/kb/307487
which coming direct from Microsoft does give some legitimacy to the issue. I may have spoken too soon about it "not being considered a bug" though as books on line (on my install) gives:
"The database being shrunk does not have to be in single user mode; other users can be working in the database when it is shrunk. This includes system databases."
so given the conflicting info, maybe the situation is not quite "not a bug", but I certainly am at least now aware of the issue.
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply