BULK INSERT & CHECK Constraints

There are a few occasions where BULK INSERT seems to be a great solution for loading data from flat files. Sure, there are alternatives. You could also use BCP or SSIS, but BULK INSERT can come in handy in a time crunch. I don’t use this too often, but in the right circumstances, this could…

DROP [IF EXISTS]

What is it? Microsoft introduced a new method of dropping objects in SQL Server 2016. There are many times we want to conditionally drop objects to prevent errors or to allow scripts to be idempotent. Without first checking if the object exists, and it turns out it doesn’t exist, the DROP statement would fail. DROP…

Using SPARSE Columns

Have you ever seen the SPARSE keyword used in a table definition? I came across this for the first time when looking at a holiday table that had a HolidayText column with a SPARSE property. So, what’s a SPARSE column? Sparse columns were introduced in SQL Server 2008. They’re intended to reduce the storage space…

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…

Generating a Series with PostgreSQL

I’ll preface this by saying that I don’t work with PostgreSQL too often, but this is one of the most useful functions I’ve seen. There are many times working with data where we need to use a “Numbers” or a “Tally” table to generate a series of data to perform something in a set-based manner…