Newsequentialid (Histrory/Benefits and Implementation)

            In general, we made significant improvements in SQL Server scalability during Yukon.  One of the areas of improvement is replication scalability.  While doing merge replication testing we found out that scaling was severely affected by high number of I/O operations.  The cause of the problem was that new rows were inserted in random disk…

15

SQL Server 2005 Debugging Requirements

There have been some questions about the sysadmin requirement of SQL Server 2005 Debugging, and I’d like to explain it in some details.  When you debug T-SQL or CLR code in SQL Server 2005, there are two users involved: user running the debugger and user making the connection that is being debugged.  User running the…

6

6.0 Best Programming Practices

  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 to another, then parameterizing the query in the client application code before execution gives some…

6

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…

6

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…

5

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…

5

XML Data Type Limitations

I’d like to take some time today to explain some of the seemingly arbitrary limits placed on the XML data type, specifically those related to ID/IDREF validation, complex XML Schema types, the depth limit for XML data, and the enigmatic “XSD schema too complex” error. ID/IDREF ValidationIf your typed XML document has attributes of type…

5

Three significant Cursor changes in SQL Server 2005

Many ISV applications use ODBC API Server Cursors. One source of developer confusion when coding and debugging API Server Cursors against previous versions of SQL Server (6.5, 7.0, & 2000) was Implicit Cursor Conversions, also known as cursor degradation.   These cursor conversions could change the cursor type requested by the application to another cursor…

5

Infinite recompile message in the errorlog

SQL Server 2005 sometimes outputs an infinite recompile warning to the errorlog.  This blog post explains what this warning is about and what to do if you see it in the errorlog. The new warning has the following format: A possible infinite recompile was detected for SQLHANDLE <handle>, PlanHandle <handle>, starting offset <offset>, ending offset…

5

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…

5