How to figure out what/who is connected to your SQL server

Had an issue recently trying to figure out what application was holding a connection open on a SQL server, and here's the lowdown on tracing it down. Firstly, go into a query window and do the following:

sp_who
select spid,hostname,hostprocess from master.dbo.sysprocesses

The 'sp_who' will list the open connections. Look through this list to find any where the ‘dbname’ column is the database you're interested in, and then look for the value in it’s SPID column. The 'sysprocesses' query will give you, for each SPID, the hostname and windows process id (‘hostprocess’) which you can then look up in the task manager on that particular computer.

Adding a column to a massive Sql server table

Recently, I had to add a column to a massive table with lots of indexes in sql server 2000 (~30mil records). The initial code looked like this:

Alter table MyTable
Add MyNewColumn char(10) not null default '';

Now, we ran this and it took 32 hours (!) to run. Being that we had to update almost a dozen of these tables, I wasn't too keen on 2 weeks of downtime.
After doing some searching, it turns out that the problem is the 'not null', which means that every row gets given a value for that column, and hence the index gets recalculated for each row. After some research, we came up with these steps:

1 - Create the column as nullable (pretty much instant)
2 - Set the values to the default value, doing only part of the table at a time (takes a while)
3 - Change the column to not null (again, pretty much instant)

After writing some sql to do this, I got it down from 32 hours to 20 minutes. The only tricky bit is to write the sql to update the column to the non-null value - you need to have some well chosen where clause there to divvy the table up into a million records at a time. Best of all, since the update are split into separate chunks, it gives us an indication of progress as opposed to scratching our heads wondering if it'll ever finish. Awesome! Here's some code for this:

Alter table MyTable
Add MyNewColumn char(10) null default '?';
go

update MyTable set MyNewColumn='?' where MyPrimaryKey between 0 and 1000000
go
update MyTable set MyNewColumn='?' where MyPrimaryKey between 1000000 and 2000000
go
update MyTable set MyNewColumn='?' where MyPrimaryKey between 2000000 and 3000000
go
..etc..

Alter table MyTable
Alter column MyNewColumn char(10) not null;

Multithreading using Delegates in C#

I've recently been experimenting with C#'s delegates, and here is an example of
how they can be used for multithreading. I've used a ray tracer as a good example
of something that can be multithreaded easily.

Using C# locks and threads to rip through a to-do list

In this example i've got a massive dataset stored (once) in memory with a year's worth of data. I want to get totals for each day's worth of this. So, what i have is 365 (or 366) discrete 'tasks' that need doing, and these are a prime candidate for multithreading because they only *read* from a shared data pool.

Using threads and lock in C#

This is a contrived example of threads and locking in C#. Basically what it does is start 10 threads, and each one is responsible for incrementing a shared value a large number of times.

Calculating an SHA1 hash in Excel

Ever wanted to be able to do this in a cell in Excel?
=SHA1('The quick brown fox jumps over the lazy dog')
And you'd get back:
2fd4e1c67a2d28fced849ee1bb76e7391b93eb12

Setting a form name and onsubmit using form_for in rails 2

Spent ages trying to figure out this:

<% form_for(@photographers,:html=>{:name=>"f",:onsubmit=>"return checkform();"}) do |f| %>
....
<% end %>


Compressing using the 7Zip LZMA algorithm in C# beats GZipStream

I've discovered that the GZip compression built into C# (System.IO.Compression.GZipStream) doesn't really do that good a job at compressing data. I heard (cannot confirm) that MS had to tread lightly around some patents which results in pretty poor compression ratios. I needed to compress some data, and compress it well, as I had a whole bunch of data files from a reporting project totalling up to 5gigs. So the GZipStream wouldn't do.

Bare minimum HTML that validates

I know, you'd think this would be simple, but i keep needing to refer to it:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">