October 18, 2012 at 10:28 pm
Comments posted to this topic are about the item Table Variables
Tom
October 19, 2012 at 12:56 am
Good QOTD.
This will clear quite a few misconceptions people have about Table Variables.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 19, 2012 at 1:53 am
Strange question if you ask me...
October 19, 2012 at 1:53 am
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.
October 19, 2012 at 2:11 am
Very good question!
IgorMi
Igor Micev,My blog: www.igormicev.com
October 19, 2012 at 2:53 am
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 <X64> (Build 6002: Service Pack 2)
DECLARE @t TABLE (id INT IDENTITY,
id1 INT CHECK (id>id1))
Msg 8141, Level 16, State 0, Line 1
Column CHECK constraint for column 'id1' references another column, table '@t'.
October 19, 2012 at 2:58 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.
Ditto. Grr. 🙂
Equally, the PK can be nonclustered. But I was thinking along the lines of explicity created indexes when I answered.
October 19, 2012 at 3:04 am
ako58 (10/19/2012)
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 <X64> (Build 6002: Service Pack 2)DECLARE @t TABLE (id INT IDENTITY,
id1 INT CHECK (id>id1))
Msg 8141, Level 16, State 0, Line 1
Column CHECK constraint for column 'id1' references another column, table '@t'.
you need to declare the check constraint separately
DECLARE @t TABLE (id INT IDENTITY,
id1 INT, CHECK (id>id1))
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.
create TABLE t(id INT IDENTITY,
id1 INT CHECK (id>id1))
create TABLE #t(id INT IDENTITY,
id1 INT CHECK (id>id1))
October 19, 2012 at 3:10 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.
A minor detail; You probably mean ROGUIDCOL, not ROWGUID.
declare @tabela table
(id int not null,
val1 varchar(20),
val2 varchar(20),
unique(val1,val2),
primary key nonclustered(id,val1)
)
Igor Micev,My blog: www.igormicev.com
October 19, 2012 at 3:21 am
I found this a good and quite straightforward question (though it is SQL Server 2012 not SQL Server 2011).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 19, 2012 at 3:32 am
I 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 😀
...and just in case anyone else was wondering...
http://www.scottishpoetrylibrary.org.uk/poetry/poems/cruaidh
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.
...translates to...
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.
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
October 19, 2012 at 4:39 am
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!
October 19, 2012 at 5:51 am
This was removed by the editor as SPAM
October 19, 2012 at 6:04 am
Very very Good Question... :p
Got 1 out of 6 is correct ;(
October 19, 2012 at 6:14 am
ako58 (10/19/2012)
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 <X64> (Build 6002: Service Pack 2)DECLARE @t TABLE (id INT IDENTITY,
id1 INT CHECK (id>id1))
Msg 8141, Level 16, State 0, Line 1
Column CHECK constraint for column 'id1' references another column, table '@t'.
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.
DECLARE @t TABLE (id INT IDENTITY,
id1 INT,
CHECK (id>id1))
and you will get "Command(s) completed successfully".
Tom
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply