@table variable with constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

@table variable with constraint

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-12 : 01:56:46

[code]DECLARE @tempfun TABLE(
col1 INT,
col2 INT,
col3 INT,
CONSTRAINT [by_co1_co2] PRIMARY KEY CLUSTERED(col1, col2))[/code]
why this simple table variable failed?

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-12 : 01:58:55

because table variable does not support this. Use temp table instead

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-12 : 02:02:08

i forgot where i read, table variable do support constraint.... but cannot use CREATE INDEX only

Hope can help...but advise to wait pros with confirmation...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-12 : 02:04:03

But its work!

DECLARE @tempfun TABLE(
col1 INT,
col2 INT,
col3 INT,
PRIMARY KEY (col1, col2))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-08-12 : 02:06:21

you can do this with table variables, you just can't name the constraint. also you can't add indexes after the table is created. this works:


DECLARE @tempfun TABLE(
	col1 INT,
	col2 INT,
	col3 INT,
	PRIMARY KEY CLUSTERED(col1, col2))

elsasoft.org

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-12 : 02:07:54

hmmm i think i see wrongly...should be like senthil post

Hope can help...but advise to wait pros with confirmation...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-12 : 02:08:31

quote:
Originally posted by jezemine

you can do this with table variables, you just can't name the constraint. also you can't add indexes after the table is created. this works:

DECLARE @tempfun TABLE(
col1 INT,
col2 INT,
col3 INT,
PRIMARY KEY CLUSTERED(col1, col2))

elsasoft.org

Thanks!

Weather the Primary key create clustered index automatically?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-12 : 02:10:20

thx all!

Hope can help...but advise to wait pros with confirmation...

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-08-12 : 02:15:57

>>Weather the Primary key create clustered index automatically?

yes, pk is clustered by default. it's redundant to put clustered if you mean it to be, but it doesn't hurt anything.

elsasoft.org

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-12 : 02:19:24

quote:
Originally posted by jezemine

>>Weather the Primary key create clustered index automatically?

yes, pk is clustered by default. it's redundant to put clustered if you mean it to be, but it doesn't hurt anything.

elsasoft.org

Ok jezemine! Thanks.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources