SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 @table variable with constraint
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

waterduck
Aged Yak Warrior

Malaysia
946 Posts

Posted - 08/12/2009 :  01:56:46  Show Profile  Reply with Quote
DECLARE @tempfun TABLE(
	col1 INT,
	col2 INT,
	col3 INT,
	CONSTRAINT [by_co1_co2] PRIMARY KEY CLUSTERED(col1, col2))

why this simple table variable failed?


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

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 08/12/2009 :  01:58:55  Show Profile  Reply with Quote
because table variable does not support this. Use temp table instead


KH
Time is always against us

Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
946 Posts

Posted - 08/12/2009 :  02:02:08  Show Profile  Reply with Quote
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...
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 08/12/2009 :  02:04:03  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 08/12/2009 :  02:06:21  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
946 Posts

Posted - 08/12/2009 :  02:07:54  Show Profile  Reply with Quote
hmmm i think i see wrongly...should be like senthil post


Hope can help...but advise to wait pros with confirmation...
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 08/12/2009 :  02:08:31  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
946 Posts

Posted - 08/12/2009 :  02:10:20  Show Profile  Reply with Quote
thx all!


Hope can help...but advise to wait pros with confirmation...
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 08/12/2009 :  02:15:57  Show Profile  Visit jezemine's Homepage  Reply with Quote
>>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

Edited by - jezemine on 08/12/2009 02:16:30
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 08/12/2009 :  02:19:24  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000