Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 How to keep away from using identity column

Author  Topic 

Nadermfr
Starting Member

4 Posts

Posted - 2012-11-08 : 12:46:14
Hello,

I know that if my primary key is identity it slows down the system

I am designing a new database.

two of the tables are question (questionid int (PK identity), questionnarrative, etc)

Quiz (quizid int(PK,identity), quizname, etc

I will have a huge huge number of questions and quizzes

Is there a way to keep away from identity and it is better to specify the column as largeint instead of int

Thanks

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-11-08 : 13:32:37
quote:
Originally posted by Nadermfr



I know that if my primary key is identity it slows down the system




hmmmm. How does the Identity slow down the system?
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-09 : 03:20:05
quote:
Originally posted by Nadermfr

Hello,

I know that if my primary key is identity it slows down the system
...



I disagree with the premise of this question. Identity PKs will not slow down your system.

-Chad
Go to Top of Page

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-11-09 : 12:58:54
I read a post that not to use identity as the primary key.
and that if you had several tables in your database with identity primary key they will be fighting to get the one.

I was confused when I read this post. So I want to make sure that using identity as primary key on several tables in my database will not affect the performance
The second part of my question is if I have a huge huge number of questions is it ok to use integer or should i go for long integer
Thanks

sarah
Go to Top of Page

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-09 : 13:03:53
No, there is no contention for identity values among multiple identity columns. Define huge...if huge is more than 2 Billion, then you probably want to consider bigint.

-Chad
Go to Top of Page

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2013-01-08 : 10:48:41
Where is Mr Joe Celko.....

If you're having to use surrogate keys to uniquely identify records in your table then your database isn't correctly normalised.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-08 : 11:36:46
>> if you had several tables in your database with identity primary key they will be fighting to get the one
Was that sql server?

>> if I have a huge huge number of questions is it ok to use integer or should i go for long integer
Depends on what you mean by a huge number. If you are going to hit the int limit then use bigint.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -