T-SQL

Implicit Conversion Mine Field: Blowing SARGability away!

Implicit Conversion Mine Field: Blowing SARGability away!

Implicit conversions are convenient. Implicit conversions are EVIL! When talking to professionals about implicit conversions, I’ve heard a few different comments, such as: (a developer) Don’t waste time converting if the engine does it for you. (a DBA) Sure it…

Fast, light-weight, row counts

Fast, light-weight, row counts

Often we need to know the number of records in a table. If you are looking for something very specific, such as the number of non-NULL values in a particular column, learn how to use COUNT() correctly with this post…

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…