Some thoughts on making a primary key with this format: AL-MMDDYYYY-1.
When "AL-" occurs in every column, drop it. It is redundant.
"MMDDYYYY" that is a standard NOT NULL DATE
When "-" occurs in every column, drop it. It is redundant.
"1" restarting every 24 hours. Why is that? If it would start from value 256 at 00:00:00 the next day, and 1346 a day later, what would be the problem?
When 4 is no problem, the number may continue, you can simply revert to using an identity column:
CREATE TABLE DaTable(
Id BIGINT IDENTITY(1, 1) NOT NULL,
DaDate DATE NOT NULL DEFAULT GetDate(),
PkDisplay AS ('AL-' + REPLACE(CONVERT(VARCHAR(10), DaDate, 101), '/', '') + '-' + CAST(Id as VARCHAR(20))) PERSISTED,
CONSTRAINT pk_DaTable PRIMARY KEY (Id)
PkDisplay is a calculated column. The value is calculated once and stored in the table (by specifying "PERSISTED"). When you do not use PERSISTED, your table will take less space, but you won't be able to create an index on it.
The real primary key is Id, but you never show it, you use PkDisplay instead.
Last edited by Wim; 03-14-11 at 06:37.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages