Wednesday, February 25, 2015

Difference between DateTime2, DateTime and SmallDateTime

Here are the main differences you should know about these three date types:

  Range of Dates

Accuracy

Size

usage Applies to
smalldatetime

January 1, 1900 - June 6, 2079

one Minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

4 bytes

DECLARE @myDate SmallDatetime

SQL 2000 >+
Datetime

January 1, 1753 - December 31, 9999

three-hundredths of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).Values are rounded to increments of .000, .003, or .007 seconds

8 bytes

DECLARE @myDate Datetime

SQL 2000 >+
Datetime2

January 1, 0001 - December 31, 9999

100ns

can take 6-8 Bytes; by default it will take 7 bytes. 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes

DECLARE @myDate   Datetime2(7)

>=SQL 2008

 

 

Armed with this knowledge, you may want to use SmallDateTime instead of DateTime if you only need to represent dates from January 1, 1900 to June 6, 2079 and you do not need accuracy below 1 minute. Why? Simple! Using SmallDateTime will reduce the amount of data your queries are pulling back. The size of each row will be a bit smaller.

Thursday, February 5, 2015

Identifying Unused Databases

This is one of the issues the consultant faces, when there client has little documentation on the systems and the databases they use.  The first step in this process is simply looking at the date & time stamps of the .MDF and .LDF files, which could help in some cases,To evaluate whether it was worth keeping the databases online, I had some investigation to do.
Step 1 : Who is Connected?
The following TSQL summarizessummarises who is connected. It ignores the currently connected user (@@SPID) and system processes (spid's up to 50)

SELECT loginame , nt_username, COUNT(*) AS Connections
FROM sys.sysprocesses
WHERE spid > 50 and spid != @@SPID
GROUP BY loginame , nt_username
ORDER BY COUNT(*) DESC


Step2 : Check data



Although not related to recent use, this gives an idea of size, historic and the table names might help decide if it is worth keeping. You never know, it might even be empty!



SELECT i.[database_id],
DB_NAME(i.[database_id]) AS [Database]
, s.name
AS [SchemaName]
, o.name
AS [TableName]
,
MAX(i.[last_user_lookup]) AS [last_user_lookup]
,
MAX(i.[last_user_scan]) AS [last_user_scan]
,
MAX(i.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.[database_id] = DB_ID()
AND s.name <> 'sys'
GROUP BY i.[database_id], s.name, o.name
ORDER BY i.[database_id], s.name, o.name


Step3 : When were tables last  accessed



Table usage can be gathered from the index usage statistics. This query shows the usage for all user tables in the current database. It excludes the system tables.



SELECT i.[database_id],
DB_NAME(i.[database_id]) AS [Database]
, s.name
AS [SchemaName]
, o.name
AS [TableName]
,
MAX(i.[last_user_lookup]) AS [last_user_lookup]
,
MAX(i.[last_user_scan]) AS [last_user_scan]
,
MAX(i.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.[database_id] = DB_ID()
AND s.name <> 'sys'
GROUP BY i.[database_id], s.name, o.name
ORDER BY i.[database_id], s.name, o.name


Step4 : Check the latest date values on the database



Assuming that there is a datatime  column and is populated periodically, this could give a better picture whether the data has been altered recently.



 



DECLARE @cols TABLE
(
r
INT IDENTITY ,
TableName
VARCHAR(100) ,
ColumnName
VARCHAR(100)
);
DECLARE @i INT ,
@nSQL NVARCHAR(1000);
DECLARE @MaxData TABLE
(
TableName
VARCHAR(100) ,
MaxDate DATETIME2
);

INSERT INTO @cols
SELECT TABLE_NAME ,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ( 'Date', 'datetime', 'datetime2' )
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsSystemTable')=0;

SELECT @i=@@ROWCOUNT;

WHILE @i>0
BEGIN
SELECT @nSQL='SELECT '+''''+TableName+''''+', MAX('+ColumnName
+') FROM '+TableName ,
@i -= 1
FROM @cols
WHERE r=@i;

INSERT INTO @MaxData
( TableName, MaxDate )
EXECUTE ( @nSQL
);
END;

SELECT *
FROM @MaxData
ORDER BY 2 DESC;


 



Step5 : Audit / run Trace



Audit the database logins associated with the database, or run a trace on that database ; Some applications, access the database on some specific times like year end,  end financial period etc. I wont suggest you go and delete the database if all the above steps intended towards deletion. You could take the database offline for few months before you delete it.