Question: Use a character with sequential no? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Question: Use a character with sequential no?

Author

Topic

jappa
Starting Member

6 Posts

Posted - 2004-05-26 : 18:47:40

This is a pretty newbie question. I'm just wondering if I can use a auto-generated (auto increment) field started with a character in MSSQL, e.g. B000323, B000324, etc. I'm developing an application using .NET, and I know I can do this by incrementing it from the code, but I wanted to check first if there's a faster way to do it from the database instead. Any help would be greatly appreciated.

Thanks,
Jap

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 19:11:30

There is no way to auto-increment this type of column. You'll have to build one, preferably in a stored procedure.

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-05-26 : 19:30:44

Create an identity column, then create a computed column that appends the character and does some padding and then place a key on the computed column. Create a view of the table the "hides" the identity column and work exclusively with the view.

DavidM

"Always pre-heat the oven"

jappa
Starting Member

6 Posts

Posted - 2004-05-26 : 19:37:36

If that's the case, the second alternative I was thinking of, is there any way to use leading zeros for auto-incrementing field? Because leading zeros is required for consistency of the 7-character field.

Thanks,
Jap

quote:
Originally posted by byrmol

Create an identity column, then create a computed column that appends the character and does some padding and then place a key on the computed column. Create a view of the table the "hides" the identity column and work exclusively with the view.

DavidM

"Always pre-heat the oven"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-26 : 19:40:19

No. Why don't you start the IDENTITY at 1000000?

CREATE TABLE Table1
(Column1 INT IDENTITY(1000000, 1) NOT NULL, Column2 VARCHAR(20))

INSERT INTO Table1 (Column2)
SELECT 'SQLTeam'
UNION ALL
SELECT 'Tara'

SELECT Column1, Column2
FROM Table1

DROP TABLE Table1

Tara

jappa
Starting Member

6 Posts

Posted - 2004-05-26 : 19:46:28

That's a good idea.. never thought about that.

Thanks a lot!

Jap

quote:
Originally posted by tduggan

No. Why don't you start the IDENTITY at 1000000?

CREATE TABLE Table1
(Column1 INT IDENTITY(1000000, 1) NOT NULL, Column2 VARCHAR(20))

INSERT INTO Table1 (Column2)
SELECT 'SQLTeam'
UNION ALL
SELECT 'Tara'

SELECT Column1, Column2
FROM Table1

DROP TABLE Table1

Tara

Kristen
Test

22859 Posts

Posted - 2004-05-27 : 06:45:59

quote:
Originally posted by jappa

is there any way to use leading zeros for auto-incrementing field? Because leading zeros is required for consistency of the 7-character field.

IDENTITY is an INTEGER field, so no leading zeros.

However, if you convert to STRING you can have leading zeros:

SELECT RIGHT('0000000' + CONVERT(varchar(20), MyIdentityColumn), 7)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources