A Fine Slice Of SQL Server
Olivier Van Steenlant runs a test:
In the previous data recipe, Create a Test Class & First Unit Test with tSQLt, we created our very first T-SQL Unit Test to test the database collation. In this data recipe, we will test the execution of a Stored Procedure. Specifically, we will validate what happens when a new User is added to the user dimension.
Click through to see how it all works.
Comments closedAndy Brownsword brings out the hangman:
We have database roles for reading and writing data but interestingly there’s no role which provides permission to execute procedures.
Most DBAs I’ve worked with – production or development – prefer to use stored procedures for data access rather than an ORM. A role to allow procedure execution would be very handy.
So let’s fix that.
The nice thing about this role is that you can then introduce module signing and allow the stored procedures to do things that you might not want regular users to do, such as truncate tables, start and stop SQL Agent jobs, etc. That way, users don’t have these advanced permissions, but the application (whose account has the stored procedure execution role) can do what it needs to do. You can, of course, also have other roles, like one for the kinds of things I mentioned and another to execute most stored procedures. Maintaining that gets a little trickier, but is doable.
Comments closedSteve Jones susses out a problem:
I had a customer that was looking to document a restore that had occurred on one of their systems and didn’t see it. They had concerns about SQL Server accurately tracking history across time and noted they hadn’t cleaned any history.
We dug through some of their instance jobs and found one that ran sp_delete_backuphistory. The person didn’t realize this removes restore history as well. This post talks a bit about how this works.
Read on for the full story.
Comments closedI love QuickieStore, but I wanted some columns to be at the front end of the results returned. Namely, I wanted top_waits, query_sql_text, and query_plan right after database name. This way I don’t have to scroll over to see those values.
Unfortunately, it would appear that there’s no advanced functionality for column ordering like we have for sp_whoisactive. But that didn’t deter Josephine, and you can grab a copy of an updated script that includes columns in this different arrangement.
Comments closedLet’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_databasesand the new@escape_bracketsparameter 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.
Comments closedAkhil 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 closedErik 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 closedErik 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 closedAndy Brownsword describes working in the data mines:
Here’s the situation: a ticket was raised due to figures on different areas of the site not aligning. The ticket was to understand what was returning different data, why this was the case and investigate how we can resolve it.
Read on for an overview of the problem, what Andy did, and the root cause.
Comments closedDavid Fowler updates an older procedure:
Presenting you with an updated version of our sp_snapshot procedure, to easily create database snapshots.
This new version adds more flexibility to the procedure, allowing you to specify the snapshot’s suffix, add a timestamp and alter the path that the snapshot files are taken to.
Read on for the procedure text and an example of how to call it.
Comments closed