This blog is to illustrate a scenario where ‘quirky’ update can be used efficiently to provide an optimized set based solution. Recently I was asked by one of guys in a forum to provide an optimized solution for the below…
Convert hexadecimal values stored in string to integer equivalent
There have been a few occasions which I have come across where its required to convert hexadecimal value stored in string value to integer. In such cases a simple cast or convert wont work.Even converting to intermediate varbinary will also…
Calculating business hours
This is a function modified from my earlier blog http://visakhm.blogspot.com/2010/02/generating-calendar-table.html The below function basically calculates the total business hours elapsed between two date periods taking into consideration business start and end times. It also assumes that there’s an existing table…
REPLACE() function – SQL 2008 v/s SQL 2005
This blog is intended for sharing an interesting difference I spotted between the functionality of REPLACE function in SQL 2008 & 2005 versions Consider the following example DECLARE @Test table ( ID int identity(1,1), Val char(10) ) INSERT INTO @Test…
FILESTREAM storage in SQL 2008
Background Of Late, I was involved in a discussion where people asked on methodology to be adopted for storing images as a part of their reporting project using SQL reporting services. One group believed in storing images on file system…
Avoiding deadlocks using new READ_COMMITED_SNAPSHOT isolation level in SQL 2005
Programmer A: There’s a deadlock happening when this procedure is run ProgrammerB: Did you try using NOLOCK hint in SELECT statements? It should prevent most deadlocks from happening … How many times have we heard the above type of conversation…
Capturing audit table values inline a.k.a Composable DML
Auditing is term we come across quite often in our projects where we need to track changes happening to data over a period of time. This article discusses how methodology of capturing audit information has evolved over various versions of…
Aggregating data over time slots
The purpose of this blog is to highlight two things 1. Generic solution for aggregating data over fixed time slots 2. Illustration of passing table valued parameters in SQL 2008 Quite often we’ve had occasions where we required to aggregate…
Importance of specifying length in datatypes
This post is just to emphasize on importance in specifying length while declaring fields,variables or parameters. I have seen quite a few occasions where developers have ignored length part. The problem is depicted below select CAST(‘12.34354’ as numeric) ————————————— 12…
Custom Batch Separator in T-SQL
I happen to see below piece of code recently which I couldn’t compile whereas I saw one of my fellow developers running this in his machine create table #test ( ID int identity(1,1), Val varchar(100) ) put insert into #test…