OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature

Using parameterized queries is a well known SQL Server Best Practice. This technique ensures caching and reuse of existing query execution plans (instead of constantly compiling new plans), as well as avoiding SQL injection by mandating that input data be data type safe. See more about SQL Server parameterization Best Practices here: http://blogs.msdn.com/sqlprogrammability/archive/2007/01/13/6-0-best-programming-practices.aspx An application…


SQL Server 2008 : new binary – hex string conversion functionality can dramatically improve related query performance by orders of magnitude.

In previous SQL Server releases it wasn’t possible to convert binary data to string characters in hex format directly, because SQL Server did not have a built-in Transact-SQL command for converting binary data to a hexadecimal string. The Transact-SQL CONVERT command converted binary data to character data in a one byte to one character fashion….


Designing Composite Indexes

 When it comes to creating composite indexes there are two questions I get asked most often by the ISVs I work with as well as their customers. 1.      What is the optimal number of columns to include in a composite index? 2.      What should the order of those columns be? While in most cases the…


How to create an autonomous transaction in SQL Server 2008

I have been asked by many customers and partners, especially those migrating from Oracle, this question: how to create an autonomous transaction in SQL Server? It turns out to be a tricky thing to do since SQL Server doesn’t have built-in autonomous transaction support like Oracle. An Autonomous transaction is essentially a nested transaction where…


UPDATE with OUTPUT clause – Triggers – and SQLMoreResults

NOTE:  the code in this BLOG is TSQL instead of ODBC calls.  Since ODBC can be hard to understand and other API’s will have the same basic issues, I decided to use the simpler and more concise TSQL, which should also appeal to a wider audience.   An ISV I work with recently ran into…


Use SQL Server replay tools to reproduce and resolve customer issues

For many ISVs that run into issues at customer sites it is sometimes difficult to isolate underlying problems, especially on 24×7 production environments, where limitations apply to real time troubleshooting and live debugging. In situations like this, constructing a repro scenario in a separate environment would be ideal to minimize impact to live production system, and…


SQL Server Intermittent Connectivity Issue

Recently many customers of an ISV I work with, reported intermittent connectivity issues when running the ISV application on SQL Server. Some customers reported the issue to be SQL Server 2005 specific. Others stated that they are experiencing the same issue on both SQL Server 2000 and 2005. Due to the intermittent nature, and the…


Using time zone data in SQL Server 2008

  In SQL Server 2008 Microsoft has introduced a number of new date and time data types.  One of these is the datetimeoffset data type. This data type includes an offset from UTC time as well as the datetime value and ensures that the datetime can be retrieved in UTC or a particular timezone based…


Increase your SQL Server performance by replacing cursors with set operations

You have probably heard many times, from different sources, that as a best practice; avoid using TSQL cursors. During a recent visit to a partner we ran into a common cursor case, which I wanted to use as an example to demonstrate why you should avoid TSQL cursors in most cases, and how to convert cursor…


Appending Data Using SQL 2008 Filestream

SQL Server 2008 has a new feature called Filestream, which allows you to save large binary files in the file system, instead of in the database.  This is targeted directly at the scenario that many document management and web applications have today where they save some metadata in the database, with a column holding the…