By Kalman Toth, M.Phil. Physics, M.Phil. Computing Science, MCDBA, MCITP

May 1, 2011

Software engineers don't think of Transact-SQL as a serious language like C++. More in the lightweight category with excellent sql query capacity, manipulating millions of rows at ease. While you cannot do object-oriented programming in T-SQL, you can now in SQL 2005 parse trees with recursive CTEs as an example for advancement in T-SQL. You don't really want to do heavy-duty algorithmic programming in T-SQL anyhow, since it is an interpreted language. If you resort to Java-like programming, make sure the stored procedure or function is not used frequently since inline coding performs better. Below is a sample function to illustrate the tremendous flexibility of the T-SQL language. Bit-wise Boolean operators are used to produce a bit switch display of byte.

Following is the code sample:

USE AdventureWorks

GO

CREATE FUNCTION dbo.fnBinaryPattern

               (@Byte TINYINT)

RETURNS CHAR(8)

AS

  BEGIN

    DECLARE  @Pattern CHAR(8)

    SET @Pattern = ''

    SELECT @Pattern = convert(VARCHAR,+(@Byte & 1) / 1) +

    convert(VARCHAR,(@Byte & 2) / 2) +

    convert(VARCHAR,(@Byte & 4) / 4) +

    convert(VARCHAR,(@Byte & 8) / 8) +

    convert(VARCHAR,(@Byte & 16) / 16) +

    convert(VARCHAR,(@Byte & 32) / 32) +

    convert(VARCHAR,(@Byte & 64) / 64) +

    convert(VARCHAR,(@Byte & 128) / 128)

    RETURN reverse(@Pattern)

  END

GO 

select dbo.fnBinaryPattern(0)
go

00000000

select dbo.fnBinaryPattern(1)
go

00000001

select dbo.fnBinaryPattern(128)
go

10000000

select dbo.fnBinaryPattern(ascii('B'))
go

01000010

select dbo.fnBinaryPattern(ascii('b'))
go

01100010

Related articles:

Bit-Masks

How to use the Boolean / bit data type?

Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise Logic