July 2, 2010 at 3:09 pm
From a programming prospective. Where is it best to add a FK in a table?
July 2, 2010 at 3:47 pm
So ... what would you do with a FACT table that has 48 FKs? location is inmaterial/
_____________________________________
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.July 2, 2010 at 5:31 pm
Thank you for your response. I'm new to this and am probably going to ask more questions you upper level guys get a chuckle over. I'm trying to learn the correct way and hopefully get it right the second time around.
July 2, 2010 at 9:05 pm
The point Paul was making is that column placement doesn't matter. Since you reference columns by name, their order in the table has no bearing on performance or storage.
At one time, it did matter because of the way the tables were structured. I believe that was in 6.0 or 6.5 - but from 7.0 on it doesn't have any effect.
And, if you think about it - what happens if next year you need to add new columns? To place a column in a specific order within the table, you have to recreate the table. The GUI tools will do this for you, but it will actually create a new table, copy the data from the existing table into the new table, drop the old table and rename the new table. This can be a very expensive operation on a table with millions of rows.
It's easier to just add the column using an alter table command - which normally will just be a meta-data change and happens very quickly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 4, 2010 at 4:27 pm
Joe Celko (7/3/2010)
You order the columns in logical groups (the street address, city state and ZIP are placed together) because of the default ordering of SQL.
:blink: "because of the default ordering of SQL".... mmmhhh... what?
Joe Celko (7/3/2010)
No good SQL programmer would keep audit data (creation dates, etc) in the table being audited. This is both stupid AND illegal.
:w00t: illegal? is it illegal to add a timestamp column to a table? In which country?
_____________________________________
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.July 4, 2010 at 5:13 pm
As mentioned it doesn't matter. Don't worry about FKs ordering when putting columns in the table.
July 4, 2010 at 11:43 pm
Steve Jones - Editor (7/4/2010)
As mentioned it doesn't matter. Don't worry about FKs ordering when putting columns in the table.
Don't worry about order of anything when putting columns in a table. Not FK, not PK, not variable-length columns, not fixed-length columns, not LOBs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2010 at 1:22 am
order doesn't matter. you can place it at any order.
Regards,
Amit kulkarni
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply