Press "Enter" to skip to content

Category: T-SQL

The Pain of Code Noise

Published 2020-04-22 by Kevin Feasel

Chris Johnson talks about a concept dear to me:

Basically code noise is anything that pulls your attention away from what the code is supposed to be doing, or obscures the true nature of the code in some way. It’s not something we consider enough when writing T-SQL code, but I think there is a lot to be said for writing code the next person will be able to read.

As a small example, I was debugging something recently and found that all of the insert statements had ORDER BY clauses. I couldn’t work out why these were making me so angry, after all it’s not doing anything to hurt performance, and in fact isn’t doing anything at all, until one of the other devs in the office pointed out that it’s one example of the code noise that the whole code base is filled with.

Chris provides us a couple examples of noise. My bottom line on this is, develop to the minimum required standards of what the computer needs (i.e., accurate data, fast enough, etc.) and give the humans maintaining the code a fighting chance. Spend more time making it easy for humans and make everybody’s life easier.

Comments closed

Set Comparisons with EXCEPT

Published 2020-04-21 by Kevin Feasel

John Sterrett walks through one of the most important set operators:

Selecting the two tables is easy if we know what change occurred and there aren’t many changes. This can get complicated quickly. Therefore, if we just want to quickly know if we have differences lets take a look at my goto method using EXCEPT. To make this example easier to read instead of using “SELECT *” I will just focus on columns that are changing. In a real example, I would want to know if any columns changed.

One of the nicest things about set comparison operators is that they innately understand the concept of NULL.

Comments closed

Decoding Statistics Names

Published 2020-04-20 by Kevin Feasel

Jason Brimhall explains how SQL Server comes up with names for auto-created statistics:

Every now and again I am asked about the meaning behind the automatically generated names for statistics in SQL Server. The quick answer is short, sweet and really easy. I give them a quick explanation and then often refer them to the blog post by Paul Randal on the topic.

The better answer is to show them what the auto-generated names really mean, alongside the great explanation from Paul. Finally, after years of the topic being on my backlog, I am sharing a script that will help decode those names and help to prove out fully what’s in a statistic name.

The proof is in the SQL; click through to see it.

Comments closed

Querying Database and Log File Sizes with T-SQL

Published 2020-04-17 by Kevin Feasel

Allen White takes us through an easy technique to check database and log file sizes:

As a consultant, I have to be able to quickly spot problems, and one of the problems I frequently find is transaction log files that are incorrectly sized.

There are two catalog views in the master database which make this easy to do – sys.master_files and sys.databases. The sys.master_files view contains the database and individual file names, and the data_space_id column always has a value of 0 for the log file. The size column returns the value in 8KB pages, so we have to multiply the column by 8, then divide by 1024 to get the size in megabytes (MB).

Click through for the demo.

Comments closed

MERGE in Many Languages

Published 2020-04-10 by Kevin Feasel

Lukas Eder takes a look at the MERGE statement in SQL:

A few dialects support MERGE. Among the ones that jOOQ 3.13 supports, there are at least:

– Db2
– Derby
– Firebird
– H2
– HSQLDB
– Oracle
– SQL Server
– Sybase SQL Anywhere
– Teradata
– Vertica

For once, regrettably, this list does not include PostgreSQL. But even the dialects in this list do not all agree on what MERGE really is. The SQL standard specifies 3 features, each one optional:

– F312 MERGE statement
– F313 Enhanced MERGE statement
– F314 MERGE statement with DELETE branch

But instead of looking at the standards and what they require, let’s look at what the dialects offer, and how it can be emulated if something is not available.

This is a really cool overview of an area where several vendors can claim support, but that support can mean quite different things. The one caveat is, I don’t know if any of the other platforms’ MERGE operators are as busted as SQL Server’s in terms of bugs.

Comments closed

All About Table Expressions

Published 2020-04-09 by Kevin Feasel

Itzik Ben-Gan has started a series on table expressions:

Perhaps this will come as a surprise to some, but I actually do find the use of the term table in common table expression as very appropriate. In fact, I find the use of the term table expression as appropriate. To me, the best way to describe what a CTE is in T-SQL, it’s a named table expression. The same applies to what T-SQL calls derived tables (the specific language construct as opposed to the general idea), views and inline TVFs. They are all named table expressions.

If you can bear with me a bit, I’ll provide the reasoning for my view of things in this article. It occurred to me that both the naming confusion, and the confusion around whether there’s a persistency aspect to table expressions, can be cleared with a better understanding of the fundamentals of our field of relational database management systems. Those fundamentals being, relational theory, how SQL (the standard language) relates to it, and how the T-SQL dialect used in the SQL Server and Azure SQL Database implementations relates to both.

There’s a lot of depth in this post, so I recommend a careful reading.

Comments closed

VARCHAR Columns and Bytecode Version Mismatch in R

Published 2020-04-06 by Kevin Feasel

Dave Mason runs through a tricky problem with SQL Server Machine Learning Services:

During my testing, I’ve found R handles CHAR and VARCHAR data within the input data set as long as the ASCII codes comprising the data is in the range from 0 to 127. This much is not surprising–those are the character codes for the ASCII table. Starting with character code 128, R begins having some trouble. 

Read on to see the problem. Dave’s advice at the end is sound (and frankly, my advice for any string data in SQL Server).

Comments closed

Combining User-Defined Types and Temp Tables

Published 2020-04-03 by Kevin Feasel

Andy Levy tries to make cats and dogs live together:


This tripped me up a few weeks ago, but once I stopped and thought about for a moment it made total sense. I was trying to copy some data into a temp table and got an error I’d never encountered before.

Column, parameter, or variable #1: Cannot find data type MyStringType.

What’s that all about? Let’s find out.

I don’t think it spoils things to say that Andy’s story is a tragedy and not a comedy. But in fairness, the number of shops using user-defined types (as opposed to user-defined table types) is probably not enormous.

Comments closed

The Peril of Local Variables

Published 2020-04-01 by Kevin Feasel

Erik Darling dives into the tradeoffs you make when using local variables in stored procedures to avoid parameter sniffing:

In a stored procedure (and even in ad hoc queries or within dynamic SQL, like in the examples linked above), if you declare a variable within that code block and use it as a predicate later, you will get either a fixed guess for cardinality, or a less-confidence-inspiring guess than when the histogram is used.

The local variable effect discussed in the rest of this post produces the same behavior as the OPTIMIZE FOR UNKNOWN hint, or executing queries with sp_prepare. I have that emphasized here because I don’t want to keep qualifying it throughout the post.

This deserves a careful read-through.

Comments closed

Tracking Object Changes and Views

Published 2020-04-01 by Kevin Feasel

Ed Pollack has a solution for tracking when the underlying objects which make up a view change:

When a view’s underlying objects change, the view itself will not change. This can result in a view where the data types of columns, as well as nullability, precision, and scale can be reported inaccurately. When this happens, it is possible for queries against these columns to return errors, truncate data, perform poorly, or otherwise behave in unexpected ways.

This article will delve into views, how they are defined, and how T-SQL can be used to programmatically test the validity of views and ensure they never become stale.

Click through for an interesting article with plenty of code demos.

Comments closed