Just a Quick Question
-
Hi All,
Is this correct, when creating tables within SQL Server is this the number 1 thing you must do is add two columns which are CreatedBy and CreatedDate even if your not going to use them?
P.s sorry for the lame Subject name i tried to change it after but i cant.
-
The only requirement for creating a table within SQL Server is that the table must have at least one column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
SQL Server does not force you to add these columns.
It may be your company policy. If it is your company standard, you need to follow it.
In my previous company, we had this database standard.
-
Hi thanks for the information,
Its not in the company standard its my line manger which has said its the number 1 rule every SQL person follows, which i did find hard to believe at the time,
I have a table which doesnt require CreatedBy CreatedDate so whats the point putting them in so im not going to.
Thanks for the information.
-
If it is your shop's requirements and policies, then obviously you need to comply with that. There is no technical hard and fast rule that is needed though. Those fields are typically used for auditing. A trigger often times fills that data in without direct and explicit setting.
-
No, I wouldn't say every table needs it at all. It's a business call. BTW, what about UpdateDate and UpdateBy? And are we doing physical deletes? Instead we could use DeleteDate & DeleteBy and do logical deletes (although query performance could get messy there, maybe move the data to a deleted table instead). There's lots of stuff you could do, but none of that I would do automatically. Also, instead of messing with the structure of the table, why not just use Change Data Capture?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
.Netter (5/7/2012)
Its not in the company standard its my line manger which has said its the number 1 rule every SQL person follows, which i did find hard to believe at the timeWell I don't do that in tables, so it's definitely not every SQL person. If I need auditing, I prefer proper auditing that will tell me who, when, from where, etc a row was created, with what values and who, when, from where, etc changed it and what exactly they changed
CreatedBy and CreatedOn don't require triggers, defaults are far better for those. ModifiedBy and ModifiedOn do require triggers, but unless there's a reason to just know who changed a row without having any idea what they changed, there's probably not that much value in them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
.Netter (5/7/2012)
Its not in the company standard its my line manger which has said its the number 1 rule every SQL person follows, which i did find hard to believe at the time,Oh, well that explains how he got promoted to manager then.
The probability of survival is inversely proportional to the angle of arrival.
-
sturner (5/7/2012)
.Netter (5/7/2012)
Its not in the company standard its my line manger which has said its the number 1 rule every SQL person follows, which i did find hard to believe at the time,Oh, well that explains how he got promoted to manager then.
You have no idea what i go through at work! you some times have to stop and think how people get in to the positions there in!
I understand what every one has written and i appreciate your time on this subject, but my point was if im not going to use them columns then why have them? if this application i was building was going to be used by more then 3 admins that sit close to one another then yes i would create them columns but its not.
-
.Netter (5/7/2012)I understand what every one has written and i appreciate your time on this subject, but my point was if im not going to use them columns then why have them? if this application i was building was going to be used by more then 3 admins that sit close to one another then yes i would create them columns but its not.
I am totally agree with you but take it in other way around, database/schema designs are not done on current requirements/structures only but also keeping future in mind to. that's called proactive approach and Good DBAs are famous for that.
So bottom line is before making any final decision or keeping yourself in any doubtful situation like this better to sit with the people and discuss these things
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
Bhuvnesh (5/8/2012)
I am totally agree with you but take it in other way around, database/schema designs are not done on current requirements/structures only but also keeping future in mind to. that's called proactive approach and Good DBAs are famous for that.I'm all for extensible designs, but adding columns just because they might be needed in the future is not a good proactive approach, it's a waste of space.
Design tables according to best practices and business requirements, normalise properly and design any required extensions with the same care as the initial tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (5/8/2012)
Bhuvnesh (5/8/2012)
I am totally agree with you but take it in other way around, database/schema designs are not done on current requirements/structures only but also keeping future in mind to. that's called proactive approach and Good DBAs are famous for that.I'm all for extensible designs, but adding columns just because they might be needed in the future is not a good proactive approach, it's a waste of space.
Design tables according to best practices and business requirements, normalise properly and design any required extensions with the same care as the initial tables.
+1000000
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Artoo22 (5/8/2012)
Firstly, SQL Server already stores created_date.SELECT name, create_date, modify_date
FROM sys.tables
That's the date the table was created, not the date that a particular row in the table was created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply