All posts by Basavaraj Biradar

Difference Between Sql Server VARCHAR and VARCHAR(MAX) Data Type

Ideally, it is better to compare Text and Varchar(MAX) data types, as in Sql Server 2005 Varchar(MAX) data type was introduced as an alternate for Text data type. Varchar(Max) data type provides multiple advantages over Text data type.

Like many initially when Varchar(MAX) datatype was introduced in Sql Server 2005, I too was not clear about the difference between Varchar and Varchar(Max) and which one to use when. Hope the differences listed in the below table clarifies these queries.

Varchar[(n)] Varchar(Max)
Basic Definition Non-Unicode Variable Length character data type.
Example:

DECLARE @Name VARCHAR(50)
         = 'BASAVARAJ'
SELECT @Name
Non-Unicode large Variable Length character data type.
Example:

DECLARE @Name VARCHAR(Max)
         = 'BASAVARAJ'
SELECT @Name
 Storage Capacity It can store maximum 8000 Non-Unicode characters (i.e. maximum storage capacity is 8000 bytes of storage). Optional Parameter n value can be from 1 to 8000. It can store maximum of 2 147 483 647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB).
Index? You can create index on Varchar column data type.
Example:

CREATE TABLE dbo.Employee
(id INT identity(1,1)
   PRIMARY KEY,
 Name VARCHAR(50))
GO
CREATE INDEX IX_EmployeeName 
 ON dbo.Employee(Name)
GO
Index can’t be created on a Varchar(Max) data type columns.
Example:

CREATE TABLE dbo.Employee
(id INT identity(1,1)
   PRIMARY KEY,
 Name VARCHAR(Max))
GO
CREATE INDEX IX_EmployeeName
 ON dbo.Employee(Name)
GO 

Error Message:
Msg 1919, Level 16, State 1, Line 1 Column ‘Name’ in table ‘dbo.Employee’ is of a type that is invalid for use as a key column in an index.

How data is stored Physically? It uses the normal data pages to store the data i.e. it stores the value ‘in a row’. Sql server will try to store the value ‘in a row’ but if it could not then it will store the value ‘out of row’. i.e. It uses the normal data pages until the content actually fills 8k of data.When overflow happens, data is stored as old TEXT Data Type and a pointer is replacing the old content.
No. of Bytes required for each character It takes 1 byte per character
Example:

DECLARE @Name VARCHAR(50)
         ='BASAVARAJ'
SELECT @Name Name,
 DATALENGTH(@Name) Length

Result:
Name Length
BASAVARAJ 9

It takes 1 byte per character
Example:

DECLARE @Name VARCHAR(MAX)
         ='BASAVARAJ'
SELECT @Name Name, 
 DATALENGTH(@Name) Length

Result:
Name Length
BASAVARAJ 9

Which one to use? If we know that data to be stored in the column or variable is less than or equal to 8000 characters, then we can use this data type.For example First Name, Last Name etc, columns value can’t cross the max 8000 characters limit, in such scenario’s it is better to use this data type. If we know that the data to be stored in the column or variable can cross a 8KB Data page, then we can use this data type.
Performance There is not much performance difference between Varchar[(n)] and Varchar(Max). Varchar[(n)] provides better performance results compared to Varchar(Max). If we know that data to be stored in the column or variable is less than or equal to 8000 characters, then using this Varchar[(n)]  data type provides better performance compared to Varchar(Max).Example: When I ran the below script by changing the variable @FirstName type to Varchar(Max) then for 1 million assignments it is consistently taking double time than when we used data type as Varchar(50) for variable @ FirstName.

DECLARE @FirstName VARCHAR(50), @COUNT INT=0, 
        @StartTime DATETIME = GETDATE()
WHILE(@COUNT < 1000000)
BEGIN
   SELECT @FirstName = 'BASAVARAJ', @COUNT = @COUNT +1
END
SELECT DATEDIFF(ms,@StartTime,GETDATE()) 'Time Taken in ms'
GO 6

Note: Here GO 6 statement executes the statements above it 6 times.

[ALSO READ] You may like to read below other popular articles on differences

1. Varchar vs NVarchar
2. Varchar vs Varchar(MAX)
3. Char vs Varchar
4. Text vs Varchar(Max)
5. Union vs Union All
6. DateTime vs DateTime2
7. SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
8. Stored Procedure vs User Defined Function
9. Primary Key vs Unique Key
10. RAISERROR vs THROW
11. Temporary Table vs Table Variable
12. Len() vs Datalength()
13. Sequence vs Identity
14. DATEDIFF vs DATEDIFF_BIG

CONCAT() STRING FUNCTION IN SQL Server 2012

This is one of the new string functions introduced in SQL Server 2012. This function provides an easy way for the developers to concatenate the string values. Let us first see the syntax of this method:

Syntax:

CONCAT( string1, string2 [,stringN])

Here the parameters: string1, string2, … ,stringN are the string values which will be concatenated. This method requires minimum two arguments and max 254 arguments.

Let us understand this new CONCAT function  with a simple example:

SELECT CONCAT('Basavaraj',' ','Biradar') Name

RESULT:
Name
—————–
Basavaraj Biradar

Some of the important features about this function are:
1) All arguments are implicitly converted to string types before concatenating them.

Let us see this with an example:

DECLARE @EmployeeId INT = 1
SELECT CONCAT(@EmployeeId,' ','Basavaraj') EmployeeIDAndName

RESULT:
EmployeeIDAndName
———————-
1 Basavaraj

In previous versions of SQL Server where if we are using ‘+’ to concatenate the values then we were explicitly need to convert them to string and then concatenate. To achieve the result as in the above SQL 2012  example we were needed to write a Sql Statement like below with CAST/CONVERT function:

DECLARE @EmployeeId INT = 1
SELECT CAST(@EmployeeId AS VARCHAR) + ' ' 
             + 'Basavaraj' EmployeeIDAndName

2) Null values are implicitly converted to an empty string.

Let us see this with an example:

DECLARE @FirstName VARCHAR(20) = 'Basavaraj', 
        @LastName VARCHAR(20)
SELECT CONCAT(@FirstName, ' ',@LastName) Name

RESULT:
Name
—————————————–
Basavaraj

In previous versions of SQL Server where if we are using ‘+’ to concatenate the values then we know that if one of the values is NULL then concatenated value will be NULL. To avoid this we were needed to use ISNULL function to check if the value is null then consider it as empty string. To achieve the result as in the above SQL 2012  example we were needed to write a Sql Statement like below with ISNULL function:

DECLARE @FirstName VARCHAR(20) = 'Basavaraj', 
        @LastName VARCHAR(20)
SELECT ISNULL(@FirstName,'') + ' ' 
       + ISNULL(@LastName,'') Name

QUIZ:
Whether the below query will return all the 8000 characters ‘B’ and 8000 characters ‘C’ in the result?

DECLARE @string1 AS VARCHAR(8000) = REPLICATE('B',8000),
 @string2 AS VARCHAR(8000) = REPLICATE('C',8000)
SELECT CONCAT(@string1,@string2)

Difference between Stored Procedure and User Defined Function in Sql Server

Below are the some of the major differences between User Defined Function and Stored Procedure in Sql Server.

To know more on the User-Defined functions with examples please visit the article: User-Defined function.
To know more on the Stored Procedure with examples please visit the articleStored Procedure.

Sl. No. User Defined function Stored Procedure
1 Function must return a value. Stored procedure may or not return values.
2 Will allow only Select statement, it will not allow us to use DML statements. Can have select statements as well as DML statements such as insert, update, delete
etc
3 It will allow only input parameters, doesn’t support output parameters. It can have both input and output parameters.
4 It will not allow us to use try-catch blocks. For exception handling we can use try catch blocks.
5 Transactions are not allowed within functions. Can use transactions within Stored procefures.
6 We can use only table variables, it will not allow using temporary tables. Can use both table variables aswell as temporary table in it.
7 Stored procedures can’t be called from function. Stored Procedures can call functions.
8 Functions can be called from select statement. Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec
statement can be used to call/execute stored procedure.
9 UDF can be used in join clause as a result set. Procedures can’t be used in Join clause

[ALSO READ] You may like to read below other popular articles on differences
Varchar Vs NVarchar
Varchar Vs Varchar(MAX)
Char Vs Varchar
Union Vs Union All
DateTime Vs DateTime2
SET QUOTED_IDENTIFIER ON Vs OFF
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity

SET Options with their setting values required while working with filtered index

While dealing with filtered index following Six SET option’s value should be set to ON

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON

And the one SET option whose setting value should be off is:

SET NUMERIC_ROUNDABORT OFF

If these settings are not set properly then filtered index creation may fail, query execution may not use the index or DML operation on the table may fail. The DML operation may fail with a error message like below:

INSERT/Update failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Click on the error message link to see a detailed example demonstrating when we get this error and how to resolve it.

To know more on the difference between SET ANSI_NULLS ON and SET ANSI_NULLS OFF Setting please visit the article: SET ANSI_NULLS ON/OFF Setting in Sql Server

To know more on the difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF Setting please visit the article: Difference Between SET QUOTED_IDENTIFIER ON and OFF setting in SQL Server

Please correct me, if my understanding is wrong. Comments are always welcome.

How to set default database in Sql Server Mangement Studio. A Time Saving Tip

In SSMS whenever we connect to any database server by default the MASTER database is the default selected one. Which forces us  in changing the database the one we want work using the USE statement or by using mouse and changing the selected db from the drop-down list.This is were anoying, and till recently daily I was wasting time in doing this multiple times.

If we know that by default most of the time we will be connecting to a specific database related to our project, then Sql Server Management Studio (SSMS) provides a way to change this. And next time onwards this nerw selected database will selected by default instead of the MASTER DB.

Steps: 1) Open Sql Server Management Studio
 2) Go to object Explorer -> Security -> Logins
 3) Right click on the login and select properties
 4) And in the properties window change the default database and click OK.

Alternatively, we can change this by below statement aswell:

Exec sp_defaultdb@loginame='TestUser', @defdb='Test'

Note: Please don’t experiment this on production database and also User should have access to the database to which we are changing.

Please correct me, if my understanding is wrong. Comments are always welcome.