Table Variables

  • Comments posted to this topic are about the item Table Variables

    Tom

  • Good QOTD.

    This will clear quite a few misconceptions people have about Table Variables.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Strange question if you ask me...

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

  • Very good question!

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • 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'.

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

  • 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))

  • 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

  • I found this a good and quite straightforward question (though it is SQL Server 2012 not SQL Server 2011).

  • 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]

  • 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!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • This was removed by the editor as SPAM

  • Very very Good Question... :p

    Got 1 out of 6 is correct ;(

  • 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