1

I'm trying to add a clustered index to a user defined table type. Not a PK, just a clustered index. I see in the documentation that I cannot add a NON CLUSTERED index to a user defined table type, so sounds like it should be possible, but I can't figure out how.

Note: talking about a user defined table type, not a table variable or temp table

Mike Turner
  • 471
  • 1
  • 7
  • 22
  • Please replace the `non-clustered-index` tag with the relevant rdbms tag. – Zohar Peled May 04 '17 at 15:59
  • which version of sql server are you using? – SqlZim May 04 '17 at 16:01
  • @SqlZim version is 2008 – Mike Turner May 04 '17 at 16:05
  • I don't think it's possible to create a non unique index on a user defined table type. However, There is a simple workaround. – Zohar Peled May 04 '17 at 16:10
  • what are you referring to? – Mike Turner May 04 '17 at 16:12
  • 3
    Everything here applies to table types too. http://stackoverflow.com/a/17385085/73226 – Martin Smith May 04 '17 at 16:14
  • Now Im really confused bec the following seems to work... and documentation says you cannot create non-clustered index on UDTable Type: CREATE TYPE [dbo].[TempIdxType] AS TABLE( [intColumn] [INT] NULL, UNIQUE NONCLUSTERED(intColumn) ) GO – Mike Turner May 04 '17 at 16:20
  • You're creating a unique constraint. That is backed up by a nonclustered index. SQL Server 2014 allows you to create indexes not associated with constraints in table types. – Martin Smith May 04 '17 at 16:37
  • 1
    what abt a non unique clustered/non clustered? – Mike Turner May 04 '17 at 16:45
  • Did you read the link? Doesn't seem like it because the answer is there. – Martin Smith May 04 '17 at 16:47
  • it's better to say "table variable" rather than "table type" – FLICKER May 04 '17 at 16:49
  • @FLICKER they are not the same thing. Though instances of table types are table variables so the both have exactly the same limitations. – Martin Smith May 04 '17 at 16:52
  • @MartinSmith, I was not aware of such a thing. Thanks for mentioning. – FLICKER May 04 '17 at 16:54
  • 1
    @MartinSmith a very good detailed answer. Why not mark as duplicate? (btw, I'm glad to find out the workaround I thoght of was described in your answer) – Zohar Peled May 04 '17 at 17:06
  • @MartinSmith I don't think you're correct. Your link says you can create a Non Unique index on a table variable - try doing that on a table type - does not seem to work. I would not mark this as a duplicate and try to answer the question –  May 04 '17 at 17:52
  • @SOS the answer is specific about versions. That works fine in 2014 table types. http://rextester.com/WPCZ35425. The OP here will need to read th bit specific to their version. – Martin Smith May 04 '17 at 17:59
  • 2
    @MartinSmith ok thx. Personally, I would not mark this as a duplicate. MikeTurner is asking abt table types, and your answer is that the rules of table variable apply to table types too, I would use that as an answer to this question. Question is no way a dupe... But no real diff to me –  May 04 '17 at 18:16

0 Answers0