How to add Nonunique index on table variable [duplicate]

Question

I want to create a table in my function but I have trouble in adding nonunique index on OBJECT_ID.

 DECLARE @VIEW_MY_DATA TABLE
                       (
                           [AREA_ID] INT,
                           [OBJECT_ID] INT,
                           [PARENT_ID] INT,
                           [OBJECT_TYPE] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
                           [RELATION] varchar(50)
                       )

I could not use CREATE TABLE in function. How to make it in SQL Server 2012?

Why are you trying to CREATE a table, or declare a table variable in a function? This seems like you are therefore using a multi-line function, which are far slower than an inline function. — Thom A
– Thom A ♦, Commented Apr 8, 2021 at 10:05
@Lamu Yes,it is used as temp table to store data and used for join in later sql code. — Ryan
– Ryan, Commented Apr 8, 2021 at 10:06
@VIEW_MY_DATA isn't a temporary table, it's a table variable. They are completely different. But, again, switch to an inline function; they are far faster. — Thom A
– Thom A ♦, Commented Apr 8, 2021 at 10:08

Dan Guzman · Accepted Answer · 2021-04-08 10:36:32Z

1

Although you cannot add an index to an already declared table variable, you can specify indexes and constraints using inline syntax of the table declaration in SQL Server 2014 and later:

 DECLARE @VIEW_MY_DATA TABLE
                       (
                           [AREA_ID] INT,
                           [OBJECT_ID] INT INDEX idx NONCLUSTERED,
                           [PARENT_ID] INT,
                           [OBJECT_TYPE] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS,
                           [RELATION] varchar(50)
                       )

edited Apr 8, 2021 at 10:36

answered Apr 8, 2021 at 10:24

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ryan Over a year ago

I have error in this way INDEX idx NONCLUSTERED

Thom A Over a year ago

Looks like this functionality is not in SQL Server 2012.

Dan Guzman Over a year ago

@Larnu, correct, I missed SQL 2012 in the question. I'll clarify my answer.

Thom A Over a year ago

Out of interest, @DanGuzman, do you know what version this was added in? Seems I missed this in the patch notes. :)

Charlieface Over a year ago

Quote from the docs: "Indexes can't be created explicitly on table variables, and no statistics are kept on table variables. Starting with SQL Server 2014 (12.x), new syntax was introduced which allows you to create certain index types inline with the table definition."

|

Collectives™ on Stack Overflow

How to add Nonunique index on table variable [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related