Wednesday, April 17, 2013

Magical Values keyword as Row Constructor and Table-Valued Constructor in SQL Server 2008

In SQL Sever 2008 one of the new  programmability enhancements is introduced. 

In Sql Server 200 and 2005, To insert multiple values in a table we write multiple statements like 



USE tempdb

GO

CREATE TABLE TableTest
(
[Id] INT,
[Value] VARCHAR(20)
)

-- Multiple insert statements to insert multiple rows in Sql Server 2000 and 2005


INSERT INTO TableTest VALUES (1,'Sql Server 2000')
INSERT INTO TableTest VALUES (2,'Sql sever 2005')
INSERT INTO TableTest VALUES (3,'Sql server 2008')
INSERT INTO TableTest VALUES (4,'Sql server 2012')


SELECT * FROM TableTest

DROP TABLE TableTest




These individual insert statements are now simplified in 2008 with row Constructor and the above insert statements can be written as


INSERT INTO [Employee] VALUES
(1,'Sandeep'),
(2,'Amit'),
(3,'Jyoti'),
(4,'Ghyasul')


Here Values is working as row constructor and it prepare the row. This can be use in Select statement.

CREATE TABLE [Employee]
( [Id] INT,
[Name] VARCHAR(20)
)
-- Multiple insert statements to insert multiple rows in Sql Server 2000 and 2005


INSERT INTO [Employee] VALUES (1,'Sandeep'),
(2,'Amit'),
(3,'Jyoti'),
(4,'Ghyasul')

SELECT * FROM [Employee]

DROP TABLE [Employee]



In similar fashion we can use this Values as Table-Valued Constructor We can create and get the result on the fly using Values constructor which returns multiple rows as drived table and this source table can be use with further statements like Join or other operations.

Select Statement to return multiple rows and create a drived table

SELECT * FROM
(
VALUES (1,'Sandeep',2),
(2,'Amit',4),
(3,'Jyoti',2)
)
AS [Employee]([ID],[Name],[DepartmentID])


Multiple Statements with join operation

SELECT [Employee].[Name],[Department].[Name] [Deparment] FROM
(
VALUES (1,'Sandeep',2),
(2,'Amit',4),
(3,'Jyoti',2)
)
AS [Employee]([EmployeeID],[Name],[DepartmentID])
Inner JOin
(
VALUES (1,'QA'),
(2,'.Net'),
(3,'Java'),
(4,'C++')
)
AS [Department]([DepartmentID],[Name])
On [Employee].[DepartmentID]=[Department].[DepartmentID]


Post a Comment