How to create a primary key as A1/D-M/100000 in SQL Server 2005?

How to create a primary key as A1/D-M/100000 in SQL Server 2005 in Visual Studio开发者_StackOverflow社区 2008 ?

Use varchar(20) as column type and generate the primary key value from the code.

Your request is not possible in SQL Server as requested, which makes me want to tell you to do some more reading.

However, you can achieve something similar by creating a primary key in Visual Basic, and storing that value in a char or varchar field in SQL, as suggested by Adrian Godong's answer.

That said, what I would do if this were my database, is create a normal PK using int or bigint (depending on how many rows I'm planning to store), and using a second column as char or varchar, with an appropriate index, to store the Ax/D-M/xxxxxx values.

What you are wanting to do is mix business rules with SQL Server's database rules, and that's a very bad idea. SQL does not care what your data looks like, so you should not force it to do so. Your business rules may change down the line, and this would be much easier to accommodate if you do a proper PK to start with, that does not rely on some arbitrary naming convention.

What parts of that key are fixed, which change from row to row??

E.g. where does the "A1" or the "D-M" come from? Are they the same for all rows? Do they change from row to row??

If those parts are fixed, and only the big number in the middle needs to change, you could:

• define a column of type ID INT IDENTITY(100000,1) for the number

• define a computed column like this:

  ALTER TABLE dbo.YourTable
     ADD YourPKField AS 'A1/D-M/' + CAST(ID AS VARCHAR(6)) PERSISTED
  

In that way, SQL Server would auto-increment your ID field, and YourPKField would contain the values:

A1/D-M/100000
A1/D-M/100001
A1/D-M/100002
A1/D-M/100003
....

and so on - automatically, without you doing anything more.

Use an INT IDENTITY(100000,1) as your Primary key.
Add the calculated (displayed) key wherever you need it (queries...). It's decoration, and as such, it's part of the front-end, not of your data.