Tag Archives: Sql Server 2016

Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server

Sql Server, Sql Server 2016, , , , , , , , , , ,

This article gives an introduction to the DATEDIFF and DATEDIFF_BIG functions by listing out the differences and similarities between them.

[ALSO READ]: How to get difference between two dates in Years, Months and days in Sql Server

DATEDIFF Function DATEDIFF_BIG Function
This function is available from very old versions of Sql Server. This function is introduced in Sql Server 2016
This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate
Syntax:
DATEDIFF( datepart, startdate, enddate)

Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and
nanosecond

Syntax:
DATEDIFF_BIG(datepart,startdate,enddate)

Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and
nanosecond

This functions return value data type is INT This functions return value data type is BigINT
Example: Below is an example getting difference between two dates in days using DATEDIFF function:

SELECT DATEDIFF(DAY,
 '12/01/2015','12/02/2015')
 AS 'Difference in days'

RESULT:
Difference in days
——————
1

Example: Below is an example getting difference between two dates in days using DATEDIFF_BIG function:

SELECT DATEDIFF_BIG(DAY,
 '12/01/2015','12/02/2015')
  AS 'Difference in days'

RESULT:
Difference in days
——————–
1

Example: This example demonstrates the behavior of the DATEDIFF function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF(MILLISECOND,
 '12/01/2015','12/30/2015')
 AS 'Difference in MILLISECOND'

RESULT:

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

 Example: This example demonstrates the behavior of the DATEDIFF_BIG function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF_BIG(MILLISECOND,
 '12/01/2015','12/30/2015') 
AS 'Difference in MILLISECOND'

RESULT:
Difference in MILLISECOND
————————-
2505600000

The minimum and maximum value that this function can return is: -2,147,483,648 and +2,147,483,647 The minimum and maximum value that this function can return is: -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

ALSO READ

DATEDIFF_BIG Function in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , ,

DATEDIFF_BIG is one of the new function introduced in Sql Server 2016. It gives the difference between the two dates in the units specified by the DatePart parameter and the returned unit is of type bigint. This function like DATEDIFF function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate. The difference between these two functions is the return type. DATEDIFF functions return type is INT, whereas the DATEDIFF_BIG functions return type is BIGINT.

Syntax:

DATEDIFF_BIG ( datepart , startdate , enddate )

[ALSO READ] Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server

The request for this new function was submitted on the Microsoft connect site some time back in 2008 by Erland Sommarskog.

With DATEDIFF function for milliseconds the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years. This is because the return type of the DATEDIFF function is INT and INT datatypes Min and Max value is: -2,147,483,648 to +2,147,483,647. But with DATEDIFF_BIG function the maximum difference is very high as the return type is bigint and it’s Min and Max value is: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Example 1: Basic example

SELECT DATEDIFF_BIG(DAY, GETDATE(), GETDATE()+1) 'DateDiff Big'

RESULT:
DATEDIFF_BIG Basic example

ALSO READ: How to get difference between two dates in Years, Months and days in Sql Server

Example 2: Below example demonstrates how DATEDIFF and DATEDIF_BIG functions behave differently when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF(ms, '2015-12-01', '2015-12-31') 'DateDiff'

RESULT:

Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

SELECT DATEDIFF_BIG(ms, '2015-12-01', '2015-12-31') 'DateDiff'

RESULT:
DATEDIFF_BIG Sql Example

So, if we know that sometime the difference between two dates is going to cross the INT max value then we have to use the DATEDIFF_BIG function

[ALSO READ]:

Live Query Statistics in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , , , , , , , , ,

Live Query Statistics is one of the new feature introduced in Sql Server 2016, it basically provides the real-time live execution plan of an active running query to the Developer/DBA.

This SSMS feature is very helpful in checking the query execution progress for a long running queries, currently we don’t know for a long running queries where it is stuck or which specific operator is taking long time or how much percentage is completed or approximately how much extra duration required to complete it etc. This feature provides a way to know all these at any given point during the query execution.
It also helps in debugging the queries without needing to wait for the completion of the query execution. As soon as the query execution starts we can see the real-time live execution plan with moving dotted lines between the operators, operator execution progress, overall query execution progress etc.

Like Actual Execution Plan option, this option can be enabled before the query execution starts, it can’t be enabled when the query execution is in progress.

Like Actual execution plan, it will have some additional performance overhead on the overall query execution. So, enable only when it is required to troubleshoot or debug the query issues. Because of this avoid enabling the live query statistics on all the session, even though we have an option to enable it for all the sessions using an extended event query_post_execution_showplan.

[ALSO READ] Compare Execution Plans in Sql Server 2016

Live query statistics feature in Sql Server 2016 provides various real-time query execution statistics like the below ones

  • Overall Query Execution Progress:

    It provides the overall query execution progress
    We can see the moving dotted-lines between the operators where execution is not yet completed. And the dotted-lines turns to solid lines once the operator/complete query execution completes. It is somewhat similar to the one we see in the SSIS data flow.

  • At operator level it provides the real-time insights like the below ones:
    • Operator execution progress
    • Operator execution elapsed time

    • In the operator property window we can see real-time Actual number of rows, Operator Status, Elapsed time and various estimated values when operator execution is in progress like Estimated Number of rows, Estimated CPU Cost, Estimated I/O cost, Estimated Row Size etc. To open the operator property window, right-clicking on any operator and select the Properties option from the context menu.

Note: This article is based on the Sql Server 2016 CTP 3.1. Some of the views mentioned in this article may change in the RTM. Will try my best to keep this article updated

To understand this feature with examples let us create a demo Database with Customer Table as shown in the following image with 200,000 records by the following script.

--Create a demo database
CREATE DATABASE SqlHintsLQSDemo
GO
USE SqlHintsLQSDemo
GO
--Create customer table
CREATE TABLE dbo.Customer( 
    CustomerId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50),  PhoneNumber NVARCHAR(20),
    CreationDate DATETIME, ChangeDate DATETIME)
--Populate customer table with sample 200000 records
INSERT INTO dbo.Customer(Name, CreationDate, ChangeDate)
SELECT TOP 200000 NEWID(), GETDATE(), GETDATE()
FROM SYS.all_columns SC1
        CROSS JOIN SYS.all_columns SC2
GO

To get the live query execution plan, just like Actual Execution plan option we need to select the Include Live Query Statistics option as shown in the below image before executing the query

Include Live Query Statistics

Let us execute the following query and observe it’s live execution plan in the following gif animation

SELECT  TOP 200000 *
FROM dbo.Customer C1 WITH(NOLOCK) 
		INNER JOIN dbo.Customer O with(Nolock)
			ON C1.CustomerId = O.CustomerId

RESULT:
Live Query Statistics Example 1
*Double click on the image to get the enlarged view

Let us update the Customer table statistics by the following statement. This statement is updating the Customer table stats that this table has only one record.

UPDATE STATISTICS dbo.Customer
WITH ROWCOUNT = 1

Now re-execute the previous query and observer it’s live execution plan.

SELECT  TOP 200000 *
FROM dbo.Customer C1 WITH(NOLOCK) 
		INNER JOIN dbo.Customer O with(Nolock)
			ON C1.CustomerId = O.CustomerId
OPTION(RECOMPILE)

RESULT:
Sql Live Query Statistics Example 2

We can observe from this live execution plan that, all operators from the beginning are showing 99% completed, but in the previous example this % done was gradually increased from 0 to 100. So we can assume that some of the stats displayed in the live query statistics indirectly depends on the statistics which sql server maintains for the table, indexes etc

At this moment the live query statistics is not supported for the Natively Compiled Stored Procedure.

[ALSO READ]: