Press "Enter" to skip to content

Category: T-SQL

The Value of QUOTENAME

Published 2023-05-31 by Kevin Feasel

Quoth Chad Callihan, “Occasionally more”:

QUOTENAME can be used to make sure database objects are valid in your query. Most of the time, objects like table names only contain valid characters, so there’s nothing to worry about. But nobody’s perfect. Let’s look at an example of what can happen when somebody creates a table with a forward slash in the name and see how QUOTENAME can be used to query against it.

QUOTENAME is also a good way of preventing SQL injection, though I still prefer appropriate use of exec sp_executesql in any case in which it’s possible to use.

Leave a Comment

Filling Gaps in T-SQL

Published 2023-05-30 by Kevin Feasel

Itzik Ben-Gan has a new challenge:

The task involves developing a stored procedure called GetBalances that accepts a parameter called @accountid representing an account ID. The stored procedure should return a result set with all existing dates and balances for the input account, but also gap-filled with the dates of the missing workdays between the existing minimum and maximum dates for the account, along with the last known balance up to that point. The result should be ordered by the date.

My first thought was last observation carried forward, which is now available in SQL Server 2022 (Itzik’s solution 2). I kind of thought of solution 3, though did not think through the mechanics of how it’d work and so I get no credit there.

Leave a Comment

Trying NTILE

Published 2023-05-17 by Kevin Feasel

Chad Callihan looks at the fourth ranking window function:

Have you ever used the NTILE function? Or have you even heard of the NTILE function? It seems to be one of the lesser known, lesser used window functions in SQL Server. I’ve never come across it in the wild but maybe there are those that use it all the time. Either way, let’s have a look at what it does and how it can be used.

Click through for a demo. I definitely use it a lot less than ROW_NUMBER(), RANK(), and DENSE_RANK(), but I have used it to some good effect in the past, mostly in cases where I’ve wanted to focus on the top X% of data for an analysis.

Leave a Comment

Choosing from a List of Values

Published 2023-05-10 by Kevin Feasel

Greg Dodd doesn’t need no steenkin’ tables:

Sometimes you have a list of values or parameters that you’d like to run a select statement over the top of to get a result. There’s a few ways of doing this, usually I see people create a Temporary Table and insert the data. But is there an easier way?

Yes, we can select from VALUES:

The result of this is called a virtual table, and as far as other parts of the query are concerned, it’s just another table.

Comments closed

Adding a UTC Time Zone Indicator to a Date in SQL Server

Published 2023-05-04 by Kevin Feasel

Bill Fellows fights with the language:

It seems so easy, I was building json in SQL Server and the date format for the API specified it needed to have 3 millsecond digits and the zulu timezone signifier. Easy peasy, lemon squeezey, that is ISO8601 with time zone Z format code 127

SELECT CONVERT(char(24), GETDATE(), 127) AS waitAMinute; Running that query yields something like 2023-05-02T10:47:18.850 Almost there but where’s my Z? Hmmm, maybe it’s because I need to put this into UTC? SELECT CONVERT(char(24), GETUTCDATE(), 127) AS SwingAndAMiss;

Running that query yields something like 2023-05-02T15:47:18.850 It’s in UTC but still no timezone indicator.

Read on for several attempts and what finally did the trick.

Comments closed

CETAS in SQL Server 2022

Published 2023-05-04 by Kevin Feasel

Eric Rouach shows off a nice extension to T-SQL in SQL Server 2022:

Create External Table As Select or “CETAS” has finally become available on SQL Server with the release of the 2022 version.

After a short setup, we can create various formats files containing any query’s result set. The created file/s must be kept on an Azure storage solution i.e. Azure Blob Storage.

The process also creates an external table reflecting the updated file’s content.

We’ve been able to do this in Azure Synapse Analytics dedicated and serverless SQL pools for a while, so it’s good to be able to create an external table from a SELECT query on-premises, especially considering that it’s the only way we have left to write to external sources using PolyBase.

Comments closed

Sparse Columns in SQL Server

Published 2023-05-02 by Kevin Feasel

Chad Callihan occasionally inserts something:

Have you ever maxed out the SQL Server table column limit yet still needed more columns? Hopefully not considering SQL Server has a max limit of 1024 columns per table. But as I found out, it’s possible for someone to reach out and ask for even more. Sparse columns are an option to consider when you can’t get enough. Let’s take a look at what sparse columns are and how they can be used.

Sparse columns have very little utility, except in the most “I don’t think you’re doing it right” scenarios. Still, if you happen to end up in that scenario, there is a way out, though I’d really want to understand the nature of the data in that problem and, knowing just the amount of detail in the scenario that I do, would lean toward storing the data either in an unpivoted fashion (one row per entity * attribute in an EAV-style “‘additional attributes” table) or as a JSON string and let the client sort it out.

Comments closed