Using a filtered index for a unique constraint

I needed to apply a unique constraint on a combination of columns, but only for rows that had not been marked as deleted. After scouring the web, I came to the conclusion that there were two worthwhile ways to achive this.

1. Set up a view that only exposes the rows on which the constraint should act. This needs to be a persisted view.
2. Instead of a constraint, set up a filtered index.

Discussions on Stack Overflow also suggested using triggers to do some magic in the system, but I have a longstanding hatred for triggers and cursors. One could also opt to enforce your uniqueness constraint in all your inserting and updating stored predures, but this is tiresome and error prone.

I opted to do the filtered index, as that seems to be the cleanest way of achieving my goal. Note that filtered indices are only available on SQL 2008. If you have 2005, you'll need to use a different mechanism, like the constraint on the view.

You can find a comparison of unique indices and unique constraints here http://msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx The writer comes to the conclusion that they're pretty much the same, with the index having a few more creation options.

Sample Code

This is the table we will use to illustrate the effect of the index:

CREATE TABLE dbo.SerialisedStock (
SerialisedStockId INT IDENTITY(1,1) NOT NULL,
StockId INT NOT NULL,
StockCode VARCHAR(50) NOT NULL,
IsDeleted BIT NOT NULL
CONSTRAINT PK_SerialisedStock PRIMARY KEY CLUSTERED
(
SerialisedStockId
)

The code to create our index on the table:

CREATE UNIQUE INDEX UK_SerialisedStock_StockNumber ON SerialisedStock(StockId, StockCode ) WHERE IsDeleted = 0

This insert statement will fail if done more than once:

INSERT INTO SerialisedStock
(StockId, StockCode, IsDeleted)
VALUES(1, 'aa',0)

This insert statement can be done multiple times:

INSERT INTO SerialisedStock
(StockId, StockCode, IsDeleted)
VALUES(1, 'aa',1)

Cool Visual Studio 2010 Extensions

Came across the PowerCommands extension for VS2010. Does some cool stuff, like

• giving you a shortcut to reopen recently closed documents (and an optional windows displaying a list of recently closed documents).


• adds a command on the context menu of the solution explorer that will open up a command prompt with the working directory set to the path of the item you clicked on.


• Adds a command to open the containing folder of an item clicked on in the solution explorer.


• copy and paste references.


• email a code snippet.

http://visualstudiogallery.msdn.microsoft.com/en-us/e5f41ad9-4edc-4912-bca3-91147db95b99


For those of us used to scroll in any direction by dragging with the middle mouse button in almost any Microsoft product, this AutoScroll extension will re-add that functionality to Visual Studio 2010. (This is not an invitation for a debate on class sizes!)

http://vs2010autoscroller.codeplex.com/

There are a couple of other add-ons and extensions that I also use (and won't go without) such as Resharper, Ankh (for svn), TestDriven.Net (though Resharper can do the same stuff) and GhostDoc.

A tribute to Joel

I haven't been keeping up on my reading list for the past couple of weeks, and have only just read Joel's announcement that he'll be retiring from blogging. It's a sad day, and I feel a little bit like I'm heading to a funeral.

Joel's writing has been a great inspiration to me, and have stimulated many fiery connerdsations at the pub. I still browse back to some of the articles in his archive from time to time. They have the type of content that makes them remain relevant, in the same way as Peopleware remains relevant, many years after being written.

Cheers Joel, and good luck for the future!

Tips and tricks for writing Windows services in C#

This is not meant to be an exhaustive tutorial on writing Windows services. This is just a couple of items that I tend to forget in the time between writing services. It’d be great if it helps anyone else in the process.

1. If you haven’t done this before, or forgot everything you’ve learned, here’s a tutorial: http://www.grinn.net/blog/dev/2008/01/windows-services-in-c-part-1.html or refer to http://msdn.microsoft.com/en-us/library/zt39148a(VS.80).aspx
2. Put your Business Logic/BOL/code that does something in a separate assembly, or at the very least in a separate method marked public, so that you can add a gui/console application/unit test to your solution to execute this code. It saves a lot of time in debugging code.
3. The default timer in the toolbox, System.Windows.Forms.Timer, does not work in a service. You need to use System.Timers.Timer, which can be added to the toolbox. Refer here for an explanation of why it doesn’t work (Winforms timers need a messagepump on a UI thread): http://msdn.microsoft.com/en-us/library/tb9yt5e6.aspx
4. Remember to stop your timer while processing, and restart it afterwards.To add an installer, right click on the component view of your service, and click “Add Installer”. Don’t bother with manually adding an installer to your project.
5. Add a setup project to your solution to install the service. Add the service’s primary output action to the install, rollback and uninstall actions. If you don’t add it to rollback, a failure after the base.Install(stateSaver) in the installer class of the service, will leave you with a service that is installed, but can’t be replaced or uninstalled by your MSI. When that happens you need to use the InstallUtil command from the command line.
6. To use the installutil from the command line, type "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\installutil" where is the name of the assembly of your service to install the service. Add the /u flag to uninstall the service.
7. Use Debug.Assert(false, "Attach here to debug") to have your service pop up a message to which you can attach the debugger to step through your code. Not absolutely neccesary, but it makes things easier. For that matter, this can also be used when debugging installers, or anything else requiring to be paused at a certain place for attachment, before a breakpoint can be hit.
8. Remember to log exceptions, they will not be logged automatically (maybe if you use the logging enterprise application block?). The eventlog is a good place to do this, but remember to register your source first. This is probably best done in the installer, unless you make it configurable in the service. Creating a source requires admin privileges. Here’s a how-to: http://msdn.microsoft.com/en-us/library/k00ce235.aspx
9. Include something to stop your service after n number of concurrent failures, or to stop logging the exceptions. You don't want to fill up the eventlog with nonsense.
10. Application settings in the config file is used as usual with Properties.Settings.Default.PropertyName
11. The default service name can be set on the properties of the service in the component view. There are a few other useful properties here, like CanPauseAndContinue. Check it out.
12. The account for which to run this service is set in the properties of the ProcessInstaller component in the ProjectInstaller file’s component view.

I’ll add more items to this list as I discover them, but this is all I’ve need so far to write a windows service.