SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 How to keep away from using identity column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Nadermfr
Starting Member

4 Posts

Posted - 11/08/2012 :  12:46:14  Show Profile  Reply with Quote
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

Canada
591 Posts

Posted - 11/08/2012 :  13:32:37  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 11/09/2012 :  03:20:05  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 11/09/2012 :  12:58:54  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 11/09/2012 :  13:03:53  Show Profile  Visit chadmat's Homepage  Reply with Quote
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

United Kingdom
206 Posts

Posted - 01/08/2013 :  10:48:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/08/2013 :  11:36:46  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
>> 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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000