Press "Enter" to skip to content

Category: T-SQL

Quickly Finding Row Counts for SQL Server Tables

Published 2022-01-31 by Kevin Feasel

Jeff Iannucci doesn’t have time for a table scan:

Have you ever had to find the number of rows in a user table, and then wrote a little “SELECT COUNT(*) FROM tblWhatever” and hit execute…and waited…and waited…and waited some more? And then started wondering what was going on?

If so, this post is for you.

Read on for a stored procedure which gets a nice estimate of the total number of rows. I tend to have a form of the underlying query saved as a snippet so I can use it easily. One thing to keep in mind is that these stats-based counts can be wrong. It’s rare and typically has to do with page splits duplicating values, but on a very large, very busy table you might occasionally be off by a few rows. I might posit in return that on such a table, if precision is that important, the amount of time between querying the stats and doing something with it will probably also cause you to see a difference in row counts anyhow.

Comments closed

An Introduction to ScriptDOM

Published 2022-01-25 by Kevin Feasel

Mala Mahadevan gives us an idea of what ScriptDOM is:

I’ve been meaning to get a series of blog posts started on this topic. A twitter conversation from yesterday finally pushed me to it. Last year, I was tasked with finding a linting tool for the large t-sql code base we have at work. I looked into several tools – tsqllint, Sonarqube and several others. We ran into similar issues on all of them. Someone else defining rules for us didn’t work.
One tool called it wrong to use more than 3 tables in a query – we had several with 6-7, maybe even more. Another started to point out lack of indexes on temp tables as a problem (the rule was designed for table scripts but worked the same on code). Getting new rules that we wanted – such as not having unnaming primary keys on temp tables (Querystore doesn’t like them) or having our naming standards enforced meant extra work on someone else’s code. Our rules were custom to our environment. There were generic best practices for sure, such as finding the SELECT * or NOLOCK hints, but those were a small subset of what we needed. I then started looking for a tool with which I could make a custom linter. That’s when I discovered ScriptDOM, which has been around for a really long time with few people knowing or using it. It took me some time to understand how to put this to use. But after I figured it out it was really easy. Now I have a fairly robust, custom linter in place written in PowerShell and integrated well into our Azure DevOps Build process. It is easy to use and it is owned by us.

Read on to see what ScriptDOM can do and stay tuned to learn more.

Comments closed

TRY_CAST and TRY_PARSE

Published 2022-01-14 by Kevin Feasel

Joe Obbish shows the difference between two functions:

There’s a lot of guidance out there that states that TRY_CAST is a faster, more modern version of TRY_PARSE and that TRY_PARSE should only be used if you need to set the optional culture parameter. However, the two functions can return different results in some cases, even without the culture parameter.

That guidance is blatantly wrong. TRY_CAST() and TRY_PARSE() both came out in SQL Server 2012. TRY_PARSE() uses .NET to perform parsing, which is going to have some edge case differences, especially around cultures and localization. TRY_CAST() is CAST() in an error-safe wrapper. If anything, TRY_CAST() is the “old” version and TRY_PARSE() the “new” version, with scare quotes in place because they both came out at the same time.

Both of them are useful, though I do agree with Joe’s advice of avoiding TRY_PARSE(), at least for larger datasets. If you’re parsing a single date or a small table of dates, TRY_PARSE() does an excellent job because TRY_PARSE('13/01/2019' AS DATE USING 'fr-fr') is not something you can easily do with TRY_CAST() in a US locale.

Comments closed

Solutions for Matching Supply with Demand

Published 2022-01-12 by Kevin Feasel

Itzik Ben-Gan has some solutions to show:

This month, I’m going to start exploring the submitted solutions, roughly, going from the worse performing to the best performing ones. Why even bother with the bad performing ones? Because you can still learn a lot from them; for example, by identifying anti-patterns. Indeed, the first attempt at solving this challenge for many people, including myself and Peter, is based on an interval intersection concept. It so happens that the classic predicate-based technique for identifying interval intersection has poor performance since there’s no good indexing scheme to support it. This article is dedicated to this poor performing approach. Despite the poor performance, working on the solution is an interesting exercise. It requires practicing the skill of modeling the problem in a way that lends itself to set-based treatment. It is also interesting to identify the reason for the bad performance, making it easier to avoid the anti-pattern in the future. Keep in mind, this solution is just the starting point.

Click through for a solution which is straightforward but slow.

Comments closed

Swap an Identity Column with a Sequence

Published 2022-01-05 by Kevin Feasel

Greg Larsen looks at sequences:

Microsoft introduced the sequence number objects starting with SQL Server 2012. A sequence object generates sequence numbers based on starting and increment values, similar to an identity column, but it has additional features. Over time, you might find that the additional benefits of a sequence number have you wanting to replace an identity column with a sequence number. This article demonstrates two options for replacing an identity column with a sequence number.

When it came out, I thought I was going to use sequences a quite often. In practice, I’ve used it a few times, but IDENTITY is so much easier to type and I’ve rarely needed sequence generators. That said, the times I have needed them, I definitely appreciate their existence.

Comments closed

Finding Substrings in a String with T-SQL

Published 2022-01-05 by Kevin Feasel

Kevin Wilkie avoids a regex:

Continuing on with our series from last time – see here if you somehow missed it – let’s have some more fun with the different functions we can use with strings.

This time, let’s focus on looking for different items we can use to find a string within a string.

With T-SQL not natively supporting regular expressions—though you can use a CLR module to do this—click through to see what Kevin uses.

Comments closed

Implementing GREATEST in SQL Server 2019

Published 2022-01-04 by Kevin Feasel

Ronen Ariely is on a mission to be the greatest:

The function GREATEST returns the maximum value from a list of one or more expressions. It returns the data type with the highest precedence from the set of types passed to the function.

This function was added to Azure SQL. At this time, it is supported in Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics serverless. 

Unfortunately, it not yet supported on SQL Server on premises and synapse dedicated sql pool.

Click through for a pair of alternative constructs while we wait for GREATEST on-premises.

Comments closed

Tower of Hanoi in T-SQL

Published 2021-12-29 by Kevin Feasel

Tomaz Kastrun would like to play a game:

T-SQL Code for the popular game of “Tower of Hanoi”, that can be played in Microsoft SQL Server, Azure Data Studio or any other T-SQL editor with support of query execution.

Given that this is the game you use to teach students recursion, I figured a T-SQL based solution would be interesting. Well, Tomaz has the solution and the workspace to play it yourself.

Comments closed

Use TOP instead of SET ROWCOUNT

Published 2021-12-21 by Kevin Feasel

Jared Poche explains why the TOP clause is superior to using SET ROWCOUNT:

I was presenting on how to use the TOP clause to break down large operations into short, fast, bite-sized operations. The mechanics are things I learned from writing processes that do garbage collection, backfill new columns, and anonymizing PII data on existing tables. I’ve just posted the slides and example scripts here if you are interested.

ARE THEY THE SAME?

The question was whether the SET ROWCOUNT command would work just the same, and the answer is sometimes yes but largely no.

Read on to see what Jared means.

Comments closed

SERVERPROPERTY() and DATABASEPROPERTYEX() Views

Published 2021-12-20 by Kevin Feasel

Andy Mallon provides a public service:

The thing I hate the most about these two functions is that you need to know the right magic spells to make them work. Let’s look at SERVERPROPERTY() first. The syntax for the function is SERVERPROPERTY( 'propertyname' ), which is easy enough syntax, but the list of values for propertyname isn’t discoverable from SQL Server metadata, DMVs, or even IntelliSense. Instead, I need to check the docs for the list of allowable values. These property names are essentially magic words, and I need to check my spell book to make sure I get it right.

Invalid values for propertyname just return NULL–which is easy enough to handle, but also means your code will compile and run, but might do unintended things if you get your magic spell wrong, due to a typo.

Click through for Andy’s solution to the problem.

Comments closed