Knowing about ‘Forwarded Records’ can help diagnose hard to find performance issues.

Imagine a customer using an ISV application that stores certain product information in a varchar(200) column in a SQL Server database table. When the system was first being used nobody ever entered a product description with more than 10 characters. However, over time new products were added to the portfolio which required the introduction of a prefix…

0

How are GUIDs compared in SQL Server 2005?

In general, equality comparisons make a lot of sense with uniqueidentifier values. However, if you find yourself needing general ordering, then you might be looking at the wrong data type and should consider various integer types instead. If, after careful thought, you decide to order on a uniqueidentifier column, you might be surprised by what…

4

Resolving Login Errors with Duplicated Databases

Many ISVs and ISV customers often have a need to duplicate their database server in entirety onto another computer system.  Such duplicated copies are required often required for development and test purposes and need to be identical to the original database server in all respects.   Copying the database itself is easy and can be…

1

XML: Typed and Untyped

Occasionally we hear from people who are surprised to find that their XML data uses more space when typed than when untyped. In general, this is to be expected. Typed XML has some advantages over untyped XML, namely smarter query plans and the ability to constrain user input, but size usually isn’t one of them….

3

Implementing Login Statistics Using SQL Server 2005 Trace Feature

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 information. This post will discuss ways to implement it using existing features of SQL…

4

SQL Server 2005 UNICODE considerations.

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 a database to Unicode with SQL Server.   SQL Server Unicode Support   SQL Server Unicode data types support UCS-2…


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

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

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,…

2

Impersonation in SQL CLR

When the user code will try to access a resource outside of the SQL server it will run in the security context under which SQL Server is running. But this is not the ideal situation since it could become a security issue when the logged user that calls the code has less privileges than account…

1