Can anyone think of a good reason for NOT having a PK?
-
I'm not at work right now... I'll dig it up when I get there. I normally have access from home but not on this particular DB...
--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) -
It was my understanding that a heap is a table without a clustered index.
Clustered or non-clustered index is not related necessarily to a PK. A PK is something different. It requires an index, but it doesn't have to be either clustered or non-clustered. I can make a unique, non-clustered index on my table as the PK and it still be a heap.
-
I gotta look that one up, Steve... I very well could be having a problem with convincing the DBA if my definition of what a heap is is incorrect. My understanding was that a heap was a bunch or rows and columns that looked like a table but wasn't because of no PK (clustered or otherwise). Since the default is for a PK to be clustered, some misunderstanding may have come from that, but I'm not sure.
--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) -
indeed, by default a PK will be a clustering index, unless there is already a clustering index active, or if you create it nonclustered.
create table mytb (col1 int not null primary key nonclustered, col2 char(10) not null default '')
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks Jeff ... a few other questions for when you get the DDL as well.
- How many rows are in the table ?
- How many pages/extents does it occupy ?
- What type of application uses the tablw (OLTP/Batch/DSS/DW) ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Steve is correct, a heap in SQL Server is a table without a clustered index. If you look in BOL at Table and Index Architecture, it shows that there are 2 ways in which SQL Server organizes the data pages, clustered tables and heaps. Think of a heap as an un-ordered pile of data.
-
The only time I have not used a PK is for a table retreiving real time data from a call switch. The application would track the data in real time, send updates current stats to the clients and write out change records to the database for later review. The table had no indexes or constraints to ensure locking was kept to a minimum as there could be tens of thousands of changes every 3 seconds. In addition if a servers link was severed the app would auto reconnect and query status for everything from scratch, this would cause records that in reality were duplicates, but due to the real time need they were just added to the table. Every night at midnight a process would go thru and change which table the application was writing to (stored procedure was altered) then another process would take the data out of that days table, evaluate the duplicate records to remove them and move all the data to the reporting server, then truncate the day table for so the next night was fresh.
-
I would just point out that a table without a primary key is not even in first normal form.
Personally, I would never accept a table into production without a primary key.
Someone mentioned the case of a table with only one row. In that situation I would especially want a primary key to be able to ensure that the table only had one row. For one-row tables I create an integer primary key, and add a check constraint on the primary key to allow only 1 as the value; since it is unique, the table can only contain one row. If I want to ensure that the table in never empty, I add a Before Delete trigger that raises an error and rolls back the transaction.
The point I am making is that even the simplest table should be properly designed and that the lack of a primary key is a violation of the most basic rule.
-
Heh... figures... Rudy! They cut off my access to the DB in question so I can't see the table and it's dependents anymore... guess they got tired of my prying into how dumb they may be.
--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) -
Here there everybody... thank you folks for the good info... most of you are of the same ilk about the necessity of a primay key... I think that's why they cut off my access... didn't want me to find any more of their genius in the form of tables that aren't tables
--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) -
Don't be an enabler!!
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Not sure if this is what was meant, but in scenarios where the table is passed around as a file from one location to another to be updated by each location, having an autonumbered column adds only confusion. Practical access needs to be through a column that contains useful data (or more likely, a combination of such columns). =Marty=
Regards,R Martin Ladner
futureec.com -
won't there be performance issues with this? just wait for them to have problems and you will be their messiah when the sh!t hits the fan. It will be worth a good giggle when you finally are able to open up their little pandora box.
-
Oh yeah... absolutely. That's part of why I was asking... Not having a PK on the table really seems dumb. And, I absolutely agree with Marty on this one... the PK should definitely NOT be an autonumbering column not only for the reason he stated, but because this is a cross-reference table for m2m joins!
--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) -
but an auto-number should be present for easier dba operations.
Viewing 15 posts - 16 through 30 (of 71 total)
You must be logged in to reply to this topic. Login to reply