8

Firstly, I understand the role of Primary Keys on tables in SQL, or any database for that matter.

However, when it comes to user-defined table types, I find myself unable to see why I should create a Primary Key. In my experience I've found no performance benefit from them, but I suspect my findings maybe come from my general usage of them and limited experience, rather than a more experienced view-point.

For example, I generally use table-valued parameters for insert / update procedures, or to pass an array of values used in a creating a select query. I've found no performance benefit from them and if anything I've experienced a small performance drop (but we're talking 10's of milliseconds at the most so its pretty negligible).

I should disclose that any data passed within them has already been cleaned / ordered with great care, so it is possible I'm not seeing benefit due to this but I have no way of easily knowing.

The question again therefore is, should I have a primary key on my user-defined table types or is it just not that important?

Apologies if this is a duplicate. I did a fair amount of searching, but then I could be using wrong combination of keywords.

Your insights as ever will be greatly appreciated.

Alexander
  • 3,129
  • 2
  • 19
  • 33
Jon Bellamy
  • 3,333
  • 20
  • 23
  • 2
    You get the small performance drop because the clustered index has to be constructed. If the data is pre-ordered and you will not be performing selective queries on the data then the index is just a waste of resources. If you wan't to impose a new order on the set or repeatedley call some selective query on the set, that will benefit from the index and, if the set is suffciently large then it would be worth creating the clustered index and potentially other non clustered indecies. – Jodrell Feb 27 '14 at 11:25
  • @Jodrell, Thanks, that makes a lot of sense. For some crazy reason, I hadn't considered the overhead of constructing the clustered index!!! It's also good to know that my assumptions on the data being pre-ordered were accurate. – Jon Bellamy Feb 27 '14 at 12:35

3 Answers3

-1

If the array you're passing is an array of ids to match directly on join with other table it makes all sense having a clustered primary key.

To avoid spending time ordering data prior to insert, you should have it already ordered on application side.

In this kind of cases I've experienced gains of about 400ms.

It has to be evaluated case by case.

Paulo Correia
  • 83
  • 1
  • 7
-1

Actually, I was curious about the same thing but from a different angle...

We had a dynamic query that was being put together from user inputs resulting in a query like

"SELECT A.* 
FROM A
<some joins, etc>
WHERE A.ID IN ('" + String.Join("','", List<UserInputs>) + "')"

I wanted to close the sql injection hole with a tvp but the query performance went totally down the toilet (orders of magnitude slower).

The plan with the literals started with a nice, clean index scan down the id column and was very quick.

The plan with the TVP got all kinds of convoluted with the join to the tvp and materializing the whole table before getting to the selectivity part.

I was wondering if putting a primary key on the tvp type might get the optimizer to generate a better plan.

user1664043
  • 695
  • 5
  • 14
-3

You can create primary key on the user defined table types,which results the formation of the clustered index.But it is not possible to create a non clustered index on user defined table type..

For your information please see the below page

http://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx

user1254579
  • 3,901
  • 21
  • 65
  • 104
  • Thanks. I've spent a lot of time looking at that particular page beforehand. Whilst I understand all of that, in this case it doesn't really answer the should I / shouldn't I conundrum I was experiencing. – Jon Bellamy Feb 27 '14 at 12:38