Press "Enter" to skip to content

Category: T-SQL Tuesday

Generating SQL Code from Metadata

Published 2022-10-13 by Kevin Feasel

Richard Swinbank shows off one of the more common uses of dynamic SQL:

Now you come to ingest your second table – and you have to do this all again!

It’s a lot of code, and worse, it’s all basically the same – only the names and types are any different. You can easily make a mistake, and the boredom of repetition only makes that more likely. Later on, when you decide you want to add something to the process – a logging statement at the end of the stored proc, for example – you’ll have to add it separately to every stored procedure you’ve created.

The solution? Generate your code automatically, from metadata, using dynamic SQL! 

Read on for what you’d need to pull this off.

Comments closed

Generating Code to Run Across All Databases via Dynamic SQL

Published 2022-10-12 by Kevin Feasel

Aaron Bertrand provides a warning around dynamic SQL:

For October’s T-SQL Tuesday, Steve Jones asks us to talk about ways we’ve used dynamic SQL to solve problems. Dynamic SQL gets a bad rap simply because, like a lot of tools, it can be abused. It doesn’t help that a lot of code samples out there show that “good enough” doesn’t meet the bar most of us have, especially in terms of security.

In a series I started last year, I talked about ways to do <X> to every <Y> inside a database, focusing on the example of refreshing every view (in a single database or across all databases). I already touched on what I want to dig into today: that it can be dangerous to try to parameterize things that can’t be parameterized in the ways people typically try.

Read the whole thing. I do find it funny how often people aren’t allowed to install well-known, third-party stored procedures (like Aaron’s sp_ineachdb) but it’s perfectly okay to write terrible code which is vulnerable to exploit because it was written in-house and is therefore more trustworthy somehow.

I don’t want to dunk on security teams too much in this regard, as I understand and really do appreciate the principle, though it often has counterintuitive first- and second-order consequences.

Comments closed

Column Lookups and Dynamic SQL

Published 2022-10-11 by Kevin Feasel

Rob Farley does a double-check:

I’ve written before about what I consider the golden rule with SQL Injection. And that is, to understand that DATA should not be executed like CODE. A parameter is fine – that’s not part of what gets executed – but you do not embed user-entered values into queries. You never create a WHERE clause like “WHERE t.col1 = 3”, if that value 3 is being passed in. Instead, you use “WHERE t.col1 = @param”. Because you can’t assume that “3” is safe to run.

But there are things in queries that can’t be parameterised. Table names, linked server names, column names, for example.

Read on to learn what Rob does in those cases.

Comments closed

T-SQL Tuesday 154 Recap

Published 2022-09-20 by Kevin Feasel

Glenn Berry summarizes what people are doing with SQL Server 2022:

Back on September 5, 2022, I posted the invitation for T-SQL Tuesday #154 Invitation – SQL Server 2022, which was due on Tuesday, September 12, 2022. I ended up getting eleven blog post responses that I am aware of (including mine). Thank you to everyone who participated! This post will be my T-SQL Tuesday #154 Recap.

Here are the blog posts for #T-SQL Tuesday #154, in alphabetical order by author.

Glenn goes the extra mile by including author photos as well.

Comments closed

Contained SQL Agent Jobs in SQL Server 2022

Published 2022-09-15 by Kevin Feasel

Allan Hirt looks at contained SQL Server Agent Jobs:

I previously wrote about Contained AGs in SQL Server 2022 and demonstrated how to create a contained login. In this blog post, I’m going to talk about contained SQL Server Agent jobs because just like logins, they are a bit confusing from an administative standpoint in their current pre-release implementation (this blog post was written using SQL Server 2022 RC0 using SSMS 19 Preview 3).

It sounds like there’s still a ways to go on the tooling side of things.

Comments closed

IS [NOT] DISTINCT FROM

Published 2022-09-14 by Kevin Feasel

Louis Davidson likes a new operator in SQL Server 2022:

The MOST exciting change from a T-SQL standpoint is: IS NOT DISTINCT FROM. This feature solves an age-old issue for T-SQL programmers and is worth its weight in gold. It is basically an equals comparison operator like =, but treats NULL as an individual value. Unlike =, this new operator returns only TRUE or FALSE, but not UNKNOWN. Writing queries that compare to values that can contain NULL is tedious, mostly because of code like the following:

Louis is quite happy here. I like the fact that the syntax is here, though I’d be concerned about performance—the syntax is nicer but you can run into the same performance issues as you’d have with “NULL or match” type queries.

Comments closed

Restoring Azure SQL MI Databases to SQL Server 2022

Published 2022-09-14 by Kevin Feasel

Garry Bargsley restores a backup:

My post for T-SQL Tuesday #154 is going to be a demo of the new SQL Server feature that allows you to restore an Azure SQL Managed Instance backup to a SQL Server 2022 instance. Actually, I am not sure if this is a feature or just an enhancement that allows for this behavior to work.

Current versions of SQL Server do not allow the restoration of backups taken on Azure SQL Managed Instance.  Managed Instances are considered “vNext” and runs a different version that is beyond anything on-premises could keep up with due to the frequent changes being applied to Managed Instance. There has always been a disconnect with the restorability between the two platforms, so it is good that Microsoft has found a solution to this limitation.

I’d still like to see the ability to fail from SQL MI to on-prem (or VM) SQL Server—you can go from on-prem to Azure SQL MI, though it sounds like right now, failback is a database restore.

Comments closed

Compacting Window Function Definitions

Published 2022-09-14 by Kevin Feasel

Rob Farley like a syntax change:

This was fine, but it did start to become a little cumbersome.

Enter SQL Server 2022. Not only do we get the ability to ignore nulls now, making it easy to get the last non-null value from a list, but we also get a WINDOW clause – part of the SELECT query itself, dropping in between the HAVING clause and the ORDER BY clause, allowing us to predefine those OVER clause segments.

Read on for the full scope of Rob’s thoughts.

Comments closed

T-SQL Tuesday 153 Roundup

Published 2022-08-17 by Kevin Feasel

Kevin Kline musters the troops:

I received a great collection of blog posts in response to T-SQL Tuesday 153 which I kicked off on Tuesday, August 2nd – asking you to write about a conference or event that had a significant event on their life. As one of the small handful of people who attended every PASS Summit from its founding through the pandemic lockdown, I’ve witnessed so many transformational experiences firsthand.

Human beings are social creatures and though we as IT pros like to focus on hard technology skills first and foremost, I think we can all admit that having a great social experience at a conference like the PASS Summit in North America, SQLBits in the UK, or Data Platform Summit in India is at least as important as the technical learning.

Read on for a summary of several posts.

Comments closed

T-SQL Tuesday 152 Round-Up

Published 2022-07-20 by Kevin Feasel

Deb Melkin casts a wide net:

It’s time to do the round up of this month’s T-SQL Tuesday entries. It was great to see so many people responding, including at least one new participant. I think there are a lot of kindred spirits here, as in we’ve all felt each other’s pain. (I know that I personally can relate to way too many of these things.) And I truly enjoyed reading everyone’s post.

Click through for the full rundown.

Comments closed