All posts by Basavaraj Biradar

Difference Between Sql Server VARCHAR and NVARCHAR Data Type

Below table lists out the major difference between the VARCHAR and NVARCHAR Data Type in Sql Server:

Varchar[(n)] NVarchar[(n)]
Basic Definition Non-Unicode Variable Length character data type.
Example:
DECLARE @FirstName AS VARCHAR(50) =‘BASAVARAJ’
SELECT @FirstName
UNicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Japanese, Korean etc) characters.
Example:
DECLARE @FirstName AS NVARCHAR(50)= ‘BASAVARAJ’
SELECT @FirstName
No. of Bytes required for each character It takes 1 byte per character

Example:
DECLARE
@FirstName AS VARCHAR(50) = ‘BASAVARAJ’
SELECT @FirstName AS FirstName,
DATALENGTH(@FirstName) AS
Length

Result:
FirstName Length
BASAVARAJ 9

It takes 2 bytes per Unicode/Non-Unicode character.
Example:
DECLARE
@FirstName AS NVARCHAR(50)= ‘BASAVARAJ’
SELECT @FirstName AS FirstName,
DATALENGTH(@FirstName) AS Length

Result:
FirstName Length
BASAVARAJ 18
Optional Parameter n range Optional Parameter n value can be from 1 to 8000.Can store maximum 8000 Non-Unicode characters. Optional Parameter n value can be from 1 to 4000.Can store maximum 4000 Unicode/Non-Unicode characters
If Optional Parameter n is not specified in the variable declaration or column definition If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 1.
Example:
DECLARE
@firstName VARCHAR = ‘BASAVARAJ’
SELECT
@firstName FirstName, DATALENGTH(@firstName) Length

Result:
FirstName Length
B 1
If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 1.
Example:
DECLARE @firstName NVARCHAR = ‘BASAVARAJ’
SELECT
@firstName FirstName, DATALENGTH(@firstName) Length

Result:
FirstName Length
B 2
If Optional Parameter n is not
specified in while using
CAST/ CONVERT functions
When this optional parameter n is not specified while using the CAST/CONVERT functions, then it is considered as 30.Example:
DECLARE @firstName VARCHAR(35) =
‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’

SELECT CAST(@firstName AS VARCHAR) FirstName,
DATALENGTH
(CAST(@firstName AS VARCHAR)) Length

Result:

FirstName Length
BASAVARAJ PRABHU BIRADAR INDIA 30
When this optional parameter n is not specified while using the CAST CONVERT functions, then it is considered as 30.Example:
DECLARE @firstName NVARCHAR(35) =
‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’

SELECT CAST(@firstName AS NVARCHAR) FirstName,
DATALENGTH
(CAST(@firstName AS NVARCHAR)) Length

Result:
FirstName Length
BASAVARAJ PRABHU BIRADAR INDIA 60
Which one to use? If we know that data to be stored in the column or variable doesn’t have any Unicode characters. If we know that the data to be stored in the column or variable can have Unicode characters.
Storage Size Takes no. of bytes equal to the no. of Characters entered plus two bytes extra for defining offset. Takes no. of bytes equal to twice the no. of Characters entered plus two bytes extra for defining offset.

As both of these are variable length datatypes, so irrespective of the length (i.e. optional parameter n value) defined in the variable declaration/column definition it will always take the no. of bytes required for the actual charcters stored. The value of n defines maximum no. of characters that can be stored.

[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

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

Difference Between SET QUOTED_IDENTIFIER ON and OFF setting in SQL Server

In this article we will discuss on the difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF. Please go through the article SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server to have detailed information on this setting. As a better practice we should always use SET QUOTED_IDENTIFIERS ON setting.

SET QUOTED_IDENTIFIER ON SET QUOTED_IDENTIFIER OFF
Characters Enclosed within double quotes is treated as Identifier is treated as Literal
Try using Characters Enclosed within double quotes as identifier Works
Example: Below statement to create a table with table name “Table” succeeds.

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo."Table"
(id int,
"Function" VARCHAR(20)) 
GO
Fails
Example: Below statement to create a table with table name “Table” Fails.

SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE dbo."Table"
(id int,
"Function" VARCHAR(20)) 
GO

Error Message:
Msg 102, Level 15, State 1,
Line 1 Incorrect syntax near ‘Table’.

Try using Characters Enclosed within double quotes as Literal. Fails
Example: Below statement fails.

SET QUOTED_IDENTIFIER ON
GO
SELECT "BIRADAR"

Error Message:
Msg 207, Level 16, State 1,
Line 1 Invalid column name ‘BIRADAR’.

Works
Example: Below Statement Works.

SET QUOTED_IDENTIFIER OFF
GO
SELECT "BIRADAR"
Characters Enclosed within single quotes is treated as Literal
Example:

SET QUOTED_IDENTIFIER ON
GO
SELECT 'BIRADAR'
is treated as Literal
Example:

SET QUOTED_IDENTIFIER OFF
GO
SELECT 'BIRADAR'
How to find all the objects which are created with SET QUTOED IDENTIFIER ON/OFF Below Statement can be used to find all the objects created with SET QUTOED_IDENTIFIER setting as ON:

SELECT OBJECT_NAME(object_id) 
FROM sys.sql_modules 
WHERE uses_quoted_identifier = 1
Below Statement can be used to find all the objects created with SET QUTOED_IDENTIFIER setting as OFF:

SELECT OBJECT_NAME(object_id) 
FROM sys.sql_modules
WHERE uses_quoted_identifier = 0
Which One to USE? Best practice is to use the SET QUOTED_IDENTIFIER ON setting. Try to avoid using SET QUOTED_IDENTIFIER OFF setting. As more and more new features require this setting value to be ON. For example while working with Filtered Indexes SET QUOTED_IDENTIFIER setting should be ON otherwise we will get into an exception as demonstrated in the article INSERT/UPDATE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’ ….

[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
Stored Procedure Vs User Defined Function
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity

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

SET QUOTED_IDENTIFIER ON/OFF Setting in Sql Server

While creating Stored Procedures, User Defined Functions etc, most of us use the SET QUOTED_IDENTIFIER ON/OFF  and SET ANSI_NULLS ON/OFF. In this article will discuss on the SET QUOTED_IDENTIFIER ON/OFF Setting.

Depending on the QUOTED_IDENTIFIER setting value either ON or OFF, SQL Server treats the data defined within double quotes or single quotes as differently. If this setting value is ON, then SQL Server treats the value defined within double quotation marks as identifier. The T-SQL rules for the identifier will not apply for these double quoted identifiers, even we can use T-SQL reserved keywords as identifier. So we can use these double quoted identifiers as Stored Procedure name, table name, user defined function name, table name, column names etc.

Let us understand this with an example:

SET QUOTED_IDENTIFIER ON GO
CREATE TABLE dbo."Table"(id int,"Function" VARCHAR(20))
GO
INSERT  INTO dbo."Table" (id,"Function") VALUES (1,'Basavaraj')
GO
SELECT id,"Function" FROM dbo."Table" GO

Result of running the above query is as below:

id          Function
----------- --------------------
1           Basavaraj
(1 row(s) affected)

In the above example we are able to use the T-SQL reserve Keywords “Table” and “Function” as the Table Name and Column Name respectively.

When this setting is ON the data/characters enclosed within single quotation mark is treated as literal. We can’t use the double quoted characters or data as literal. Below example demonstrates this behaviour, in this example the first select statement succeeds where as second select statement fails because it treats the characters enclosed within double quotation mark as identifier.

SET QUOTED_IDENTIFIER ON
 GO
 SELECT 'BASAVARAJ'
 GO
 SELECT "BIRADAR"

Result of running above statement is as below:

---------
BASAVARAJ
(1 row(s) affected)
Msg 207, Level 16, State 1, Line 1 Invalid column name 'BIRADAR'.

On the other hand if SET QUOTED_IDENTIFIER setting is OFF, then SQL Server treats the values defined within single/double quotation mark as literal. Let us understand this with below two simple examples:

Example 1) This example demonstrates that, we can’t use double quoted identifiers when SET QUOTED_IDENTIFIER setting is OFF.

SET QUOTED_IDENTIFIER OFF GO
CREATE TABLE dbo."Table"(id int,"Function" VARCHAR(20))
GO

Result of running the above sql statements:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Table'.

Example 2) This example demonstrates the fact that the values enclosed within either singles or double quotes is treated as literal when SET QUOTED_IDENTIFIER setting is OFF.

SET QUOTED_IDENTIFIER OFF GO
SELECT 'BASAVARAJ' GO
SELECT "BIRADAR"

Result of running the above statement is:

---------
BASAVARAJ
(1 row(s) affected)
-------
BIRADAR
(1 row(s) affected)

Another important point to note is that, the QUOTED_IDENTIFIER setting with which we create Stored Procedur will be stored in the meta data. So, whenever Stored Procedure executes, it will use these setting stored in the meta data. It will ignore the settings of the client or the calling application.

Below query can be used to find the objects which are created with SET QUOTED_IDENTIFIER setting as OFF.

SELECT OBJECT_NAME (object_id) FROM sys.sql_modules
WHERE uses_quoted_identifier = 0 -- 0 means OFF and 1 means ON

You would also like to read the article: Difference Between SET QUOTED_IDENTIFIERS ON and OFF settings.

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

Sparse Columns in SQL Server 2008

Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.

For example: As we know that storing a null/non-null value in a DATETIME column takes 8 bytes. On the other hand Sparse DATETIME column takes no space for storing null value but storing a non-null value will take 12 bytes i.e. 4 bytes extra then normal DATETIME column.

At this moment the obvious question which arises in our mind is: Why 0 bytes for null value and 4 bytes extra for storing non-null value in a sparse column? Reason for this is, sparse column’s value is not stored together with normal columns in a row, instead they are stored at the end of each row as special structure named Sparse Vector. Sparse vector structure contains:

 [List of non-null Sparse Column Id’s – It takes 2 Bytes for each non-null Sparse column] + [List of Column Offsets — It takes 2 bytes for each non-null Sparse column].

So, defining columns with high density of null value as Sparse will result in huge space saving. As explained previously non-null value in the sparse column is stored in a complex structure, so reading non-null sparse column value will have slight performance overhead.

Let us understand the Sparse Column concept with below example.

Example:  In this example we will create two identical tables. Only difference between them is, in one table two columns are marked as Sparse. In both of these tables insert some 25k records and check the space utilization by these tables.

CREATE DATABASE SPARSEDEMO
GO
USE SPARSEDEMO
GO
CREATE TABLE SPARSECOLUMNTABLE
(
 col1 int identity(1,1),
 col2 datetime sparse,
 col3 int sparse
)
CREATE TABLE NONSPARSECOLUMNTABLE
(
 col1 int identity(1,1),
 col2 datetime,
 col3 int
)
GO
INSERT INTO SPARSECOLUMNTABLE VALUES(NULL,NULL)
INSERT INTO NONSPARSECOLUMNTABLE VALUES(NULL,NULL)
GO 25000

 Now check the space used by these tables by executing the below statements:

EXEC SP_Spaceused SPARSECOLUMNTABLE 
EXEC SP_Spaceused NONSPARSECOLUMNTABLE

Result:

name              rows        reserved data   index_size unused
SPARSECOLUMNTABLE 25000       392 KB  344 KB 8 KB       40 KB

name                 rows        reserved data   index_size unused
NONSPARSECOLUMNTABLE 25000       712 KB  656 KB 8 KB       48 KB

So, with above example it is clear that defining a column with high density of null values result’s in huge space saving.

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

Difference between Sql Server Char and Varchar Data Type

Everyone knows about the basic difference between CHAR and VARCHAR data types. In this article apart from the basic difference, will discuss on one more interesting difference which I have observed recently.

CHAR Data Type is a Fixed Length Data Type. For example if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.

On the other hand VARCHAR is a variable length Data Type. For example if you declare a variable/column of VARCHAR (10) data type, it will take the no. of bytes equal to the number of characters stored in this column. So, in this variable/column if you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes. And in this example as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.

Below example illustrates the basic difference explained above:

DECLARE @CharName Char(20) = 'Basavaraj',
  @VarCharName VarChar(20) = 'Basavaraj'
 SELECT DATALENGTH(@CharName) CharSpaceUsed,
  DATALENGTH(@VarCharName) VarCharSpaceUsed

Result:

CharSpaceUsed VarCharSpaceUsed
------------- ----------------
20            9
(1 row(s) affected)

Below is an interesting difference, which I have observed recently while writing some script.

Concatenation of CHAR variables:

DECLARE @FirstName Char(20) = 'Basavaraj',
  @LastName Char(20) = 'Biradar'

IF @FirstName + ' ' + @LastName = 'Basavaraj Biradar' 
 PRINT 'I was Expecting'
ELSE 
 PRINT 'Surprise to me ...'

SELECT  @FirstName + ' ' + @LastName AS Name,
 len(@FirstName + ' ' + @LastName) AS Length

Result:

Surprise to me …
Name                                      Length
—————————————– ———–
Basavaraj            Biradar              28
(1 row(s) affected)

Concatenation of VARCHAR variables:

DECLARE @FirstName VarChar(20) = 'Basavaraj',
 @LastName VarChar(20) = 'Biradar'

IF @FirstName + ' ' + @LastName = 'Basavaraj Biradar' 
 PRINT 'I was Expecting'
ELSE 
 PRINT 'Surprise to me ...'

SELECT  @FirstName + ' ' + @LastName AS Name,
 len(@FirstName + ' ' + @LastName) AS Length

Result:

I was Expecting
Name                                      Length
----------------------------------------- -----------
Basavaraj Biradar                         17
(1 row(s) affected)

So, it is clear from the above examples that during concatenation of CHAR data type variables, it includes space in-place of unused space in the result of concatenation.

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