Press "Enter" to skip to content

Category: T-SQL

Building Your Own TRY_PARSE Function

Published 2023-03-10 by Kevin Feasel

Aaron Bertrand gives it a go:

A while back, I wrote a couple of tips about simulating TRY_CONVERT – a function added in SQL Server 2012 – in earlier versions (see Part 1 and Part 2). Recently, someone in the community had a similar requirement: they wanted to make TRY_PARSE work for dates in SQL Server 2008. Now, I’m not one to help people stay on versions of SQL Server that are now 15 years old, but I do acknowledge that hands may be tied, and they can’t always control the version they’re stuck with.

Aaron does this community member a solid, though it’s probably something you’d never want to use if you’re on SQL Server 2012 or later.

Comments closed

Handling Optional T-SQL Code via SQLCMD

Published 2023-03-07 by Kevin Feasel

Louis Davidson shows off a bit of SQL Server Management Studio’s SQLCMD mode:

In the creating a database script, there are quite a few parameters you will want as part of your script. The database name, the settings, whether or not to drop the database or not. Built into SQL Server Management Studio is a cool tool called SQLCMD mode. Scripts using this mode get a few scripting tools that are really useful. These tools allow you to do things like insert other script files, set environment variables, and one I particularly like, make sure you don’t accidentally execute a script if no code is highlighted using EXIT to start your script ().

Where it is really lacking is in the area of control of flow language. In this blog I am going to share a few techniques I have used to get around this when building scripts that need to optionally execute different bits of code.

Click through to see how you can use it. Going one step further, you can directly script against sqlcmd.exe, which can be useful for automating deployments.

Comments closed

Approximate Percentiles in Azure SQL DB and MI

Published 2023-03-02 by Kevin Feasel

Balmukund Lakhani announces a feature has gone generally available:

Today, we are announcing General Availability (GA) of native implementation of APPROX_PERCENTILE in Azure SQL Database and Azure SQL Managed Instance. We announced preview of these functions in October 2022. Since then, many customers have adopted these for the applications where response time of percentile calculation was more important than the accuracy of the result.

I have and will continue to extol the virtues of these two functions wherever I go. They’re considerably better than the originals once you start getting into the hundreds of thousands or millions of rows. They’re also available in SQL Server 2022.

Comments closed

Fun with Implicit Conversions to DateTime

Published 2023-03-01 by Kevin Feasel

Andrea Allred gets tested:

I have been teaching a T-SQL 101 class and for the homework, we asked the students to get all the records where our heroes had a birthdate between 1995 through 1999. I expected something like this:

[…]

Imagine my surprise when one of the students turned in this:

SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate BETWEEN '1995' AND '1999'

When I first saw the query I thought, “There is no way they ran that and it worked.” So I wrote it up and ran it on my data. Guess what? IT RUNS AND RETURNS DATA! I was shocked.

Click through to see what it returns and how that’s not quite right.

Comments closed

Speeding Up Queries via IF EXISTS

Published 2023-03-01 by Kevin Feasel

Chad Callihan doesn’t need to wait for the query’s end credits sequence:

When checking for the existence of a value or values in a table, typically, the whole table does not need to be read. The goal is to obtain more of a true or false answer, whether a criteria is met or not. If the criteria is met with the first few records then there’s no need to read on. Nonetheless, you may come across scripts written to unnecessarily retrieve the complete count of a value in a table. Let’s compare the performance difference between using COUNT(*) and using “IF EXISTS” when checking a table for values.

One’s going to give you a full scan and the other will give you a semi-join. Read on to see what the practical effect of this is.

Comments closed

Performing a Detailed Code Review of a Stored Procedure

Published 2023-02-27 by Kevin Feasel

Aaron Bertrand has 99 problems and this stored procedure is 40 of them:

I’ve been at this for a while now, and have a very particular set of rules and coding conventions that I follow when writing and, more importantly, reviewing T-SQL code. I often perform code reviews and thought it would be fun to frame this exercise: a completely fictitious stored procedure hits my desk, I’ll reject it of course, and enumerate the reasons why. The procedure might pass review in a lot of shops, but not in mine.

Click through and give it a try. Aaron has outdone himself with this and I got angry with him about 2/3 of the way through the procedure. That’s how you know it’s a good example.

Comments closed

ORDER BY Clause in Subqueries and SSMS Warnings

Published 2023-02-20 by Kevin Feasel

Ronen Ariely explains a warning message:

Warning: The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself. Click CANCEL to discard your modifications. Click OK to save the view.

Read on for the full context of when you might see this warning message in SQL Server Management Studio.

Comments closed

The Power of Date Truncation

Published 2023-02-14 by Kevin Feasel

Magda Bronowska rounds to the nearest minute:

From MS Learn:

DATETRUNC() function returns an input date truncated to a specified datepart.

On the surface the work similarly to DATEPART(), however that function returns integer values, opposed to the dates returned by DATETRUNC() (we will see that better in the example below).

Read on for plenty of examples of this, as well as two more syntax updates in SQL Server 2022.

Comments closed

The Benefit of IS DISTINCT FROM

Published 2023-02-14 by Kevin Feasel

Rob Farley enjoys the syntax:

This month, Deepthi Goguri (@dbanuggets) asks us about our favourite new feature in SQL Server 2022 or Azure. And while there are always a few, I’m going to write about why I have a particular fondness of “IS [NOT] DISTINCT FROM“, despite the fact that it’s overly wordy and the functionality isn’t actually new at all.

People understand my point that it’s a little wordy. Typing “IS NOT DISTINCT FROM” instead of “=” doesn’t sound fun to anyone, and I think “==” or “IS” ought to be fine. The fact that the functionality isn’t new… well that statement seems to raise a few eyebrows.

Read on for Rob’s take on what IS DISTINCT FROM (and its negative cousin) actually do and what performance-killing alternative people used prior to that.

Comments closed

Error Handling with OPENROWSET

Published 2023-02-14 by Kevin Feasel

Deborah Melkin handles missing servers with aplomb:

OPENROWSET is a functionality that allows you to access data sources outside your current server. This could be reading from an Excel file or calling another SQL Server instance. You’re able to treat that other data source as record set, or derived table, and work with the rows returned as you would a local table. One reason you may want to do this is that you need to use a stored procedures to query data from other servers and bring the data together, effectively creating an ELT (Extract – Load – Transform) process without having to use SSIS or Azure Data Factory (ADF).

Read on to see how OPENROWSET() works, what happens if you try to access a remote server which doesn’t exist (or times out), and how you can capture that error message in a CATCH block—something that is not possible to do by default.

Comments closed