problem creating autoincremental column

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

SQL Server 2005 Forums

Transact-SQL (2005)

problem creating autoincremental column

Author

Topic

sateesh.sqldba
Starting Member

25 Posts

Posted - 2010-07-21 : 07:51:27

Hi experts,

i want to create column like this data

col1

custmer00001
custmer00002
custmer00003
|
|
|
|
customer10000
that must be in auto incrmental

thanq so much in advance

sateesh

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-07-21 : 08:38:52

You can use identity column as a base for another computed column that produce the output you want. Something like this:


CREATE TABLE T1
(
   id SMALLINT NOT NULL IDENTITY,
   col1 AS 'customer' + RIGHT('00000' + CAST(id AS VARCHAR(5)), 5)
);

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-21 : 10:12:32

quote:
Originally posted by malpashaa

You can use identity column as a base for another computed column that produce the output you want. Something like this:

CREATE TABLE T1
(
id SMALLINT NOT NULL IDENTITY,
col1 AS 'customer' + RIGHT('00000' + CAST(id AS VARCHAR(5)), 5)
);

There is no need to specify NOT NULL for id column as it is a Identity column

Madhivanan

Failing to plan is Planning to fail

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-07-21 : 11:06:41

quote:

There is no need to specify NOT NULL for id column as it is a Identity column

Madhivanan

You are right. But I prefer to include the constraint even if it is redundant. By the way SQL Server Management Studio also includes the constraint in creation script.

Kristen
Test

22859 Posts

Posted - 2010-07-21 : 12:43:12

"There is no need to specify NOT NULL for id column as it is a Identity column"

Required if it is a PK column?

Possibly will be used as a hint to Query Optimiser?

(Dunno the answer to either, just thinking out loud

)

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-07-21 : 13:46:25

quote:
Originally posted by Kristen

"There is no need to specify NOT NULL for id column as it is a Identity column"

Required if it is a PK column?

Possibly will be used as a hint to Query Optimiser?

(Dunno the answer to either, just thinking out loud )

Actually IDENTITY attribute is not allowed on a nullable column. So when you do not constrain the column with NOT NULL constraint, the constraint will be added automatically behind the scene. That is why I prefer to make it crystal clear, and add the NOT NULL constraint manually.

Kristen
Test

22859 Posts

Posted - 2010-07-21 : 18:26:44

I do that too. I didn't know I didn't need to but, like you, I explicitly do such things anyway for the avoidance of doubt / during maintenance / by others.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-21 : 18:28:59

I prefer to add NOT NULL too. Makes it easier when I'm scanning a long table for null and not null columns.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sateesh.sqldba
Starting Member

25 Posts

Posted - 2010-07-21 : 23:17:08

Thank you so much madhivanan

quote:
Originally posted by madhivanan

quote:
Originally posted by malpashaa

You can use identity column as a base for another computed column that produce the output you want. Something like this:

CREATE TABLE T1
(
id SMALLINT NOT NULL IDENTITY,
col1 AS 'customer' + RIGHT('00000' + CAST(id AS VARCHAR(5)), 5)
);

There is no need to specify NOT NULL for id column as it is a Identity column

Madhivanan

Failing to plan is Planning to fail

sateesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources