This can be really useful if you need to kill all the connections by a user. An example might be a out of control web application which is running under a certain user. Or because you just want to abort a single users quries because they have left an open transaction and a pile of tables locked for example.

CREATE PROCEDURE [dbo].[KillConnectionsUser] @username varchar(MAX)
AS
	DECLARE @spid int
	DECLARE @sql varchar(MAX)

	DECLARE cur CURSOR FOR
		SELECT spid FROM sys.sysprocesses P
			JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
			JOIN sys.sysusers U ON (P.uid = U.uid)
			WHERE loginame = @username
			AND P.spid != @@SPID

	OPEN cur

	FETCH NEXT FROM cur
		INTO @spid
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT CONVERT(varchar, @spid)

		SET @sql = 'KILL ' + RTRIM(@spid)
		PRINT @sql
		EXEC(@sql)

		FETCH NEXT FROM cur
			INTO @spid
	END

	CLOSE cur
	DEALLOCATE cur
GO

It is really simply to use.

Just call the stored procedure with in the following way where 'Test' happens to be the name of the user you want to kill off.

Here is a q quick exmaple.

EXEC KillConnectionsUser @username = 'Test'

Note: If you use this on your own username it will not attempt to kill its self in the process.

Normally temp table's are only visible to the current connection that they are originally created on. However this is not the case. This can lead to a race situation occuring between mutliple connections using temp tables. Or worse between applications that are using the same constraint name in two seperate temp tables.

If you run the following code on a connection and then on a 2nd connection (in any other database) it will fail!

IF (OBJECT_ID('tempdb..#Import') IS NOT NULL)
      DROP TABLE #Import

CREATE TABLE #Import (
      ID int
      CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
      (
            [ID] ASC
      )
)

The code above will fail with the following error message.

There is already an object named 'PK_ID' in the database.
Could not create constraint. See previous errors.

So there we have it. In sql server 2005/2008 normal temp table's are not just visible to the current connection. The constraint name is global in the temp table. Of course the solution to this problem is easy. That would be to use a guid for the constraint name.

Here is a really simple solutions to create and unsigned int on ms sql server. The following has been tested on sql server 2008. Unfortunatly there is no native support for an unsigned int but we can force the functionality with using a big int and creating a set of rules based on a custom type. If we really still wanted to create an unsigned int we could of course use a .net clr class and build our own type.

CREATE TYPE dbo.uint
      FROM bigint
GO

 
CREATE RULE uint_range
      AS @i >= 0 AND @i <= 4294967295
GO

EXEC sp_bindrule 'uint_range', 'uint'
GO

These sorts of rules are also useful for creating any custom type. eg phone number or such as well. The major difference is the rule is on the type not on the table constraint so they can be changed in all table's where they are being used at the same time.

In MSSQL its possiblt to convert an ip address on the fly so that it makes it possible todo ip range search on text based ip addresses. I found out about this on another posted article. However it has a little issue if you want to scale.

However i did't like the method that was used. As its doing the compares it is extremly slow to search on calculated values all the time. So i came up with the following functions. I dropped the use of the PARSENAME function because it isn't deterministic. In these functions you can create a pre computed field on existing data on the function, add an index, then perform the ip range quriy using index seek's vs an index scan. Or you could just simply convert the ip address into a bigint and store it into the table instead, this would save a lot of space on larger table's.

CREATE FUNCTION [dbo].[IPToBINT] (@ip varchar(max)) 
	RETURNS bigint
	WITH SCHEMABINDING
AS
BEGIN
	DECLARE @idx1 int
	DECLARE @idx2 int
	DECLARE @idx3 int
	DECLARE @idx4 int
	DECLARE @ret bigint
	
	SELECT @idx1 = CHARINDEX('.', @ip)
	SELECT @idx2 = CHARINDEX('.', @ip, @idx1+1);
	SELECT @idx3 = CHARINDEX('.', @ip, @idx2+1);
	
	SELECT @ret = CONVERT(bigint, SUBSTRING(@ip, 0, @idx1)) * POWER(2, 24) +
					CONVERT(bigint, SUBSTRING(@ip, @idx1 + 1, @idx2 - @idx1 - 1)) * POWER(2, 16) +
					CONVERT(bigint, SUBSTRING(@ip, @idx2 + 1, @idx3 - @idx2 - 1)) * POWER(2, 8) +
					CONVERT(bigint, SUBSTRING(@ip, @idx3 + 1, LEN(@ip) - @idx3))
	RETURN @ret
END
GO

CREATE FUNCTION [dbo].[BINTToIP] (@ip bigint)
	RETURNS varchar(16)
BEGIN
	DECLARE @ret varchar(16)
SELECT
	@ret = 	CONVERT(varchar, @ip / POWER(2, 24)) + '.' +
				CONVERT(varchar, @ip  / POWER(2, 16) & 0xFF) + '.' +
				CONVERT(varchar, @ip / POWER(2, 8) & 0xFF) + '.' +
				CONVERT(varchar, @ip & 0xFF)
	RETURN @ret
END
GO

I managed to come across an unexpected issue today when using C# + MS SQL. I was writting a program that involved doing a lot of quries on a varchar field for merging data into a customer database. It did not perform as expected. Since i had already run my own test quries to make sure the index's had been created correctly and they were being used by the quries when executed.

The main problem when i started to run the program in C# it was only managing around 4-5 quries per second (i was expecting 1000's per second). This occured because the string's in c# was being converted to nvarchar using the SqlCommand.Parameters when it is passed to sql server.

For this example i have create a small test table in sql and a short c# program that does a search.

Our table for the test

CREATE TABLE [dbo].[Test](
	[TestID] [uniqueidentifier] NOT NULL,
	[Name] [varchar](255) NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
	[TestID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_Test_TestID]  DEFAULT (newid()) FOR [TestID]
GO

And a short c# program.

namespace SqlParamsConvert
{
    class Program
    {
        static void Main(string[] args)
        {
            DBConn Conn = new DBConn("Data Source=SQL;Initial Catalog=Testing;Integrated Security=True;Connect Timeout=30;Timeout=120");

            SqlCommand sql = new SqlCommand("SELECT * FROM Test WHERE Name = @Name");
            sql.Parameters.AddWithValue("@Name", "Misc");

            Conn.Execute(sql);
        }
    }
}

So using the sql server profiler i was able to extract the query that was actually being executed. So here is what you would expect to be executed and what is really happening in reality.

SELECT * FROM Test
	WHERE Name = 'Misc'
	
EXEC sp_executesql N'SELECT * FROM Test WHERE Name = @Name',
	N'@Name nvarchar(4)',
	@Name=N'Misc'

The above is only slightly different. Of course you can see the data converstion to nvarchar for the @Name parameter. However it results in the following different in performance in ms sql. Here they have the same timing's because i am only using a small amount of test data but in the real system they were 99% for the index scan and 1% for the index seek over approx 120k rows.

If oyu look into the details properties in the index scan it will show that it has the following

CONVERT_IMPLICIT(nvarchar(255),[Testing].[dbo].[Test].[Name],0)=[@Name]

The solution to this problem is actually really simple. Add the following to control your Parameter types.

sql.Parameters["@Name"].SqlDbType = SqlDbType.VarChar;