Questions about Primary Key and Null

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

SQL Server 2000 Forums

SQL Server Development (2000)

Questions about Primary Key and Null

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-08-05 : 12:21:46

Couple of questions:

1 - There's an article on sqlteam about virtues of not using identity fields as primary keys. The best candidate for that in my database would probably be the email field. Wouldn't a 4 byte INT identity Column perform faster joins than a variable length email? If I'm wrong about this, great. Using email as the Primary / Foreign key to join tables results in a more readable result.

2 - Many of the Columns in our tables allow NULL - which is used for defaults. There's a lot written about the ill effects - DB overhead and query ambiguity stemming from the use of NULL. It is a small effort for me to revise the columns to use default values. In most instances this would be specifying '' as the default for strings, maybe 0 for most numeric. Knowing what I do about our DB usage and performance, I'm guessing that I won't realize performance benefits from this change - it is largly a modification to follow better principles. Any thoughts that would further justify the effort

SamC

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-05 : 12:37:32

1. You are going to have to try both the surrogate and the natural key to see how much the performance issues costs. Yes, joining and sorting on varchars will be a bit slower than the same operation on ints. However, the contrived identity column a)increases rowsize by 4 bytes thus causing less rows per physical page and b) takes time to generate and populate. Also, depending on how your queries are constructed, if you use the natural key, you may be able to query tables on that fk without going back to your original table to look up an email based on an id...


--with identity
select
   t2.blah
from
   table1 t1
   inner join table2 t2
       on t1.identitycol = t2.foreignkey
where
   t1.email = @email

--with natural key
select
   t2.blah
from
   table2 t2
where
   t2.foreignkey = @email

Bottom line....try both and see what happens.

2. There is little or no value in this method of getting rid of nulls. A default constraint of '' is meaningless. Your NULL are most likely a result of a non-normalized design more than anything else. Read Pascal's column.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources