Microsoft, SQL, SQL Server, Technical

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 when converted to binary has only one bit set.
For example: 4=100, 8=1000, 16=10000
And one digit less in binary looks like:
3 = 011, 7=0111, 15=01111

So an AND operation between {4,3}, {8,7}, {16,15} and so on will always yield a ‘0’

Advertisements
Code, Microsoft, Performance, SQL, SQL Server, T-SQL, Technical

Computed Columns in SQL Server 2005

“Computed Columns” as the name suggests allows you to set the value of a column based on other columns data. Here’s how you could specify the formula in T-SQL:

--Create Table
CREATE TABLE T1 (
    a INT, 
    b INT, 
    operator CHAR,
    c AS CASE operator
        WHEN '+' THEN a+b
        WHEN '-' THEN a-b
        ELSE a*b
    END
    PERSISTED
) ;

--Insert dummy data into it
INSERT INTO T1 VALUES(1,2,'+')
INSERT INTO T1 VALUES(5,3,'-')
INSERT INTO T1 VALUES(4,4,'')

--View the results
SELECT * FROM T1

Notice the keyword PERSISTED. This will enable the computed column value to be stored physically on to the disk so that it need not be created every time you ask for it. Creating an index on this computed column will improve performance by doing a seek rather than a table scan.

If you want to specify the above computed column in SQL Server management studio directly you can do this:

Modify the table. Go to the computed column specification for the specific column. Expand it and type the following in the “Formula” field:

(CASE [operator] WHEN '+' THEN [a]+[b] WHEN '-' THEN [a]-[b]

ELSE [a]*[b] END)

Enter without line breaks. If you want the column values to be persisted in the storage medium, just change the value of “Is Persisted” to “Yes”

SQL, SQL Server, T-SQL, Technical

Concatenating column values in SQL Server 2000/2005

If you want to concatenate values from a specific column like for example:

Table
=====
1    One
2    Two
3    Three
4    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 + ‘, ‘ + ColumnName From TableName
SELECT STUFF(@t,1,2,”)

STUFF() deletes a specified length of characters and inserts another set of characters at a specified starting point.

Microsoft, SQL Server, Technical

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 to String. So you could go ahead and read byte by byte and convert it into a string, concatenating it as you go along. To facilitate things, SQL Server comes built in with a function that could convert common data types to string. It is called:

sys.fn_sqlvarbasetostr

The above function will in turn call

sys.fn_varbintohexsubstring

in our case because we are trying to convert binary to string. To use the function in a select query just do so:

SELECT sys.fn_sqlvarbasetostr(MD5Hash) From HashTable

where MD5Hash is the binary column you would like to convert.

SQL Server

Join Multiple OPENXML queries

If you ever wanted to run multiple openxml queries and join them give this a go:

DECLARE @idoc int
DECLARE @idoc1 int
DECLARE @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, @doc
EXEC sp_xml_preparedocument @idoc1 OUTPUT, @doc1
— Execute a SELECT statement that uses the OPENXML rowset provider.

SELECT d1.*, d2.ContactNumber
FROM  OPENXML (@idoc, ‘/ROOT/Customer’,1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20)) as d1
INNER JOIN
OPENXML (@idoc1, ‘/ROOT/Customer’,1)
            WITH (CustomerID  varchar(10),
                  ContactNumber varchar(20)) as d2
ON d1.CustomerID = d2.CustomerID

EXEC sp_xml_removedocument @idoc
EXEC sp_xml_removedocument @idoc1

This is a simple example but you get the idea.

Technorati Tags: ,,

Shortcuts, SQL Server

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 provides an easier way to accomplish this.. You can assign a quick kepboard shortcut to access the contents of the procedure. Just go to Tools -> Options -> Keyboard and type in SP_HELPTEXT for a key of your choice. Now just select the procedure name and hit the shortcut key ..

image

Microsoft, SQL Server, Technical

System functions in SQL Server 2005

dbfunctions 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 pretty descriptive tooltip is also available.