Wednesday, November 28, 2012

DATEFROMPARTS function in SQL Server 2012


One of the new data functions in SQL Server 2012 is the DATEFROMPARTS function. This function will give you the date when you will add the parameters to it.

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Here is an example:

DECLARE @years TABLE (
 [Year] INT);INSERT @years([Year])SELECT 2010 AS Year 
UNION ALL 
SELECT 2011 
UNION ALL 
SELECT 2012;
SELECT [FirstDayOfYear] = DATEFROMPARTS(t1.[Year],1,1)
,      [LastDayOfYear]  = DATEFROMPARTS(t1.[Year],12,31)FROM @years y;

SELECT [FirstDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Year] * 10000) + 101)))
,      [LastDayOfYear]  = CONVERT(DATE,CONVERT(CHAR(8),((y.[Year] * 10000) + 1231)))FROM @years y;

In this example you could see how much more easier it is to get the First day of the Year and the last day of the year with the usage of the function DATEFROMPARTS.

Thursday, November 15, 2012

My new index scripts for SQL Server 2008

Please be free to try out my new script concerning indexes in SQL Server 2008 and above. These scripts won't work in SQL Server 2005 environment.


http://www.4shared.com/document/SRceeYh1/sp_SQL2008_helpindex.html?

This procedure will show you everything you want to know about the indexes in your database.

http://www.4shared.com/document/9QiLKdan/sp_SQL2008_finddupes.html?

This procedure will show you the duplicates within your database.

Both can be used with a parameter to define the table or view you want to use.

Example:

exec sp_sql2008_helpindex

exec sp_sql2008_helpindex 'tablename'


exec sp_sql2008_finddupes

exec sp_sql2008_finddupes 'tablename'



Please leave some comments if you find a bug in one of these two procedures.