SQLServerCentral.com / Article Discussions / Article Discussions by Author / Discuss content posted by Tom Thomson / Table Variables / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 15:23:07 GMT20RE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx5 good answer + 1 bad = 0 pointThu, 10 Jan 2013 07:44:36 GMTjfgoudeRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxGood question, managed to be one of the few to get it right. It wasn't by knowledge alone, however, it took a bit of deductive reasoning as well.Fri, 26 Oct 2012 07:33:35 GMTLynn PettisRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxDifficult question.Mon, 22 Oct 2012 13:20:15 GMT@CassieRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxSuds. one stinking mis-click though I intended to click permit identity I slipped and hit the wrong check box.Grrrrr...:-)Mon, 22 Oct 2012 11:26:07 GMTSQLRNNRRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxIntresting question...got it wrong though...Mon, 22 Oct 2012 01:07:54 GMTkalyani.k478RE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxWhat's an [i]unclustered[/i] index? On BOL I only see clustered and nonclustered :-PGreat question, but a wee bit too much for in one single question actually.Mon, 22 Oct 2012 00:52:40 GMTKoen VerbeeckRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxGood question. thank youIulianSun, 21 Oct 2012 01:32:18 GMTIulian -207023RE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]patrickmcginnis59 (10/19/2012)[/b][hr][quote][b]L' Eomot Inversé (10/19/2012)[/b][hr]When you referred to SQL Server not managing the size of tempdb correctly, I assumed you were complaining about SQL Server not managing the size correctly.[/quote]True! I really didn't give the proper terminology, sorry to divert the thread![/quote]Oh M.G!I didn't mean to say quite that - that was very rude of me. :blush: All I meant top say was that there were different ways of interpreting it. Sorry!Fri, 19 Oct 2012 19:41:27 GMTTomThomsonRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]L' Eomot Inversé (10/19/2012)[/b][hr]When you referred to SQL Server not managing the size of tempdb correctly, I assumed you were complaining about SQL Server not managing the size correctly.[/quote]True! I really didn't give the proper terminology, sorry to divert the thread!Fri, 19 Oct 2012 13:55:36 GMTpatrickmcginnis59RE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]Hugo Kornelis (10/19/2012)[/b][hr][quote][b]L' Eomot Inversé (10/19/2012)[/b][hr]Actually, there's a misprint in the question (my stupid error) - it should say @ROWGUIDCOL, not ROWGUID. You can have as many rowguid columns as you like in any kind of table (just as you can have as many int columns as you like in any kind of table - after all, rowguid is a type like any other type), but only one of them can have the ROWGUIDCOL property (just as only one column can have the IDENTITY property). I'll ask Steve to correct that wording (and 2011) if possible.[/quote]I disagree with this. Yes, the question was technically not completely accurate. But your explanation here is worse. The term "ROWGUID" does not exist in SQL Server. It is not a type like any other type. uniqueidentifier is a type like any other type, and the commonly accepted short form for that data type name is guid. Not ROWGUID.To me, it was obvious that "ROWGUID columns" was intended as "columns with the ROWGUIDCOL attribute. Especially since "ROWGUIDCOL columns" would have sounded redundant.[/quote]Well, since ROWGUID (as opposed to GUID or ROWGUIDCOL) is not a legitimate term, it's possible for people to mistake it as meaning GUID (ie uniqueidentifier) type rather than the column property ROWGUIDCOL, and it seems clear that this is what Arthur.Teter did; so I wanted to eliminate the ambiguity, to make sure that more people don't fall into that trap. I agree about the sound of redundancy, though. I think I picked up using ROWGUID instead of ROWGUIDCOL from the appalling habit displayed in certain third party databases I had which I had the misfortune to look into where the vendors had the awful habit of using uniqueidentifier surrogate keys and calling every surrogate just rowguid in the table where it was primary key (instead of something useful like tablename_surrogate_key); I think their developers must have been used to working in an old dialect of Fortran where the maximum allowed identifier length was 8 characters and believed the same length restriction applied in SQL.Fri, 19 Oct 2012 13:30:52 GMTTomThomsonRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]patrickmcginnis59 (10/19/2012)[/b][hr][quote][b]L' Eomot Inversé (10/19/2012)[/b][hr] 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.[/quote]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:[url]http://www.sqlservercentral.com/Forums/FindPost1369994.aspx[/url]which refers to[url]http://support.microsoft.com/kb/307487[/url]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.[/quote]Ah. I think I misunderstood you.If you had said "shrink" (implying either shrinkdatabase or shrinkfile) I would have agreed that was bad (and I do agree MS have a bug there). In my opinion they do have a bug there - but they have clearly admitted that doing this can cause corruption of tempdb, so I don't think they are denying it. Whether shrink (as opposed to alter database modify) should be considered a legitimate method for the user to reduce the size of tempdb without ensuring a quiesced system by going into single user mode is neither here nor there (lets be clear: I don't consider it a legitimate method at all): until the documentation says you can't do that and the user interface prevents you from doing it, either they fix it so that it wroks reliably or they have a bug. When you referred to SQL Server not managing the size of tempdb correctly, I assumed you were complaining about SQL Server not managing the size correctly - but actually you are referring to a bug that happens when the user (not SQL Server) attempts to manage the size of tempdb instead of letting SQL Server carry out its own management, guided by required start sizes, growth rates, and max sizes specified by the user. So you weren't assertuing a bug in the part that I thought you were. Hence the misunderstanding.Fri, 19 Oct 2012 13:14:50 GMTTomThomsonRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]arthur.teter (10/19/2012)[/b][hr]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 errorsdeclare @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[/quote]I'm pretty sure that the ROWGUID options are not about the UNIQUEIDENTIFIER data type, but about the ROWGUIDCOL property for a column, and only one column per table can have this property, just like only one column can have the IDENTITY property.[url=http://msdn.microsoft.com/en-us/library/ms191131(v=sql.105).aspx]Creating and Modifying Identifier Columns[/url]Fri, 19 Oct 2012 12:55:07 GMTNils Gustav StråbøRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]L' Eomot Inversé (10/19/2012)[/b][hr]Actually, there's a misprint in the question (my stupid error) - it should say @ROWGUIDCOL, not ROWGUID. You can have as many rowguid columns as you like in any kind of table (just as you can have as many int columns as you like in any kind of table - after all, rowguid is a type like any other type), but only one of them can have the ROWGUIDCOL property (just as only one column can have the IDENTITY property). I'll ask Steve to correct that wording (and 2011) if possible.[/quote]I disagree with this. Yes, the question was technically not completely accurate. But your explanation here is worse. The term "ROWGUID" does not exist in SQL Server. It is not a type like any other type. uniqueidentifier is a type like any other type, and the commonly accepted short form for that data type name is guid. Not ROWGUID.To me, it was obvious that "ROWGUID columns" was intended as "columns with the ROWGUIDCOL attribute. Especially since "ROWGUIDCOL columns" would have sounded redundant.Fri, 19 Oct 2012 12:48:32 GMTHugo KornelisRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]arthur.teter (10/19/2012)[/b][hr]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 errorsdeclare @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[/quote]Actually, there's a misprint in the question (my stupid error) - it should say @ROWGUIDCOL, not ROWGUID. You can have as many rowguid columns as you like in any kind of table (just as you can have as many int columns as you like in any kind of table - after all, rowguid is a type like any other type), but only one of them can have the ROWGUIDCOL property (just as only one column can have the IDENTITY property). I'll ask Steve to correct that wording (and 2011) if possible.Fri, 19 Oct 2012 12:44:20 GMTTomThomsonRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxGood question although the unlustered index thing got me as well.Fri, 19 Oct 2012 12:01:46 GMTLon-860191RE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]L' Eomot Inversé (10/19/2012)[/b][hr] 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.[/quote]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:[url]http://www.sqlservercentral.com/Forums/FindPost1369994.aspx[/url]which refers to[url]http://support.microsoft.com/kb/307487[/url]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.Fri, 19 Oct 2012 09:31:28 GMTpatrickmcginnis59RE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]arthur.teter (10/19/2012)[/b][hr]While I know you are not supposed to be able to add multiple GUIDs to any table[/quote]Why not? What if a table has to store foreign keys into multiple tables that each use a GUID for their primary key?Fri, 19 Oct 2012 09:20:24 GMTHugo KornelisRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxWow! I got it right! It was worth spend an hour researching to get this two points. I'll be happy all the weekend. :-DThanks Tom!Fri, 19 Oct 2012 09:15:07 GMTNarudRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxTom,Except for the 2011 typo (which was so obvious that I hope all ignore it), this is a great question.Thanks!Fri, 19 Oct 2012 09:09:03 GMTWayneSRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxWhile 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 errorsdeclare @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 @TableVarFri, 19 Oct 2012 09:07:25 GMTarthur.teterRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]Dave62 (10/19/2012)[/b][hr][quote]Which of the following statements is true of table variables in SQL Server 2008, 2008 R2, and 2011? (select 6)[/quote]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.[/quote]I blame my left ring finger - it's trying to usurp extra territory and keeps on pushing the little finger over. :w00t:Fri, 19 Oct 2012 08:26:21 GMTTomThomsonRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]patrickmcginnis59 (10/19/2012)[/b][hr]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. [/quote]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. [quote]However I recently read that its not a good idea to resize a tempdb and this seems much more critical to know[/quote]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:[code]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% );[/code] and that certainly did me no harm. [quote] (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),[/quote]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. [quote]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).[/quote] 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.[quote]This is not an invite to argue, I'm just offering up an interesting perspective I've been viewing the SQL world from lately.[/quote]I wouldn't disagree with your general idea here, so no real argument; but some of the detail may be worth a second thought.Fri, 19 Oct 2012 08:20:16 GMTTomThomsonRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxThere'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... :-)Fri, 19 Oct 2012 08:08:23 GMTpaul.knibbsRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxWow.... 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?Fri, 19 Oct 2012 07:38:19 GMTmtassinRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote]Which of the following statements is true of table variables in SQL Server 2008, 2008 R2, and 2011? (select 6)[/quote]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.Fri, 19 Oct 2012 07:09:47 GMTDave62RE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxThe 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:Fri, 19 Oct 2012 06:55:51 GMTDan GraveenRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]Hugo Kornelis (10/19/2012)[/b][hr]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![/quote]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.Fri, 19 Oct 2012 06:48:05 GMTpatrickmcginnis59RE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxExcellent question. This took some time to research because I rarely use table variables.Fri, 19 Oct 2012 06:37:57 GMTMeow NowRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]Nils Gustav Stråbø (10/19/2012)[/b][hr]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.[/quote]+1This 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...RichEdited to add hyperlink: There's a nice write-up about table variables [url=http://www.sqlservercentral.com/articles/Temporary+Tables/66720/]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.Fri, 19 Oct 2012 06:34:27 GMTrmechaberRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]Hugo Kornelis (10/19/2012)[/b][hr]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![/quote]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.Fri, 19 Oct 2012 06:29:52 GMTTomThomsonRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]ako58 (10/19/2012)[/b][hr]Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 &lt;X64&gt; (Build 6002: Service Pack 2) DECLARE @t TABLE (id INT IDENTITY, id1 INT CHECK (id&gt;id1))Msg 8141, Level 16, State 0, Line 1Column CHECK constraint for column 'id1' references another column, table '@t'.[/quote]Just put a comma before CHECK so that you are declaring a table constraint instead of trying to declare a column constraint that references another column.[code]DECLARE @t TABLE (id INT IDENTITY, id1 INT, CHECK (id&gt;id1))[/code]and you will get "Command(s) completed successfully".Fri, 19 Oct 2012 06:14:29 GMTTomThomsonRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxVery very Good Question... :pGot 1 out of 6 is correct ;(Fri, 19 Oct 2012 06:04:31 GMTJamsheerRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxGood question, thanks Tomvery nearly stumbled on the non-clustered index option, but remembered a wee discussion with Gail in this regard...Fri, 19 Oct 2012 05:51:17 GMTStewart "Arturius" CampbellRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxVery 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!Fri, 19 Oct 2012 04:39:08 GMTHugo KornelisRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxI got it wrong, you can have multi almost everything except rowguid (which is the only one I got right)... ah well... good question though my brain hurts :-D...and just in case anyone else was wondering...[url=http://www.scottishpoetrylibrary.org.uk/poetry/poems/cruaidh]http://www.scottishpoetrylibrary.org.uk/poetry/poems/cruaidh[/url][i]nuair a ruigeas tu Tìr a’ Gheallaidh,mura bi thu air t’ aire,coinnichidh Sasannach riut is plion air,a dh’ innse dhut gun tug Dia, bràthair athar, còir dha anns an fhearann.[/i]...translates to...[i]when you reach the Promised Land,unless you are on your toes,a bland Englishman will meet you,and say to you that God, his uncle, has given him a title to the land.[/i]Fri, 19 Oct 2012 03:32:09 GMTDugyCRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxI found this a good and quite straightforward question (though it is SQL Server 2012 not SQL Server 2011).Fri, 19 Oct 2012 03:21:26 GMTPaul WhiteRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]Nils Gustav Stråbø (10/19/2012)[/b][hr]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.A minor detail; You probably mean ROGUIDCOL, not ROWGUID.[/quote]declare @tabela table(id int not null, val1 varchar(20), val2 varchar(20), unique(val1,val2), primary key nonclustered(id,val1))Fri, 19 Oct 2012 03:10:35 GMTIgor MicevRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]ako58 (10/19/2012)[/b][hr]Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 &lt;X64&gt; (Build 6002: Service Pack 2) DECLARE @t TABLE (id INT IDENTITY, id1 INT CHECK (id&gt;id1))Msg 8141, Level 16, State 0, Line 1Column CHECK constraint for column 'id1' references another column, table '@t'.[/quote]you need to declare the check constraint separately[code="sql"]DECLARE @t TABLE (id INT IDENTITY, id1 INT, CHECK (id&gt;id1))[/code]The error you got applies to normal and temporary tables aswell, so it is not a limitation on table variables. the following will also fail with the same error message.[code="sql"]create TABLE t(id INT IDENTITY, id1 INT CHECK (id&gt;id1)) create TABLE #t(id INT IDENTITY, id1 INT CHECK (id&gt;id1))[/code]Fri, 19 Oct 2012 03:04:00 GMTNils Gustav StråbøRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspx[quote][b]Nils Gustav Stråbø (10/19/2012)[/b][hr]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.[/quote]Ditto. Grr. :-)Equally, the PK can be nonclustered. But I was thinking along the lines of explicity created indexes when I answered.Fri, 19 Oct 2012 02:58:27 GMTGazarethRE: Table Variableshttp://www.sqlservercentral.com/Forums/Topic1374633-2681-1.aspxMicrosoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 &lt;X64&gt; (Build 6002: Service Pack 2) DECLARE @t TABLE (id INT IDENTITY, id1 INT CHECK (id&gt;id1))Msg 8141, Level 16, State 0, Line 1Column CHECK constraint for column 'id1' references another column, table '@t'.Fri, 19 Oct 2012 02:53:30 GMTako58