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

This series of posts will discuss some TSQL semantics and language features that may confuse 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...

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

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

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 http://msdn2.microsoft.com/en-us/library/ms190417...

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

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

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

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

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

This series of blogs describe name resolution behavior of schema objects, types and XML schema collections, and how it interacts with default schema, compile plan implicit schema. The first part will lay some background so that we are at the same page. The following shows the general syntax...