Press "Enter" to skip to content

Day: November 11, 2020

Optimizing Multiple CTEs

Itzik Ben-Gan continues a series on table expressions:

Last month I explained and demonstrated that CTEs get unnested, whereas temporary tables and table variables actually persist data. I provided recommendations in terms of when it makes sense to use CTEs versus when it makes sense to use temporary objects from a query performance standpoint. But there’s another important aspect of CTE optimization, or physical processing, to consider beyond the solution’s performance—how multiple references to the CTE from an outer query are handled. It’s important to realize that if you have an outer query with multiple references to the same CTE, each gets unnested separately. If you have nondeterministic calculations in the CTE’s inner query, those calculations can have different results in the different references.

Say for instance that you invoke the SYSDATETIME function in a CTE’s inner query, creating a result column called dt. Generally, assuming no change in the inputs, a built-in function is evaluated once per query and reference, irrespective of the number of rows involved. If you refer to the CTE only once from an outer query, but interact with the dt column multiple times, all references are supposed to represent the same function evaluation and return the same values. However, if you refer to the CTE multiple times in the outer query, be it with multiple subqueries referring to the CTE or a join between multiple instances of the same CTE (say aliased as C1 and C2), the references to C1.dt and C2.dt represent different evaluations of the underlying expression and could result in different values.

Definitely worth the read.

Comments closed

Azure Data Studio Database Projects

Warwick Rudd takes us through database projects in Azure Data Studio:

For a long time source control for Database Code, has been difficult or costly to implement and use.

With the ever expanding list of resources available for Azure Data Studio, we can now do even more while staying inside of a single tool allow us to be more productive and take advantage of implementing and using source control in our environments.

In the September 2020, release we have a new extension – Database Projects that I recommend you install and have approved if needed in your environment making your life easier with your database development being incorporated into source control.

I’ll stick to the ones in Visual Studio for now, but will check in on this in a couple releases to see if there’s enough value in here to make the switch.

1 Comment

String Concatenation in SQL Server

Guy Glantser hits on a pain point in SQL Server when dealing with long strings:

Now, let’s talk about concatenation. What do you think would be the data type of the following expression?


Correct! It’s NVARCHAR(7). Everything is making sense. Isn’t it great?

Now, let’s complicate things just a little bit. Suppose you have an expression that is a concatenation of two string literals – one of them contains 3,000 characters and the other contains 2,000 characters.

Guy also has a function to print beyond 4000 Unicode characters:

Sometimes, you want to print a long string. For example, you might want to print the definition of a long stored procedure. Or you might have a very long dynamic batch that you are going to execute, but you want to print it first for debug purposes.

The problem with the PRINT statement is not only that it prints up to the first 8,000 bytes. It also truncates your text without even generating a warning.

This is a long-running frustration of mine, especially when writing out complicated dynamic SQL. I think PRINT should have been changed 15 years ago to handle MAX types.

Comments closed

Infrastructure Notes for RMDBS on Azure VMs

Kellyn Pot’vin-Gorman takes a look at some of the hardware choices you have in Azure, focusing on what works for relational database management systems:

The truth is, its often a combination of database and infrastructure issues that are the cause.  Although many of you may want me to dig into database performance data, I’m actually going to first focus on infrastructure, as it’s the area that most aren’t privy to for Oracle, or for that matter, any database on Azure IaaS.

The topic of infrastructure is an essential one for any database running in IaaS and even more so VMs on Linux, which can be a bit foreign for the Microsoft data specialist.  Yes, this may be intimidating when doing the shift to Linux and understanding some of the nuances to running a database on Linux, but understanding the infrastructure is a key to removing it from the scenario.  Hopefully these tips will assist you, no matter if you’re running Oracle, (MySQL, PostgreSQL or SQL Server) on Linux VMs on Azure IaaS.

Click through for some guidance on the topic.

Comments closed

Renaming a Power Query Column Based on Position

Ed Hansberry shows how to deal with renaming columns whose names regularly change:

The easiest way to rename a column in Power Query is to do it the same way you do in Excel – just double-click it and rename it. Sometimes though the column you are renaming can have different names with each refresh, so this method won’t work. We can use a List in Power Query to make this dynamic. Consider this very simple example:

You receive this file every day, and the column name starting in Row 6, Column B, constantly changes. We are going to extract that date and make it a column, as it should be, and then rename both columns regardless of what they are called.

Read on for the process.

Comments closed

Exporting SQL Server Configurations

Garry Bargsley shows how you can export configuration settings for your SQL Server instances:

Have you ever deleted a login by mistake from a hastily typed TSQL script or dropped a list of logins because the “Business” said they are not used anymore? Have you ever made a change to a SQL Server Agent job and then it failed on the next execution. What about that time you changed the Database Mail profile on all of your servers and left your personal account in the script instead of the DBA distribution list.

While each of these examples is not life-threatening, they will strike fear in you depending on how prepared you are to recover the items in question.

This is the type of thing you’d want to store in source control, too. That way, you have a record of changes over time.

Comments closed