Comma delimited list creation in SQL Server T-SQL
The following T-SQL scripts demonstrate the building of comma delimited list:
-- EXAMPLE 1 - Using XML PATH (SQL Server 2005 and on)
-- T-SQL create comma delimited list from single column result
SELECT ColorCommaDelimitedList =
Stuff((SELECT ', ' + Color AS [text()]
FROM
(SELECT DISTINCT Color FROM AdventureWorks2008.Production.Product
) x
For XML PATH ('')),1,1,'')
/*
ColorCommaDelimitedList
Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow
*/
------------
-- EXAMPLE 2 - Using XML PATH & CTE (SQL Server 2005 and on)
-- T-SQL create comma delimited list using CTE - Common Table Expression
;WITH cteColor AS
(SELECT DISTINCT Color FROM AdventureWorks2008.Production.Product)
SELECT ColorCommaDelimitedList =
Stuff((SELECT ', ' + Color AS [text()]
FROM cteColor
For XML PATH ('')),1,1,'')
/*
ColorCommaDelimitedList
Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow
*/
------------
-- EXAMPLE 3 - Using local variable (SQL Server 2000 and before)
-- T-SQL creating comma delimited list with local variable & multiple statements
USE AdventureWorks;
DECLARE @CommaLimitedList VARCHAR(MAX) = ''
SELECT @CommaLimitedList = Color + ', ' + @CommaLimitedList
FROM (SELECT DISTINCT Color FROM Production.Product WHERE Color is not null) x
SELECT CommaDelimitedList=@CommaLimitedList
GO
/*
CommaDelimitedList
Yellow, White, Silver/Black, Silver, Red, Multi, Grey, Blue, Black,
*/
------------
-- EXAMPLE 4 - Using XML PATH & correlated subquery for sublist
-- Create comma delimited sublist
SELECT Subcategory = ps.[Name],
ColorList = Stuff((SELECT DISTINCT ', ' + Color AS [text()]
FROM AdventureWorks2008.Production.Product p
WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID
FOR XML PATH ('')),1,1,'')
FROM AdventureWorks2008.Production.ProductSubcategory ps
ORDER BY Subcategory;
GO
/*
Subcategory ColorList
....
Helmets Black, Blue, Red
Hydration Packs Silver
Jerseys Multi, Yellow
....
*/
------------
-- EXAMPLE 5 - Preparing spaces delimited list
-- T-SQL make spaces delimited list of ProductNumbers
SELECT Alpha.List.value('.','varchar(256)') AS DelimitedList
FROM (SELECT TOP ( 5 ) ProductNumber + ' '
FROM AdventureWorks2008.Production.Product
ORDER BY ProductNumber DESC
FOR XML PATH(''), TYPE) AS Alpha(List);
/*
DelimitedList
WB-H098 VE-C304-S VE-C304-M VE-C304-L TT-T092
*/
------------
--source: http://www.sqlusa.com/bestpractices/training/scripts/commadelimitedlist/
--other examples: http://www.sqlusa.com/bestpractices/
use superliga
drop table matches
drop table teams
go
create table teams
(
Id char(3) primary key,
name varchar(40),
nomatches int,
owngoals int,
othergoals int,
points int
)
create table matches
(
id int identity(1,1),
homeid char(3) foreign key references teams(id),
outid char(3) foreign key references teams(id),
homegoal int,
outgoal int,
matchdate datetime
)
insert into teams values('agf','AGF',0,0,0,0)
insert into teams values('sif','Silkeborg',0,0,0,0)
insert into teams values('fck','FC København',0,0,0,0)
insert into teams values('rfc','Randers FC',0,0,0,0)
insert into teams values('hbk','HB Køge',0,0,0,0)
insert into teams values('søn','SønderjyskE',0,0,0,0)
insert into teams values('ob','OB',0,0,0,0)
insert into teams values('fcm','FC Midtjylland',0,0,0,0)
insert into teams values('efb','Esbjerg fB',0,0,0,0)
insert into teams values('bif','Brøndby IF',0,0,0,0)
insert into teams values('fcn','FC Nordsjælland',0,0,0,0)
insert into teams values('aab','AaB',0,0,0,0)
insert into matches values('fcn','fck',2,0,'2009-07-18')
insert into matches values('fcm','efb',0,0,'2009-07-18')
insert into matches values('søn','rfc',1,0,'2009-07-19')
insert into matches values('hbk','sif',1,1,'2009-07-19')
insert into matches values('bif','ob',2,2,'2009-07-19')
insert into matches values('agf','aab',1,0,'2009-07-20')
--
insert into matches values('fck','hbk',7,1,'2009-07-25')
insert into matches values('ob','søn',3,1,'2009-07-25')
insert into matches values('sif','fcm',4,0,'2009-07-26')
insert into matches values('efb','bif',2,1,'2009-07-26')
insert into matches values('rfc','agf',2,3,'2009-07-27')
--
insert into matches values('fck','sif',1,1,'2009-08-01')
insert into matches values('søn','efb',1,1,'2009-08-01')
insert into matches values('hbk','aab',0,5,'2009-08-02')
insert into matches values('fcn','rfc',2,2,'2009-08-02')
insert into matches values('bif','fcm',3,1,'2009-08-02')
insert into matches values('agf','ob',2,2,'2009-08-03')
--
insert into matches values('fcn','sif',3,0,'2009-08-08')
insert into matches values('fcm','rfc',4,1,'2009-08-09')
insert into matches values('agf','hbk',2,1,'2009-08-09')
insert into matches values('efb','ob',1,2,'2009-08-09')
insert into matches values('søn','fck',0,1,'2009-08-09')
insert into matches values('bif','aab',0,2,'2009-08-09')
--
insert into matches values('fck','agf',0,1,'2009-08-15')
insert into matches values('aab','søn',1,0,'2009-08-15')
insert into matches values('sif','efb',2,3,'2009-08-16')
insert into matches values('rfc','bif',1,3,'2009-08-16')
insert into matches values('hbk','fcn',1,1,'2009-08-16')
insert into matches values('ob','fcm',1,0,'2009-08-17')
--
insert into matches values('aab','fcn',1,0,'2009-08-19')
--
insert into matches values('fcm','fck',1,4,'2009-08-22')
insert into matches values('agf','sif',2,2,'2009-08-22')
insert into matches values('efb','aab',2,0,'2009-08-23')
insert into matches values('søn','fcn',1,0,'2009-08-23')
insert into matches values('bif','hbk',6,1,'2009-08-23')
insert into matches values('ob','rfc',1,0,'2009-08-24')
--
insert into matches values('fcn','agf',0,2,'2009-08-29')
insert into matches values('rfc','efb',0,1,'2009-08-30')
insert into matches values('hbk','søn',1,0,'2009-08-30')
insert into matches values('aab','fcm',1,0,'2009-08-30')
insert into matches values('fck','bif',1,1,'2009-08-30')
insert into matches values('sif','ob',3,1,'2009-08-31')
--
insert into matches values('ob','fck',1,1,'2009-09-12')
insert into matches values('fcm','fcn',0,2,'2009-09-12')
insert into matches values('efb','hbk',3,2,'2009-09-13')
insert into matches values('sif','søn',1,1,'2009-09-13')
insert into matches values('bif','agf',1,0,'2009-09-13')
insert into matches values('rfc','aab',0,3,'2009-09-14')
--
insert into matches values('aab','sif',0,1,'2009-09-19')
insert into matches values('hbk','ob',1,3,'2009-09-20')
insert into matches values('søn','bif',2,4,'2009-09-20')
insert into matches values('fcn','efb',0,4,'2009-09-20')
insert into matches values('fck','rfc',3,0,'2009-09-20')
insert into matches values('agf','fcm',2,4,'2009-09-14')
--
insert into matches values('ob','fcn',2,0,'2009-09-26')
insert into matches values('fcm','søn',0,2,'2009-09-27')
insert into matches values('rfc','hbk',1,1,'2009-09-27')
insert into matches values('sif','bif',4,1,'2009-09-27')
insert into matches values('aab','fck',1,2,'2009-09-27')
insert into matches values('efb','agf',3,2,'2009-09-28')
--
insert into matches values('agf','søn',2,1,'2009-10-03')
insert into matches values('rfc','sif',1,2,'2009-10-04')
insert into matches values('fck','efb',2,1,'2009-10-04')
insert into matches values('fcm','hbk',2,1,'2009-10-04')
insert into matches values('bif','fcn',6,3,'2009-10-04')
insert into matches values('ob','aab',2,1,'2009-10-05')
--
insert into matches values('fcn','ob',0,2,'2009-10-17')
insert into matches values('aab','rfc',1,1,'2009-10-18')
insert into matches values('hbk','fck',0,2,'2009-10-18')
insert into matches values('sif','efb',2,2,'2009-10-18')
insert into matches values('bif','fcm',1,1,'2009-10-18')
insert into matches values('søn','agf',1,0,'2009-10-19')
--
insert into matches values('rfc','ob',1,1,'2009-10-24')
insert into matches values('efb','søn',2,0,'2009-10-25')
insert into matches values('fcm','hbk',2,1,'2009-10-25')
insert into matches values('fck','sif',1,0,'2009-10-25')
insert into matches values('aab','bif',1,2,'2009-10-25')
insert into matches values('agf','fcn',0,2,'2009-10-26')
--
insert into matches values('agf','fcm',2,2,'2009-10-31')
insert into matches values('rfc','sif',0,2,'2009-11-01')
insert into matches values('fcn','bif',0,1,'2009-11-01')
insert into matches values('søn','hbk',0,0,'2009-11-01')
insert into matches values('efb','fck',0,0,'2009-11-01')
insert into matches values('ob','aab',1,1,'2009-11-02')
--
insert into matches values('hbk','agf',1,1,'2009-11-07')
insert into matches values('sif','ob',0,1,'2009-11-07')
insert into matches values('fcm','rfc',2,1,'2009-11-08')
insert into matches values('søn','fcn',0,1,'2009-11-08')
insert into matches values('bif','efb',2,4,'2009-11-08')
insert into matches values('aab','fck',1,0,'2009-11-08')
--
insert into matches values('hbk','fcn',1,2,'2009-11-21')
insert into matches values('fcm','efb',3,0,'2009-11-21')
insert into matches values('sif','søn',1,2,'2009-11-22')
insert into matches values('fck','rfc',2,0,'2009-11-22')
insert into matches values('bif','ob',1,3,'2009-11-22')
insert into matches values('aab','agf',0,0,'2009-11-23')
--
insert into matches values('efb','aab',1,1,'2009-11-28')
insert into matches values('rfc','søn',0,0,'2009-11-29')
insert into matches values('fcn','sif',0,1,'2009-11-29')
insert into matches values('fck','fcm',2,0,'2009-11-29')
insert into matches values('agf','bif',1,0,'2009-11-29')
insert into matches values('ob','hbk',1,0,'2009-11-30')
--
insert into matches values('søn','aab',2,0,'2009-12-05')
insert into matches values('fcn','fcm',3,0,'2009-12-06')
insert into matches values('sif','bif',3,0,'2009-12-06')
insert into matches values('rfc','hbk',2,1,'2009-12-06')
insert into matches values('ob','fck',0,2,'2009-12-06')
insert into matches values('agf','efb',1,1,'2009-12-07')
--
insert into matches values('fcm','ob',2,2,'2010-03-06')
insert into matches values('efb','rfc',0,0,'2010-03-07')
insert into matches values('hbk','sif',1,4,'2010-03-07')
insert into matches values('bif','søn',1,1,'2010-03-07')
insert into matches values('fck','agf',5,0,'2010-03-07')
insert into matches values('aab','fcn',2,1,'2010-03-08')
--
insert into matches values('ob','søn',1,1,'2010-03-13')
insert into matches values('efb','hbk',2,1,'2010-03-14')
insert into matches values('rfc','fcn',0,0,'2010-03-14')
insert into matches values('fcm','aab',2,0,'2010-03-14')
insert into matches values('fck','bif',2,0,'2010-03-14')
insert into matches values('agf','sif',1,2,'2010-03-15')
--
insert into matches values('sif','fcm',0,2,'2010-03-20')
insert into matches values('hbk','aab',0,3,'2010-03-21')
insert into matches values('fcn','efb',1,0,'2010-03-21')
insert into matches values('ob','agf',2,0,'2010-03-21')
insert into matches values('søn','fck',0,2,'2010-03-21')
insert into matches values('bif','rfc',1,1,'2010-03-22')
--
insert into matches values('fck','fcn',0,2,'2010-03-24')
insert into matches values('aab','sif',1,0,'2010-03-24')
insert into matches values('bif','hbk',1,3,'2010-03-25')
insert into matches values('efb','ob',1,2,'2010-03-25')
insert into matches values('fcm','søn',0,0,'2010-03-25')
insert into matches values('rfc','agf',2,1,'2010-03-25')
--
insert into matches values('fcn','aab',1,1,'2010-03-27')
insert into matches values('agf','fck',0,0,'2010-03-28')
insert into matches values('rfc','efb',4,0,'2010-03-28')
insert into matches values('sif','hbk',3,0,'2010-03-28')
insert into matches values('søn','bif',1,3,'2010-03-28')
insert into matches values('ob','fcm',1,2,'2010-03-29')
--
insert into matches values('efb','agf',0,4,'2010-03-31')
insert into matches values('bif','sif',2,2,'2010-04-01')
insert into matches values('fck','ob',2,0,'2010-04-01')
insert into matches values('fcm','fcn',1,0,'2010-04-01')
insert into matches values('hbk','rfc',1,2,'2010-04-01')
insert into matches values('aab','søn',1,1,'2010-04-02')
--
insert into matches values('agf','rfc',0,0,'2010-04-04')
insert into matches values('hbk','bif',1,2,'2010-04-04')
insert into matches values('fcn','fck',0,3,'2010-04-05')
insert into matches values('ob','efb',0,0,'2010-04-05')
insert into matches values('sif','aab',1,1,'2010-04-05')
insert into matches values('søn','fcm',0,2,'2010-04-05')
--
go
drop trigger trig1
go
create trigger trig1
on matches
for insert,update
as
declare @homeid char(3)
declare @outid char(3)
declare @homegoal int
declare @outgoal int
declare @homepoint int
declare @outpoint int
select @homeid=homeid,@outid=outid,@homegoal=homegoal,@outgoal=outgoal
from inserted
if @homegoal > @outgoal
begin
set @homepoint = 3
set @outpoint = 0
end else
if @homegoal < @outgoal
begin
set @homepoint = 0
set @outpoint = 3
end
else
begin
set @homepoint = 1
set @outpoint = 1
end
update teams set nomatches = nomatches + 1,owngoals = owngoals + @homegoal,
othergoals = othergoals + @outgoal, points = points + @homepoint
where id = @homeid
update teams set nomatches = nomatches + 1,owngoals = owngoals + @outgoal,
othergoals = othergoals + @homegoal, points = points + @outpoint
where id = @outid
go
create trigger trig2
on matches
for delete,update
as
declare @homeid char(3)
declare @outid char(3)
declare @homegoal int
declare @outgoal int
declare @homepoint int
declare @outpoint int
select @homeid=homeid,@outid=outid,@homegoal=homegoal,@outgoal=outgoal
from deleted
if @homegoal > @outgoal
begin
set @homepoint = 3
set @outpoint = 0
end else
if @homegoal < @outgoal
begin
set @homepoint = 0
set @outpoint = 3
end
else
begin
set @homepoint = 1
set @outpoint = 1
end
update teams set nomatches = nomatches - 1,owngoals = owngoals - @homegoal,
othergoals = othergoals - @outgoal, points = points - @homepoint
where id = @homeid
update teams set nomatches = nomatches - 1,owngoals = owngoals - @outgoal,
othergoals = othergoals - @homegoal, points = points - @outpoint
where id = @outid
go
--
-- shows the scoretable
select * from teams order by points desc,owngoals-othergoals desc,owngoals desc
create proc calcscoretable
@until datetime
as
declare @t table
(
id char(3),
name varchar(40),
nomatches int,
owngoals int,
othergoals int,
points int
)
insert into @t select ID,name,0,0,0,0 from teams
declare @homepoint int,@outpoint int
declare p cursor
for select homeid,outid,homegoal,outgoal from matches where matchdate <= @until
declare @homeid char(3),@outid char(3),@homegoal int,@outgoal int
open p
fetch p into @homeid,@outid,@homegoal,@outgoal
while @@fetch_status != -1
begin
if @homegoal > @outgoal
begin
set @homepoint = 3
set @outpoint = 0
end else
if @homegoal < @outgoal
begin
set @homepoint = 0
set @outpoint = 3
end
else
begin
set @homepoint = 1
set @outpoint = 1
end
update @t set nomatches = nomatches + 1,owngoals = owngoals + @homegoal,
othergoals = othergoals + @outgoal, points = points + @homepoint
where id = @homeid
update @t set nomatches = nomatches + 1,owngoals = owngoals + @outgoal,
othergoals = othergoals + @homegoal, points = points + @outpoint
where id = @outid
fetch p into @homeid,@outid,@homegoal,@outgoal
end
close p
deallocate p
select * from @t order by points desc,owngoals-othergoals desc,owngoals desc
go
exec calcscoretable '2009.09.01'
-
-- find the leading team every day
drop proc findleader
go
create proc findleader
as
declare @t table
(
id char(3),
name varchar(40),
nomatches int,
owngoals int,
othergoals int,
points int
)
insert into @t select ID,name,0,0,0,0 from teams
declare @homepoint int,@outpoint int
declare @oldmatchdate datetime
declare @name varchar(40)
declare p cursor
for select homeid,outid,homegoal,outgoal,matchdate from matches
declare @homeid char(3),@outid char(3),@homegoal int,@outgoal int,@matchdate datetime
open p
fetch p into @homeid,@outid,@homegoal,@outgoal,@matchdate
if @oldmatchdate is null
set @oldmatchdate=@matchdate
set nocount on
while @@fetch_status != -1
begin
if @oldmatchdate<@matchdate
begin
select top 1 @name=name from @t order by points desc,owngoals-othergoals desc,owngoals desc
print 'On date ' + cast (@oldmatchdate as varchar) + ' the leader is ' + @name
set @oldmatchdate=@matchdate
end
if @homegoal > @outgoal
begin
set @homepoint = 3
set @outpoint = 0
end else
if @homegoal < @outgoal
begin
set @homepoint = 0
set @outpoint = 3
end
else
begin
set @homepoint = 1
set @outpoint = 1
end
update @t set nomatches = nomatches + 1,owngoals = owngoals + @homegoal,
othergoals = othergoals + @outgoal, points = points + @homepoint
where id = @homeid
update @t set nomatches = nomatches + 1,owngoals = owngoals + @outgoal,
othergoals = othergoals + @homegoal, points = points + @outpoint
where id = @outid
fetch p into @homeid,@outid,@homegoal,@outgoal,@matchdate
end
close p
deallocate p
select top 1 @name=name from @t order by points desc,owngoals-othergoals desc,owngoals desc
print 'On date ' + cast (@matchdate as varchar) + ' the leader is ' + @name
go
exec findleader
--exec SearchAllTables '074'
USE DBName
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%ItemID%'
ORDER BY schema_name, table_name;
SELECT sc.name +'.'+ ta.name TableName,
SUM(pa.rows) RowCnt, c.name TableColumn
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
INNER JOIN sys.columns c ON ta.OBJECT_ID = c.OBJECT_ID
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) and c.name LIKE '%INVOICELINE%' --CARRIERNUMID
GROUP BY sc.name,ta.name, c.name
ORDER BY SUM(pa.rows) DESC
USE [DBName]
GO
/****** Object: StoredProcedure [dbo].[SearchAllTables] Script Date: 17-09-2013 17:05:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SearchAllTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal', 'numeric(28,12)', 'uniqueidentifier')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results
END
--This function is useful when you need to make procedure to select more then 1 ID
--in a table. By using tsql "in" function you would be able to dynamically insert
--the amouth of id's. Example:
--WHERE INVER_ID in (SELECT items from dbo.Split(@INVER_ID,',')) and reYear=@reYear and
--reMonth=@reMonth
--where you get @INVER_ID nvarchar(1000) as a parameter of the procedure.
CREATE function [dbo].[Split]
(
@String nvarchar(4000),
@Delimiter char(1)
) Returns @Results Table (Items nvarchar(4000))
As
Begin
Declare @Index int
Declare @Slice nvarchar(4000)
Select @Index = 1
If @String Is NULL Return
While @Index != 0
Begin
Select @Index = CharIndex(@Delimiter, @String)
If (@Index != 0)
Select @Slice = left(@String, @Index - 1)
else
Select @Slice = @String
Insert into @Results(Items) Values (@Slice)
Select @String = right(@String, Len(@String) - @Index)
If Len(@String) = 0 break
End
Return
End
--the select statement:
SELECT items from dbo.Split('1,22,333,4444,57654',',')
Eksample you can dynamically add amouth of id's in ID parameter:
create proc UpdateInVoiceLine @INVER_ID char(1000), @reMonth int, reYear int
as
begin
INSERT INTO tblInvoiceline(reRecordID_ref, ilYear, ilMonth, itPROCESSINGLAB, ilRealsamplecollectiontime)values(
SELECT [reRecodrID], [reYear], [reMonth], [INVESTIGATIONPROCESSINGLAB_ID]
, [REALSAMPLECOLLECTIONTIME]
FROM [Faktureringssystem].[dbo].[tblMVIEW_RESULT_EXTRACTION]
WHERE INVER_ID in (SELECT items from dbo.Split(@INVER_ID,',')) and reYear=@reYear and reMonth=@reMonth
end
exec UpdateInVoiceLine '39,74,4000079', 1, 2011
Select AdressCommaDelimitedList
=
Stuff((SELECT ', ' + dmUnit, dmInstitution, dmFreeText, dmStreet, dmHouseNo, dmFloor, dmPostbox,
dmCity, dmZipcode, dmPostdistrict, dmCountry AS [text()]
FROM
(SELECT dmUnit, dmInstitution, dmFreeText, dmStreet, dmHouseNo, dmFloor, dmPostbox,
dmCity, dmZipcode, dmPostdistrict, dmCountry FROM [Faktureringssystem].[dbo].[tblDEPARTMENT]
where DEPARTMENT_ID = 56070)
x For XML PATH ('')),1,1,'')
select ',' + isnull(dmUnit,';'), ',' + isnull(dmInstitution,';'), ',' + isnull(dmFreeText, ';'), ',' + isnull(dmStreet,';'), ',' + isnull(dmHouseNo,';'), ',' + isnull(dmFloor,';'), ',' + isnull(dmPostbox,';'),
',' + isnull(dmCity,';'), ',' + isnull(dmZipcode,';'), ',' + isnull(dmPostdistrict,';'), ',' + isnull(dmCountry,';') from [Faktureringssystem].[dbo].[tblDEPARTMENT]
where DEPARTMENT_ID = 56070
declare @tempvar nvarchar(max) = ''
select @tempvar = @tempvar + ',' + isnull(dmUnit,';'), @tempvar = @tempvar + ',' + isnull(dmInstitution,';'), @tempvar = @tempvar + ',' + isnull(dmFreeText, ';'), @tempvar = @tempvar + ',' + isnull(dmStreet,';'), @tempvar = @tempvar + ',' + isnull(dmHouseNo,';'), @tempvar = @tempvar + ',' + isnull(dmFloor,';'), @tempvar = @tempvar + ',' + isnull(dmPostbox,';'),
@tempvar = @tempvar + ',' + isnull(dmCity,';'), @tempvar = @tempvar + ',' + isnull(dmZipcode,';'), @tempvar = @tempvar + ',' + isnull(dmPostdistrict,';'), @tempvar = @tempvar + ',' + isnull(dmCountry,';') from [Faktureringssystem].[dbo].[tblDEPARTMENT]
where DEPARTMENT_ID = 56070
select @tempvar
-- Creating a cursor
declare p cursor
for SELECT m.idMeter FROM Meter m
LEFT JOIN Reading r
ON m.idMeter = r.idMeter
WHERE m.idMeter NOT IN (
SELECT idMeter FROM Reading WHERE DATEPART(YEAR, [datetime]) = @year
)
GROUP BY m.idMeter
declare @idMeter int
open p
fetch p into @idMeter
-- Looping through all meters with missing readings for the given year
while @@fetch_status != -1
begin
-- Declaring variables
declare @datetime1 datetime
declare @datetime2 datetime
declare @kwh1 float
declare @kwh2 float
declare @avgKwh float
declare @numOfDays1 float
declare @numOfDays2 float
declare @newKwh float
declare @maxDiff float
declare @maxLimit float
SET @kwh1 = 0;
SET @datetime1 = NULL
-- Selecting maxDiif and maxLimit
SELECT @maxDiff = maxDiff, @maxLimit = maxLimit FROM Limit
-- Selecting the last two readings
SELECT TOP 1 @datetime1 = [datetime], @kwh1 = kwh FROM Reading WHERE idMeter = @idMeter ORDER BY [datetime] DESC
SELECT TOP 2 @datetime2 = [datetime], @kwh2 = kwh FROM Reading WHERE idMeter = @idMeter ORDER BY [datetime] DESC
-- If we have two previous readings
IF(@datetime1 IS NOT NULL AND @datetime2 IS NOT NULL AND @datetime1 != @datetime2)
BEGIN
SET @numOfDays1 = DATEDIFF(DAY, @datetime2, @datetime1)
SET @avgKwh = (@kwh1-@kwh2) / @numOfDays1
SET @numOfDays2 = DATEDIFF(DAY, @datetime1, GETDATE())
SET @newKwh = (@avgKwh * @numOfDays2 * 1.15) + @kwh1
END
-- If we don't have two previous readings
ELSE
BEGIN
SET @newKwh = @maxLimit * 365
IF(@kwh1 != 0)
BEGIN
SET @newKwh = @newKwh + @kwh1
END
END
-- Inserting the calculated reading
INSERT INTO Reading(idMeter, kwh, [datetime], idReadingType) VALUES(@idMeter, @newKwh, GETDATE(), 1)
fetch p into @idMeter
end
close p
deallocate p
use bigdb
create table big
(
id int,
name char(30),
filler char(200)
)
-- insert BATCH inserts 40000 records
declare @name char(30)
declare @i int
select @i = 10000
while @i < 50000
begin
select @name = 'Andersen' + convert(char(6),@i)
insert into big values(@i,@name,'hey')
select @i = @i + 1
end
go
select top 20 * from big
select COUNT(*) from big
-- finds one record
-- set statistics time on
declare @t1 datetime, @t2 datetime
set @t1 = getdate()
select * from big where name = 'Andersen20113'
set @t2 = getdate()
select DATEDIFF(ms,@t1,@t2)
go
-- makes a non-clustered index
declare @t1 datetime, @t2 datetime
set @t1 = getdate()
create index bigindex on big(name)
set @t2 = getdate()
select DATEDIFF(ms,@t1,@t2)
go
-- makes a clustered index
declare @t1 datetime, @t2 datetime
set @t1 = getdate()
create clustered index bigindex2 on big(name)
set @t2 = getdate()
select DATEDIFF(ms,@t1,@t2)
go
-- Clean the buffer
checkpoint
go
dbcc dropcleanbuffers
go
-- dropd an index
drop index bigindex on big
-- *********************************
-- Here the part for lesson 5 starts
-- *********************************
--create clustered with fill-factor
create clustered index bigindex on big(name) with fillfactor = 60,PAD_index
go
select * from big where id = 20113
go
alter index bigindex
on big
rebuild
alter index bigindex
on big
reorganize
-- change the default fill factor
go
sp_configure
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor', 100;
GO
RECONFIGURE;
GO
-- the next lines is for illustrating how SQL-server makes
-- counting and existing
select * from big where name = 'Andersen10000'
select COUNT(*) from big where name = 'Andersen10000'
If exists (select * from big where name = 'Andersen10000')
print 'yes'
go
select * from big where id = 10000
select COUNT(*) from big where id = 10000
If exists (select * from big where id = 10000)
print 'yes'
go
create index bigindex2 on big(id)
go
select * from big where id = 10000
select COUNT(*) from big where id = 10000
If exists (select * from big where id = 10000)
print 'yes'
--
use bigdb
--
set statistics io on
--
-- The next lines shows three different ways to check
-- if a record with a particular id is in the table
-- ******
-- without any index
select * from big where id = 10000
select COUNT(*) from big where id = 10000
If exists (select * from big where id = 10000)
print 'yes'
-- without any index another id
select * from big where id = 20000
select COUNT(*) from big where id = 20000
If exists (select * from big where id = 20000)
print 'yes'
-- try the exist with clean buffers
checkpoint
go
dbcc dropcleanbuffers
go
If exists (select * from big where id = 10000)
print 'yes'
--
create clustered index bigindex on big(name)
-- with a clutered index
select * from big where name = 'Andersen10000'
select COUNT(*) from big where name = 'Andersen10000'
If exists (select * from big where name = 'Andersen10000')
print 'yes'
go
-- when there is a clustered index on aother attribute
select * from big where id = 10000
select COUNT(*) from big where id = 10000
If exists (select * from big where id = 10000)
print 'yes'
go
create index bigindex2 on big(id)
go
-- with a nonclustered index
select * from big where id = 10000
select COUNT(*) from big where id = 10000
If exists (select * from big where id = 10000)
print 'yes'
--
-- examples about system tables.
select * from sysobjects order by name
--
select * from sysobjects where name like 'sys%'
--
select * from sysobjects where name not like 'sys%'
--
select * from sysobjects where category = 0
select * from syscolumns
select obj.name,col.name
from syscolumns col,sysobjects obj
where obj.category = 0 and col.id=obj.id and
obj.type='u'
-- updating the servers statistics
update statistics person
-- shows when updata statistics was made last time
declare @tabid int
select @tabid = object_id('person')
select STATS_DATE(@tabid,1)
go
-- example on use of systemtables
-- a stored procedure for updating statistics for all
-- tables in the database
drop proc Updateallindexees
go
create proc Updateallindexees
as
declare p cursor
for select name from sysobjects where category = 0 and type = 'u'
declare @name sysname
open p
fetch p into @name
while @@fetch_status != -1
begin
execute ('update statistics ' + @name)
fetch p into @name
end
close p
deallocate p
go
-- checks last updated for company-table
declare @tabid int
select @tabid = object_id('company')
select STATS_DATE(@tabid,1)
go
exec Updateallindexees
-- ********* optimizer examples
select distinct name
from person pe,zipcode zi
where pe.zip=zi.zip and
zi.postaldistrict = 'Århus C'
go
select name,companyname
from person pe,workplace w,company co
where pe.cpr=w.cpr and
w.companynr = co.companynr
--
select name,companyname
from person pe,workplace w,company co
where pe.cpr=w.cpr and
w.companynr = co.companynr and
pe.job = 'programmer'
--
create clustered index index1 on person(cpr)
--
create clustered index index2 on workplace(cpr,companynr)
create clustered index index3 on company(companynr)
--
select companynr,AVG(salary)
from person p join workplace w on p.cpr=w.cpr
group by companynr
set transaction isolation level read uncommitted
set transaction isolation level read committed
set transaction isolation level repeatable read
set transaction isolation level serializable
-- dirty read
--1
begin tran
insert into person values('19','hans kurt','systemudvikler',400000,'8210')
--3
rollback tran
-- unrepeatable read (lost update)
-- 2
update person set salary = salary*1.02 where cpr = '1212121212'
-- phantom
-- 2
insert into person values('21','hans kurt','systemdeveloper',4000000,'8210')
-- snapshot example
-- without read transaction
--1
select salary from person where cpr='1212121212'
select salary from person where cpr='1313131313'
--3
select salary from person where cpr='1414141414'
select salary from person where cpr='1515151515'
-- with read transaction
begin tran
select salary from person where cpr='1212121212'
select salary from person where cpr='1313131313'
--3
select salary from person where cpr='1414141414'
select salary from person where cpr='1515151515'
commit tran
-- with snapshot isolation
set transaction isolation level snapshot
begin tran
select salary from person where cpr='1212121212'
select salary from person where cpr='1313131313'
--3
select salary from person where cpr='1414141414'
select salary from person where cpr='1515151515'
commit tran
use eksdb
-- the traditonal ANSI isolation levels
set transaction isolation level read uncommitted
set transaction isolation level read committed
set transaction isolation level repeatable read
set transaction isolation level serializable
-- the new isolation level in SQL Server 2005
set transaction isolation level snapshot
-- with SQL Server 2005 you are able to change the way
-- the isolation level read commtted works
-- if you run the following alter command
alter database eksdb set read_committed_snapshot on
-- this will make the database use row versions instead of locks
-- dirty read
-- 2
select * from person
-- unrepeatable read (lost update i dette tilfælde)
-- 1
begin tran
select salary from person where cpr = '1212121212'
-- wants to give him a raise in salary on 10000
-- 3
update person set salary = 280000 where cpr = '1212121212'
commit tran
select * from person
-- phantom
--1
begin tran
select job,sum(salary) from person group by job
-- 3
select zip,sum(salary) from person group by zip
commit tran
-- example for snapshot isolation
-- good for read-write conflicts
-- (conflicts between a transaction, which only reads
-- and a transaction, which makes insert, update or delete)
set transaction isolation level repeatable read
--2
begin tran
update person set salary = salary - 50000
where cpr = '1212121212'
update person set salary = salary + 50000
where cpr = '1414141414'
commit tran
alter database eksdb set allow_snapshot_isolation on
set transaction isolation level snapshot
--Create mail reminder in mssql:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
--Then create the stored proc
drop proc [dbo].[sp_send_cdosysmail]
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@bodytype varchar(10),@output_desc varchar(1000) output,@output_mesg varchar(10) output
AS
DECLARE @imsg int DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)
EXEC @hr = sp_oacreate 'cdo.message', @imsg out
--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
--SMTP Server
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',
'smtp.live.com'
--UserName
exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',
'xxx@hotmail.com'
--Password
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',
'password'
--UseSSL True/False
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',
'False'
--PORT
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',
'587'
--Requires Aunthentication None(0) / Basic(1)
EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',
'1'
EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null
EXEC @hr = sp_oasetproperty @imsg, 'to', @to
EXEC @hr = sp_oasetproperty @imsg, 'from', @from
EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject
-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.
EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body
EXEC @hr = sp_oamethod @imsg, 'send', null
SET @output_mesg = 'Success'
-- sample error handling.
IF @hr <>0 SELECT @hr
BEGIN
EXEC @hr = sp_oageterrorinfo null, @source out, @description out
IF @hr = 0
BEGIN
--set @output_desc = ' source: ' @source
set @output_desc = @description
END
ELSE
BEGIN
SET @output_desc = ' sp_oageterrorinfo failed'
END
IF not @output_desc is NULL
SET @output_mesg = 'Error'
END
EXEC @hr = sp_oadestroy @imsg
--To execute the stored procedure do the following
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10)
EXEC sp_send_cdosysmail 'xxx@hotmail.com','xxx@gmail.com','test subject','test from mssql','textbody', @output_desc = @out_desc output, @output_mesg = @out_mesg output
PRINT @out_mesg
drop proc checkdays
create proc checkdays
as
--declare @tempdesc nvarchar(max)
--declare @tempmedarbejder nvarchar(max)
--declare @EventDate date
declare @mail nvarchar(128)
begin
--select @tempdesc = EventDesc, @tempmedarbejder = Medarbejder, @EventDate = EventDate from kalender
--where convert(date, EventDate) = convert(datetime,(CONVERT(char(10), GETDATE(), 103)),103)
declare p cursor
for select EventDesc, Medarbejder, EventDate from kalender where
convert(date, EventDate) = convert(datetime,(CONVERT(char(10), GETDATE(), 103)),103)
declare @event varchar(max),@name nvarchar(128), @date date
open p
fetch p into @event,@name, @date
while @@fetch_status != -1
begin
print @event
print @name
print @date
select @mail = E_MAIL from Log_Users where Username = @name
DECLARE @out_desc varchar(1000),
@out_mesg varchar(10), @subject nvarchar(max)
set @subject = ('subject reminder: ' @name cast(@date as nvarchar))
EXEC sp_send_cdosysmail 'xxx@hotmail.com',@mail,@subject,@event,
'textbody', @output_desc = @out_desc output, @output_mesg = @out_mesg output
fetch p into @event,@name, @date
end
close p
deallocate p
end
go
--create scheduled job
--http://msdn.microsoft.com/en-us/library/ms190268.aspx
--http://technet.microsoft.com/en-us/library/ms190268.aspx
--http://social.msdn.microsoft.com/Forums/fi-FI/sqldatabaseengine/thread/75272da8-df78-4c1f-ae44-99b78d4c04ea
--http://www.quackit.com/sql_server/tutorial/sql_server_agent_jobs.cfm
-- **********************************
-- OPTIMIZER HINTS
-- **********************************
-- Table hints
-- forcing the use of an index (called index2)
select name,companyname
from person pe,workplace w with (index=index2),company co
where pe.cpr=w.cpr and
w.companynr = co.companynr
-- forcing it not to use the index
select name,companyname
from person pe,workplace w with (index = 0) ,company co
where pe.cpr=w.cpr and
w.companynr = co.companynr
-- forcing it to use join hints
select name,companyname
from person pe inner loop join workplace w on pe.cpr=w.cpr
inner hash join company co on w.companynr = co.companynr
-- query hint OPTION field
select name,companyname
from person pe,workplace w,company co
where pe.cpr=w.cpr and
w.companynr = co.companynr OPTION (merge join,force order)
-- The next lines shows three different ways to check
-- if a record with a particular id is in the table
-- ******
-- without any index
select * from big where id = 10000
select COUNT(*) from big where id = 10000
If exists (select * from big where id = 10000)
print 'yes'
-- without any index another id
select * from big where id = 20000
select COUNT(*) from big where id = 20000
If exists (select * from big where id = 20000)
print 'yes'
-- try the exist with clean buffers
checkpoint
go
dbcc dropcleanbuffers
go
If exists (select * from big where id = 10000)
print 'yes'
--
create clustered index bigindex on big(name)
-- with a clutered index
select * from big where name = 'Andersen10000'
select COUNT(*) from big where name = 'Andersen10000'
If exists (select * from big where name = 'Andersen10000')
print 'yes'
go
-- when there is a clustered index on aother attribute
select * from big where id = 10000
select COUNT(*) from big where id = 10000
If exists (select * from big where id = 10000)
print 'yes'
go
create index bigindex2 on big(id)
go
-- with a nonclustered index
select * from big where id = 10000
select COUNT(*) from big where id = 10000
If exists (select * from big where id = 10000)
print 'yes'
--
-- examples about system tables.
select * from sysobjects order by name
--
select * from sysobjects where name like 'sys%'
--
select * from sysobjects where name not like 'sys%'
--
select * from sysobjects where category = 0
select * from syscolumns
select obj.name,col.name
from syscolumns col,sysobjects obj
where obj.category = 0 and col.id=obj.id and
obj.type='u'
-- updating the servers statistics
update statistics person
-- shows when updata statistics was made last time
declare @tabid int
select @tabid = object_id('person')
select STATS_DATE(@tabid,1)
go
-- example on use of systemtables
-- a stored procedure for updating statistics for all
-- tables in the database
drop proc Updateallindexees
go
create proc Updateallindexees
as
declare p cursor
for select name from sysobjects where category = 0 and type = 'u'
declare @name sysname
open p
fetch p into @name
while @@fetch_status != -1
begin
execute ('update statistics ' + @name)
fetch p into @name
end
close p
deallocate p
go
-- checks last updated for company-table
declare @tabid int
select @tabid = object_id('company')
select STATS_DATE(@tabid,1)
go
exec Updateallindexees
-- ********* optimizer examples
select distinct name
from person pe,zipcode zi
where pe.zip=zi.zip and
zi.postaldistrict = '�rhus C'
go
select name,companyname
from person pe,workplace w,company co
where pe.cpr=w.cpr and
w.companynr = co.companynr
--
select name,companyname
from person pe,workplace w,company co
where pe.cpr=w.cpr and
w.companynr = co.companynr and
pe.job = 'programmer'
--
create clustered index index1 on person(cpr)
--
create clustered index index2 on workplace(cpr,companynr)
create clustered index index3 on company(companynr)
--
select companynr,AVG(salary)
from person p join workplace w on p.cpr=w.cpr
group by companynr
USE master
DECLARE @dbName varchar(50) = 'UdlejningDB'
DECLARE @spid int
select @spid = spid
FROM sys.sysprocesses
WHERE dbid in (DB_ID(@dbName))
WHILE @@ROWCOUNT <> 0
BEGIN
EXEC ('KILL ' + @spid)
SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE
dbid IN (DB_ID(@dbName)) AND
spid > @spid
END
/*Paging proc*/
USE [DB_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--DROP PROC [dbo].[proc_ProcName]
CREATE PROCEDURE [dbo].[proc_ProcName]
(
@PageNumber Int,
@PageSize Int,
@OrderColumn NVarChar(250) = NULL,
@OrderDirection NVarChar(4) = NULL,
@UserId Int = NULL,
@OrderId int = NULL
)
AS
DECLARE @SQL VarChar(8000)
DECLARE @RowStart Int
DECLARE @RowEnd Int
IF @PageNumber > 0
BEGIN
SET @PageNumber = @PageNumber - 1;
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1;
BEGIN
--Selection
SET @SQL = 'SET NOCOUNT ON;
SELECT dt.[RowNumber],
dt.[RecordCount],
[Id],
[OrdreID],
[UserID],
[AdminID],
[Name]
--=
--CASE WHEN
--dt.IsSystem = 1
-- THEN
-- ''''
-- ELSE
-- dt.Name
-- END
,
[Mail]
--=
--CASE WHEN
--dt.IsSystem = 1
--THEN
--''''
--ELSE
-- dt.Mail
-- END
,
[MailSubject],
[Body],
[DateTimeSent],
[DateTimeDeleted],
[IsVisible]
FROM
(
SELECT TOP ' + STR(@RowEnd) + '
m.[Id],
m.[OrderID],
m.[UserID],
m.[AdministratorID],
m.[Name],
m.[Mail],
m.[MailSubject],
m.[Body],
m.[DateTimeSent],
m.[DateTimeDeleted],
m.[IsVisible],
u.[IsSystem],
ROW_NUMBER() OVER (ORDER BY ' + @OrderColumn + ' ' + @OrderDirection + ') AS [RowNumber],
COUNT(Id) OVER (PARTITION BY '''') AS RecordCount
FROM
dbo.[OrderMail] m
INNER JOIN [User] AS u on m.UserID = u.UserID
WHERE 1 = 1 AND '
IF @UserId is not null
BEGIN
SET @SQL = @SQL + 'm.[UserID] = ' + STR(@UserId);
END
IF @UserId is null
BEGIN
SET @SQL = @SQL + 'm.[OrderID] = ' + STR(@OrderId);
END
SET @SQL = @SQL + ' ) AS dt
WHERE (RowNumber BETWEEN ' + STR(@RowStart) + ' AND ' + STR(@RowEnd) + ')
ORDER BY [RowNumber] ASC '
PRINT (@SQL)
EXEC (@SQL)
RETURN
END
END
/*end*/
-- First restart the server in Sql Server Configuration Manager
--then run:
EXEC sp_resetstatus 'CampingForum';
ALTER DATABASE CampingForum SET EMERGENCY
DBCC checkdb('CampingForum')
ALTER DATABASE CampingForum SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('CampingForum', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE CampingForum SET MULTI_USER
--refresh the db and youre done!!!
use master
exec sp_addtype cprtype,'char(10)'
go
create table testperson
(
cpr cprtype,
name varchar(25)
)
create rule cprrule
as isdate(substring(@x,5,2)+substring(@x,3,2)+substring(@x,1,2))=1
go
sp_bindrule cprrule,cprtype
--sp_bindrule cprrule,'testperson.cpr'
insert into testperson values('2212603611 ','OK') -- OK with date and modulus 11
insert into testperson values('1002893211','OK too') -- OK with date and modulus 11
insert into testperson values('1002895211','not OK') -- OK date wrong modulus 11
insert into testperson values('3002893217','wrong date') -- wrong date
go
sp_unbindrule cprtype
go
-- create new SQL Server logins
exec sp_addlogin 'peter','britney1'
exec sp_addlogin 'dan','britney2'
exec sp_addlogin 'susan','britney3'
-- exec sp_droplogin 'dan'
-- give the login access to a database (here the bigdb db)
use bigdb
exec sp_grantdbaccess 'peter'
exec sp_grantdbaccess 'dan','killerdan'
exec sp_grantdbaccess 'susan'
-- make new userdefined roles
exec sp_addrole 'developer'
exec sp_addrole 'normaluser'
-- exec sp_droprole 'developer'
-- add members to the roles
exec sp_addrolemember 'developer','peter'
exec sp_addrolemember 'developer','susan'
exec sp_addrolemember 'normaluser','killerdan'
--
-- add users or roles to fixed roles
exec sp_addrolemember 'db_ddladmin','developer'
exec sp_addrolemember 'db_datareader','peter'
-- statement permission
grant create view,create table to killerdan
grant create procedure to developer
-- grant all to [killerdan]
revoke create view from killerdan
-- objects permission
grant select,insert on teams to [frank nobody]
revoke insert on teams from [frank nobody]
deny delete on teams to [frank nobody]
--Trigger Syntax:
--CREATE TRIGGER trigger_name
--ON { table | view }
--[ WITH ENCRYPTION ]
--{
-- {
-- { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
-- [ WITH APPEND ]
-- [ NOT FOR REPLICATION ]
-- AS
-- [
-- {
-- IF UPDATE ( column )
-- [ { AND | OR } UPDATE ( column ) ... ]
-- |
drop trigger trigAddStudents
CREATE TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
DECLARE @Newname VARCHAR(100)
SELECT @Newname =(SELECT Name FROM INSERTED)
PRINT 'THE STUDENT ' + @Newname + ' IS ADDED.'
update Students set _action = 'IS ADDED' where name = @Newname;
go
INSERT INTO Students(id, name, grade, id_ref, classroom) VALUES (12,'George Mathew', 1, 25542, 1);
drop trigger trigDeleteStudents
CREATE TRIGGER trigDeleteStudents
ON Students
FOR DELETE
AS
DECLARE @Newname VARCHAR(max)= '';
SELECT @Newname = @Newname + ', ' + Name FROM DELETED ;
SET @Newname = STUFF(@NewName, 1, 2, '') ;
--SELECT @Newname = (SELECT top 1 Name FROM DELETED)
PRINT 'THE STUDENT ' + @Newname + ' IS DELETED.'
go
delete from Students where id = (select top 1 id from Students where id = 12 order by id desc)
drop trigger trigUpdateStudents
CREATE TRIGGER trigUpdateStudents
ON Students
FOR UPDATE
AS
PRINT 'THE STUDENT ' + ' IS UPDATED.'
go
update Students set _action = 'IS UPDATED' where name = 'George Mathew';
drop table Students;
create table Students(
id bigint, --identity primary key clustered,
name nvarchar(256),
grade int,
id_ref int,
classroom int,
_action nvarchar(500) null
)
select * from Students
drop database mandatoryDB;
create database mandatoryDB;
use mandatoryDB;
set transaction isolation level read uncommitted
set transaction isolation level read committed
set transaction isolation level repeatable read
set transaction isolation level serializable
set transaction isolation level snapshot
alter database mandatoryDB set read_committed_snapshot on
drop table Readings
create table Readings(
--readingsId int identity primary key,
readingsId int primary key,
[date] datetime,
value int, --hvis jeg tilf�jer samlede v�rdi for kunde er det hurtigere at l�se dette vil g�re databasen meget stor og defor performance d�rligere
meterId int foreign key (meterId) references Meter(meterId)
)
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
declare @date date
declare @value int
declare @year int
declare @i int
select @i = 10000
select @value = 10
select @year = 2010
while @i < 20000
begin
select @date = DATEADD(YEAR, 1, convert(char, @year) + '-08-30')
select @value = @value
insert into Readings(readingsId, [date], value, meterId) values(@i, @date, @value, @i)
select @i = @i + 1
select @value = @value + 5200
select @year = @year + 1
end
go
drop proc [dbo].[getWarning]
GO
create procedure [dbo].[getWarning] @newvalue decimal(18,0) output
as
begin
declare @date date
set @date = GETDATE()
select @date as 'date'
declare @pk int
declare @es1 int
declare @es2 int
declare @estimatedValue decimal
select top 1 @es1 = r.value, @pk = readingsid FROM Readings r order by r.date desc
select top 2 @es2 = r.value FROM Readings r order by r.date desc
select @pk as 'pkbeforeadd'
set @pk = @pk + 10
select @pk as 'pk'
declare @dateint int
declare @d1 date
declare @d2 date
select top 1 @d1 = r.date FROM Readings r order by r.date desc
select top 2 @d2 = r.date FROM Readings r order by r.date desc
set @dateint = datediff(year,@d1,@d2)
set @estimatedValue = @es2-@es1
set @estimatedValue= @estimatedValue/@dateint
declare @checkvalue decimal
set @checkvalue = @estimatedValue
select @checkvalue as 'estimatedvalue'
DECLARE @returnValue bit
SET @returnValue = CAST(0 as bit)
IF (@newvalue >= @checkvalue * 1.15 or @newvalue <= @checkvalue * 0.85)
BEGIN
SET @returnValue = CAST(1 as bit)
END
SELECT @returnValue as 'Warning'
insert into Readings(readingsId, [date], value) values(@pk, @date, @newvalue)
end
exec [dbo].[getWarning] 41538610
go
select * from Readings where value = 41538610
select * from Customer c join Meter m on c.customerId = m.meterId join Readings r on r.readingsId = m.meterId order by c.customerId
select * from Customer c inner hash join Meter m on c.customerId = m.meterId inner hash join Readings r on r.readingsId = m.meterId order by c.customerId
select * from Customer c inner loop join Meter m on c.customerId = m.meterId inner loop join Readings r on r.readingsId = m.meterId order by c.customerId
-- ***************************************
-- Example with transactions and try catch
-- ***************************************
Create table Person
(
name varchar(30),
stilling varchar(30),
salary int
)
insert into Person values('Paul','systemudvikler',400000)
insert into Person values('Jan','programmør',340000)
-- transaction with runtime error - no automatic rollback
begin tran
insert into Person values('Pauline','systemudvikler',40000000000)
insert into Person values('Britney','programmør',340000)
commit tran
--
select * from Person
-- a correct transactional solution (but not the best)
begin tran
insert into Person values('Pauline','systemudvikler',40000000000)
if @@error = 0
begin
insert into Person values('Britney','programmør',340000)
if @@error = 0
commit tran
else
rollback tran
end
else
rollback tran
go
--
-- a solution a bit better but still not the best
begin tran
declare @error int
insert into Person values('Pauline','systemudvikler',40000000000)
set @error = @@error
if @error = 0
insert into Person values('Britney','programmør',340000)
set @error = @error + @@error
if @error = 0
-- new insert
set @error = @error + @@error
if @error = 0
commit tran
else
rollback tran
go
-- a solution using try catch
begin tran
Begin try
insert into Person values('Pauline','systemudvikler',40000000000)
insert into Person values('Britney','programmør',340000)
commit tran
print 'all well'
end try
begin catch
rollback tran
print 'error occured'
print error_message()
end catch
go
--
insert into Person values('Peter','systemudvikler',420000)
insert into Person values('Roy','programmør',540000)
select * from person
-- *******************
-- the new TOP command
-- *******************
-- example old top
select top 2 name,salary
from person
order by salary desc
-- now it can be used with percent
select top 25 percent name,salary
from person
order by salary desc
-- now it can be used with update and delete
update top (25) percent person set salary = salary*2
-- well it did not work, so we try something else
update Person set salary = salary * 2 where name in
(select top (25) percent name from Person order by salary desc)
-- but we did not use the top in the update any longer !!!!
-- ************************
-- OUTPUT command
-- ************************
delete from Person output deleted.* where salary < 500000
-- the delete har now returned a result-set as a select would have done.
-- you can also use it with a table variable
declare @resulttable table
(
name varchar(30),
salary int
)
delete from Person output deleted.name,deleted.salary into @resulttable
where salary < 700000
select top 1 * from @resulttable
--
select * from person
-- ******************************
-- COMMON TABLE EXPRESSIONS (CTE)
-- ******************************
-- here is a normal example of the use of CTE
go
WITH RichPeople(name)
AS (select top 50 percent name
from Person
order by salary desc)
select AVG(p.salary)
from Person p, RichPeople r
where p.name=r.name
-- this is the same as
select AVG(p.salary)
from Person p,(select top 50 percent name
from Person
order by salary desc) as r
where p.name=r.name
-- ************************
-- CTE and recursion
-- ************************
create table fatherson
(father varchar(20),
son varchar(20)
)
-- This means that BeBob is the father of Bob and Alan and so on
insert into fatherson values('BeBob','Bob')
insert into fatherson values('BeBob','Alan')
insert into fatherson values('BeBeBob','BeBob')
insert into fatherson values('BeBeBob','BeAlan')
insert into fatherson values('BeBeBeBob','BeBeBob')
insert into fatherson values('BeBeBeBeBob','BeBeBeBob')
--
-- I want all the descendants of BeBeBeBob'
-- This can de done with a big union
select son
from fatherson
where father = ('BeBeBeBob'');
union
select f2.son
from fatherson f1, fatherson f2
where f2.father = f1.son and f1.father = ('BeBeBeBob');
union
select f3.son
from fatherson f1, fatherson f2,fatherson f3
where f3.father= f2.son and f2.father = f1.son and f1.father = ('BeBeBeBob');
-- this is impossible if you do not know the how deep to go
go
-- this problem can be solved, if we use CTE to make recursion
with fathsonCTE(son)
as
(
select son
from fatherson
where father = ('BeBeBeBob');
union all
select f.son
from fatherson as f
join fathsonCTE as ff
on f. father = ff.son
)
select son from fathsonCTE
-- *******************
-- New 2008 stuff
-- *******************
declare @d date
declare @t time
set @d = GETDATE()
set @t = GETDATE()
select @d
select @t
--
declare @x int
declare @y int
set @x = 7
set @y = 9
set @x += @y
select @x,@y
/*--------------------------------3.1------------------------------*/
drop table customer
go
create table customer( id int identity(1, 1),
name varchar(30), depth int, guarantor datetime,
created datetime)
insert customer (name, depth, guarantor, created) values('Person1', 1, '1-1-2009', '2-1-2009')
insert customer (name, depth, guarantor, created) values('Person2', 2, '3-1-2009', '4-1-2009')
insert customer (name, depth, guarantor, created) values('Person3', 3, '5-1-2009', '6-1-2009')
insert customer (name, depth, guarantor, created) values('Person4', 4, '7-1-2009', '8-1-2009')
insert customer (name, depth, guarantor, created) values('Person5', 5, '9-1-2009', '10-1-2009')
insert customer (name, depth, guarantor, created) values('Person6', 6, '1-1-2009', '2-1-2009')
insert customer (name, depth, guarantor, created) values('Person7', 7, '3-1-2009', '4-1-2009')
insert customer (name, depth, guarantor, created) values('Person8', 8, '5-1-2009', '6-1-2009')
insert customer (name, depth, guarantor, created) values('Person9', 9, '7-1-2009', '8-1-2009')
insert customer (name, depth, guarantor, created) values('Person10', 10, '9-1-2009', '10-1-2009')
DELETE customer WHERE created IN( SELECT TOP 20 percent created FROM customer ORDER BY created desc)
GO
select * from customer
update customer set depth =+1 output inserted.* where depth > 5
set implicit_transactions off -- or on
-- if ON a transaction is started all by itself.
-- you have to end every transaction yourself
select * from person
select @@trancount
commit tran
-- a way to do transaction control
declare @startedtran bit
if @@TRANCOUNT = 0
begin
set @startedtran = 1
begin tran
end
-- do the body of the transaction
if @startedtran = 1 and @@TRANCOUNT > 0
commit tran
-- the use of nested transactions
begin tran
begin tran
begin tran
select @@TRANCOUNT
raiserror('hej',16,1)
select @@TRANCOUNT
commit tran
select @@TRANCOUNT
rollback tran
select @@TRANCOUNT
-- pseudocode for commit
-- if @@trancount > 1
-- @@trancount = @@trancount - 1
-- else
-- if @@trancount = 1
-- begin
-- commits all work done by the transaction
-- @@trancount = 0
-- end
-- else
-- ERROR
--
--
-- pseudocode for rollback (without any parameter)
-- rolls back all work done by the transaction
-- @@trancount = 0
-- pseudocode for rollback (with a savepont parameter)
-- rolls back all work backwards until the savepoint
-- leaves @@trancount unchanged
-- savepoint example
create table customer
(
id int primary key,
name varchar(50),
balance int
)
insert into customer values(1,'Dan',1000)
insert into customer values(2,'Paul',2000)
create table goods
(
goodsid int,
goodsname varchar(50),
price int
)
insert into goods values(1,'pommer frites',14)
insert into goods values(2,'hamburgers',25)
insert into goods values(3,'coca cola',18)
create table orders
(
id int identity(1,1),
selcustid int,
buycustid int,
goodsid int,
number int,
payed bit
)
drop proc makeorder
go
create proc makeorder
@selcust int,
@buycust int,
@goodsno int,
@number int
as
begin tran
declare @orderid int
insert into orders values(@selcust,@buycust,@goodsno,@number,null)
select @orderid = @@identity
save tran point1
declare @price int
set @price = (select @number*price from goods
where goodsid= @goodsno)
if (select balance from customer where id = @buycust) >= @price
begin try
update customer set balance = balance + @price where id = @selcust
update customer set balance = balance - @price where id = @buycust
update orders set payed = '1' where id=@orderid
commit tran
end try
begin catch
rollback tran point1
if @@trancount > 0 commit tran
end catch
else
begin
rollback tran point1
commit tran
end
go
select * from customer
select * from goods
select * from orders
exec makeorder 2,1,2,5
go
alter table customer with check
add constraint tkcont check (balance > 870)
drop table person
Create table Person
(
name varchar(30),
stilling varchar(30),
salary int
)
insert into Person values('Paul','systemudvikler',400000)
insert into Person values('Jan','programm�r',340000)
-- This is a BATCH - it fails totally because of misspelling
inserd into Person values('Pauline','systemudvikler',400000)
insert into Person values('Britney','programm�r',340000)
--
-- This is a BATCH - the first insert fails because the salary exceeds MAXINT
-- The second insert is done
insert into Person values('Pauline','systemudvikler',40000000000)
insert into Person values('Britney','programm�r',340000)
--
-- It is difficult to predict which errors are compile-time errors and
-- which errors are run-time errors!!!!!!!!
--
-- The use of GO
-- The example from above
insert into Person values('Pauline','systemudvikler',400000)
go
insert into Person values('Britney','programm�r',340000)
--
select * from Person
-- This is an example of variables and variable scope
declare @s varchar(20)
set @s = 'Hey with you'
select @s
-- Another example of assignment
declare @i varchar(20)
select @i=count(*) from Person
select @i
-- This is a dangerous use of select (normally a WRONG solution)
declare @s varchar(20)
select @s=name from Person
select @s
print @s
-- An example using IF
declare @i int
declare @answer varchar(50)
select @i=count(*) from Person
IF @i > 0
set @answer = 'There is ' + cast (@i as varchar) + ' records in the table'
ELSE
set @answer = 'The table is empty'
select @answer
-- An example using WHILE
--Left AS exercise
-- CASE example
SELECT name,
CASE stilling
WHEN 'systemudvikler' THEN 'system developer'
WHEN 'programm�r' THEN 'programmer'
ELSE 'other IT-person'
END
FROM person
-- EXECUTE example
declare @tabname varchar(30)
select @tabname = 'person'
EXECUTE('select * from ' + @tabname)
-- CURSOR example
declare p cursor
for select name,salary from person
declare @name varchar(30),@salary int
open p
fetch p into @name,@salary
while @@fetch_status != -1
begin
print @name
print convert(varchar(20),@salary)
fetch p into @name,@salary
end
close p
deallocate p
select * from person