These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Jonathan RozenblitDeveloper Evangelist
Susan IbachDeveloper Evangelist
Dates, we store them everywhere, DateOrdered, DateEntered, DateHired, DateShipped, DateUpdated, and on and on it goes. Up until and including SQL Server 2005, you really didn’t have much choice about how you stored your date values. But in SQL Server 2008 and higher you have alternatives to DateTime and they are all better than the original.
DATETIME stores a date and time, it takes 8 bytes to store, and has a precision of .001 seconds
In SQL Server 2008 you can use DATETIME2, it stores a date and time, takes 6-8 bytes to store and has a precision of 100 nanoseconds. So anyone who needs greater time precision will want DATETIME2. What if you don’t need the precision? Most of us don’t even need milliseconds. So you can specify DATETIME2(0) which will only take 6 bytes to store and has a precision of seconds. If you want to store the exact same value you had in DATETIME, just choose DATETIME2(3), you get the same precision but it only takes 7 bytes to store the value instead of 8. I know a lot of you are thinking, what’s one byte, memory is cheap. But it’s not a question of how much space you have on your disk. When you are performance tuning, you want to store as many rows on a page as you can for each table and index. that means less pages to read for a table or query, and more rows you can store in cache. Many of our tables have multiple date columns, and millions of rows. That one byte savings for every date value in your database is not going to make your users go ‘Wow everything is so much faster now’, but every little bit helps.
If you are building any brand new tables in SQL Server 2008, I recommend staying away from DATETIME and DATETIME2 altogether. Instead go for DATE and TIME. Yes, one of my happiest moments when I first started learning about SQL Server 2008 was discovering I could store the DATE without the time!! How many times have you used GETDATE() to populate a date column and then had problems trying to find all the records entered on ‘05-JUN-06’ and got no results back because of the time component. We end up truncating the time element before we store it, or when we query the date to ignore the time component. Now we can store a date in a column of datatype DATE. If you do want to store the time, store that in a separate column of datatype TIME. By storing the date and time in separate columns you can search by date or time, and you can index by date and or time as well! This will allow you to do much faster searches for time ranges.
Since we are talking about the date and time datatypes, I should also mention that there is another date datatype called DATETIMEOFFSET that is time zone aware. But that is a blog for another day if you are interested.
Here is a quick comparison of the different Date and Time Data types,
Today’s My 5 is of course related to the Date and Time datatypes.
My 5 Important Date functions and their forward and backwards compatibility
Also one extra note, because I know there are some former Oracle developers who use this trick. If you have any select statements where you select OrderDate+1 to add one day to the date, that will not work with the new date and time datatypes. So you need to use the DATEADD() function.
The precision of DATETIME is actually 0.003 seconds, not 0.001
Nice idea to split, we do a lot of grouping by date which would be faster if we had a date only column instead of datetime.
However, is there even the most smallest chance that when creating a new row, (with the date and time fields being auto-generated when the row is created), that the date and time could be on different days, if it inserts at midnight (even if it is a one in one billion chance!)?
@Filip you are correct, I have updated the table to reflect the correct precision
Interesting question. I created the following table using default values of SYSDATETIME for columns of type DATE, TIME, and DATETIME2.
CREATE TABLE TimingIsEverything
(id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
dateentered DATE DEFAULT SYSDATETIME() NULL,
timeentered TIME DEFAULT SYSDATETIME() NULL,
DateTimeentered DATETIME2 DEFAULT SYSDATETIME() NULL,
description VARCHAR(50) NULL)
Then I inserted three rows into the table
INSERT INTO timingiseverything
('so what '),
With the nanosecond precision for SYSDATETIME I was curious to see if there would be a difference in the values across columns and rows.Here are the results
id dateentered timeentered DateTimeentered description
-- ------------ ---------------- --------------------------- --------------
1 2011-06-23 13:16:29.7471443 2011-06-23 13:16:29.7471443 Hello
2 2011-06-23 13:16:29.7471443 2011-06-23 13:16:29.7471443 so what
3 2011-06-23 13:16:29.7471443 2011-06-23 13:16:29.7471443 happens
So as you can see the times did not change even when stored at the nanosecond level. I would be curious to try this on a table that had multiple indexes to be updated and a million rows that were fragmented, just to see if I could get a difference before stating unequivocally they will always be the same. I will see if I can do a little digging and get an official/definitive answer one way or another.
@Fin, because you list SYSDATETIME() twice it is likely called twice so I think the only way to be absolutely sure that you are getting a DATE and TIME that are from the exact same point in time is to use a program or STORED PROCEDURE. Call SYSDATETIME or an equivalent function to get the current date and time, store it in a variable, and pass that variable to the table to populate the columns. That way SYSDATETIME() is only called once and is guaranteed to have the same value for both columns
something like this
CREATE PROCEDURE AddNewTimingRecord
DECLARE @TodaysDate DATETIME2 = SYSDATETIME()
INSERT INTO timingIsEverything
(dateEntered, TimeEntered, Description)
(@TodaysDate, @TodaysDate, @Description)
Thanks Susan, yeah stored procedures would solve the problem. I was just wondering as my typical setup would be using Linq to Entities to insert into the database, with my date field having a default of GETDATE(). For all practical purposes its not really an issue anyway!
This does not help you answer the question but I think it would be a bug in SQL Server if the times were different because the INSERT should be a set-based operation. Therefore the data to be inserted should be determined as a snapshot at a single point in time. I.e. even though effectively a cursor may be used internally to produce the result (making you think the times could be different), the outcome should be as if the full INSERT happened instantaneously.
One more little gotta. If you're using an "older" version of Office (2007) as a front-end (Access in particular), it doesn't recognize the new data types. (Haven't checked Office 2010 yet). This is a problem when using date/time functions within the Office products.
You bring up an excellent point in general to consider whenever you are looking at new datatype: Client side support! You absolutely need to consider the client tools and code that will be accessing the database to make sure they support a datatype before implementing it.
I'm confused; your prose sys that DATETIME has a precision of 0.001 seconds, but then your chart shows a precision of 0.00333 seconds. Which is correct?
Hey Tarzan 0.00333 is correct, I had a mistake in the original blog and corrected it in the chart, but forgot it was mentioned in the text, sorry for the confusion. The chart is correct!
OK, I did go consult with a few more SQL gurus on the question about whether two columns in the same table using a DEFAULT SYSDATETIME() would ever return different values. Here is what I heard back from someone who brought it up with a group of SQL masters.
"I imagine they would have the same value, but this is not how I would implement this. Instead, why not just create computed fields based on the datetime as in the below. This guarantees that all of the fields are always in sync. You can index on the computed fields or declare persisted for storage-tradeoff to performance, so I don’t see any advantage over using explicit columns versus computed the date/time derived fields."
CREATE TABLE importantStuff
(id INT IDENTITY(1,1) NOT NULL, description VARCHAR(30),
dateTimeEntered DATETIME2 DEFAULT SYSDATETIME(),
dateEntered AS CONVERT(DATE, DateTimeEntered),
timeEntered AS CONVERT(TIME, DateTimeEntered)
Thanks for a great discussion!