Louis Davidson wants to see what he can cast to a bit type:
There are no other textual/alpha string values that will cast to a bit value, but the numeric values that will cast to a bit are voluminous (even some that are in string format). Consider the following eight statements:
SELECT CAST(100 AS bit);SELECT CAST(-100 AS bit);SELECT CAST(99999999999999999999999999999999999999 AS bit);SELECT CAST(-99999999999999999999999999999999999999 AS bit);SELECT CAST(88.999999 AS bit);SELECT CAST('1' AS bit);SELECT CAST('2' AS bit);SELECT CAST('999999' AS bit);Danged if they didn’t all work, and all return 1.
Check out what else Louis tries to cast to a bit type.
Randolph West continues his date and time data type series:
DATETIMEOFFSETworks the same way as theDATETIME2data type, except that it is also time zone aware. It is formatted as'YYYY-MM-DD HH:mm:ss[.nnnnnnn][{+|-}hh:mm]'.Got all that?
YYYYrepresents a four-digit year,MMis a two-digit month between 1 and 12,DDis a two-digit day between 1 and 31 depending on the month,HHrepresents a two-digit hour between 0 and 23,mmis the minutes between 0 and 59, whilessis the number of seconds between 0 and 59. Once again,nrepresents between zero and seven decimal places in a fraction of a second.The main difference from
DATETIME2is the time zone offset at the end, which is the number of hours and minutes as an offset from UTC time.
Read on for more. I generally don’t use this date type much, preferring to stick with DATETIME2 and saving data as UTC.
Randolph West continues his SQL Server date & time data types series:
SQL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the
DATETIMEandSMALLDATETIMEtypes that we looked at previously.This week, we look at the
DATETIME2data type. I’m not the first person to think that this was probably not the best name for a data type, but here we are, a decade later.
DATETIME2is, at its heart, a combination of theDATEandTIMEdata types we covered in previous weeks.DATEis 3 bytes long andTIMEis between 3 and 5 bytes long depending on accuracy. This of course means thatDATETIME2can be anything from 6 to 8 bytes in length.
Nowadays, if you want to store a date plus time, this should be your default, not DATETIME.
Randolph West covers the TIME data type in SQL Server:
This week, we look at the
TIMEdata type. It is formatted asHH:mm:ss.fffffff, whereHHis hours between 0 and 23,mmis minutes between 0 and 59,ssis seconds between 0 and 59, andfrepresents 0 or more fractional seconds, up to a maximum of seven decimal places.With a maximum length of 5 bytes,
TIMEcan store a value with a granularity of up to 100 nanoseconds.
I tend not to use TIME very often. It’s useful if you need it, but I rarely find myself needing a dateless time.
Randolph West continues his dates and times series:
QL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the previous
DATETIMEandSMALLDATETIMEtypes that we looked at previously.The first one we look at this week is
DATE. WhereasDATETIMEuses eight bytes andSMALLDATETIMEuses four bytes to store their values,DATEonly needs a slender three bytes to store any date value between0001-01-01and9999-12-31inclusive.
The DATE data type was a fantastic addition to SQL Server 2008.
Randolph West argues against using the SMALLDATETIME data type:
But let’s say you don’t need that kind of accuracy and are happy with a granularity to the nearest minute. Maybe you’re storing time cards and don’t think it’s necessary to store seconds. As discussed in the Fundamentals series, you really want to choose the most appropriate datatype for your data.
Enter
SMALLDATETIME, which rounds up or down to the nearest minute. The seconds value for anySMALLDATETIMEis 00. Values of 29.999 seconds or higher are automatically rounded up to the nearest minute, while values of 29.998 seconds or lower are rounded down.
Read on to see Randolph’s explanation of why he recommends against using SMALLDATETIME.
Randolph West gets into the DATETIME data type:
DATETIMEis an eight-byte datatype which stores both a date and time in one column, with an accuracy of three milliseconds. As we’ll see though, the distribution of this granularity may not be exactly what we’d expect.Valid
DATETIMEvalues are January 1, 1753 00:00:00.000, through December 31, 9999 23:59:59.997. On older databases designed prior to SQL Server 2008, because there was no explicit support for date values, it was sometimes customary to leave off the time portion of aDATETIMEvalue, and have it default to midnight on that morning. So for example today would be stored asFebruary 21, 2018 00:00:00.000.
If you’re not particularly familiar with SQL Server data types, this is detailed enough information to get you going and to explain exactly why you shouldn’t use DATETIME anymore…
Bill Fellows has a pop quiz for us:
Given the following DDL
CREATE TABLE dbo.IntToTime ( CREATE_TIME int );What will be the result of issuing the following command?
ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME time NULL;Clearly, if I’m asking, it’s not what you might expect.
Click through if you have not memorized your implicit conversion tables.
Koen Verbeeck argues that date keys in warehouses should be actual date types:
The worst are by far the string representation, as there is no actual check on the contents. It can literally contain everything. And is ’01/02/2018′ the first of February 2018 (like any sane person would read, because days come before months), or the 2nd of January? So if you have to store dates in your data warehouse, avoid strings at all costs. No excuses.
The integer representation – e.g. 20171208 – is really popular. If I recall Kimball correctly, he said it’s the one exception where you can use smart keys, aka surrogate keys that have a meaning embedded into them. I used them for quite some time, but I believe I have found a better alternative: using the actual date data type.
I bounce back and forth, but I’m sympathetic to Koen’s argument, which you can read by clicking through.
Raul Gonzalez on (in)appropriate use of National character strings:
Yes, you have read it… I see dates stored as NVARCHAR(10) and NCHAR(10) on daily basis, please don’t ask me why.
This case is even worse, because DATE takes 3 bytes where NCHAR(10) takes 20 bytes, yes Ladies and Gentlemen more than 6 times more space to store the same data.
But wait! how can you be certain that those ten characters are actually a valid date? You can’t, unless you reinvent the wheel and validate that those dates are obviously valid dates and pay the performance penalty of doing it.
You’d think that picking the right data type for something would be fairly easy and then you find a table with a few dozen NVARCHAR(MAX) columns.
Kevin Feasel
2018-04-02
Data Types, T-SQL