identity seed questions

My client has a need for the auto identity field to be 6 digits in length starting with the number 001000. They want the leading 0's preserved since this will be a casenumber. Even if I set the identity seed to 001000 it gets rid of the leading 0's. How can I get it to keep those?

Since the identity property works on numeric column leading zeroes doesn't matter. You can however use an identity column with seed 1000 and then use a computed column that formats the value in the desired format. See example below:

create table #t ( i int identity(1000, 1) not null check(i between 1000 and 999999), inum as right(replicate('0', 6) + cast(i as varchar), 6))

insert into #t default values

select * from #t

drop table #t

Identity Field plus leading characters

I was wondering if the identity collumn of a table can include the same
first 3 characters. All I want to do is add DMC to the front. If I cant
do this in the database, I guess I could just append it to the data when
displaying it. But if this is possible I could really do with using it.
Thanks in advance!
SteveWhy don't you use a view:
CREATE VIEW dbo.v_foo
AS
SELECT idColumn = 'DMC'+RTRIM(fooID) FROM dbo.foo
Or a computed column:
CREATE TABLE dbo.foo
(
fooID INT IDENTITY(1,1),
idColumn AS CONVERT(VARCHAR(12),'DMC'+RTRIM(fooID))
)
"Dooza" <steve@.dont.spam.me.dooza.tv> wrote in message
news:%23CwbtDvsFHA.1256@.TK2MSFTNGP09.phx.gbl...
>I was wondering if the identity collumn of a table can include the same
>first 3 characters. All I want to do is add DMC to the front. If I cant do
>this in the database, I guess I could just append it to the data when
>displaying it. But if this is possible I could really do with using it.
> Thanks in advance!
> Steve|||It is doable, but it does not mean it is helpful. You will be wasting disk
space.
create table t1 (
c1 int not null identity unique,
c2 as 'DMC' + ltrim(c1)
)
insert into t1 default values
insert into t1 default values
insert into t1 default values
select * from t1
drop table t1
go
AMB
"Dooza" wrote:

> I was wondering if the identity collumn of a table can include the same
> first 3 characters. All I want to do is add DMC to the front. If I cant
> do this in the database, I guess I could just append it to the data when
> displaying it. But if this is possible I could really do with using it.
> Thanks in advance!
> Steve
>|||Hi Aaron,
Thats not a bad idea. Thanks for your help.
Steve
Aaron Bertrand [SQL Server MVP] wrote:
> Why don't you use a view:
> CREATE VIEW dbo.v_foo
> AS
> SELECT idColumn = 'DMC'+RTRIM(fooID) FROM dbo.foo
> Or a computed column:
> CREATE TABLE dbo.foo
> (
> fooID INT IDENTITY(1,1),
> idColumn AS CONVERT(VARCHAR(12),'DMC'+RTRIM(fooID))
> )
>
>
> "Dooza" <steve@.dont.spam.me.dooza.tv> wrote in message
> news:%23CwbtDvsFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>
>|||Hi there,
How much wasted space are we talking about here? I dont think I will be
having more than 5000 records.
Steve
Alejandro Mesa wrote:
> It is doable, but it does not mean it is helpful. You will be wasting disk
> space.
> create table t1 (
> c1 int not null identity unique,
> c2 as 'DMC' + ltrim(c1)
> )
> insert into t1 default values
> insert into t1 default values
> insert into t1 default values
> select * from t1
> drop table t1
> go
>
> AMB
> "Dooza" wrote:
>|||> How much wasted space are we talking about here? I dont think I will be
> having more than 5000 records.
Well, that's really not the point, I suppose. I see no reason to store
'DMC' in the table at all, if *every signle row* will always be prefixed by
it. This sounds more like a job for the presentation layer or, as I
previously suggested, a view. Taking more disk space than necessary is a
subjective thing, but in general, I think it is a mistake to do it when it
buys you nothing, such as in this case.
Driving 120 mph is dangerous, but how dangerous is it really? Not very, so
long as you don't hit something. :-)|||Aaron Bertrand [SQL Server MVP] wrote:
>
> Well, that's really not the point, I suppose. I see no reason to store
> 'DMC' in the table at all, if *every signle row* will always be prefixed b
y
> it. This sounds more like a job for the presentation layer or, as I
> previously suggested, a view. Taking more disk space than necessary is a
> subjective thing, but in general, I think it is a mistake to do it when it
> buys you nothing, such as in this case.
> Driving 120 mph is dangerous, but how dangerous is it really? Not very, s
o
> long as you don't hit something. :-)
I completely agree with you. I will do as you suggest, and use a view.
Thanks for your help!
Steve|||Dooza wrote:
> Aaron Bertrand [SQL Server MVP] wrote:
>
>
> I completely agree with you. I will do as you suggest, and use a view.
> Thanks for your help!
> Steve
One extra thing I have thought about. Say I wanted the ID to start with
DMC0001, the first ID is going to be 1, not 0001. How can I do this?
Steve|||> One extra thing I have thought about. Say I wanted the ID to start with
> DMC0001, the first ID is going to be 1, not 0001. How can I do this?
'DCM'+RIGHT('0000'+RTRIM(col_name),4)|||> One extra thing I have thought about. Say I wanted the ID to start with
> DMC0001, the first ID is going to be 1, not 0001. How can I do this?
And just as a warning, you said before that you would only have 5000 rows,
however please remember that an IDENTITY column is prone to gaps
(transaction rollbacks, deletes) so I'm not so sure I agree that reserving
only 4 digits is a wise choice. Or, at least, I hope you are prepared to
re-work everything when you need to make more room.
A