Creating custom Primary Key in SQL Server 2005

[AMD_GAMER]

I currently have a website which is using ASP.NET 2.0, C#, and SQL
Server 2005. The website will be used to enter grants for a
university. When a new grant is entered, I need to generate a primary
key. The primary key will need to follow the format: Two digit for
fiscal year, then number of the grant for that year. Example:

Year 08 and 14th grant of the year would be: 0814

How can I implement this. Right now, I have a "New Grant.aspx" page
with a Submit button. I am guessing the date is going to be formatted
in C#. How can I check what the last primary key in the database is?
Also, it seems to me that SQL Server insists that the primary key be
32 bits long, however my primary key will only be 4. How can I
override this? Thanks.

[Erland Sommarskog]

AMD_GAMER (am********@gmail.com) writes:

I currently have a website which is using ASP.NET 2.0, C#, and SQL
Server 2005. The website will be used to enter grants for a
university. When a new grant is entered, I need to generate a primary
key. The primary key will need to follow the format: Two digit for
fiscal year, then number of the grant for that year. Example:

Year 08 and 14th grant of the year would be: 0814

How can I implement this. Right now, I have a "New Grant.aspx" page
with a Submit button. I am guessing the date is going to be formatted
in C#. How can I check what the last primary key in the database is?
Also, it seems to me that SQL Server insists that the primary key be
32 bits long, however my primary key will only be 4. How can I
override this? Thanks.

SQL Server accept any length of the PK up to 900 bytes.

0814 appears a tad short to me. What if there are more than 99 grants
in one year?

It would be better to make the PK a two-column key:

Year char(4) NOT NULL,
grantno int NOT NULL,

You find the new grantno with:

BEGIN TRANSACTION

SELECT @nextgrantno = coalesce(MAX(granttno), 0) + 1
FROM tbl WITH (UPDLOCK)
WHERE Year = @year

INSERT ...

For display you add a computed column:

displaykey as substring(Year, 3, 2) +
right(ltrim(str(grantno + 100))), 2) PERSISTED UNIQUE

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[AMD_GAMER]

Ok, so I should use the 32 bit auto-generated key as the SQL PK, and
then use my own calculated GrantID just as an identifier.

[--CELKO--]

>I should use the 32 bit auto-generated key as the SQL PK, and then use my own calculated GrantID just as an identifier. <<

No. You ought to visit the accounting department and find out what
your university uses for a grant identifier. Cowboy coders love to re-
invent the wheel and avoid research; professional actually gather
specs BEFORE they code anything.