TRIM Does More Than Remove Spaces

I was excited when SQL Server 2017 was released. There was a new function in town, named TRIM. This meant we could replace a lot of code where LTRIM(RTRIM(x)) was being used, as TRIM by itself can remove leading and trailing spaces. Excel had support for the TRIM function long before SQL Server, so this…

Building a Cash Register in SQL Server

This is going to be ridiculous. I’ve wanted to work on this project for a while now. The “Cash Register” problem is a pretty common interview question, but usually it’s intended for object-oriented programming languages. The gist of the question is this, “How would you implement a program to return the combination of monetary denominations…

MIN/MAX Functionality Extension

I would love to see Transact-SQL add functionally to return the largest or smallest value from a list of expressions. In simpler terms, I wish I could do something like the following: Obviously in that example, we can clearly tell which of those two datetimes would be larger (the first one), but what if we…

Introduction to Filtered Indexes

Filtered indexes were introduced in SQL Server 2008, providing us with the ability to save space and potentially improve performance. These indexes are nonclustered, meaning they essentially make a “copy” of our underlying data in a B-tree structure. The primary difference from creating a traditional nonclustered index, is the inclusion of a WHERE clause. So,…

Using VALUES to SELECT

Many people are aware that we can INSERT data using the VALUES keyword. I don’t see a lot of people using VALUES to SELECT data. Doesn’t VALUES only work for INSERT? Working with SQL Server, we often see queries to insert data like this: This statement would insert two rows into the dbo.Person table. Those…

Raising an Error to Print

Why do we print messages? Printing messages is a common, well-known way of debugging code. This is useful to troubleshoot and identify where execution is currently taking place. PRINT is often used to debug code in SQL Server. We often see this typically used in stored procedures or deployment scripts. An example using PRINT Here’s…

Rolling Back Table Variables

Table variables, love ’em or hate ’em it seems they’re here to stay. Sometimes table variables don’t always follow the rules that we think they should. Sometimes they behave in strange and mysterious ways. Fortunately, they have been improved with SQL Server 2019 (hopefully we’ll see less a few less recompile hints). What happens if…

What Could Go Wrong With sp_rename?

The classic sp_rename stored procedure can sometimes lead to some strange, unexpected outcomes. We’ve all probably seen the message “Caution: Changing any part of an object name could break scripts and stored procedures.” at some time using this stored procedure, which is pretty obvious. Of course dependencies could be broken as we’re literally renaming an…