Press "Enter" to skip to content

Category: Data Types

Migrating DATETIME Columns to DATETIMEOFFSET

Published 2020-11-12 by Kevin Feasel

Josh Darnell makes a change:

We have an application that uses datetime columns 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 closed

How SQL Server Stores sql_variant Data

Published 2020-10-01 by Kevin Feasel

Randolph West answers one of the mysteries in life:

By a show of hands, who uses the sql_variant data 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 with NULL values, 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.

Comments closed

Space Savings from Separate Date and Time Columns in Power BI

Published 2020-09-22 by Kevin Feasel

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 closed

Avoid sys.sp_columns

Published 2020-09-21 by Kevin Feasel

Aaron Bertrand went down a rabbit hole:

I spent 20 minutes the other night thinking about possible ways to remove an ntext column 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.

Comments closed

Using the Geography Data Type

Published 2020-09-16 by Kevin Feasel

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 closed

The Importance of Specifying Nullability

Published 2020-09-14 by Kevin Feasel

Josh Darnell points out a case where not specifying nullability on columns can lead to confusion:

I always indicate whether a column allows NULL or not in CREATE TABLE and ALTER TABLE statements.

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

Performance Impacts of Computed Columns

Published 2020-09-14 by Kevin Feasel

Robert Sheldon takes us through a few scenarios:

In this article, I walk you through the process of applying these strategies so you have a better sense of the available options. For the examples, I created four similar tables and populated them with identical data, which comes from the WideWorldImporters sample database. Each table includes the same computed column, with the column persisted in two tables and indexed in two tables, resulting in the following mix:

– The Orders1 table includes a non-persisted computed column.
– The Orders2 table includes a persisted computed column.
– The Orders3 table includes an indexed, non-persisted computed column.
– The Orders4 table includes an indexed, persisted computed column.

For each table, I show you the execution plan that’s generated when querying the computed column. The column’s expression is a relatively simple one, and the data set very small. Even so, this should be enough to demonstrate the principles of creating persistent and indexed computed columns and how they can help address performance-related issues.

Click through for Robert’s results.

Comments closed

How SQL Server Stores XML Data

Published 2020-09-10 by Kevin Feasel

Randolph West continues a series on internal storage of data types:

The SQL Server XML data type supports both schema-defined and schema-less XML documents (typed and untyped), and partial documents. Partial documents are still considered well-defined XML, but don’t have surrounding <?xml>...</xml> tags for example. Either way, the storage engine validates the data when inserting or updating into an XML column.

The XML data type lets us store up to 2GB per column per row, but it’s prudent here to note that just because we can, it doesn’t mean we should.

Click through for the details.

Comments closed

DATETIME2 and Storage Size

Published 2020-09-03 by Kevin Feasel

Randolph West digs into an issue:

Two years ago I wrote a post that got a lot of traction in the comments at the time. Last month there was renewed interest because one of the commenters noted that the official SQL Server documentation for DATETIME2 disagreed with my assertions, and that I was under-representing the storage requirements.

To remind you, I have been saying for years that you can use DATETIME2(3) as a drop-in replacement for DATETIME, and have better granularity (1ms versus 3ms) for 12.5% less storage (1 byte per column per row). The commenter intimated that because my statement conflicted with the documentation, that I must be wrong. As it turns out the documentation was wrong, but I also learned something new in the process!

It’s an interesting internal look at how difficult it is to get documentation right, even on something which sounds simple.

Comments closed

When Altering a Table Blocks the Log Reader

Published 2020-08-20 by Kevin Feasel

Aaron Bertrand walks us through a painful scenario:

We recently performed a DDL operation against a SQL Server table – simply increasing the size of a varchar column – which should have been instantaneous. Instead, we killed the SQL Server process after observing 20 minutes of HARD_SYNC_COMMIT waits and a blocked replication log reader. Could this issue have been avoided? What went wrong?

I spotted the issue pretty quickly, but it’s easy to miss in a code review. Read the whole thing.

Comments closed