Performance of sys.partitions
After a question on #sqlhelp, I dug into what could be making sys.partitions slow, and how they might get the required information in a more efficient way.
Category: SQL Server
Patch Tuesday 2021-01-12 : SQL Server Updates
A security fix for an elevation of privilege issue has been released for all supported versions of SQL Server.
A use case for sp_prepare / sp_prepexec
I explain a recent case where sp_prepare came in handy: trying to validate a plan guide's impact.
Navigating DBCC CHECKDB for VLDB
I recently came across an interesting issue with CHECKDB snapshots, as well as a bit of information missing from the documentation.
Parse parameter default values using PowerShell – Part 3
More background on ParamParser, some recent changes, and several syntax examples.
T-SQL Tuesday #131 : My favorite SQL Server analogy
Rob Volk asks us to share our favorite analogies that help explain database-related concepts to less technical folk.
SQL Server 2019 Cumulative Update #8
SQL Server 2019 Cumulative Update #8 is available, with 74 enhancements. The build number is 15.0.4073.23.
Parse parameter default values using PowerShell – Part 2
I've made the GitHub repository for ParamParser public; here is some more background behind this project.
SQL Server 2016 SP2 Cumulative Update #15
SQL Server 2016 SP2 Cumulative Update #15 is available, with 21 enhancements. The build number is 13.0.5850.14.
Parse parameter default values using PowerShell – Part 1
I've started a new project to parse default values from stored procedures and functions.
Please don't use sys.sp_columns!
If you ever use sys.sp_columns as shorthand for catalog views, please don't, and I'll tell you why.
When pigs fly : My first Windows PC build in over a decade
Whether you love or fear new PC builds, it's not every day a Mac person builds a Windows PC. See the parts I chose and how much more economical this option can be.
SQL Server 2019 Cumulative Update #7
SQL Server 2019 Cumulative Update #7 is available, with 56 enhancements. The build number is 15.0.4063.15.
Please stop using this UPSERT anti-pattern
There is a very common anti-pattern you should avoid, involving updating a row if it exists and inserting it if it doesn't. See how to avoid race conditions and deadlocks.
Sometimes you CAN upsize a column in-place
Altering a fixed-width column on a large table can often mean either a lot of planning or a lot of downtime, but in some scenarios there may be an easy out.
Altering a column ONLINE might still be a disruptive, size-of-data operation
We had a case where we increased a varchar column's size, ONLINE, but it caused significant downstream effects. See why.
Help improve STRING_SPLIT without threatening backward compatibility
There are some limitations with STRING_SPLIT that could be overcome, but the cleanest solution might be to add a new function altogether.
SQL Server 2016 SP2 Cumulative Update #14
SQL Server 2016 SP2 Cumulative Update #14 is available, with 18 enhancements. The build number is 13.0.5830.85.
SQL Server 2019 Cumulative Update #6
SQL Server 2019 Cumulative Update #6 is available, with 51 enhancements. The build number is 15.0.4053.23.
A reliable and flexible replacement for sp_MSforeachdb
Find out about the replacement I wrote for the undocumented, unsupported, and ill-advised system procedure, sp_MSforeachdb.
Troubleshooting Error 18456
Updated in 2020 with a few new entries, this is a fairly comprehensive list of the reasons behind various 18456 error messages.