Can anyone think of a good reason for NOT having a PK?
-
Sergiy, we don't do joins on domain tables in the device code. The only place that we do joins to them is for reports and those are done on desktop.
Because the device is single user NOLOCK is of no use to me. Although there are claims that SQL Mobile is a "full implementation of SQL server" it's just not so. There are limitations. I leave it to the reader to go look up the differences.
Once again, If I were only concerned about desktop and not Mobile I would put very inclusive indexes on all of them. Likely my lookups would just touch such an index and not hit the table at all.
As I have stated making these decisions was based upon the evidence of testing. We set up experiments that had some test cases that seemed to make no sense. When we looked at all the results we were forced to come to the conclusions that we did.
I'm a caring, sensitive, and in-touch dude. I sympathize with my customers. Feeling their plight motivates me to want to make our software better. When it come to dealing with the software and hardware feelings go right out the door and the geeky engineer in me jumps up.
You have to tune your SQL to your environment. Training, books, forums (like this excellent one), and even your own experience can only serve as guides.
ATBCharles Kincaid
-
There's at least one scenario in which I'll avoid placing a primary key on a table. It has already been stated that when you are loading data from external sources and you are not responsible for the integrity of the data it's often preferrable to create staging tables in your database to house the loaded data, then create a process which transforms that data. The constraints and referential integrity which exists in the target database along with the transformation process will insure the integrity of your data, you usually don't care if the source system, which you also have no authority over, has maintained the integrity of their data. Adding primary keys to staging tables only means you have to deal with violations while loading the data and that's more difficult than dealing with them during the transformation process. Once I have the data in SQL Server it's easy to manipulate, many times getting the data into SQL Server is the most difficult part of the process.
There are theoretically other scenarios in which you might want to avoid having a primary key on your table. Specifically one which I can think of is in the case of a table in which data is added extremely frequently or is heavily updated. In that case the index maintenance might not be worth the expense.
Remember, SQL Server enforces the PK constraint by way of an index, you can't have a PK without the overhead of the index. Obviously it goes without saying that the fastest way to load data into a table is by removing all indexes from the table which means that if you need sheer speed then you are going to have a table which doesn't have a PK for data loads.
That's not to say in your scenario, there's a valid reason to drop the PK from the table.
-
So, you actually don't do joins.
You don't use relational database functionality and use database tables as set of flat files.
That's what I suspected when you described your tests.
Yes, set of primary and foreign keys on tables not to be joint in queries appears to be useless overhead.
As well as whole Relational Database Managent System which MS SQL Server appears to be.
It's like having car wheels on a bike. It will really slow that bike down.
But it does not mean that there is a problem with car wheels, that it's better not to use car wheels.
It means that car wheels must be used on cars.
Yes, there are some narrow tracks you can go only by bike. And it's much easier to park bike in a center of a city than a car.
But it does not mean bike should be used for carrying furniture or movig to another city.
Your approach must be OK for building small data storages not pretending to be scalable. But I'm not sure why you need RDMS like SQL Server for those tasks. There are plenty of free SQL database systems which will perform in such tasks not worse than MS SQL Server.
But if you mean to build relational database and actually use its functionality you must use PK's.
_____________
Code for TallyGenerator -
You are right. It is lazy on our part. We develop processing classes on the desktop because we can edit and continue (not available on the device). We can port the classes over without any code change becase the schema is the same. It would take time to make some things tables and some things flat files. As developers we are lazy.
ATBCharles Kincaid
-
Until a couple weeks ago I would have agreed with the Anders answer. I've gone the no-PK route in the exact scenario mentioned... tracking logins. That view changed when someone showed up with 10 million row table sans any indexes. Seems the job that summarized the data and moved it to another table got turned off and knowbody noticed. The client wanted to look at the May data, so we couldn't truncate the table, and yada yada yada... Deleting rows from a large table without indexes turns out to be a very slow and painful process.
Anther motivation for the addition of a PK is this. MSSM won't update or delete a row through the "open table" interface if it isn't unique. If nothing else, adding a PK forces each row to be unique.
-
The only time I can think of when a PK would be optional is when the entire table will fit onto one page. And, even then I'd probably create one just in case the table ever expands...
Steve G.
-
Anther motivation for the addition of a PK is this. MSSM won't update or delete a row through the "open table" interface if it isn't unique. If nothing else, adding a PK forces each row to be unique. There's a lot of apps that suffer the same problem and most DBA's know that... that's why I was so surprised that the DBA allowed the deletion of the PK without any replacement.
I don't ask questions on forums very often but I really needed the second opinions I got from this thread... just thought I'd say "thanks" for all of your input folks... lots'o good ideas on this thread.
--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) -
Wanna know REAL reason?
Just to prove the point that normal-minded developers can design and run a data centric system without yours, bloody annoying SQL geek, stupid suggestions and intrusions, the way normal people can understand and follow.
And to hide the fact how hard they struggle and how badly they fail they don't want you to see it. Because when people are proud of their work they are trying to expose its results.
I can see it in my experience time after time. When another VB developer rejects my set-based code and tries to prove that cursor approach is better.
_____________
Code for TallyGenerator -
Heh... you DO have a way with words! Good thing I had a DBA with the same type of extremely dry humor you do
I understand exactly what you mean... thanks, Serqiy.--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) -
What does the data in the table represent?
If a row can be duplicated, what does that mean?
i.e., if you say it in words, and it still makes sense, then there may be a reason.
Probably not in this case, as Jeff says it's a join table.
Wait..don't tell me both columns are NULLable, too...;-))
(Don't laugh too hard, I inherited a couple of DBs containing some tables having ALL NULLable columns!! Really.)
-
"(Don't laugh too hard, I inherited a couple of DBs containing some tables having ALL NULLable columns!! Really.)"
Yep, me too. Sytem was converted to SQL server from some other DBMS that did not have auto number. They used a sequence generator for the key fields.
ATBCharles Kincaid
-
>>Wait..don't tell me both columns are NULLable, too...;-))
Heh... dunno... like I said, they cut off my ability to pry any further... but the way they do things in that particular system, I wouldn't doubt it.
--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)
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply