A Fine Slice Of SQL Server
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.
Leave a CommentAaron 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.
Leave a CommentJosh 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.
Leave a CommentRobert 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.
Leave a CommentRandolph 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 anXMLcolumn.The
XMLdata 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.
Leave a CommentRandolph 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
DATETIME2disagreed 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 forDATETIME, 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 closedAaron 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 closedSo 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.
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