Creating custom Primary Key in SQL Server 2005

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • AMD_GAMER
    #1

    Creating custom Primary Key in SQL Server 2005

    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.
    Tags: None
    • Erland Sommarskog
      #2
      Re: Creating custom Primary Key in SQL Server 2005

      AMD_GAMER (amdgamer18@gma il.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(gr anttno), 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, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • AMD_GAMER
          #3
          Re: Creating custom Primary Key in SQL Server 2005

          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.

            Comment

            • --CELKO--
              #4
              Re: Creating custom Primary Key in SQL Server 2005

              >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.

                Comment

                Previous template Next
                Working...