Can I add an index to a table type

Question

Can I add an index to a table type

mw_sql_developer

SSCoach

Points: 19445
More actions
June 23, 2015 at 2:43 pm

#299368

This is my table type definition ( below ) and as you see I declared a table whose name is in @t
/*
CREATE TYPE BP_Data_ACRC_427
as table
(
AIMS_VALUE_NUMERIC numeric(19,2),
bp_type VARCHAR(4),
Dt datetime,
ID int IDENTITY(1,1),
MBP numeric(19,2),
MPOG_PHYSIOLOGIC_CONCEPT_ID VARCHAR(10)
)
*/
Declare @t as BP_Data_ACRC_427
Question: can we add an index that combines AIMS_VALUE_NUMERIC and Dt

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

mw112009 (6/23/2015)
This is my table type definition ( below ) and as you see I declared a table whose name is in @t
/*
CREATE TYPE BP_Data_ACRC_427
as table
(
AIMS_VALUE_NUMERIC numeric(19,2),
bp_type VARCHAR(4),
Dt datetime,
ID int IDENTITY(1,1),
MBP numeric(19,2),
MPOG_PHYSIOLOGIC_CONCEPT_ID VARCHAR(10)
)
*/
Declare @t as BP_Data_ACRC_427
Question: can we add an index that combines AIMS_VALUE_NUMERIC and Dt

Is the combination of those two columns guaranteed to be unique? If so you can just define those columns as the primary key. There are other options if that combination is not unique.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Just pick any 2 columns you wish. I am only interested in the syntax.

I don't want a primary key, I need an index to make things faster

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

mw112009 (6/23/2015)
Just pick any 2 columns you wish. I am only interested in the syntax.
I don't want a primary key, I need an index to make things faster

An index on any two columns is NOT going to make things faster. Your queries aren't just going to magically be faster because an index exists.

Do you realize that a primary key by default will also generate a clustered index? You can't create an index on a user defined table type like a standard index. You have to get a bit more creative.

From the MSDN page for user defined table types (https://technet.microsoft.com/en-us/library/bb522526.aspx):

A nonclustered index cannot be created on a user-defined table type unless the index is the result of creating a PRIMARY KEY or UNIQUE constraint on the user-defined table type. (SQL Server enforces any UNIQUE or PRIMARY KEY constraint by using an index.)

This is why I am asking about those two columns being unique. This can be accomplished but it is a little different than a persistent table.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

CREATE TYPE BP_Data_ACRC_427_B

as table

(

AIMS_VALUE_NUMERIC numeric(19,2),

bp_type VARCHAR(4),

Dt datetime ,

ID int IDENTITY(1,1),

MBP numeric(19,2),

MPOG_PHYSIOLOGIC_CONCEPT_ID VARCHAR(10)

UNIQUE CLUSTERED (MPOG_PHYSIOLOGIC_CONCEPT_ID,Dt)

)

IT works in SQL Sevrer 2012

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

mw112009 (6/23/2015)
CREATE TYPE BP_Data_ACRC_427_B
as table
(
AIMS_VALUE_NUMERIC numeric(19,2),
bp_type VARCHAR(4),
Dt datetime ,
ID int IDENTITY(1,1),
MBP numeric(19,2),
MPOG_PHYSIOLOGIC_CONCEPT_ID VARCHAR(10)
UNIQUE CLUSTERED (MPOG_PHYSIOLOGIC_CONCEPT_ID,Dt)
)
IT works in SQL Sevrer 2012

Sure that works. That is why I asked if that combination of columns is unique. If it isn't there are some other tricks you can do to make it work. I would point out that the two columns you posted here do NOT match the two columns you stated you wanted your index on originally. As such your performance benefits may be nil since the leading edge of your index is a different column.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

mw112009 (6/23/2015)
CREATE TYPE BP_Data_ACRC_427_B
as table
(
AIMS_VALUE_NUMERIC numeric(19,2),
bp_type VARCHAR(4),
Dt datetime ,
ID int IDENTITY(1,1),
MBP numeric(19,2),
MPOG_PHYSIOLOGIC_CONCEPT_ID VARCHAR(10)
UNIQUE CLUSTERED (MPOG_PHYSIOLOGIC_CONCEPT_ID,Dt)
)
IT works in SQL Sevrer 2012

Yes, it does. If you read what Sean wrote and look at your code above you will see just what he said, a UNIQUE constraint on the columns MPOG_PHYSIOLOGIC_CONCEPT_ID, Dt. The constraint is supported by an index, in this case a clustered index.

C. Westra SSC Enthusiast Points: 156 More actions · Answer 7

Although this is an old post, it shows up as first reply on my search in Google.

Answers above, that it's impossible to create nonclustered indexes in a tabletype are outdated.

As of Sql Server 2016 one can create indexes in a tabletype and in table variables.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql?f1url=%3FappId%3DDev14IDEF1%26l%3DEN-US%26k%3Dk(Create_Type_TSQL);k(sql13.swb.tsqlresults.f1);k(sql13.swb.tsqlquery.f1);k(DevLang-TSQL)%26rd%3Dtrue&view=sql-server-ver15

Create Type MyTable as Table(
Col1 int Primary Key,
Col2 nvarchar(20),
Index Ix_Col2 Nonclustered (Col2));

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Awesome! Thanks for the update!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)