Gokul's Blog


Leave a comment

Delete Duplicate records in Sql

Reference Source link : http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/


/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

Advertisements


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'))
BEGIN
	--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)
	BEGIN
		PRINT '-- Increasing the size of the Name nvarchar column to 255.'
		ALTER TABLE MyDatabase.dbo.MyTable 
		ALTER COLUMN [ColName] nvarchar(255);
	END
END

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;

Console.WriteLine(PageSizebaseThreadSize);

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


Leave a comment

Creating a log file from Stored Procedure

http://www.codeproject.com/KB/database/Stored_Procedure_Log_File.aspx


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
)
as
begin
declare @cmd varchar(2000)
if(@Start = 1)
begin
set @cmd = ‘echo ————–‘+ convert(varchar(10),getDate(),101) +’——- ————— > C:\MyLog.txt’
exec master..xp_cmdshell @cmd
end

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

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)
)
as
begin
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
return
ErrorHandler:
begin
Rollback transaction
set @Msg = ‘Transaction Rollbacked, Error occured’
exec CreateLog @Msg,0
End
End

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;
  MultipleActiveResultSets=true;

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…
http://stackoverflow.com/questions/2355801/sqldatareader-inside-sqldatareader/2355827#2355827


2 Comments

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.
------------------------------------------------------------------ */
SELECT
      [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
            + ';',
      mid.equality_columns,
      mid.inequality_columns,
      mid.included_columns
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