Friday, April 23, 2010

Clean Duplicate records

DECLARE @Count int
DECLARE @name nvarchar(50)
DECLARE @culture_name nvarchar(50)

DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT name, culture_name , Count(*) - 1
FROM resource
GROUP BY name, culture_name
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @name, @culture_name, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM resource WHERE name = @name AND culture_name = @culture_name
SET ROWCOUNT 0

FETCH NEXT FROM dublicate_cursor INTO @name, @culture_name, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor

Tuesday, March 30, 2010

Find the table column order not matching

if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp

create table #tmp (database_name nvarchar(100), table_name nvarchar(100), column_name nvarchar(100), ordinal_position int)


EXEC master..sp_MSForEachDB '
if(''?'' not in (''master'',''msdb'',''tempdb'',''model''))
begin
use [?]
insert into #tmp
select db_name(), table_name , column_name , ordinal_position from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ''users''
end
'
go

select * from #tmp;

select *
from #tmp t1
inner join #tmp t2 on t1.column_name = t2.column_name
where t1.ordinal_position <> t2.ordinal_position

Wednesday, March 24, 2010

A simple way to loop true the time counter

startme:
print convert(nvarchar(100),GETDATE(),109);
waitfor delay '00:00:01';

if(GETDATE() > '2010-03-24 15:18:00.713')
begin
goto endme;
end
else
begin
goto startme;
end

endme:

Wednesday, March 17, 2010

Displaying Execution Plans

Check the query IO, TIME, Execution Plan, Plan Text while running a query

Displaying Execution Plans1

Select Top 100 Slow Queries

SELECT DISTINCT TOP 100
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
WHERE s.execution_count > 1
ORDER BY AvgElapsedTime desc , s.max_elapsed_time DESC, ExecutionCount DESC;
GO

Tuesday, March 16, 2010

View SQL cached Plans and clean the execution cached plans

use Master
go
--clear cache
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go

USE master
GO
--view cached plans
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY dbid,usecounts DESC;
GO

Friday, March 12, 2010

sp_kill

use master
go

if exists (select name from sysobjects where name = 'sp_kill' and type = 'p' )
drop proc sp_kill
go

create proc sp_kill
@dbname varchar(100) = null, -- When specified, kills all spids inside of the database
@loginame varchar(50) = null, -- When specified, kills all spids under the login name
@hostname varchar(50) = null -- When specified, kills all spids originating from the host machine
as
begin
set nocount on
select spid, db_name(dbid) as 'db_name', loginame, hostname into #tb1_sysprocesses from master.dbo.sysprocesses (nolock)
declare @total_logins int, @csr_spid varchar(100)
set @total_logins = ( select count(distinct spid) from #tb1_sysprocesses )
if @dbname is null
begin
if @loginame is null
begin
if @hostname is null
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where hostname = @hostname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
--------------------------------------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame = @loginame and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end

end
-----------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where db_name = @dbname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end

end
drop table #tb1_sysprocesses
end

Thursday, February 18, 2010

ASCII Character Codes

ASCII Character Codes

Is ISOLATION LEVEL READ UNCOMMITTED same as WITH (NOLOCK)

In one word, YES they are the same.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

Check here for more detailed information


Also here is another document :
Read Committed Isolation Level