T-SQL

Writing Repeatable T-SQL

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…

SQL Server SCHEMABINDING

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 is bound to the database objects that it references. This…

Multiple Server Management – Finding the Database Server

Multiple Server Management – Finding the Database Server

Often database administrators must ask themselves a simple, yet tedious question, “what server is that database on,” or maybe, “how many servers do we still have on SQL Server 2005?” This article will cover how to use SQL Server’s Central…

Stored Procedures, Dynamic SQL and Execution Plans

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)

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

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

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

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…

How to change the schema of a table

The other day I set out to performance tune a database deployment. The company was getting ready to implement almost a year’s worth of database schema drift where major restructuring had occurred. One of the first things that I noticed…