IDENTITY Column in SQL Server

What is IDENTITY Function in SQL Server?
How to create IDENTITY Column in SQL Server?
How to explicitly insert Identity Column Data?
How to find all Identity Columns in a Database?
How to Reset Identity Function SEED value?
How to remove Identity Function from Column?
How to add Identity Function after creating the Table?

What is IDENTITY Function in SQL Server?

Generally for any column if we want to insert only unique values then we can make that column as IDENTITY column. It automatically inserts a unique value whenever a new record will be inserted into table. We can have only one IDENTITY column in a table. Generally we use this on Primary Key column. We have to define the IDENTITY Column at the time of Table Creation. After creating a table you can’t use ALTER Command to add IDENTITY function. Once IDENTITY Column is created we can’t use ALTER Command to drop that IDENTITY Function. IDENTITY Column data range will depend on the Column data type. If IDENTITY Value reaches to the Max data range then new record can’t be inserted it will raise error so it’s better to choose a proper data type for the IDENTITY Column. We don’t provide IDENTITY column value in INSERT Statement means we can’t explicitly insert any values into the IDENTITY column, Identity column value automatically gets populated in Identity Column. You can’t perform Insert operation on Identity Column; if you want to do so then you have to set Identity Insert property to ON and you have to define column list in the insert statement, note you can't perform Update operation on Identity column means We can’t update Identity Column value.. In this article I will try to explain every concept with a simple example.


How to create IDENTITY Column in SQL Server?

Syntax to Create Identity Column 

ColumnName <DataType> [Data Width] IDENTITY (SEED,INCR)

SEED – It is the staring value for the IDENTITY Function

INCR – It is the difference between to subsequent values generated by the IDENTITY Function.

Both SEED and INCR are optional, if value is not specified then 1 and 1 will be taken as default

Let’s create an IDENTITY Column

USE TEACHMESQLSERVER
GO

IF OBJECT_ID('DEP','U') IS NOT NULL

DROP TABLE DEP

GO

CREATE TABLE DEP(DEPID INT IDENTITY(1,1), DEPNAME VARCHAR(MAX))

/*We have successfully created IDENTITY Column (DEPID), now lets insert data into DEP table.
*/

Inserting new record in Identity Column

INSERT INTO DEP VALUES (1,'HR')

/*REMEMBER WE CAN'T EXPLICITLY INSERT INTO IDENTITY COLUMN IF IDENTITY_INSERT IS SET TO OFF, THAT IS WHY ABOVE QUERY FAILED WITH BELOW ERROR MESSAGE*/


Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'DEP' can only be specified when a column list is used and IDENTITY_INSERT is ON.

GO
INSERT INTO DEP VALUES ('HR')
GO

SELECT * FROM DEP

/*WE SUCCESSFULLY INSERTED THE RECORD, LET'S INSERT MORE RECORDS INTO DEP TABLE*/

GO

INSERT INTO DEP VALUES ('IT'),('FINANCE'),('MARKETING'),('SALES')

GO

SELECT * FROM DEP



You can see DEPID value was automatically generated by the Identity function and all generated values are unique. DEPID value started with 1 and every new value is just incremented by 1. It is because at the time of declaration we gave SEED value as 1 and INCR value as 1.

How to explicitly insert Identity Column Data?

If you want to Insert values into Identity column then first you have to set identity insert to on and second you have to define list of columns in the insert statement.

SET IDENTITY_INSERT DEP ON

GO

INSERT INTO DEP (DEPID,DEPNAME) VALUES(1000,'FUNCTIONAL')

GO

SELECT * FROM DEP

You successfully inserted a row with Identity column value. If you want Identity function to generate unique value and automatically and insert it into the Identity column then You have to Set Identity insert property to OFF.

SET IDENTITY_INSERT DEP OFF

How to delete records from Identity Table?

You can delete rows without any problem, let’s delete recently added record.

DELETE FROM DEP WHERE DEPID =1000

Note- Delete Command will not reset the Identity Column value, only Truncate can reset Identity column value. Identity column SEED value is always incrementing, it never fill the Gap which were created after deleting the records. Suppose your SEED value is 5 and you inserted a new record that records will be inserted with value 6. If you delete that newly inserted row and reinsert then it will be inserted with value 7 not 6.

How to find all Identity Columns in a Database?

You can easily find all Identity columns list from IDENTITY_COLUMNS System Table using the below query.

SELECT * FROM SYS.IDENTITY_COLUMNS

If you want to find out Identity column Table name for above query then you can pass Object Id in below query.

SELECT Name FROM SYS.OBJECTS WHERE OBJECT_ID=306100131 /*In my case Object id is 306100131, it may differ in your case*/

System Function for Identity column

1- IDENT_CURRENT – It will return the current value of Identity Function


Select IDENT_CURRENT('DEP') Returns 5


2- IDENT_SEED – It will return the SEED Value of Identity Function


Select IDENT_SEED('DEP')Returns 1


3- IDENT_INCR – It will return the INCR value of Identity Function


Select IDENT_INCR('DEP')Retunrs 1

How to Reset Identity Function SEED value?

If you want to reset the SEED Value then you can reset using DBCC CHECKIDENT command. Identity column will allow duplicates values until it was not created with UNIQUE constraint. Suppose we created Identity function on a column that has not UNIQIUE Constraint defined, if we reset SEED value to already existing value in that case it will insert it. When we create Identity function on Primary Key column in that case it will not allow duplicates. We will understand this with example.

Syntax

DBCC CHECKIDENT (TABLENAME, RESEED, NEWVALUE)

Let’s change SEED value to 4 and insert new record

GO

DBCC CHECKIDENT (DEP,RESEED,4)


On successful run you will get below message

Checking identity information: current identity value '5', current column value '4'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

GO
INSERT INTO DEP VALUES('FUNCTIONAL’)
GO

SELECT * FROM DEP




As you can see Identity function taking latest SEED value for new record. You can also see our DEPID has duplicates records it is because DEPID column doesn’t have Primary Key Constraint or Unique Constraint defined.

How to remove Identity Function from Column?

You can’t use ALTER command to Drop Identity Function, but there is work around to handle this.

Drop Identity Column

If you don’t want to keep data then you can drop the column and recreate the column using ALTER command.

Method 1 -

GO

ALTER TABLE DEP DROP COLUMN DEPID
GO
ALTER TABLE DEP ADD DEPID INT
GO
SELECT * FROM DEP

But who wants to lose their data, every single record is important. Below example shows without losing the data we can drop the Identity column.

Let’s Drop DEP table and recreate it with Identity function, because in our previous exercise we dropped the Identity column.

Method 2-

DROP TABLE DEP
GO
CREATE TABLE DEP(DEPID INT IDENTITY(1,1), DEPNAME VARCHAR(MAX))
/*LET'S POPULATE SOME DATA INTO DEP TABLE*/

GO
INSERT INTO DEP VALUES ('IT'),('FINANCE'),('MARKETING'),('SALES')
GO
SELECT * FROM DEP
GO
SELECT * INTO ##DEP FROM DEP /*Copying DEP table data into Temp table DEP*/

GO
DROP TABLE DEP /*Dropping DEP Table*/
GO

CREATE TABLE DEP(DEPID INT,DEPNAME VARCHAR(MAX)) /*Creating DEP Table with same definition but without Identity function on DEPID*/

INSERT INTO DEP SELECT * FROM ##DEP /*Populating Old DEP table data into DEP table from TEMP DEP table*/

DROP TABLE ##DEP /*Dropping Temp DEP table*/

We have successfully dropped the identity column without losing the data. You can confirm this by running the below queries.

GO
SELECT * FROM DEP
GO

SELECT * FROM SYS.IDENTITY_COLUMNS


How to add Identity Function after creating the Table?

You can’t use ALTER command to ADD Identity Function after creating the table, but there is work around to handle this.

In our previous exercise we DROPPED the Identity function from DEPID column; in the exercise we will add Identity Column on existing DEP Table.

GO
SELECT * INTO ##DEP FROM DEP /*Copying DEP table data into Temp table DEP*/
DROP TABLE DEP /*Dropping DEP Table*/
GO

SELECT MAX(DEPID) FROM ##DEP /*Finding Max DEPID and we will use that when we will define IDENTITY FUNCTION*/

GO

CREATE TABLE DEP(DEPID INT IDENTITY(5,1),DEPNAME VARCHAR(MAX)) /*Creating DEP Table with same defintion WITH Identity function on DEPID*/

GO

SET IDENTITY_INSERT DEP ON /*SETTING IDENTITY INSERT ON SO THAT WE CAN INSERT RECORDS INTO THE TABLE*/

GO

INSERT INTO DEP (DEPID,DEPNAME) SELECT * FROM ##DEP /*Copying TEMP DEP table data into REAL DEP TABLE*/

GO

SET IDENTITY_INSERT DEP OFF /*SETTING IDENTITY INSERT OFF SO THAT IDENTITY FUNCTION CAN WORK IN TABLE*/

GO

We have successfully ADDED INDETITY FUNCTION ON DEPID column without losing the data. You can confirm this by running the below queries.

SELECT * FROM DEP
GO
SELECT * FROM SYS.identity_columns


IDENTITY Columns Violation
We all know about the identity columns of SQL Server and how important it is. This article is related to it but I am representing this article to solving a common proble.

First take a quick look about the identity columns and how it works. Then we are going to discuss about the problem and how to solve it programmatically.

Definition of Identity Columns

An IDENTITY column contains a value for each row, generated automatically by Adaptive Server that uniquely identifies the row within the table.

Each table can have only one IDENTITY column. You can define an IDENTITY column when you create a table with a create table or select into statement, or add it later with an alter table statement. IDENTITY columns cannot be updated and do not allow nulls.

You define an IDENTITY column by specifying the keyword identity, instead of null or not null, in the create table statement. IDENTITY columns must have a datatype of numeric and scale of 0. Define the IDENTITY column with any desired precision, from 1 to 38 digits, in a new table:

CREATE TABLE table_name
(column_name INT NOT NULL IDENTITY(1,1))


The Problem comes for Identity

One of my friends told me that, he has a problem for IDENTITY columns. He told me that when he make the INSERT entry into table objects, he never check the uniqueness of the records for that the primary key violation error occurs. He just shows the error.

The problem is the table objects contain identity columns. The value of the identity columns increases each time, even the PK violation exists.

The problem is mentioned by T-SQL statements

Step-1 [ Create the Base Table with IDENTITY columns ]

-- Table defination

DROP TABLE tbl_Example
CREATE TABLE tbl_Example
(
ROLL INT NOT NULL PRIMARY KEY,
SNAME VARCHAR(50) NULL,
SCLASS INT NULL,
ROWNUM INT NOT NULL IDENTITY(1,1)
)


Step-2 [ Normal Insertions of Values ]

INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (1, 'JOYDEEP', 1)


INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (2, 'SUKAMAL', 1)


Step-3 [ See the Output ]

SELECT * FROM tbl_Example

-- Output

ROLL SNAME SCLASS ROWNUM
---- ----- ------ ------
1 JOYDEEP 1 1
2 SUKAMAL 1 2


Step-4 [ Make an Invalid entry that generate an error ]

-- Violation of Primary Key

INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (2, 'RAJESH', 1)


-- Output

Msg 2627, Level 14, State 1, Line 1


Violation of PRIMARY KEY constraint 'PK__tbl_Exam__44C28DB623BDA346'.

Cannot insert duplicate key in object 'dbo.tbl_Example'.

The statement has been terminated.



Step-5 [ Now Make the Correction entry after correction of data ]

-- Correction of Entry

INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (3, 'RAJESH', 1)


Step-6 [ Look the INDENTITY columns has Increased it gives 4 instead of 3 ]

SELECT * FROM tbl_Example


--Output

ROLL SNAME SCLASS ROWNUM
---- ----- ------ ------
1 JOYDEEP 1 1
2 SUKAMAL 1 2
3 RAJESH 1 4

Note that: It is the Normal behavior of the IDENTITY columns. It maintains the uniqueness. Here we are going to break it depends on our needs and it is not good for development. However I am just demonstrating it that we can do it if needed.

Here I am going to make a stored procedure to demonstrate it.

CREATE PROCEDURE my_proc
(
@param_roll INT,
@param_name VARCHAR(50),
@param_calss INT
)

AS

DECLARE @v_NOREC INT
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO tbl_Example
(ROLL,
SNAME,
SCLASS)
VALUES (@param_roll,
@param_name,
@param_calss)

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION

PRINT 'ERROR EXISTS'


SELECT @v_NOREC=COUNT(*) FROM tbl_Example
DBCC CHECKIDENT (tbl_Example, reseed, @v_NOREC)

END CATCH
END

Now we execute it and see the result step by step.

-- Execution-1

EXECUTE my_proc
@param_roll = 1,
@param_name = 'JOYDEEP',
@param_calss = 1

SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1

-- Execution-2


EXECUTE my_proc
@param_roll = 2,
@param_name = 'SUKAMAL',
@param_calss = 1


SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
2 SUKAMAL 1 2

-- Execution-3 [ Primary Key Error ]

EXECUTE my_proc
@param_roll = 2,
@param_name = 'SANGRAM',
@param_calss = 1


0 row(s) affected)

ERROR EXISTS

Checking identity information: current identity value '3', current column value '2'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM
1 JOYDEEP 1 1
2 SUKAMAL 1 2

-- Execution-4

EXECUTE my_proc
@param_roll = 3,
@param_name = 'SANGRAM',
@param_calss = 1

SELECT * FROM tbl_Example
ROLL SNAME SCLASS ROWNUM

1 JOYDEEP 1 1
2 SUKAMAL 1 2
3 SANGRAM 1 3 

 

How to insert rows into table with identity column

Imagine you have two billion rows on employee table, you have to archive this table. After archiving you are going to insert back only 110 million rows to employee table from employee_temp, There is a column emp_id is identity, so how can you insert data back from archived tables. Here you can find the tested solution , first you need to disable an indentity column to auto insert.

Step 1: Make sure to stop all process(application connection) against employee table

Step2 :Create a new table employee_copy from employee table

Find max(emp_id) from employee eg: 2000000001and RESEED this value to employee_copy

DBCC CHECKIDENT (employee_copy , RESEED, 2000000001)

Step3: Rename the orginal table using sp_rename 'employee' , 'employee_temp'

Step4: Rename the new table using sp_rename 'employee_copy' , 'employee'

Step5: Insert the rows from employee_temp table to employee table


SET IDENTITY_INSERT employee ON

GO

INSERT INTO employee (emp_id, emp_name, country_code)

SELECT emp_id, emp_name, country_code from employee_temp(nolock) where emp_id > 110000000

GO

SET IDENTITY_INSERT employee OFF

GO

Views in SQL Server

A View can be thought as either a virtual table or a stored SQL Query in the database. Like a real table has named columns and rows of data, a view also has set of named columns and rows of data but the only difference is View never stores the data like a real table. The data associated with the view is not stored in the view; it is stored in the base table of view. We can create a new view on view. We can also create trigger and Index on view.

Let’s create a database

--CREATE A NEW DATABASE TEACHMESQLSERVER

USE master

GO

IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')

DROP DATABASE TEACHMESQLSERVER

GO

CREATE DATABASE TEACHMESQLSERVER

GO

USE TEACHMESQLSERVER

GO

We have created our database; now will create one table in this database.

-- CREATE A NEW TABLE EMP IN TEACHMESQLSERVER DATABASE

CREATE TABLE EMP(EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX), SALARY MONEY)

GO

INSERT INTO EMP VALUES(1,'A',100),(2,'k',1005),(3,'B',3100),(4,'C',1100),(5,'D',1009),(6,'E',1700),(7,'F',1040),(8,'G',100),(9,'H',1000)

GO

SELECT * FROM SYS.objects WHERE name='EMP'

Now we will create a view on this EMP table.

Syntax to create view:

CREATE VIEW viewname
WITH <View Attributes> -- (This is Optional)
AS
SELECT STATEMENT -- (Your SQL Query, This is mandatory)
WITH CHECK OPTION -- (This is optional)


-- CREATE A VIEW ON THIS EMP TABLE

CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP

You can find this view information from system catalog Views. Click here to know about System Catalog Views

What is stored in the database is a SELECT STATEMENT that you can find using the below query.


GO

SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE BELOW QUERY.

GO

SELECT * FROM SYSCOMMENTS WHERE ID=37575172 -- IN MY CASE IT IS 37575172 BUT IT WILL BE DIFFERENT IN YOUR CASE.

A User can use this view by referencing the view name (EMPVIEW) in SQL statements in the same way a table is referenced.

GO

SELECT * FROM EMPVIEW

If you will run the SELECT STATEMENT (SELECT EMPID,EMPNAME FROM EMP) which was used to create EMPVIEW then you will get the same above result set which means the result set of the Select Statement forms the virtual table which is returned by the view.

The rows and columns are produced dynamically from the base tables (used in the select statement) when the view is referenced.

As you can notice in our EMP table we have three columns EMPID, EMPNAME, SALARY. But when you reference View it only gives you EMPID and EMPNAME. It is because we have not included the SALARY column at the time of CREATE VIEW. So you can say A View acts as a filter on the underlying tables referenced in the view. We didn’t want other users to know about the other EMP’s salary so we didn’t include the Salary column in Create View Statement.

A View can be created from one or more tables or views in the current or other database. There are no restrictions on querying through views but there are few restrictions on modifying data through View, Yes we can perform DML Operation on Views. When you perform any DML operation it affects the referenced tables used in view.


Modify View

You can modify View using ALTER COMMAND

ALTER VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM EMPVIEW
GO


Drop VIEW

If you want to delete view then you can delete it using DROP COMMAND

DROP VIEW EMPVIEW

GO

SELECT * FROM SYS.views WHERE name='EMPVIEW'


We have gone through the basics of view, let’s go through WITH <VIEW ATTRIBUTES> and WITH CHECK OPTION

VIEW ATTRIBUTES

This is optional; we have two attributes ENCRYPTION and SCHEMABINDING. Let’s explore the use of these two attributes one by one.


1- ENCRYPTION

When you create any view without ENCRYPTION in that case you can see view definition in Syscomments system table, below example will explain it


CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP

GO

SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE BELOW QUERY.

GO

SELECT * FROM SYSCOMMENTS WHERE ID=37575172 -- IN MY CASE IT IS 37575172 BUT IT WILL BE DIFFERENT IN YOUR CASE.

You can see view definition from previous query in text column.

If you want to hide that information from the system tables then you have to create view with ENCRYPTION, below example will explain it. Let’s alter the view with ENCRYPTION option.


ALTER VIEW EMPVIEW
With encryption
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO

SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE BELOW QUERY.
GO

SELECT * FROM SYSCOMMENTS WHERE ID=53575229 -- IN MY CASE IT IS 53575229 BUT IT WILL BE DIFFERENT IN YOUR CASE.

check the text column returned from the above query it is showing NULL


2- SCHEMABINDING

Suppose you have create a view which has columns column1, column2, column3, what if user delete column3 from the referenced table. In that case your view will not work it will give error because when we created the view at that time it saved the view definition with all three columns. Now one of the column has been dropped so when we will reference the view it will search for all three columns but it will not find the column3. How can we handle this kind of problem? Yes we can handle this problem what we have to do is we have to create dependencies between view columns and referenced table columns.

Using schemabinding option we create this dependence. So when we create a view with schemabinding option.

The base table or tables can’t be modified in a way that would affect the view definition.

If you want to modify the table then first either you have to drop the view or alter the view without schemabinding option.

When you use Schemabinding , the select statement must include the two part names (schema.object) of tables that are referenced.

We can’t use Select * when we create a view with schemabinding option, we have to specify each column name.

All referenced objects must be in same database.

Let’s go with example to understand the concept, we will alter our previously created view.

GO

DROP VIEW EMPVIEW --If view is already exists

GO

CREATE VIEW EMPVIEW --View Without Schemabinding
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP

GO
SELECT * FROM EMPVIEW

We have create a view without schemabinding, now let’s drop salary column from the referenced table.

GO
ALTER TABLE EMP DROP COLUMN SALARY

You successfully dropped the salary column, now let’s query against our EMPVIEW

Go
SELECT * FROM EMPVIEW

Your view didn't return any result set but it gave below error

Msg 207, Level 16, State 1, Procedure EMPVIEW, Line 3


Invalid column name 'SALARY'.

Msg 4413, Level 16, State 1, Line 1

Could not use view or function 'empview' because of binding errors.

User was able to drop Salary Column from the referenced table because our view was not created with schemabinding option. Now let’s modify the view with schemabinding option.

GO
ALTER VIEW EMPVIEW --View Schemabinding
WITH SCHEMABINDING
AS
SELECT EMPID,EMPNAME FROM DBO.EMP
GO
SELECT * FROM EMPVIEW

We have create a view schemabinding, now let’s drop EMPNAME column from the referenced table.

GO
ALTER TABLE EMP DROP COLUMN EMPNAME

You can’t drop the column because empname is dependent in view empview, you will get below error

Msg 5074, Level 16, State 1, Line 1


The object 'EMPVIEW' is dependent on column 'EMPNAME'.


Msg 4922, Level 16, State 9, Line 1


ALTER TABLE DROP COLUMN EMPNAME failed because one or more objects access this column.

Last time we successfully dropped the salary column because we didn’t use schemabinding for view, but this time we used schemabinding option that is why we were not able to drop the column. If you want to drop the EMPNAME column

Then first you have to either drop the view or alter view with no schemabinding option.

GO
ALTER VIEW EMPVIEW --View Without Schemabinding
AS
SELECT EMPID,EMPNAME FROM DBO.EMP
GO
SELECT * FROM EMPVIEW
GO
ALTER TABLE EMP DROP COLUMN EMPNAME

You successfully dropped the EMPNAME column, now you have to modify your view otherwise it will give error i.e. Invalid column name EMPNAME

Refreshing Views

Suppose you are working with non schema bind view if base table has been modified which affected your view, In that case your view will not work it will give an error. You have to refresh your View definition to make it workable.

There are couple of ways to update View definition, one you can use alter command and update the view definition another is using Stored Procedure you can refresh Views definition.

i.e
Execute sp_refreshview 'Your View Name'


Types of Views in SQL Server

I have already discussed about View in my previous post, In this article I will discuss about different types of view.

In SQL Server Views has been divided into three categories

1- Standard Views
2- Indexed Views
3- Partitioned Views

In this article we will explore Standard Views, To know more about Index Views and Partitioned view click on above links.


Standard Views

We can create view on single table or combination on multiple tables. When we create a view on a single table without using any aggregated function then we call it as Simple View, when we create a view on combination of multiple tables or if any aggregated function (i.e GROUP BY clause, HAVING Clause, Distinct Functions, Group Functions, Functions call) is used on single table then we call it as Complex View.

We can perform DML Operation on Simple view but all Not Null columns must be present in DML command and in View. This means Simple Views are Updatable Views; we will explore this later with a simple example.

We can’t perform DML Operation on Complex view by default; this means Complex Views are Non Updatable Views. We can make complex view updatable with the help of Instead of Trigger. We will explore this with a simple example in this article.

Now we can say Standard Views are of two types.

1- Simple View (Updatable View)
2- Complex View (Non Updatable View)


Simple View (Updatable View) explanation with example

Let’s create a simple view and perform any DML operation

/*I AM USING MY OLD DATABASE, YOU CAN USE YOUR DATABASE OTHERWISE YOU CAN CREATE NEW DATABASE NAME AS TEACHMESQLSERVER USING THE BELOW COMMAND*/

Create DATABASE TEACHMESQLSERVER

GO

Use TEACHMESQLSERVER

Go

/*CREATING TWO TABLES */

Create table DEP (DEPID int primary key,DEPNAME Varchar(max))

go

create table EMP (EMPID int primary key, EMPNAME varchar(max),DEPID int foreign key references Dep(DEPID))

GO

/*INSERTING DATA INTO BOTH TABLES */

INSERT INTO DEP VALUES(10,'HR'),(11,'SALES'),(12,'IT')

GO

INSERT INTO EMP VALUES(1,'GHANESH',10),(2,'PRASAD',11),(3,'GAUTAM',10),(4,'ANVIE',12),(5,'OJAL',12)

GO

/*CREATING A SIMPLE VIEW*/

CREATE VIEW SIMPLEVIEW
AS
SELECT DEPID,DEPNAME FROM DEP
GO

/*REFERENCING VIEW TO FETCH DATA */

SELECT * FROM SIMPLEVIEW

You have successfully created a simple view now let’s perform any DML Operation on SimpleView.


/*DML OPERATION PERFORMING ON SIMPLE VIEW*/

GO
INSERT INTO SIMPLEVIEW VALUES(13,'MARKETING') --INSERTING A NEW RECORD IN SIMPLEVIEW

GO
SELECT * FROM SIMPLEVIEW -- NEW RECORD HAS BEEN ADDED THROUGH VIEW, THIS RECORD WAS ADDED INTO BASE TABLE THAT YOU CAN FIND USING BELOW QUERY

GO
SELECT * FROM DEP -- ANY DML OPERATION PERFORMED ON VIEW MAKES IMPACT ON REFERENCED TABLES(BASE TABLES)

GO
DELETE FROM SIMPLEVIEW WHERE DEPID=13 -- DELETING NEWLY INSERTED RECORD IN PREVIOUS STEP FROM SIMPLEVIEW

GO
SELECT * FROM SIMPLEVIEW

We have successfully inserted and deleted records in simple view in our previous examples but there are couple of point we need to make sure if you want to perform DML operation on simple view.

1- Our view must have all NOT NULL columns and primary key column, otherwise INSERT Command will fail.

2- The columns being modified in the view must not be calculated column or must not have aggregated function.

3- The columns being modified can’t be affected by GROUP BY, HAVING or DISTINCT Clause.


Let’s go through with one example to understand the concept

GO

TRUNCATE TABLE EMP -- TRUNCATING THE TABLE

GO

DELETE FROM DEP -- WE CAN'T USE TRUNCATE COMMAND ON THIS TABLE BECAUSE IT IS BEING REFERENCED WITH FOREIGN KEY IN EMP TABLE , SO USING DELETE COMMAND

GO

ALTER TABLE DEP ADD DEPFULLNAME VARCHAR(MAX) NOT NULL -- ADDING A NEW COLUMN WITH NOT NULL CONSTRAINT

GO

/*INSERTING DATA INTO BOTH THE TABLES*/

INSERT INTO DEP VALUES(10,'HR','HUMAN RESOURCES'),(11,'SALES','SALES'),(12,'IT','INFORMATION TECHNOLOGY')

GO

INSERT INTO EMP VALUES(1,'GHANESH',10),(2,'PRASAD',11),(3,'GAUTAM',10),(4,'ANVIE',12),(5,'OJAL',12)

GO


As you know we have already added a new column DEPFULLNAME which has NOT NULL constraint. This column is not present in our previously created View (SimpleView). Let’s try to insert a new record into SimpleView.

INSERT INTO DEP (DEPID) VALUES (13)
GO

The above query failed and gave below error

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'DEPFULLNAME', table 'TEACHMESQLSERVER.dbo.DEP'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Because behind the picture our view was trying to insert this record in the base table DEP, our base table has three columns DEPID which is primary key, DEPNAME (NULL is allowed) and DEPFULLNAME (NULL is not allowed). Our query was trying to insert NULL records for one of the NOT NULL column (DEPFULLNAME) that is why it gave error.

Let’s ALTER the DEPFULLNAME column to accept NULL and try to run the same query again.


ALTER TABLE DEP ALTER COLUMN DEPFULLNAME VARCHAR(MAX)
GO
INSERT INTO DEP (DEPID) VALUES (13)
GO
SELECT * FROM SIMPLEVIEW
GO
SELECT * FROM DEP

We were able to successfully insert a new record in the base table through view.


Complex View (Non Updatable View) explanation with example

Let’s create a complex view and try to perform any DML operation

CREATE VIEW COMPLEXVIEW
AS
SELECT DEPNAME,EMPNAME FROM DEP INNER JOIN EMP ON DEP.DEPID= EMP.DEPID
GO

SELECT * FROM COMPLEXVIEW

GO

We have successfully created a complex view and you can clearly see data is coming from two different tables in complex view.

As I have already told Complex Views are Non Updatable Views means you can’t perform any DML Operation on complex view. If you don’t trust me you can try the below query to perform insert operation.

INSERT INTO COMPLEXVIEW VALUES(13) -- Failed with error
GO
INSERT INTO COMPLEXVIEW VALUES('MARKETING','RAKESH') -- Failed with error


Error Message

Msg 4405, Level 16, State 1, Line 1


View or function 'COMPLEXVIEW' is not updatable because the modification affects multiple base tables.

Why complex views are non-updatable it is because our complex view have columns from multiple tables and when we try to insert new record through view It can’t understand which column belongs to which table. We can make Complex view updatable by using Instead of Trigger.

Our task is to distribute all columns present in DML Command to their base tables.

Let’s make complex view updatable using Instead of Trigger

We are creating Instead of Trigger (Click here to know about Triggers in SQL Server) on COMPLEXVIEW which takes data from Insert Command and insert into their Base Tables.

GO
CREATE TRIGGER COMPLEWVIEWUPDATABLE ON COMPLEXVIEW

INSTEAD OF INSERT
AS
DECLARE @DEPNAME VARCHAR(MAX),@EMPNAME VARCHAR(MAX)
SELECT @DEPNAME=DEPNAME, @EMPNAME=EMPNAME FROM INSERTED

INSERT INTO DEP (DEPID,DEPNAME)VALUES((SELECT MAX(DEPID)+1 FROM DEP),@DEPNAME)

INSERT INTO EMP (EMPID,EMPNAME) VALUES((SELECT MAX(EMPID)+1 FROM EMP),@EMPNAME)

GO

INSERT INTO COMPLEXVIEW VALUES('MARKETING',’RAKESH’)

GO

This time our same Insert Statement was successful, and it inserted MARKETING into DEP table and RAKESH into EMP Table.

Let’s check

SELECT * FROM EMP
GO
SELECT * FROM DEP

New records were added successfully

We can't use Delete Command on complex view; you have to create Instead of Trigger if you want to perform Delete Operation.

You can Use UPDATE Command on complex view. Let’s take one example to explain this

GO
UPDATE COMPLEXVIEW SET DEPNAME='IT' WHERE DEPNAME='FINANCE'

GO
SELECT * FROM DEP

Indexed View in SQL Server

We have already understood about the standard Views and types of Standard View in my previous post. If you want to know about Standard Views please Click here to know about Standard Views

In this article I will talk about Indexed Views. What is Indexed View?

When we create Index on Simple or Complex view, we call that view as Indexed View. If we create Unique Clustered index on any view that View will be called as Index View. Indexed Views are also known as Materialized view, this means result set of indexed view is computed and stored in the database just like a table with a clustered Index is stored.

If our View is created from multiple table joins and has aggregation that process many records in that case Query performance will be slow. Indexed views can improve query performance drastically.

Indexed view can’t improve query performance for the those queries which are running against a database which has multiple Writes and Updates ,Queries which don’t has join and aggregations.

If you want to create a Clustered Index on your view then you have to make sure that your view was created with Schemabinding option. I have already discussed about the Schemabinding in my previous post Click here to know about Schemabinding option on Views. If your view was not created with Schemabinding option then you can’t create Index on non Schemabinding views.

Let’s take one example to explain the concept.

/*CREATING A NEW DATABASE*/
USE MASTER

IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')

DROP DATABASE TEACHMESQLSERVER

GO

CREATE DATABASE TEACHMESQLSERVER



/*CREATING TWO NEW TABLES AND POPLUTAING DATA*/

GO
USE TEACHMESQLSERVER
GO

CREATE TABLE DEP (DEPID INT PRIMARY KEY,DEPNAME VARCHAR(MAX))

GO

CREATE TABLE EMP (EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX),DEPID INT FOREIGN KEY REFERENCES DEP(DEPID))

GO

INSERT INTO DEP VALUES(10,'HR'),(11,'SALES'),(12,'IT')

GO

INSERT INTO EMP VALUES(1,'GHANESH',10),(2,'PRASAD',11),(3,'GAUTAM',10),(4,'ANVIE',12),(5,'OJAL',12)



/*CREATING A NEW COMLEX VIEW*/

GO

IF EXISTS(SELECT NAME FROM SYS.VIEWS WHERE NAME='COMPLEXVIEW')

DROP VIEW COMPLEXVIEW

GO

CREATE VIEW COMPLEXVIEW
WITH SCHEMABINDING
AS

SELECT DBO.EMP.EMPID,EMPNAME,DEPNAME FROM DBO.DEP INNER JOIN DBO.EMP ON DBO.DEP.DEPID= DBO.EMP.DEPID


/*REFERENCEING COMPLEX VIEW*/

GO
SELECT * FROM COMPLEXVIEW


/*CREATING UNIQUE CLUSTERED INDEX*/

GO
CREATE UNIQUE CLUSTERED INDEX INDEXEDCOMPLEXVIEWIN ON COMPLEXVIEW (EMPID ASC)

Congratulations you have successfully created a Indexed view. If you want to check this you can check from below queries


GO
SELECT * FROM SYS.all_views WHERE NAME='COMPLEXVIEW'

/*COPY OBJECT_ID FROM PREVIOUS QUERY AND PASS IT INTO BELOW QUERY*/

GO
SELECT * FROM SYS.indexes WHERE OBJECT_ID=101575400

You can find it using below query also

GO

SELECT * FROM SYS.indexes WHERE name='INDEXEDCOMPLEXVIEWIN'


Partitioned View in SQL Server

In this article I will talk about Partitioned Views and what are the types of Partitioned View?

Partitioned View allows a large table to be split horizontally into many smaller member tables. The data is distributed among member tables based on range of data values in one of the columns from our Main table for example My Sales table has millions of records for 2013, This sales table stores monthly sales transaction records. I can distribute 12 months data into 12 different member tables (one for each month). These 12 tables will be called as member tables. The data range for reach member table is defined with a CHECK constraint on the Month column (Partitioned Column). You have to make sure structure of all member tables is same.





Now partitioned view will be created by using UNION ALL on all member tables and it appears as a single Sales Table to the end users. When we execute Select statements against our Partitioned view with a search condition using where clause on partition column (Month name is our case), the query optimizer use the CHECK constraint definitions to find which member tables contains the searched data.

Partitioned Views are of two types

1- Local Partitioned View
2- Distributes Partitioned View

Local Partitioned View- View that joins member tables from same instance of SQL Server will be called as local Partitioned view

Distributed Partitioned View- View that joins member tables from multiple different SQL Servers will be called as Distributed Partitioned View

Let’s understand the concept using a simple example


/*CREATING A NEW DATABASE*/

USE MASTER

IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')

DROP DATABASE TEACHMESQLSERVER

GO

CREATE DATABASE TEACHMESQLSERVER


/*CREATING THREE NEW TABLES AND POPLUTAING DATA*/

GO
USE TEACHMESQLSERVER

GO

CREATE TABLE DEP (DEPID INT PRIMARY KEY,DEPNAME VARCHAR(MAX))

GO

CREATE TABLE EMP (EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX),DEPID INT FOREIGN KEY REFERENCES DEP(DEPID))

GO

CREATE TABLE SALES(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT, SALESMONTH VARCHAR(10),EMPIDINT )

GO

INSERT INTO DEP VALUES(10,'HR'),(11,'SALES'),(12,'IT')

GO

INSERT INTO EMP VALUES(1,'GHANESH',10),(2,'PRASAD',11),(3,'GAUTAM',10),(4,'ANVIE',12),(5,'OJAL',12)

GO

INSERT INTO SALES VALUES(1,100,2013,'JAN',1),(2,1000,2013,'JAN',5),(3,1050,2013,'JAN',1),(4,13400,2013,'FEB',3),(5,1010,2013,'FEB',1),(6,10230,2013,'MAR',2),

(7,89998,2013,'MAR',3),(8,102320,2013,'MAR',1),(9,11100,2013,'MAR',2),(10,10430,2013,'APR',1),(11,10,2013,'APR',4),(12,1908700,2013,'MAY',3),(13,10320,2013,'JUN',1)

,(14,100,2013,'JUN',1),(15,109980,2013,'JUL',4),(16,1590,2013,'AUG',5),(17,90000,2013,'AUG',1),(18,9100,2013,'SEP',2),(19,1000,2013,'OCT',1),(20,1009,2013,'NOV',5),(21,100,2013,'DEC',4)


/*PARTITIONING SALES TABLE DATA INTO 12 MEMBER TABLES ON MONTH PARTITIONED COLUMN*/

CREATE TABLE SALESJAN(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='JAN'),EMPID INT )

CREATE TABLE SALESFEB(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='FEB'),EMPID INT )

CREATE TABLE SALESMAR(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='MAR'),EMPID INT )

CREATE TABLE SALESAPR(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='APR'),EMPID INT )

CREATE TABLE SALESMAY(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='MAY'),EMPID INT )

CREATE TABLE SALESJUN(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='JUN'),EMPID INT )

CREATE TABLE SALESJUL(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='JUL'),EMPID INT )

CREATE TABLE SALESAUG(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='AUG'),EMPID INT )

CREATE TABLE SALESSEP(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='SEP'),EMPID INT )

CREATE TABLE SALESOCT(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='OCT'),EMPID INT )

CREATE TABLE SALESNOV(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='NOV'),EMPID INT )

CREATE TABLE SALESDEC(SALESID INT PRIMARY KEY, SALESAMOUNT MONEY, SALESYEAR INT CHECK(SALESYEAR=2013),SALESMONTH VARCHAR(10) CHECK(SALESMONTH='DEC'),EMPID INT )

/*POPULATINF DATA INTO MEMBER TABLES*/

GO
INSERT INTO SALESJAN SELECT *FROM SALES WHERE SALESMONTH='JAN'

INSERT INTO SALESFEB SELECT *FROM SALES WHERE SALESMONTH='FEB'

INSERT INTO SALESMAR SELECT *FROM SALES WHERE SALESMONTH='MAR'

INSERT INTO SALESAPR SELECT *FROM SALES WHERE SALESMONTH='APR'

INSERT INTO SALESMAY SELECT *FROM SALES WHERE SALESMONTH='MAY'

INSERT INTO SALESJUN SELECT *FROM SALES WHERE SALESMONTH='JUN'

INSERT INTO SALESJUL SELECT *FROM SALES WHERE SALESMONTH='JUL'

INSERT INTO SALESAUG SELECT *FROM SALES WHERE SALESMONTH='AUG'

INSERT INTO SALESSEP SELECT *FROM SALES WHERE SALESMONTH='SEP'

INSERT INTO SALESOCT SELECT *FROM SALES WHERE SALESMONTH='OCT'

INSERT INTO SALESNOV SELECT *FROM SALES WHERE SALESMONTH='NOV'

INSERT INTO SALESDEC SELECT *FROM SALES WHERE SALESMONTH='DEC'

GO

/*CREATING PARTITIONED VIEW*/

GO
IF EXISTS(SELECT NAME FROM SYS.VIEWS WHERE NAME='PARTITIONEDVIEW')

DROP VIEW PARTITIONEDVIEW

GO

CREATE VIEW PARTITIONEDVIEW
AS
SELECT * FROM SALESJAN

UNION ALL

SELECT * FROM SALESFEB

UNION ALL

SELECT * FROM SALESMAR

UNION ALL

SELECT * FROM SALESAPR

UNION ALL

SELECT * FROM SALESMAY

UNION ALL

SELECT * FROM SALESJUN

UNION ALL

SELECT * FROM SALESJUL

UNION ALL

SELECT * FROM SALESAUG

UNION ALL


SELECT * FROM SALESSEP

UNION ALL

SELECT * FROM SALESOCT

UNION ALL

SELECT * FROM SALESNOV

UNION ALL

SELECT * FROM SALESDEC



/*REFERENCEING PARTITIONED VIEW, PLEASE PRESS CTRL+M BEFORE RUNNING THE BELOW QUERY, IT WILL SHOW THE RESULTSET WITH THE EXECUTION PLAN*/

GO
SELECT * FROM PARTITIONEDVIEW WHERE SALESMONTH ='JAN'

Execution plan for above query:

The SQL Server query optimizer recognizes that the search condition in this SELECT statement references only rows in the SALESJAN table. Therefore, it limits its search to SALESJAN table.




Don’t forget to drop the database, I hope you enjoyed the article.


WITH CHECK OPTION in View

I have already discussed about Views in my previous post. In this article we will know the use of WITH CHECK OPTION on Views.

When we create a view without CHECK Option, in that case we can perform INSERT, UPDATE, DELETE operation on View. If records are not in scope of View and we want to perform DELETE and UPDATE operation on those records then DELETE and UPDATE operation will be succeed without any modification in the base table but INSERT operation will succeed and it will insert new records in base table.

When we create a view WITH CHECK OPTION, in that case we can’t perform INSERT, UPDATE, DELETE operation on those records which are not satisfying the criteria set within the select statement of View. If we try to perform DELETE and UPDATE operation on out of scope records then the operation will be succeed without any modification in the base table.

Let’s go through a simple example


USE TEACHMESQLSERVER
GO

-- Creating a new table and popluating data into the table

IF OBJECT_ID('EMP','U') IS NOT NULL

DROP TABLE EMP

GO

CREATE TABLE EMP( EMPID INT, EMPNAME VARCHAR(MAX))

GO

INSERT INTO EMP VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E')

GO


-- Creating a View without CHECK Option

IF OBJECT_ID('EMPVIEW','V') IS NOT NULL

DROP VIEW EMPVIEW

GO

CREATE VIEW EMPVIEW

AS

SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5

GO

SELECT * FROM EMPVIEW


--Performing DML Operation on VIEW

GO

INSERT INTO EMPVIEW VALUES(6,'F') -- Successfully Inserted new record in Base Table

GO

SELECT * FROM EMP

GO

UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5

GO

DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5

GO

SELECT * FROM EMP


WITH CHECK OPTION EXAMPLE

-- ALTERING the View WITH CHECK OPTION

GO

ALTER VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP WHERE EMPID <5
WITH CHECK OPTION


--Performing DML Operation on VIEW

GO
UPDATE EMPVIEW SET EMPNAME='G' WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5

GO
DELETE FROM EMPVIEW WHERE EMPID=6 -- Successfully executed without any modification in Base Table, because this record is out of scope in EMPVIEW.EMPVIEW has all records where EMPID<5

GO
INSERT INTO EMPVIEW VALUES(7,'H') -- Failed with below error, because the INSERT statement is not satisfying the Cretria set in Select Statement, VIEW can have all records where EMPID<5 in this case EMPID is greater than 5 so it failed.

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

GO

INSERT INTO EMPVIEW VALUES(0,'I') -- Successfully Inserted new record in Base Table because it satisfied EMPVIEW Criteria.

GO

SELECT * FROM EMP

Database Views| MSSQL Views| SQL Server Views| Syntax of Database Views| Types of Views

A view may be thought of as a virtual table, that is, a table that does not reallyexist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead adefinition of view is stored in data dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.SyntaxCREATE VIEW [<> . ] view_name [ ( column [ ,...n ] ) ] [ WITH <> [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ]
<> ::= { ENCRYPTION SCHEMABINDING VIEW_METADATA }
Note: Column is for statements, which contain aggregate functions
Encryption: Code View is encrypted by using this.
Schemabinding: The view is binded with the underlying table structure. Suppose you drop a column of the base table, normally the view gives an error that there is no such column name But if you specify With Schemabinding, then we will not be able to drop the column as it is being accessed by other objects.
View_MetaData: Returns the meta data information about the view.
Check Option: Suppose we create a view with a query, which has where condition. For this view, if the user tries to insert the data that does not match the query then its of no use as that is not shown in the result. So that means, we should give access only to insert records which meets the query criteria. Hence WITH CHECK OPTION is used. The result would be an error for the situation above.A CREATE VIEW statement cannot:Include ORDER BY clause, unless there is also a TOP clause (remember that a view is nothing else but a virtual table, why would we want to order it?)
Include the INTO keyword.
Include COMPUTE or COMPUTE BY clauses.
Reference a temporary table or a table variable.
Select statement can use multiple SELECT statements separated by UNION or UNION ALL.Restrictions for updating data:
A view cannot modify more than one table. So if a view is based on two or more tables, and you try to run a DELETE statement, it will fail. If you run an UPDATE or INSERT statement, all columns referenced in the statement must belong to the same table.
It’s not possible to update, insert or delete data in a view with a DISTINCT clause.
You cannot update, insert or delete data in a view that is using GROUP BY.
It’s not possible to update, insert or delete data in a view that contains calculated columns.

Types:
Indexed Views: Indexed views work best for queries that aggregate or compute columns in the table. The disadvantage of indexed views is that it will slow down a query that updates data in the underlying tables.
Partitioned Views: When you need to write a join query with tables that are in different databases, then we use partitioned views.

View

OVERVIEW

A virtual entity of DB which looks like a table but does not store the records physically.
It is something like window over a table
Complex query can be converted into view and can be used in FROM clause
A table can be dropped even though view is existing which is using this table. However view created with SCHEMABINDING option won’t allow dropping a table
All DML operations are possible on View but again it has some limitations.
It shouldn't violate any constraint like foreign key, unique, identity, not null, etc.
View should point to only 1 table and not multiple tables
View should not have GROUP by clause or any arithmetic functions
Won’t allow SELECT INTO, ORDER BY
We can use ORDER BY but it should have TOP operator then only we can use it
Index can be created on view but with following conditions. If we do this, view will start storing the data physically
View should be schemabound
First we have to create unique clustered index then only we can create other non-clustered indexes
Creating index on view is sort of alternate solution of partition.


SYNTAX

Basic Syntax

CREATE VIEW vwName
AS
SELECT QUERY

With Schemabinding syntax

CREATE VIEW vw
WITH SCHEMABINDING
AS
SELECT eid, ename , salary, dob
FROM dbo.Employee
WHERE Salary > 10000

Common Error in case of Materialized View


Introduction
In this article we are trying to discuss about some common error related to materialize view. To understand the nature of the Error we are going to take an simple example. Hope it will be informative.

Example

Step-1 [ Creating the Base Table ]


IF OBJECT_ID(N'tbl_StudentDetails', N'U') IS NOT NULL

DROP TABLE tbl_StudentDetails;


CREATE TABLE tbl_StudentDetails
(
StdRoll INT NOT NULL IDENTITY PRIMARYKEY,
StdName VARCHAR(50) NOT NULL
);

GO


Step-2 [ Insert some records in the Base Table ]


INSERT INTO tbl_StudentDetails
(StdName)
VALUES ('Joydeep Das'), ('Santinath Mondal'), ('Avijit da');
GO


Step-3 [ Now Create a Simple View from the Base table ]

CREATE VIEW view_StudentDetails
AS
SELECT * FROM tbl_StudentDetails;

We find that the view is Created Successfully and there is no problem in case of simple view creation.


Step-4 [ Now Create a Materialized View with above Example ]

CREATE VIEW view_StudentDetails WITH SCHEMABINDING
AS
SELECT * FROM tbl_StudentDetails;

The error occurred:

Msg 1054, Level 15, State 6, Procedure view_StudentDetails, Line 26


Syntax '*' is not allowed in schema-bound objects.

So we are not providing star (*) in Materialized view and we must provide the column name instead of star (*).

Try to fix it by providing the column name in SELECT statement of materialized view.

CREATE VIEW view_StudentDetails WITH SCHEMABINDING
AS
SELECT StdRoll, StdName FROM tbl_StudentDetails;


Again another Error occurs:

Msg 4512, Level 16, State 3, Procedure view_StudentDetails, Line 33


Cannot schema bind view 'view_StudentDetails' because name 'tbl_StudentDetails' is

invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Now the above error says that we must provide the Schema name with Table Name in case of materialized view.


CREATE VIEW view_StudentDetails WITH SCHEMABINDING
AS
SELECT StdRoll, StdName FROM dbo.tbl_StudentDetails;

Now it’s perfect and complied successfully.


Summary:

Form the above example we find that the in case of materialized view we must provide the column name within the SELECT statement and also provide the schema name in the Table object.

SELECT StdRoll, StdName FROM dbo.tbl_StudentDetails;

DELETE restrictions of VIEWS

One of my friends told me that, he has a VIEW from a single base table and the user of the view is always trying to DELETE the records from VIEW. So the underlying TABLE records are also deleted for deletion actions of the view. He wants to restrict the deletions of records from VIEW.

The article is related to the DELETE restrictions of VIEWS. We can easily do it by REVOKING the DELETE permissions from the VIEW objects. But my friends have no permission over GRANT and REVOKE.

So the solution is given by using the INSTEAD OF DELETE Trigger. Here I am not going to describe the definition of it.

To know more about it, just go to my previous article.

FOR | AFTER | INSTEAD OF

http://sqlknowledgebank.blogspot.in/2012/03/for-after-instead-of.html

Rather I just provide a simple self explanatory example to demonstrate it


-- Base Table

IF OBJECT_ID('emp_data') IS NOT NULL
BEGIN
DROP TABLE emp_data
END

GO

CREATE TABLE emp_data
(
IDNO INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
EMPGRADE VARCHAR(1) NOT NULL,
CONSTRAINT CHK_emp_data_EMPGRADE
CHECK (EMPGRADE IN('A','B','C'))
)

GO

-- Insert records

INSERT INTO emp_data
(EMPNAME, EMPGRADE)
VALUES ('Sudip Das', 'A'),
('Joydeep Das', 'B'),
('Sangram Jit', 'B'),
('Tuhin Shinah', 'B'),
('Bhola', 'C')


-- Display records

SELECT IDNO, EMPNAME, EMPGRADE
FROM emp_data

-- Output

IDNO EMPNAME EMPGRADE

1 Sudip Das A
2 Joydeep Das B
3 Sangram Jit B
4 Tuhin Shinah B
5 Bhola C

-- Creation of VIEWS

IF OBJECT_ID('view_emp_data') IS NOT NULL
BEGIN
DROP VIEW view_emp_data
END

GO

CREATE VIEW view_emp_data
AS
SELECT IDNO, EMPNAME, EMPGRADE
FROM emp_data

GO


-- Display the Records of Views

SELECT IDNO, EMPNAME, EMPGRADE
FROM view_emp_data

-- Output from View

IDNO EMPNAME EMPGRADE

1 Sudip Das A
2 Joydeep Das B
3 Sangram Jit B
4 Tuhin Shinah B
5 Bhola C


-- Protect DELETION From VIEWS

IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL
BEGIN
DROP TRIGGER trg_RestrictDEL
END

GO


CREATE TRIGGER trg_RestrictDEL ON view_emp_data
INSTEAD OF DELETE
AS
BEGIN
IF @@rowcount > 0

BEGIN

RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16,2 )

ROLLBACK

END

END

GO


-- Now try to delete records from views

DELETE view_emp_data
WHERE IDNO=1

-- Output generated for DELETE Action

Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 8


Rows from Views Named: view_emp_data, CANNOT be DELETED!


Msg 3609, Level 16, State 1, Line 1


The transaction ended in the trigger. The batch has been aborted.


Why VIEW takes long time to Execute




Here in this article we are discussing related to the performance of the views. Why the performance of the view is slow. Please note that I am not taking about the indexed views, it's another thing and out of the scope of this article.

Like stored procedure, the optimizers cache the execution plan in the case for further use.

Let's take an example:

We have a simple base table contains records and we just create a view from this base table objects and then execute both (Base table and View separately)


CREATE TABLE emp_Table
(empid INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
empname VARCHAR(50) NOT NULL,
empsal DECIMAL(20,2))

GO


CREATE VIEW vw_empView
AS
SELECT empid, empname, empsal
FROM emp_Table

GO

-- Example-1

SELECT * FROM emp_Table
GO

-- Example-2

SELECT * FROM vw_empView
GO

Here in this example which one is executed faster?

It's Example-1. That means direct query from Table objects. To get the better understanding related to execution we just look at the execution plan.





Both execution plans are same. So, why the execution of views take long time?

This is because it takes SQL Server extra work such as looking up data in the system tables before it can execute the view.

This extra work is not part of the execution plan, so it appears that the two SELECT statements should run at the same speed, which they don't, because some of the work SQL Server is doing is hidden.


Can Views take the Input Parameters
One of my friends is trying to passing some values in views. He told me that is there any options in SQL server to pass the parameters in views.

The answer is NO. It is not possible at any version of the SQL server as the view is not build for that purpose.

But we have others ways to do that, and we can do it very easily by table value functions. Here in this article I am trying to demonstrate it easily by taking an example.

-- My base table

CREATE TABLE my_Emplyee
(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EMP_NAME VARCHAR(50) NULL,
SAL DECIMAL(20,2)NOT NULL)

-- Inserting so records on it

INSERT INTO my_Emplyee
(EMP_NAME, SAL)
VALUES('Sukamal jana', 40000),
('Manisankar Year', 30000),
('Tuhin Shina', 40000),
('Sabgram jit', 30000),
('Subrata Kar', 20000),
('Debojit Ganguli', 20000)


-- Display records

SELECT * FROM my_Emplyee

Output

ID EMP_NAME SAL

1 Sukamal jana 40000.00
2 Manisankar Year 30000.00
3 Tuhin Shina 40000.00
4 Sabgram jit 30000.00
5 Subrata Kar 20000.00
6 Debojit Ganguli 20000.00

I am again mentioned that the parameters cannot be passed in views.

Now I am moving to the solution of that by table value function and passing some parameters to get the desired result set.

IF OBJECT_ID (N'fn_EMP_VIEW') IS NOT NULL
DROP FUNCTION dbo.fn_EMP_VIEW
GO


CREATE FUNCTION dbo.fn_EMP_VIEW
(@p_Sal DECIMAL(20,2))
RETURNS TABLE
AS RETURN
(
SELECT *
FROM my_Emplyee
WHERE SAL>=@p_Sal
)
GO

We want to display the employee details that have salary 40,000 or more than that.

-- Execute it

SELECT *
FROM dbo.fn_EMP_VIEW(40000)


Output

ID EMP_NAME SAL

1 Sukamal jana 40000.00
3 Tuhin Shina 40000.00


Materialize view and the performance issue

In this article I am trying to illustrate some points related to materialize view and the performance issue.

Views are the good example to handle the complex query.

Thinks about the situations like this

If a view performs an aggregation of millions of rows, query performance of the view decreases dramatically and this operation is performed many times every day, the database system must access millions of rows repeatedly.

The solutions is the materialized view

A new data structure must be defined to deal with this kind of scenario. A materialized view is defined just as a regular view but the result set of the query is stored as persistent data object such as table which is frequently updated from the underlying base tables when changes occur. They are useful to aggregate data in business intelligence applications with complex queries.


How I implement it

SET STATISTICS TIME ON

It displays the number of milliseconds required to parse, compile, and execute each statement.

Now execute the Query mentioned bellow:

SELECT p.ProductID, sum(t.ActualCost), sum(t.Quantity)
FROM Production.TransactionHistory t
INNER JOIN Production.Product p on t.ProductID=p.ProductID
GROUP BY p.ProductID;

It takes 42334 microseconds to execute.

To improve the response time, our strategy is to implement the materialize view.

To implements we must follows this steps.

1. Create a normal view
2. Include WITH SCHEMABINDING Options
3. Make a clustered index on this view


Step-1 and 2

CREATE VIEW view_totCostQtyByProd
WITH SCHEMABINDING
AS
SELECT p.ProductID, sum(t.ActualCost), sum(t.Quantity)
FROM Production.TransactionHistory t
INNER JOIN Production.Product p ON t.ProductID=p.ProductID
GROUP BY p.ProductID;

Step-3

CREATE UNIQUE CLUSTERED INDEX Indx_TotCostQtyByProd
ON view_totCostQtyByProd(ProductID)

Now execute the view

SELECT *
FROM view_totCostQtyByProd;

It takes just 32 milliseconds, dramatically improve the performance.

Summary

So we understand that the creating materialized views dramatically improve the performance of the executions.


About VIEW

So what is a VIEW?

A view is a virtual table that consists of columns from one or more tables. Though it is similar to a table, it is stored in the database. It is a query stored as an object. Hence, a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables.

Once you have defined a view, you can reference it like any other table in a database.

A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

Views ensure the security of data by restricting access to the following data:
Specific rows of the tables.
Specific columns of the tables.
Specific rows and columns of the tables.
Rows fetched by using joins.
Statistical summary of data in a given tables.
Subsets of another view or a subset of views and tables.

Some common examples of views are:
A subset of rows or columns of a base table.
A union of two or more tables.
A join of two or more tables.
A statistical summary of base tables.
A subset of another view, or some combination of views and base table

Type of VIEWS

There are 3 type of view that we can create.

Standard View

Combining data from one or more tables through a standard view lets you satisfy most of the benefits of using views. These include focusing on specific data and simplifying data manipulation. Indexed Views

Indexed View

An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.

Partitioned Views

A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.


SQL CREATE VIEW Examples

If you have the Northwind database you can see that it has several views installed by default.

The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the following SQL:

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued='No'


We can query the view above as follows:

SELECT * FROM [Current Product List]

Another view in the Northwind sample database selects every product in the "Products" table with a unit price higher than the average unit price:

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

We can query the view above as follows:

SELECT * FROM [Products Above Average Price]

Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called "Product Sales for 1997":


CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

We can query the view above as follows:

SELECT * FROM [Category Sales For 1997]

We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No


SQL Dropping a View


You can delete a view with the DROP VIEW command.


SQL DROP VIEW Syntax

DROP VIEW view_name

Special NOTE:

Remember the ORDER BY clause is not directily used in the view. For example if you want to create a view and used order by clause it gives you and error msg.


Msg 1033, Level 15, State 1, Procedure abc, Line 3


The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

But you can still use ORDER BY with view by TOP 100 PERCENT like this

CREATE VIEW [ViewName]
AS
SELECT TOP 100 PERCENT itemcode, itemdescr
FROM item_master ORDER BY itemdescr


Indexed View

Views allow you to create a virtual table by defining a query against one or more tables. With a standard view, the result is not stored in the database. Instead, the result set is determined at the time a query utilizing that view is executed.

Creating a unique clustered index on a view changes it to an indexed view. The clustered index is stored in SQL Server and updated like any other clustered index, providing SQL Server with another place to look to potentially optimize a query utilizing the indexed view.

Queries that don't specifically use the indexed view can even benefit from the existence of the clustered index from the view. In the developer and enterprise editions of SQL Server, the optimizer can use the indexes of views to optimize queries that do not specify the indexed view. In the other editions of SQL Server, however, the query must include the indexed view and specify the hint NOEXPAND to get the benefit of the index on the view.

If your queries could benefit from having more than one index on the view, non-clustered indexes can also be created on the view. This would supply the optimizer with more possibilities to speed up the queries referencing the columns included in the view.


Where to Use Them

Indexed views have both a benefit and a cost. The cost of an indexed view is on the maintenance of the clustered index (and any non-clustered indexes you may choose to add). One must weigh the cost to maintain the index against the benefit of query optimization provided by the index. When the underlying tables are subject to significant inserts, updates, and deletes, be very careful in selecting the indexes (both table and view) that will provide the greatest coverage across your queries for the lowest cost.

Typically, environments that are best suited for indexed views are data warehouses, data marts, OLAP databases, and the like. Transactional environments are less suitable for indexed views. Look for repeating joins utilizing the same columns, joins on large tables, aggregations on large tables, and repeating queries as potential candidates for indexed views. Be careful of creating indexed views where the result set contains more rows than the base tables as this will be counterproductive.


How to Create Them

A view that is to be indexed has to be created with schema binding. This means that once the indexed view is created, the underlying tables cannot be altered in any way that would materially affect the indexed view unless the view is first altered or dropped. It also means that all the tables referenced in the view must be referenced by their two-part name (schemaname.tablename).

Below is an example of the CREATE statement for an indexed view, MyView, and its underlying table, MyBigTable. The table is first created, then the view that references two of the table's three columns, and finally the unique clustered index on the view making it an indexed view.

CREATE TABLE MyBigTable(
ItemID INT PRIMARY KEY,
ItemDsc VARCHAR(20),
QTY INT)

GO

CREATE VIEW MyView WITH SCHEMABINDING AS
SELECT ItemID, QTY
FROM dbo.MyBigTable
WHERE QTY > 10

GO
CREATE UNIQUE CLUSTERED INDEX idx_MyView ON MyView(QTY)

Once this index is created, the result set of this view is stored in the database just like any other clustered index. Any query that explicitly uses the view will be able to take advantage of the index on the view. Queries that contain a predicate similar to the view and that fall into the range defined by the view may also reap the optimization rewards of having that index available (assuming the execution cost is non-trivial). Consider the following query:

SELECT ItemID
FROM MyBigTable
WHERE QTY > 30

Even though the query does not use the indexed view, the optimizer has the option of using the clustered index created on the view if it provided better performance than the clustered or non-clustered indexes on the base table.

If you want the optimizer to always choose the indexed view over the base tables when optimizing a query containing an index view, you must use the hint NOEXPAND. Conversely, if you'd like to see how a query containing an indexed view would perform utilizing the base tables instead, you can specify the option EXPAND VIEWS, thus saving you the time substituting the base tables yourself.

Constraints

An index cannot be created on just any view. Several constraints exist that a view must meet in order for the index creation to be successful. We discussed WITH SCHEMABINDING and two-part table names above. Here are some other constraints.
The view must have been created with certain SET options, such as QUOTED_IDENTIFIER and CONCAT_NULL_YIELDS_NULL set to ON.
The session creating the index must also have the correct SET options.
Any user-defined functions referenced by the view must have been created using WITH SCHEMABINDING.
The view must be deterministic (consistently providing the same result given the same input).
The base tables must have been created with the proper ANSI_NULLS setting.
The result set of the view is physically stored in the database, thus storage space for the clustered index is also a constraint to consider.

In addition to this, there are constraints on the contents of the view. For instance, the view may not contain EXISTS or NOT EXISTS, OUTER JOIN, COUNT(*), MIN, MAX, sub queries, table hints, TOP, UNION, and much more. Check the SQL Server Development Center on MSDN for a complete listing.

Conclusion

Indexed Views are a great way to realize performance gains under the right circumstances. Be aware of the costs of creating indexed views in highly transactional environments. If your environment happens to be one of more querying than updating, however, indexed views might be just what the optimizer ordered.

SQL Server System Views

In this article we will learn where SQL server stores our object i.e. Tables, Indexes, Procedures, Triggers, Views etc. and how to find them using catalog views.

As we all know once we create objects it gets stored in our database. Suppose your database has many tables, Views, Triggers and want you find how many Tables, Views and other objects are there in your database don’t worry You can easily find all information from SQL Server System Catalog Views. We have many system Views inside our SQL Server database. You can find all system views under Views folder in your database. Open Object Explorer go in your database and then go in Views folder under view folder you will find system Views folder, there you go all you system views are present in this folder System views are also known as Catalog Views.You can right click and Select Top 1000 rows to see the data.

You can write a Simple SQL Query against these system views if you want to see all tables.

SELECT * FROM SYS.TABLES

Below I list all important sys views and their purpose lets go through with one example-

We will create a fresh new database and we will create two tables and then we will find how many tables are present in your database.

Create Database Teachmesqlserver

We have created one database in sql server, if you wants to search how many databases are there in your sql server you can easily find it from your system views

If you want to search how many databases are there in your SQL Server run below query

Select * from Sys.Databases




You will find just now created Teachmesqlserver database along with other databases if present in your sql server.

Let’s create two new tables in our recently created database

Create Table EMP(EMPID int)

Go

Create Table DEP(DEPID Int)

Now let’s find how many tables our database has, we will run simple SQL Query against Sys.Tables system view.

SELECT * FROM SYS.TABLES





Below is the list of all important system views.

Data Integrity

In relational database management systems, data integrity ensures the accuracy, reliability and consistency of the data during any operation like as data manipulation, data retrieval, data storage, backup or restore operation etc. It also guarantees that the recording of data is accurate and as intended. Having any bad or unintended data in the database, is a failure of data integrity.

For example, having sales detail in Sales table of a product not available in Product master is a failure of referential integrity.

Below are few scenarios where we might require data integrity to accept and maintain valid, accurate and intended data in the database:
We don’t have a NULL value as a product in Product master table. (Entity integrity)
To ensure that we don’t have duplicate products in Product Master table. (Entity integrity)
We don’t have any alphanumeric value in Amount column of Sales table.(Domain integrity)
To ensure that Sales table does not have any product which is not available in Product master table. (Referential integrity)
Deletion of any product from Product master table is not allowed if it has a reference in other tables, such as, Sales table. (Referential integrity)
Sale of a product is not allowed if its not available in stock. (User defined integrity or custom integrity)

In above examples, we can see that data integrity ensures the accuracy and consistency of data during data storage and manipulation operations. It defines, what valid values and changes on these values, are allowed in a column of a table. It also maintains the accurate and consistent related data throughout multiple related tables.

Lets discuss each type of data integrity in brief below:

Entity Integrity

Entity integrity talks about the primary key of a table. It states that a table must have a defined primary key to ensure unique and not null values in primary key column(s) of the table. Entity integrity rule on a column does not allow duplicate and null values in the column(s). The primary key column must identify each row uniquely in the table.

For example, in a student table, student identification number must be unique and not null to uniquely identify each student and his / her details.

Domain Integrity

Domain integrity defines the type, range and format of data allowed in a column. Domain integrity states that all values in a column must be of same type; i.e., an integer type column can only store integer values and not character data. It also defines that the range of the accepted values; e.g., a four byte integer type column can accept values between -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) including boundary values (SQL Server 2014). It also defines the nullability of the column; i.e., does null values allowed in the column or not.

We define domain integrity on a table in column definitions. Apart from column definitions, we can also use constraints, stored procedures, functions and triggers to implement domain integrity on tables.

Referential Integrity

Referential integrity talks about the foreign key concepts and ensures that the child tables do not have any orphaned record. Referential integrity ensures that data in related tables remains accurate and consistent before and after changes. In detail, if we have an Employee table with Employee_ID column having a reference inSalary table, to delete an employee from Employee table, we must have to delete all the records of that employee from Salary table and all other related tables first. By enabling referential integrity on Salary table, we ensure that we don’t have any orphaned record in Salary table which would mislead the paid salary amount; i.e., we will not be able to trace that who got the sum.


In case we implement referential integrity on a table, we cannot perform few operations such as:
Cannot use TRUNCATE command on a table being referenced by other table(s).
Cannot insert a record in child table when there is no related record in parent table.
Cannot delete a record from parent table when there is a reference in any other table.
Cannot update a record in child table which results orphaned records in child table.
Cannot update records in parent table which causes orphaned records in child table.

User Defined Integrity

Finally, if there is some custom business requirement which does not fit in any of the above discussed integrity types, we can define our own integrity constraint for a column with column definition or using functions or triggers. Creating such kind of custom constraints is known as User Defined Integrity or Custom integrity.

Below image summarizes the data integrity concepts in one view:





Data integrity is used to ensure accurate, reliable and consistent data throughout the database. To avoid invalid data, we must apply appropriate data integrity om columns.

JOINS in SQL Server

What is meant by JOIN in SQL Server?
What are different types of JOINS in SQL Server?


What is meant by JOIN in SQL Server?

Joins in SQL Server are used to retrieve data from multiple tables based on logical relationships between the tables.

A Join condition defines the way two tables are related in a query by.

1- Specifying the column from each of the table to be used for the join. A typical join condition specifies a foreign key from one table and its associate key in other table.

2- Specifying a logical operator (i.e =, <>, <) to be used in comparing values from the columns.


What are different types of JOINS in SQL Server?

Types of JOINS in SQL Server.

Inner Join
      Equi-join
      Natural Join

Outer Join
     Left outer Join
     Right outer join
     Full outer join

Cross Join
Self Join


CREATE DATABASE TEACHMESQLSERVER
GO

USE TEACHMESQLSERVERGO
GO

CREATE TABLE DEP(DEPID INT, DEPNAME VARCHAR(MAX))
CREATE TABLE EMP(EMPID INT, EMPNAME VARCHAR(MAX), DEPID INT)
GO

INSERT INTO DEP VALUES(1,'SALES'),(2,'IT'),(3,'HR'),(4,'MARKETING'),(5,'ACCOUNT')
GO

INSERT INTO EMP VALUES(1,'GHANESH',1)
INSERT INTO EMP VALUES(2,'PRASAD',2)
INSERT INTO EMP VALUES(3,'GAUTAM',3)
INSERT INTO EMP VALUES(4,'OJAL',1)
INSERT INTO EMP VALUES(5,'YOGITA',1)
INSERT INTO EMP VALUES(6,'ANVIE',2)
INSERT INTO EMP VALUES(7,'SAURABH',3)
INSERT INTO EMP VALUES(8,'KAPIL',4)
INSERT INTO EMP(EMPID,EMPNAME) VALUES(9,'ATUL')
INSERT INTO EMP(EMPID,EMPNAME) VALUES(10,'SACHIN')
GO

SELECT * FROM DEP
GO

SELECT * FROM EMP




INNER JOIN

Inner join returns the record when at least one match is found in both the tables. Inner Join can be Equi Join and Non Equi Join.

Equi- Join

In Equi join we only use Equality (=) operator in query for comparing the values from columns. Equi join can be written for Outer Join also.

We can write the query in two ways, first method is the old way of writing INNER join query, second statement is the new style of writing INNER join query.


SELECT DEPNAME,EMPNAME FROM DEP ,EMP WHERE DEP.DEPID=EMP.DEPID
OR
SELECT DEPNAME,EMPNAME FROM DEP INNER JOIN EMP ON DEP.DEPID=EMP.DEPID



As you can see from the result set we are getting only 8 records but we have 10 employees in our EMP table. Our query is returning only those records for which at least one match was found. EMPNAME ATUL and SACHIN were not returned because there was no match found in DEP table for these two records.

Non Equi Join

In Non Equi join we don’t use = operator but we use other available operator (i.e BETWEEN) in query for comparing the values from columns.


Outer Join

We have discussed Inner joins which return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows if there is no match from both the tables. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

SQL Server uses the following ISO keywords for outer joins specified in a FROM clause:

LEFT OUTER JOIN or LEFT JOIN- All rows are retrieved from the left table (DEP) referenced with a left outer join plus unmatched information from EMP table.


USE TEACHMESQLSERVER

SELECT DEPNAME,EMPNAME FROM DEP LEFT JOIN EMP ON DEP.DEPID=EMP.DEPID



As you can see from the result set our query is returning all records from LEFT table (DEP), Based on Join condition If match will be found in other table then it will return the value , if no match is found in other table in that case it fill the column value with NULL.


RIGHT OUTER JOIN or RIGHT JOIN - All rows are retrieved from the right table (EMP) referenced in a right outer join plus unmatched information from DEP table.

USE TEACHMESQLSERVER

SELECT DEPNAME,EMPNAME FROM DEP RIGHT JOIN EMP ON DEP.DEPID=EMP.DEPID



As you can see from the result set our query is returning all records from RIGHT table (EMP),Based on Join Condition If match will be found in other table then it will return the value, if no match is found in other table in that case it fill the column value with NULL.


FULL OUTER JOIN or FULL JOIN- All rows from both tables are returned in a full outer join.

USE TEACHMESQLSERVER

SELECT DEPNAME,EMPNAME FROM DEP FULL JOIN EMP ON DEP.DEPID=EMP.DEPID



As you can see from the result set our query is returning all records from LEFT table (DEP) and RIGHT table (EMP), Based on Join condition If match will be found in other table then it will return the value , if no match is found in either table in that case it fill the column value with NULL.


Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. If a WHERE Clause is added, the cross join behaves as an Inner Join.

USE TEACHMESQLSERVER

SELECT DEPNAME,EMPNAME FROM DEP, EMP




Total records returned the query is 50 because DEP table has 5 records and EMP table has 10 records.

Self Join

When you join a table with itself then this join is known as Self Join.

I hope you enjoyed the article don't forget to share on Facebook. Please leave your comments below if there are any questions.

Aim :- This article will make you learn about Joins in SQL Server. I will provide you some Pictorial view and examples of Joins in SQL Server. I will be discussing on the 2 sections –
Define joins in SQL Server?
Different types of joins in SQL Server?

Description :- Now, I am going to explain you each section of joins in SQL Server into detail.


Question #1. What is a Join?

We all know how to fetch data from a SQL table. It is very easy – Just fire a Select query. You will get your desired output.

But what you will do if you require data which is present in 2 or more SQL tables. This gives rise to a powerful SQL clause known as Join. Joins in SQL Server are used to combine rows from two or more tables. This join operation is based on a common field between the tables.

Question #2. What are different types of joins in sql server?

Generally we have three types of joins. They are as following –
Inner Join.
Outer Join.
Self Join.

These 3 categories are than further sub-divided into various types of Joins in SQL Server. I am mentioning below all the joins you will hear in your SQL life.
Inner Join is divided into – Equi Join || Natural Join || Cross Join.
Outer Join is divided into – Left Outer Join || Right Outer Join || Full Outer Join.

I will provide some brief information about each Joins in SQL Server. Also for better understanding, I will show all types of joins in Pictorial format. The pictorial format will give more idea to understand thefunctionality of each join. I hope you will like and enjoy learning Joins in SQL Server this way.





Type 1. Inner Join –

An Inner join in SQL Server returns “Matched Rows” from the multiple tables. Just follow the below picture, it will clearly show how inner join performs operations internally. Inner join can be further sub divided into 3 types –
Equi Join.
Natural Join.
Cross Join.

Equi Join – The Equi join in SQL Server is used to display all the “Matched Rows” from the Joined tables and also display Redundant data. In this join, we need to use * sign to join the table.


Natural Join – The Natural Join in SQL Server is same as Equi Join but the only difference is that it willnot display Redundant data.


Cross Join – This join is a Cartesian join and does not necessitate any condition to join. The result set contains records that are multiplication of record number from both the tables. In Simple words, Cross join in SQL Server gives a Cartesian product of multiple tables.


Type 2. Outer Join –


An Outer join in SQL Server returns “Matched Rows” as well as “Unmatched Rows” from the multiple tables. Outer join is classified into 3 types –
Left Outer Join.
Right Outer Join.
Full Outer Join.

Left Outer Join – A Left Outer Join in SQL Server returns the “Matched Rows” from multiple tables and “Non Matched Rows” from Left side table. Follow the above picture for more understanding.

Right Outer Join – A Right Outer Join in SQL Server returns the “Matched Rows” from multiple tables and “Non Matched Rows” from Right side table. Follow the above picture for more understanding.

Full Outer Join – A Full Outer Join returns “Matched Rows” from multiple tables and also “Non Matched Rows” from multiple tables. Follow the above picture for more understanding.

Summary :- In this article on Joins in SQL Server, we learned –
What is a Join and Why we use this Clause.
What are different types of Joins available in SQL Server.
Pictorial representation of each Join.


SQL CROSS JOIN

In SQL Server, Cross Join returns the cartesian product of both the tables. Cross Join does not require any common column to join two table.

Cartesian product means Number of Rows present in Table 1 Multiplied by Number of Rows present in Table 2
SQL Cross Join Syntax

The basic syntax of the SQL Server Cross Join is as follows:

SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
CROSS JOIN
Table2

--OR We can Simply Write it as

SELECT Table1. Column(s), Table2. Column(s),
FROM Table1, Table2

In this article we will show you, How to write Cross Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.

Data present in the Employee Table is:





Data present in the Department Table is:




SQL Cross Join Example

The following SQL Query will display the Cartesian product of the columns present in Employees and Department tables.

T-SQL CODE

SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
CROSS JOIN
[Department] AS Dept


--OR We Can Write

SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
[Department] AS Dept


OUTPUT




If you observe the above screenshot, It is displaying 120 records. It means 15 rows from the Employee multiplies by 8 rows in the Department table

Using Where Clause in SQL Cross Join

SQL allows us to use the Where Clause to restrict the number of rows returned by the Cross Join

T-SQL CODE

SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp,
[Department] AS Dept
WHERE Dept.[DepartmentName] = 'Software Developer'


OUTPUT





SQL FULL JOIN

In SQL Server, Full Join returns all the records (or rows) present in both Left table and the right table. All the Unmatched rows will be filled with NULL Values.
SQL Full Join Syntax

The basic syntax of the SQL Server Full Join is as follows:

SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
FULL OUTER JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column


--OR We can Simply Write it as

SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
FULL JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column


TIP: In SQL Server, Full Outer join can also be called as Full Join so it is optional to use the Outer Keyword.

Let us see the visual representation of the Full join for better understanding.





From the above image you can understand easily that, Full Outer join displays all the records present in Table 1 and Table 2

In this article we will show you, How to write Full Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.

Data present in the Employee Table is:



Data present in the Department Table is:



SQL Full Join – Selecting All Columns

The following SQL Query will display all the columns and rows present in Employees and Department tables

T-SQL CODE


SELECT *
FROM [Employee]
FULL OUTER JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]


OUTPUT




If you observe the above screenshot, Although We have 15 records in Employee table, Full Join is displaying 17 records. This is because, there are two records in the Department table i.e., Department Id 3, 4 (Module Lead and Team Lead) so 15 + 2 = 17 total records.

For Department Id 3, 4 (Module Lead and Team Lead) there is no matching records in Employees table so they are simply replaced by NULLS.

For [DepartID], id, [Department Name] it is displaying NULL Values for the ID number 10, 11, 14 and 15 . This is because, Department Id for them in Employee table are NULLS so there is no matching records in right table.

NOTE: The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns so, Please avoid SELECT * Statements in Full Join
SQL Full Join – Selecting Few Columns


Please place the required columns after the SELECT Statement to avoid unwanted columns

T-SQL CODE

SELECT [FirstName]
,[LastName]
,[DepartmentName]
FROM [Employee]
FULL JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]


OUTPUT




Above query will perfectly work as long as the column names from both tables (Employee and Department) are different like above. What happens if we have same Column names in both the tables? Well, you will end up in a mess. Let us see how to resolve the issue.

The following query is using table name before the column name and also used theALIAS names to rename the column names. By this approach we can inform the Database that we are looking for columns belonging to particular table.


T-SQL CODE


SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
FULL OUTER JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [DepartmentName] ASC


OUTPUT



Although we don’t have any common column names, Imagine we have [DepartmentName] column in Employee as well and we want to display Department Columns in both the tables.

We can simply write as


SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Emp.[DepartmentName] AS [Employee Department]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
FULL JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [DepartmentName] ASC


NOTE: It is always best practice to use the table name before the Column name in Joins. For example, SELECT Employee.Firstname


SQL INNER JOIN

In SQL, INNER JOIN returns the records (or rows) present in both tables, If there is at least one match between columns.

Or we can Simply say

INNER JOIN returns the records (or rows) present in both tables as long as the Condition after the ON Keyword is TRUE.

SQL INNER JOIN Syntax

The basic syntax of the SQL Server Inner Join is as follows:

SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
INNER JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column


--OR We can Simply Write it as


SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column

In SQL, Inner join is the default join so it is optional to use the INNER Keyword.

Let us see the visual representation of the Inner join for better understanding.





From the above image you can understand easily that, Inner join only displays the matching records from Table 1 and Table 2 (Like an Intersect in Math)

In this article we will show you, How to write Inner Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.

Data present in the Employee Table is:




Data present in the Department Table is:



SQL Inner Join – Selecting All Columns

The following SQL Query will display all the columns present in Employees and Department tables

T-SQL CODE

SELECT *
FROM [Employee]
JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]


OUTPUT




If you observe the above screenshot, Although we have 15 records in Employee table, Inner join is displaying 11 records. This is because, Department Id for the remaining 4 records (i., ID number 10, 11, 14 and 15) in Employee table are NULLS.

NOTE: The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns so, Please avoid SELECT * Statements in INNER JOINS
SQL Inner Join – Selecting Few Columns

Please place the required columns after the SELECT Statement to avoid unwanted columns

T-SQL CODE


SELECT [FirstName]
,[LastName]
,[DepartmentName]
FROM [Employee]
INNER JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]


OUTPUT



Above query will perfectly work as long as the column names from both tables (Employee and Department) are different like above. What happens if we have same Column names in both the tables? Well, you will end up in a mess. Let us see how to resolve the issue.

The following query is using table name before the column name and also used theALIAS names to rename the column names. By this approach we can inform the Database that we are looking for columns belonging to particular table.

T-SQL CODE


SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
INNER JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [FirstName] ASC


OUTPUT



Although we don’t have any common column names, Imagine we have [DepartmentName] column in Employee as well and we want to display Department Columns in both the tables.

We can simply write as

SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Emp.[DepartmentName] AS [Employee Department]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
INNER JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [FirstName] ASC


NOTE: It is always best practice to use the table name before the Column name in Joins. For example, SELECT Employee.Firstname


SQL LEFT JOIN

In SQL Server, Left Join returns all the records (or rows) present in the Left table and matching rows from the right table.

NOTE: All the Unmatched rows from the right table will be filled with NULL Values.
SQL LEFT JOIN Syntax

The basic syntax of the SQL Server Left Join is as follows:


SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
LEFT OUTER JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column


--OR We can Simply Write it as


SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
LEFT JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column


TIP: In SQL Server, Left Outer join can also be called as Left Join so it is optional to use the Outer Keyword.

Let us see the visual representation of the Left Outer join for better understanding.



From the above image you can understand easily that, Left Outer join displays all the records present in Table 1 and matching records from Table 2

In this article we will show you, How to write Left Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.

Data present in the Employee Table is:



Data present in the Department Table is:



SQL Left Join – Selecting All Columns

The following SQL Query will display all the columns present in Employees and Department tables

T-SQL CODE

SELECT *
FROM [Employee]
LEFT OUTER JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]


OUTPUT




If you observe the above screenshot, We have 15 records in Employee table, Left Outer Join is displaying 15 records but for [DepartID], id, [Department Name] it is displaying NULL Values for the ID number 10, 11, 14 and 15 . This is because, Department Id for them in Employee table are NULLS so there is no matching records in right table.

NOTE: The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns so, Please avoid SELECT * Statements in Left Join

SQL Left Join – Selecting Few Columns

Please place the required columns after the SELECT Statement to avoid unwanted columns

T-SQL CODE


SELECT [FirstName]
,[LastName]
,[DepartmentName]
FROM [Employee]
LEFT JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]


OUTPUT




Above query will perfectly work as long as the column names from both tables (Employee and Department) are different like above. What happens if we have same Column names in both the tables? Well, you will end up in a mess. Let us see how to resolve the issue.

The following query is using table name before the column name and also used theALIAS names to rename the column names. By this approach we can inform the Database that we are looking for columns belonging to particular table.

T-SQL CODE

SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
LEFT JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [DepartmentName] ASC


OUTPUT



Although we don’t have any common column names, Imagine we have [DepartmentName] column in Employee as well and we want to display Department Columns in both the tables.

We can simply write as

SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Emp.[DepartmentName] AS [Employee Department]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
LEFT JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [DepartmentName] ASC

NOTE: It is always best practice to use the table name before the Column name in Joins. For example, SELECT Employee.Firstname


SQL RIGHT JOIN

In SQL Server, Right Join returns all the records (or rows) present in the Right table and matching rows from the Left table.

NOTE: All the Unmatched rows from the Left table will be filled with NULL Values.
SQL RIGHT JOIN Syntax

The basic syntax of the SQL Server Right Join is as follows:


SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
RIGHT OUTER JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column


--OR We can Simply Write it as

SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
RIGHT JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column

TIP: In SQL Server, Right Outer join can also be called as Right Join so it is optional to use the Outer Keyword.

Let us see the visual representation of the Right Outer join for better understanding.





From the above image you can understand easily that, Right Outer join displays all the records present in Table 2 and matching records from Table 1

In this article we will show you, How to write Right Join in SQL Server 2014. For this, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.

Data present in the Employee Table is:



Data present in the Department Table is:



SQL Right Join – Selecting All Columns

The following SQL Query will display all the columns present in Employees and Department tables

T-SQL CODE

SELECT *
FROM [Employee]
RIGHT OUTER JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]


OUTPUT



If you observe the above screenshot, Although we have 15 records in Employee table, Right join is displaying 13 records. This is because, Department Id for 14th and 15th records in Employee table are NULLS so there is no matching records in right table.

If you observe the 8th and 9th records, they are displaying NULL values because in Employee table there are no matching records for Department Id 3, 4 (Module Lead and Team Lead) in the Department table so they are simply replaced by NULLS.

NOTE: The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns so, Please avoid SELECT * Statements in Right Join
SQL Right Join – Selecting Few Columns

Please place the required columns after the SELECT Statement to avoid unwanted columns

T-SQL CODE

SELECT [FirstName]
,[LastName]
,[DepartmentName]
FROM [Employee]
RIGHT JOIN
[Department] ON
[Employee].[DepartID] = [Department].[id]


OUTPUT



Above query will perfectly work as long as the column names from both tables (Employee and Department) are different like above. What happens if we have same Column names in both the tables? Well, you will end up in a mess. Let us see how to resolve the issue.

The following query is using table name before the column name and also used theALIAS names to rename the column names. By this approach we can inform the Database that we are looking for columns belonging to particular table.

T-SQL CODE

SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
RIGHT JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [FirstName] ASC


OUTPUT



Although we don’t have any common column names, Imagine we have [DepartmentName] column in Employee as well and we want to display Department Columns in both the tables.

We can simply write as


SELECT Emp.[FirstName] AS [First Name]
,Emp.[LastName] AS [Last Name]
,Emp.[DepartmentName] AS [Employee Department]
,Dept.[DepartmentName] AS [Department Name]
FROM [Employee] AS Emp
RIGHT JOIN
[Department] AS Dept ON
Emp.[DepartID] = Dept.[id]
ORDER BY [FirstName] ASC

NOTE: It is always best practice to use the table name before the Column name in Joins. For example, SELECT Employee.Firstname


SQL SELF JOIN

In SQL Server, Self Join is used to Join the Table 1 to Itself. If we have the Primary key and Foreign key in the same table then, we can use this Self join to connect them.
SQL Self Join Syntax

The basic syntax of the SQL Server Self Join is as follows:

SELECT Tab1.Column(s), Tab2.Column(s),
FROM Table1 AS Tab1,
Table1 AS Tab2

NOTE: Alias Table Name is mandatory for Self Join

In this article we will show you, How to write Self Join in SQL Server 2014. For this, We are going to use Employees table present in our [SQL Server Tutorials] Database.

Data present in the Employees Table is:




From the above screenshot you can observe that we have 15 different employees and each person belongs to Department and we have the Foreign key column DepartID.
SQL Self Join Example

In general, Every Department will have one Department Head and he will definitely belongs to one of the Employee. To get the Department Head name we have to use the Self Join using the Primary key and Foreign key. For example, The following SQL Query will display the Name of the Department Head for each and every Employee present in the Employees table.


T-SQL CODE

SELECT EMP1.[FirstName]
,EMP1.[LastName]
,EMP1.[Department Name]
,EMP2.[FirstName] +' '+ EMP2.[LastName] AS [Department Head]
FROM [Employees] AS EMP1, [Employees] AS EMP2
WHERE EMP1.DepartID = EMP2.ID


OUTPUT




If you observe the above screenshot, Rob Walter is not only Sr. Software Developer but also Department Head for the remaining Sr. Software Developer in an organization.


JOIN ORDER cans Increase Performance

Introduction

All the developer is very much concern related to performance. If someone say that this increase performance all the developer are running behind it. It is not a bad practice at all. Rather as per my point of view we must span all our effort related improve the performance of query.

“One common question that we find that, if we change the ordering of table join in case of inner join will effect or increase performance”

To understand it lets take a simple example of Inner join. There is two tables named Table-A and Table-B. We can us the Inner Join on both the table.

Like this

FROM [Table-A] AS a INNER JOIN [Table-B] AS b ON a.IDNO = b.IDNO

OR

FROM [Table-B] AS a INNER JOIN [Table-A] AS b ON a.IDNO = b.IDNO

Which one is best for performance?

To answer this question we all know that whenever a SQL Query is executed the MS SQL server create several query plans with different join Order and choose the best one.

That means the Join order that we are writing in the query may not be executed by execution plan. May be different join order is used by the execution plan. In the above case the execution plan decide which Join order he will chose depends on best possible costing of execution.

Here [Table-A] JOIN [Table-B] or [Table-B] JOIN [Table-A], MS SQL Server knows it well that both are same.


To understand it Details Lets take an Example

Step-1 [ Create Base Table and Insert Some Records ]

-- Item Master

IF OBJECT_ID(N'dbo.tbl_ITEMDTLS', N'U')IS NOT NULL

BEGIN
DROP TABLE [dbo].[tbl_ITEMDTLS];
END

GO

CREATE TABLE [dbo].[tbl_ITEMDTLS]
(

ITEMCD INT NOT NULL IDENTITY PRIMARY KEY,
ITEMNAME VARCHAR(50) NOT NULL
)
GO

-- Inserting Records

INSERT INTO [dbo].[tbl_ITEMDTLS]
(ITEMNAME)
VALUES ('ITEM-1'),('ITEM-2'),('ITEM-3');


-- Item UOM Master

IF OBJECT_ID(N'dbo.tbl_UOMDTLS', N'U')IS NOT NULL

BEGIN
DROP TABLE [dbo].[tbl_UOMDTLS];
END
GO
CREATE TABLE [dbo].[tbl_UOMDTLS]
(
UOMCD INT NOT NULL IDENTITY PRIMARY KEY,
UOMNAME VARCHAR(50) NOT NULL
)
GO


-- Inserting Records

INSERT INTO [dbo].[tbl_UOMDTLS]
(UOMNAME)
VALUES ('KG'), ('LTR'), ('GRM');
GO


-- Transaction Table

IF OBJECT_ID(N'dbo.tbl_SBILL', N'U')IS NOT NULL

BEGIN
DROP TABLE [dbo].[tbl_SBILL];
END


GO

CREATE TABLE [dbo].[tbl_SBILL]
(
TRID INT NOT NULL IDENTITY PRIMARY KEY,
ITEMCD INT NOT NULL,
UOMCD INT NOT NULL,
QTY DECIMAL(18,3) NOT NULL,
RATE DECIMAL(18,2) NOT NULL,
AMOUNT AS QTY * RATE
);

GO

-- Foreign Key Constraint

ALTER TABLE [dbo].[tbl_SBILL]
ADD CONSTRAINT FK_ITEM_tbl_SBILL FOREIGN KEY(ITEMCD) REFERENCES [dbo].[tbl_ITEMDTLS](ITEMCD);

GO

ALTER TABLE [dbo].[tbl_SBILL]
ADD CONSTRAINT FK_UOMCD_tbl_SBILL FOREIGN KEY(UOMCD) REFERENCES [dbo].[tbl_UOMDTLS](UOMCD);


-- Insert Records

INSERT INTO [dbo].[tbl_SBILL]
(ITEMCD, UOMCD, QTY, RATE)
VALUES (1, 1, 20, 2000),(2, 3, 23, 1400);

Step-2 [ Now Make Some JOIN ]

SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD,
c.UOMNAME, b.QTY, b.RATE, b.AMOUNT
FROM [dbo].[tbl_ITEMDTLS] AS a
INNER JOIN [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD
INNER JOIN [dbo].[tbl_UOMDTLS] AS c ON b.UOMCD = c.UOMCD;

Here [tbl_ITEMDETAILS] JOIN [tbl_SALES] JOIN [tbl_UOMDETAILS]


If we look at the Execution Plan




We find that
[tbl_SALES] JOIN [tbl_ITEMDETAILS] JOIN [tbl_UOMDETAILS]


Step-2 [ Now we need to Force Order Hint to maintain Join Order ]

SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD,
c.UOMNAME, b.QTY, b.RATE, b.AMOUNT
FROM [dbo].[tbl_ITEMDTLS] AS
INNER JOIN [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD
INNER JOIN [dbo].[tbl_UOMDTLS] AS c ON b.UOMCD = c.UOMCD
OPTION ( QUERYRULEOFF JoinCommute);





For this we need the FORCE ORDER Hint.

The query optimizer uses different rules to evaluate different plan and one of the rules is called JoinCommute. We can turn it off using the undocumented query hint QUERYRULEOFF.

Condition in ON cluase of OUETR JOIN Effects


Introduction

Where we are making JOIN, a lot of developer uses extra condition within the ON clause of the JOIN. In my review I find that some developer added a lot of condition in the ON clause (they are including WHERE condition within the ON clues of the JOIN).

Please remind it that the extra condition of ON clause of JOIN can affect the OUTER JOIN. This article is related to it.

What MS says

First we look at what MS Says about it.

"Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join."


Example of Extra Condition

To understand it properly am taking an example of that.


Step-1 [ Create the Base Table ]

IF OBJECT_ID('my_TABLE1') IS NOT NULL

BEGIN
DROP TABLE my_TABLE1;
END

GO

CREATE TABLE my_TABLE1
(ID INT NOT NULL IDENTITY PRIMARY KEY,
SNAME VARCHAR(50) NOT NULL);

GO

IF OBJECT_ID('my_TABLE2') IS NOT NULL
BEGIN
DROP TABLE my_TABLE2;
END

GO

CREATE TABLE my_TABLE2
(ID INT NOT NULL IDENTITY PRIMARY KEY,
SECTION VARCHAR(1) NOT NULL);

GO

Step-2 [ Insert Records ]
INSERT INTO my_TABLE1
(SNAME)
VALUES ('JOYDEEP'),
('SUKAMAL'),
('TUHIN'),
('SANGRAM'),
('ROOHUL');

GO
INSERT INTO my_TABLE2
(SECTION)
VALUES ('A'),
('B'),
('A');

Step-3 [ Make the INNER JOIN]

SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a

INNER JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A'

SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
INNER JOIN my_TABLE2 b ON a.ID = b.ID
WHERE b.SECTION = 'A'

Both of the Queries display the same output

ID SNAME SECTION

1 JOYDEEP A
3 TUHIN A


Step-4 [ Make the LEFT JOIN ]

SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
LEFT JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A'


Output:

ID SNAME SECTION

1 JOYDEEP A
2 SUKAMAL NULL
3 TUHIN A
4 SANGRAM NULL
5 ROOHUL NULL


SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
LEFT JOIN my_TABLE2 b ON a.ID = b.ID
WHERE b.SECTION = 'A'

Output:

ID SNAME SECTION

1 JOYDEEP A
3 TUHIN A


Observation -1

In case of INNER JOIN both the query is fine and work perfectly.

But in case of LEFT JOIN both the query react differently.

In case of LEFT OUTER JOIN first query the SECTION = 'A' condition works on before JOIN. It retrieves the result from my_TABLE2 and LEFT OUTER JOIN with my_TABLE1 and it does not affect the my_TABLE1.

When we add the WHERE clause it effects of the complete result set after JOIN and filters the records.

Observation -2

Now we see the actual execution plan of both query (INNER JOIN and LEFT JOIN).

SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a

INNER JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A';

SELECT a.ID, a.SNAME, b.SECTION
FROM my_TABLE1 a
LEFT JOIN my_TABLE2 b ON a.ID = b.ID AND b.SECTION = 'A';





In case of Query-2 the hash match operator is used as join. In Query-2 by optimizer will be switched to inner join instead left outer join.


INNER or LEFT JOIN for Performance

In this article I am trying to discuss a very common scenario that between INNER JOIN and LET JOIN which one is increase the performance and also discuss how to boost the join performance.

First look at the definition of both INNER and LEFT JOIN.

INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (Table-1), even if there are no matches in the right table (Table-2).

General rules

INNER JOIN are usually faster than left joins, but if we need a left join for unmatched results then an inner join will not give you the results that we need.

If we are filtering on the "right" table in a LEFT JOIN, then indeed we should use an INNER JOIN instead, and we may get better performance.

What exactly we have to do

Only thing is that we must look at the execution plan very carefully as the Query use the HASH or MERGE Join operator. As the MERGE JOIN which is more efficient than HASH JOIN for fairly small, sorted tables.

The extra work the left join performs is when a row in the left table has no matching rows in the right table; the result set row contains NULL values for all the selected columns coming from the right table. If this is what we want, then use this type of join and it is the real performance killer.

So it matters depends on situation to situation.

Here are an example of INNER JOIN and a LEFT JOIN which gives us the same result sets.

CREATE TABLE #tem_Tab1
(emcd INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
empname VARCHAR(50) NOT NULL)


INSERT INTO #tem_Tab1
(empname)
VALUES ('Joydeep'), ('Sangram'), ('Sudip'), ('Tuhin')


CREATE TABLE #tem_Tab2

(emcd INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

empsal DECIMAL(20,2) NOT NULL)

INSERT INTO #tem_Tab2

(empsal)

VALUES (10000.00), (23000.00), (12000.00)

SELECT * FROM #tem_Tab1

SELECT * FROM #tem_Tab2

SELECT #tem_Tab1.emcd, #tem_Tab1.empname, #tem_Tab2.empsal
FROM #tem_Tab1 INNER JOIN #tem_Tab2 ON #tem_Tab1.emcd=#tem_Tab2.emcd

SELECT #tem_Tab1.emcd, #tem_Tab1.empname, #tem_Tab2.empsal
FROM #tem_Tab1 LEFT JOIN #tem_Tab2 ON 1=1
WHERE #tem_Tab1.emcd=#tem_Tab2.emcd


Look at the execution plan of both the query and we find the same execution plan for both the query.

To Boost the Join performance

1. When joining two or more table performance increased, if join columns have index.

2. Foreign keys are not automatically indexed. So if you ever plan to join a table to the table with the foreign key, using the foreign key as the linking column, then we should consider adding an index to the foreign key column. An index on a foreign key column can substantially boost the performance of many joins.

3. For best join performance, the indexes on the columns being joined should ideally be numeric data types, not CHAR or VARCHAR, or other non-numeric data types.

4. For maximum performance when joining two or more tables, the indexes on the columns to be joined should have the same data type, and ideally, the same width.

5. If our join is slow, and currently includes hints, remove the hints to see if the optimizer can do a better job on the join optimization.

6. One of the best ways to boost JOIN performance is to ensure that the Joined tables include an appropriate WHERE clause to minimize the number of rows that need to be joined.

7. In the SELECT statement that creates your JOIN, don't use an * to return all of the columns in both tables.

8. If you have the choice of using a JOIN or a subquery to perform the same task, generally the JOIN (often an OUTER JOIN) is faster.

CROSS APPLY Vs OUTER APPLY



In my previous article "CROSS APPLY on SQL Server 2005", I already mentioned the definition of cross apply with proper example.

Reference link related to article "CROSS APPLY on SQL Server 2005"

http://sqlknowledgebank.blogspot.in/2012/05/cross-apply-on-sql-2005.html

In this article I am trying to discuss related to different formation of APPLY or we can say it CROSS APPLY Vs OUTER APPLY.


APPLY has two forms

1. CROSS APPLY

2. OUTER APPLY

CROSS APPLY

Think it as an INNER JOIN.


The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table-valued functions or table expression. In other words, the right table expression returns rows for left table expression match only.


OUTER APPLY

Think it as a LEFTOUTER JOIN.

The OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table-valued functions or table expression. For those rows for which there are no corresponding matches in right table-valued functions or table expression, it contains NULL values in columns of right table expression.

To understand it properly we just take an example of both CROSS APPLY and OUTER APPLY


Step-1 [ Create the Environment ]

CREATE TABLE tbl_Dep
(DepID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
DepName VARCHAR(50) NOT NULL)

GO

INSERT INTO tbl_Dep
(DepName)
VALUES ('Developemnt')

GO

INSERT INTO tbl_Dep
(DepName)
VALUES ('Management')

GO

INSERT INTO tbl_Dep
(DepName)
VALUES ('Logistic')

CREATE TABLE tbl_Emp
(EmpID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
EmpName VARCHAR(50) NOT NULL,
DepID INT)

GO

INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Joydeep Das',1)

GO

INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Tuhin Shina',1)

GO

INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Sangram Jit',1)

GO

INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Bhola',1)

GO

INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Sudip Das',2)

GO

INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Mithun deshi',3)


GO
INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Rajesh Keshri',NULL)

GO

INSERT INTO tbl_Emp
(EmpName,DepID)
VALUES('Sukamal Jana',NULL)

GO


-- Table Valued Function

CREATE FUNCTION dbo.fnGetDept
(@p_DepID AS INT)
RETURNS TABLE
AS
RETURN

(
SELECT DepID,
DepName
FROM tbl_Dep
WHERE DepID = @p_DepID
)

GO

SELECT * FROM tbl_Emp

GO

EmpID EmpName DepID

1 Joydeep Das 1
2 Tuhin Shina 1
3 Sangram Jit 1
4 Bhola 1
5 Sudip Das 2
6 Mithun deshi 3
7 Rajesh Keshri NULL
8 Sukamal Jana NULL


Step-2 [ CROSS APPLY ]

SELECT a.EmpID,
a.EmpName,
b.DepID,
b.DepName
FROM tbl_Emp a
CROSS APPLY dbo.fnGetDept(a.DepID)b

Output

EmpID EmpName DepID DepName

1 Joydeep Das 1 Developemnt
2 Tuhin Shina 1 Developemnt
3 Sangram Jit 1 Developemnt
4 Bhola 1 Developemnt
5 Sudip Das 2 Management
6 Mithun deshi 3 Logistic

Please look at the output as the Employee named "Rajesh Keshri" with Emp ID: 7 and "Sukamal Jana" with Emp ID 8, not appears in the list as the CROSS APPLY works like INNER JOIN.

Step-3 [ OUTER APPLY ]

SELECT a.EmpID,
a.EmpName,
b.DepID,
b.DepName
FROM tbl_Emp a
OUTER APPLY dbo.fnGetDept(a.DepID)b

GO

Output

EmpID EmpName DepID DepName

1 Joydeep Das 1 Developemnt
2 Tuhin Shina 1 Developemnt
3 Sangram Jit 1 Developemnt
4 Bhola 1 Developemnt
5 Sudip Das 2 Management
6 Mithun deshi 3 Logistic
7 Rajesh Keshri NULL NULL
8 Sukamal Jana NULL NULL

Here the employee named "Rajesh Keshri" with Emp ID: 7 and "Sukamal Jana" with Emp ID 8 appears but there Dept ID and Dep Name Contains NULL values. So it looks like LEFT OUTER JOIN.


Join HINT

JOIN HINT in SQL, What and When:

Joint hint specifies that the query optimizer enforced the join strategy before joining two tables.

By default SQL server takes the best execution plan before joining table's objects, be careful using joint hint in your SQL statement until you know them very well.

The hint is using in join to increase the performance of execution of statement. But sometimes it gives you reverse action also.

It applies to SELECT, DELETE and UPDATE statements.

Syntax is

<join_hint> ::=
{ LOOP | HASH | MERGE | REMOTE }

In this article I try to explain about the different types of hint used in SQL server.

LOOP Joint Hint:

This is the simplest form of LOOP Join. Suppose you have Tabe1 Join with Table2 by LOOP join hint.

The algorithm is motioned bellow.

FOR each row R1 in the outer table
FOR each row R2 in the inner table
IF R1 joins with R2
return (R1, R2)

Example:

SELECT a.*, b.*
FROM Table1 a
INNER LOOP JOIN Table2 b ON a.roll=b.roll
WHERE a.roll<=12


HASH Joint hint:

Hash joins parallelize and scale better than any other join and are great at maximizing throughput in data warehouses.

The hash join executes in two phases: build and probe. During the build phase, it reads all rows from the first input (often called the left or build input), hashes the rows on the equijoin keys, and creates an in-memory hash table. During the probe phase, it reads all rows from the second input (often called the right or probe input), hashes these rows on the same equijoin keys, and looks or probes for matching rows in the hash table. Since hash functions can lead to collisions (two different key values that hash to the same value), we typically must check each potential match to ensure that it really joins.

The algorithm is motioned bellow.

FOR each row R1 in the build table
BEGIN
calculate hash value on R1 join key(s)
insert R1 into the appropriate hash bucket
END
FOR each row R2 in the probe table
BEGIN
calculate hash value on R2 join key(s)
FOR each row R1 in the corresponding hash bucket
IF R1 joins with R2
return (R1, R2)
END

Example:

SELECT a.*, b.*
FROM Table1 a
INNER HASH JOIN Table2 b ON a.roll=b.roll
WHERE a.roll<=12


MERGE Join Hint:

The merge join works by simultaneously reading and comparing the two sorted inputs one row at a time. At each step, we compare the next row from each input. If the rows are equal, we output a joined row and continue. If the rows are not equal, we discard the lesser of the two inputs and continue. Since the inputs are sorted, we know that we are discarding a row that is less than any of the remaining rows in either input and, thus, can never join.

The algorithm is motioned bellow.

get first row R1 from input 1
get first row R2 from input 2
WHILE not at the end of either input
BEGIN
IF R1 joins with R2
BEGIN
return (R1, R2)
get next row R2 from input 2
NED
ELSE IF R1 < R2
get next row R1 from input 1
ELSE
get next row R2 from input 2
END

Example:

SELECT a.*, b.*
FROM Table1 a
INNER MERGE JOIN Table2 b ON a.roll=b.roll
WHERE a.roll<=12

REMOTE Joint Hint:

It is useful when the join left table is a local table and the right table is a remote table. It is used when the left table is fewer rows then the right table.

REMOTE is only used with Inner join.

Example:

SELECT a.*, b.*
FROM Table1 a
INNER REMOTE JOIN Table2 b ON a.roll=b.roll
WHERE a.roll<=12

The link is mentioned bellow.

http://www.kunal-chowdhury.com/2012/01/join-hint-in-sql-guest-post-by-joydeep.html
http://sqlhints.com/2014/02/01/joins-in-sql-server/
http://www.interviewquestionspdf.com/2014/07/complex-joins-sql-joins-queries.html
http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx
http://www.w3resource.com/sql/joins/cross-join.php
http://www.c-sharpcorner.com/UploadFile/63f5c2/joins-in-sql-server/
http://www.madeiradata.com/loop-hash-and-merge-join-types/
http://www.mssqlcity.com/Articles/General/hashjoins.htm