identity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

identity

Author

Topic

vishu.av
Starting Member

26 Posts

Posted - 2007-04-28 : 06:17:54

Hi All,
Is there way that i can make a column of varchar type in a table
as IDENTITY?
Is there any other alternative?

SQL server allows only int, bigint, smallint, tinyint, or decimal or numeric to be identity types

I have a column whose value may be like below
SLNO00001
The next time i insert a column i wish to make this column as
SLNO00002 with new row added..

Thanks in advance..

vishu
Bangalore

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-28 : 06:24:26

see http://www.sqlteam.com/item.asp?ItemID=26939

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-28 : 12:33:43

Just create an IDENTITY column like you normally would and also create a "calculated column" to do the conversion to the desired format. Anything else will lead to a hotspot in the database whether it be in the original table or a sequence table. For example...

 CREATE TABLE yourtable 
        (
        ID INT IDENTITY(1,1) NOT NULL, 
        Somestring VARCHAR(10), 
        SerialNumber AS 'SLNO'+RIGHT('00000'+CAST(ID AS VARCHAR(5)),5)
        )

 CREATE UNIQUE 
  INDEX SomeIDXName ON yourtable (SerialNumber)

The purpose of the index is to give you some chance at creating foreign keys to the table... I don't believe you can create a Primary Key on a calculated column because it won't allow a NOT NULL constraint on the column.

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-28 : 14:45:43

Ok... also figured out how to make the calculated column a Primary Key, if that's what you need...

DROP TABLE yourtable
GO
CREATE TABLE yourtable 
        (
        ID INT IDENTITY(1,1) NOT NULL, 
        Somestring VARCHAR(10), 
        SerialNumber AS ISNULL('SLNO'+RIGHT('00000'+CAST(ID AS VARCHAR(5)),5),'xxx') PRIMARY KEY
        )

There is simpler code using REPLACE and STR for the calculated column, but that will make the column "non-deterministic or imprecise" and won't allow either an index or a key.

--Jeff Moden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-28 : 16:11:09

or, just handle it in the front end. ids with text in them are to be avoided, imo.

www.elsasoft.org

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-28 : 19:59:20

Actually, both methods stink, in this case vishu... you've severely limited the content of the table to a mere 99,999 items, then BOOOOMMM!

--Jeff Moden

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-04-30 : 04:53:56

I'm not sure if it was an example, but neither method guarantees to give SLNO00002 after SLNO00001.

I don't want to bring up the old 'why not? that's what I must have' vs 'that is impossible/slow/not scalable why do you want that anyway' arguments but you need to consider it.

Generally this stuff is better in the front end, although the calculated field may have merits.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-30 : 08:55:45

All of these options and considerations and the pros and cons of each are discussed and laid out in the link provided by khtan.....

(yes, that was a shameless plug since I wrote the article!!

)

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources