21 Mar

Built in Functions in SQL Server 2012

Microsoft SQL Server 2012 introduced 14 new built in functions. These new functions are:

Conversion functions

  • PARSE
  • TRY_CONVERT
  • TRY_PARSE

Date and time functions

  • DATEFROMPARTS
  • DATETIME2FROMPARTS
  • DATETIMEFROMPARTS
  • DATETIMEOFFSETFROMPARTS
  • EOMONTH
  • SMALLDATETIMEFROMPARTS
  • TIMEFROMPARTS

Logical functions

  • CHOOSE
  • IIF

String functions

  • CONCAT
  • FORMAT

In addition to the 14 new functions, one existing function has been changed. The existing LOG function now has an optional second base parameter.

Obviously all of these functions are useful in one way or the other, but I’ll be explaining five very useful functions which I feel are common\useful to all the environments.

 

1. 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)

This can be easily understood with the help of an example:

DECLARE @date DATETIME;

SET @date = ’12/06/2010′;

SELECT EOMONTH (@date) AS Result;

–or

DECLARE @date VARCHAR(255);

SET @date = ’12/06/2010′;

SELECT EOMONTH (@date) AS Result;

Both of these queries will return the same output i.e.,

–>   2010-12-31 00:00:00.000

 

In order to find the last day of the month for any future\previous month you must use the second parameter. You can provide a positive or negative value to the second argument based on the requirements. The example below explains it in a better way.

DECLARE @date DATETIME;

SET @date = GETDATE();

SELECT EOMONTH ( @date ) as ThisMonth;

SELECT EOMONTH ( @date, 1 ) as NextMonth;

SELECT EOMONTH ( @date, -1 ) as LastMonth;

 

2. 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) This function takes at-least 2 arguments, where the first must be an INT and the second onwards can be varchar.

The following example returns the Second item from the list of values that are provided.

Select Choose (2, ‘January’, ‘February’, ‘March’);

You can further enhance this functionality and make it dynamic, just declare a variable and depending upon the logic set the value and pass the variable as the first parameter.

Here’s the example for this:

declare @a int

set @a =2

SELECT CHOOSE ( @a, ‘January’, ‘February’, ‘March’);

 

3. IIF

If you’ve ever done some programming in Java or VB you must’ve realized what is this? Yes this is the same Conditional function which will return the value based on the condition you specified as the first argument. It takes three parameters where the first declares the condition and rest two are the results you want it to return in case the condition comes out to be true or false respectively.

A. Simple IIF example

DECLARE @a int = 45;

DECLARE @b int = 40;

SELECT IIF (@a > @b, ‘TRUE’, ‘FALSE’) AS Result;

You can also put multiple conditions using ‘and’, ‘or’ keywords which will help you to evaluate the condition based on multiple things.

 

B. Complex IIF example

DECLARE @a int = 45;

DECLARE @b int = 40;

SELECT IIF (@a>@b and @b>30, ‘TRUE’, ‘FALSE’) AS Result;

‘And’ keyword specifies that both the conditions @a>@b and @b>30 should be satisfied in order to make it true, whereas if you replace ‘and’ with ‘or’ keyword then the condition will be accepted as true even if one of them fails.

 

4. 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. It accepts a minimum of 2 (at-least) Arguments and maximum of 254 Arguments.

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 isvarbinary(max) orvarchar(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 isnvarchar(<= 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 isnvarchar(max).

 

A. Using CONCAT

SELECT CONCAT (‘Today ‘, ‘is ‘, 6th, ‘-‘, ‘December’) AS Result;

 

5. TRY_CONVERT

Seems this Function is carrying try and catch block with itself. In earlier versions of SQL Server whenever the cast was not successful we were getting error messages but now no need to worry using this function will try to convert the data-type and return the value if it succeeds otherwise it will return null rather than that ugly error, but wait a minute there is a catch to it, if you are requesting this function to convert something which is explicitly not allowed this will definitely end-up giving an error.

 

A. TRY_CONVERT returns null

SELECT TRY_CONVERT(float,’test’)

You can decorate this with a custom message to make it more clear like:

SELECT

CASE WHEN TRY_CONVERT(float,’test’) IS NULL

THEN ‘Cast failed’

ELSE ‘Cast succeeded’

END AS Result;

GO

B. TRY_CONVERT fails with an error

SELECT TRY_CONVERT(xml, 4);

The result of this statement is an error, because an integer cannot be cast into an xml data type.

 

Hope this post helped you, do leave comments.

Regards

Sarabpreet Anand


16 Feb

SQL Server AWE Enabled Option | Deprecated

Like we already know, Windows-based applications can use Windows AWE (Address Windowing Extensions) APIs to allocate and to map physical memory into the process address space. AWE allow 32-bit operating systems to access large amounts of memory. Memory that is allocated by using this method is never paged out by the operating system, provided “Lock Pages In Memory” user right (LPIM) has been granted to the application Service account.

 

Lock Pages in memory is by default given to Local system Service account.

SQL Server supports dynamic allocation of AWE memory on Windows Server. The SQL Server 64-bit version also uses “locked pages” to prevent the process working set (committed memory) from being paged out or trimmed by the operating system. When you enable “locked pages,” it is very important to set an appropriate value for “max server memory” and for “min server memory” configuration options for each instance of SQL Server to avoid system-wide problems.

During startup, AWE reserves only a small portion of AWE-mapped memory. As additional AWE-mapped memory is required, the operating system dynamically allocates it to SQL Server. Similarly, if fewer resources are required, SQL Server can return AWE-mapped memory to the operating system for use by other processes or applications.

AWE Feature is not available in SQL Server 2012.

Even though the “awe enabled” feature is not available in 32-bit SQL Server 2012, you can still use the “locked pages” feature by assigning the “lock pages in memory” user right for the SQL Server startup account.

 

Note:

The virtual address space that is reserved for these allocations is determined by the memory_to_reserve configuration option. The default value that SQL Server uses is 256 MB. To override the default value, use the SQL Server -g startup parameter. This part of the virtual address space is also known as “Memory-To-Leave” or “non-Buffer Pool region.”

Because SQL Server 2012 has the new “any size” page allocator that handles allocations greater than 8 KB, thememory_to_reserve value does not include the multi-page allocations. Except for this change, everything else remains the same with this configuration option.

 

Regards

Sarabpreet Anand