Primary Key

Question

Primary Key

Viewing 11 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

I would love to agree but I just can't 😀 Happens we -or at least I - are talking about a very specific case which is STG tables properly defined as an extension of the source database therefore they are not part of your Data Warehouse database even when they are phisically sitting in your data warehouse.

Ashok Kumar Pant (10/13/2008)
TO make sure the Normalization rule, there must be primary key in a table. If there is no PK then how can you stop data redundancy in a table. PK also helps to make sure each and every records are unique and the required data field also filled by user.

Answer is... you don't care, STG is an extension of the source database -at least in this part of the world and in this particular parallel universe - you extract into STG whatever the source database offers to you.

Another thing is Performance issue. It is easier to apply clustered indexing on PK because it makes sure the criteria for Clustered Index.

Answer is... STG tables - when in a properly designed environment - are always processed from end to end therefore the best thing you can do is a Full Table Scan so... why in the world would you like to have a PK?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

Heh... this has been a lot of fun, but the funny thing about this whole thing is that people keep talking about everything except the original post.

Yes, I agree that staging tables are a place where you might not need a primary key especially if you don't have to do any updates or deletes to the staging tables themselves. Some "archive" and maybe even some audit tables don't necessarily need a primary key. In fact, I've saved over 54 gig by removing a ridiculous 9 column, very badly formed PK from an archive table and, no, I've not added a simplified replacement. And, I frequently post code examples without regard to a PK.

Bottom line is (PaulB is actually quite correct in this area), depending on what you're doing, you don't need a PK on a table to use it effectively.

But let's get back to the original question... the original post in this thread asks the simple question of whether or not you should put a PK on a simple 4 row permanent table like a lookup table? My answer would be "Yes". I also maintain the positition that although tables with no PK can be quite useful, they're not really tables unless they have a PK to enforce Entity Integrity.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Jeff Moden for President!!!

You have my vote Jeff, I do agree.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

GSquared SSC Guru Points: 260824 More actions · Answer 4

Robert Frasca (10/13/2008)
Don't mean to be fussy but a table can have a PK and be a heap table. A table without a clustered index is part of the heap. It doesn't matter whether the clustered index is the PK. In fact, I often see PK's on identity attributes and clustered indexes on some other natural key that would support range queries more effectively.

Yes, but the two are related. The only tables where I've deliberately not had a PK also didn't have a clustered index, and no constraints other than data definitions for the columns (nothing that would show under the constraints tab in management studio). That's because the only times I've found not having a PK a useful thing have been times where the most important consideration was speed of inserts, so getting rid of as many checks as possible was important. That's why I wrote about the two as if they were connected.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

Okay... lets try to get to the bottom line on this stuff.

You should create a PK when you want to have RI enforced.

If you want to enforce uniqueness an Unique Index would do it, you don't need a PK and actually the PK wouldn't enforce uniqueness. Let me break the news for you guys, when you create a PK to enforce uniqueness is the unique index SQL Server creates for you on your PK columns that does the trick.

Settled? :satisfied:

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

sqlizer SSCrazy Points: 2120 More actions · Answer 6

Ok then I have another question. I created a table

create table employee

(id INT,

Name VARCHAR(50))

Then Created a Unique Clustered index on that table.

CREATE UNIQUE CLUSTERED INDEX CIX ON EMPLOYEE(ID)

Then I inserted 2 rows.

INSERT INTO employee

SELECT NULL, 'ABC'

UNION

SELECT 1, 'DEF'

So it let me enter NULL value in the clustered index column. so my question is, What stops NULL value from entering in the Primary key column of a table? I know the question is not related to this example i have mentioned above. It's not UNIQUE CLUSTERED index.

Pls reply.

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 7

Unless there's a not null constraint you can have one null value in as this 'does not break the uniqueness if the primary key values'

Personally, my view is this is gentleman's dangly objects. Null is unknown. Therefore, strictly, you don't know this to be true - if you don't know a value it cannot be guaranteed not to conflict with another value.

Well - that's more highly picky than strict I suppose.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

chintan kotwal (10/16/2008)
Ok then I have another question. I created a table
create table employee
(id INT,
Name VARCHAR(50))
Then Created a Unique Clustered index on that table.
CREATE UNIQUE CLUSTERED INDEX CIX ON EMPLOYEE(ID)
Then I inserted 2 rows.
INSERT INTO employee
SELECT NULL, 'ABC'
UNION
SELECT 1, 'DEF'
So it let me enter NULL value in the clustered index column. so my question is, What stops NULL value from entering in the Primary key column of a table? I know the question is not related to this example i have mentioned above. It's not UNIQUE CLUSTERED index.
Pls reply.

Unique index (clustered or not) is not the same as a primary key. If you go back to relational theory, a Primary Key is a "unique ADDRESSABLE value composed of one or more columns in a table" (among other things). Primary Keys can therefore never be null. So - your Unique Clustered index can never be the primary key for that table.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

PaulB (10/16/2008)
Okay... lets try to get to the bottom line on this stuff.
You should create a PK when you want to have RI enforced.
If you want to enforce uniqueness an Unique Index would do it, you don't need a PK and actually the PK wouldn't enforce uniqueness. Let me break the news for you guys, when you create a PK to enforce uniqueness is the unique index SQL Server creates for you on your PK columns that does the trick.
Settled? :satisfied:

No - Primary keys ensure uniqueness. Whether an index is used to IMPLEMENT the primary key concept or not ultimately is not germane to the Primary Key concept. In the early days, there were a lot of RDBMS implementations that used other mechanisms to enforce or implement primary keys.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 10

PaulB (10/16/2008)
Okay... lets try to get to the bottom line on this stuff.
You should create a PK when you want to have RI enforced.
If you want to enforce uniqueness an Unique Index would do it, you don't need a PK and actually the PK wouldn't enforce uniqueness. Let me break the news for you guys, when you create a PK to enforce uniqueness is the unique index SQL Server creates for you on your PK columns that does the trick.
Settled? :satisfied:

I'm afraid not. I think a lot of people confuse a key and an index. A PK does enforce uniqueness but I'm not really sure that it's accurate to say that it enforces RI unless it is used as a foreign key elsewhere. By definition a primary key is unique since it is an abstraction. It is a column or columns that uniquely identify a row. Because that is true, a PK is a constraint. In SQL Server 2005, the engine enforces that constraint via a unique index. Sometimes the natural primary key of a table encompasses more than one column which can be a bit of a performance hit if it is large and a unique index is created on it. This is why many data modelers create surrogate keys that are typically an integer column that has the identity attribute turned on. A four-byte integer surrogate key has a significantly lower cost than a nine-column 48 byte natural primary key. It has the added benefit of being transparent to the user and greatly simplifies queries by allowing you to join on one column instead of nine. It also simplifies referential integrity if that PK is a foreign key in another table, for example, in a hierarchy like OrderID in an OrderDetail table.

I don't buy the argument that creating a PK on a table is bad because it slows down inserts. If it slows down inserts that significantly then I'm betting that it is a natural primary key and not a surrogate key. Additionally, a primary key serves more than one purpose. Not only does it enforce uniqueness, it also creates an efficient query path for the optimizer to choose. If you need to bulk-load a table, turn off the PK, load the table, and then turn it back on again. That is frequently the route that is chosen in DW's and staging tables. The overhead of creating a unique index on an integer surrogate key after the data is loaded is also significantly less than the same overhead for a nine-column natural key.

I always create surrogate primary keys for every single table I create. However, if it doesn't make sense for that primary key to be a clustered index (which is the default), i.e. will I be making range queries or not, then I make it a non-clustered index.

I will grant you that sometimes, in very, very rare circumstances, a primary key appears to add little value from a query point of view. In that case, I still create a surrogate key on the table but do not create the primary key constraint. The reason I say that the circumstances are very rare is that I don't see much point in creating a table that isn't going to be queried. Even short-lived staging tables are going to be queried by something.

"Beliefs" get in the way of learning.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 11

I'm loving it! 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.