Category: T-SQL Fundamentals

Three ways to read the source of a stored procedure, and the trap in all of them: without VIEW DEFINITION, OBJECT_DEFINITION returns NULL rather than an error, which looks exactly like the procedure does not exist.

Two stored procedures create a linked server, and on a modern instance the first attempt usually fails with a certificate error because MSOLEDBSQL 19 encrypts by default. The fix, plus rpc out, OPENQUERY, Msg 7411 and Msg 7202.

DROP TABLE IF EXISTS arrived in SQL Server 2016 and replaced a three-line OBJECT_ID check that everybody had memorised. It is a small feature and it is genuinely useful, but there is more behaviour hiding in it than the syntax suggests.

Somebody hands you a database you have never seen and a value: an email address, a reference code, a customer name. Where is it stored? There is no Ctrl+F for a schema, so the job is to build one.

Every DBA hits this one eventually. A table has two or more rows that are identical in every column that matters, and you need to remove all but one of them. An ETL job re-ran without a dedupe check. An import script fired twice. A missing unique constraint let bad data in through the front…

When someone asks: “What’s the difference between DELETE and TRUNCATE?” Most answers stop at: That is technically correct. It is also not enough. In production environments, choosing between DELETE and TRUNCATE is not about syntax. It is about: This post focuses on the production impact, not the textbook answer. Quick Technical Comparison Feature DELETE TRUNCATE…