EXCEPT operation in SQL server 2005

The EXCEPT operation is a new feature that is introduced in SQL Server 2005

EXCEPT returns any distinct values from the left query that are not also found on the right query.

In general, except operations is used to find difference between two identical tables.
The basic rules for combining the result sets of two queries that use EXCEPT are the following:
1. The number and the order of the columns must be the same in all queries. 

2. The data types must be compatible. 

Consider the situation where we have to compare two tables for the correctness of their data or maybe they are tables in Test and Production databases, and you want to see if they are in sync or not. This can be done Using EXCEPT Operation on these two tables.
Let us try it out using two tables Table_A and Table_B. Consider that these tables have same schema.


Example 1:
Select * from Table_A

Except

Select * from Table_B

This example, compares Table_B with Table_A and give the distinct values from Table_A which are not present in Table_B. i.e. Any additional rows that are present in Table_A but not present in Table_B will be displayed along with the rows in Table_A which does not match in Table_B. But this does not display Vice-Versa.


Example 2:

SELECT * FROM

 (

 SELECT * FROM Table_A

 EXCEPT

 SELECT * FROM Table_B

 ) AS DifferenceFromLeft

 UNION

 SELECT * FROM

 (

 SELECT * FROM Table_B

 EXCEPT

 SELECT * FROM Table_A

 ) AS DifferenceFromRight

This example, give the complete differences between the two tables. i.e. any non matching records in any of the two tables as well as any new records in any of the two tables will be displayed. But the drawback of this query is that it does not show from which table the mismatch is from.


Example 3:
To overcome the drawback of Example 2, we will tune the query of Example 2 to include the source table names which will be helpful in identifying the source of the mismatch.
SELECT 'Table_A' as Source, * FROM

 (

 SELECT * FROM Table_A

 EXCEPT

 SELECT * FROM Table_B

 ) AS DifferenceFromLeft

 UNION

 SELECT 'Table_B' as Source, * FROM

 (

 SELECT * FROM Table_B

 EXCEPT

 SELECT * FROM Table_A

 ) AS DifferenceFromRight
To get the correct and expected results out of EXCEPT operation, it is mandatory to specify the columns in both the queries in the same order. If the order of the columns mismatch, then the result from the EXCEPT operation will mislead (you will not get the correct results).

This article is also available in pdf format for downloading.

Please Click here to get your copy.

SQL Exercise Examples (Basics)

Download SQL Server Examples from Here.
This Contains basics and are usefull for starters.

SQL Server 2008 requires Microsoft .NET Framework 2.0 SP2 to be installed

When you are installing SQL Server 2008 express edition, your installation might fail due to the lower version of .NET Framework.

To overcome this error
1. Install .NET Framework 3.5 and 3.5 SP1.
     .NET Framework 3.5 can be downloaded from Here.
     .NET Framework 3.5 SP1 can be downloaded from Here.
2. Install Windows installer 4.5.
    Windows installer 4.5 can be downloaded from Here.

Undocumented DATE and TIME functions in SQL

There are some Undocumented Date and time functions that are available in SQL.
These functions are listed below.
Run these functions and check their results, they are self-explanatory.
select {fn current_date()}
select {fn current_time()}
select {fn now()}
select {fn extract(hour from getdate())}
select {fn extract(minute from getdate())}
select {fn extract(second from getdate())}
select {fn extract(day from getdate())}
select {fn extract(month from getdate())}
select {fn extract(year from getdate())}
select {fn dayname(GetDate())}
select {fn monthname(GetDate())}
select {fn month(GetDate())}
select {fn year(GetDate())}

How to Backup Analysis Service Database

An Analysis Service database can be backed up using SSMS or XMLA Query Editor.

Backup using SSMS:

1. Login to SSAS instance using SSMS

2. Right-Click on the Analysis service database you want to backup.

3. And Choose the option Backup.

4. Now in the Backup Database page, Enter the location to where you want the backup file to be placed.

5. Choose the other options, if required.

6. And Click on OK to backup the database.
Backup Using XMLA Query Editor:
1. Open the XMLA Query Editor using the New Query Option.
2. Enter the below query.
 <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> 
   <Object>
     <DatabaseID>Test</DatabaseID>
   </Object>
   <File>E:\Test.abf</File>
   <AllowOverwrite>true</AllowOverwrite>
 </Backup>
3. Replace the DatabasesID with the Analysis Service database name which you want to backup.

4. And replace the File to where(location on disk) you want the analysis service database to be backed up to.

5. Execute the Query.