A Fine Slice Of SQL Server
Brent Ozar is a fan of integers:
I love integers.
Furthermore, I think we’ve all been taking them for granted.
Integers are clean, elegant datatypes that do exactly what they’re supposed to. They’re the Golden Retrievers of the database world: they have a few shortcomings, but so, so few. And so for this month’s T-SQL Tuesday, I wanna share my love for integers with you.
Click through for four things to like.
Comments closedEitan Blumin likes an underappreciated data type:
The xml data type in SQL Server exists since about SQL Server 2005, and it introduced a lot of very powerful and useful capabilities that were never before seen in SQL Server. It was the first-ever data type with built-in CLR methods (i.e. where you can write a dot after a column name and execute some kind of method. For example: mycolumn.nodes(…)).
The xml data type and the functionality around it made it relatively easy to “refactor” a resultset from a relational structure into a scalar structure (i.e. a single XML document) using the FOR XML directive, and vice versa (single XML document into a relational structure) using the nodes(), value(), and query() methods.
Show me a thousand posts and I don’t expect to see XML show up as a favorite type more than three or four times. Eitan also shares a least favorite type, and I do expect that one to show up on the most-hated list quite frequently.
Comments closedLina Kovacheva reviews SQL Server’s numerical data types:
SQL Server provides two dedicated data types for storing monetary values. You can think of MONEY and SMALLMONEY as DECIMAL – Money is effectively the same as DECIMAL(19,4) while SMALLMONEY is effectively the same as DECIMAL(10,4). If you are planning to use MONEY you should have in mind that performing division and multiplication can cause rounding errors that result in the unintentional loss of precision. The cause of the problem is that MONEY only saves information up to the 4th decimal place and if your multiplication or division results in an integer that goes to the 5th decimal place or more, MONEY will round it off, causing an accuracy error.
Click through for an analysis of these data types.
Comments closedKevin Chant is a fan of the bit type:
I decided to tweak it a bit for this post, to provide a humorous comparison between the bit data type and others that are available in SQL Server. In reality, this won’t cover every single one.
By the end of this post, you will some see pitfalls to using certain data types and some tips on how to avoid them. Plus, you will find out which data type is my least favourite to use.
Click through for a “haha-just-serious” take on an underappreciated datatype which nonetheless can’t decide if it’s a boolean or not.
Comments closedWe have an application that uses
datetimecolumns in a number of places. All of the users have always been in Eastern Time, but now we have a request to introduce users from a different time zone (Central Time) into the system. The lack of time zone information in our dates and times now presents a problem.The system needs to communicate to users how long ago something occurred, or a time in the future that something needs to be done. If an Eastern Time user enters in a “follow up time” of today at 2:00 pm, a Central Time user could log in, see that, and end up being an hour late following up with their customer.
Click through for the process and several bugaboos you might run into. What we’ve done was to force all application times in UTC in DATETIME or DATETIME2 format and then store user preferences on time zone in the application, translating from UTC to the relevant time zone at that level.
Comments closedRandolph West answers one of the mysteries in life:
By a show of hands, who uses the
sql_variantdata type? In my experience the answer is “no one,” and that’s not necessarily a bad thing. There’s a general philosophy in our industry where we assume that columns are a specific data type. This predictability makes writing queries and applications against those columns easier. It’s difficult enough dealing withNULLvalues, so adding confusion with handling data conversions is an invitation to introduce bugs.
The sql_variant data type is a bit of a relic. I think the people who used that were also big variant data type users in Visual Basic. Nonetheless, Randolph clarifies how SQL Server stores this data.
Shabnam Watson runs an experiment:
As you may have already heard, one of the easiest ways to reduce a Power BI model (dataset) size is by splitting DateTime columns into separate Date and Time columns but the question is how much space reduction can you achieve by doing so. As I show in this blog post, the reduction can be significant and up to % 80 or % 90 depending on the number and cardinality of the datetime columns.
That’s a lot of savings.
Comments closedAaron Bertrand went down a rabbit hole:
I spent 20 minutes the other night thinking about possible ways to remove an
ntextcolumn from a 28TB table, and I did all of this thinking for nothing.I was investigating the table for other reasons. And because I’m lazy, I scanned the output of the system procedure
sys.sp_columns(instead of writing a proper query against more modern metadata). I spotted the legacy type in the output, and dropped everything.
I’m pretty sure I’ve never seen sp_columns before. For everyone in the same boat, continue dutifully avoiding it.
Edwin Sanchez takes a look at SQL Server’s GEOGRAPHY data type:
A common application in geography spatial data is called the nearest neighbor query. In this query, you want to know how far or close something is to another object or place.
In my post about the SQL graph, I described how a customer could find the nearby restaurants from the current location. Let’s refer to that example again:
Read on for a primer on the data type and some useful examples.
Comments closedJosh Darnell points out a case where not specifying nullability on columns can lead to confusion:
I always indicate whether a column allows
NULLor not inCREATE TABLEandALTER TABLEstatements.Or, you know, I’m in the habit of doing that.
Like, I try to be in the habit.
I’m doing my best, okay?
Anyway, it’s good to be clear about nullability, because you can run into surprises when you aren’t.
Click through for a surprise.
Comments closed