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 Thursday, October 18, 2012 10:28 PM


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
Comments posted to this topic are about the item Table Variables

Tom
Post #1374633
Posted Friday, October 19, 2012 12:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:13 AM
Points: 2,631, Visits: 4,723
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/
Post #1374659
Posted Friday, October 19, 2012 1:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
Strange question if you ask me...
Post #1374669
Posted Friday, October 19, 2012 1:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 4:51 AM
Points: 1,854, Visits: 3,451
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.
Post #1374670
Posted Friday, October 19, 2012 2:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 2,933, Visits: 2,953
Very good question!
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1374676
Posted Friday, October 19, 2012 2:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:40 AM
Points: 1,615, Visits: 242
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'.
Post #1374692
Posted Friday, October 19, 2012 2:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:20 AM
Points: 1,945, Visits: 3,204
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.
Post #1374693
Posted Friday, October 19, 2012 3:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 4:51 AM
Points: 1,854, Visits: 3,451
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))

Post #1374695
Posted Friday, October 19, 2012 3:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 2,933, Visits: 2,953
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,
SQL Server developer at Seavus
www.seavus.com
Post #1374697
Posted Friday, October 19, 2012 3:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
I found this a good and quite straightforward question (though it is SQL Server 2012 not SQL Server 2011).



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1374701
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse