selecting

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 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)

 

SQL Server Pagination

by
Facebooktwitterredditpinterestlinkedinmail

Paging through data is something that developers need to do frequently.  Until SQL Server 2012, the best way to achieve this is with the ROW_NUMBER function… and let’s face it… that wasn’t the easiest/most elegant thing to use.  Our prayers have been answered in SQL Server 2012.  The SQL Server team has come out with a better way of doing pagination using the OFFSET FETCH clause.

 
The OFFSET FETCH Clause

The OFFSET FETCH clause allows the client application to pull only a specified range of records.  To implement pagination using the OFFSET FETCH clause, it takes two parts… the OFFSET and the FETCH. 🙂

NOTE: To use the OFFSET FETCH clause for pagination, you must use an ORDER BY clause as well.

 
OFFSET

This part tells SQL Server to skip N number of rows in the result set.

SELECT	*
FROM	Animal
ORDER	BY AnimalName ASC
OFFSET	50 ROWS

The above statement tells SQL Server to return all of the AnimalNames in the Animal table, but only return the names after the 50th.  So 51 and beyond.

 
FETCH

This part tells SQL Server how many records to return in the result set.  If you use FETCH, you always need to use OFFSET.

SELECT	*
FROM	Animal
ORDER	BY AnimalName ASC
OFFSET	50 ROWS
FETCH   NEXT 10 ROWS ONLY

The above statement tells SQL Server to return the AnimalNames in the Animal table with row numbers 51-60.  The OFFSET clause tells it to skip 50 rows and the FETCH clause tells it to return 10 records.

 
Variable Row Counts

You are also able to use a variable for the record counts in the query.  If you wanted to do this, it would look like this:

SELECT	*
FROM	Animal
ORDER	BY AnimalName ASC
OFFSET	@RowsToSkip ROWS
FETCH	NEXT @RowsToReturn ROWS ONLY

 
 
Reference: http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

 

SQL Server Trim

by
Facebooktwitterredditpinterestlinkedinmail

SQL Server trim function does not exist!  Although this seems a bit odd, SQL Server does somewhat have support for trimming data.  SQL Server has built in functions for Right Trim and for Left Trim.  Using these functions, you can accomplish a full trim.  I’ll first show you how to trim the different sides of the text and then show how to trim the full thing.

 
Trim Text Inline

The functions to trim text are RTrim() and LTrim().

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

-- Trims the spaces from the left side of the data
SELECT	LTRIM(@TextToTrim) AS LeftSpacesRemoved

-- Trims the spaces from the right side of the data
SELECT	RTRIM(@TextToTrim) AS RightSpacesRemoved

-- Trims the spaces from both sides of the data
SELECT	LTRIM(RTRIM(@TextToTrim)) AS FullTrim

 
If a full trim is something that you do often, you can create a SQL Server Trim function to make the process a little simpler.

CREATE	FUNCTION TRIM(@TextToTrim VARCHAR(8000))
RETURNS	VARCHAR(8000)
BEGIN

	RETURN	LTRIM(RTRIM(@TextToTrim))

END

You can use this new fancy SQL Server Trim function by calling it like this:

DECLARE	@TextToTrim VARCHAR(50) = '     TEXT TO TRIM      '

SELECT	dbo.TRIM(@TextToTrim) AS FullTrim

Case Sensitive Compare

by
Facebooktwitterredditpinterestlinkedinmail

Occasionally we need to do a string comparison in SQL Server as a case sensitive compare.  By default, case sensitive compare is not turned on for tables in SQL Server.  It’s actually pretty easy to change a string comparison to a case sensitive compare.  You can do this by using the COLLATE clause.

Read below to find out why this works and other tips on doing a case sensitive compare.

SELECT	A.AnimalID, A.AnimalName, AFT.FeedingTime
FROM	Animal A
INNER	JOIN AnimalFeedingTime AFT ON AFT.AnimalName = A.AnimalName COLLATE SQL_Latin1_General_CP1_CS_AS

 
 
What Is The COLLATE Clause

The COLLATE clause is something that you can add to the end of an expression to set the collation of the operation to a specific collation.  So in the example… the string equal comparison is being changed to a collation of SQL_Latin1_General_CP1_CS_AS.

A collation in SQL Server is something defines how data is compared.  This could be things like case sensitivity, acceptable characters, etc…  Most of the time (for English installations of SQL Server) it will be a collation type of SQL_Latin1_General_CP1_CI_AS.  This is because the Latin character set covers English and other Latin based languages.

The two most common collations that I deal with are SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP1_CS_AS.  You can see that they are very similar.  In fact… the only difference is the _CI_ and _CS_.  These actually stand for Case-Insensitive and Case-Sensitive.
 
 
Field Level Case Sensitivity

Even better… you don’t need to set the collation on every sql statement that you want to make case sensitive.  There is actually a setting that you can apply to a field in a table.

NOTE: that if you are joining on this table, both collations must be the same or you will need to specify the collation to use.

Create a new table with a case sensitive field

CREATE
TABLE	Animal
(AnimalID		INT,
AnimalName	VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS)

Change a table’s column to be case sensitive

ALTER
TABLE	Animals
ALTER	COLUMN AnimalName VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS