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

Five Things a SQL Programmer/DBA Should Not Do

I read this very nice article here. Here's an excerpt of the entire list. I pick two of the most common case I encounter.

Performance Tuning Queries in Production

As tempting as the urge may be, it’s really not a good idea to query tune directly in your production environment. You’re making changes to your code base without actually knowing for sure, the consequences of your actions. Don’t even get me started on a DBA running Ad-hoc queries live on a production server.

So you’ve identified an index that will make a currently poorly performing query run like lightning, great! Now go away and test it properly in your performance tuning environment so you can see how well it plays with the other queries and be confident that there will be no adverse affects

Making Changes Without Testing (a.k.a Winging-it)

Even if you are a T-SQL Ninja, if you’re making changes to production that you have not tested then in my opinion that makes you a fool. You may think I’m being harsh but the experienced folk out there know that I speak the truth. If you are not testing your changes before they go to production then you are putting the data assets that you are ultimately responsible for at unnecessary risk. Doing so goes completely against your primary responsibility as a DBA. Why take the chance?

To view the complete list, please read this.

A special thanks to John Samson. You can read more about him on his blog.

~~ CK