I show how to enable, disable, drop, and re-create all triggers in a database using dynamic SQL.
SQL Server
Estimated data size as a result of implicit conversions can make a huge difference in the execution plan.
In this tip, I talk about a slightly more flexible way to simulate TRY_CONVERT() on unsupported versions of SQL Server.
If you are still stuck on an unsupported version of SQL Server, there may still be a way for you to have TRY_CONVERT() functionality.
Check this list for possible problems if you have trouble connecting using the Dedicated Administrator Connection.
This was mostly tongue-in-cheek, but I show that there is no performance difference between tabs and spaces. (There is, however, a better reason to prefer tabs: accessibility.)
I address several questions raised during two parameter sniffing webinars I presented with Kimberly L. Tripp and Andy Mallon.
You can now enable this setting at the database level, optimizing performance there while not violating support for other vendor databases (like SharePoint).
See a few creative ways to make use of STRING_AGG() and STRING_SPLIT().
Some very important qualitative questions your company needs to ask itself to be sure you are complying with this new regulation.
Tip : Overview of Database Engine Changes in SQL Server 2017
In this tip, I dig into some of the inner workings of resumable index rebuilds.
Tip : Dealing with the single-character delimiter in STRING_SPLIT function
I illustrate why you should be very careful about oversizing varchar / nvarchar columns.
In this tip, I explain a simple trick to prevent accidental updates or deletes.
Tip : Performance Impact of SQL Server 2016 Row-Level Security
I show a "redundant" non-clustered index outperforming the clustered index with the same key.
I start a new series around disproving prevalent myths regarding SQL Server performance.
I show how to implement simple triggers that maintain trigram-type tables used for better supporting wildcard searches.
In an effort to make leading wildcard searches sargable, I play around with a trigram-type implementation in SQL Server.
I show that DISTINCT and GROUP BY are usually interchangeable, but there are cases where one performs better than the other.
I start digging deeper into the performance of string concatenation methods STRING_AGG and FOR XML PATH.
I take an initial look at the performance of a new function, STRING_AGG, in SQL Server v.Next CTP 1.1.
I describe my steps for setting up a development stack, with VS Code on Mac, managing SQL Server on Ubuntu, inside a Docker container.
After a conversation online, I show where excessive comments in your stored procedures might have an impact on performance.
We've all accidentally placed objects in master. In this tip, I show how you can clean it up a little.
I refreshed the demo kit that helps you learn (or teach!) the ins and outs of SentryOne Plan Explorer.
I explains why I code defensively – matching the case of data type names, even when it is irrelevant.
I run some tests to challenge the notion that CHARINDEX is always faster at pattern matching than LEFT and LIKE.
I talk about two core issues to consider when choosing a standard for stored procedure names.
I continue a series on widening an IDENTITY column, showing how I would attack the problem directly.
I take a look at one of the classic debates in SQL Server: whether to support Unicode.
Tip : Beware of Side-Channel Attacks in Row-Level Security in SQL Server 2016
I follow up on a recent post about DATEFROMPARTS() with a deeper look into the estimates and potential mitigation techniques.
I ask you to vote for and, more importantly, comment on a Connect item aimed at adding MAXDOP controls to statistics updates.
Continuing the #EntryLevel challenge, I discuss optimizing the kitchen sink procedure with dynamic SQL and, optionally, OPTION (RECOMPILE).
For T-SQL Tuesday #78, I take a look at whether RID Lookups are faster than key Lookups, with a small battery of fairly simple duration tests.