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

Auto increment help

Is there an auto increment feature for integer key fields. I'm using VS2005 to configure my DB and I do not see a way to set this.

Thanks in Advance
MoonWa

CREATE TABLE tblArtists
(
artistID int identity primary key,
artistName nvarchar(50) NOT NULL
)

this SQL creates a table with two fields, the keyword 'identity' makes a field auto increment. In the UI there's a property 'identity specification', or something similar. If you open this, you can check 'is identity'.

|||

DataColumn type has a property AutoIncrement:

dtEmployees.Columns[0].AutoIncrement = true;
dtEmployees.Columns[0].AutoIncrementSeed = -1;
dtEmployees.Columns[0].AutoIncrementStep = -1;

SQL Server table may also have one identity column, as Christian mentioned. But you should be aware of some points:

1. SQL Server by default doesn't allows inserting in Identity columns (and never allows updates)
2. Your DataColumn instance knows nothing about current SQL Server Identity value and will possibly generate duplicate values (which will fail on insert if column is constrained via Primary Key or Unique, as usually).

So, if you let SQL Server to generate values, you also should update your column' values with generated one from stored proc output parameters or from output from FOR INSERT trigger on your table (or so) using DataAdapter. For example:

daEmployees.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

WBR, Evergray
--
Words mean nothing...

|||Thanks for your help.

MoonWa

Auto increment

How do I do to give a column an integer type and setting it to auto increment
so that I don't have to give this value every time I insert something in the
table?
You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:

>How do I do to give a column an integer type and setting it to auto increment
>so that I don't have to give this value every time I insert something in the
>table?

Auto increment

How do I do to give a column an integer type and setting it to auto incremen
t
so that I don't have to give this value every time I insert something in the
table?You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:

>How do I do to give a column an integer type and setting it to auto increme
nt
>so that I don't have to give this value every time I insert something in th
e
>table?

Auto increment

How do I do to give a column an integer type and setting it to auto incremen
t
so that I don't have to give this value every time I insert something in the
table?Hi
See an IDENTITY property
CREATE TABLE #Test
(
col1 INT NOT NULL IDENTITY(1,1),
col2 CHAR(1)
)
INSERT INTO #Test (col2) VALUES ('A')
"Joachim" <Joachim@.discussions.microsoft.com> wrote in message
news:319993D2-2CE4-47BE-A8C7-BB2064E6EFB0@.microsoft.com...
> How do I do to give a column an integer type and setting it to auto
increment
> so that I don't have to give this value every time I insert something in
the
> table?

Auto increment

How do I do to give a column an integer type and setting it to auto increment
so that I don't have to give this value every time I insert something in the
table?You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:
>How do I do to give a column an integer type and setting it to auto increment
>so that I don't have to give this value every time I insert something in the
>table?