Introducing Multiple Active Result Sets (MARS) (2)

MARS is a powerful tool, but you may shoot yourself by the foot if you don’t use it correctly. In the following several talks, I will talk about how to use MARS safely. This talk will cover the session/request context management. In later talks, I will cover transaction usage, security context usage and yield/resume logic,…


TSQL Basics I: Stored Procedures In An Intrepreted Language

This series of posts will discuss some TSQL semantics and language features that mayconfuse some users new to TSQL.  Specially those users familiar with other popular imperative programming languages and familiar with the SQL query language, but not familiar with TSQL. In this post I consider some interesting semantics associated with TSQL stored procedures (SPs)…


Improving query plans with the SCHEMABINDING option on T-SQL UDFs

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.   Before I jump into the details, let me briefly summarize the take-away: If…


Predicate ordering is not guaranteed

A typical programmer may expect that the predicates are always evaluated in the order that they are specified, but this is not true in database systems. For example, for the following clause,  where col11 = 5 and convert(int, col2) = 100 programmers may think col11 = 5 is always evaluated first, but this is not…


Plan Guides Are Your Best Friends

Plan guides are used to optimize the performace of a query without modifying it. They are intended for advanced users and help in situations where a query submitted through an application can not be changed. For more details on this feature please visit In this blog, I will give some insight into its implementation….


Introducing Multiple Active Result Sets (MARS) (1)

MARS is a new programming interface introduced in SQL Server 2005. It enables multiple result sets to exist at the same time for a given connection. To say it in a simple way, you can make a connection to server and then submit multiple requests to server at the same time, and then read results…


SQL Server Identity column FAQs

Q. How is identity values generated? Does SQL Server internally use lock as synchronization mechanism? A. Identity values are generated in-memory using light-weight synchronization, i.e. Spinlock (roughly speaking, Interlocked* function with yield). When an identity-value is either generated or claimed for use by DML (insert), a log record is used to track that value (OR one greater than it)…


Name resolution, default schema, implicit schema Part IV

This post will talk about implicit schema used in compile plan and conclude the topic of Name resolution, default schema, implicit.    A compile plan may refer to non-qualified schema object. It needs to record that fact an “implicit” schema is used (Name resolution detect such scenario and default schema is recorded in the compile plan,…


Name resolution, default schema, implicit schema Part III

It is time to cover name resolution algorithm in SQL 2005 for objects. [Algorithm] In a nutshell, objects name resolution follows the same rule as types and XML schema collections. However, SQL Server name resolution and execution context used to have special behaviors in SQL2000 as outlined in Section I, not to mention the fact…


Name resolution, default schema, implicit schema Part II

Last section clarified some background, let us continue with Name Resolution Algorithm for Types and XML Schema Collections [Algorithm] As implied by general syntax for referencing entities, types or XML schema collections cannot be referenced across database. If schema name is unspecified, SQL server uses “sys first” algorithm as outlined below. 1.      First, Look in the “sys”…