Dave Mason investigates the valid data types you can use as inputs for WAITFOR:
There are certain design patterns in T-SQL that give me pause. They may not be “code smells” per se, but when I encounter them I find myself thinking “there’s got to be a more sensible way to accomplish this”. WAITFOR DELAY is one example. I’ve used it a few times here and there, mostly in one-off scripts. If I wrote some code that used it regularly in production, I’d be paranoid about putting my SPID to sleep forever. Maybe a little bit of paranoia is a good thing. But I digress.
A recent task found its way to me, and I’ve decided to use WAITFOR DELAY as part of my solution. (It hasn’t been tested or implemented yet–perhaps more on this in another post.) My usage this time has been more complex than in the past. What I already knew is that you can use a string literal for the time_to_pass argument. For example, this will delay for 3½ seconds:
WAITFOR DELAY '00:00:03.500'
Click through for a bunch of testing.
Solomon Rutzky digs into UTF-8 support in SQL Server 2019 and has found a few bugs:
Let’s start with what we are told about this new feature. According to the documentation, the new UTF-8 Collations:
can be used …
- as a database-level default Collation
- as a column-level Collation
- by appending “
_UTF8” to the end of any Supplementary Character-Aware Collation (i.e. either having “_SC” in their name, or being of level140or newer)- with only the
CHARandVARCHAR
(implied) have no effect on
NCHARandNVARCHARdata (meaning: for these types, the UTF-8 Collations behave the same as their non-UTF-8 equivalents
“This feature may provide significant storage savings, depending on the character set in use.” (emphasis mine)
Solomon takes his normal, thorough approach to the problem and finds several issues.
Bert Wagner shows us a good way of converting strings to numbers when multiple cultures are in play:
Why are the salaries stored as nvarchar and formatted with commas, spaces, and periods?
Great question! Someone wanted to make sure these amounts would look good in the UI so storing the formatted values in the database would be the way to go…
Pretty for the UI, not really great for needing to do analysis on.
Admittedly, TRY_PARSE is slow, but for a one-time conversion as you try to fix a bad idea, it’s fine.
Randolph West looks at the AT TIME ZONE clause when working with a specific time zone in SQL Server:
The time zone name is taken from a list maintained in the following Windows registry hive:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time ZonesNote: For SQL Server on Linux (and Docker containers), a registry shim performs the same function as the Windows registry by intercepting the API calls and returning the expected value(s).
We can also use a Transact-SQL (T-SQL) query against the system view
sys.time_zone_info, which uses the information from the registry hive. This is the recommended method if you do not have access to the registry hive.
Click through for a couple of examples.
Kendra Little explains how the AVG() function works with a couple different data types:
This week’s Quizletter featured a quiz on using the AVG() function in SQL Server.
I was inspired to write this quiz because I’ve been teaching TSQL School each week. When we covered aggregate functions, I remembered just how tricksy and non-average that AVG() can be.
Want to take the quiz before you read the answers?
Head over here and take the quiz first.
Or read this post and then take the quiz, if you prefer. I’m not gonna call it cheating, because it’s learning either way.
Kendra explains each of the answers, so I’d recommend taking the quiz first.
Ellen Talbot gives us an overview of the different data types in R:
Now here’s something we didn’t cover in the video and is especially helpful if something just WILL NOT work and you’ve spent all morning panic eating biscuits.
You can write checks to see if something is numeric, or an integer, with
is.numeric()oris.integer().The general “‘is.XXXXX()’” function will take many of the data types we cover here and more, and can be a real time/life saver.
We could also use
class()here and inspect the result.^[You might recall thatclass(1)had the result of “numeric” – R was not by default considering 1 as an integer for the purpose of theclass()function. ### Special numbers As well asito denote imaginary numbers, there are some additional symbols you might encounter or want to use.
There’s a video as well as a full blog post.
Randolph West hits us with a misnamed SQL Server data type:
It occurred to me that we haven’t covered the
TIMESTAMPdata type in this series about dates and times.
TIMESTAMPis the Windows Millennium Edition of data types. It has nothing to do with date and time. It’s a row version. Microsoft asks that we stop calling itTIMESTAMPand useROWVERSIONinstead.Much like
DECIMALis a synonym ofNUMERIC, so too isTIMESTAMPa synonym ofROWVERSION. Please call it aROWVERSIONand pretend thatTIMESTAMPdoesn’t exist. Microsoft is deeply sorry for the confusion.
As I say, dates and times are hard. But at least this is easy: if you don’t use it, you won’t have problems with it.
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.
Kevin Feasel
2018-10-29
Data Types, Syntax