robert

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 2014 Feature – Timeout For Online Index Rebuilding

by
Facebooktwitterredditpinterestlinkedinmail

SQL Server 2014 comes with a new argument for how to handle the blocking issues that come from online index rebuilds.

 
Before SQL Server 2014

Before the existence of SQL Server 2014, if you were doing an online index rebuild… SQL Server would wait until the index rebuild could get the locks that it needs for rebuilding the index.  This can cause long running maintenance plans on very active tables.

 
SQL Server 2014 Online Index Rebuild Argument

The new functionality available in SQL Server 2014 tags on to the original index/table rebuild syntax.  It allows you to customize how you want to handle the blocking that occurs on the indexes/tables that are being rebuilt.  Here is a sample of the new syntax for managing the blocking on the index rebuild.

ALTER	INDEX ALL
ON		Animal
REBUILD	WITH
(
	SORT_IN_TEMPDB = ON,
	STATISTICS_NORECOMPUTE = OFF,
	ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES,
									     ABORT_AFTER_WAIT = BLOCKERS ) )
)

Notice in the statement above, the new WAIT_AT_LOW_PRIORITY flag that gets added to the ONLINE argument.  When you rebuild an index in SQL Server with the WAIT_AT_LOW_PRIORITY flag turned on, it will allow other operations to proceed while the index build waits for low priority locks.  This argument comes with 2 parameters.

 
MAX_DURATION

The MAX_DURATION parameter is how long you want to wait in minutes.  It must always be in minutes.

 
ABORT_AFTER_WAIT

This parameter comes with 3 options.  This is basically who loses if the rebuild process cannot get the locks that it needs.  The connection that you choose will be terminated after the timeout period.

  • NONE – Continue waiting for the lock
  • SELF – The rebuild operation will fail
  • BLOCKERS – All transactions that are blocking the rebuild will be killed

 
If you do not specify WAIT_AT_LOW_PRIORITY with you index rebuild, then SQL Server will automatically set it to the default of 0 minutes and NONE.

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

Table Reference:  http://msdn.microsoft.com/en-us/library/ms190273.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