September 9, 2008 at 7:49 am
HI all,
Can anyone see an advantage to having a smalldatetime field(no time stored e.g 00:00:00) and a timestamp field, for recording the date and time a record was last updated.
As apposed to just having a smalldatetime that holds the time as well.
Also , how can I convert a timestamp to something that is understandable in english?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 9, 2008 at 7:53 am
sqlserver timestamp is "for internal use only" !!
This kind of columns will be updated by sqlserver itself with every modification to the row !!
This is not a datetime column !!!
We use a datetime_updated column that has a default value getdate and is updated by a trigger for update every time the row is modified.
Check BOL !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 9, 2008 at 7:57 am
Thanks for the reply 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 9, 2008 at 8:08 am
As for your first question, it is quite an interesting one to check. I think, If you have just date with no time your index on that column wont be huge, and it will be more effective. It would be a good test to do. Will get back to you on that one.
September 9, 2008 at 8:45 am
It will only be of benefit if you actually use the time without the date. If you query for records that have a time between 8 and 9 AM regardless of date, having an index on just the time is useful. If you always look for a specific date and time, you would want a composite index anyway and having the data in a single datetime will be more efficient for seeks and take up less space.
September 9, 2008 at 12:59 pm
For tables that often need to be queried on time, without date, I've found that calculated columns (generally with an index on them) is a great solution. Keep the datetime/smalldatetime intact, and use calculated columns to pull out the parts if you query them often enough.
- 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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply