Differences

Below table lists out the major difference between PRIMARY KEY and UNIQUE KEY:

	PRIMARY KEY	UNIQUE KEY
NULL	It doesn’t allow Null values. Because of this we refer PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT	Allows Null value. But only one Null value.
INDEX	By default it adds a clustered index	By default it adds a UNIQUE non-clustered index
LIMIT	A table can have only one PRIMARY KEY Column[s]	A table can have more than one UNIQUE Key Column[s]
CREATE SYNTAX	Below is the sample example for defining a single column as a PRIMARY KEY column while creating a table: CREATE TABLE dbo.Customer ( Id INT NOT NULL PRIMARY KEY, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY: CREATE TABLE dbo.Customer ( Id INT NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100), City VARCHAR(50), CONSTRAINT PK_CUSTOMER PRIMARY KEY(Id,FirstName) )	Below is the sample example for defining a single column as a UNIQUE KEY column while creating a table:CREATE TABLE dbo.Customer ( Id INT NOT NULL UNIQUE, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) ) Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY: CREATE TABLE dbo.Customer ( Id INT NOT NULL, FirstName VARCHAR(100) NOT NULL, LastName VARCHAR(100), City VARCHAR(50), CONSTRAINT UK_CUSTOMER UNIQUE(Id,FirstName) )
ALTER SYNTAX	Below is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created and doesn’t have any primary key:ALTER TABLE dbo.Customer ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY(Id)	Below is the Syntax for adding UNIQUE KEY CONSTRAINT on a column when the table is already created:ALTER TABLE dbo.Customer ADD CONSTRAINT UK_CUSTOMERUNIQUE (Id)
DROP SYNTAX	Below is the Syntax for dropping a PRIMARY KEY:ALTER TABLE dbo.Customer DROP CONSTRAINT PK_CUSTOMER	Below is the Syntax for dropping a UNIQUE KEY:ALTER TABLEdbo.Customer DROP CONSTRAINT UK_CUSTOMER

Below table lists out the comparative analysis between LEN() and DATALENGTH() Sql Server functions:

LEN()	DATALENGTH()
DEFINITION
The LEN() Sql Server function returns the number of characters in the specified string expression	The DATALENGTH() Sql Server function returns the number of bytes used/required to represent an expression
SYNTAX
LEN (string_expression)	DATALENGTH (expression)
INPUT PARAMETER
Input parameter is a string expression, it can be a constant or variable or column of character or binary data	Input parameter is an expression of any data type
RETURN TYPE
bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int	bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise int
EXAMPLE
SELECT LEN('Shree') AS 'LEN' RESULT:	SELECT DATALENGTH('Shree')  AS 'DATALENGTH' RESULT:
EXCLUDES TRAILING BLANK SPACES?
YES. LEN() function excludes the trailing blank spaces while calculating the number of characters in the specified string expressionEXAMPLE: SELECT LEN('Shree     ')   AS 'LEN' RESULT:	NO. DATALENGTH() function includes the trailing blank spaces while calculating the number of bytes used/required to represent an expressionEXAMPLE: SELECT DATALENGTH('Shree     ')  AS 'DATALENGTH' RESULT:
INPUT PARAMETER IS NULL?
LEN(NULL) is NULL	DATALENGTH(NULL) is NULL
WHEN INPUT IS A UNICODE (i.e. double byte) CHARACTER STRING CONSTANT
For LEN() function it doesn’t matter whether input is a single byte or double byte (i.e. unicode) charcter strings, it always counts the number of characters. EXAMPLE: SELECT LEN(N'Shree') AS  'LEN of Unicode chars' RESULT: Note: The prefix N for any character string denotes that the following charcter string is of Unicode or double byte type	As we know each character in the UNICODE character string requires two bytes of storage. So DATALENGTH of UNICODE character string will be equal to the number characters including spaces multiplied by 2. EXAMPLE: SELECT DATALENGTH(N'Shree') AS  'DATALENGTH of Unicode chars' RESULT:
WHEN INPUT IS A DOUBLE BYTE VARIABLE (EX: NVARCHAR DATATYPE)
DECLARE @Name NVARCHAR(50)  = 'Shree' SELECT LEN(@Name) AS  'LEN of Unicode chars' RESULT:	DECLARE @Name NVARCHAR(50)  = 'Shree' SELECT DATALENGTH(@Name) AS  'DATALENGTH of Unicode chars' RESULT:
WHEN INPUT IS A DOUBLE BYTE VARIABLE (EX: NVARCHAR DATATYPE) HAVING VALUE WITH TRAILING SPACES
DECLARE @Name NVARCHAR(50)  = 'Shree     ' SELECT LEN(@Name) AS  'LEN of Unicode chars' RESULT:	DECLARE @Name NVARCHAR(50)  = 'Shree     ' SELECT DATALENGTH(@Name) AS  'DATALENGTH of Unicode chars' RESULT:
SUPPORTS TEXT, NTEXT AND IMAGE DATA TYPES?
NO. DECLARE @Customer TABLE (TextColumn TEXT) INSERT INTO @Customer VALUES('100') SELECT LEN(TextColumn) AS 'LEN' FROM #t1 RESULT: Msg 8116, Level 16, State 1, Line 7 Argument data type text is invalid for argument 1 of len function.	YES. DECLARE @Customer TABLE (TextColumn TEXT) INSERT INTO @Customer VALUES('100') SELECT DATALENGTH(textcolumn)   AS 'DATALENGTH' FROM #t1 RESULT: DATALENGTH ———– 3

Char Vs Varchar

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

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

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

By using the UNION or UNION ALL operators we can combine multiple result sets into one result set.

UNION OPERATOR (Alias: DISTINCT UNION ORDERED LIST): is used to combine multiple result sets into one result set and will remove any duplicates rows that exist.  Basically it is performing a DISTINCT operation across all columns in the result set.

UNION ALL OPERATOR:  is used to combine multiple result sets into one result set, but it does not remove any duplicate rows.  Because this does not remove duplicate rows this process is faster, but if you don’t want duplicate records you will need to use the UNION operator instead.

Performance TIP:  Compared UNION ALL operator, UNION operator has the extra overhead of removing duplicate rows and sorting results. So, If we know that all the records returned by our query is unique from union then use UNION ALL operator instead of UNION Operator.

 Following are the constraints for using UNION/UNION ALL Operator:

All the query’s which need to combine need to have the same number of columns

Column should be of the same data type/compatible data types

ORDER BY clauses can only be issued for the overall result set and not within each result set

Column names of the final result set will be from the first query

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 ASVARCHAR(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 ASNVARCHAR(50)= ‘BASAVARAJ’ SELECT @FirstName
No. of Bytes required for each character	It takes 1 byte per character Example: DECLARE @FirstName ASVARCHAR(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 ASNVARCHAR(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 nis 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 ASVARCHAR) FirstName, DATALENGTH(CAST(@firstName ASVARCHAR)) 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 ASNVARCHAR) FirstName, DATALENGTH(CAST(@firstName ASNVARCHAR)) 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.

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 @FirstName AS VARCHAR(50)= ‘BASAVARAJ’ SELECT @FirstName	Non-Unicode large Variable Lengthcharacter data type.Example: DECLARE @FirstName ASVARCHAR(Max)= ‘BASAVARAJ’ SELECT @FirstName
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, FirstNameVARCHAR(50)) GO CREATE INDEX IX_EmployeeFirstName ONdbo.Employee(FirstName) 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, FirstNameVARCHAR(Max)) GO CREATE INDEX IX_EmployeeFirstName ONdbo.Employee(FirstName) GO  Error Message: Msg 1919, Level 16, State 1, Line 1 Column ‘FirstName’ 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 @FirstName AS VARCHAR(50)= ‘BASAVARAJ’ SELECT @FirstName ASFirstName, DATALENGTH(@FirstName) AS Length Result: FirstName Length BASAVARAJ 9	It takes 1 byte per characterExample: DECLARE @FirstName ASVARCHAR(MAX)= ‘BASAVARAJ’ SELECT @FirstName ASFirstName, DATALENGTH(@FirstName) AS Length Result: FirstName 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.