Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Table Variables Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 7:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:41 AM
Points: 2,046, Visits: 2,045
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.

Thanks for the question.
Post #1374808
Posted Friday, October 19, 2012 7:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 AM
Points: 3,675, Visits: 72,433
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
Post #1374820
Posted Friday, October 19, 2012 8:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 1,632, Visits: 5,585
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...
Post #1374843
Posted Friday, October 19, 2012 8:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 8,723, Visits: 9,273
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
Post #1374851
Posted Friday, October 19, 2012 8:26 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:29 AM
Points: 8,723, Visits: 9,273
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.

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.


Tom
Post #1374855
Posted Friday, October 19, 2012 9:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 10:16 AM
Points: 1,015, Visits: 98
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
Post #1374877
Posted Friday, October 19, 2012 9:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,594, Visits: 8,879
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
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
Post #1374879
Posted Friday, October 19, 2012 9:15 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, May 2, 2014 4:11 PM
Points: 645, Visits: 377
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!
Post #1374882
Posted Friday, October 19, 2012 9:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 5,977, Visits: 8,237
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
Post #1374885
Posted Friday, October 19, 2012 9:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
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.
Post #1374892
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse