Tag Archives: Programming

Using TIMEFROMPARTS() – SQL Server 2012

This new function in SQL server 2012 helps in converting time parts to time. The syntax of this function is as follows

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

The range of the parameters for TIMEFROMPARTS is as follows :
Hour -> 0-23.
Minutes -> 0-59.
Seconds -> 0-59.
Fractions -> 0-9999999.
Precision -> 0-7.

Let us understand this with an example.

DECLARE @hour INT, @min INT,@Sec INT,@frac INT;
SET @hour = 13
SET @min = 24
SET @Sec = 22
SET @frac = 45
SELECT TIMEFROMPARTS(@hour,@min,@Sec,@frac,2)

time1

This function requires a valid value for the Hour,Minute, Seconds, Fractions, Precision parameters. If any invalid value is passed then this function will return an error. If a Null value is passed on for the Precision parameter then it generates an error. For other parameters if a Null is passed then the output is also Null.

For example in the below code we pass an invalid value for the Hour parameter

time2

Now lets see what happens when we pass a Null value to the Fraction parameter

time3
 
 
 
 
 
 
   
   
   
      
If we fail to pass any one of the parameters then the following error message is returned.
time4

Using DATEFROMPARTS() – SQL Server 2012

DATEFROMPARTS() returns a date value for the specified year, month, and day.

Let us understand this with an example.

The SQL code for returning day, month and year parameters as a date would probably look something like this

DECLARE @Day INT = 07, @Month int = 03,@Year INT = 2014
SELECT CONVERT(datetime,CONVERT(varchar(10),@Year) + '-' +
CONVERT(varchar(10),@Month) + '-' +
CONVERT(varchar(10),@Day),103) AS TheDate

datetime1Now lets implement DATEFROMPARTS

Here is the code for the same

DECLARE @Day INT = 07,
@Month int = 03,
@Year INT = 2014
SELECT DATEFROMPARTS (@Year, @Month, @Day) AS TheDate

datetime2

TRY_CONVERT function in SQL Server 2012

TRY_CONVERT is one of the new conversion function introduced in SQL SERVER 2012. It returns a value converted to the specified data type if the conversion succeeds. Otherwise,it returns returns NULL value when it fails to convert to a requested data type. TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted

The syntax for TRY_CONVERT is as follows

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Let us now implement this function and see how it works with different examples

SELECT TRY_CONVERT(xml, 'Manchester United')
SELECT TRY_CONVERT(DATETIME, '02/18/2014 05:30',111)
SELECT TRY_CONVERT(INT, '40')

The output of the above as follows
TryCon1

Now let us see examples where this function might fail or throw an exception

SELECT TRY_CONVERT(xml, 40)

And the output is
TryCon2

SELECT TRY_CONVERT(DATETIME, '22/18/2014 05:30',111)

And the output is
TryCon3The first error is self explanatory. The second code gives an output of NULL because the date is an invalid date.

Now the question is how is TRY_CONVERT different from CONVERT function?

Lets understand with this example

SELECT CONVERT(DATETIME, 'ABC')

TryCon4CONVERT will give an error message stating the conversion failed.

SELECT TRY_CONVERT(DATETIME, 'ABC')
AS 'Result'

TryCon5TRY_CONVERT will give a NULL.

 

Pagination – Using OFFSET and FETCH in SQL Server 2012

OFFSET and FETCH are two new clauses introduced in SQL Server 2012 that allows us to extract a portion of rows from the result set. When we need to display a large result set to the user, the best way of going about it is to split them .i.e use pagination. In SQL Server 2012, we can achieve pagination by using the ‘OFFSET’ and ‘FETCH’ commands. Let us understand this with an example:

USE AdventureWorks2012
GO
SELECT pp.ProductId,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.productid

OandF1
The query returns 504 rows as depicted above. So what do i do if i need to fetch only a portion of the above rows?

Enter OFFSET and FETCH. How? let us understand this with an example

USE AdventureWorks2012
GO
SELECT PP.ProductID,PP.Name,pp.ProductNumber,
pp.DaysToManufacture,pp.ListPrice
FROM Production.Product PP
ORDER BY pp.ProductID
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY

OandF2Here, the OFFSET tells the query to ignore the first 100 rows and then return only the following 10 rows. This is very easy to use and a quick way to return just a portion of records.
Few Limitations of the OFFSET and FETCH clause as stated in msdn:
a) ORDER BY is mandatory to use OFFSET and FETCH clause.
b) OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
c) TOP cannot be combined with OFFSET and FETCH in the same query expression.
d) The OFFSET/FETCH row count expression can be any arithmetic, constant, or parameter expression that will return an integer value. The row count expression does not support scalar sub-queries.

IIF – The InLine conditional Statement in SQL Server 2012

IIF is a new inline conditional statement in SQL Server. We can pass an expression that can be evaluated to either true or false to the function and it returns one value for true and another one for false.

Let us understand this with an example:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Arsenal'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

The output of the above as below. In this we have passed two different values.
IIF1Now let us pass two same values and see how this works:

DECLARE @MyTeam nvarchar(40) = 'Manchester United'
DECLARE @YourTeam nvarchar(40) = 'Manchester United'
SELECT IIF (@MyTeam = @YourTeam, 'True Devils',
'False Gooners') AS YouAre

IIF2
IIF is very useful where a straightforward comparison has to be made without writing case statements.

How to find the last execution details of a stored procedure in SQL Server

In this article I will demonstrate how we can find the last execution details of a stored procedure in SQL Server 2012 or SQL Server 2008 R2.With DMV’s getting modified in these 2 editions of SQL Server, the amount of information we can obtain from the plan cache can be handy for investigation purpose.
When a stored procedure is created or recompiled, a plan for the same is created and cached in the plan cache. Whenever the same stored procedure is executed, the plan is recalled from the SQL memory for execution purpose. The details of an execution is stored internally in SQL Server which can be fetched via the dynamic management views.

A key element in this process is that the plan has to be in the plan cache for us to derive the information. By any chance if the SQL Server gets restarted or the plan cache is cleared then the information would not be available.

Let us see how we can fetch the execution details with an example. I have used the Adventureworks2012 database for demonstration purpose
SQL Server 2012:
Execute the below mentioned SP on SQL Server 2012

USE [AdventureWorks2012]
GO
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 135

The execution produces 4 rows
ED1Now let us find the execution details of this stored procedure from the plan cache. Open another Query editor and execute the below mentioned query. The query searches the plan cache for the execution details of the stored procedure, whose name we have filtered in the last line of the query

SELECT qs.sql_handle,qs.creation_time,qs.last_execution_time,
qp.dbid,qs.execution_count,qs.last_rows,qs.last_logical_reads,
qs.last_logical_writes,qs.last_physical_reads,st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT) AS qp
WHERE st.text like '%USPGET%'---filter by name of the SP

The output would be as follows:
ED2Now if we observe the outcome we would find the following information very handy
1. Last execution Time
2. Execution Count
3. Last_rows: This depicts the number of rows as output when the SP executed last.
4. Last_Logical_Reads
5. Last_Logical_Writes
6. Last_Physical_Reads

In SQL Server 2008 R2, the column that would be missing is Last_rows. So the query that you can use in SQL Server 2008 R2 would be as below

SELECT qs.sql_handle,qs.creation_time,
qs.last_execution_time,
qp.dbid,qs.execution_count,qs.last_logical_reads,
qs.last_logical_writes,qs.last_physical_reads,st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY
sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%Name of Stored Proc%'
%d bloggers like this: