Query for Powers of 2 in SQL Server 2005

I had a table with two columns: ID and Description. The task was to display the description of all elements whose ID is a power of two. Here’s the query that let me accomplish that:
SELECT ID, Desc FROM tblBits WHERE ID > 0 AND ID & (ID-1) = 0
How does this work?
Every power of two [...]

Computed Columns in SQL Server 2005

Using Persited Columns in SQL Server 2005 via t-sql and sql server management studio

Concatenating column values in SQL Server 2000/2005

If you want to concatenate values from a specific column like for example:
Table=====1    One2    Two3    Three4    Four

to the following:
Output======One, Two, Three, Four

Here is a simple way to do it:
SQL Server 2005:
SELECT STUFF(SELECT ‘, ‘ + ColumnName FROM TableName FOR XML PATH(”),1,2,”)

SQL Server 2000:
DECLARE @t varchar(4000)SET @t = ”SELECT @t = @t + ‘, [...]

Convert column data from Binary to String with SQL Server 2005

I had this binary column in SQL Server 2005 which was storing the MD5 hashes of certain files. The requirement was to display these hashes in a ASP.NET web UI. So i set about the task of converting the same. Binary data as such cannot be displayed on the UI. It needs to be converted [...]

Join Multiple OPENXML queries

If you ever wanted to run multiple openxml queries and join them give this a go:
DECLARE @idoc intDECLARE @idoc1 intDECLARE @doc varchar(1000)DECLARE @doc1 varchar(1000)SET @doc =’<ROOT><Customer CustomerID=”1″ ContactName=”Balaji Ramesh” /></ROOT>’SET @doc1 =’<ROOT><Customer CustomerID=”1″ ContactNumber=”1234567890″ /></ROOT>’–Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @docEXEC sp_xml_preparedocument @idoc1 OUTPUT, @doc1– Execute a SELECT statement that [...]

View contents of a Stored Procedure (quickly)

I am currently working on a SQL Server 2005 database that has over 500 stored procedures on it. And finding them on the object explorer has been tedious if i just need to view its contents. So i prefer typing in ‘SP_HELPTEXT <procname>’ to view the contents of the procedure. But SQL Server management studio [...]

System functions in SQL Server 2005

Want to know what functions are available in your database but don’t want to go through the pain of searching help.. well you can actually look for it right in SQL Server management studio. Connect to your database. Expand programmability -> Functions -> System Functions. This lists out all the available system functions. A [...]

Updating Identity Columns in SQL Server

Identity columns are kind of a helpful feature. But there might be times when you would want to change the value of an identity column.Guess what, SQL server does not allow it. I have not come across a way by which you can update the identity column but there sure is a way to insert [...]