Best Practices for Date/Time Calculations in SQL Server

Arithmetic Operations on Time Portion of Date/Time DataSo far this article has discussed the arithmetic operations on the date portion of data/time values. So when you try to run SELECT GETDATE() + 1, the second addend is implicitly considered the number of days that have to be added to the value returned by the GETDATE() function. But what if you want to add one hour or one minute to the current datetime value? Using the SQL Server DATEADD() function you can do that very easily, but using addition or substraction operators will be tricky.
Let's examine how to do that. One day consists of 24 hours or 1,440 minutes or 86,400 seconds. So if you run the following statement:
SELECT GETDATE() + 1/24
You probably will get the current datetime plus one hour. In reallity, however, you cannot use just 1/24 because both dividend and divisor are integers, and the result of dividing them will be zero. You need to convert the integers to decimal or float data types as follows in order to get the correct result:
-- How to add one hour
---------------------------------------
SELECT GETDATE()
SELECT DATEADD(hh, 1, GETDATE())
SELECT GETDATE () + CAST(1 AS dec(9,4))/ CAST (24 AS dec(9,4))
SELECT GETDATE () + CAST(1 AS dec)/ CAST (24 AS dec)
SELECT GETDATE () + CAST (1 AS float)/ CAST (24 AS float)
Results:
2007-03-25 20:31:13.870
2007-03-25 21:31:13.870
2007-03-25 21:31:13.867
2007-03-25 21:31:13.870
2007-03-25 21:31:13.870
-- How to add one minute
---------------------------------------
SELECT GETDATE()
SELECT DATEADD(mi, 1, GETDATE())
SELECT GETDATE () + CAST(1 AS dec(9,4))/ CAST (1440 AS dec(9,4))
SELECT GETDATE () + CAST(1 AS dec(18,9))/ CAST (1440 AS dec(18,9))
SELECT GETDATE () + CAST (1 AS float)/ CAST (1440 AS float)
Results:
2007-03-25 20:35:15.127
2007-03-25 20:36:15.127
2007-03-25 20:36:15.123
2007-03-25 20:36:15.127
2007-03-25 20:36:15.127
-- How to add one second
---------------------------------------
SELECT GETDATE()
SELECT DATEADD(ss, 1, GETDATE())
SELECT GETDATE () + CAST(1 AS dec(9,4))/ CAST (86400 AS dec(9,4))
SELECT GETDATE () + CAST(1 AS dec(18,9))/ CAST (86400 AS dec(18,9))
SELECT GETDATE () + CAST(1 AS dec(24,18))/ CAST (86400 AS dec(24,18))
SELECT GETDATE () + CAST (1 AS float)/ CAST (86400 AS float)
Results:
2007-03-25 20:42:26.617
2007-03-25 20:42:27.617
2007-03-25 20:42:27.613
2007-03-25 20:42:27.613
2007-03-25 20:42:27.613
2007-03-25 20:42:27.617
-- How to add one second, using variables
------------------------------------------
DECLARE @dec1 dec(24,18), @dec2 dec(24,18), @dec3 dec(24,18), @dt datetime
SELECT @dec1 = 1, @dec2 = 86400, @dt = GETDATE();
SELECT @dec3 = @dec1 / @dec2;
SELECT @dt
SELECT DATEADD(ss, 1, @dt)
SELECT @dt + @dec3
SELECT @dt + CAST (1 AS float)/ CAST (86400 AS float)
Results:
2007-03-25 20:49:16.817
2007-03-25 20:49:17.817
2007-03-25 20:49:17.813
2007-03-25 20:49:17.817
As you can see from the last example, the SQL Server function DATEADD() works perfectly, but an addition operator may cause a problem. For example, when you try to add one hour or one minute, you need to find a sufficient precision for decimal data type. Otherwise, the result will be slightly inaccurate. However, when you try to add one second, applying an addition operator and decimal conversion, you won't be able to get the exact result at all.
On the other hand, the float conversion looks precise and safe for the time calculations with an addition operator, but if you start to use it you may run into a problem: duplicates and missing values. To illustrate and understand the problem, create and populate an auxillary table:
SET NOCOUNT ON;
DECLARE @max int, @cnt int;
SELECT @cnt = 10000;
IF EXISTS(SELECT * FROM sysobjects
WHERE ID = (OBJECT_ID('sequence')) AND xtype = 'U')
DROP TABLE sequence;
CREATE TABLE sequence(num int NOT NULL);
INSERT INTO sequence VALUES(1);
SELECT @max = 1;
WHILE(@max <= @cnt)
BEGIN
INSERT INTO sequence
SELECT @max + num FROM sequence;
SELECT @max = MAX(num) FROM sequence;
END
When you run this script, it will insert 16,384 sequential numbers into the table sequence. (The number 16,384 doesn't have any special meaning. It was selected for illustration purposes only.)
Now, generate the sequence of hours using the auxillary table and SQL Server's date/time function as follows:
IF EXISTS(SELECT * FROM sysobjects
WHERE id = OBJECT_ID('test'))
DROP TABLE test;
SELECT num, DATEADD(hh, num, 'Dec 31, 2006 23:00:00') dt
INTO test
FROM sequence;
SELECT * FROM test;
Results:
num dt
----------- -----------------------
1 2007-01-01 00:00:00.000
2 2007-01-01 01:00:00.000
3 2007-01-01 02:00:00.000
. . . . . . . . . . . . . . . . . .
3099 2007-05-10 02:00:00.000
3100 2007-05-10 03:00:00.000
. . . . . . . . . . . . . . . . . .
16381 2008-11-13 12:00:00.000
16382 2008-11-13 13:00:00.000
16383 2008-11-13 14:00:00.000
16384 2008-11-13 15:00:00.000
The function DATEADD() works as expected and generates a sequence of datetime values with one-hour intervals.
Now, try to roll up the sequence you just generated using the same the SQL Server DATEADD() function:
SELECT DISTINCT DATEADD(hh, -num, dt) FROM test
Results:
2006-12-31 23:00:00.000
The last result proves that SQL Server's date/time function generates date/time values properly. In order to test the solution that uses an arithmetic operator (+), run the following example:
DECLARE @time float
SELECT @time = CAST(1 as float)/CAST(24 as float)
IF EXISTS(SELECT * FROM sysobjects
WHERE id = OBJECT_ID('test'))
DROP TABLE test;
SELECT num, (CAST('Dec 31, 2006 23:00:00' AS datetime) + @time * num) dt
INTO test
FROM sequence;
SELECT * FROM test;
num dt
----------- -----------------------
1 2007-01-01 00:00:00.000
2 2007-01-01 01:00:00.000
3 2007-01-01 02:00:00.000
4 2007-01-01 03:00:00.000
5 2007-01-01 03:59:59.997
6 2007-01-01 05:00:00.000
7 2007-01-01 05:59:59.997
8 2007-01-01 07:00:00.000
9 2007-01-01 08:00:00.000
10 2007-01-01 08:59:59.997
. . . . . . . . . . . . . . . . . .
16380 2008-11-13 11:00:00.000
16381 2008-11-13 11:59:59.997
16382 2008-11-13 12:59:59.997
16383 2008-11-13 14:00:00.000
16384 2008-11-13 14:59:59.997
You will find that an addition operator produces inaccurate results. Sometimes they differ from the expected ones by 3 ms. If you try to roll up the sequence of generated date/time values, you will get more than one date/time "seed" value (as in the following example) and that is incorrect:
SELECT DISTINCT DATEADD(hh, -num, dt) FROM test
Results:
2006-12-31 22:59:59.997
2006-12-31 23:00:00.000
You may say that +/- 3 ms precision is acceptable for most applications, but look how that seemingly tiny problem can produce a bigger one:
SELECT CONVERT(varchar(100), dt, 100)
FROM test
ORDER BY num
Results:
Jan 1 2007 12:00AM
Jan 1 2007 1:00AM
Jan 1 2007 2:00AM
Jan 1 2007 3:00AM
Jan 1 2007 3:59AM
Jan 1 2007 5:00AM
Jan 1 2007 5:59AM
. . . . . . . . . .
Nov 13 2008 5:59AM
Nov 13 2008 6:59AM
Nov 13 2008 8:00AM
Nov 13 2008 8:59AM
Nov 13 2008 9:59AM
Nov 13 2008 11:00AM
Nov 13 2008 11:59AM
Nov 13 2008 12:59PM
Nov 13 2008 2:00PM
Nov 13 2008 2:59PM
This example uses a CONVERT() function to produce a different date/time format. As a result, inaccuracies in the generated values increased from 3 ms to 1 minute and became unacceptable. However, this is not the only problem. If you try to generate the sequences of minutes or seconds, things become even worse. Look at this example:
DECLARE @time float
SELECT @time = cast(1 as float)/cast(1440 as float)
IF EXISTS(SELECT * FROM sysobjects
WHERE id = OBJECT_ID('test'))
DROP TABLE test;
SELECT num, (CAST('Dec 31, 2006 23:59:00' AS datetime) + @time * num) dt
INTO test
FROM sequence;
SELECT * FROM test;
Results:
num dt
----------- -----------------------
1 2007-01-01 00:00:00.000
2 2007-01-01 00:01:00.000
. . . . . . . . . . . . . . . . . .
1579 2007-01-02 02:17:59.997
1580 2007-01-02 02:19:00.000
1581 2007-01-02 02:19:59.997
. . . . . . . . . . . . . . . . . .
16382 2007-01-12 09:01:00.000
16383 2007-01-12 09:01:59.997
16384 2007-01-12 09:03:00.000
As you can see, there are inaccuracies in the generated values again. In addition, when you try to convert these values to another format as follows, you will get duplicated or missing dates:
SELECT CONVERT(varchar(100), dt, 100)
FROM test
ORDER BY num
Results:
Jan 1 2007 12:00AM
Jan 1 2007 12:01AM
. . . . . . . . . .
Jan 2 2007 12:00AM
Jan 2 2007 12:00AM
Jan 2 2007 12:02AM
Jan 2 2007 12:02AM
Jan 2 2007 12:04AM
Jan 2 2007 12:04AM
. . . . . . . . . .
Jan 12 2007 9:00AM
Jan 12 2007 9:01AM
Jan 12 2007 9:01AM
Jan 12 2007 9:03AM
For instance, there are two values "Jan 02, 2007 12:02AM", but the value "Jan 02, 2007 12:03AM" is missing.
If you want to see the list of all duplicates, you can run the following query:
SELECT COUNT(*), CONVERT(varchar(100), dt, 100)
FROM test
GROUP BY CONVERT(varchar(100), dt, 100)
HAVING COUNT(*) > 1
ORDER BY 2
Finally, you can generate the sequence of seconds using the same approach:
DECLARE @time float
SELECT @time = cast(1 as float)/cast(86400 as float)
IF EXISTS(SELECT * FROM sysobjects
WHERE id = OBJECT_ID('test'))
DROP TABLE test;
SELECT num, (CAST('Dec 31, 2006 23:59:59' AS datetime) + @time * num) dt
INTO test
FROM sequence;
SELECT * FROM test;
Results:
num dt
----------- -----------------------
1 2007-01-01 00:00:00.000
2 2007-01-01 00:00:01.000
3 2007-01-01 00:00:02.000
4 2007-01-01 00:00:03.000
5 2007-01-01 00:00:03.997
6 2007-01-01 00:00:05.000
7 2007-01-01 00:00:06.000
8 2007-01-01 00:00:07.000
9 2007-01-01 00:00:08.000
10 2007-01-01 00:00:08.997
11 2007-01-01 00:00:09.997
. . . . . . . . . . . . . . . . . .
16382 2007-01-01 04:33:00.997
16383 2007-01-01 04:33:02.000
16384 2007-01-01 04:33:03.000
SELECT CONVERT(varchar(100), dt, 120)
FROM test
ORDER BY num
Results:
2007-01-01 00:00:00
2007-01-01 00:00:01
2007-01-01 00:00:02
2007-01-01 00:00:03
2007-01-01 00:00:03
2007-01-01 00:00:05
. . . . . . . . . .
2007-01-01 04:32:55
2007-01-01 04:32:56
2007-01-01 04:32:56
2007-01-01 04:32:58
2007-01-01 04:32:59
2007-01-01 04:33:00
2007-01-01 04:33:00
2007-01-01 04:33:02
2007-01-01 04:33:03
This last example has the same problems as the previous one. In addition, using arithmetic operators for date/time manipulations can lead to other errors, weird results, degradation in performance, and more problems than are discussed here. You can avoid all these problems by using the SQL Server's date/time functions.

Search text in All Stored Procedures in a Database

declare @searchString varchar(100)
Set @searchString = '%' + strSearchText+ '%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK) I
NNER JOIN syscomments SC (NOLOCK)
on SO.Id = SC.ID AND SO.Type = 'P'
AND SC.Text LIKE @searchString
ORDER BY SO.Name

To Defragment All Indexes In A Database

Simple Way To Defragment All Indexes In A Database that Is Fragmented Above A Declared Threshold.

-- Specify your Database Name
USE AdventureWorks;
GO
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(128);
DECLARE @execstr VARCHAR(255);
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables
CURSOR FOR SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal
);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT FROM tables INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;DEALLOCATE tables;-
- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ', ' + RTRIM(@indexid) + ') - fragmentation currently ' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%'; SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'; EXEC (@execstr); FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO

How to find the Index Creation /Rebuild Date in SQL Server

This query which uses STATS_DATE() function to get the STATISTICS updated date. This will not give you accurate result if you are updating STATISTICS explicitly. The logic behind the query is, if you rebuild indexes the STATISTICS are also being updated at the same time. So if you are not explicitly updating STATISTICS using UPDATE STATISTICS tableName command , then this query will give you the correct information
--In SQL Server 2000
Select Name as IndexName, STATS_DATE ( id , indid ) as IndexCreatedDate
From sysindexes
where id=object_id('HumanResources.Employee')
-- In SQL Server 2005
Select Name as IndexName, STATS_DATE ( object_id , index_id ) as IndexCreatedDate
From sys.indexes
where object_id=object_id('HumanResources.Employee')

Performance tuning : Beginner

The slow performance of stored procedures might have a lot of causes, like hardware, server memory, OS, network etc.
Primarily four parameters to monitor.
CPU : The amount of CPU time in milliseconds.
READS : A scalar counter, which shows number of logical reads performed by the server.
WRITES : Number of physical disk writes performed by the server.
DURATION : Amount of time taken to execute the batch or SP.
there are no specific rules to tune SQL statements. The ultimate goal is to reduce time and system resources.

Open the Management Studio and the stored procedure that needed to tune. Before running Profiler, execute the two commands below.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
These two commands clears the machine cache and cleaned the buffers. This allows to get the Profiler readings from scratch.

We should take readings from SET STATISTICS IO ON and SET STATISTICS TIME ON in Management Studio.

• Performance tuning few things:
  i) We should be careful about creating temporary tables, dynamic SQL and joining. Those things are the gold mine for the tuners as eliminating them can make your procedure much faster.
  ii) Be very careful about the functionality of the procedure as your procedure should return the same results after tuning as before.
• iii) It is not enough that SSMS is returning the "Command(s) completed successfully." message. We should check all the tables used in the procedures. Whether they have the same number of rows and whether the rows are contain the same data. These are the kinds of checks we should perform after tuning.
  iv) We always want to see the execution plan for each DML statement. The execution plan is the road layout, based on which the optimizer will decide which path will take less time and resources.
  v) Try and avoid table scans. If the query optimizer chooses a table scan, it will scan the whole table, which is far more time consuming than index scan. If possible, create indexes where table scan is used (though based on the size of the table, optimizer sometimes decide to go for a table scan though there are indexes present for the column, but in this case table should contains a small number of records).
  vi) Always try to write all the SQL statements in ANSI format. Generally, ANSI syntax not reduces the logical Reads, but it is more helpful to understand.

SQL Server tough Interview questions

1. What is the output of the following query? SELECT 1 UNION SELECT NULL UNION SELECT '1'

2. What do you expect the result of the following query to be? No cheating, don't run until you've answered!WITH DATA (Numbers) AS(SELECT NULL UNION ALL SELECT NULL UNION ALLSELECT NULL UNION ALLSELECT 1 UNION ALL SELECT 2 UNION SELECT 3)SELECT COUNT(ALL Numbers) AS NULLNumberCount FROM DATA WHERE Numbers IS NULL

3. You are trying to run some scripts using SQLCMD but keep getting errors that your password has expired. SSMS is not installed on this machine. What should you do?

4. What does sp_validname do?

5. You receive some data from UI and need to populate table Customer if the name received from UI does not exist in database. Which of options below should be used for this purpose:1.IF NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName ) INSERT INTO [Customer] ( [Name]) VALUES ( @CustomerName)2.INSERT INTO [Customer] ( [Name])SELECT @CustomerNameWHERE NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName )

6. What will be the count against each category? Each row in the result is shown as a pair in the answers.CREATE TABLE #CATEGORY(CATID INT ,VAL1 INT)INSERT INTO #CATEGORY VALUES(1,NULL)INSERT INTO #CATEGORY VALUES(2,1)INSERT INTO #CATEGORY VALUES(3,2)CREATE TABLE #DATA(VAL1 INT)INSERT INTO #DATA VALUES(1)INSERT INTO #DATA VALUES(1)INSERT INTO #DATA VALUES(1)INSERT INTO #DATA VALUES(2)INSERT INTO #DATA VALUES(2)INSERT INTO #DATA VALUES(3)SELECT C.CATID, COUNT(*) FROM #DATA D INNER JOIN #CATEGORY C ON C.VAL1 = D.VAL1 OR C.VAL1 IS NULLGROUP BY C.CATID

7. Cursor optimization we all know that cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can't be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.So, suppose you find yourself in a situation where you do need a cursor - which cursor option must be specified in order to assure maximum performance, assuming the cursor fits in cache?

8. You are developing security policy for your SQL Servers and have all of the data entry clerks needing access to a series of tables. You create a Data Entry role and assign the proper permissions as well as add the users.
You then find out that Bob is a part of the HR group because of cross training and needs to use the same objects from the same application except for the Vacation table, to which he should not have access. No column permissions are assigned. What should you do?

9. What is the difference between a WHERE clause and a HAVING clause?

10. A developer is building an application against SQL Server 2000 and comes to see you regarding his design. This portion of the application is providing reporting on a series of clients that have been selected. His design is as follows:
Display a list of clients that can be selected
Loop through the list of clients and call a stored procedure to get data for each client (same data for each
Asemble this information and display a grid to the user To improve performance, what suggestions would you give the developer?

11. I want to append output data from a query into an existing file, which command prompt tool will help me?

12. If I create a database in simple recovery mode in SQL Server 2005, then backup the database, then add 1000 records, then change the recovery mode to full, then add 500 more. Can I recover using the log backups?

13. In SQL Server 2008, what is the maximum number of table sources that you can have in a single SELECT query?

14. What does the CURRENT_TIMESTAMP function return?

SQL Server Memory

Buffer Pool (BPool) Consists of several fragmented regions (up to 32) of address space used by SQL Server. This area is dynamic (the size can change) and is used by SQL Server for many internal processes, such as storing compiled and execution plans, creating indexes, and allocating for cursors.
MemToLeave Consists of a single contiguous region of addresses within the SQL Server process space that is set aside at startup. This area is static (the size cannot change) and is left unallocated by SQL Server for the use by components in the SQL Server process space, such as extended procedures, COM/OLE Automation objects, and linked servers. Memory allocations by SQL Server larger than 8 KB are also made from the MemToLeave area.
Reserved A region of address space that is set aside for future use of a process, and the OS has not used any physical RAM for this allocation.
Committed A region of address space that is currently being used by a process, and the OS has provided either physical RAM and/or paging file space for this allocation.
Allocation The act of giving the memory resource to the consumer.

SQL Server grows and shrinks its memory usage dynamically by committing and de-committing buffers from the buffers reserved at startup. The LazyWriter process is responsible for growing and shrinking the BPool. A committed bitmap array is maintained to track the commit or de-commit of buffers.
Growing the BPool :
When the LazyWriter wakes up, it checks if SQL Server needs more memory. If so, then the LazyWriter checks the committed bitmap to find a buffer with the bit set to off (0 or not committed) and locates the buffer. The buffer is then committed, and finally the bit in the committed bitmap is flipped to on (1 or committed).
Shrinking the BPool:
When the LazyWriter wakes up, it checks if the OS has sufficient memory available to service the memory requests from other applications. If not, then the LazyWriter looks for buffers that can be de-committed. If it finds such a buffer, it is de-committed and the 8 KB page is returned to SQL Server's reserved address space, which can be used in the OS.

DBCC MEMORYSTATUS
The DBCC MEMORYSTATUS command provides a snapshot of the current memory status of SQL Server. The output from this command is useful in troubleshooting issues that relate to the memory consumption of SQL Server or to specific out-of-memory errors