OUTPUT clause and MERGE statement

Just responded to a post in MSDN forum, link:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c06e1db4-7fd6-43c4-8569-5335d555dac8

Accroding to MS-BOL, OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Lets go with a self descriptive example:

–> OUTPUT with INSERT

create table manoj (sn int, ename varchar(50))
insert into manoj
OUTPUT INSERTED.*
values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj')

This gives me following output instead of normal message (N row(s) affected):
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj

select * from manoj

This gives me the same output as above:
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj

–> OUTPUT with DELETE

delete from manoj
OUTPUT DELETED.*
where sn = 4

This gives me following output:
sn      ename
4	pankaj

select * from manoj

Now the result set is changed to:
sn      ename
1	manoj
2	hema
3	kanchan

–> OUTPUT with UPDATE

update manoj
set ename = 'pankaj'
OUTPUT DELETED.*, INSERTED.*
from manoj
where sn = 2

This gives me following output:
sn      ename   sn     ename
2	hema	2	pankaj

select * from manoj

Now the result set is changed to:
sn      ename
1	manoj
2	pankaj
3	kanchan

–> OUTPUT with MERGE

According to MS-BOL, MERGE performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

create table manoj2 (sn int, ename varchar(50))

insert into manoj2
values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj'), (5,'saurabh')

select * from manoj2

This gives me following output instead of normal message (N row(s) affected):
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj

MERGE manoj AS TARGET
USING (SELECT sn, ename FROM manoj2) AS SOURCE
ON (TARGET.sn = SOURCE.sn)
WHEN MATCHED THEN
	UPDATE SET TARGET.ename = SOURCE.ename
WHEN NOT MATCHED BY TARGET THEN
	INSERT (sn, ename) VALUES (sn, ename)
OUTPUT $action, DELETED.*, INSERTED.*;

This gives me following output:
$action sn      ename   sn      ename
INSERT	NULL	NULL	4	pankaj
INSERT	NULL	NULL	5	saurabh
UPDATE	1	manoj	1	manoj
UPDATE	2	pankaj	2	hema
UPDATE	3	kanchan	3	kanchan

select * from manoj

Now the result set is changed to:
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj
5	saurabh

–> Final cleanup

drop table manoj
drop table manoj2

Plz note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

MS BOL:-

- On MERGE:
http://technet.microsoft.com/en-us/library/bb510625.aspx

- On OUTPUT:
http://technet.microsoft.com/en-us/library/ms177564.aspx

SQL Server 2012, codename Denali – New Features

Microsoft released out the first Community Technology Preview (CTP) of the next generation of its SQL Server Enterprise Database product codenamed Denali.

I found very interesting information on MS BOL and want to brief it here as we developers are interested in new features, like:

A. New Server Installation:
1. SQL Server Multi-Subnet Clustering (One can now configure a SQL Server failover cluster using clustered nodes on different subnets, link:
http://msdn.microsoft.com/en-us/library/ff878716(v=SQL.110).aspx
)
2. Prerequisite Installation during SQL Server Setup (You must download the following and install them before you start SQL Server Setup, link:
http://msdn.microsoft.com/en-us/library/ms143219(v=SQL.110).aspx
)
2.a. Windows PowerShell
2.b. MS.NET Framework
2.c. No-Reboot package for .NET Framework 4.0
3. No support for SQL Server Itanium editions

B. New (Database Engine):
1. Availability Enhancements – “HADR” is a high-availability and disaster recovery solution introduced in SQL Server Code-Named “Denali” to enable you to maximize availability for one or more of your user databases. Deploying “HADR” involves configuring one or more availability groups. Each availability group establishes the context within which a selected set of user databases can fail over as a single unit. (
http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx
)
2. Manageability Enhancements – New Improved & enhanced tools & monitiring features.
2.a. SSMS, enhanced functionality for Transact-SQL debugging and IntelliSense (
http://msdn.microsoft.com/en-us/library/cc645579(v=SQL.110).aspx
)
2.b. Powershell, is no longer installed by SQL Server Setup, but is a pre-requisite for installing SQL Server Code-Named “Denali”.
2.c. Contained Databases, Moving databases from one instance of the SQL Server Database Engine to another instance of the Database Engine is simplified by introducing contained databases. Users in a contained database are no longer associated with logins on the instance of SQL Server. Many other dependencies on the instance are also removed. (
http://msdn.microsoft.com/en-us/library/ff929071(v=SQL.110).aspx
)
2.d. Startup Options, Database Engine startup options are now configured by using a new Startup Parameters tab of SQL Server Configuration Manager. (
http://msdn.microsoft.com/en-us/library/ms345416(v=SQL.110).aspx
)
3. Programmability Enhancements – includes property-scoped full-text search and customizable proximity search, ad-hoc query paging, circular arc segment support for spatial types, support for sequence objects, and numerous improvements and additions to Transact-SQL.
3.a. Full-Text Search, You can configure a full-text index to support property-scoped searching on properties, such as Author and Title, which are emitted by IFilters. (
http://msdn.microsoft.com/en-us/library/ee677637(v=SQL.110).aspx
)
You can customize a proximity search by using the new custom NEAR option of the CONTAINS predicate or CONTAINSTABLE function. Custom NEAR enables you to optionally specify the maximum number of non-search terms that separate the first and last search terms in a match. Custom NEAR also enables you to optionally specify that words and phrases are matched only if they occur in the order in which you specify them. (
http://msdn.microsoft.com/en-us/library/ms142568(v=SQL.110).aspx
)
3.b. Metadata Discovery, The SET FMTONLY option for determining the format of a response without actually running the query is replaced with sp_describe_first_result_set, sp_describe_undeclared_parameters, sys.dm_exec_describe_first_result_set, and sys.dm_exec_describe_first_result_set_for_object. (
http://msdn.microsoft.com/en-us/library/ms173839(v=SQL.110).aspx
)
3.c. EXECUTE Statement, can now specify the metadata returned from the statement by using the WITH RESULT SETS argument. (
http://msdn.microsoft.com/en-us/library/ms188332(v=SQL.110).aspx
)
3.d. UTF-16 Supplementary Characters (SC) Collations
3.e. Ad-hoc Query Paging Implementation, You can specify a range of rows returned by a SELECT statement based on row offset and row count values that you provide. This is useful when you want to control the number of rows sent to a client application for a given query. (
http://msdn.microsoft.com/en-us/library/ms188385(v=SQL.110).aspx
)
3.f. Circular Arc Segment Support for Spatial Types
3.g. Sequence Objects, is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. It operates similar to an identity column, but sequence numbers are not restricted to use in a single table. (
http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx
)
4. Scalability and Performance Enhancements (http://msdn.microsoft.com/en-us/library/cc645580(v=SQL.110).aspx)
5. Security Enhancements, include new SEARCH PROPERTY LIST permissions, new user-defined server roles, and new ways of managing server and database roles.
5.a. New Permissions, New GRANT, REVOKE, and DENY permissions to a SEARCH PROPERTY LIST are available. New GRANT, REVOKE, and DENY permissions to CREATE SERVER ROLE and ALTER ANY SERVER ROLE.
5.b. New Role Management, User-defined server roles are now available. To manage user-defined server roles and to add and remove members from all server roles, use CREATE SERVER ROLE, ALTER SERVER ROLE, and DROP SERVER ROLE.
5.c. Hashing Algorithms, The HASHBYTES function now supports the SHA2_256, and SHA2_512 algorithms.
5.d. Database Engine Access through Contained Databases

C. New (Integration Services): (
http://msdn.microsoft.com/en-us/library/bb522534(v=SQL.110).aspx
)
1. Deployment and Administration Enhancements, Build your project, packages, and parameters to a project deployment file (.ispac extension) and deploy it directly to the SQL Server database engine. New dialog boxes in SQL Server Management Studio and a comprehensive set of Transact-SQL views and stored procedures help you manage, execute, and monitor your packages in real time.
2. Object Impact and Data Lineage Analysis
3. Usability Enhancements
4. Reduced Memory Usage by the Merge and Merge Join Transformations, Merge and Merge Join transformations are more robust and reliable now.
5. New Data Correction Component, that enables you to more easily and accurately improve the quality of data.

Source (MSDN):
http://msdn.microsoft.com/en-us/library/bb500435(v=SQL.110).aspx

Note: I’ve copied and collated the entire content from MS-BOL link above,  just for keeping lot of things at one place and in concise.

Integrity Constraints

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity allows to define certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn’t meet these requirements, the RDBMS will not allow so.

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.

CONSTRAINT=The threat or use of force to prevent, restrict, or dictate the action or thought of others.

There are 7 types of Constraints and they are grouped in to 4 types, they are:

A. ENTITY INTEGRITY
	1.PRIMARY KEY
	2.UNIQUE KEY
B. REFERENTIAL INTEGRITY
	3.FOREIGN KEY
C. DOMAIN INTEGRITY
	4.DEFAULT
	5.NOT NULL
	6.CHECK
D. USER DEFINED INTEGRITY
	7.RULE

CUBE, ROLLUP, COMPUTE, COMPUTE BY, GROUPING SETS

The CUBE and ROLLUP operators are useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.

Difference b/w CUBE and ROLLUP:
- CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
- ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

select 'A' [class], 1 [rollno], 'a' [section], 80 [marks], 'manoj' stuName
into #tempTable
UNION
select 'A', 2, 'a', 70 ,'harish'
UNION
select 'A', 3, 'a', 80 ,'kanchan'
UNION
select 'A', 4, 'b', 90 ,'pooja'
UNION
select 'A', 5, 'b', 90 ,'saurabh'
UNION
select 'A', 6, 'b', 50 ,'anita'
UNION
select 'B', 1, 'a', 60 ,'nitin'
UNION
select 'B', 2, 'a', 50 ,'kamar'
UNION
select 'B', 3, 'a', 80 ,'dinesh'
UNION
select 'B', 4, 'b', 90 ,'paras'
UNION
select 'B', 5, 'b', 50 ,'lalit'
UNION
select 'B', 6, 'b', 70 ,'hema'

select class, rollno, section, marks, stuName from #tempTable

Output:
class	rollno	section	marks	stuName
A	1	a	80	manoj
A	2	a	70	harish
A	3	a	80	kanchan
A	4	b	90	pooja
A	5	b	90	saurabh
A	6	b	50	anita
B	1	a	60	nitin
B	2	a	50	kamar
B	3	a	80	dinesh
B	4	b	90	paras
B	5	b	50	lalit
B	6	b	70	hema

WITH ROLLUP:

select class, section, sum(marks) [sum]
from #tempTable
group by class, section with ROLLUP

Output:
class	section	sum
A	a	230
A	b	230
A	NULL	460  -- 230 + 230  = 460
B	a	190
B	b	210
B	NULL	400  -- 190 + 210 = 400
NULL	NULL	860  -- 460 + 400 = 860 

WITH CUBE:

select class, section, sum(marks) [sum]
from #tempTable
group by class, section with CUBE

Output:
class	section	sum
A	a	230
A	b	230
A	NULL	460  -- 230 + 230  = 460
B	a	190
B	b	210
B	NULL	400  -- 190 + 210 = 400
NULL	NULL	860  -- 460 + 400 = 860
NULL	a	420  -- 230 + 190 = 420
NULL	b	440  -- 230 + 210 = 440 

COMPUTE & COMPUTE BY:
A COMPUTE BY clause allows you to see both detail and summary rows with one SELECT statement. You can calculate summary values for subgroups, or a summary value for the whole result set.
The COMPUTE clause takes the following information:
- The optional BY keyword. This calculates the specified row aggregate on a per column basis.
- A row aggregate function name. This includes SUM, AVG, MIN, MAX, or COUNT.
- A column upon which to perform the row aggregate function.

select class, section, marks
from #tempTable
COMPUTE SUM(marks), AVG(marks)

select class, section, marks
from #tempTable
order by class
COMPUTE SUM(marks), AVG(marks) by class

select class, section, marks
from #tempTable
order by class, section
COMPUTE SUM(marks), AVG(marks) by class, section

Final Cleanup, drop the temp tables:

drop table #tempTable

GROUPING SETS:
SQL Server 2008 has a new GROUPING SETS operator which can generate the same result set as that generated by using a simple GROUP BY, ROLLUP, or CUBE operator.
More on GROUPING SETS:
http://msdn.microsoft.com/en-us/library/bb522495.aspx

Useful Links:
ROLLUP & CUBE:
http://msdn.microsoft.com/en-us/library/ms189305(SQL.90).aspx

COMPUTE & COMPUTE BY:
http://msdn.microsoft.com/en-us/library/ms190452(v=SQL.90).aspx

Query Excel file source through Linked Server

In previous post we saw how to setup a Linked Server for MySQL Database. Now lets go with other data sources. Excel files are the most important source of data and report management in a particular department.

When you need to do some query on Excel data, one way is to use Import/Export wizard, push the excel contents to SQL Server and then query on SQL Server DB. Another and easy way is to create a Linked Server to Excel file and query directly the Excel file itself.

You just need to create the Excel file and execute the following SQL Statements below:

For Excel 2003 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLS_NewSheet',
	@srvproduct = 'Jet 4.0',
	@provider = 'Microsoft.Jet.OLEDB.4.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xls',
	@provstr = 'Excel 5.0; HDR=Yes'

Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLS_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLS_NewSheet...[Sheet1$]

For Excel 2007 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLSX_NewSheet',
	@srvproduct = 'ACE 12.0',
	@provider = 'Microsoft.ACE.OLEDB.12.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xlsx',
	@provstr = 'Excel 12.0; HDR=Yes'

Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLSX_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLSX_NewSheet...[Sheet1$]

Note: If your excel file don’t have headers, then set HDR=No

You may need to execute the following SQL Statements to configure the Linked Server initially:

USE MSDB
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

Useful Link:
http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx