Get required permissions for DMVs
Part of my job at work is to update Transact-SQL reference content. System dynamic management views (DMVs) have permissions that are managed in the SQL Server Database Engine source code, so it’s a little tricky…
I don’t blog anymore, but not because I work at Microsoft
Last year I had a health scare that got my blood pressure at 200-and-something over 100-and-something, soon to be wired up to an IV drip, put through a CT scanner, and a couple of weeks…
The T-SQL Tuesday Corollary
T-SQL Tuesday is a fantastic series of blog posts derived from over 130 topics over the past 11 years, inviting bloggers to share their thoughts on a particular theme once a month. I’ve even participated…
Remember this if you want to use sp_
Erik Darling, of Erik Darling Data (blog | Twitter) recently posted a very interesting video (my YouTube playlist is all Erik Darling Data videos and Honest Trailers). In this video he demonstrates an easily repeatable…
Using indexed views? What is an imprecise or non-deterministic convert?
After last week’s post about using WITH (NOEXPAND) to query indexed views even on SQL Server Enterprise Edition, this week is a short but interesting side-road into deterministic values, and why it is important to…
Using indexed views? Don’t forget this important tip.
Today after fighting with designing a view that let me create a clustered index on it (indexed views — also known as materialized views — are awesome in the right context!), I kept running into the same…
Proposed SQL Server defaults: max server memory
(This post was updated on 1 August 2022.) A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable…
The easy way to handle UTF-8 in a .NET application when dealing with SQL Server 2019
A short post this week. On a mailing list recently, someone noticed that a .NET application writing to SQL Server did not have the expected behaviour with UTF-8 collation and data types. To refresh our…
A short Azure SQL Edge explainer
On Thursday May 2nd, 2019, Microsoft announced a new edition of SQL Server targeting Internet of Things (IoT) edge devices. That means SQL Server can now run almost anywhere. From the Venturebeat article: It supports…
The case of scalar UDF inlining, where context is everything
Here’s an interesting story for you this week. As part of the new Intelligent Query Processing improvements introduced in the upcoming SQL Server 2019, we find a new feature called scalar UDF inlining. This post…
Proposed SQL Server defaults: optimize for ad hoc workloads
(This post was updated on 5 February 2022.) A few months ago I suggested that the following settings should be the default for most SQL Server instances: Set cost threshold for parallelism to 50 Disable…
Using a home-grown Azure Blob Storage solution for SQL Server backups
I’m here for the small organizations, the shops that can’t afford expensive solutions to maintain their environments. I’m here for them because that’s me: the one-person consultancy. I’ve built stuff that’s useful to me, and…
The free SQL to Excel Export tool
Earlier this month I released a new, free tool to export SQL Server query data to Excel files without needing Excel. It also installs stored procedures and runs them, if that’s what you desire. Go ahead…
Database modelling in a post-SSMS world: dbForge Studio
A few months ago, Microsoft announced that SQL Server Management Studio (SSMS) will no longer include the visual Database Diagrams feature from v18.0 onward. [Edit: Microsoft reversed this decision with SSMS 18.1, and the designer…
Some databases may show a NULL collation in the sys.databases DMV
While working on my Swart’s Ten Percent Rule post last week, I needed to test the Windows version of the script on my SQL Server 2016 instance. Just before removing all the databases, I noticed…