Show or Hide SQL Warnings – SET Statement – SQL Server

ANSI_WARNINGS Specifies ISO standard behavior for errors in SQL Server. It helps to show and hide the error messages during query execution.

Usage

SET ANSI_WARNINGS ON

or

SET ANSI_WARNINGS OFF

 

If ANSI_WARNINGS is ON than warning will be raised when the SQL statements has problem while execution. such as, null value in aggregate function returns warning message. So, I have used aggregation function in the below given example to show how this set statement treats the error messages.

If ANSI_WARNINGS is OFF then warnings will not be shown.

If  ANSI_WARNINGS is set to OFF then the divide-by-zero and arithmetic overflow errors returns null values. Else, it rolled back the transaction and throws an error message.

SET ANSI_WARNINGS must be ON when we are creating or using indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

So, we should understand the use of this set statement and add this feature in our application properly.

Ignore arithmetic overflow error – SET Statement – SQL Server

ARITHIGNORE Set statement is used to control the messages raised by arithmetic overflow or divide-by-zero errors during a query execution.

Usage

SET ARITHIGNORE ON

or

SET ARITHIGNORE OFF

 

SET ARITHIGNORE setting only controls whether an error message is returned or not. IF set ON then no message else if set OFF message will be returned. NULL value is returned in both case.

The SET ARITHABORT setting is used to determine whether the query is terminated in the event of arithmetic overflow or divide-by-zero error.

If SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON then SQL Server still returns an error message when encountering divide-by-zero or overflow errors.

These set statement values are initialized by the SQL server while we create the session. So, It is mandatory to set all set statements properly to obtain the required result.

Get the current value of the set statements in the current session using @@OPTIONS.

Example

1. When ARITHABORT and SET ANSI_WARNINGS is OFF,  ARITHIGNORE is ON. Null is returned due to the arithmetic errors.

2. When ARITHABORT and SET ANSI_WARNINGS is OFF,  ARITHIGNORE is OFF. Null is returned due to the arithmetic errors and error message is also returned.

Set auto rollback transactions due to arithmetic overflow – SET Statements – SQL Server

SET ARITHABORT statement allows SQL Server to end the query execution whenever the system encounters arithmetic overflow or divide-by-zero error. This set statement is always on by default. But, we can turn this off. If it is off then we may face poor performance problem and even query optimizer get different query.

Arithbort set statement becomes more effective if we use it with ansi_warnings set statement.

SET ARITHABORT ON + SET ANSI_WARNINGS ON = the query execution will be terminated if the system encounters arthimetic overflow or divide-by-zero error.

SET ARITHABORT ON + SET ANSI_WARNINGS OFF = This will terminate SQL execution and If the errors occur in a transaction then the transaction will be rolled back.

SET ARITHABORT OFF + SET ANSI_WARNINGS OFF = this will display a warning message  and NULL is assigned to the result of the arithmetic operation.

note:  If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. So, ARITHABORT must be ON when we create or change indexes on computed columns or indexed views.

Control SQL locks using locking statements – SQL Server – SET Statements

Locking is an unavoidable activities in concurrency system. But, we can control and manage the resource effectively. But, in this blog post I am going to introduce a SET statement called DEADLOCK_PRIORITY. This is one of the locking set statement in SQL Server. It tells how to react when dead lock occurs in the current session. As the name priority suggest that the importance or the order in which the deadlock should be treated with other processes.

DEADLOCK_PRIORITY Syntax (SQL Server 2012)

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }

<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

Argument description

LOW, NORMAL and HIGH are the constant value pre-defined in SQL Server. The numeric value equivalent to the names are -5, 0 and 5 respectively. We can also use this strings in a variable to assign to the SET statement. And, We can also give any whole number value between -10 to 10 instead variable or constant. Setting low value means that the priority for the deadlock for that session is low and make the current session as deadlock victim.

Normal is the default value for DEADLOCK_PRIORITY.

SET statement examples

SET DEADLOCK_PRIORITY HIGH

or

Declare @dlVar as varchar(10) = ‘LOW’

SET DEADLOCK_PRIORITY @dlVar

or

SET DEADLOCK_PRIORITY -1

Deadlock victim

To resolves the deadlock by choosing one of the sessions as a deadlock victim. The current transaction of the deadlock victim is rolled back and deadlock error message 1205 is returned to the client. It helps the other session to continue its work without interruption. Priority value plays the vital role in choosing deadlock victim session.

How does this SET statement react to the deadlock in concurrency system?

When Session A’s DEADLOCK_PRIORITY is set with a value (-10 to 10) and If Deadlock occurred and the other session in the deadlock chain is having the DEADLOCK_PRIORITY value greater than Session A’s priority value then the Session A will be the deadlock victim.

if both sessions having same priority value than the SQL Server will choose the deadlock victim by estimating the cost to rollback the current transaction and choose the less cost session as deadlock victim.

I have already wrote a blog post about lock timeout set statement. Please read  Lock_Timeout in SQL Server

 

Change the Date format of the current language – SQL Server – SET Statements

Formatting date is one of the mandatory task for the developers to show date values in the user reports. There are various ways to format date in SQL Server database and reporting services. But, in this blog post I am going to show you how to set up the date format that user is going to use for the date value with rearranging the date part string (dmy) in SQL Server.

This setting only applies to date data type (date, smalldatetime, datetime, datetime2 and datetimeoffset) string value in an expression.

DateFormat set statement also overrides the date format defined in the default language settings. Learn about Language in SQL Server

Set statement syntax

SET DATEFORMAT { format | @format_var }

Format or @format variable  must contain day (d), month (m) and year(y) combination format. For instance mdy, dmy, ymd, ydm, myd, and dym.

There are some restrictions applies to specific DateFormat format to use a date value stored in date, datetime2 and datetimeoffset data types. Because, we cannot use ydm format to use the date value.

A valid expression

Control current session and specific information – SQL Server – SET Statements

SET statement in SQL Server is used to handle a specific information (such as date format, displaying text length and etc) in the current session by altering the current session’s default set values. Some of the SET statements are already setup by the administrator at SQL instance level. So, when we initiate the session it applies all the default SET options to the user of that session. But, we have an option to alter that default value. Usually, we have to turn the set option ON or OFF and in some cases we have to pass some value to the set option. There are different groups of SET statements available in SQL Server as given below.

I have discussed some of the functions that are related to SET statements in my blog post. Those are given below for reference.

• get-the-current-values-of-set-options-sql-server
• set-textsize-and-textsize-sql-server
• set-first-day-of-the-week
• SET IDENTITY_INSERT

Construct SQL statements using catalog views – SQL Server

I recently worked on a project where there are some SQL statement that I need to write to insert, update and Delete the data in all columns in a set of tables. There may be some efficient way to implement this task. But, I found one simple solution for my scenario.

I have used SQL catalog views to build my SQL Statements. My scenario was to update the columns by replacing a character in it.

Example for building SQL Queries using catalog view

select ‘UPDATE ‘ + OBJECT_NAME(object_id) +’ SET ‘ + name + ‘ = REPLACE(‘ + name +’, ”+”,””)’ from sys.columns
where left(OBJECT_NAME(object_id), 3) = ‘tbl’

This query generates SQL update statements to replace character + to nothing in each column in all the tables starts with ‘tbl’ that I specified in the where clause.

Execution

there could be some very good way to do this. but found it is very quick solution for me. This is not the stopping point we can also use other object catalog views to accomplish our task quickly and simply.

object catalog views for reference