SQL Server 2008 - Table Type Parameter

This is very neat feature in SQL Server 2008 which many people really waited for...

now SQL Server 2008 has got a table type which can be passed as parameters in functions and stored procedures.

simplest example.. lets create employees table.

CREATE TABLE dbo.Employee(
EmpID int NOT NULL,
EmpName nvarchar(100) NOT NULL,
EmpEmail nvarchar(100) NOT NULL)


and create stored procedure to add new employees..

CREATE PROCEDURE NewEmployeeMS(@EmpID int,@EmpName nvarchar(100),@EmpEmail
nvarchar(100))
As
BEGIN
INSERT INTO dbo.Employee
values(
@EmpID, @EmpName, @EmpEmail)


now if I have to add record in it.. I have to execute this procedure multiple times..

now lets take example.. that I got records in another table and I have to get records from that and put it in another table.. I will go about doing it this way..

CREATE TYPE EmployeeTableType AS TABLE
(EmpID INT, EmpName nvarchar(100), EmpEmail nvarchar(100))

creating table type.. and when I create stored procedure it will take parameter as table type only..

CREATE PROCEDURE NewEmployee(@EmployeeDetails EmployeeTableType READONLY)
As
BEGIN
INSERT INTO dbo.Employee
SELECT * FROM @EmployeeDetails
END


notice READONLY thing in parameter.. it says that table is going to be read only.. so no any update, insert, delete will be performed.. just select.. and this thing is necessary.

finally I will do this..

DECLARE @NewEmployees EmployeeTableType
INSERT INTO @NewEmployees
VALUES(1,'John McLean','JohnMcLean@contoso.com')
INSERT INTO @NewEmployees
VALUES(2,'Bob Smith','BobSmith@contoso.com')
INSERT INTO @NewEmployees
VALUES(3,'Ted Connery','TedConnery@contoso.com')
EXECUTE NewEmployee @NewEmployees

SQL Server 2008 - MERGE Function

I am quite impressed with the MERGE function.. in SQL Server 2008, which is a new feature added.

lets take a simplest example to understand MERGE Function.

let's say we got a PurchaseRecords table, in which we are storing customerId and productId and PurchaseDate...

here is code to create a table with all indexes..

IF OBJECT_ID (N'dbo.PurchaseRecords', N'U') IS NOT NULL
DROP TABLE dbo.PurchaseRecords;
GO

CREATE TABLE PurchaseRecords (ProductID int, CustomerID int, PurchaseDate datetime);
GO

IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_PurchaseRecords_ProductID')
DROP INDEX IX_PurchaseRecords_ProductID ON dbo.PurchaseRecords;
GO

CREATE CLUSTERED INDEX IX_PurchaseRecords_ProductID
ON dbo.PurchaseRecords (ProductID);
GO

and here is the code to insert some dummy records in table.

INSERT INTO PurchaseRecords VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO


now we gona have a table which is going to store information regards to CustomerPurchaseHabit..

and code for that is here..

IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL
DROP TABLE dbo.FactBuyingHabits;
GO

CREATE TABLE FactBuyingHabits (ProductID int, CustomerID int, LastPurchaseDate datetime);
GO

IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FactBuyingHabits_ProductID')
DROP INDEX IX_FactBuyingHabits_ProductID ON dbo.FactBuyingHabits;
GO

CREATE CLUSTERED INDEX IX_FactBuyingHabits_ProductID
ON dbo.FactBuyingHabits (ProductID);
GO


INSERT INTO FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO


ok now what we want to do is.. we want to go through each records in PurchaseRecords and check whether this information exists in FactBuyingHabits or not.. if it exists then update its date if it doesnt then insert it, now if we want to do this.. we will go about creating cursor then if condition inside it and do it.. what do we get? we get result.. but cursors are very slow... in SQL Server 2008 Microsoft introduced MERGE function which allows us to do this pretty easily, and performance is increased pretty good.

and code for this is something like following:

MERGE FactBuyingHabits AS fbh
USING (SELECT CustomerID, ProductID, PurchaseDate FROM PurchaseRecords) AS src
ON (fbh.ProductID = src.ProductID AND fbh.CustomerID = src.CustomerID)
WHEN MATCHED THEN
UPDATE SET fbh.LastPurchaseDate = src.PurchaseDate
WHEN NOT MATCHED THEN
INSERT (CustomerID, ProductID, LastPurchaseDate)
VALUES (src.CustomerID, src.ProductID, src.PurchaseDate);

Bad Captcha

This is seriously bad captcha.. I cant understand what it says...

while I was writing above post I got this captcha..

Blog Addiction

70%How Addicted to Blogging Are You?