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…
Find out particular occurance of weekday in a month
The following function returns the Nth occurance of week day in a month IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME =’GetNthWeekday’ AND ROUTINE_SCHEMA = ‘dbo’ AND ROUTINE_TYPE=’FUNCTION’)DROP FUNCTION dbo.GetNthWeekdayGOCREATE FUNCTION dbo.GetNthWeekday(@date datetime,–Any date of month under consideration@N int,– Nth…
Finding the DDL Triggers in a database
Below is a method to list out DDL triggers which are created on your database SELECT * FROM sys.triggers WHERE parent_class=0 Please note that DDL triggers are not schema scoped and hence their information wont be available in system catalog…
Generating Calendar Table
Recently I’ve seen couple of posts where posters asks for requirement for generating day by day reports like sales analysis report showing all day info. In such cases, we might need to generate a calendar table on the fly to…