Category: SQL Server & Data Page 1 of 5

Simple sample in how to use Table Valued Parameter which is a new feature in SQL Server 2008. I found it very useful to pass bulk data from one SP to another SP

CREATE TYPE JobQueueBroker AS TABLE (JobID INT NOT NULL, UpdateDate DATETIME DEFAULT(GETDATE()))
GO

CREATE PROCEDURE [dbo].[Jobs_JobX_SubmitQueueBulk]
@Jobs JobQueueBroker READONLY
AS

BEGIN
DECLARE @Message XML

SELECT @Message = ( SELECT * FROM @Jobs
FOR XML PATH(‘Job’),
TYPE
);

— Above will fomulate valid XML message
DECLARE @Handle UNIQUEIDENTIFIER ;

— Dialog Conversation starts here
BEGIN DIALOG CONVERSATION @Handle FROM SERVICE ServiceJobXJobFinishedProcessing TO SERVICE ‘ServiceJobXJobUpdate’ ON CONTRACT [JobContract] WITH ENCRYPTION = OFF ;
SEND ON CONVERSATION @Handle MESSAGE TYPE JobDetails (@Message) ;

END
GO

DECLARE @Jobs JobQueueBroker
INSERT @Jobs VALUES (1, GETDATE())
INSERT @Jobs VALUES (2, GETDATE())
INSERT @Jobs VALUES (3, GETDATE())
EXEC dbo.[Jobs_JobX_SubmitQueueBulk] @Jobs
GO

In SQL Server 2008, we can do a table synchronisation in one transaction of SQL Command. For example normally, we have a source table and then we have an updated records and what we would like to do is "I want to delete the record from the source table if it is not in my updated list, if there is a matched record then I’d like to update the value to the latest value, and if the record is not exists at all on the source table then I’d like to insert it"

I got a sample query with the recordset and syntax below in how to use "MERGE" to handle the case above. I got the actual sample code from (http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/) – by Arshad Ali

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = ‘Products’)
BEGIN
DROP TABLE Products
END
GO
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
–Insert records into target table
INSERT INTO Products
VALUES
(1, ‘Tea’, 10.00),
(2, ‘Coffee’, 20.00),
(3, ‘Muffin’, 30.00),
(4, ‘Biscuit’, 40.00)
GO
SELECT * FROM Products
GO
–Create source table
DECLARE @UpdatedProducts TABLE
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
–Insert records into source table
INSERT INTO @UpdatedProducts
VALUES
(1, ‘Tea’, 10.00),
(2, ‘Coffee’, 25.00),
(3, ‘Muffin’, 35.00),
(5, ‘Pizza’, 60.00)

SELECT * FROM @UpdatedProducts

MERGE Products AS TARGET
USING @UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
–When records are matched, update
–the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
–When no records are matched, insert
–the incoming records from source
–table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
–When there is a row that exists in target table and
–same record does not exist in source table
–then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

SELECT * FROM Products

GO

I was required to create a SQL Splitter function and I tried to browse internet to find an article about it but I never thought there is someone has done wonderful job in comparing the performance of his SQL Split function including with the benchmark and technical break down. Now based on this article, I’m quite sure I have a strong/high performance SQL Splitter function

http://bradsruminations.blogspot.com.au/2010/08/integer-list-splitting-sql-fable.html

Regards,
Fransiscus

Now you can use IIF in SQL Server 2012 instead of CASE (again this only if you have 2 possible values that you want to evaluate)

–This will return Apple
SELECT IIF(‘APPLE’ = ‘APPLE’, ‘Apple’, ‘Banana’)

–This will return Banana
SELECT IIF(‘APPLE’ = ‘Banana’, ‘Apple’, ‘Banana’)

In SQL Server 2005, normally when we do paging we need to use CTE or nested SELECT Statement, but in SQL Server 2012 we can use OFFSET and FETCH to do the paging

Let’s start of how we normally do paging in SQL Server 2005/2008 – this will return the first 10 rows ordered by REF

WITH Fields AS
(
SELECT REF, NAME, DISPLAY, ROW_NUMBER() OVER (ORDER BY REF) AS RowNumber
FROM SU_FIELD
)
SELECT REF, NAME, DISPLAY
FROM Fields
WHERE RowNumber BETWEEN 1 AND 10
ORDER BY RowNumber ASC;

To move to next page we do

WITH Fields AS
(
SELECT REF, NAME, DISPLAY, ROW_NUMBER() OVER (ORDER BY REF) AS RowNumber
FROM SU_FIELD
)
SELECT REF, NAME, DISPLAY
FROM Fields
WHERE RowNumber BETWEEN 11 AND 20
ORDER BY RowNumber ASC;

In SQL Server 2012 – it looks much more simpler to write the query. OFFSET is used for skipping the first x rows ad FETCH NEXT is used to control how many records to be returned

–This will return 10 records without skipping any row (REF from 0 – 9)
SELECT REF, NAME, DISPLAY
FROM SU_FIELD
ORDER BY REF
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

to move to the next page

–This will return 10 records with skipping first 10 rows (REF from 10 – 19)
SELECT REF, NAME, DISPLAY
FROM SU_FIELD
ORDER BY REF
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

One of the new TSQL feature in SQL Server 2012 is TRY_CONVERT, basically it’s a function that will return null if the object passed is not compatible with the expected casting. In earlier version of SQL Server we don’t have this feature which means you need to make sure your data is cast-able to the expected format

Earlier Version of SQL Server – you can run this

SELECT CONVERT(INT, ‘abc’)

and it will throw an error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘abc’ to data type int.

Then you will normally do work around with this to avoid the error which means no record will be returned when the object is not a valid data type

SELECT CONVERT(INT, ‘abc’) AS TEST WHERE ISNUMERIC(‘abc’) = 1

SQL Server 2012

SELECT TRY_CONVERT(INT, ‘abc’) AS TEST

This will still return a record but with NULL value when the data is not cast-able

I found some interesting article to share in regards of joining the UDF into your join which ends up in executing the UDF as many as number of records being joined to. The solution is to put it into a temp table before joining it

http://sqlblog.com/blogs/rob_farley/archive/2011/11/08/when-is-a-sql-function-not-a-function.aspx

The default template in SQL profiler doesn’t trace the dynamic SQL executed by the stored procedure. In order to trace the Dynamic SQL, you need to turn on these 2 options in your tracing profile

SP:stmtstarting and SQL:stmtstarting

Below is the sample in how to reproduce a recursive parent-child relationship and how to find out which records are the troublesome one

CREATE TABLE RecursiveSample
(
REF INT NOT NULL,
PARENT_REF INT NOT NULL,
NAME VARCHAR(255)
)
GO
/*Inserting the parents*/
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(1, 0, ‘Parent 1’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(2, 0, ‘Parent 2’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(3, 0, ‘Parent 3’)
GO
/*Inserting the child*/
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(4, 1, ‘Child Parent 1’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(5, 2, ‘Child Parent 2’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(6, 3, ‘Child Parent 3’)
GO
/*Inserting the grand child*/
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(7, 4, ‘Grand Child Parent 1’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(8, 5, ‘Grand Child Parent 2’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(9, 6, ‘Grand Child Parent 3’)
GO
/*Let’s update the record to make it recursive*/
UPDATE RecursiveSample SET PARENT_REF = 7 WHERE REF = 4
GO
/*Run this query to find the Parent child structure*/
;WITH CTE
AS(
SELECT *,[PATH]=CAST(REF AS NVARCHAR(1000))FROM RecursiveSample WHERE PARENT_REF =0
UNION ALL
SELECT A.*,[PATH]=CAST(C.[PATH]+’/’+RTRIM(C.REF) AS NVARCHAR(1000))
FROM CTE C
INNER JOIN RecursiveSample A ON C.REF=A.PARENT_REF AND PATINDEX(‘/’+RTRIM(A.REF)+’/%’,’/’+C.[PATH]+’/’)=0
)
SELECT * FROM CTE
/*
REF PARENT_REF NAME PATH