Recently I was trying to track down a deadlock situation in production that was happening more than I would like (out of about 4,000 calls per day this stored procedure was deadlocking about 10-15 times). The odd thing I noticed was that the isolation level on the connection was serializable…on a select procedure. My thought… Continue reading Transaction isolation level escalation; does it happen?
Just a quick set of notes on the FORMAT function in SQL Server. This function was new with SQL Server 2012, and it allows the developer to format the output of a variable or column easily using standard and custom formatting. It works much like the FORMAT function in .NET. I don’t want to bore… Continue reading Tips on using FORMAT
Relational databases are pretty forgiving…it’s usually not too important to try and “match” datatypes when you are working with data, the database engine will usually implicitly (i.e. automatically!) change one of your data types to match the command you are performing and continue on with you none-the-wiser. Cool…however, that implicit conversion is noted (in the… Continue reading Matching Data Types
My post today is more informational than anything else, but I find this information to be useful… When you issue an UPDATE statement in T-SQL, what happens? There’s much more to an UPDATE statement than just changing the data that you requested…read on… (Note that this topic is also relevant to INSERTS and DELETES). What… Continue reading Updates – Behind the Scenes
We’re now using SQL Server 2019, and we’ve been slowly migrating from using the datatype DATETIME to DATETIME2 for some time now. As a refresher, you might want to read my log on this topic: Moving from Datetime to Datetime2 – Steve Henry’s SQL Server Tips (wordpress.com) This entry provides some additional information on the… Continue reading DATETIME Precision Issue
I know, I haven’t posted in a while…blame it on COVID! Anyway, I’ve been busy upgrading our SQL 2014 instances to SQL 2019, so today’s tip is related to my upgrades. In SQL 2017 Microsoft introduced a new function, STRING_AGG. Based on my initial reading it seemed like something we could make use of on… Continue reading Get Rid of FOR XML
What does it mean? Those of us that have used SQL Server know that the database engine will compile our T-SQL code and create an execution plan, which are the “instructions” used by the engine to complete our request. We also know that the engine attempts to reuse these plans as much as possible since… Continue reading Recompiling
So…you need to check for the existence of a row (or several rows) on a table, and branch in your code based on that existence. What is the best way to do that? In the T-SQL examples below I show you three different methods for existence checking; I have seen all three of these methods… Continue reading Checking for Existence properly
…That is the question. SPARSE is a column definition, storage-level option that is available in SQL Server starting with SQL 2008. If you use this option then all NULL values for that column will not use any storage. The downside is that, for non-null values, it takes more storage. SPARSE is not available if compression… Continue reading To SPARSE or not to SPARSE?
How do you insert new rows in a table, but only if they are not already there? I recently reviewed some code by a developer and he wrote a WHILE loop, checking each row individually to see if it already existed, and, if not, inserting the row. Works great, but that’s the slow method…Since we… Continue reading Inserting Rows Only if they do not already exist
Steve Henry's SQL Server Tips 