Composite PK/Surrogate and Sequence/Identity questions.

Question

Composite PK/Surrogate and Sequence/Identity questions.

mili.nikolic

Right there with Babe

Points: 729
More actions
June 21, 2015 at 9:54 am

#398627

So, I have some questions about best practice in SQL Server.
1.) I have PK like this (company TINYINT, store TINYINT, action TINYINT, invoice INT, sn SMALLINT). I know JOINS will work faster with surrogate key but I have only couple of JOINS on that table. I use members of PK in WHERE clause mainly, alone and combined for reporting purpose. Is it always better to have surrogate key because they don't have any meaning and context of data laying in current PK. Basically my question would be when to use surrogate key and be confident that I make right choice.
2.) In my PK from above I have two candidates for using Sequence object. Invoice start with 1 for every (company,store,action) combination. Sn start with 1 for every (company,store,action,invoice) combination. I would like to know can I implement Sequence object here knowing that Sequence don't support PARTITION BY in OVER clause. From what I red it cannot be done via Sequence but I have to ask.
Here is data sample for this PK
company store action invoice sn
----------- ----------- ----------- ----------- -----------
1 1 1 2017 1
1 1 1 2018 1
1 1 1 2019 1
1 1 1 2019 2
1 1 1 2019 3
1 1 2 1 1
1 1 2 2 1
1 1 2 2 2
1 1 2 2 3
1 1 2 3 1
1 1 2 3 2
1 1 2 3 3
1 1 2 3 4
1 1 2 3 5
Sorry about english, not my native.

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

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

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 1

Your English is just fine. Don't worry about it. As far as your question:

1. There is a debate within the database community on using surrogate keys in an OLTP database. Some advocate using them all the time primarly for the performance gains. Others advocate using natural keys when they are available primarily to prevent duplicate entries. I prefer natural keys when a good choice is available. I rarely find performance is an issue. I've also seen tables keyed by surrogate that have duplicates.

2. I don't know first hand that SEQUENCE has the limitation you mention. I would recommend you use the tables inherent IDENTITY capability unless you need the key to be unique across tables. SEQUENCE is ideal for this.

If this doesn't give you enough information to keep going, please rephrase the question in a different way to see if that helps clarify it.

mili.nikolic Right there with Babe Points: 729 More actions · Answer 2

For surrogate key I was aware it's more point of view to use it or not. Your answer is very good.

I didn't see anywhere example of sequence object in composite PK. If you can provide link or explanation I would appreciate it very much.

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 3

I didn't see anywhere example of sequence object in composite PK

The sequence by its nature would not need to be part of a composite key. It will be unique across the database. If you're using it, that should be the only field in the PK. If you are having an issue with a foreign key (FK) reference to a SEQUENCE field, that would be an unexpected issue. Please clarify if this is the case.

Thanks,

mili.nikolic Right there with Babe Points: 729 More actions · Answer 4

RonKyle (6/22/2015)
I didn't see anywhere example of sequence object in composite PK
The sequence by its nature would not need to be part of a composite key. It will be unique across the database. If you're using it, that should be the only field in the PK. If you are having an issue with a foreign key (FK) reference to a SEQUENCE field, that would be an unexpected issue. Please clarify if this is the case.
Thanks,

I know it is unique across the database, also I know it doesnt have to be part of composite key. No, its not issue with FK.

I found explanation and solution, there is no easy solution in my case. My question is exactly like this one below and solution is provided, so I am clear now.

http://stackoverflow.com/questions/24184749/sql-server-unique-composite-key-of-two-field-with-second-field-auto-increment

Thank you very much RonKyle! 🙂

drew.allen SSC Guru Points: 76944 More actions · Answer 5

RonKyle (6/22/2015)
I didn't see anywhere example of sequence object in composite PK
The sequence by its nature would not need to be part of a composite key. It will be unique across the database. If you're using it, that should be the only field in the PK. If you are having an issue with a foreign key (FK) reference to a SEQUENCE field, that would be an unexpected issue. Please clarify if this is the case.
Thanks,

That's not necessarily true for two reasons: sequences can cycle and sequence fields can be edited.

The following is quoted from https://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx

Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. To prevent sequence values from being changed, use an update trigger on the table to roll back changes.
Uniqueness is not automatically enforced for sequence values. The ability to reuse sequence values is by design. If sequence values in a table are required to be unique, create a unique index on the column. If sequence values in a table are required to be unique throughout a group of tables, create triggers to prevent duplicates caused by update statements or sequence number cycling.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mili.nikolic Right there with Babe Points: 729 More actions · Answer 6

Its ok, I learned that SEQUENCE has many more possibilities then IDENTITY but in my case provided link from stackoverflow is best solution there is.

thank you both for your contribution.

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 7

It may be possible to recyle a SEQUENCE number, but IDENTITYs can be reseeded. If you want to mess something up, it's always possible.