Press "Enter" to skip to content

Category: T-SQL

Pitfalls with Window Functions

Published 2019-08-14 by Kevin Feasel

Itzik Ben-Gan takes us through two issues you might run into when using window functions:

There are two common pitfalls involving window functions, both of which are the result of counterintuitive implicit defaults that are imposed by the SQL standard. One pitfall has to do with calculations of running totals where you get a window frame with the implicit RANGE option. Another pitfall is somewhat related, but has more severe consequences, involving an implicit frame definition for the FIRST_VALUE and LAST_VALUE functions.

There’s a lot going on in these two examples, so read on.

Comments closed

Spaces in CHAR Columns

Published 2019-08-12 by Kevin Feasel

John McCormack wants to store a single space in a CHAR(1) column:

I was asked by a colleague why his where clause wasn’t being selective when filtering on a space value. The column was a char(1) data type. To understand the curious case of the space in char(1), we need to understand how the char data type works and also a bit more about the need for it in this scenario.

The ANSI standard makes sense, but it is something you have to keep in mind in cases like this.

Comments closed

SQL Server and String Concatenation

Published 2019-08-07 by Kevin Feasel

Slava Murygin walks us through the rules around string concatenation with the + operator in SQL Server:

Pretty recently hit very interesting problem of how SQL Server concatenates strings.

The very same SELECT clause in some cases produced VARCHAR(MAX), but in same cases it cut the result to VARCHAR(8000) ( or NVARCHAR(4000) )

After a research I’ve discovered following:

Read on to see what Slava has discovered.

Comments closed

Procedure Parameters: Optional and Required

Published 2019-07-30 by Kevin Feasel

Kenneth Fisher takes us through procedure parameters:

If you are executing a stored procedure with a bunch of parameters it can be a bit of a pain if you have to pass a value in for each of them. Fortunately, it’s pretty easy to make some parameters required and others optional. You simply give them a default value.

Kenneth also points out that functions don’t behave this way, and shows how to handle parameters where you don’t want to accept NULL under any circumstances. This is useful when NULL is just a placeholder for “I don’t really want to use this parameter” but the application doesn’t know how to avoid sending the parameter in the first place.

Comments closed

Getting the Largest Value Per Group

Published 2019-07-25 by Kevin Feasel

Erik Darling has a series on getting the highest value using CROSS APPLY. Part 1 covers the no-index route:

Let’s say you wanna get the highest thing. That’s easy enough as a concept.

Now let’s say you need to get the highest thing per user. That’s also easy enough to visualize.

There are a bunch of different ways to choose from to write it.

Part 2 covers the yes-index route:

In this round, row number had a tougher time than other ways to express the logic.

It just goes to show you, not every query is created equal in the eyes of the optimizer.

I don’t think I’m spoiling too much by saying that you really want a good index in place when using CROSS APPLY in this manner.

Comments closed

Median Calculation with T-SQL

Published 2019-07-24 by Kevin Feasel

Nisarg Upadhyay shows three ways to calculate the median in T-SQL:

To calculate the median of any dataset, we first need to arrange all values from the dataset in a specific order. After arranging the data, we must determine the middle value of the specified dataset. If the dataset contains an odd number of values, than the middle value of the entire dataset will be considered as a median. Following is the example:

Median (M) = value of ((X + 1)/2) th item. (x is the number of values in the dataset)

Honestly, CLR’s probably the best approach here if you want a fast calculation for a reasonably large number of rows. Using ML Services and R/Python is another alternative, though the launchpad spinup time will probably make it slower than CLR.

Comments closed

Simple Query Zen

Published 2019-07-23 by Kevin Feasel

Erik Darling wants you to simplify your life queries:

See, when a query is big and complicated to you, there’s a pretty good chance you’re gonna get a big and complicated query plan, because it’s big and complicated to the optimizer, too.

This isn’t to say the optimizer is dumb or bad or ugly; it’s just that there’s only so long it’s willing to spend coming up with a plan.

Remember, cheap plan fast. Not perfect, not great, maybe good enough.

It’s a good operating philosophy: if you have a query which has gone off the rails, one of the best things you can do is try to turn the query into several small steps. It’s possible to reduce complexity that way…though you may also gain complexity in the process if you do it wrong.

Comments closed

Finding Broken Code in SQL Server

Published 2019-07-22 by Kevin Feasel

Pamela Mooney shows us how we can find broken code on our SQL Server instances:

Before we approached our last major SQL Server upgrade, I was curious about what might break.  Yes, I had used the DEA to check our code against deprecated or discontinued code.  But I am talking about code that might not have been used in some time and would break because objects no longer existed, or other things like that.  So I wrote these scripts to refresh the sprocs, views and functions in our (non-production) environment.

Pamela’s motivation was to handle code which breaks during an update. You can also use this to see what you can probably deprecate—if the view doesn’t work, it can’t be in use. That means either someone should fix it or drop it.

Comments closed