July 9, 2012 at 1:02 pm
SQLKnowItAll (7/9/2012)
Lynn Pettis (7/9/2012)
SQLKnowItAll (7/9/2012)
Well, personally, I like my createdBy, createdDAte, modifiedBy, modifiedDate at the end of the table. Of course, it really doesn't matter... but it makes it seem more logically organized. I like my primary key columns to be first. It gives an organization scheme to the table that is easily viewed in SSMS or any GUI that is easier on the brain. Coming from a background in psychology with some study in industrial organizational psych, I can see how a persons' logical interpretation being matched with a visual representation can help make development easier. It is really a preference or psychological thing though.If my tables have those columns, I actually prefer them at the front of the table. But then, I always try to add new columns at the end.
Proves my point (I think) that we all have things that make sense to us and those may differ between individuals, companies, etc. I think from a business standpoint it is good to have some consistent design patterns that can be implemented to make things easier across the organization.
Yes, it does. As I don't use the GUI to create tables preferring to write the SQL directly, using a template for the create table statement it helps to have the common columns at the beginning just makes sense to me. You start with those, then add the columns that are needed for each unique table.
July 9, 2012 at 1:52 pm
At a place I used to work, the IS manager (who was the DBA before they hired me) had a policy that every table had to have an ID column first, and had to end with CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, IsCurrent. (The last was to support virtual deletion/update.)
Their standard procedure was to use the table designer in SSMS to add columns in the middle and leave the first and last pieces intact. This, of course, was implemented by dumping to a temp table, dropping and re-creating the table with the new structure, then inserting from the temp table into the new table structure. But the mechanics of it were hidden, and nobody there had ever looked under the hood to see how it was being done.
They couldn't figure out why simply adding a column could take a long time (big tables), could even time out (really big tables), and definitely hadn't taken into account that a few of their tables, containing BLOB data, were too large to create a duplicate in tempdb, and would have crashed the server if they had tried (a couple of tables too big to have two copies on their SAN at the same time).
They also wanted to implement a DR plan involving Replication. Which would have had to include some tables that they might need to edit, and you can't do the drop-create cycle on replicated tables (the subscription will give you an error message if you try it).
The policy needed to be changed. Simple as that.
Placing the audit columns first would have allowed the tables to be modified by adding to the end (simple DDL scripts), and would have allowed for Replication. Allowing columns to be in whatever sequence they're in would have been even easier and would have prevented these problems in the first place.
The detail that those kind of audit columns, and the virtual update/delete piece, are more likely to mess you up than to do anything useful, are tangential to this discussion, but if the reason you want specific column sequences is to keep that kind of data in a particular pattern, please reconsider that as well.
If you want to look at a table with columns in a particular sequence, write your query that way, don't use "Select *" and depend on SQL Server to feed you columns in "the order they are in the table". You can select from sys.columns with an Order By clause on it if you need a "wizard" to write the sequence for you.
Something like this:
CREATE TABLE #T
(Col3 INT,
Col7 INT,
Col1 INT,
ID INT IDENTITY
PRIMARY KEY,
Col2 INT,
CreatedBy VARCHAR(100) NOT NULL,
CreatedAt DATETIME NOT NULL
DEFAULT (GETDATE()),
Col4 INT,
Col6 INT,
ModifiedBy VARCHAR(100) NULL,
Col5 INT,
ModifiedAt DATETIME NOT NULL
DEFAULT (GETDATE())) ;
DECLARE @TableName SYSNAME = N'tempdb..#T' ;
SELECT 'SELECT ' + STUFF((SELECT ',[' + name + ']'
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID(@TableName)
ORDER BY CASE name
WHEN 'ID' THEN 0
WHEN 'CreateBy' THEN 2
WHEN 'CreatedAt' THEN 3
WHEN 'ModifiedBy' THEN 4
WHEN 'ModifiedAt' THEN 5
ELSE 1
END,
name
FOR XML PATH(''),
TYPE).value('.[1]', 'varchar(max)'), 1, 1, '') + ' FROM ' + @TableName
You could modify that script to be smart enough to detect if the table name started with a # and look in tempdb if it does, or look in the local database's metadata if it doesn't. Could easily add further logic to it depending on what you need.
Then use that to template out your queries with the columns in the order you like, regardless of "how they are in the database".
(For documentation, this queries from sys.columns using the Object ID. Then it uses a For XML trick to concatenate the columns together into a single, comma-separated list. It uses the .value() function on the XML to turn it back into text, from XML, so that if any columns have special characters in their names you get the actual column names, not the XML version of them. That handles columns with & or / in the name, for example. The Stuff() function is then used to strip off the unneeded comma at the beginning of the list. After that, it's just the kind of query that you would use to build dynamic SQL, adding in the SELECT and FROM statements.)
- 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
July 9, 2012 at 2:14 pm
Why not just create a view that has the columns in the desired order, and just add anything new to the physical table at the end?
July 9, 2012 at 2:28 pm
tim.cloud (7/9/2012)
Why not just create a view that has the columns in the desired order, and just add anything new to the physical table at the end?
That's the same solution as I mentioned, of creating your Select statements the way you want the columns to appear.
However, that can have some unintended consequences as well. For example, if you drop a column from a table, and forget to drop it from the view, you'll get an error from the view. Not a big deal, but it's that much more to keep track of.
- 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
July 9, 2012 at 2:30 pm
True, more maintenance. However, I had no idea what maintenance was until I starting partitioning. That will drive you nuts. 🙂
July 9, 2012 at 2:31 pm
tim.cloud (7/9/2012)
True, more maintenance. However, I had no idea what maintenance was until I starting partitioning. That will drive you nuts. 🙂
Very true.
- 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
July 10, 2012 at 12:48 am
J Livingston SQL (7/9/2012)
Andy Hyslop (7/9/2012)
It's a little extra work, but I always try to add a new column 'in the right place' where applicable. I concede that it doesn't actually make any difference from a technical standpoint however it pays in terms of 'readability' for me and others who may have to support my database.
This is, of course, a personal preference
+1 I agree 100%! 🙂
hmmm....how do you define "readabilty"..?
do you arrange columns by col_name alphabetically , by data_type, ??
just playing devil's advocate 😛
Well, when I create a table I give some thought to the order of the columns (and I think at some level most of us do) so why should it be any different for columns I add afterwards?
July 10, 2012 at 6:36 am
David McKinney (7/10/2012)
J Livingston SQL (7/9/2012)
Andy Hyslop (7/9/2012)
It's a little extra work, but I always try to add a new column 'in the right place' where applicable. I concede that it doesn't actually make any difference from a technical standpoint however it pays in terms of 'readability' for me and others who may have to support my database.
This is, of course, a personal preference
+1 I agree 100%! 🙂
hmmm....how do you define "readabilty"..?
do you arrange columns by col_name alphabetically , by data_type, ??
just playing devil's advocate 😛
Well, when I create a table I give some thought to the order of the columns (and I think at some level most of us do) so why should it be any different for columns I add afterwards?
Because it's an arbitrary standard. It takes work, causes problems (like the ones I mentioned with large datasets, replication, transaction log bloat, etc.), and has absolutely no actual advantages at all. None. At best, it saves you a small fraction of a second when writing a Select statement.
So, it's all cost with no actual gain. Really. Any gain from it is an illusion created by the user-interface you're working with (probably Management Studio), and can be replicated with nearly zero effort, without the risks and costs associated with routinely dropping and re-creating tables in a production environment.
- 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
July 10, 2012 at 6:44 am
I strongly suggest then that you keep doing it your way 😉
July 10, 2012 at 7:08 am
David McKinney (7/10/2012)
I strongly suggest then that you keep doing it your way 😉
Yep.
(Wouldn't go so far as to call it my way. More like Codd's and Date's way. After all, the original definition of "relational data" explicitly says that order of rows and columns is immaterial. I got the idea from them.)
Edit: Though, of course, I disagree with them on a number of other things. Consistency isn't one of my strong points. 🙂
- 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
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply