A Fine Slice Of SQL Server
Jess Pomfret has a Powershell snippet for us:
This morning I was working on pulling together some information which included whether certain accounts were in the local administrator’s group on some remote servers. I had the perfect snippet saved in my code repo so I was quickly able to answer that question – and then I realised I should share that with you all.
Click through for the script.
Comments closedItzik Ben-Gan explains Inline Table-Valued Functions:
Compared to the previously covered named table expressions, iTVFs resemble mostly views. Like views, iTVFs are created as a permanent object in the database, and therefore are reusable by users who have permissions to interact with them. The main advantage iTVFs have compared to views is the fact that they support input parameters. So, the easiest way to describe an iTVF is as a parameterized view, although technically you create it with a CREATE FUNCTION statement and not with a CREATE VIEW statement.
It’s important not to confuse iTVFs with multi-statement table-valued functions (MSTVFs). The former is an inlinable named table expression based on a single query similar to a view and is the focus of this article. The latter is a programmatic module that returns a table variable as its output, with multi-statement flow in its body whose purpose is to fill the returned table variable with data.
Now that we have that sorted, click through to see examples and dive into performance ramifications.
Comments closedShane O’Neill dives right into the deep end:
I once had the annoyingly complex T-SQL to change MS format time into a human-readable format memorised.
To be specific, that’s the nasty integer format used in SQL Agent jobs, a format which hasn’t made sense to use in decades (if it ever did).
Comments closedAndy Yun shows off some random skills:
First, there are times where you need multiple random numbers. Simply using multiple iterations of RAND() in a single statement won’t cut it, as you need to vary the seed. So I keep this snippet handy for when I need a bunch of random values in a single statement:
Click through for that as well as two more uses of RANDOM(). This is my reminder that RANDOM() generates data across a uniform distribution (every value in the range is equally likely to be chosen), making it great for these sorts of experiments but can look weird by itself if you’d expect non-uniform distributions of the data. For that, you would need some distributional trickery—though frankly, between the uniform and normal distributions, you’ve probably covered about 95-99% of test dataset needs.
Chad Baldwin goes the extra mile:
I’m excited that this will be my first time participating in a T-SQL Tuesday topic!
Most of my time is spent writing T-SQL, PowerShell and working in the PowerShell terminal, so that’s how I’ll split the post up.
I had to cut it short otherwise this post would be a mile long. If you’re interested in seeing more quick tricks, SQL Prompt snippets, etc, please leave a comment and let me know and I can do a Part 2 in the future.
Click through for a baker’s dozen plus a couple spares.
1 CommentDeborah Melkin does not take kindly to bugs:
I often find that I have to write complicated stored procedures where I need to check things as I go along. My go-to for using this snippet is when I write stored procedures that use dynamic SQL. You’d be surprised (or not) at how often I have had to do this over the years. There’s been functionality where the user gets to choose the columns being used, rewriting ORM data layer “catch-all” queries to improve performance, and cross database queries where the name of the database may not be the standard name (think development and QA databases living on the same SQL instance.)
Click through for an example of where the @Debug parameter pays off. My recollection was that, for really long NVARCHAR(MAX) strings, running PRINT by itself might cut off the code after ~4000 characters, but that could be a historical recollection.
Deepthi Goguri re-shares some helpful scripts:
For the past couple of years as a DBA, I migrated several databases and used many handy scripts that helped me made my work easier. These scripts may be simple but if you have a migration project involving several SQL Servers with some hundreds of databases, test and production database migrations becomes tedious. I would like to share some of then here which you might already known them very well.
Click through for three scripts.
Comments closedChad Callihan has a few close-to-one-liners:
The most common, short script I could think of may very well be one of the shortest T-SQL scripts that can be executed. When you want to know what version of SQL Server you are connected to, you can run this script:
Read on for that one-liner as well as a few other short scripts.
Comments closedMala Mahadevan has a few short-ish Query Store scripts for us:
I use Query Store a lot where I work – and I’d like to share queries I use on Query Store DMVs that I find incredibly useful.
My favorite is one below, which I use to see stored procedure duration. It comes with other information including plan id, start and end time – all of us help me see red flags right away if there is any query not performing as well as it should.
Click through for that script as well as two more.
Comments closedRob Farley has a quick script to find references in SQL Server:
As a consultant, the kind of work that I do from customer to customer can change a bit. Sometimes I’m reviewing people’s environments; sometimes I’m performance tuning; sometimes I’m developing code or reports or cubes; sometimes I’m writing T-SQL, but it’s often DAX or PowerShell.
Click through for a quick script to search modules for a particular string.
Comments closed