I’m not trying to start up a debate whether you should use tabs or spaces when indenting code. Personally, I prefer spaces because when I copy the code to another editor the outlining of the code remains the same while with tabs it’s not always the case (looking at you, Word and Outlook). But I don’t want to hit the spacebar 4 times whenever I want to indent something, so I use the setting “insert spaces instead of tabs”:

Best of both worlds However, recently I was writing some T-SQL code and I noticed that tabs were being inserted instead of spaces. I had just installed SSMS 22 so I thought I forgot to change the settings. But this wasn’t the case, the setting was configured correctly. Maybe the issue is with Redgate SQL Toolbelt, which is installed as an extension into SSMS 22. But also there the setting was applied correctly:

However, when hitting the tab key spaces were not being inserted.

Then I noticed something small in the bottom right corner of the query window:

When clicking on “TABS”, you get the option to switch between tabs and spaces:

When switching to spaces, all tabs are automatically converted to spaces (or the other way around).

Likewise, you can set the encoding and the line endings. It seems that these “query window settings” overrule any settings from the options menu or from any extension. I wasn’t aware that these settings existed in the query window, and maybe by hitting some key combination I accidentally switched from spaces to tabs. I checked SSMS 21 and these options are there as well.

Conclusion: if you want to insert spaces instead of tabs and SSMS is refusing to play along even if all settings are correct, check the bottom right corner of your query window to see if spaces are selected.

Quite a long title for a short blog post
While deploying a DACPAC (from a SQL Server Data Tools Database Project) through Azure Devops, I got the following error message:

The user attempting to perform this operation does not have permission as it is currently logged in as a member of an Azure Active Directory (AAD) group but does not have an associated database user account. A user account is necessary when creating an object to assign ownership of that object. To resolve this error, either create an Azure AD user from external provider, or alter the AAD group to assign the DEFAULT_SCHEMA as dbo, then rerun the statement.

Guess the SQL Server team didn’t get the memo that Azure AD has been renamed to Entra ID. Anyway, the Azure Devops pipeline uses a service connection defined in Devops, and in that service connection a user-defined managed identity is configured that has contributor access on the resource group that contains the Azure SQL DB. Furthermore, that managed identity is an actual user in the database, so the error message is completely misleading. The error was thrown when the following SQL script was executed:

CREATE SCHEMA myschema AUTHORIZATION dbo;

Turns out, the managed identity didn’t have the CREATE SCHEMA permissions, and it’s not part of the dbo role, so the CREATE SCHEMA script fails with the error above. I created the necessary schemas with a more privileged user and then the deployment pipeline ran without issues.

I wanted to try out the new JSON index which is for the moment only available in the SQL Server 2025 preview version. I know, it’s not even available in Azure SQL DB. Cloud not first? What?

I already had a virtual machine in Azure, running SQL Server 2025 CTP 2.0 (which uses a pre-made image). I explain how to set that one up in the article Install SQL Server 2025 Demo Environment in Azure. But I wanted to use the latest preview, which is Release Candidate 0 at the time of writing. Unfortunately, there’s no image available (yet?), so I had to do it the old-school way: installing SQL Server manually.

I created a VM in Azure in pretty much the same way as in the article, except I didn’t pick an image with SQL Server pre-installed, but one with only a Windows operating system.

Once the VM was provisioned, I logged in using remote desktop, enabled Internet and downloaded the RC0 installation media (for which you need to fill in a form to get it). I started the SQL Server installer and launched SQL Server setup:

Most of the setup wizard is “next-next-finish”, so I’ll highlight the important parts. First, you need to pick a SQL Server edition.

New in SQL Server 2025 is that there’s a Standard Developer edition, meaning a Developer edition that only has the features of Standard. I want all the features, so I choose Enterprise Developer.

After accepting the license terms, checking for updates and installation rules, and finally ignoring Azure extension, we get to the feature selection page:

Pretty wild that it fits in one single screenshot. They really removed a lot of features which are now separate downloads (or just gone, like Data Quality Services). For contrast, check out this screenshot from SQL Server 2012:

I only need the database engine and I’m installing it as the default instance, so I skip straight through the instance configuration, where I check the box:

For the server configuration, I picked mixed mode because SQL authentication is a tad easier to use when logging in remotely (use a strong password for the sa account!) and it’s just a test environment. Add your user as an admin.

After that, hit next till SQL Server 2025 is installed (which should just take a couple of minutes).

Inside remote desktop, I can now log into my SQL Server 2025 instance using windows authentication. I also want to log into the database with SSMS from my own machine. I disabled the Windows Firewall (I know, not exactly best practice) and I added port 1433 to the VM firewall:

I also enabled the TCP/IP protocol in the SQL Server Configuration Manager:

After all that, you should be able to connect to the database instance from your local machine:

Don’t forget to prefix the public IP address of your Azure VM with tcp: to make sure the correct protocol is used.

Disclaimer: this post is not sponsored by Redgate

For those who’ve missed it, the new version of SSMS is now generally available. It’s a big new version and it now uses Visual Studio 22 behind the scenes, so it’s now installed through the Visual Studio Installer.

Anyway, I’m a big fan of Redgate SQL Prompt as it enables my to quickly format code and it also has better autocomplete features. Officially, the latest version of SQL Prompt supports the preview version of SSMS 21. But what about the new GA version?

I installed SSMS 21, imported existing settings from my old version of SSMS and also installed the latest version of SQL Prompt (10.16.12 at the time of writing). However, when I fired up SSMS 21, there were no Redgate tools to be seen. Bummer. The extensions menu was empty:

In the customize screen there was also no trace of any Redgate extension:

After some digging around, someone mentioned that the extension is installed in the wrong folder. It’s installed in the Preview folder of SSMS, instead of the Release folder.

By copying these folders to the correct location, we can get the extensions to work in SSMS 21.

This is probably a temporary issue though, and I hope they fix it soon at Redgate. However, when reopening SSMS 21, the SQL Prompt extension was still not visible. You can fix this by going back to the customize menu, and deselecting SQL Prompt from the list.

(SQL Prompt appearing twice is another bug btw)

Now it should pop right up in SSMS, and you can continue writing awesome T-SQL!

The slides and scripts for my session “Tackling the Gaps & Islands Problem with T-SQL Window Functions” at dataMinds Connect 2024 can be downloaded from GitHub. If you want to run the sample scripts, some of them require an installation of the AdventureWorks DW sample database.

I was writing some dynamic SQL that generates some SQL statements to load my facts and dimensions into a data warehouse. Some of those SQL statements can become very long, for example if a dimension has a lot of columns. When debugging, I noticed a couple of statements failing with various errors. Turns out, they were truncated after 4000 characters. What was going on?

I’m using the CONCAT_WS function to dynamically build my SQL statement. As a separator, I use a variable holding a carriage return and a line feed, so I get nice multi-line statements. Something like this:

DECLARE @crlf CHAR(2) = (CHAR(13) + CHAR(10));
SELECT
    SQLStatement = CONCAT_WS(@crlf
                           ,'SELECT *'
                           ,'FROM dbo.' + m.TableName)
FROM dbo.MyMetadata m;

Well, obviously a bit longer than that, because such a short statement won’t be truncated. I checked the documentation, but it doesn’t state CONCAT_WS can only hold 4000 chars, so the issue is not with the function itself. The problem is that it’s a bit hard to debug, because when you run the query in SSMS there’s also a limit on how much characters the result window shows. And the PRINT command is limited as well (4000 chars for nvarchar, 8000 for varchar). So I created an SSIS package that runs the dynamic SQL and outputs the result to a CSV file (which was more painful than I expected. SSIS can be really frustrating sometimes). There I could verify the data was really truncated, and that it wasn’t just the output windows of SSMS or ADF.

But what’s causing the truncation? After some searching, I found out it has to do with data type precedence in the CONCAT_WS function (or any concatenation function in SQL Server). The rules are a bit better explained in the documentation of CONCAT. Specifically:

Since none of the input arguments to CONCAT_WS was larger than 4000 chars, the result was a NVARCHAR(4000). The fix is luckily quite simple:

DECLARE @crlf CHAR(2) = (CHAR(13) + CHAR(10));
SELECT
    SQLStatement = CONCAT_WS(@crlf
                           ,CONVERT(NVARCHAR(MAX),'SELECT *')
                           ,'FROM dbo.' + m.TableName)
FROM dbo.MyMetadata m;

By doing an explicit conversion on one of the input parameters, we can force the function to return a NVARCHAR(MAX), which means the data is no longer truncated.

The title of this blog post is a bit misleading, because what we actually want to do is to install the SSIS development functionality in Visual Studio 2022. SSAS and SSRS have been available for some time now in VS 2022, but the extension for SSIS has only just been released in preview.

For those of you that have been working on an older version of SSIS/SQL Server (2014-2016, something like that), the BI components (SSIS/SSAS/SSRS) are now extensions in Visual Studio. SQL Server Data Tools (SSDT) is no longer available as a separate download.

So you’ll need a full-blown version of Visual Studio (make sure you only install the workflows you actually need). The good news is that you can use the community edition of VS if you’re just using it for BI development. Anyway, install VS 2022 on your machine and download the SSIS extension here.

After the download (approx. 450MB), you can start the setup and it’s a real “next-next-finish” setup, as there’s nothing to configure.

You only have to choose into which installation of VS you want to install the extension, but normally there aren’t that many options to choose from.

Click Install and wait for a few minutes.

It’s possible you’ll have to restart the machine after the setup finishes:

I could open an existing project (saved as a SSIS 2019 project) without issue. However, when I changed the TargetServerVersion property of the project, I noticed there’s only 2019 and 2022 available.

This might be because the extension is still in preview, but I’m not sure. Another issue is that when I tried to deploy the project, I got build errors saying the protection level between the project and the package were not the same.

However, they are the same. Both the project and the packages are set to DontSaveSensitive. Opening a package, clicking on the protection level property and saving the package fixes the issue for that package. But you’d have to do this for every package in your project. Normally you can use the batch update property feature of BIDSHelper, as explained in this blog post by Cathrine Wilhelmsen. Unfortunately, this extension has not yet been updated to VS 2022.

Creating a new project is straight forward. Either choose the SSIS project type, or the SSIS Azure-Enabled project type. This last type gives you the option to debug your package on your Azure-SSIS IR in Azure Data Factory, instead of your local machine. For more info, check out the article Azure-Enabled Integration Services Projects in Visual Studio.

Since I mentioned the Azure-SSIS IR, there’s another issue. When you try to deploy to a catalog in Azure, you get the following error message:

But as I pointed out earlier, you cannot set the project to 2017. Whoops. If you’re still using VS 2019 to develop your Azure SSIS project, you might want to hold out on upgrading.

SQL Server 2022 has been released! In this blog post, I’ll walk you through how you can install the latest version of SQL Server on your machine. I’ll be using the developer edition of SQL Server 2022, which can be downloaded here.

The download link will only download a small 4MB file. Opening this file will present you with 3 options:

• you can do a basic installation. This is a “next-next-finish” installation with some defaults selected. Personally, I like to have a bit more control over what happens.
• For this reason, I choose the custom installation. Here you can choose exactly which bits are installed and how they are configured.
• The last option is to download the installation media. For example, when you want to install SQL Server on a machine that doesn’t have an Internet connection.

Next, you can choose where you want to download the installation media. After the setup, this folder remains available, so you can use those bits if you want to install SQL Server on another machine, or if you want to add (or remove) features later on.

Unlike Visual Studio, SQL Server doesn’t have a streaming set-up. Meaning, the setup files are downloaded first, then setup is launched and then you can start configuring and installing SQL Server. Visual Studio on the other hand, does the configuration first and then downloads the necessary bits. While downloading, the installation already begins.

After the download is finished, we are presented with the familiar setup tool:

Go to Installation and then choose to install a new standalone installation. This page also includes download links for SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS).

In the first page of the setup, you need to choose your license. Since we’re using developer edition, we can leave it to the default. An interesting new feature is the pay-as-you-go billing through Azure. Basically you can pay for SQL Server for each hour used (price is per core per hour). More information can be found here. As a side note, this can save you a lot of money when you use SQL Server as a data warehouse. Once the data is loaded into Power BI and the ETL stops running, you can shut down SQL Server until the next ETL run, although I’m not 100% sure you can actually get the costs to zero. Possibly you can only lower your number of cores (when using a VM), so contact your sales rep to be sure.

Of course, don’t forget to accept the license terms:

The install rules are being checked and there’s not much difference with previous versions.

A new page is the Azure Extension for SQL Server, which you apparently need for some features. Since I’m going to use this installation of SQL Server as my personal sandbox, I can disable this feature.

Now we can finally select which features we want to install. Personally, I’m installing the database engine, along with SSIS and SSAS. SSRS has been moved out of the setup since its “joint venture” with Power BI. In contrast with older versions of SQL Server, there’re less features to choose. For example, the client tools, the SDK and distributed replay are gone (this blog post has some screenshots of the full list for SQL Server 2019). Surprised Data Quality Services still makes the list though…

In the next screen, you can choose to either install SQL Server as the default instance, or as a named instance (since I already have an installation of SQL Server on my machine, I can only choose the named instance option).

Then you can configure the service account. Typically I leave everything as the default.

For the database configuration, I don’t think much has changed. I opted for mixed mode for the authentication. You never know when you need some SQL Server authentication. Don’t forget to add yourself as the admin. Since I only have one drive on my machine, I leave everything else as the default. For a real installation of SQL Server, I’d definitely change the data directories so that data (and log files) aren’t stored on the C: drive.

In the config screen of SSAS, I choose for the default Tabular, and add myself as an admin. Again, for real server installations I’d change the data directories as well.

We’re almost done. We get a final overview and then we can click on the Install button.

Grab a coffee or other beverage of your choice and wait till the installation finishes.

After some time, the setup (hopefully) finishes successfully. In my case, I got a pop-up instructing me to restart the computer.

And that’s it. Now we can use SSMS or any other client tool to connect to our brand new server:

I have a blog post series about some nice features in the Snowflake cloud data warehouse; one of them is about the IS [NOT] DISTINCT FROM predicate. I was excited to find out this is now also included in the T-SQL language since the SQL Server 2022 CTP 2.1 preview! You can find the official documentation here.

A quick recap: IS [NOT] DISTINCT FROM allows you to compare two expressions (much like = and <>), but this predicate takes NULL values into account. Basically, it’s a shorter way to write the following:

SELECT *
FROM dbo.FactInternetSales
WHERE OrderDate = ISNULL(@orderdate,'1900-01-01');

Previously, you had to take care of NULL values when working with parameters or nullable columns. Even if you use non-nullable columns, NULL values can slip in when using a LEFT OUTER JOIN for example. So to make sure WHERE clauses or JOINS work like you intended to, you had to do some extra work for those pesky NULL values. But no more! Well, after you’ve upgraded to SQL Server 2022 at least

With IS NOT DISTINCT FROM, we can rewrite the SQL like this:

SELECT * FROM dbo.FactInternetSales
WHERE OrderDate IS NOT DISTINCT FROM @orderdate;

Make a habit of writing your WHERE and JOIN clauses with this new predicate (or any Boolean comparison), it can save you some headaches.

You can find more info about this predicate and other new T-SQL stuff in this excellent blog post by Itzik Ben-Gan: Additional T-SQL Improvements in SQL Server 2022. And check out the official announcement of CTP 2.1 for more new features.

It’s the second Tuesday of the month, so it’s time for the T-SQL Tuesday blog party! This month’s topic is chosen by Camila Henrique (blog): what T-SQL advice would you give to your younger self? Here’s a little list I’d tell anyone starting out with T-SQL:

• you can configure most tools that when you hit Tab, it enters 4 spaces instead
• get the difference between set-based processing and RBAR (aka cursors)
• learn the different types of JOINs
• don’t try to write one giant SQL statement that does it all. Either use common table expressions to clarify your code, or split up and use (temp) tables to store intermediate results.
• learn how indexes work. They’re one of the most fundamental things to learn when it comes to performance tuning.
• learn about SARGable queries, so your indexes are actually used
• learn about window functions, because they’re awesome. I can absolutely recommend the book by Itzik Ben-Gan.
• put everything in source control
• comment your code
• don’t spend too much time on hierarchyid, XML and other exotic stuff, nobody uses it (but you need to learn it for the certification exams)
• learn about dynamic SQL and how you can generate stuff based on metadata
• learn the logical query processing order. E.g. SELECT comes last, FROM comes first.
• get the subtle difference between DISTINCT and GROUP BY to remove duplicates. And don’t forget UNION and UNION ALL are not the same.