Category Archives: SQL Server Development

There are two problems with indexes on large nvarchar columns: You will likely hit the 900 byte limit in your index Indexing large data isn’t efficient anyway. A neat feature of SQL Server is the CHECKSUM() function which hashes your … Continue reading →

A neat feature in SQL Server 2005 is the ability to “include” columns in an index. These included columns are not in the main part of the index, but are additional information in the index. For example, lets say you … Continue reading →

sp_getapplock is not very well advertised in SQL Server 2005, however it is a good way to synchronize code in a stored procedure. Before finding out about sp_getapplock, I would SELECT from a table with an exclusive lock, like so: … Continue reading →

EXECUTE AS OWNER is a great way to limit the permissions of a SQL Server Login. The general idea is to create your stored procedure with the EXECUTE AS OWNER modifier. Any user who has the permissions to execute the … Continue reading →

This blog entry has a good description of the pros (and some cons) of surrogate keys: http://rapidapplicationdevelopment.blogspot.com/2007/08/in-case-youre-new-to-series-ive.html

I had an issue recently where I needed to sort on a uniqueidentifier column and read the data in .Net. I found that .Net sorts Guids differently than SQL Server. You can see for yourself. Run the following code. DECLARE … Continue reading →

The Problem Mike Simpson’s post on foreign keys raises some good points: http://www.slipjig.org/Mike/post/2007/11/Are-Foreign-Keys-Bad–You-Decide!.aspx. The main issue raised is how foreign keys cause deadlocks. In order to avoid deadlocks, you have to acquire locks on records in the same order, always. … Continue reading →