Create index in table variable

Question

Create index in table variable

adonetok

SSCrazy Eights

Points: 8395
More actions
December 6, 2010 at 2:11 pm

#388372

How to create index in table variable? The code below will get an error:
declare @order table (
member_name varchar(30),
address varchar(50)
CREATE INDEX IX_member_name
ON @order (member_name)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

adonetok (12/6/2010)
How to create index in table variable? The code below will get an error:
declare @order table (
member_name varchar(30),
address varchar(50)
CREATE INDEX IX_member_name
ON @order (member_name)

Hi

See the below samples

declare @order table (

member_name varchar(30) PRIMARY key ,

address varchar(50) )

or

declare @order table (

member_name varchar(30) UNIQUE,

address varchar(50) )

Thanks

Parthi

Thanks
Parthi

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

Once create a table variable, I need to insert huge data into it. I want to create index after inserting.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

adonetok (12/6/2010)
Once create a table variable, I need to insert huge data into it. I want to create index after inserting.

Use a temp table instead.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 5

Then table variable(@) cant be use at this point use temp table (#) ,where you can use or modify the table

Eg:

--Create temp table

create table #temp(

number int not null,

alpha varchar(50) null

)

--- Adding some datas

if needed you can use this on condition after data has been loaded

alter table #temp

add constraint CL_temp primary key clustered(number asc)

Thanks

Parthi

Thanks
Parthi

Lowell SSC Guru Points: 323504 More actions · Answer 6

there was a thread alst week on how performance can be poor when you try to drop a temp table , where you created a temp table, and then add an index to it as a seperate step.

the work around was to make sure whatever column you wanted to index is either a PK or has a unique cosntraint on it in the single command CREATE TABLE definition.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 7

Hope, my article will help you on this.

http://venkattechnicalblog.blogspot.com/2010/12/data-compression-in-sql-server-2008.html

Cheers,

Venkatesan Prabu .J

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/

WayneS SSC Guru Points: 95409 More actions · Answer 8

adonetok (12/6/2010)
Once create a table variable, I need to insert huge data into it. I want to create index after inserting.

You cannot run DDL statements on a table variable after it has been created; everything you want to do must be done as part of the DECLARE statement.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2