Tag: sqlserverrider

SQL SERVER 2012 – CONCAT() An Introduction to String Concatenation Function


CONCAT() : It is a string concatenation function introduced in SQL SERVER 2012. It helps us to merge or concatenate two or more string values in to a single string value. We must provide at-least two string values to this function otherwise this function will raise an error. The length of the output string is determined by the function arguments.

Function argument type and output type and its length mapping

pic1If all the function arguments are null then CONCAT function returns a single byte empty string value.

Usage

CONCAT ( string value1, string value2 [, string value N ] )

Where N = 255, So, the total number of arguments to this function is 255.

Example

1. This is a simple example to show the use of CONCAT function

pic22. In this example we are going to use NULL value in the argument

pic3pic4We can also use other data typed arguments in this function argument. I hope you have understood the concept and the use of CONCAT string function.

Learn more about other new functions in SQL SERVER 2012 here.

SQL SERVER – REVERSE() a string function @SQLSERVER


REVERSE() : It is a string function used to change the position of the whole string in the reverse order. This function takes a string or binary argument value. But, it converts the argument to string data type implicitly otherwise we have to use CAST or CONVERT function to change the function argument type to string. It returns varchar or nvarchar type value as a function output.

Usage

REVERSE ( string_expression )

Example

1. Simple example to show how Reverse function uses string constant

select ‘SQL SERVER’ as ‘Original value’, Reverse(‘SQL SERVER’) as ‘Reversed String’

Output

pic1

2. This example is to show you, How reverse function is converting its arguments DATE and FLOAT to a string value implicitly.

pic23. In this example I am going to use the REVERSE function to find a string is Palindrome or not.

Before we begin, let us learn about the history behind Palindrome

A palindrome is a word, phrase, number, or other sequence of symbols or elements, whose meaning may be interpreted the same way in either forward or reverse direction.

The word “palindrome” was coined from the Greek by the English writer Ben Jonson in the 17th century. There are more interesting stuff in that page. So, please read more information about Palindrome here – Wikipedia

SQL Query

Declare @str as varchar(50)
Set @str = ‘SQL SERVER RIDER’
Select IIF( @str = REVERSE(@str),  @str+’ is a Palindrome’, @str+’ is not a Palindrome’) as ‘Finding palindrome Word’

Output 1

pic3Output 2

pic4A list of single or multiple word Palindrome.

I hope you have understood the user of REVERSE function. To learn about other string function please refer to the blog post “String Functions at a glance“.

SQL SERVER 2012 – IIF() a logical function @SQLSERVER


IIF() is a logical function that evaluates a Boolean expression and choose one of the two output values given in the true or false block. This function is similar to CASE statement. We can use IIF() function to create nested logical statement up to 10 levels.

Usage

IIF(Boolean expression, True block, False block)

Example

1. A simple logical statement using IIF function

Print IIF(1 > 0, ‘TRUE’, ‘FALSE’)

pic12. Another simple example for nested IIF statement

pic2IIF() is a very simple and useful function in SQL SERVER 2012 and higher version. It helps developer to evaluate a simple logical expression in the SQL Query or T-SQL statement. It reduces the line of code and gives more readability to the program and simplifies the logic.

There is one more logical function CHOOSE() is available in SQL SERVER 2012. Click here to learn all new functions in SQL SERVER 2012.

SQL SERVER – Implicit Transactions – SET IMPLICIT_TRANSACTIONS – @SQLSERVER


Transaction is the section or block of work that user is currently working. User has two choice, the section can be commit (Store permanent) or rollback (erase) the current transaction changes.

SET IMPLICIT_TRANSACTIONS

Implicit transaction is defined as a new transaction that is implicitly started when the prior transaction completes, but each transaction must be explicitly COMMIT or ROLLBACK by the user. otherwise the transactions will be rolledback automatically when the connection is terminated.

when user set the current session with IMPLICIT_TRANSACTIONS ON then SQL Server changes the connection into implicit transaction mode. If we set this set statement OFF then it will set the current environment as autocommit mode (ie, all individual statements are committed if they complete successfully).

The following keywords will start a new transaction automatically in the implicit transaction mode.

pic1Usage

SET IMPLICIT_TRANSACTIONS  ON

or

SET IMPLICIT_TRANSACTIONS OFF

Example

1. when implicit_transactions is off, I am executing the following statement.

use DataLab
 GO
set implicit_transactions off
 GO
Create table Trans
 (
 Stmt varchar(50)
 )
 GO
rollback transaction
 GO
insert into Trans values('SQL SERVER RIDER')
 GO
Select * from Trans
 GO

Result and message

pic2Rollback statement failed because we did not open a transaction in this session. See the result now.

pic3

Table creation and Insert statement executed successfully.

2. when implicit_transactions is on, executing the above SQL statements. Drop the table Trans before execution the SQL statement.

pic4Line 16 and 19 failed because of the object (table) Trans does not exist. Create statement opens a new transaction and we have rolled-back the table creation statement in the next line. So, there is not a table called Trans for Insert and Select statement.

We can also get the count of open transaction using @@TRANCOUNT function. It helps user to get the transaction that are opened in the current session.

I hope you have understood the use of the IMPLICIT_TRANSACTIONS set statement.

SQL SERVER – Get File IO Statistics using sys.dm_io_virtual_file_stats – DMV – @SQLSERVER


sys.dm_io_virtual_file_stats is the Dynamic Management Function. It is used to retrieve the database files such as Data file and Log file I/O statistics.

This function returns the following information.

pic1This function requires two arguments such as DatabaseID and FileID. But, we can also use NULL as the function arguments if we need IO statistics information for all database files in that instance.

Example

1. Retrieving IO statistics for all databases in the current instance.

select * from sys.dm_io_virtual_file_stats(null,null)

2. Retrieving IO statistics for the current database only.

select * from sys.dm_io_virtual_file_stats(DB_ID(),null)

3. Retrieving IO statistics for the data files of the current database.

select * from sys.dm_io_virtual_file_stats(DB_ID(),FILE_IDEX(‘DataLab’))

Sample Outputpic2

File statistics information is very useful for performance tuning. We can use this data to understand the workload of a physical storage. It can act as a key data when we choose the drive for storing data and log file for high responsive database for the business application.