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

Identity columns

Hi,
Quick question, Can I use the 'Alter table mytable alter column' to
change the Identity column to include the 'not for replication' clause? Or
do I need to drop and recreated the table?
thanks,
Nope, but you can use this:
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0
go
sp_configure 'allow updates', 0
this will fix your identity keys on all of your tables for the not for
replication switch
If you want to limit it to a set of tables you are best to do this
sp_configure 'allow_updates', 1
go
reconfigure with override
go
update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
colstat & 0x0008 =0 and id=object_id('tablename')
go
sp_configure 'allow updates', 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Joe" <jkdriscoll@.qg.com> wrote in message
news:d2hjin$2kfu$1@.sxnews1.qg.com...
> Hi,
> Quick question, Can I use the 'Alter table mytable alter column' to
> change the Identity column to include the 'not for replication' clause? Or
> do I need to drop and recreated the table?
> thanks,
>
|||Hilary,
Thank you for the response. I can't begin to tell you how much work this
will save me. We've had this one SQL Server, that is production and was
originally only used a little bit. But, as you can guess, over time it's
grown in use. Now we need to set up replication. And what a project this will
be. This server now as 23 production databases, none of which where designed
with replication in mind.
So, once again I say Thanks.
Joe.
"Hilary Cotter" wrote:

> Nope, but you can use this:
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0
> go
> sp_configure 'allow updates', 0
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do this
>
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||I have thought of a follow up question.
Will this sql affect any current or future Identity column values?
thanks again.
Joe
"Hilary Cotter" wrote:

> Nope, but you can use this:
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0
> go
> sp_configure 'allow updates', 0
>
> this will fix your identity keys on all of your tables for the not for
> replication switch
>
> If you want to limit it to a set of tables you are best to do this
>
> sp_configure 'allow_updates', 1
> go
> reconfigure with override
> go
> update syscolumns set colstat=colstat|0x0008 where colstat & 0x0001 <> 0 and
> colstat & 0x0008 =0 and id=object_id('tablename')
> go
> sp_configure 'allow updates', 0
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Joe" <jkdriscoll@.qg.com> wrote in message
> news:d2hjin$2kfu$1@.sxnews1.qg.com...
>
>
|||no it won't.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JD" <John316@.online.nospam> wrote in message
news:3715E8B9-22F3-40EB-BDBF-09036E45A71D@.microsoft.com...[vbcol=seagreen]
> I have thought of a follow up question.
> Will this sql affect any current or future Identity column values?
> thanks again.
> Joe
> "Hilary Cotter" wrote:
and[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
clause? Or[vbcol=seagreen]

IDENTITY Column!

Hi all,
I have noticed when i export data, that any resulting tables created don't
seem to include the IDENTITY attribute on the necessary columns.
These attributes are defined in the source tables, but don't carry over to
tables resulting from an export.
Can someone fill me in on what is happening there?
Is there something i could be missing?
Cheers,
AdamAdam
create table source
(
col1 int identity(1,1) not null primary key,
col2 char(1)
)
insert into source (col2) values ('a')
--create a destination table
select * into destination from source
--you will see that an identity property exists but primary key constraint
doesn't
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:exS9CWHAGHA.3372@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have noticed when i export data, that any resulting tables created don't
> seem to include the IDENTITY attribute on the necessary columns.
> These attributes are defined in the source tables, but don't carry over to
> tables resulting from an export.
> Can someone fill me in on what is happening there?
> Is there something i could be missing?
> Cheers,
> Adam
>|||By which method do you export data?|||By which method do you export data?