The Argument Against Bit

I know a column of bit data type is very helpful as a flag or a code with only two valid values (three if you count NULL). However, I rarely use this data type to signify codes.

Say the column is used to identify an account status, which could be open or closed. More often than that, though, additional information is usually requested like when was it closed or when was it open. In this case, an AccountOpenDate and AccountCloseDate of date data type is more helpful.

I'm sure bit has its use, but sometimes I believe it's better to use a different data type that can hold other information than just three values.

As always, comments are welcome...


~~ CK

Product() Aggregate Funcion

According to BOL, aggregate functions perform a calculation on a set of values and return a single value. However, I can't find a PRODUCT() function. Looks like it's not one of the aggregate functions provided by T-SQL.

Consider this data set:

custid      Factor
----------- -----------
1           2
1           3
2           4
2           5
2           2
3           6
3           7
3           3
4           8
4           9

To produce this result set:

custid      Product
----------- ----------
1           6
2           40
3           126
4           72

Here's the code:

select custid, POWER(10.,SUM(LOG10(Factor)))
from SampleTable
group by custid

As usual, comments are welcome...


 ~~ CK

The Caveat of IsNull()

IsNullI() is a very handy function to replace the value of an expression with another, if the expression being tested is NULL. However, this function should be use with care. Remember always that the expression being checked and the replacement value should have the same data type or some unexpected (and sometimes undetectable) result may occur. As a rule, the function will return an expression with the same data type of that of the expression being checked.

Considering the following code:

declare @a as bit
select
 test_result = 
    case 
      when isnull(@a, 9) = 9 then 'Nine'
      when @a = 1 then 'One'
      when @a = 0 then 'Zero'
      else  'Unknown'
    end

The above code will never return the value 'Nine', even if at first glance, the code above should return the string 'Nine'.

What's going on? It's because the variable @a is a bit data type. The function will explicitly convert the second expression into the data type of the first expression. On the sample code, 9 will be converted into a bit expression which will return 1. So the expression isnull(@a, 9) = 9 will always be false. The entire code will not return an error and is not immediate visible.

This can also happen with strings. Consider the following code:

declare @a as varchar(5)


select isnull(@a, 'abcdefghij')

The above code will return the first 5 characters ('abcde') only. Why? Because this time, the variable @a is 5-character string. So even if it looks like the entire replacement string should have been returned by the function, again, the second expression will be converted into the first expression resulting to data truncation.

So make sure to check the data types of those two parameters.

As always, comments are welcome...


~~ CK

Replace Multiple Spaces with One

Once in a while I encounter requirements to remove multiple spaces in a string column. My most common solution is to use loop to replace multiple spaces with one. The problem starts when the value that needs cleaning is a column in huge table. There's a possibility that the operation may result in multiple table write or an RBAR (Row By Agonizing Row).

Browsing around I saw this function. The best part of this function is that it's a set-based operation which, most of the time, is faster. With permission to it's original author, I'm posting it here so I can use it next time I need it.

CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN   replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),
  '                                 ',' '),
  '                 ',' '),
  '         ',' '),
  '     ',' '),
  '   ',' '),
  '  ',' '),
  '  ',' ')
END

Big thanks to Michael Mierruth


 ~~ CK

Access Variable Inside a Trigger

Have you ever needed to pass a variable into a trigger? So far, I have not encountered the need to pass a variable to a trigger. I figure, sooner or later, I am going to encounter a similar requirement. And while I can still remember this simple workaround, might as well post it.

The idea is to take advantage of the CONTEXT_INFO statement. CONTEXT_INFO is a statement that returns the context_info value that was set for the current session or batch.

Here’s the remarks excerpt from the BOL:

Multiple active result sets (MARS) enables applications to run multiple batches, or requests, at the same time on the same connection. When one of the batches on a MARS connection runs SET CONTEXT_INFO, the new context value is returned by the CONTEXT_INFO function when it is run in the same batch as the SET statement. The new value is not returned by the CONTEXT_INFO function run in one or more of the other batches on the connection, unless they started after the batch that ran the SET statement completed. 

To illustrate, consider this table:

create table students
(
 StudentId tinyint identity(1,1),
 StudentName varchar(25)
)
insert into students (studentname)
select 'John'
union all
select 'Paul'
union all 
select 'George'

select * from students

This is how the table looks like:

StudentId StudentName
--------- -------------------------
1         John
2         Paul
3         George

Now let's create the trigger..

create trigger trgDelete
on students for delete
as 
begin
 insert into students (studentname)
 values (cast(CONTEXT_INFO() as varchar(10)))
end

Here's the sample code to illustrate...

declare @namevar varchar(10)
DECLARE @BinVar varbinary(128)

set @namevar = 'Ringo'
SET @BinVar = CAST(@namevar AS varbinary(128) )
SET CONTEXT_INFO @BinVar

delete from students 
where studentname = 'Paul'

select * from students

Here is the result after the delete statement...

StudentId StudentName
--------- -------------------------
1         John
4         Ringo     
3         George

This is just a sample that shows how a string can be access inside a trigger. Other data type may also be stored, just don't forget that the value needs to be converted into a varbinary, then just convert it back as needed.
As always, comments are welcome...


~~ CK