Press "Enter" to skip to content

Category: Data Types

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

When Date Tables Go Bad

Published 2020-08-05 by Kevin Feasel

Brent Ozar walks through a scenario in which a calendar table (AKA, date dimension) makes a query perform quite a bit worse:

So why did the date table not perform as well as the old-school way?

SQL Server doesn’t understand the relationship between these two tables. It simply doesn’t know that all of the rows in the Users table will match up with rows in the calendar table. It assumes that the calendar table is doing some kind of filtering, especially given our CAST on the date. It doesn’t expect all of the rows to match.

My reaction was pretty much the same as Koen Verbeeck’s in the comments. Put in clearer terms, calendar tables work best when you’re joining a DATE type to a DATE type. Once you introduce times into the mix, the optimizer has to behave differently, not least because you have to do things like CAST() to coerce data types.

Comments closed

When NULL Makes Sense

Published 2020-07-09 by Kevin Feasel

Chris Johnson shares some rules of thumb on when allowing a column to be nullable makes sense:

Now I want to talk a bit about when we should be using NULLs and when we shouldn’t, inspired again by some things I’ve seen in legacy code a few days ago. And when I say using, I mean when should we allow values to be NULL, in tables, and parameters, and anything else.

The first thing to remember is what does a NULL represent. It’s very simple, NULL means “I don’t know”. If you keep that in mind, and you’re practising some good development design where everything has a point, you should be able to tell if it’s possible to be unsure about any particular value for a row.

One of the tricky parts about NULL values and legacy code is that if I need to add a new column and there is not a good default, either I make the column nullable (regardless of whether it should be) or I take downtime by blocking table access until my change is in place. As a result, quite often, I simply need to make something nullable because I can’t afford to block the table that long.

Comments closed

Expanding INT to BIGINT Without (Much) Downtime

Published 2020-06-22 by Kevin Feasel

Michael J. Swart finds a creative way around a problem, and then a creative way around the problem it causes, and so on:

The table has over 2 billion rows and it looks like it’s going to run out of space soon because the LogId column is defined as an INT. I need to change this table so that it’s a BIGINT. But changing an INT to a BIGINT is known as a “size of data” operation. This means SQL Server has to process every row to expand the LogId column from 4 to 8 bytes. But it gets trickier than that.

The biggest challenge is that the table has to remain “online” (available for queries and inserts).

Compression?
Gianluca Sartori (spaghettidba) had the idea of enlarging the columns with no downtime using compression. It’s promising, but I discovered that for this to work, all indexes need to be compressed not just the ones that contain the changed column. Also, any indexes which use the column need to be disabled for this to work.

Cheating
I gave up on solving this problem in general and constrained my focus to the specific problem I was facing. There’s always some context that lets us bend the rules. In my case, here’s what I did.

Read the whole thing. Also, as soon as I saw Michael’s title, I immediately thought of this:

Comments closed