SQL Server Date and Time related Interview Questions

Posted on January 27, 2017 by uday arumilli

SQL Server Date and Time related Interview Questions

This post can help you in answering SQL Server Date and Time related Interview Questions. Below is the list of queries which are recently asked in SQL Server Developer interviews. These are not just for interview preparation also helps you as a quick reference while working on date & time related queries.

Along with these it would be good if you can refer the most commonly used DATETIME data types (DATE, TIME, SMALLDATETIME, DATETIME, DATETIME2, and DATETIMEOFFSET) and functions (GETDATE, SYSDATETIME, CONVERT, DATENAME, DATEPART, DATEDIFF, DATEADD, EOMONTH etc.)

/**********************************************************************/
/*************** SQL Server Date and Time Queries *********************/
/**********************************************************************/

------------------------- Date ------------------------

-- Retireve Current Date and Time
SELECT GETDATE() 'Today'

-- Retrieve Yesterday Date
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'

------------------------- Week ------------------------

-- Get the First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AS 'Current_Week_First_Day'

-- Get the Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) AS 'Current_Week_Last_Day';

-- Get the First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AS 'Last_Week_First_Day';

-- Get the Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) AS 'Last_Week_Last_Day';

------------------------- Month ------------------------

-- Get the First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS 'Current_Month_First_Day';

-- Get the Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) AS 'Current_Month_Last_Day';

-- Get the First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS 'Last_Month_First_Day'

-- Get the Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) AS 'Last_Month_Last_Day'

------------------------- Quarter ------------------------

-- Get the First Day of Current Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0) AS 'Current_Quarter_First_Day'

-- Get the Last Day of Current Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0)) AS 'Current_Quarter_Last_Day'

-- Get the First Day of Last Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0) AS 'Last_Quarter_First_Day'

-- Get the Last Day of Last Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0)) AS 'Last_Quarter_Last_Day'

------------------------- Year ------------------------

-- Get the First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) AS 'Current_Year_First_Day'

-- Get the Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) AS 'Current_Year_Last_Day'

-- Get the First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) AS 'Last_Year_First_Day'

-- Get the Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) AS 'Last_Year_Last_Day'

Logic Explained:

When you are asked to write a query in an interview, you need not give the exact output rather you are expected to give the logic. The person who understands the base logic can easily implement a solution for any given requirement. Now let’s try to understand the logic.

Here ‘0’ indicates the starting date in the data time range. i.e : 1900-01-01 00:00:00.000

SELECT DATEADD (DD, 0, 0);

Now we’ll see how to get the first day of current week:

SELECT DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()),0) AS ‘Current_Week_First_Day’

DATEDIFF (wk, 0, GETDATE()): First we are capturing the total number of weeks between the start date in date range (1900-01-01) and the current date (Based on the GTEDATE). For example today’s date is 24-Jan-17 then the difference will be (SELECT DATEDIFF (wk, 0, ‘2017-01-24’) ; 6108.

DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()), 0): We have calculated the inner value as 6108. Now we simply add the number of weeks to the start date then it fetch the 6108 week starting date. i.e.: SELECT DATEADD (wk, 6108, 0); returns the week start date as “2017-01-23 00:00:00.000”

SQL Server Date and Time related Interview Questions Download the T-SQL script file.

Posted in Interview Q&A, SQL Development, SQL Scripts | Tagged datetime queries, Interview questions, sql script, sql server datetime, T-SQL | 2 Comments

SQL Server Transactions Per Day

Posted on January 8, 2017 by uday arumilli

SQL Server Transactions Per Day

This post can help you in capturing SQL Server Transactions per day. Sometimes we need to capture the average number of transactions per day / hour / minute. Below is the T-SQL script that can help you to capture the SQL Server Transactions per day. Below script returns two result sets:

Retrieves the total transactions occurred in SQL Server Instance since last restart
Database Wise Average Transactions since last restart

T-SQL to capture SQL Server Transactions per day:

/************************************************************/
/******* SQL Server Transactions Per Day / Hour / Min *******/
/******* Tested : SQL Server 2008 R2, 2012, 2014 ************/
/******* Author : udayarumilli.com **************************/
/************************************************************/
DECLARE	@Days	SMALLINT,
		@Hours		INT,
		@Minutes	BIGINT,
		@Restarted_Date DATETIME;

/*** Capture the SQL Server instance last restart date ***/
/*** We will gte the Tempdb creation date ***/
SELECT  @Days = DATEDIFF(D, create_date, GETDATE()),
		@Restarted_Date = create_date
FROM    sys.databases
WHERE   database_id = 2;

/*** Prepare Number of Days and Hours Since the last SQL Server restart ***/
SELECT @Days = CASE WHEN @Days = 0 THEN 1 ELSE @Days END; 
SELECT @Hours = @Days * 24; 
SELECT @Minutes = @Hours * 60; 


/*** Retrieve the total transactions occurred in SQL Server Instance since last restart ***/
SELECT  @Restarted_Date			AS 'Last_Restarted_On',
		@@SERVERNAME		AS 'Instance_Name',
		cntr_value		AS 'Total_Trans_Since_Last_Restart',
		cntr_value / @Days	AS 'Avg_Trans_Per_Day',
		cntr_value / @Hours	AS 'Avg_Trans_Per_Hour',
		cntr_value / @Minutes	AS 'Avg_Trans_Per_Min'
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Transactions/sec'
        AND instance_name = '_Total';


/*** Database Wise Average Transactions since last restart ***/
SELECT  @Restarted_Date			AS 'Last_Restarted_On',
		@@SERVERNAME		AS 'Instance_Name',
		instance_name		AS 'Database_Name',
		cntr_value		AS 'Total_Trans_Since_Last_Restart',
		cntr_value / @Days	AS 'Avg_Trans_Per_Day',
		cntr_value / @Hours	AS 'Avg_Trans_Per_Hour',
		cntr_value / @Minutes	AS 'Avg_Trans_Per_Min'
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Transactions/sec'
        AND instance_name <> '_Total'
ORDER BY cntr_value DESC;

Here is the script file: SQL_Server_Transactions_Per_Day

These are the average values. To get the exact transaction details we can automate the capture process:

Create a table to capture the transaction counts since last restart
Create a stored procedure to capture the transactions since the last restart
Schedule a job to execute the procedure on hourly basis
Write a query to capture the hourly differences

Posted in SQL Development, SQL Scripts, SQL Server DBA | Tagged capacity planning, database hits, per minute, sql, SQL Scripts, sql server, T-SQL, total connections, transaction count, transactions per day, TSQL | 6 Comments

TSQL script to get the index size and information in SQL Server

Posted on January 4, 2017 by uday arumilli

TSQL script to get the index size and information in SQL Server

TSQL script to get the index size and information in SQL Server post can help you in getting the index information from a database in SQL Server. There are two scripts provided:

Script 1: It captures all possible information of all indexes in the given database

Script 2: Captures the table wise number of indexes and total index size on a table

Script 3: Captures the index fragmentation details in the given database

TSQL script to get the index information in SQL Server:

/****************************************************************/
/************** Index Information *******************************/
/****************************************************************/
SELECT  DB_NAME (DB_ID())			AS 'Database_Name'
      ,	OBJECT_SCHEMA_NAME(i.object_id)		AS 'Schema_Name'
      , OBJECT_NAME(i.object_id)		AS 'Object_Name'
      , i.name					AS 'Index_Name'
      , i.is_primary_key			AS 'IsPrimaryKey'
      , i.is_unique				AS 'IsUniqueIndex'
      , c.name					AS 'Column_Name'
      , ic.is_included_column			AS 'IsIncludedColumn'
      , ic.key_ordinal				AS 'Column_Order'
	  , i.type_desc				AS 'Index_Type'
	  , STATS_DATE(i.object_id, i.index_id) AS 'lastupdated'
FROM    sys.indexes i
        INNER JOIN sys.index_columns ic
            ON i.object_id = ic.object_id
               AND i.index_id = ic.index_id
        INNER JOIN sys.columns c
            ON ic.object_id = c.object_id
               AND ic.column_id = c.column_id
        INNER JOIN sys.objects o
            ON i.object_id = o.object_id
WHERE   o.is_ms_shipped = 0 and o.type = 'U'
ORDER BY    OBJECT_SCHEMA_NAME(i.object_id) 
	  , OBJECT_NAME(i.object_id) 
	  , i.is_primary_key DESC
          , i.is_unique DESC
          , i.name 
          , ic.key_ordinal;

TSQL script to get the index size and count in SQL Server:

/****************************************************************/
/************** Table Wise Index Count and Size *****************/
/****************************************************************/
SELECT	Table_Name	AS 'Table_Name',
	COUNT(1)	AS 'Index_Count',
	CAST(SUM(IndexSizeKB)/1024.00 AS DECIMAL(16,2))			AS 'Total_Index_Size_MB',
	CAST(SUM(IndexSizeKB)/(1024.00*1024.00) AS DECIMAL(16,2))	AS 'Total_Index_Size_GB'
FROM(
	SELECT 
		OBJECT_NAME(I.Object_id)	AS 'Table_Name',
		i.[name]			AS 'IndexName' ,
		SUM(s.[used_page_count]) * 8	AS 'IndexSizeKB'
	FROM	sys.dm_db_partition_stats s
		INNER JOIN sys.indexes AS i 
			ON s.[object_id] = i.[object_id]
				AND s.[index_id] = i.[index_id]
		INNER JOIN sys.objects o
			ON i.object_id = o.object_id
	WHERE i.Name IS NOT NULL AND o.is_ms_shipped = 0 and o.type = 'U'
	GROUP BY OBJECT_NAME(I.Object_id),i.[name]
) A
GROUP BY TABLE_NAME
ORDER BY Total_Index_Size_GB DESC;
GO

TSQL script to get the index fragmentation details in SQL Server:

/****************************************************************/
/************** Index Fragmentation Details *********************/
/****************************************************************/
SELECT	object_schema_name(ips.object_id)	AS 'Schema_Name',
	object_name (ips.object_id)		AS 'Object_Name',
	i.name					AS 'Index_Name',
	ips.avg_fragmentation_in_percent	AS 'Avg_Fragmentation%',
	ips.page_count				AS 'Page_Count',
	CASE	WHEN (ips.avg_fragmentation_in_percent BETWEEN 5 AND 30) AND ips.page_count > 1000
			THEN 'Reorganize'
		WHEN ips.avg_fragmentation_in_percent > 30 AND ips.page_count > 1000 
			THEN 'Rebuild'
		ELSE	     'Healthy'
	END AS 'Index_Status'
FROM	sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ips
	INNER JOIN sys.indexes i
		ON i.object_id = ips.object_id 
		   AND i.index_id = ips.index_id
WHERE	ips.index_id > 0
ORDER BY avg_fragmentation_in_percent DESC;

Here is the script file: TSQL script to get the index size and information in SQL Server

Posted in Performance Tuning, SQL Development | Tagged 2012, 2016, fragmentation, index size, indexes, performance tuning, Scripts, SQL Scripts, sql server, SQL Server 2014, T-SQL, TSQL | 1 Comment

Data Meets AI