Automatically Defining Primary keys

Hi,

I am new to SQL Server platform, i want to define primary keys automatically other than using an identity. For a table called indicator i want it primary keys to be like ind_001, ind_002, ind_003 and so on.

Can anybody help me on how to do this? I am new to this platform so i will appreciate it if suggestions are explained very clearly. Thank you

You will either have implement this in your fronent logic or use triggers to reset the values inserted to the appropiate pattern.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Auto-Increment of varchar primary key

Hi All
I am looking for a bit of advice.
I am in the process of creating a database in which it has been decided that
all primary keys are going to varchar(40). Not my decision, but anyway.
When inserting into each table it will be possible to specify a value for
the primary, but if not specified a value should be auto-generated. That
means that the values in the primary key field can be a mixture of both
numbers and letters, but if auto-generated it should just be a number.
What be the best way to make this autogenerated values if no value is being
specified in the insert?
TIA
KlausDepends really, if auto-generated does it just need to be a number? Any old
number, or a specific format and range?
I always recommend putting a surrogate key on the tables and use that as the
foriegn key and inside the application (not for display purposes, but for
use as the value in a listbox for instance), that can be a int column with
the IDENTITY property, not null and have a unique constraint on it.
You could set the value of the primary key to that if not specified, that
would save calculating a new unique number.
Otherwise, you could use an 'instead of' trigger, for example...
Instead of using MAX, you could take the value from a table that holds the
last number used.
create table testtrg (
mycol int not null unique
)
go
insert testtrg ( mycol ) values ( 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
declare @.nextid int
begin tran
set @.nextid = ( select max( mycol )
from testtrg with (tablockx) )
set @.nextid = isnull( @.nextid, 0 ) + 1
insert testtrg values( @.nextid )
commit tran
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Klaus" <Klaus@.discussions.microsoft.com> wrote in message
news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
> Hi All
> I am looking for a bit of advice.
> I am in the process of creating a database in which it has been decided
> that
> all primary keys are going to varchar(40). Not my decision, but anyway.
> When inserting into each table it will be possible to specify a value for
> the primary, but if not specified a value should be auto-generated. That
> means that the values in the primary key field can be a mixture of both
> numbers and letters, but if auto-generated it should just be a number.
> What be the best way to make this autogenerated values if no value is
> being
> specified in the insert?
> TIA
> Klaus
>|||Thanks a lot, Tony. That was very helpfull.
I will create a unique field on each of my tables. The value for this will
be auto-generated using identity. A trigger will then keep an eye on the
inserts. If no value is being specified for the Primary key, the Identity
value will be copied into the varchar(40) primary key field.
-- Klaus
"Tony Rogerson" wrote:

> Depends really, if auto-generated does it just need to be a number? Any ol
d
> number, or a specific format and range?
> I always recommend putting a surrogate key on the tables and use that as t
he
> foriegn key and inside the application (not for display purposes, but for
> use as the value in a listbox for instance), that can be a int column with
> the IDENTITY property, not null and have a unique constraint on it.
> You could set the value of the primary key to that if not specified, that
> would save calculating a new unique number.
> Otherwise, you could use an 'instead of' trigger, for example...
> Instead of using MAX, you could take the value from a table that holds the
> last number used.
> create table testtrg (
> mycol int not null unique
> )
> go
>
> insert testtrg ( mycol ) values ( 1 )
> go
>
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
>
> declare @.nextid int
>
> begin tran
>
> set @.nextid = ( select max( mycol )
> from testtrg with (tablockx) )
>
> set @.nextid = isnull( @.nextid, 0 ) + 1
>
> insert testtrg values( @.nextid )
>
> commit tran
>
> end
> go
>
> -- Note, inserting 1 but it already exists so should give a key violation
,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> go
>
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Klaus" <Klaus@.discussions.microsoft.com> wrote in message
> news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
>
>|||The second part of Tony's point should not be lost. You should use that int
key as the FK for relationships with other tables. If you need to show your
client the benefit of using an int instead of a varchar(40). Load up a
couple of tables with some test data. Perform join's using varchar(40) as
the keys and then the same using int as the key. The performance difference
is noticable.
So use an int (or even bigint) PK, put a unique constraint on the
varchar(40) column and for all business logic purposes, the varchar(40) fiel
d
is the "key". But behind the scenes in the database the far more efficient
int is the key.
John Scragg
"Klaus" wrote:
> Thanks a lot, Tony. That was very helpfull.
> I will create a unique field on each of my tables. The value for this will
> be auto-generated using identity. A trigger will then keep an eye on the
> inserts. If no value is being specified for the Primary key, the Identity
> value will be copied into the varchar(40) primary key field.
> -- Klaus
> "Tony Rogerson" wrote:
>|||Also, if you need the data in a varchar(40) field you can use a calculated
column (if they dont need to enter it).
I concurr with Tony & John, if you're doing joins, definately use the INT
field as the joining field, joining on varchar fields gets very slow at
medium to high data volumes.
create table ( id int identity(1,1) primary key , myPK AS cast( ID as
varchar(40)) )
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:EDB86798-0F96-415A-9D8D-733ED2E0CA02@.microsoft.com...
> The second part of Tony's point should not be lost. You should use that
int
> key as the FK for relationships with other tables. If you need to show
your
> client the benefit of using an int instead of a varchar(40). Load up a
> couple of tables with some test data. Perform join's using varchar(40) as
> the keys and then the same using int as the key. The performance
difference
> is noticable.
> So use an int (or even bigint) PK, put a unique constraint on the
> varchar(40) column and for all business logic purposes, the varchar(40)
field
> is the "key". But behind the scenes in the database the far more
efficient
> int is the key.
> John Scragg
> "Klaus" wrote:
>
will
Identity
Any old
as the
for
with
that
the
violation,
decided
anyway.
value for
That
both
number.
is

auto_increment_offset

I need to setup a way to manage auto_inc keys on MSSQL without the
distributor handling it. I am using MSSQL 2005 transactional replication
with updateable subscriptions.
I have done this with MySQL using auto_increment_offset
see article:
http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
if you want more info about the process.
Can MSSQL do anything like this?
Thanks
You can use dbcc checkident for this. It is better to let replication handle
it through automatic identity range mangement. Here is an article on it.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
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
"Sintel Silverblade" <sintel@.segamer.com> wrote in message
news:e5%235uVhqHHA.4324@.TK2MSFTNGP04.phx.gbl...
>I need to setup a way to manage auto_inc keys on MSSQL without the
> distributor handling it. I am using MSSQL 2005 transactional replication
> with updateable subscriptions.
> I have done this with MySQL using auto_increment_offset
> see article:
> http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
> if you want more info about the process.
> Can MSSQL do anything like this?
> Thanks
>

Auto incremented integer primary keys vs varchar primary keys

Hi,

I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.

For example:

id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]

isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.

Regards
Mike
Why does everyone confuse a "primary key" and a "unique constraint"? They are totally different things. A PK is UNIQUE and ORDER of a table, which is also a "unique constraint". The PK should be the field (NOT fields) you use to LINK the table to other tables.

I have been doing databases for a very long time and I have several rules, which I have developed over the years on creating tables:

EVERY table has an integer id field, even if it is not needed right now, it will be later -- integer searching is 10,000 times faster than character searching and the indexes are much smaller.

NEVER key tables on data the user can change -- If you use ClientCode in your example above, and you change ClientCode, you would need to change EVERY SINGLE table which links the table using ClientCode.

|||

First of all Tom I have to disagree with the "ORDER of a table" statement. The order of a table is based on the clustered index butI do agree in most cases the primary key is the clustered key but this is not mandatory.

Most of the times when people tend to use non integer fields as a primary key it is because there is a suitable natural key for a table. I prefer natural keys if they fulfill the requirements for a primary key and that is unique, narrow and static (one might say ever increasing but then we would be back at the point of the clustered index story). Your example clearly shows the static requirement.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||That is true. A PK is usually a clustered index, although it is not required to be, in SQL Server. Other database engines may require it to be clustered. Generally, the PK is a clustered index, because it is field most searched, it improves performance by having the table ordered by the PK.

In the case where "ClientCode" can NEVER EVER change, other than speed, there is nothing wrong with using it. However, I personally would not use it.
|||Hi,

Thanks for your replies, I appreciate it. I know about the performance bonus that one gets by using a numeric primary key vs a varchar but I was curious to know the possible advantages of why one would sometimes not use it especially since the 'ClientCode' is not controlled within my database and is actually a primary key from a table in another database that I am pulling data from.

Personally I have always been using an integer autoincremented primary key and placed an unique index on the 'ClientCode' but I became curious to know why people don't always do it after seeing some other databases where they use the 'ClientCode' as a primary key.

Regards
Mike
|||

The are several different arguments around natural keys vs artificial or surrogate keys. The one Wesley mentioned where you don't have a column that's practical to use as a key. And the issues you are aware of in terms of performance. And another argument is that what is a natural key for a table may change over time - business needs and rules change, external sources may change, etc. If you use an artificial key (generated number in your case), that won't change in terms of being the unique identifier. You can do a search on the two terms and find many articles that debate this. It's one of those ongoing, long standing debates in the relational database world. Some people just prefer one over the other depending on which side of the "debate" they believe in.

-Sue

|||In your case where ClientCode is not controlled inside you database, that qualifies as "user data" and I would never use that field to link to other tables in my database. I would create an internal key to use as the FK.

What happens to your data if ClientCode is a varchar(10) now and the vendor decides to change it to a long? Or worse, allowes duplicates at some point.
|||Hi there,

Thank you all for your valuable input. I can understand why this might have been an ongoing debate since some people prefer simplicity over performance etc and I, for one, tend to be more of an 'performance' person so I will keep on using an seeded primary key.

It was good to hear the opinion of everyone here.

Thanks a lot
Mike

Auto incremented integer primary keys vs varchar primary keys

Hi,

I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.

For example:

id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]

isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.

Regards
Mike
Why does everyone confuse a "primary key" and a "unique constraint"? They are totally different things. A PK is UNIQUE and ORDER of a table, which is also a "unique constraint". The PK should be the field (NOT fields) you use to LINK the table to other tables.

I have been doing databases for a very long time and I have several rules, which I have developed over the years on creating tables:

EVERY table has an integer id field, even if it is not needed right now, it will be later -- integer searching is 10,000 times faster than character searching and the indexes are much smaller.

NEVER key tables on data the user can change -- If you use ClientCode in your example above, and you change ClientCode, you would need to change EVERY SINGLE table which links the table using ClientCode.

|||

First of all Tom I have to disagree with the "ORDER of a table" statement. The order of a table is based on the clustered index butI do agree in most cases the primary key is the clustered key but this is not mandatory.

Most of the times when people tend to use non integer fields as a primary key it is because there is a suitable natural key for a table. I prefer natural keys if they fulfill the requirements for a primary key and that is unique, narrow and static (one might say ever increasing but then we would be back at the point of the clustered index story). Your example clearly shows the static requirement.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||That is true. A PK is usually a clustered index, although it is not required to be, in SQL Server. Other database engines may require it to be clustered. Generally, the PK is a clustered index, because it is field most searched, it improves performance by having the table ordered by the PK.

In the case where "ClientCode" can NEVER EVER change, other than speed, there is nothing wrong with using it. However, I personally would not use it.
|||Hi,

Thanks for your replies, I appreciate it. I know about the performance bonus that one gets by using a numeric primary key vs a varchar but I was curious to know the possible advantages of why one would sometimes not use it especially since the 'ClientCode' is not controlled within my database and is actually a primary key from a table in another database that I am pulling data from.

Personally I have always been using an integer autoincremented primary key and placed an unique index on the 'ClientCode' but I became curious to know why people don't always do it after seeing some other databases where they use the 'ClientCode' as a primary key.

Regards
Mike
|||

The are several different arguments around natural keys vs artificial or surrogate keys. The one Wesley mentioned where you don't have a column that's practical to use as a key. And the issues you are aware of in terms of performance. And another argument is that what is a natural key for a table may change over time - business needs and rules change, external sources may change, etc. If you use an artificial key (generated number in your case), that won't change in terms of being the unique identifier. You can do a search on the two terms and find many articles that debate this. It's one of those ongoing, long standing debates in the relational database world. Some people just prefer one over the other depending on which side of the "debate" they believe in.

-Sue

|||In your case where ClientCode is not controlled inside you database, that qualifies as "user data" and I would never use that field to link to other tables in my database. I would create an internal key to use as the FK.

What happens to your data if ClientCode is a varchar(10) now and the vendor decides to change it to a long? Or worse, allowes duplicates at some point.
|||Hi there,

Thank you all for your valuable input. I can understand why this might have been an ongoing debate since some people prefer simplicity over performance etc and I, for one, tend to be more of an 'performance' person so I will keep on using an seeded primary key.

It was good to hear the opinion of everyone here.

Thanks a lot
Mike

Auto incremented integer primary keys vs varchar primary keys

Hi,

I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.

For example:

id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]

isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.

Regards
Mike
Why does everyone confuse a "primary key" and a "unique constraint"? They are totally different things. A PK is UNIQUE and ORDER of a table, which is also a "unique constraint". The PK should be the field (NOT fields) you use to LINK the table to other tables.

I have been doing databases for a very long time and I have several rules, which I have developed over the years on creating tables:

EVERY table has an integer id field, even if it is not needed right now, it will be later -- integer searching is 10,000 times faster than character searching and the indexes are much smaller.

NEVER key tables on data the user can change -- If you use ClientCode in your example above, and you change ClientCode, you would need to change EVERY SINGLE table which links the table using ClientCode.

|||

First of all Tom I have to disagree with the "ORDER of a table" statement. The order of a table is based on the clustered index butI do agree in most cases the primary key is the clustered key but this is not mandatory.

Most of the times when people tend to use non integer fields as a primary key it is because there is a suitable natural key for a table. I prefer natural keys if they fulfill the requirements for a primary key and that is unique, narrow and static (one might say ever increasing but then we would be back at the point of the clustered index story). Your example clearly shows the static requirement.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||That is true. A PK is usually a clustered index, although it is not required to be, in SQL Server. Other database engines may require it to be clustered. Generally, the PK is a clustered index, because it is field most searched, it improves performance by having the table ordered by the PK.

In the case where "ClientCode" can NEVER EVER change, other than speed, there is nothing wrong with using it. However, I personally would not use it.
|||Hi,

Thanks for your replies, I appreciate it. I know about the performance bonus that one gets by using a numeric primary key vs a varchar but I was curious to know the possible advantages of why one would sometimes not use it especially since the 'ClientCode' is not controlled within my database and is actually a primary key from a table in another database that I am pulling data from.

Personally I have always been using an integer autoincremented primary key and placed an unique index on the 'ClientCode' but I became curious to know why people don't always do it after seeing some other databases where they use the 'ClientCode' as a primary key.

Regards
Mike
|||

The are several different arguments around natural keys vs artificial or surrogate keys. The one Wesley mentioned where you don't have a column that's practical to use as a key. And the issues you are aware of in terms of performance. And another argument is that what is a natural key for a table may change over time - business needs and rules change, external sources may change, etc. If you use an artificial key (generated number in your case), that won't change in terms of being the unique identifier. You can do a search on the two terms and find many articles that debate this. It's one of those ongoing, long standing debates in the relational database world. Some people just prefer one over the other depending on which side of the "debate" they believe in.

-Sue

|||In your case where ClientCode is not controlled inside you database, that qualifies as "user data" and I would never use that field to link to other tables in my database. I would create an internal key to use as the FK.

What happens to your data if ClientCode is a varchar(10) now and the vendor decides to change it to a long? Or worse, allowes duplicates at some point.
|||Hi there,

Thank you all for your valuable input. I can understand why this might have been an ongoing debate since some people prefer simplicity over performance etc and I, for one, tend to be more of an 'performance' person so I will keep on using an seeded primary key.

It was good to hear the opinion of everyone here.

Thanks a lot
Mike