SQL Programmability & API Development Team Blog

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...

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...

In SQL Server 2005, certain database maintenance operations or certain dbcc commands such as “dbcc freeproccache” or “dbcc freesystemcache” will clear the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all...

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...

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...

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...

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...

If after upgrading from SQL 2000 to SQL 2005 RTM and/or SP1, you notice decreased OLTP database application throughput, bloated plan cache or out of memory errors, you will need to gather machine configuration information and system performance data in...

Plan cache related performance problems usually manifest in decrease in throughput (or increase in query response time), and some times out of memory errors, connection time outs. None of these symptoms however point to plan cache related performance...

11.1 Temporary Tables versus Table Variables In order to determine if table variables or temporary tables is the best fit for your application, let us first examine some characteristics of table variables and temporary tables: 1. Table variables...

ISV applications today often require international support. Migrating an existing non-Unicode database to Unicode is a commonly discussed topic in the ISV application space. This BLOG entry discusses the general considerations and approaches to migrate...

With 64-bit servers becoming more mainstream and SQL Server customers running more deployments on native 64-bit Windows there is a common misperception that running on 64-bit will always provide an increase in performance over 32-bit. Many customers are...

Introduction Login statistics is a useful mechanism to keep track of login activity on a server system. Availability of such information is a requirement for systems to be certified under Common Criteria. SQL Server 2005 SP1 does not offer such...

An ISV recently found an anomoly in their implementation of a "drop table partition" function which could lead to unexpected partitions being switched out. Typically, to do this "drop partition logic" SQL Server partitions are manipulated using the...

This blog describes how the SCHEMABINDING option specified during creation of T-SQL UDFs may affect query plans involving these UDFs in SQL Server 2005. Armed with this knowledge, you may find that you can dramatically improvement your query performance for free....

The following queries can be used to understand plan cache behavior. It should be noted that for systems with large plan caches, selecting all rows and joining with sys.dm_exec_sql_text is not recommended. To find the size of the plan cache use: ...

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...

In this section we will outline some programming practices for efficient plan cache usage: 6.1 Client Side Parameterization of Queries If your application has repeated execution of the same query with only parameter values changing from query...

12.1 Trace Events 12.1.1 Performance Statistics Trace Event The performance statistics trace event introduced in SQL Server 2005, gives persisted plan and runtime statistics information. With the information part of the trace event data when...

3.1 Costing algorithm change between SQL Server 2000 and SQL Server 2005 RTM In SQL Server 2000 and 2005, the cost of a query depends on 3 factors: number of disk IO requests, number of context switches during query compilation (in 4 ms quantums...

Recently, an ISV I work with ran into an interesting problem with Filtered Indexes. The application does all SQL INSERT, DELETE, and UPDATE operations using individual stored procedures. To improve performance, they decided to create a Filtered Index...

Many applications have a functional requirement for the underlying database to have a case-sensitive sort-order, implying that all character data related operations are case-sensitive. With SQL Server, such databases are created with either the ‘binary...

When SQL Server has an optimal index that satisfies the search predicates of a query the optimizer performs an index SEEK operation as opposed to an index (or table) scan to retrieve the required rows; this is desirable. Based on this, one may be led...

2.1 What is a Plan_Handle Cached compiled plans are stored in the SQLCP or the OBJCP cache stores. A cached compiled plan can be retrieved from either of these cache stores using the plan_handle of the compiled plan. The plan_handle is a hash value...

Using parameters or parameter markers in queries increases the ability of SQL Server to reuse compiled plans. There are two places where parameterization of queries can be done: on the client side application (or mid tier) or on the server side. ...