t-sql

Order By On A Union?

by
Facebooktwitterredditpinterestlinkedinmail

Have you ever tried to add an ORDER BY clause to a UNION operator and have gotten this error message?

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘UNION’.

SELECT	AnimalName
FROM	Animal
ORDER	BY AnimalName

UNION

SELECT	AnimalName
FROM	Animal
ORDER	BY AnimalName

So what the heck is this and how do we fix it?

 
What The Error Says
The error basically says that you have an issue with your syntax. The ORDER BY clause should only be applied one time in your query. Logically SQL Server will pull the data requested from the two tables… then it will perform the sort operation on the data.

When people see the UNION operator, they think that it is combining two different result sets, thus they need two ORDER BY clauses. In fact, you only need to order the final result set. This is done by placing the ORDER BY command at the very end of the statement.

SELECT AnimalName
FROM Animal
-- Notice the removed ORDER BY here?

UNION

SELECT AnimalName
FROM Animal
ORDER BY AnimalName

 

Parse XML With SQL Server

by
Facebooktwitterredditpinterestlinkedinmail

SQL Server has a lot of useful functionality built-in when it comes to XML.  Today I want to show you an easy way to parse XML data in to a usable format inside your query.

 
For this example, I will be parsing XML data into a temp table, then inserting that data in to a table.

DECLARE	@XMLToParse  XML

-- Load the XML data in to a variable to work with.
-- This would typically be passed as a parameter to a stored proc
SET	@XMLToParse =	'<Animals>
			   <LandAnimals>
				<Animal>Baboon</Animal>
				<Animal>Yak</Animal>
				<Animal>Zebra</Animal>
			   </LandAnimals>
			</Animals>'

-- Declare temp table to parse data into
DECLARE	@ParsingTable  TABLE
	(Animal	VARCHAR(100))

-- Parse the XML in to the temp table declared above
INSERT
INTO	@ParsingTable
	(Animal)
SELECT	xmlData.A.value('.', 'VARCHAR(100)') AS Animal
FROM	@XMLToParse.nodes('Animals/LandAnimals/Animal') xmlData(A)

-- Insert into the actual table from the temp table

INSERT
INTO	Animals
	(AnimalName)
SELECT	Animal
FROM	@ParsingTable

 
The statement that parses the XML has 2 parts to it.

FROM   @XMLToParse.nodes('Animals/LandAnimals/Animal') xmlData(A)

This part says to parse the @XMLToParse variable (previously filled with the XML file) using the method built in to SQL Server called .nodes.  It specifies the repeating node to be Animals/LandAnimals/Animal.  It assigns an aliases this XML parsed records as a table named xmlData.  The (A) is the column name of the rowset.  This will be referenced in the select part of the statement.

 

SELECT  xmlData.A.value('.', 'VARCHAR(100)') AS Animal

The select part of this statement references xmlData (which is the table aliases) and A (which is the column named for that table).  It calls the .value function to return the value from the table/column.  For the .value function, you pass in 2 elements.

The first element is the field.  In this case we are passing in just a period.  We do this because there is no node below the Animal node.  If there was, we would need to specify it here..

The second parameter is the datatype that you would like the value to be casted as.  Then I always aliases the field to something relevant.

 
Although parsing XML can be a little confusing in SQL Server, it is very powerful.  This is a great way to pass bulk data to a stored procedure from any type of client application.

Difference Between UNION and UNION All

by
Facebooktwitterredditpinterestlinkedinmail

The UNION operation and the UNION ALL operation perform almost the same operation.  They are both used to combine two result sets in to one result set.  The main difference between the two operations is that the UNION operation will return the unique records in the final result set.  The UNION ALL operation will return any duplicates in the final result set.

 
In the examples below, we will assume we have two tables with the following data.  Notice that the values Cat and Dog exist in both tables.
[table width=”0″ colwidth=”100|100″ colalign=”left|left”]
Table1,Table2
Alligator,Cat
Beaver,Dog
Cat,Eagle
Dog,Frog
[/table]

 
The UNION Operation

SELECT	*
FROM	Table1

UNION

SELECT	*
FROM	Table2

[table width=”0″ colwidth=”100″ colalign=”left”]
Result
Alligator
Beaver
Cat
Dog
Eagle
Frog
[/table]

Notice that the duplicate Cat and Dog values were removed from the result.

 
The UNION ALL Operation

SELECT	*
FROM	Table1

UNION	ALL

SELECT	*
FROM	Table2

[table width=”0″ colwidth=”100″ colalign=”left”]
Result
Alligator
Beaver
Cat
Dog
Cat
Dog
Eagle
Frog
[/table]

Notice that the duplicate Cat and Dog values are left in the result.

 
Speed Considerations

One final difference between UNION and UNION ALL is the speed difference.  Because the UNION operation needs to return a distinct set of values, this will take extra processing time.  A general rule is that if you know that the combined data is going to be unique already… just use the plain UNION ALL operation.  Only use the UNION operation if there are duplicates that you would like to have remove.
NOTE:  The UNION and UNION ALL operations are actually one operation.  The ALL keyword is just an attribute.  Because they are so different, I find it is easier to think of them both as separate operations.

 
Reference:  http://msdn.microsoft.com/en-us/library/ms180026.aspx
 

SQL Server Quarter From Date

by
Facebooktwitterredditpinterestlinkedinmail

One question that we get a lot is how to get the quarter from a date.  SQL Server has a very easy way to get the quarter from a date (since SQL 2005) using the DATEPART command.

 
To get the quarter from the current date

SELECT	DATEPART(quarter, GETDATE()) AS Quarter_From_Date

 
To get the quarter from a different date

DECLARE	@DateToCheck DATETIME

SET		@DateToCheck = '7/1/2014'

SELECT	DATEPART(quarter, @DateToCheck) AS Quarter_From_Date

 

SQL Server Truncate Date

by
Facebooktwitterredditpinterestlinkedinmail

A common function that people need to do when dealing with datetimes is extracting the date from the datetime.  Oracle has a built-in function to do this called TRUNC.  SQL Server does not have this (yet).  However… you can accomplish this very easily.  Here are a few ways to perform a truncate date function in SQL Server.

-- Assign date to variable
DECLARE	@DateToTruncate	DATETIME = '2014-08-01 14:12:34'

-- Get the date using casts
SELECT	CAST(CAST(@DateToTruncate AS DATE) AS DATETIME)

-- Get the date using convert
SELECT	CAST(CONVERT(VARCHAR(10), @DateToTruncate, 101) AS DATETIME)