Tag Archives: T-Sql

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)


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


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

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

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


Format() function in SQL Server 2012

SQL Server 2012 introduces this new function called FORMAT which returns a value in the specified format and also can optionally apply a regional format. This function relies on the .NET Framework.

In this article we will explore this function and see how it can be implemented.

FORMAT() accepts 3 parameters. The first parameter is the VALUE parameter where we pass on the date value or a numeric value. The second parameter is the .NET Framework format string. The format parameter is case sensitive. The third parameter is the culture. This can be any culture supported by the .NET Framework.

Let us now explore this function with some examples:
1) Display the date using Bengali Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Bn-IN' )
 AS DateInBangla;

format12) Display the date using Tamil Culture

DECLARE @date DATETIME = '12/04/2013';
SELECT FORMAT ( @date, 'MMMM dddd dd yyyy', 'Ta-IN' ) AS

format23) Display an amount into different currency structures

SELECT FORMAT(100, 'C', 'en-GB') AS Pounds,
FORMAT(100, 'C', 'en-US') AS Dollars,
FORMAT(100, 'C', 'es-ES') AS Euro,
FORMAT(100, 'C', 'en-IN') AS Rs;

format34) Display current date time in US format

SELECT FORMAT(GETDATE(), N'"Time now is "dddd MMMM dd, yyyy', 'en-US')
AS USTimeStamp;

format4For more details on this function please refer to this msdn link.

Script to find the size of all Indexes in a database

Continuing with my interest with Indexes, i wrote this script that finds the size of all indexes in a database along with the table and the filegroup on which the index resides. I have used the AdventureWorks2012 database as an example. Please replace the DB name for which you want to find the information.

USE AdventureWorks2012
IF OBJECT_ID('tempdb..#Indexdata', 'U') IS NOT NULL
DROP TABLE #Indexdata

@SizeofIndex BIGINT, @IndexID INT,
@NameOfIndex nvarchar(200),@TypeOfIndex nvarchar(50),
@ObjectID INT,@IsPrimaryKey INT,
@FGroup VARCHAR(20)

create table #Indexdata (name nvarchar(50),
IndexID int, IndexName nvarchar(200),
SizeOfIndex int, IndexType nvarchar(50),
IsPrimaryKey INT,FGroup VARCHAR(20))
SELECT idx.object_id, idx.index_id, idx.name, idx.type_desc
FROM sys.indexes idx
join sys.objects so
on idx.object_id = so.object_id JOIN sys.filegroups fg
ON idx.data_space_id = fg.data_space_id
where idx.type_desc != 'Heap'
and so.type_desc not in ('INTERNAL_TABLE','SYSTEM_TABLE')

OPEN Indexloop

INTO @ObjectID, @IndexID, @NameOfIndex,

SELECT @SizeofIndex = sum(avg_record_size_in_bytes * record_count)
FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID,
@IndexID, NULL, 'detailed')

insert into #Indexdata(name, IndexID, IndexName, SizeOfIndex,
SELECT TableName = OBJECT_NAME(@ObjectID),
IndexID = @IndexID,
IndexName = @NameOfIndex,
SizeOfIndex = CONVERT(DECIMAL(16,1),(@SizeofIndex/(1024.0 * 1024))),
IndexType = @TypeOfIndex,
IsPrimaryKey = @IsPrimaryKey,
FGroup = @FGroup

INTO @ObjectID, @IndexID, @NameOfIndex,
CLOSE Indexloop

select name as TableName, IndexName, IndexType,
SizeOfIndex AS [Size of index(MB)],
case when IsPrimaryKey = 1 then 'Yes' else 'No' End as [IsPrimaryKey]
,FGroup AS [File Group]
from #Indexdata order by SizeOfIndex DESC

Snapshot of the output below:

Find Top 5 executed queries ordered by execution count

I wrote this query that helps us find executed queries with most number of execution counts. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 SQLtxt.text AS 'SQL',
AS 'Execution Count',
qstats.total_logical_writes/DATEDIFF(second, qstats.creation_time,
GetDate()) AS 'Logical Writes Per Second',
qstats.total_physical_reads AS [Total Physical Reads],
qstats.total_worker_time/qstats.execution_count AS [Average WorkerTime],
qstats.total_worker_time AS 'Total Worker Time',
DATEDIFF(hour, qstats.creation_time,
GetDate()) AS 'TimeInCache in Hours'
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS SQLtxt
WHERE SQLtxt.dbid = db_id()
ORDER BY qstats.execution_count DESC

%d bloggers like this: