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

Find out all FK CONSTRAINT that exist on a table

declare @tableName varchar(200)
set @tableName = 'ssis_tasks'

select * from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME in
(
select k.name from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName
)

Thursday, February 11, 2010

Run SP automatically when SQL Server starts

EXECUTE sp_procoption
@procname = 'usp_StartTrace',
@optionname = 'startup',
@optionvalue = 'on'

Identify Primary Keys and PK column name

SELECT KU.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION

Tuesday, February 09, 2010

Change Tracking with SQL 2008

USE [Test]
GO

DROP TABLE [ChangeTrack]
GO

CREATE TABLE [dbo].[ChangeTrack](
[LineID] [int] IDENTITY(1000,1) NOT NULL,
[Fname] [nvarchar](100) NULL,
[Lname] [nvarchar](100) NULL,
[InsertDate] [datetime] NULL,
CONSTRAINT [PK_ChangeTrack] PRIMARY KEY CLUSTERED
(
[LineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChangeTrack] ADD CONSTRAINT [DF_ChangeTrack_InsertDate] DEFAULT (getdate()) FOR [InsertDate]
GO
ALTER TABLE [dbo].[ChangeTrack] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)
GO

declare @synchronization_version bigint , @last_synchronization_version bigint

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
waitfor delay '00:00:05'

--load table
insert into [ChangeTrack] (Fname, Lname ) values ('Pat','Rick'), ('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick')
waitfor delay '00:00:05'

--Obtain initial data set.
--SELECT * FROM [ChangeTrack]


--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1302
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'

--some updates
update [ChangeTrack] set Fname = Fname + '_CHANGED' where LineID % 10 = 4
waitfor delay '00:00:05'

--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1312
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'

--some insert/update/delete
insert into [ChangeTrack] (Fname, Lname ) values ('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111')
update [ChangeTrack] set Fname = '123' where LineID % 10 = 5
delete from [ChangeTrack] where LineID % 10 = 6
waitfor delay '00:00:05'

--select * from [ChangeTrack]

--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1313
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION()

PIVOT , UNPIVOT

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO

select * from pvt

--Unpivot the table.
SELECT VendorID, Employee, Orders
into #tmp
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

select * from #tmp
select * from pvt

-- pivot table
select *
from #tmp
pivot
(
sum(orders)
for employee in (Emp1, Emp2, Emp3, Emp4, Emp5)
) as p

---------------------------
--dynamic creation
declare @str nvarchar(1000)
set @str = ''
select @str = @str + employee+ ',' from #tmp group by employee
set @str = left(@str, LEN(@str)-1)
select @str

declare @sql nvarchar(1000)
set @sql = '
select *
from #tmp
pivot
(
sum(orders)
for employee in ('+@str+')
) as p'

print @sql
exec sp_executesql @sql
---------------------------

Monday, February 01, 2010

Data Type GEOMETRY usage to draw my face

SELECT geometry::Point(x, y, 0).STBuffer(0.4) As sig
FROM ( VALUES
(02,16),(02,17),(02,19),(02,20),(02,25),(02,26),(02,27),(02,28),(03,29),(03,30),(02,31),(02,32),(02,33),
(03,15),(03,22),(03,23),(03,24),(03,26),(03,27),(03,28),(03,29),(03,30),(03,31),(03,32),(03,33),(03,34),(03,35),(03,36),
(04,31),(04,32),(04,33),(04,34),(04,35),(04,36),(04,37),
(05,35),(05,36),(05,37),(05,38),(05,39),(05,40),
(06,23),(06,26),(06,37),(06,38),(06,39),(06,40),
(07,23),(07,24),(07,26),(07,38),(07,39),(07,40),(07,41),
(08,16),(08,22),(08,23),(08,24),(08,26),(08,27),(08,40),(08,41),(08,42),
(09,12),(09,13),(09,23),(09,24),(09,26),(09,27),(09,41),
(10,12),(10,13),(10,23),(10,24),(10,25),(10,26),(10,27),(10,43),
(11,13),(11,23),(11,24),(11,25),(11,39),(11,41),
(12,02),(12,13),(12,22),(12,23),(12,25),(12,38),(12,39),(12,40),(12,41),
(13,01),(13,02),(13,10),(13,13),(13,16),(13,17),(13,39),(13,40),(13,41),(13,42),
(14,01),(14,02),(14,07),(14,12),(14,39),(14,40),(14,41),(14,42),
(15,01),(15,07),(15,39),(15,40),(15,41),(15,42),(15,43),
(16,01),(16,15),(16,40),(16,41),(16,42),(16,43),
(17,01),(17,10),(17,16),(17,40),(17,41),(17,42),(17,43),
(18,01),(18,10),(18,13),(18,16),(18,40),(18,41),(18,42),(18,43),
(19,01),(19,10),(19,13),(19,16),(19,23),(19,24),(19,25),(19,39),(19,40),(19,41),(19,42),
(20,01),(20,06),(20,10),(20,11),(20,12),(20,22),(20,23),(20,24),(20,25),(20,26),(20,40),(20,41),(20,42),
(21,02),(21,03),(21,05),(21,06),(21,11),(21,12),(21,22),(21,23),(21,24),(21,25),(21,26),(21,40),(21,41),(21,42),
(22,02),(22,03),(22,12),(22,16),(22,17),(22,22),(22,23),(22,26),(22,27),(22,40),(22,41),
(23,03),(23,04),(23,05),(23,06),(23,12),(23,23),(23,24),(23,27),(23,40),(23,41),
(24,04),(24,05),(24,23),(24,24),
(25,04),(25,05),(25,06),(25,23),(25,24),(25,27),
(26,05),(26,06),(26,07),(26,23),(26,27),
(27,06),(27,07),(27,37),(27,38),(27,39),
(28,07),(28,22),(28,23),(28,24),(28,28),(28,29),(28,30),(28,31),(28,32),(28,35),(28,36),(28,37),(28,38),(28,39),
(29,08),(29,09),(29,24),(29,26),(29,27),(29,28),(29,29),(29,30),(29,31),(29,32),(29,33),(29,34),(29,35),(29,36),(29,37),(29,38),
(30,12),(30,28),(30,29),(30,30),(30,31),(30,32),(30,33),(30,34),(30,35),(30,36),
(31,12),(31,17),(31,18),(31,19),(31,20),(31,21),(31,22),(31,23),(31,24),(31,25),(31,26),(31,27),(31,28),(31,29),(31,30),(31,31),(31,32),(31,33),
(32,21),(32,22),(32,23),(32,24),(32,25),(32,26),(32,27),(32,28)

) AS Coordinates(x, y);