Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create Index on Table Variable

Status
Not open for further replies.

ej00

Programmer
Sep 18, 2001
6
US

Is it possible in SQL Server 2000 to create an index on a table declared as a variable. I get a syntax error when attempting to do this.

For example...
DECLARE @mytable TABLE (col_a INT)

CREATE INDEX i_mytable ON @mytable (col_a)
 

You can't use a variable for a table or column name. However, you can dynamically create a SQL statement and execute it.

Create Procedure CreateNewIndex
@mytable nvarchar(40),
@indexname nvarchar(20),
@columns nvarchar(60) As

Declare @sql nvarchar(500)

Set @sql=
'CREATE INDEX ' + @indexname +
' ON ' + @mytable +
'(' + @columns + ')'

Execute @sql

----------------------

Stored procedure call:
Exec CreateNewIndex 'tblAddresses', 'LastName, FirstName' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 


Thanks for the quick response. I am not sure I stated clearly what I was asking for so please let me elaborate. If I have the following...


CREATE PROCEDURE a
AS
BEGIN


DECLARE @mytable TABLE (col_a INT)

INSERT INTO @mytable
SELECT col_1
FROM any_other_table

SELECT * FROM @mytable

END

This is all legal so far.

If I include the following line...


CREATE PROCEDURE a
AS
BEGIN

DECLARE @mytable TABLE (col_a INT)

CREATE INDEX i ON @mytable(col_a)

INSERT INTO @mytable
SELECT col_1
FROM any_other_table

SELECT * FROM @mytable

END

I get the syntax error. Is there a way to create the index i? I suppose I could create col_a as a primary key and that would do for this example but more importantly I want to be able to create an index on a table variable.
 

I apologize for the misundersanding. You must be using SQL 2000 and the table variable. We are just converting to SQL 2000 so I'm not familiar with table variables.

The following information from SQL BOL should answer your questions.[ul]Table variables and user-defined functions that return a table can be used only in certain SELECT and INSERT statements, and where tables are supported in the UPDATE, DELETE, and DECLARE CURSOR statements. table variables and user-defined functions that return a table cannot be used in any other Transact-SQL statements.

Indexes or other constraints applied to the table must be defined as part of the DECLARE variable or CREATE FUNCTION statement. They cannot be applied later, because the CREATE INDEX or ALTER TABLE statements [ul]cannot reference[/u] table variables and user-defined functions.
[/ul]
Are you adding a large number of rows to the table? Are you joining the table to another table? What is the purpose of the index? Inserts should be faster without an index. Indexing won't do much to improve select performance unless the number of records is quite large. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 

Terry,

Thanks for the feedback. I am using SQL 2000. As for the usage, I am inserting about 500,000 records into the table. I have a couple of insert statements then go on to reference the table several more times. I read that table variables are more efficient and was trying to take advantage of that fact. I just converted over to a temp table (#mytable) and built the index after the inserts. In doing so I cut my run time in half (20 minutes down to 10 minutes). Hopefully a future release will include the ability to build an index post creation of the table variable. Thanks again for the information.


Ed.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top