Getting rid of IDENTITY 
Author Message
 Getting rid of IDENTITY

I am trying to reduce the number of tables that contain identity
columns.  But I had some questions...

I have some tables that don't have any real natural key other than a
name/description.  These columns range anywhere from 30 to 64
characters in length.  I have detail tables that reference these
tables.  Should I use these wide character columns as the primary key
for the master table and reference them in the details?  Should they
be clustered??  I could create another column and uniquely identifies
the groups, but it would not be any less ambiguous than an identity
column, and as far as I can it would be even more difficult to
implement...

CREATE TABLE Groups (
  group_name varchar(64) NOT NULL,
  adate datetime NOT NULL CONSTRAINT DF__Groups__adate DEFAULT
(GETDATE()),
  CONSTRAINT PK__Groups PRIMARY KEY CLUSTERED (group_name) ON
[PRIMARY])

CREATE TABLE GroupMembers (
  group_name varchar(64) NOT NULL CONSTRAINT
FK__GroupMembers__group_name FOREIGN KEY REFERENCES Groups
(group_name),
  member_name varchar(30) NOT NULL,
  column0 int NOT NULL,
  ..
  columnN datetime NOT NULL,
  CONSTRAINT PK__GroupMembers PRIMARY KEY CLUSTERED (group_name,
member_name) ON [PRIMARY])

Ideas or criticisms?

Brandon
--
"In the beginning the universe was created. This has made a lot of
people very angry, and has been widely regarded as a bad move." -
Douglas Noel Adams (1952-2001)
[Please remove "nospam_" from email address to reply.]



Mon, 11 Oct 2004 05:33:10 GMT
 Getting rid of IDENTITY

You need numeric key.
It is not as hard as it looks to you.
Joins will be faster.

How do you rename your Group_name ?

What if you need to rename it and you have 100 child tables?


Quote:
> I am trying to reduce the number of tables that contain identity
> columns.  But I had some questions...

> I have some tables that don't have any real natural key other than a
> name/description.  These columns range anywhere from 30 to 64
> characters in length.  I have detail tables that reference these
> tables.  Should I use these wide character columns as the primary key
> for the master table and reference them in the details?  Should they
> be clustered??  I could create another column and uniquely identifies
> the groups, but it would not be any less ambiguous than an identity
> column, and as far as I can it would be even more difficult to
> implement...

> CREATE TABLE Groups (
>   group_name varchar(64) NOT NULL,
>   adate datetime NOT NULL CONSTRAINT DF__Groups__adate DEFAULT
> (GETDATE()),
>   CONSTRAINT PK__Groups PRIMARY KEY CLUSTERED (group_name) ON
> [PRIMARY])

> CREATE TABLE GroupMembers (
>   group_name varchar(64) NOT NULL CONSTRAINT
> FK__GroupMembers__group_name FOREIGN KEY REFERENCES Groups
> (group_name),
>   member_name varchar(30) NOT NULL,
>   column0 int NOT NULL,
>   ..
>   columnN datetime NOT NULL,
>   CONSTRAINT PK__GroupMembers PRIMARY KEY CLUSTERED (group_name,
> member_name) ON [PRIMARY])

> Ideas or criticisms?

> Brandon
> --
> "In the beginning the universe was created. This has made a lot of
> people very angry, and has been widely regarded as a bad move." -
> Douglas Noel Adams (1952-2001)
> [Please remove "nospam_" from email address to reply.]



Mon, 11 Oct 2004 05:43:50 GMT
 Getting rid of IDENTITY
Why the obsession with getting rid of identity columns?

Regards

Vaughan Powell
MCDBA, MCSD, MCSE

Consultancy Director
Information Management Group
<http://www.imgeurope.com/>

This posting is provided "AS IS" with no warranties, and confers no
rights.
You assume all risk for your use. ?2002 Information Management Group.
All rights reserved.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 11 Oct 2004 06:07:21 GMT
 Getting rid of IDENTITY

Quote:
> Why the obsession with getting rid of identity columns?

Not so much an obsession, but they are terribly overused in our
system, and I want to remove them where it is practical.  I am just
wanting to get some guidance for specific situations.

Brandon
--
"In the beginning the universe was created. This has made a lot of
people very angry, and has been widely regarded as a bad move." -
Douglas Noel Adams (1952-2001)
[Please remove "nospam_" from email address to reply.]



Mon, 11 Oct 2004 20:23:02 GMT
 Getting rid of IDENTITY

Quote:
> You need numeric key.
> It is not as hard as it looks to you.
> Joins will be faster.

I realize that joins on numeric columns will be faster, but I am at a
loss how to assign a unique numeric value to a table whose only unique
(and static) column is a wide character column.  How would you handle
this?

Quote:
> How do you rename your Group_name ?
> What if you need to rename it and you have 100 child tables?

You don't.  Once named, it's there forever.  But if it was a column
that I wanted to be able to rename, then I wouldn't make it a primary
key, and would use an identity, since I have no other reliable means
of generating a unique numeric value :)

Brandon
--
"In the beginning the universe was created. This has made a lot of
people very angry, and has been widely regarded as a bad move." -
Douglas Noel Adams (1952-2001)
[Please remove "nospam_" from email address to reply.]



Mon, 11 Oct 2004 20:26:47 GMT
 Getting rid of IDENTITY
Hi!

in our aplication we have natural key and surrogate key.
Your ID is natural key. Surrogate Key is numeric prmary key.
Put Unique constraint on ID and you done.
ID is something that user will be able to see. Key is something user don't
know about.
It is not necessary to make it identity. It could be just integer, But make
sure you generate it before insert (from Trigger? Stored procedure?)

Even ID is unique it will be chance to rename it and all relationships will
be the same.

Hope it helps


Quote:


> > You need numeric key.
> > It is not as hard as it looks to you.
> > Joins will be faster.

> I realize that joins on numeric columns will be faster, but I am at a
> loss how to assign a unique numeric value to a table whose only unique
> (and static) column is a wide character column.  How would you handle
> this?

> > How do you rename your Group_name ?
> > What if you need to rename it and you have 100 child tables?

> You don't.  Once named, it's there forever.  But if it was a column
> that I wanted to be able to rename, then I wouldn't make it a primary
> key, and would use an identity, since I have no other reliable means
> of generating a unique numeric value :)

> Brandon
> --
> "In the beginning the universe was created. This has made a lot of
> people very angry, and has been widely regarded as a bad move." -
> Douglas Noel Adams (1952-2001)
> [Please remove "nospam_" from email address to reply.]



Mon, 11 Oct 2004 22:05:32 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Getting rid of the IDENTITY property on a column withou getting rid of the column

2. Getting rid of excess lseeks()

3. Getting rid of Primary Log file - SQL 2K

4. getting rid of duplicates

5. Answer:Getting rid of the logon prompt

6. Agility Grid Control, Getting rid and bringing back columns

7. Getting rid of version control stored procs

8. Getting rid of NULLs

9. Getting rid of version control stored procs

10. Getting Rid of 1205 Errors

11. Getting Rid of toolbar in the DataReports

12. getting rid of extra spaces in comments


 
Powered by phpBB® Forum Software