Category Archives: SQL Server Development

Influencing the Execution Plan

I had a performance problem recently with SQL Server. This post shows an easy relatively hands-off approach to influencing the execution plan. Continue reading

Posted in SQL Server Development | Tagged , , | 10 Comments

HierarchyID in SQL Server 2008

SQL Server 2008 includes a new HierarchyID datatype!

Posted in SQL Server Development | Tagged | 5 Comments

SQL Server Hash Indexes

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

Posted in SQL Server Development | 7 Comments

“Including” Columns in an Index

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

Posted in SQL Server Development | 7 Comments

The sp_getapplock secret

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

Posted in SQL Server Development | 22 Comments

SQL Server Security with EXECUTE AS OWNER

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

Posted in SQL Server Development | 56 Comments

Natural Keys vs Surrogate Keys

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

Posted in SQL Server Development | 9 Comments

Sorting uniqueidentifiers in SQL Server 2005

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

Posted in SQL Server Development | 12 Comments

Are Foreign Keys Bad?

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

Posted in SQL Server Development | 19 Comments