Writing Repeatable T-SQL
Writing code for a database is more of a challenge than writing for applications. Before the developers bite my head off, I do not mean that the languages, such as T-SQL, are more difficult or that applications are any less…
Development
SQL Server SCHEMABINDING
SCHEMABINDING is an option that is available for objects in T-SQL which contain user defined code. Examples include, stored procedures, indexes, and functions. Straight from MSDN, SCHEMABINDING… Specifies that the schema is bound to the database objects that it references….
Choosing a Git Client
Version control (VC) is very important for any type of IT shop, big and small, as well as for application code and database code. Most of my career I’ve used Microsoft’s Team Foundation Server (TFS) for my VC system. I have…
Database Deployment Work Flow with SQL Server Data Tools (SSDT)
Happy April Fools Day everyone! I hope that you do not spend too much time reading completely false blog posts today. I, for one, promise that this post is not an April Fools Day joke. Feel free to read on…
Statement was run under snapshot isolation but the transaction did not start in snapshot isolation
My organization enabled READ COMMITTED SNAPSHOT ISOLATION (RCSI) on one of our major systems before we entered a peaking operating cycle. We had tested the impact to our system and felt that it was pretty safe. About half way through…
Stored Procedures, Dynamic SQL and Execution Plans
Question If stored procedure execution plans are cached once and then reused, what version of the execution plan is cached for stored procedures that have dynamically created queries? Answer Dynamic SQL queries and stored procedures generate separate execution plans and…
Transact-SQL COUNT(*) vs COUNT(column) vs COUNT(1)
I have heard a few different beliefs regarding how to write T-SQL queries when using the COUNT() function. Never use COUNT(*), it must read all columns and cause unnecessary reads. Always use COUNT(1) because, generally, the primary key is the…
Derik Hammer Presents, Fun with DATETIME
As my regular readers might have noticed, I get a lot of my ideas for this blog from my daily life. This post is no different. I was helping a co-worker troubleshoot an incorrect result-set. After we had corrected the…
Optimizing for unknown and NULLs
The other day I had an interesting conversation about something that sounds counter-intuitive. If NULL means unknown or missing, then does OPTIMIZE FOR UNKNOWN mean to optimize for NULL? Let’s take a step back for a moment and put this…
How to log errors with T-SQL try/catch
Today a co-worker of mine (Brad) asked a question regarding the usage of T-SQL’s Try-Catch block. Brad wanted to write to a table every time a stored procedure failed so that it was easy to recover the error messages and…