problem in inserting data into table in 24-073110-XX format ????.....

Question

I need help insering an id into the database in ASP.net mvc (C#). Here theid is the primary key and it should be in the format 24-073110-XX, where XX represents a numeric value which should be incremented by 1.

How should I insert the id in this format?

Answer 1

If the 24-073110- part of the data is always going to be the same, there's little to no point in storing it in the database. Given that you've said that the XX component is a numeric value that increments by one, I'd suggest having your table created similarly to this:

CREATE TABLE [dbo].[MyTable]
(
  MyTableId INT IDENTITY(1,1) NOT NULL,
  /*
      Other columns go here
  */
)

This way, you can let the database worry about inserting unique automatically incrementing values for your primary key.

Answer 2

As Rob said - don't store the whole big identifier in your table - just store the part that changes - the consecutive number.

If you really need that whole identifier in your table, e.g. for displaying it, you could use a computed column:

ALTER TABLE dbo.MyTable
  ADD DisplayID AS '24-073110-' + RIGHT('00' + CAST(ID AS VARCHAR(2)), 2) PERSISTED

This way, your INT IDENTITY will be used as an INT and always contains the numerical value, and it will be automatically incremented by SQL Server.

Your DisplayID field will then contain values like:

ID  DisplayID
 1  24-073110-01
 2  24-073110-02

12  24-073110-12
13  24-073110-03

21  24-073110-21

Since it's a persisted field, it's now part of your table, and you can query on it, and even put an index on it to make queries faster:

SELECT (fields) FROM dbo.MyTable WHERE DisplayID = '24-073110-59'

Update:

• I would definitely not use DisplayID as your primary key - that's what the ID IDENTITY column is great for

• to create an index on DisplayID is no different than creating an index on any other column in your table, really:

  CREATE NONCLUSTERED INDEX SomeIndex ON dbo.MyTable(DisplayID)