0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ryan
  • 19,118
  • 10
  • 37
  • 53
  • 2
    You can't index a table variable. – Thom A Apr 08 '21 at 10:04
  • 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 Apr 08 '21 at 10:05
  • @Lamu Yes,it is used as temp table to store data and used for join in later sql code. – Ryan Apr 08 '21 at 10:06
  • L a r n u... Please. – Thom A Apr 08 '21 at 10:07
  • 2
    `@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 Apr 08 '21 at 10:08
  • You'll be far better off asking a question showing us what you are *really* trying to achieve here. As, however, this'll invalid Dan's answer, I would suggest doing so in a new question as moving the goal post so much is severely frowned upon once you have answers. – Thom A Apr 08 '21 at 10:41

1 Answers1

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)
                       )
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • I have error in this way `INDEX idx NONCLUSTERED` – Ryan Apr 08 '21 at 10:26
  • Looks like this functionality is not in SQL Server 2012. – Thom A Apr 08 '21 at 10:27
  • @Larnu, correct, I missed SQL 2012 in the question. I'll clarify my answer. – Dan Guzman Apr 08 '21 at 10:35
  • Out of interest, @DanGuzman, do you know what version this was added in? Seems I missed this in the patch notes. :) – Thom A Apr 08 '21 at 10:36
  • 1
    Quote from the [docs](https://learn.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql?view=sql-server-ver15#limitations-and-restrictions): "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." – Charlieface Apr 08 '21 at 10:36
  • Ty @Charlieface . – Thom A Apr 08 '21 at 10:36