Implementing uniqueness constraints on large columns

SQL Server uniqueness constraints create an underlying unique index. SQL Server index keys may not be more than 900 bytes. Below I discuss how to implement uniqueness constraints with hash indexes when the key size can exceed 900 bytes and give the results of some tests on the relative performance of the hash index approach….


SQL Server Modeling Services announcement

The code name “Oslo” repository now has the official name “SQL Server Modeling Services”. SQL Server Modeling Services will be a SQL Server workload like SQL Server Reporting Services. Details will be announced at the PDC where the most relevant session is You can also read about it online at In Microsoft, like…


Paging SQL Server result sets

Paging through result sets is an approach for reducing the network and client resources used to display large result sets. Essentially, the approach is to load only a page (e.g. 100 rows) of data at a time. It is likely that a user will only want to see a page of data. If they want…


"Oslo" repository lifecycle/versioning whitepaper

Repositories face issues to do with versioning schema and data as well as integrating with an organization’s lifecycle processes. Here is a whitepaper I wrote on how the “Oslo” team thinks about handling various lifecycle/versioning issues The whitepaper deals with the following issues: Application lifecycle management (ALM): Facilitating team development of software in a continuous cycle…


How to make a copy of the repository

Here is a nice article on how to make a copy of the “Oslo” repository so you can later restore it to its previous state This is great for experimenting with the repository and for edit debug cycles with domains. The steps are essentially what we do internally at Microsoft.


Favorite keyboard shortcuts

One of the duties of working at Microsoft is the day-to-day use of pre-beta and pre-release products—we call it eating our own dog food. The idea is to find and fix real-life product issues before customers see them. In honor of the wonderful experience I have had with Windows 7 dog-food, attached are a list of…


SQL Server 2008 error handling best practice

Error handling in SQL Server 2008 needs careful implementation. The Microsoft “Oslo” Repository’s API has the further problem that we cannot mandate the error handling logic in our callers. Thus a stored procedure call could be in a transaction or not and in a try-catch block or not. Below is the pattern we have chosen…


Testing strings for equality counting trailing spaces

The SQL standard requires that string comparisons, effectively, pad the shorter string with space characters. This leads to the surprising result that N” ≠ N’ ‘ (the empty string equals a string of one or more space characters) and more generally any string equals another string if they differ only by trailing spaces. This can…


Instead of triggers over views (part 1)

Views are useful for creating a business entity based view data while allowing for an efficient logical schema. However, views are normally not updatable–limiting their utility. However, SQL Server’s instead of triggers allow many of these views to be updatable. SQL Server view-based instead of triggers can be a tricky to use. Below are some…


Large tuple uniqueness constraints in SQL Server

SQL Server 2008 limits unique constraints to 900 bytes of data per tuple. Here is a technique for enforcing uniqueness over larger tuple sizes. In many cases, the technique is more efficient than SQL Server’s unique constraints and can be used for performance critical cases. Hash index based implementation Essentially, the approach stores a hash…