Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variables


Table Variables

Author
Message
Dave62
Dave62
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3054 Visits: 2716
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.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 72512
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?



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
paul.knibbs
paul.knibbs
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1972 Visits: 6214
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... :-)
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10712 Visits: 12016
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

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10712 Visits: 12016
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

arthur.teter
arthur.teter
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 122
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
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Narud
Narud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 502
Wow! I got it right! It was worth spend an hour researching to get this two points. I'll be happy all the weekend. :-D

Thanks Tom!
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 11579
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?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
patrickmcginnis59
patrickmcginnis59
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 2333
L' Eomot Inversé (10/19/2012)

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.


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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search