Press "Enter" to skip to content

Category: T-SQL

Building a Series of T-SQL Statements via Regular Expression

Published 2024-08-01 by Kevin Feasel

Andy Levy now has two problems:

Earlier this year, I embarked on a bit of a project to tidy up the indexes in a sizeable database. This database has over 900 tables, and there are quite a few indexes which I have long suspected don’t need to exist for a variety of reasons. Some indexes are redundant, others don’t get used, still others could be combined into a single index. By reducing the number of indexes, we can improve write performance in the database, and the size of the database itself. But we have to do it carefully.

Read on for an example of how you can use a regular expression to take text in one form (DatabaseName.SchemaName.IndexName, for example) and convert it into a T-SQL script.

Comments closed

The Performance Impact of Local Variables

Published 2024-08-01 by Kevin Feasel

Jared Westover talks performance:

Often, developers use local variables when writing ad hoc queries or stored procedures for many reasons. You might hear “never repeat code” or “avoid using magic numbers.” While writing a lengthy stored procedure, I might include a few. However, did you know that local variables can hurt the performance of your queries? How can you keep local variables from negatively affecting performance? Keep reading to find those answers and more.

This is the kind of performance issue that you can easily forget about. Jared includes two methods for resolving the issue if you run into performance problems on a specific query or stored procedure.

Comments closed

Rounding Options in T-SQL

Published 2024-07-29 by Kevin Feasel

Rick Dobson talks rounding:

Please compare the SQL Server round function to banker’s rounding in T-SQL for converting decimal values to integer values. I seek a framework for assessing how closely banker’s rounding results versus SQL Server Round function results match the underlying decimal values. Please provide a couple of empirical comparisons with the framework to indicate which set of rounded values are closer to the underlying decimals and by how much.

Rick talks about what banker’s rounding is and shows how its results adhere more closely to the underlying distribution. Rick does show a user-defined function that generates a rounded number, but if you’re doing this with large enough amounts of data, using CLR and the System.Math.Round() function will likely give you better performance. Incidentally, this is also why if you write T-SQL code to round decimal numbers and .NET code to round numbers, your results may be a little different: T-SQL rounds to the nearest integer, whereas .NET uses banker’s rounding by default.

Comments closed

Comparing Configuration of Two SQL Server Instances

Published 2024-07-25 by Kevin Feasel

Jana Sattainathan checks the labels on these bottles:

A lot of times, you have nearly identical database servers for an application running in Production, Test and Development but you may notice performance differences between them for the same data/queries that you could not attribute to any reason since CPU, Memory, Disk etc., may all be identical.

This is, strictly speaking, a comparison of configurations rather than data differences, indexing, and the like. Nonetheless, it’s useful to make this sort of comparison just to ensure that your instances have your desired state configuration.

Comments closed

Finding Long-Running Queries in Azure SQL DB

Published 2024-07-24 by Kevin Feasel

Kunal Rathi has a script for us:

Identifying and optimizing long-running queries is crucial for maintaining the performance and efficiency of your Azure SQL Database. Slow queries can lead to resource bottlenecks, affecting the overall user experience and system stability. In this post.we will see how to find long running queries in Azure SQL database. Whether you’re a database administrator or a developer, these insights will enable you to ensure your database runs smoothly and efficiently.

Click through for the script. It’ll work not only for Azure SQL DB, but also SQL Server and Azure SQL Managed Instance as well.

Comments closed

Calling a REST Endpoint from Azure SQL Database

Published 2024-07-23 by Kevin Feasel

Meagan Longoria makes a call:

External REST endpoint invocation in Azure SQL DB went GA in August 2023. Whereas before, we might have needed an intermediate technology to make a REST call to an Azure service, we can now use an Azure SQL Database to call a REST endpoint directly.

One use case for this would be to retrieve a file from blob storage. I explain how to set this up below.

Read on to learn more about the process, as well as a few ideas on when you might use it.

Comments closed

Translating Numbers to Words in T-SQL

Published 2024-07-16 by Kevin Feasel

Sebastiao Pereira needs no number:

Sometimes, it is necessary to have numbers spelled out in words, like when writing a sentence (“Two hundred sixty-one victims were hospitalized.”). How do you express numbers in words with T-SQL code?

I could see a bit more recursion in the solution making it tidier, but in fairness, a bit more recursion would make it a lot slower in T-SQL.

For moderate difficulty mode, try it in German. For hard mode, switch to French.

Comments closed

Removing Leading Zeroes from a String in T-SQL

Published 2024-07-12 by Kevin Feasel

Steve Stedman gets rid of leading zeroes:

When working with data in SQL Server, there may be times when you need to remove leading zeros from a string. This task can be particularly common when dealing with numerical data stored as strings, such as ZIP codes, product codes, or other formatted numbers. In this blog post, we’ll explore several methods to remove leading zeros in SQL Server.

I’m not sure I see the reason to use anything other than CAST() (or, better yet, TRY_CAST()), but Steve does show two other methods.

2 Comments

Unicode Characters in SQL Queries

Published 2024-07-10 by Kevin Feasel

Chad Baldwin does some interesting things with Unicode characters:

I don’t want to get too far into the weeds explaining encodings, code points, etc. Mostly because you can just Google it, but also because it’s very confusing. Despite all the hours I’ve spent trying to learn about it, I still don’t get a lot of it. There’s also a lot of nuance regarding encodings when it comes to SQL Server, different collations, and different SQL versions. However, I did come across this blog post that seems to break it down well.

Click through for a few scenarios, including things like tracking progress or marking data in a manner that’s easier to see then “True” and “False” values in columns.

Comments closed

Document Templates in SQL

Published 2024-07-09 by Kevin Feasel

Sebastiao Pereira fills in the blanks:

In some industries, there is a series of repeated content stored about a specific topic, i.e., contracts, terms and conditions, legal agreements, lease agreements, bills, etc., with only minor differences. With these documents, there is a need to just replace specific keywords. What is the most efficient way to maintain consistency, data accuracy, and optimize storage?

This is a technique I’ve used a few times, and there’s no real trick to it: just pick something as your tag identifier that will never appear in the text itself.

Comments closed