SQL Server 2000, create index on table datatype?

• mike wilson-201866

  SSC Enthusiast

  Points: 180

  More actions

  February 20, 2008 at 3:06 pm

  #186905

  In SQL Server 2000, can you create index on table datatype with the "Create Index" syntax?

  If so, should you be able to see the index in dbo.sysindexes?

  How can you prove the index exists on a table datatype?

  Thanks

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  February 20, 2008 at 3:45 pm

  #780443

  You cannot use the CREATE INDEX syntax with a table variable. For this reason, it is recommended that you use temporary tables (created with the CREATE TABLE # syntax) for storing temporary data where adding indexes may help with data operations. Table variables are typically used for small numbers of rows. You can, however, define a primary key on a column as you declare the table variable. Run the following example and check out the execution plans for the SELECTS.

  DECLARE @Table1 TABLE (Row1 int IDENTITY(1,1), Row2 varchar(10))

  INSERT INTO @Table1 (Row2)

  SELECT 'one' UNION ALL

  SELECT 'two' UNION ALL

  SELECT 'three' UNION ALL

  SELECT 'four' UNION ALL

  SELECT 'five'

  DECLARE @Table2 TABLE (Row1 int IDENTITY(1,1) PRIMARY KEY, Row2 varchar(10))

  INSERT INTO @Table2 (Row2)

  SELECT 'one' UNION ALL

  SELECT 'two' UNION ALL

  SELECT 'three' UNION ALL

  SELECT 'four' UNION ALL

  SELECT 'five'

  SELECT * FROM @Table1 WHERE Row2 = 'two'

  SELECT * FROM @Table1 WHERE Row1 = 3

  SELECT * FROM @Table2 WHERE Row2 = 'two'

  SELECT * FROM @Table2 WHERE Row1 = 3

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden