Press "Enter" to skip to content

Category: Stored Procedures

Updates to Open-Source Procedures sp_QuickieStore, etc.

Published 2024-04-15 by Kevin Feasel

Erik Darling has been busy:

Let’s start with giving some credit, here, since I hate an overdue bill.

ReeceGoding reported and very capably fixed an issue with sp_QuickieStore. The problem only occurred when you ran the procedure with @get_all_databases and the new @escape_brackets parameter set to true. Each loop would add an escape character to the search string, which could end up looking like this: %\\\\\[AnyStringStartingAndEndingWithSquareBrackets]\\\\\]%'

That’s a fix in place, and you can also see updates to sp_HealthParser and sp_PressureDetector.

Leave a Comment

Input, Output, & Input/Output Parameters in Oracle & Postgres Procedures & Functions

Published 2024-03-11 by Kevin Feasel

Akhil Reddy Banappagari makes me use too many ampersands:

When migrating Oracle routines to PostgreSQL, handling OUT and INOUT parameters can be tricky. Understanding the distinctions between Oracle and PostgreSQL in how they manage these parameters is essential for a successful migration. This knowledge helps you smoothly adjust your routines, ensuring your code works well without any issues. In this article, we shall explore IN, OUT and INOUT parameters in Oracle and PostgreSQL and understand some of the important differences.

Read on to see how these work in Postgres and Oracle.

Comments closed

Search Patterns in T-SQL

Published 2024-02-23 by Kevin Feasel

Erik Darling puts on the fedora and grabs the bullwhip:

First, what you should not do: A universal search string:

The problem here is somewhat obvious if you’ve been hanging around SQL Server long enough. Double wildcard searches, searching with a string type against numbers and dates, strung-together OR predicates that the optimizer will hate you for.

These aren’t problems that other things will solve either. For example, using CHARINDEX or PATINDEX isn’t a better pattern for double wildcard LIKE searching, and different takes on how you handle parameters being NULL don’t buy you much.

Read on for an example of a terrible search query, a mediocre search query, a good search query, and a possible unicorn: an actually valid reason to use a non-clustered columnstore index.

Comments closed

Community Updates to Community Stored Procedures

Published 2024-02-22 by Kevin Feasel

Erik Darling shares some updates:

If you’re the kind of person who needs quick and easy ways to troubleshoot SQL Server performance problems, and you haven’t tried my free scripts yet, you’re probably going to keep having SQL Server performance problems.

I don’t get a lot of visitor contributions to my code (and here I thought it was just because it’s perfect), but I had a couple cool recent additions to sp_QuickieStore, my free tool for searching and analyzing Query Store data.

Read on to see what’s new in a few procedures. Also, Die verboten Toten would absolutely be the name of my German punk rock or possibly DC hardcore style band.

Comments closed

Working with Erik Darling’s Stored Procedures in Azure SQL DB

Published 2024-01-31 by Kevin Feasel

Josephine Bush tries out some stored procedures:

Erik Darling, founder of Darling Data, has created these fantastic stored procedures to query SQL Server more efficiently to get health, log, or performance information. I will go through them here regarding using them in Azure SQL database since I don’t have any SQL Servers I manage anymore.

Read on to see which ones you can use in Azure SQL DB and which require SQL Server.

Comments closed

Updates to SQL Server Troubleshooting Stored Procedures

Published 2024-01-10 by Kevin Feasel

Erik Darling shares some updates:

I’ve been doing a lot of work on all of my free SQL Server troubleshooting stored procedures lately.

If you haven’t used them, or haven’t even heard of them, now’s a good time to talk about what they are, what they do, and some of the newer features and functionality.

Read on to see what’s new. If you haven’t used any of Erik’s procedures, I highly recommend them.

Comments closed

Stored Procedure Wrapup

Published 2024-01-08 by Kevin Feasel

Erik Darling wraps up a series on stored procedures. First, cursors and loops:

You will, for better or worse, run into occasions in your database career that necessitate the use of loops and cursors.

While I do spend a goodly amount of time reworking this sort of code to not use loops and cursors, there are plenty of reasonable uses for them.

I do think we push the “don’t use cursors or loops” thing a little too hard in the SQL Server world, but I also think that a majority of cases in which you’re doing something in a loop, you should be doing it in code outside of SQL Server.

Erik then wraps things up for real:

The general idea of the series was to teach developers about the types of things I always seem to be fixing and adjusting, so that I can hopefully fix really interesting problems in the future.

Of course, that all depends on folks finding these and reading them. If that were the general sway of the world, I’d probably never had been in business in the first place.

Click through for a listing of all of the posts in the series.

Comments closed

Dynamic Search in SQL Server Stored Procedures

Published 2024-01-04 by Kevin Feasel

Erik Darling isn’t content with simple searches:

Like having a built-in type to make dynamic SQL more easily managed, it would also be nice to have some mechanism to manage dynamic searches.

Of course, what I mean by dynamic searches is when you have a variety of parameters that users can potentially search on, with none or few of them being required.

Erik provides two techniques and contrasts the two, so check it out.

Comments closed