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