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

Why the size of the backup file changes?

Hi Friends,

Have you ever noticed, while taking a compressed backup the file which gets created initially has a different size than the final file size when the backup completes. To be more precise, the file which gets created initially allocates more space than the final backup file size.

 

But the question is why?

Actually the size of the backup file depends upon the compression settings applied while taking the backup. Now since SQL Server is programmed to get maximum performance, SQL Server calculates the estimated size of the backup and initially allocates the space depending upon an algorithm to avoid frequent growth which results in size changes.

 

Now the question arises does this happens every time?

The answer is No; SQL Server can estimate accurate size of the backup if we are not using any compression settings, based on the size of the Database.

 

Then on what algorithm the SQL Engine decides the backup size?

The size is estimated from the number of allocated extends in the data files, this should be close to the reserved column in the output of sp_spaceused.  In the case of compressed backups, the size depends upon the compression setting which is again dependent upon the data as to how much it can be compressed. In this case SQL server creates the initial file equal to one third of the reserved size of the database. If at any point during backup more space is required the file is extended and if in case the final size is smaller than the initial size (which will be the case most of the time), SQL Server will trim the backup file to the actual used size.

For Example: The DB Size is 50 GB, the target backup size decided by SQL Algorithm comes out to be 17 GB, means the initial size of the backup file would be somewhere around 17GBs but the final size comes to 10GB, because of the compression. The file gets trimmed before releasing the lock since the actual used size was just 10 GBs.

 

Regards

Sarabpreet Anand

Unable to open Maintenance Plans in SSMS

Hi Friends,

A few days back, I got a backup failure alert regarding one of our most critical SQL Instance. I quickly connected to our jump-box and took a session of the server to diagnose further. From the job history I concluded that I need to modify the maintenance plan in order to fix it, so I located the respective maintenance plan (we have many maintenance plans on this instance) and when I double clicked I was welcomed with a strange unique error.

I was using SQL Server 2005 SP2 on this server. Here is a screenshot of the error i received.

1_SQL_Server_Unable_to_open_Maintenance_Plans_in_SSMS

The Error Says: <writing the error in text so that search engine could locate this entry and you can find it when needed>

Microsoft SQL Server Management Studio is unable to load this document:
SSIS object name is invalid. More specific errors would have been raised explaining the exact naming problem.

And I got a pop-up with the error:

Value cannot be null.
Parameter name: component (system.Design)

After clicking Ok I noticed the same first error is repeated in the error list without any description.

2_SQL_Server_Unable_to_open_Maintenance_Plans_in_SSMS

Now that was pretty strange and unique (to me at-least), so I started searching for similar kind of problems, after a lot of BINGing and GOOGLEing I came to know that there is a dts dll which sometimes doesn’t work or gets unregistered (reason mentioned but was not correct in my case so don’t want to mislead you all) and in order to fix this problem we can try to re-register the same dll.

Step i followed in order to resolve this:

  1. For registering dts.Dll you need to first locate the C:\Programs Files\ Microsoft SQL Server\100\DTS\Binn directory from Command prompt.

Note: The complete path may be different in your case. I am using c:\ drive since the SQL Instance is installed on C drive for this server.

3_SQL_Server_Unable_to_open_Maintenance_Plans_in_SSMS

 

2. Issue a command regsvr32 dts.dll

After pressing enter I got a pop-up confirming that the DTS.DLL has been registered.

4_SQL_Server_Unable_to_open_Maintenance_Plans_in_SSMS

 

That’s it, now open the SQL Server Management Studio and it will let you edit\view the SSIS packages. The problem I was having with backup was a different story all together, will tell you some other day.

In case you are still not able to open SSIS packages, you may also want to try the steps given below based on the SQL Engine you have. (The above solution worked for me like anything so never tried the additional option)

If the versions of the instances of SQL Server 2005 are not earlier than SQL Server 2005 SP2, run the following command at a command prompt to register the 32-bit Dts.dll file:

%windir%\syswow64\regsvr32 “%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll”

You may also need to manually register MsDtsSrvrUtil.dll, using

%windir%\syswow64\regsvr32 “%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll
If running 64-bit Integration Services, you may need to manually register the 64-bit dlls using these commands

%windir%\system32\regsvr32 “%ProgramFiles%\Microsoft SQL Server\90\dts\binn\dts.dll

%windir%\system32\regsvr32 “%ProgramFiles%\Microsoft SQL Server\90\dts\binn\MsDtsSrvrUtil.dll

 

Note:

Ideally it should fix the issue, pls. check if you registered the dll for correct install (32\64-bit)

also try this out: regsvr32 oleaut32.dll

regsvr32 actxprxy.dll

Also check if the client tools are also updated on the box where SQL 2005 SP4 is installed.

 

The above issue should get fixed if the client tools of SQL 2005 are also updated with SP4 and you’ve registered the correct set of dlls.

 

Regards

Sarabpreet Anand

Memory Configuration Changes | SQL Server 2012

Hi Friends,

There are a few changes in SQL Server 2012 in Memory Manger, and a DBA should be aware of such changes before upgrading or installation of SQL 2012 Instance on production.

In Earlier versions of SQL Server (before SQL 2012) SQL Server was also consuming memory outside of the limit defined in Max Server Memory. Those who don’t know, in earlier versions SQL Server was allocating memory mainly in two ways Single Page and Multi Page. Single page allocation was consuming memory from the limit defined in Min & Max Memory Setting; the main consumer of this type of memory is Buffer Pool. Multi-Page allocation was capable to consume memory out of the limit which is S0_Called available for OS.

SQL Server 2012 has a new page allocator that manages both single-page and multi-page allocations (less than 8 KB and greater than 8 KB allocation requests). Therefore, there is no separate categorization that is called “Multi-Page allocations” in SQL Server 2012.

 

Changes to “max & min server memory (MB)”

In earlier versions of SQL Server (2005, 2008, 2008 R2), the following configuration options determined the limits of physical memory that the buffer pool consumed. Notice that we are talking about physical memory in this case, physical memory that is committed by the SQL Server database engine process:

  • max server memory (MB)
  • min server memory (MB)

This configuration option typically included only memory allocations that were less than or equal to 8 KB in the SQL Server process. These allocations were also referred to as “single_page_allocations” because the SQL Server memory manager used a page size of 8 KB. This configuration did not include the following memory allocation requests:

  • Multi-Page allocations from SQL Server: These are allocations that request more than 8 KB.
  • CLR allocations: These allocations include the SQL CLR heaps and its global allocations that are created during CLR initialization.
  • Memory allocations for thread stacks in the SQL Server process.
  • Memory allocation requests made directly to Windows: These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL Server process. Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

 

Starting with SQL Server 2012, Multi-Page allocations and CLR allocations are also included in memory limits that are controlled by max server memory (MB) and min server memory (MB).

Carefully review your current max server memory (MB) and min server memory (MB) values after you upgrade to SQL Server 2012. You should review such values because SQL Server 2012 now includes and accounts for more memory allocations compared to earlier versions. These changes apply to both 32-bit and 64-bit versions of SQL Server 2012.

The following table indicates whether a specific type of memory allocation is controlled by the max server memory (MB) and min server memory (MB) configuration options.

 

Type of memory allocation SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 SQL Server 2012
Single-page allocations Yes Yes, consolidated into “any size” page allocations
Multi-page allocations No Yes, consolidated into “any size” page allocations
CLR allocations No Yes
Thread stacks memory No No
Direct allocations from Windows No No

 

Changes to “memory_to_reserve”

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

AWE Feature is not available in SQL Server 2012

http://www.sarabpreet.com/2016/02/sql-server-awe-enabled-option-deprecated/1509

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.

Carefully review the memory requirements of the existing instance of SQL Server before you upgrade to SQL Server 2012.

Do let us know your thoughts on this by leaving a comment.

Regards

Sarabpreet Anand

Which trigger will be triggered first?

Hi Friends,

Yesterday I was working on a specific requirement of a client to create DDL trigger for a Database to log & validate certain things. While doing so I discovered there were already two DDL triggers created on the DB for the same event. Now I was not sure which one will be triggered first and after searching msdn I came across a stored procedure which can control this behaviour to certain extent.

SP_SetTriggerOrder, this is a system stored procedure which can specify the after triggers that should be fired at First or Last order. Unfortunately you can’t control the complete sequence but this much is more than enough to handle most of the requirements.

This system stored procedure accepts four parameters namely:

TriggerName: Trigger Name, you should mention schema name wherever applicable. However as stated earlier also this will not accept InsteadOf triggers.

Order: This parameter can accept one of these three values (‘First’, ‘Last’, ’None’) you can specify None if you don’t wish to set any sequence for this trigger.

Stmttype (StatementType): you can specify here the EventType for which the trigger has been created. You can’t specify an EventGroup.

NameSpace: This parameter describes the scope of trigger, the valid values are (‘Database’, ‘Server’, ‘NULL’) to define Database Scope, Server Scope and DML Trigger respectively.

Here is an example where I am configuring the zone_target Stored procedure to be the last fired trigger for Insert Statement

Exec sp_settriggerorder

@triggername= ‘sales.zone_target’,

@order=’Last’,

@stmttype = ‘Insert’;

Caveat –there are a few things you need to consider before using this SP.

  1. Replication automatically generates a First trigger for the tables if an immediate update or Queued updating subscription is configured, so in case you’ve defined a first trigger for a table you’ll not be able to add it in replication. To make it work either alter the replication trigger or change the sequence for other trigger on the object.
  2. There can be only one First and one Last trigger for each statement on a single table.
  3. If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. The order value must be reset by using sp_settriggerorder.
  4. If the same trigger must be designated as the first or last order for more than one statement type, sp_settriggerorder must be executed for each statement type. Also, the trigger must be first defined for a statement type before it can be designated as the First or Last trigger to fire for that statement type.
  5. The order of execution of DDL triggers that exist on the same event is as follows:
  • The server-level trigger marked First.
  • Other server-level triggers.
  • The server-level trigger marked Last.
  • The database-level trigger marked First.
  • Other database-level triggers.
  • The database-level trigger marked Last.

Hope this will help you, do leave us comments & let us know how we are doing.

 

 

Regards

Sarabpreet Anand

Memory Shortage for all 32 Bit SQL Server 2012 Instances

Hi Friends,

As you all know AWE has been deprecated and not available from SQL Server 2012 (Find details here), as a result when you upgrade from SQL Server 2008 R2 to the next version of SQL Server,you cannot use more memory than what the virtual address space limits in 32-bit instances of SQL Server. If you must have more memory for this instance of SQL Server, then you have to migrate to a 64-bit instance of SQL Server.

After you upgrade to SQL Server 2012, the maximum amount of memory that can be used by the 32-bit instance of SQL Server is determined as follows.

SQL Server and operating system settings Maximum amount of memory used by SQL Server
32-bit SQL Server on 32-bit OS

2 GB

32-bit SQL Server on 32-bit OS with /3G boot option 3 GB
32-bit SQL Server on 64-bit OS 4 GB

Those who don’t know much about AWE;

AWE was being used in earlier versions of 32 Bit SQL Servers so that they can support more than 4GB of Physical Memory. SQL Server can access up to 64 GB of memory on Microsoft Windows Server 2000 & 2003.

By Default, Standard 32-bit addresses can map a maximum of 4 GB of memory. By default, on 32-bit Microsoft Windows operating systems, 2 GB is reserved for the operating system, and 2 GB is made available to the application. If you specify a /3gb parameter in the Boot.ini file of Windows Server, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB.

AWE is a set of extensions to the memory management functions of Windows that allow applications to address more memory than the 2-3 GB that is available through standard 32-bit addressing. AWE lets applications acquire physical memory, and then dynamically map views of the non-paged memory to the 32-bit address space.

Although the 32-bit address space is limited to 4 GB, the non-paged memory can be much larger. This enables memory-intensive applications, such as large database systems, to address more memory than can be supported in a 32-bit address space.

Before you configure the operating system for AWE on 32-bit operating systems, you must add the /pae parameter to the Boot.ini file and reboot the computer. Windows Server 2003, PAE is automatically enabled only if the server is using hot-add memory devices in Windows Server 2003.

The SQL Server buffer pool can fully utilize AWE mapped memory; however, only database pages can be dynamically mapped to and unmapped from SQL Server’s virtual address space and take full advantage of memory allocated through AWE. AWE does not directly help supporting additional users, databases, queries, and other objects that permanently reside in the virtual address space.

Lock pages in Memory

This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. The Lock Pages in Memory option is set to OFF by default in SQL Server. If you have system administrator permissions, you can enable the option manually by using the Windows Group Policy tool (gpedit.msc) and assign this permission to the account that SQL Server is running.

Although it is not required, we recommend locking pages in memory when using 64-bit operating systems. For 32-bit operating systems, Lock pages in memory permission must be granted before AWE is configured for SQL Server.

Regards

Sarabpreet Anand

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

Calculate Number of Leaves – excluding weekends

Hi Friends,

Sometime back I was answering questions on TechNet Forum when I saw an interesting and genuine problem.

The user wanted to calculate the number of leaves taken by an employee excluding weekends. Now calculating the number of leaves wasn’t a big deal all we need to do is just use datediff function but excluding the weekends was something which forced me to think harder.

Then I started digging into this and finally I was able to make logic.

Here is the script:  (Updated)

Direct Link: (Updated)

It works pretty well

For the sake of simplicity I’m creating the table with just three columns:

  1. leave_start
  2. leave_end
  3. leave_working_days

Here is the output:

1_SQL_Server_T-SQL_Calculate_Number_of_Leaves_excluding_weekends

Do let me know your way to tackle this problem – by way of leaving a comment.

Enjoy!

Regards

Sarabpreet Anand