Primary Key field(s)
-
I usually design my tables with the PK as the first field, and then the rest of the fields in some sort of order of importance, eg foreign key fields next and so on.
In my current job the standard is to define fields in alphabetical order.
Are there any technical reasons why one approach is better than the other or does SQL not care one jot what order the fields are in?
-
From a personal preference, I prefer PKs first (in key order if composite)
I've no preferences for the rest, apart from generally place large text columns at the bottom.
Cheers
-
Aye, that's the approach I used to take. It just seems 'right' to have the PK first.
However, I am wondering if there is a technical reason - performance perhaps - for doing this?
-
"Aye": Would that be scottish then? Not that I am, spent many years there though.
I think you can "look" at the table definition say, in QA object browser, and know that where your PK is and what order the keys is in.
Otherwise I reckon it would be easy to write a poor query against that table e.g. by using a WHERE clause against the PK in the wrong order. I have seen code (and fixed it) that rearranging joins and filters improved query perf by aligning them to the PK order.
-
I'm not Scottish, I just say 'aye' a lot.
I agree with your observations regarding the order of PK fields, especially being able to see at-a-glance the correct order for a composite key.
Thanks for the input 🙂
-
There is no technical reason to define the columns in any specific order (in fact, in a truly relational DBMS there is no such thing as an order of the columns).
Personally, I think that any standard that is more than a recommendation would be a bad thing. For instance, if you need to add a new column called name, to a table that already has the columns id and size. If the standard says that the columns should be in alpabetical order, do you have to rearrange the columns then (which in effect means to drop and recreate the table)? Or if id is the PK and you want to add a column called type which will be an FK, do you need to insert it between id and size?
-
Thanks for the info about there being no technical reason.
As for your other questions, if there is a standard in place then alterations to a table must conform to that standard. However, the more I ask and read about this subject, the less sure I am of a standard to employ: I think your idea of a recommendation looks more and more attractive.
Cheers 🙂
-
Yep, that standard would not be so great when you need to rearrange the column order of a 200+ million row.
-
Hi all,
Here are my 2 cents: On ORACLE, actually it makes a difference how you order columns. I am not sure if this stands for M$$QL, too, but it makes sense. On ORACLE, it is recommended to put columns that are most frequently NULL at the bottom of the table. This is for storage optimization reasons. If all columns after a certain column are NULL, ORACLE doesn't occupy any space for them in the physical block. So, when values of those columns are retrieved from the table, and they are not found, they are assumed to be NULL. I respect all theoretical RDBMS concepts, but this is an implementational issue. Relational algebra doesn't say anything about disk space, but relational algebra is not too usefull if it remains just a theory
.Now, I didn't see any similar explanation in BOL, but (having in mind which vendor leads the RDBMS resarch field, and which one "reuses"
ideas) it seems to me that is only a matter of time when we will see this feature on M$$QL.At the end, since PK columns are NOT NULL, Michael's standard to put PK fields first complies with described ORACLE logic.
Regards,
Goce.
-
Now, I do not want to go into any religous discussion here, but why use those M$$QL and "reuses ideas" 'tokens' to try and enforce your message? The only reaction you are going to get with them is frustration.
Anyway, you cannot compare low-level implementation details of Oracle to SQL Server. The physical storage details in Oracle and SQL Server differ a lot, so this advice makes no sense for SQL Server. There is no such specialized implementation in SQL Server (except for varying size columns that are null, and would be empty). In fact, going from SQL Server 6.5 to 7 they changed the implementation so that fixed-length columns containing NULL no longer needed only 2 bytes but now instead use the full amount of bytes (for instance 4 bytes for an int, even if it is NULL).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply