Generate a Sequence number for each row in a table.


Howdy EveryOne

Generating a sequence a number for each and every row in a table…. Hmm if you are familiar with TSQL already I think you are thinking of creating Identity column like me…. (ofcourse if you are not an advanced user)…..

Now you can create the Identity column in a temporary table followed by the same schema of your own table to generate a sequence number for all the rows in your table, But in order to accomplish this task we are using a temporary table in between. If the data too small we might not see any performance issue but if the data is too large (too many record sets) then you are in serious trouble of performance.

How can we accomplish this task without the temp table in between… Is it possible, Yes it is ?

And again Thanks to Kent W. on MSDN forums who shown the best approach to do this….

DECLARE @a table
(
	a int
)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
select row_number() over(order by (select 1)) , a from @a

Concatenate rows into a column separated by a delimiter


Howdy EveryOne

While working on the MSDN Forums for quite a while, I see every now and then  people wants to know how to concatenate a single column value of multiple rows into a single column…. I think it might be better understood by a example

Table A: INPUT

Col1
Row 1 a
Row 2 b
Row 3 c
Row 4 d

Desired output:

a,b,c,d

If this is the case then without using the XML tags previously we used to do the below one

declare @t table
(
	a char
)
insert into @t values ('a')
insert into @t values ('b')
insert into @t values ('c')
insert into @t values ('d')
declare @concate varchar(100)
set @concate = ''
SELECT @concate = @concate +','+ a
FROM @t
select STUFF(@concate,1,1,'')

Although it is a good approach, The select statement (concate = concate+…) needs to be executed in a separate batch. This approach will need an extra lines of code incase if this concatenated value is needed else where….

So, in search of a better solution, I stumbled upon a post written by Kent W. in MSDN forums with FOR XML PATH which will accomplish my task very smoothly.

Below is an example of how to write the above code with the FOR XML PATH

declare @t table
(
	a char
)
insert into @t values ('a')
insert into @t values ('b')
insert into @t values ('c')
insert into @t values ('d')
SELECT STUFF( (SELECT ',' + a FROM @t FOR XML PATH ('')),1,1,'')

Advantages of the second approach:
No need of additional variables.
Can be included in a subquery directly.

TSQL — Find position of a repeated character in the string


Howdy Everyone,

Today I got hit by a simple problem by my friend.. He wanted to find out the position of  a character within in a string when it came for the 4th time within that string… For example if the string is “abc;a123;afgh;ayui” and he wants to know the position of 4th a in the string above.

I suggested him to write a UDF like below which will do the trick.

The while loop can also be replaced with a CTE, but I put down the WHILE loop here for historical reasons.

IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE name = 'charNumIndex' and xtype = 'FN')
	drop function dbo.charNumIndex
GO
CREATE function dbo.charNumIndex
(
	@string			varchar(4000)
	,@lookupChar	varchar(5)
	,@repeatedTime	INT
)
RETURNS INT
BEGIN
	declare @charPosition	INT
	DECLARE @loop			INT
	DECLARE @rtPosition		INT
	set @charPosition = -1
	SET @loop = 1
	WHILE (CHARINDEX(@lookupChar,@string,(@charPosition+1)) <> 0) AND (@loop <= @repeatedTime)
	BEGIN
		SET @charPosition = CHARINDEX(@lookupChar,@string,(@charPosition+1))
		SET @loop = @loop + 1
	END
	IF (@loop > 2)
		SET @rtPosition =  @charPosition
	ELSE
		SET @rtPosition =  0
	RETURN @rtPosition
END

EXAMPLE:
select left(‘http://abcd/abc/abcd&#8217;,dbo.charNumIndex(‘http://abcd/abc/abcd&#8217;,’/’,4))

TSQL — Backup all your databases or selected databases


Howdy Everyone,

Today one of my friend asked me to take a look into his backup script that he is using, to take backup of  all databases in his testing environment.The script is hard coded with the database names (50 of them) and the poor fellow needs to maintain the script as well  add  / delete databases on the request of his team.

I advised my friend to change the script and use the dynamic SQL for this purpose and re-wrote the script for him.

I thought it might be helpful to some one like my dear friend. So I pasted the same in here as my new blog post.

How it Works?

There are few choices I have given to the users for this script

  1. The user can restrict the databases that are needed to be taken a backup
  2. The user should give the backup location in which the backup files needs to be placed
  3. The user can choose the backup file trailer incase if he wishes to add something there & if it is left blank current date in the format YYYYMMDD will be added to the end of the databasename.
What if my friend X above want to take a backup of only certain databases like 10 out of his 50 right now… I thought of that a wrote a lookup table for this (databases_To_be_backedUp).  Insert the database names (10 in my case) that needs to be backed up into the lookup table, so that it will take care of the backup part for you. (Follow Example 4)
All the backup files will be created with the extension of “BAK”
IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE name = 'databases_To_be_backedUp' and xtype = 'U')
	DROP TABLE databases_To_be_backedUp
CREATE TABLE databases_To_be_backedUp
(
	ID				INT		Identity(1,1)
	,database_name	varchar(200)
)
GO

IF EXISTS ( SELECT 1 FROM SYSOBJECTS where name = 'backup_all_databases' and xtype = 'P')
	DROP PROCEDURE backup_all_databases
CREATE procedure backup_all_databases
(
	@backupName_trailer	varchar(20) = '' --- If the parameter is passed the backup names will have the trailer with this
										  -- else by default the proc will add the YYYYMMDD trailer.
	,@backupLocation	varchar(100)
)
AS
BEGIN
	declare @databaseName	Varchar(200)
			,@lookUpCount	int
			,@sql			varchar(1000)

	SELECT @lookUpCount = count(1)
	FROM databases_To_be_backedUp

	IF (@lookUpCount > 0) -- Some databases are inserted into the databases_To_be_backedUp, that means only these databases are needed to be backed up.
	BEGIN
		DECLARE database_cursor CURSOR
		FOR SELECT database_name FROM databases_To_be_backedUp ORDER BY ID
	END
	ELSE
	BEGIN
						  -- The databases_To_be_backedUp is empty so all the databases are needed to backed up.. apart from the system databases
						  -- In this script we are considering the MASTER, MODEL,TEMPDB, MSDB as system databases..
						  -- Incase if you want to change this change the not in parameter for the below select statement.
		DECLARE database_cursor CURSOR
		FOR select name from sys.databases where name not in('master','model','msdb', 'tempdb')
	END
	OPEN database_cursor
	FETCH database_cursor into @databaseName
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		Select @sql = 'BACKUP DATABASE ' + @databaseName + ' TO DISK = ' + '''' + @backupLocation + @databaseName
										+ CASE @backupName_trailer
												WHEN '' THEN convert(varchar(10),getdate(),112)
												ELSE @backupName_trailer
										  END
										+ '.bak'			-- extension  for the backup file
										+ ''''
										+ ' WITH INIT,SKIP'
		print @sql
		exec(@sql)
		FETCH database_cursor into @databaseName
	END
	CLOSE database_cursor
	DEALLOCATE database_cursor
END
GO

-- examples
-- 1 backup all your databases to d:\
exec backup_all_databases @backupLocation = 'D:\'

-- 2 backup with backup as a trailer in the file name
exec backup_all_databases @backupName_trailer = 'backup',@backupLocation = 'D:\'

-- 3 if you want to take backup of only choosen databases rather than every thing
create database a -- creates a temp database a
create database b -- creates a temp database b

insert into databases_To_be_backedUp(database_name) values ('a');
insert into databases_To_be_backedUp(database_name) values ('b');

exec backup_all_databases @backupLocation = 'D:\'

Find MAX number without using MAX,Where Clause, Order by


How can some one retrieve the max of the number from a table using TSQL where you are not allowed to use the MAX function or the order by clause or the Where clause.

I have found out a solution below, although the algorithm can be changed by using different sort techniques. I found this is simple to understand so the users might build upon this

Method 1

create table salaries (empid int identity(1,1), salary int)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go
insert into salaries (salary) values (3000)
go
insert into salaries (salary) values (2400)
go
insert into salaries (salary) values (100)
go
insert into salaries (salary) values (200)
go
insert into salaries (salary) values (1050)
go
insert into salaries (salary) values (8000)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go

select top 1 s1.salary from salaries s1
      inner join salaries s2 on s1.salary> s2.salary
      and s1.empid not in (select s2.empid from salaries s1
      inner join salaries s2 on s1.salary> s2.salary)

Method 2

create table #table
(
	num int
)

insert into #table values (1)
insert into #table values (2)
insert into #table values (3)
insert into #table values (4)
insert into #table values (10)
insert into #table values (6)
insert into #table values (7)
insert into #table values (8)
insert into #table values (9)

declare @max_number int
declare @present_num int
declare @previous_num int

SET @present_num = 0
SET @previous_num = 0
SET @max_number = 0
update t
SET	@present_num = t.num
    ,@max_number = case when @present_num > @max_number Then @present_num  else @max_number end
    ,@previous_num = t.num
FROM #table t

select @present_num ,@max_number,@previous_num

UDF to Split a delimited string and return it as a table


Howdy Every One

Each and everyday we might find out that we need a split a string and return it as a table with rows in SQL. There are many programming languages in which there are built in functions which will do this for you (javascript split). Now if you want the same functionality in SQL, the only choice left is to write your own UDF (as of SQL Server 2008 R2 version).

Below is code of the UDF that might help you out

CREATE FUNCTION dbo.Split
(
 @RowData nvarchar(2000),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 DECLARE @index INT
 SET @index = Charindex(@SplitOn,@RowData)
 While (@index>0)
 Begin
 Insert Into @RtnValue (data)
  Select
 Data = ltrim(rtrim(Substring(@RowData,1,@index-1)))

 Set @RowData = Substring(@RowData,@index+1,len(@RowData))
 Set @Cnt = @Cnt + 1
 SET @index = Charindex(@SplitOn,@RowData)
 End

 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))

 Return
END

TSQL — Delete duplicates in your table


Howdy Everyone,
It will be quite problematic to delete the duplicates in your table, if they are inserted because of an improper insert or some other means (may be bcp was run twice ….. )
In this case the below stored procedure can work, People who want to create can simply create the function out of it , I assume or else just let me know, I will post that too….

IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE name = 'deleteDuplicates' and xtype = 'P' )
	DROP PROCEDURE deleteDuplicates
GO

CREATE PROCEDURE dbo.deleteDuplicates
(
	@tableName	sysname
   ,@check		int = 0	--- 0 just to see what are duplicates, 1 delete the duplicates.
)
AS
BEGIN
	DECLARE @columnsDelimited	NVARCHAR(max)
			,@dySql				NVARCHAR(max)
			,@tran				int
			,@tempTable			sysname
	SET @tran = 0
	SET @columnsDelimited = ''
	SET @tempTable = 'delteDuplicates' + CAST (@@SPID AS VARCHAR)
	BEGIN TRY

		IF NOT EXISTS ( SELECT	1
						FROM	sys.tables
						WHERE	name	= @tableName
					  )
		BEGIN
			PRINT @tableName + ' not exists in the current database'
			RETURN -1
		END
		SELECT @columnsDelimited = @columnsDelimited
									+ CASE LEN(@columnsDelimited)
										WHEN 0 THEN ''
											ELSE ','
										END
									+ QUOTENAME(c.name)
		FROM sys.columns C
		JOIN sys.tables  T
		ON	 C.object_id	= T.object_id
		AND  c.is_identity  = 0
		AND  T.name			= @tableName

		IF OBJECT_ID(@tempTable) IS NOT NULL
		BEGIN
			SET @dySql = 'DROP TABLE ' + @tempTable
			exec (@dySql)
		END

		SET @dySql	= 'SELECT ROW_NUMBER() OVER(PARTITION BY ' + @columnsDelimited  + ' ORDER BY ' +@columnsDelimited + ' ) as row_number_delete, * into '  + @tempTable
						+ ' FROM ' + @tableName
		exec sp_executeSQl @dySql
		SELECT @dySql

		IF (@check = 0)
		BEGIN
			SET @dySql = 'SELECT * FROM ' + @tempTable + ' WHERE row_number_delete > 1 '
			EXEC (@dySql)
		END

		IF (@check = 1)
		BEGIN
			SET @dySql = 'DELETE ' + @tempTable + ' WHERE row_number_delete > 1'
			EXEC (@dySql)

			BEGIN TRAN
				SET @tran = 1
				SET @dySql	= 'TRUNCATE TABLE ' +@tableName
				EXEC ( @dySql)

				SET @dySql	= 'INSERT INTO ' + @tableName + ' (' +@columnsDelimited + ') ' + 'SELECT ' + @columnsDelimited + ' FROM ' + @tempTable
				EXEC ( @dySql)

			COMMIT TRAN
		END

	END TRY
	BEGIN CATCH
		IF (@tran = 1)
			ROLLBACK;
		SELECT 'errored_line '+ CAST (ERROR_LINE() AS VARCHAR) + ' error_msg ' + ERROR_MESSAGE() + ' error_number ' + ERROR_NUMBER()
	END CATCH;
END

GO

--- TEST DATA
drop table abcd
create table abcd
(
	a int
	,b int
	,c int
)

insert into abcd values (1,2,3)
insert into abcd values (1,2,3)
insert into abcd values (1,2,3)
insert into abcd values (1,2,3)
insert into abcd values (2,2,3)
insert into abcd values (2,2,3)
insert into abcd values (2,2,3)
insert into abcd values (3,0,0)

exec deleteDuplicates 'abcd',1