Gokul's Blog

Leave a comment

Alter table for column Length

IF EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
	--Increase the size of the [Name] column.
	IF EXISTS (SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale
	FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.MyTable') AND name = 'ColName'
	AND max_length = 200)
		PRINT '-- Increasing the size of the Name nvarchar column to 255.'
		ALTER TABLE MyDatabase.dbo.MyTable 
		ALTER COLUMN [ColName] nvarchar(255);

MSDN Article: http://msdn.microsoft.com/en-us/library/ms190273(v=SQL.90).aspx

Leave a comment

Paging query helper C#

Code snippet which generates paging query 

int RecordCount = 3435354535647829;
int PageSize = 50000;
int MaxThreadCount = 5;
int PageSizebaseThreadSize = (RecordCount / MaxThreadCount) + 1;

PageSize = PageSizebaseThreadSize;


int i = 0;
while(i i = i + PageSize;

Leave a comment

Creating a log file from Stored Procedure


In this example I am going to show how to create log file for stored Procedure.

Log File will be created on Database Server.

Give write permission in c: drive or folder where to create the logfile.

Create a table Person

create table Person
PID int Primary Key identity (1, 1)
,Person_Name varchar(50)
,Person_Address varchar(150) not null

This is test table used in the stored procedure

create procedure CreateLog
@Msg varchar(500)
,@Start bit
declare @cmd varchar(2000)
if(@Start = 1)
set @cmd = ‘echo ————–‘+ convert(varchar(10),getDate(),101) +’——- ————— > C:\MyLog.txt’
exec master..xp_cmdshell @cmd

set @cmd = ‘echo ‘ + @Msg + ‘ >> C:\MyLog.txt’
exec master..xp_cmdshell @cmd

Now I am going to create a stored procedure in which i will insert one record and then update the Person_Address with null value which will throw error and i will record that in logfile.

create procedure PersonUpdate
@PID int
,@Address varchar(50)
declare @LineNumber varchar(500)

begin transaction

insert into Person values (‘Mack’,’New York’)

if(@@error 0) goto ErrorHandler

exec CreateLog ‘Mack , New York inserted in Person table’,1

Update Person set Person_Address = @Address where PID = @PID

if(@@error 0) goto ErrorHandler

exec CreateLog ‘city has been update For give PID’,0

commit transaction
Rollback transaction
set @Msg = ‘Transaction Rollbacked, Error occured’
exec CreateLog @Msg,0

now execute the command

exec PersonUpdate 1,null

Now go to C:\MyLog.txt
and open this file messages are recorded here.

Leave a comment

Multiple data readers in one sql connection

You need to have two nested data readers, and this requires the ADO.NET “MARS” feature – Multiple Active Result Sets.

This is available as of ADO.NET 2.0, and requires a specific setting (MultipleActiveResultSets=true;) in the connection string:

Server=.\SQLEXPRESS;Database=master;Integrated Security=SSPI;

See this blog post for an excellent discussion.

Once you have this, you should be able to have more than one SqlDataReader shared on the sameSqlConnection in your code, and use them independently of one another.

UPDATE: this blog post here mentions that the MARS feature is not available inside the SQL CLR environment 😦 So that won’t work inside a SQL CLR stored proc…


Sql Server Tuning & suggest missing Indexes Query

I came across this great article on SQL server performance & tuning.

Main Page: http://use-the-index-luke.com/. As the author explains in the preface

As it turns out, the only thing developers need to know to write efficient SQL is how indexes work.

Some interesting topics which will help us to fine tune Sql code:

One of my friend(Doug) suggested about this nice little piece of code thought would add here for easy reference.

/* ------------------------------------------------------------------
-- Title:   FindMissingIndexes
-- Author:  Brent Ozar
-- Date:    2009-04-01
-- Modified By: Clayton Kramer <ckramer.kramer @="" gmail.com="">
-- Description: This query returns indexes that SQL Server 2005
-- (and higher) thinks are missing since the last restart. The
-- "Impact" column is relative to the time of last restart and how
-- bad SQL Server needs the index. 10 million+ is high.
-- Changes: Updated to expose full table name. This makes it easier
-- to identify which database needs an index. Modified the
-- CreateIndexStatement to use the full table path and include the
-- equality/inequality columns for easier identifcation.
------------------------------------------------------------------ */
      [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
      [Table] = [statement],
      [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_'
            + sys.objects.name COLLATE DATABASE_DEFAULT
            + '_'
            + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
            + ' ON '
            + [statement]
            + ' ( ' + IsNull(mid.equality_columns, '')
            + CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE
                  CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END
            + mid.inequality_columns END + ' ) '
            + CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END
            + ';',
FROM sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
      INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
      INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
            (SELECT TOP (500) group_handle
            FROM sys.dm_db_missing_index_group_stats WITH (nolock)
            ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
      AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC