Retrieving password policy settings for SQL login accounts

Today, I wrote the following query for our internal audit report for SAS70. This query provides all the necessary details about SQL Logins policy settings.

This query is using LOGINPROPERTY function to retrieve the sql login policy settings information:

USE [master]
GO

DECLARE @PwdExpirationAge [int]

SET @PwdExpirationAge = 28

SELECT [name] AS [SQL_User]
	,LOGINPROPERTY([name], 'PasswordLastSetTime') AS [PasswordLastResetDT]
	,@PwdExpirationAge - DATEDIFF(DAY, CONVERT([datetime], LOGINPROPERTY([name], 'PasswordLastSetTime')), GETDATE()) AS [DaysUntilExpiration]
	,LOGINPROPERTY([name], 'BadPasswordCount') AS [BadPasswordCount]
	,LOGINPROPERTY([name], 'BadPasswordTime') AS [BadPasswordDT]
	,LOGINPROPERTY([name], 'HistoryLength') AS [HistoryLength]
	,LOGINPROPERTY([name], 'IsExpired') AS [IsExpired]
	,LOGINPROPERTY([name], 'IsLocked') AS [IsLocked]
	,LOGINPROPERTY([name], 'IsMustChange') AS [IsMustChange]
	,LOGINPROPERTY([name], 'LockoutTime') AS [LockoutTime]
FROM [sys].[sql_logins]
GROUP BY [name];
GO

I hope you will find this query useful.

Useful new DMV’s in SQL Server 2008 R2 SP1 and SQL Server 2012

Introduction

Microsoft SQL Server 2008 R2 and SP1 and SQL Server 2012 has a new set of DMVs that includes sys.dm_server_memory_dumps, sys.dm_server_registry, sys.dm_server_services, sys.dm_os_windows_info, sys.dm_os_volume_stats and sys.dm_exec_query_stats. These DMVs can be used to return the information about SQL Server configuration and installation, memory dumps related information and information that can be useful to diagnose problems and tune query performance.

In this article, I’ll cover the purpose and use of these new DMVs of SQL Server 2008 R2 SP1 and SQL Server 2012 (DENALI):

1) sys.dm_server_memory_dumps – It contains the information about memory dumps files generated by the SQL Server Database Engine. It returns 3 columns listed as below:

Column Name Data type Description Attribute
filename nvarchar(256) Memory dump file name and path. NOT NULL
creation_time datetimeoffset(7) Date and time the file was created. NOT NULL
size_in_bytes bigint Size of the memory dump file in bytes. NULL

Usage:

USE [master]
GO

SELECT * FROM sys.dm_server_memory_dumps;
GO

Example:

Currently there are no memory dump files are available but if you see any on your SQL Server then alert your SQL Server DBA, because memory dumps can be serious and could lead to data corrouption and other problems.

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://technet.microsoft.com/en-us/library/hh204543.aspx

2) sys.dm_server_registry – This DMV contains the configuration and installation information for the current instance of SQL Server that is stored in Windows registry. This DMV returns one row per registry key. It returns 3 columns listed as below:

Column Name Data type Description Attribute
registry_key nvarchar(256) Stores name of registry key. NULL
value_name nvarchar(256) Stores the name of key value. In Windows Registry Editor this item is shown under Name column. NULL
value_data sql_variant Stores value of the key data. In Windows Registry Editor this item is shown under Data column. NULL

In old version of SQL Server you can access the Windows registry information via xp..regread. This DMV is use full if you want to audit the configuration of your SQL Server.

USE [master]
GO

SELECT * FROM sys.dm_server_registry;
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/hh204561.aspx

3) sys.dm_server_services – The initial version of this DMV that was released in SQL Server 2008 R2 SP1 only returns information about SQL Server and SQL Agent service. In SQL Server 2012 it also returns information about Full-text service.

This DMV returns one row per service. It returns 11 columns listed as below:

Column Name Data type Description Attribute
servicename nvarchar(256) Stores name of the SQL Server, Full-text or SQL Server Agent service. NOT NULL
startup_type int Indicates start mode of the SQL Server, Full-text or SQL Server Agent service.

Value Description
0 Other (boot start)
1 Other (system start)
2 Automatic
3 Manual
4 Disabled
NULL
startup_desc nvarchar(256) Describe the start mode of the SQL Server, Full-text or SQL Server Agent service.

Value Description
Other Other (boot start)
Other Other (system start)
Automatic Auto start
Manual Demand start
Disabled Disabled
NOT NULL
status int Indicates the existing status of the SQL Server, Full-text and SQL Agent service.

Value Description
1 Stopped
2 Other (start pending)
3 Other (stop pending)
4 Running
5 Other (continue pending)
6 Other (pause pending)
7 Paused

 

NULL
status_desc nvarchar(256) Describe the existing status of the SQL Server, Full-text and SQL Agent service.

Value Description
Stopped Service is currently stopped.
Other (start pending) Service is in the process of starting.
Other (stop pending) Service is in the process of stopping.
Running Service is currently running.
Other (continue pending) Service is in pending state.
Other (pause pending) Service is in the process of pausing.
Paused Service is currently paused.

 

NOT NULL
process_id int Stores the process id of the SQL Server, Full-text and SQL Agent service. NOT NULL
last_startup_time datetimeoffset(7) Stores the date and time when the SQL Server, Full-text and SQL Agent service was last started. NULL
service­_account nvarchar(256) Stores the account name of authorized account to control the service. NOT NULL
filename nvarchar(256) Stores the full OS path of service executable. NOT NULL
is_clustered nvarchar(1) Indicates whether the service is installed as a resource of the cluster server. NOT NULL
cluster_nodename nvarchar(256) Stores the names of the cluster nodes. NULL

In old versions of SQL Server you can access the same information by accessing the Windows registry information for the service via xp..regread. This DMV is useful if you want to audit the what services are installed as part of the SQL installation.

Usage:

USE [master]
GO

SELECT * FROM sys.dm_server_services;
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/hh204542.aspx

4) sys.dm_os_windows_info – The DMV returns detailed information about Windows OS version for example the release number, service pack level and language version of SQL Server Windows OS. This DMV returns only one row with four columns listed as below:

Column Name Data type Description Attribute
windows_release nvarchar(256) Stores the Windows OS release number. NOT NULL
windows_service_pack_level nvarchar(256) Store the service pack level of Windows operating system. NOT NULL
Windows_sku int Stores ID of Windows Socket Keeping Unit (SKU) NULL
os_language_version int Stores Windows locale identifier (LCID) of the operating system. NOT NULL

In old versions of SQL Server you can access the same information by accessing the Windows registry information for the OS via xp..regread.

Usage:

USE [master]
GO

SELECT * FROM sys.dm_os_windows_info;
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/hh204565.aspx

5) sys.dm_os_volume_stats – The DMV returns Windows OS volume information on which the database files resides. It takes two parameters i.e. database_id (ID of the database) and file_id (ID of the database file). This DMV returns 13 columns listed as below:

Column Name Data type Description Attribute
database_id int ID of the database. NOT NULL
file_id int ID of the file. NOT NULL
volume_mount_point nvarchar(512) Mount point at which the volume is rooted. Can return an empty string. NOT NULL
volume_id nvarchar(512) Operating system volume ID. Can return an empty string NOT NULL
logical_volume_name nvarchar(512) Logical volume name. Can return an empty string NOT NULL
file_system_type nvarchar(512) Type of file system volume (for example, NTFS, FAT, RAW). Can return an empty string NOT NULL
total_bytes bigint Total size of the volume in bytes. NOT NULL
available_bytes bigint Available free space on the volume in bytes. NOT NULL
supports_compression bit Indicates if the volume supports operating system compression. NOT NULL
supports_alternate_streams bit Indicates if the volume supports alternate streams. NOT NULL
supports_sparse_files bit Indicates if the volume supports sparse files. NOT NULL
is_read_only bit Indicates if the volume is currently marked as read only. NOT NULL
is_compressed bit Indicates if this volume is currently compressed. NOT NULL

In old versions of SQL Server you can access the same information by writing your own CLR procedure by using one of the .NET framework languages.

Usage:

USE [master]
GO

SELECT * FROM sys.dm_os_volume_stats (database_id, file_id);
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/hh223223.aspx

6) sys.dm_exec_query_stats – This existing DMV has new columns added that return very useful information. These new columns are listed as below:

  • total_rows – Returns the total number of rows returned by the query
  • last_rows – Returns the total number of rows returned by last execution of the query.
  • min_rows – Returns the minimum number of rows query returned over the number of times that the plan has executed since it was last compiled.
  • max_rows – Returns the maximum number of rows query returned over the number of times that the plan has executed since it was last compiled.

Usage:

USE [master]
GO

SELECT * FROM sys.dm_exec_query_stats;
GO

Example:

Permissions:

Requires VIEW SERVER STATE permission on the server.

MSDN reference: http://msdn.microsoft.com/en-us/library/ms189741.aspx

Conclusion

In this article, I’ve covered the new useful DMVs of SQL Server 2008 R2 SP1 and SQL Server 2012 (DENALI). These DMVs help us to manage our SQL Servers efficiently by returning some very useful information. It is difficult to retrieve similar kind of information in older versions of SQL Servers and one need to write lengthy bespoke code to retrieve the similar information returned by these DMVs. I’ve already written some SSRS queries by using these DMVs which details the useful information which our companies auditors needs to audit our our SQL Servers.

Using SQL Server 2012 FileTables

SQL Server 2012 allows you to store file/directories in a special table called FileTable that builds on top of SQL Server FILESTREAM technology. As per Microsoft BOL, “FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.”

FileTable has a fixed schema and each row of this table represents a file or a directory. The main advantage of FileTable is that it supports Win32APIs for file or directory management which mean we can access file and directory hierarchy through a Windows Share and database storage is transparent to Win32 application. Files can be bulk loaded, updated as well as managed in T-SQL like any other column. SQL Server also supports backup and restore job for this feature.

In this tip we will take a look at how to use FileTable feature of SQL Server 2012.

To use FileTable feature, execute the following query to verify that FILESTREAM is enabled at the instance level:

Note: 0 in value_in_use column indicates that FILESTREAM support s disabled for that instance, 1 indicates that FILESTREAM is available for Transact-SQL access and 2 indicates that the FILESTREAM access is enabled both for Transact SQL access and Win32 streaming access.

You can enable FILESTREAM feature at the instance level if it is disabled on your instance by following the instructions listed in the step 1 below:

Step-1: Enable FILESTREAM at the instance level

Open SQL Server Configuration Manager, Right click SQL Server service and choose Properties, Click on FILESTREAM tab and then tick Enable FILESTREAM for Transact-SQL access, Enable FILESTREAM for file I/O access, specify Windows Share name, and tick Allow remote clients access to FILESTREAM data as shown below:

Now open SQL Server Management Studio, click New Query to display the Query Editor. In Query Editor, enter the following Transact-SQL code:

<br />USE [master]<br />GO<br />-- Enabling Filestream<br />EXEC sp_configure filestream_access_level, 2<br />RECONFIGURE<br />

Click Execute and then restart the SQL Server service.

Microsoft Reference: Enable the FILESTREAM at instance level

MSDN – http://msdn.microsoft.com/en-us/library/cc645923.aspx

Step 2: Create Database with FILESTREAM enabled
Now executed the following Transact-SQL code to create a database with FILESTREAM File group, specify folder location for FILESTREAM folder and enable non-transaction access at the database level as follow:

<br />USE [master]<br />GO<br /><br />CREATE DATABASE SQLDocumentStoreDB ON PRIMARY (<br />	NAME = SQLDocumentStoreDB<br />	,FILENAME = 'D:\Program Files\Microsoft SQL Server\DBData\SQLDocumentStoreDB.mdf'<br />	)<br />	,FILEGROUP FileStreamFG CONTAINS FILESTREAM (<br />	NAME = SQLDocumentStoreFileTable<br />	,FILENAME = 'D:\FileTable_DocumentStore'<br />	) --Folder location<br />	LOG ON (<br />	NAME = SQLDocumentStoreDB_Log<br />	,FILENAME = 'D:\Program Files\Microsoft SQL Server\DBLogs\SQLDocumentStoreDB_Log.ldf'<br />	)<br />	WITH FILESTREAM<br />		--Gives full non-transactional access to the share/ directory<br />		(NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'DocumentStore');<br />GO<br />

Step 3: Create FileTable

Now create a FileTable by executing the following Transact-SQL statement against your FILESTREAM enabled database in SQL Server Management Studio:

<br />USE [SQLDocumentStoreDB]<br />GO<br /><br />CREATE TABLE MyDocuments AS FILETABLE<br />	WITH (<br />		 FileTable_Directory = 'MyDocumentStore'<br />		,FileTable_Collate_Filename = database_default<br />	     );<br />

This can also be accesed at:

<br />\\&lt;ServerName&gt;\&lt;FILESTREAM Share Name&gt;\&lt;DirectoryName&gt;\&lt;FileTable Directory&gt;\<br />

For this example share path is:

<br />\\Machine-LT01\DEV01\DocumentStore\MyDocumentStore<br />

Step 4: Examine your newly created FileTable

Examine the FileTable and its structure in Object Explorer:

Now query your FileTable as follow:

<br />USE [SQLDocumentStoreDB]<br />GO<br /><br />SELECT [stream_id]<br />	,[file_stream]<br />	,[name]<br />	,[path_locator]<br />	,[parent_path_locator]<br />	,[file_type]<br />	,[cached_file_size]<br />	,[creation_time]<br />	,[last_write_time]<br />	,[last_access_time]<br />	,[is_directory]<br />	,[is_offline]<br />	,[is_hidden]<br />	,[is_readonly]<br />	,[is_archive]<br />	,[is_system]<br />	,[is_temporary]<br />FROM [dbo].[MyDocuments];<br />GO<br />

It will return no data because the FileTable is empty (see below):

Step 5: Copy files to your FileTable

This can be done by copying files to your FileTable directory. For this example FileTable directory name is MyDocumentStore and the full share path is as follow:

<br />\\Machine-LT01\DEV01\DocumentStore\MyDocumentStore<br />

Step 6: Examine the data again in your FileTable

Its time to query your FileTable again in SQL Server Management Studio as you have just copied files to your FileTable directory:

<br />USE [SQLDocumentStoreDB]<br />GO<br /><br />SELECT [stream_id]<br />	,[file_stream]<br />	,[name]<br />	,[path_locator]<br />	,[parent_path_locator]<br />	,[file_type]<br />	,[cached_file_size]<br />	,[creation_time]<br />	,[last_write_time]<br />	,[last_access_time]<br />	,[is_directory]<br />	,[is_offline]<br />	,[is_hidden]<br />	,[is_readonly]<br />	,[is_archive]<br />	,[is_system]<br />	,[is_temporary]<br />FROM [dbo].[MyDocuments];<br />GO<br />

As you can see this query is now returning the rows related to the files which I’ve just copied to FileTable directory:

Step 7: Explore FileTable directory via SSMS

You can also explore the FileTable directory via SQL Server Management Studio as follow:

Expand Tables > FileTables > Right-click your FileTable > Choose option Explore FileTable directory option as follow:

Step 8: View FILESTREAM Database Options

You can execute the following query to view the FILESTREAM database option as follow:

<br />USE [master]<br />GO<br /><br />SELECT DB_NAME(Database_id) AS [Database]<br />	,[database_id] AS [DatabaseID]<br />	,[non_transacted_access] AS [NonTransactedAccess]<br />	,[non_transacted_access_desc] AS [NonTransactedAccessDesc]<br />	,[directory_name] AS [DirectoryName]<br />FROM [sys].[database_filestream_options]<br />WHERE DB_NAME(Database_id) = &lt; Databse_Name &gt;<br />

For example:

You can also Enable/Disable the FileTable namespace as follow:

<br />USE [&lt;Database_Name&gt;]<br />GO<br /><br />ALTER TABLE &lt; FileTable NAME &gt; DISABLE FILETABLE_NAMESPACE;<br />GO<br />

This will disable all system-defined constraints and Win32 access to FileTable. This is useful when doing bulk-loading or re-organization of your data.

Key information for FileTables

  • DML\DDL triggers are supported on FileTable but DML trigger on a FileTable cannot update any FileTables.
  • Normal Insert/Update/Delete are allowed for the FileTable manipulation.
  • Built-infunctions:
    • GetFileNamespacePath()–UNC path for a file/directory.
    • FileTableRootPath()–UNC path to the FileTable root
    • GetPathlocator()–path_locator value for a file/directory.
  • FileTable can be dropped similar to any other table.
  • Database Backup/Restore operations include FileTable data.
    • Point in time Restore may contain more recent FILESTREAM data due to non-transactional updates during backups.
  • FileTables are secured similar to any other user tables. Also same security is enforced for Win32 access.
  • Windows tools like xcopy/robocopy OR drag-drop operations through WindowsExplorer can be used BCP operations are supported for direct T-SQL data inserts.

Conclusion

FileTable is a cool feature of SQL Server 2012 that allows applications or users to store files/directories inside database. This feature is built on of SQL Server FILESTREAM technology and is very simple to use.

References

SQL Server 2012 Installation Guide

This article walks the user through installation of SQL Server 2012 on a Windows Server 2008 system using the SQL Server setup installation wizard. The installation process is simple, straight forward and is very similar to SQL Server 2008 / 2008 R2 setup. The procedure described is for a non-clustered server, and can be applied either to a default instance or a named instance. It is intended to be read by Database Administrators and anybody interested in the technical aspects of carrying out this task.

Continue reading

New built-in functions in SQL Server 2012

Introduction:

Microsoft SQL Server 2012 Release Candidate 0 introduces 14 new built-in functions. These functions will make the migration path for information workers by equalling functionality that is found in the expression languages.

In this blog post, I will be covering common usage of these functions because they are very useful for SQL developers in one way or other.

These new functions are:

Conversion functions

  • PARSE (Transact-SQL)
  • TRY_PARSE (Transact-SQL)
  • TRY_CONVERT (Transact-SQL)

Date and time functions

  • DATEFROMPARTS (Transact-SQL)
  • DATETIMEFROMPARTS (Transact-SQL)
  • DATETIME2FROMPARTS (Transact-SQL)
  • SMALLDATETIMEFROMPARTS (Transact-SQL)
  • DATETIMEOFFSETFROMPARTS (Transact-SQL)
  • TIMEFROMPARTS (Transact-SQL)
  • EOMONTH (Transact-SQL)

Logical functions

  • CHOOSE (Transact-SQL)
  • IIF (Transact-SQL)

String functions

  • CONCAT (Transact-SQL)
  • FORMAT (Transact-SQL)

Conversion Functions:

1) PARSE (Transact-SQL) – Parse a value and returns the result of an expression, translated to specified data type. It will raise error if translation isn’t possible. This function relies on CLR. It will take some performance overhead. You can only use this function to convert strings to or from date time and numeric values. You may still use CAST or CONVERT for general type of conversions. PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Syntax:

PARSE ( string_value AS data_type [ USING culture ] )

PARSE(), expects three parameters,

  1. string_value – String value to parse into the specified data type.
  2. data_type – Return data type, numeric or datetime type
  3. culture – A language (English, Japanese, Spanish, Danish, French etc.) which will used by SQL Server to interpret data.

Example:

Execute the following to convert string value to date time using CAST, CONVERT and PARSE functions:

SELECT CAST('18/02/2012' AS datetime2) AS [Using CAST Function]
GO

SELECT CONVERT(datetime2, '18/02/2012') AS [Using CONVERT Function]
GO

SELECT PARSE('18/02/2012' AS datetime2 USING 'en-GB') AS [Using PARSE Function]
GO

These commands will execute successfully and will produce exactly same output as below:

Using CAST Function
---------------------------
2012-02-18 00:00:00.0000000

Using CONVERT Function
---------------------------
2012-02-18 00:00:00.0000000

Using PARSE Function
---------------------------
2012-02-18 00:00:00.0000000

Now execute the following queries to see the real use of PARSE():

SELECT CAST('Saturday, 18 February 2012' AS datetime2) AS [Using CAST Function]
GO

SELECT CONVERT(datetime2, 'Saturday, 18 February 2012') AS [Using CONVERT Function]
GO

SELECT PARSE('Saturday, 18 February 2012' AS datetime USING 'en-GB') AS [Using PARSE Function]
GO

As you can see that only PARSE will be able to convert the string value to date time and the first two queries that are using CAST and CONVERT will fail that is shown below:

Using CAST Function
---------------------------
Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

Using CONVERT Function
---------------------------
Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

Using PARSE Function
-----------------------
2012-02-18 00:00:00.000

2) TRY_PARSE (Transact-SQL) – TRY_PARSE() function is similar to PARSE() with only difference is that it returns NULL if the conversion is not possible.

Syntax:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

TRY_PARSE(), expects three parameters,

  1. string_value – String value to parse into the specified data type.
  2. data_type – Return data type, numeric or datetime type
  3. culture – A language (English, Japanese, Spanish, Danish, French etc.) which will used by SQL Server to interpret data.

Example:

Execute the following to convert numeric value to int:

SELECT PARSE('10.20' AS INT) AS [Using PARSE Function]
GO

SELECT TRY_PARSE('10.20' AS INT) AS [Using TRY_PARSE Function]
GO

Here is the result:

Using PARSE Function
--------------------
Msg 9819, Level 16, State 1, Line 4

Error converting string value '10.20' into data type int using culture ''.

Using TRY_PARSE Function
------------------------
NULL

Since 10.20 is not an integer value PARSE() fails, and TRY_PARSE() returns NULL.

3) TRY_CONVERT (Transact-SQL) – Checks whether conversion from one type to other is possible or not. It transforms the source data into the target type if the conversion is possible. Returns NULL if the conversion is not possible.

Syntax:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

TRY_CONVERT(), expects three parameters,

  1. data_type – The data type into which to cast expression.
  2. expression – The value to be cast.
  3. style – Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

Example:

Execute the following:

SET DATEFORMAT DMY;
GO

SELECT CONVERT(datetime2, '02/18/2012', 103) AS [Using CONVERT Function];
GO

SELECT TRY_CONVERT(datetime2, '02/18/2012', 103) AS [Using TRY_CONVERT Function];
GO

Here is the result set:

Using CONVERT Function
---------------------------
Msg 241, Level 16, State 1, Line 2

Conversion failed when converting date and/or time from character string.

Using TRY_CONVERT Function
---------------------------
NULL

As you can see the both queries fail conversion but only first query that is using COVERT function throws error whereas query that is using TRY_CONVERT returns NULL indicating that conversion fails.

4) Date and time functions

4.1) DATEFROMPARTS – The DATEFROMPARTS function, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default.

Syntax:

DATEFROMPARTS ( year, month, day )

DATEFROMPARTS(), expects three parameters,

  1. Year – Integer expression specifying a year.
  2. Month – Integer expression specifying a month, from 1 to 12.
  3. Day – Integer expression specifying a day.

Example:

Execute the following:

DECLARE  @YEAR INT = 2012
	,@MONTH INT = 2
	,@DAY INT = 18

SELECT DATEFROMPARTS(@YEAR, @MONTH, @DAY) AS [Using DATEFROMPARTS Function]
GO

Here is the result set:

Using DATEFROMPARTS Function
----------------------------
2012-02-18

4.2) DATETIMEFROMPARTS – The DATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified milliseconds.

Syntax:

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

DATETIMEFROMPARTS(), expects seven parameters,

  1. Year – Integer expression specifying a year.
  2. Month – Integer expression specifying a month, from 1 to 12.
  3. Day – Integer expression specifying a day.
  4. Hour – Integer expression specifying hours.
  5. Minute – Integer expression specifying minutes.
  6. Seconds – Integer expression specifying seconds.
  7. Milliseconds – Integer expression specifying milliseconds.

Example:

Execute the following:

DECLARE @YEAR INT = 2012
	,@MONTH INT = 2
	,@DAY INT = 18
	,@HOUR INT = 11
	,@MINUTE INT = 59
	,@SECONDS INT = 59
	,@MILLISECONDS INT = 0

SELECT DATETIMEFROMPARTS(@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS) AS [Using DATETIMEFROMPARTS Function]
GO

Here is the result set:

Using DATETIMEFROMPARTS Function
--------------------------------
2012-02-18 11:59:59.000

4.3) DATETIME2FROMPARTS – The DATETIME2FROMPARTS function, returns full datetime2 value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified precision.

Syntax:

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

DATETIME2FROMPARTS(), expects eight parameters,

  • Year – Integer expression specifying a year.
  • Month – Integer expression specifying a month, from 1 to 12.
  • Day – Integer expression specifying a day.
  • Hour – Integer expression specifying hours.
  • Minute – Integer expression specifying minutes.
  • Seconds – Integer expression specifying seconds.
  • Fractions – Integer expression specifying fractions.
  • Precision – Integer literal specifying the precision of the datetime2 value to be returned.

Example:

Execute the following:

DECLARE @YEAR INT = 2012
	,@MONTH INT = 2
	,@DAY INT = 18
	,@HOUR INT = 11
	,@MINUTE INT = 59
	,@SECONDS INT = 59

SELECT DATETIME2FROMPARTS(@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3) AS [Using DATETIME2FROMPARTS Function]
GO

Here is the result set:

Using DATETIME2FROMPARTS Function
---------------------------------
2012-02-18 11:59:59.500

4.4) SMALLDATETIMEFROMPARTS – The SMALLDATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour and the specified minute.

Syntax:

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

SMALLDATETIMEFROMPARTS(), expects five parameters,

  1. Year – Integer expression specifying a year.
  2. Month – Integer expression specifying a month, from 1 to 12.
  3. Day – Integer expression specifying a day.
  4. Hour – Integer expression specifying hours.
  5. Minute – Integer expression specifying minutes.

Example:

Execute the following:

DECLARE @YEAR INT = 2012
	,@MONTH INT = 2
	,@DAY INT = 18
	,@HOUR INT = 11
	,@MINUTE INT = 59

SELECT SMALLDATETIMEFROMPARTS(@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS [Using SMALLDATETIMEFROMPARTS Function]
GO

Here is the result set:

Using SMALLDATETIMEFROMPARTS Function
-------------------------------------
2012-02-18 11:59:00

4.5) DATETIMEOFFSETFROMPARTS – The DATETIMEOFFSETFROMPARTS function returns a full datetimeoffset. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

Syntax:

DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)

DATETIMEOFFSETFROMPARTS(), expects ten parameters,

  1. Year – Integer expression specifying a year.
  2. Month – Integer expression specifying a month, from 1 to 12.
  3. Day – Integer expression specifying a day.
  4. Hour – Integer expression specifying hours.
  5. Minute – Integer expression specifying minutes.
  6. Seconds – Integer expression specifying seconds.
  7. Fractions – Integer expression specifying fractions.
  8. Hour_offset – Integer expression specifying the hour portion of the time zone offset.
  9. Minute_offset – Integer expression specifying the minute portion of the time zone offset.
  10. Precision – Integer literal specifying the precision of the datetimeoffset value to be returned.

Example:

Execute the following:

DECLARE @YEAR INT = 2012
,@MONTH INT = 2
,@DAY INT = 18
,@HOUR INT = 11
,@MINUTE INT = 59
,@SECONDS INT = 59

SELECT DATETIMEOFFSETFROMPARTS(@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 5, 30, 3) AS [Using DATETIMEOFFSETFROMPARTS Function]
GO

Here is the result set:

Using DATETIMEOFFSETFROMPARTS Function
--------------------------------------
2012-02-18 11:59:59.500 +05:30

4.6) TIMEFROMPARTS – The TIMEFROMPARTS function, returns a full time value as shown in the below query result. It is important to note that the fractions argument actually depends on the precision argument.

When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second. When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second. When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

Syntax:

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

TIMEFROMPARTS(), expects five parameters,

  • Hour – Integer expression specifying hours.
  • Minute – Integer expression specifying minutes.
  • Seconds – Integer expression specifying seconds.
  • Fractions – Integer expression specifying fractions.
  • Precision – Integer literal specifying the precision of the time value to be returned.

Example:

Execute the following:

DECLARE @HOUR INT = 11
	,@MINUTE INT = 59
	,@SECONDS INT = 59

SELECT TIMEFROMPARTS(@HOUR, @MINUTE, @SECONDS, 500, 3) AS [Using TIMEFROMPARTS Function]
GO

Here is the result set:

Using TIMEFROMPARTS Function
----------------------------
11:59:59.500

4.7) EOMONTH – This function takes two parameters first being start_date which is mandatory and the second one is Month_to_add which is optional. This function will return the last day of the month (also termed as EOM or end of the month) for the given date, By passing the second argument also it will add the months to the start_date and then returns the last day of the month as per the final date (start_date + Month_to_add)

Syntax:

EOMONTH ( start_date [, month_to_add ] )

EOMONTH(), expects two parameters,

  • Start_date – Date expression specifying the date for which to return the last day of the month.
  • Month_to_add – Optional integer expression specifying the number of months to add to start_date.

Example:

Execute the following:

DECLARE @STARTDATE DATETIME = GETDATE()

SELECT EOMONTH (@STARTDATE, 1) AS [Using EOMONTH Function]
GO

Here is the result set:

Using EOMONTH Function
----------------------
2012-03-31

5) Logical Functions:

5.1) CHOOSE – This function can be used to return the value out of a list based on its index number (Note: Index no. here starts from 1).

Syntax:

CHOOSE ( index, val_1, val_2 [, val_n ] )

CHOOSE(), expects two parameters,

  1. Index – Is an integer expression that represents a 1-based index into the list of the items following it.
  2. Value – List of values of any data type.

Example:

Execute the following:

DECLARE @index INT

SET @index = 3

SELECT CHOOSE(@index, 'Cricket', 'Football', 'Hockey') AS [Using CHOOSE Function]

SET @index = 2

SELECT CHOOSE(@index, 'Cricket', 'Football', 'Hockey') AS [Using CHOOSE Function]

Here is the result set:

Using CHOOSE Function
---------------------
Hockey

Using CHOOSE Function
---------------------
Football

5.2) IIF – Conditional function which will return the value based on the condition you specified as the first argument. This is similar to SSRS IIF expression.

Syntax:

IIF ( boolean_expression, true_value, false_value )

IIF(), expects three parameters,

  1. Boolean_expression – A valid Boolean expression.
  2. True_value – Value to return if boolean_expression evaluates to true.
  3. False_value – Value to return if boolean_expression evaluates to false.

Example:

Execute the following:

DECLARE @a INT = 45;
DECLARE @b INT = 40;

SELECT IIF(@a >;@b
		AND @b >;30, 'TRUE', 'FALSE') AS [Using IIF Function];

SET @a = 25

SELECT IIF(@a >;@b
		AND @b >;30, 'TRUE', 'FALSE') AS [Using IIF Function];

Here is the result set:

Using IIF Function
------------------
TRUE

Using IIF Function
------------------
FALSE

5.3) String Functions:

CONCAT – It’s the same concatenate function that we use in excel, it will concatenate two or more strings to make it single string. It implicitly converts all arguments to string types.

The return type depends on the type of the arguments. The following table illustrates the mapping:

Input type Output type and length
If any argument is a SQL-CLR system type, a SQL-CLR UDT, or nvarchar(max) nvarchar(max)
Otherwise, if any argument is varbinary(max) or varchar(max) varchar(max) unless one of the parameters is an nvarchar of any length. If so, then the result is nvarchar(max).
Otherwise, if any argument is nvarchar (<;= 4000) nvarchar(<;= 4000)
Otherwise, in all other cases varchar(<;= 8000)unless one of the parameters is an nvarchar of any length. If so, then the result is nvarchar(max).

Syntax:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

CONCAT(), expects at least two parameters and maximum of 254 parameters,

String_value – A string value to concatenate to the other values.

Example:

Execute the following:

SELECT CONCAT ( 'I'
		,' '
		,'Love'
		,' SQL Server'
		,' 2012 DENALI'
		) AS [Using CONCAT Function];

Here is the result set:

Using CONCAT Function
-----------------------------
I Love SQL Server 2012 DENALI

5.4) FORMAT – Function for locale-aware formatting of date/time and number values as strings.

Syntax:

FORMAT ( value, format [, culture ] )

FORMAT(), expects at three parameters,

  1. Value – Expression of a supported data type to format.
  2. Format – nvarchar format pattern.
  3. Culture – Optional nvarchar argument specifying a culture.

“If the culture argument is not provided, then the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server . If the culture argument is not valid, FORMAT raises an error.”

Example:

Execute the following:

DECLARE @Date DATETIME = '2011/21/02';

SELECT FORMAT(@Date, 'yyyy/MM/dd hh:mm:ss tt', 'en-GB') AS [Using FORMAT Function];

SELECT FORMAT(@Date, 'D', 'en-US') AS [Using FORMAT Function];

Here is the result set:

Using FORMAT Function
-----------------------
2011/02/21 12:00:00 AM

Using FORMAT Function
-------------------------
Monday, February 21, 2011

Conclusion

In this article I have covered with examples the fourteen new built in functions in four different categories that are introduced with Microsoft SQL Server 2012 Release Candidate 0. These built-in functions makes database developers lifes easier by helping them to format data quickly. These new useful functions equal the functionality that is found in other expressions languages.