Category Archives: Differences

Difference Between Primary Key and Unique Key In Sql Server

CONSTRAINTS, Differences, Sql Server, , , , , , , , ,

Both PRIMARY KEY and UNIQUE KEY enforces the Uniqueness of the values (i.e. avoids duplicate values) on the column[s] on which it is defined.  Also these key’s can Uniquely identify each row in database table.

[ALSO READ] Difference Between Sql Server VARCHAR and NVARCHAR Data Type

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_CUSTOMER UNIQUE (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 TABLE dbo.Customer
DROP CONSTRAINT UK_CUSTOMER

ALSO READ

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

Differences, Sql Server, , , , ,

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

Difference between Stored Procedure and User Defined Function in Sql Server

Differences, 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 article: Stored 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