SQL Server Transaction Savepoints

1. Within a transaction you can create one or more transaction savepoints
2. When you rollback to a savepoint, all of the database updates performed after that savepoint are reversed
3. Updates that happened after the transaction started but before the savepoint was declared are not affected.
4. You can create multiple savepoints within a single transaction and roll them back individually
5. it's important to note that rolling back to a savepoint also removes any savepoints that were created later. for example, if you created savepoints named "s1", "s2" and "s3" in that order, rolling back savepoint "s2" would remove savepoint "s3". Savepoint "s1" would still be active.
6. Savepoint name should be a string of up to 32 characters. If the name is longer than 32 characters the additional text is ignored. 

SAVE TRAN savepoint-name

ROLLBACK TRAN savepoint-name

SET NOCOUNT ON

BEGIN TRAN

PRINT'First Transaction: '+ CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES('Tom')

SAVE TRAN Savepoint1

PRINT'Second Transaction: '+ CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES('Dick')

ROLLBACK TRAN Savepoint1

PRINT'Rollback: '+ CONVERT(VARCHAR,@@TRANCOUNT)

COMMIT TRAN

PRINT'Complete: '+ CONVERT(VARCHAR,@@TRANCOUNT)

/* MESSAGES

First Transaction: 1

Second Transaction: 1

Rollback: 1

Complete: 0

Note:

1. savepoints have the limitation that they cannot be used in distributed transactions. 
2. you should note that locks created during a transaction are retained when rolling back to a savepoint. They are released only when the entire transaction is committed or rolled back.

What is difference between View and Materialized View in Database or SQL?

What is Materialized View in database

Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

A Materialized View (MV) replaces a SQL multi-table-view (or query) with a new table that holds all data permutations
MV's are used to improve performance, and are preferable to replication where problem is due to an inefficient query plan 

Difference between View vs Materialized View in database

Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table

6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

When to Use View vs Materialized View in SQL

Mostly in application we use views because they are more feasible,  only logical representation of table data no extra space needed. We easily get replica of data and we can perform our operation on that data without affecting actual table data but when we see performance which is crucial for large application they use materialized view where Query Response time matters so Materialized views are used mostly with data ware housing or business intelligence application.

That’s all on difference between View and materialized View in database or SQL. I suggest always prepare this question in good detail and if you can get some hands on practice like creating Views, getting data from Views then try that as well.

CREATE VIEW vStateCity AS
   SELECT StateID, StateName, CityID, CityName
      FROM tState s, tCity c
      WHERE c.StateIDFK = s.StateID;

Example: MV Create: 

/* Create the MV table */
CREATE TABLE mvStateCity AS SELECT * FROM vStateCity;

/* Optionally add index(es) for the queries you want to speed up */
CREATE INDEX iStateCity ON mvStateCity(StateName, CityName)

/* Rename the old view to save it and to avoid application re-coding */
/* (For MS-SQL, use syntax 'EXEC sp_rename [old], [new]') */
RENAME TABLE vStateCity TO vStateCityOld;

/* Create the view that points to the MV */
CREATE VIEW vStateCity AS
   SELECT * FROM mvStateCity;

Example: MV Query Run Time 

SELECT * FROM vStateCity WHERE STATE= ‘California’ AND CityName LIKE ‘%an%’; 

-----> runs 10x faster than original VStateCity query for large amounts of data

Grouping Sets in T-SQL

whenever any aggregate function is required, the GROUP BY clause is the only solution

There has always been a requirement get these aggregate functions based on different sets of columns in the same result set. It is also safe to use this feature as this is an ISO standard.

Though the same result could be achieved earlier, we would have to write different queries and would have to combine them using a UNION operator. The result set returned by a GROUPING SET is the union of the aggregates based on the columns specified in each set in the Grouping Set.

As you can see from the code itself, you just specify the needed grouping sets inside the GROUP BY GROUPING SETS clause – everything else is performed transparently by SQL Server. The empty parentheses specify the so-called Empty Grouping Set, the aggregation across the whole table. When you also look at the output of STATISTICS IO, you can see that the table Sales.SalesOrderHeader was accessed only once! That’s a huge difference from the previous manual implementation that we have performed.

Within the execution plan SQL Server uses a Table Spool operator that stores the retrieved data temporarily in TempDb. The data from the work table created in TempDb is afterwards used in the second branch of the execution plan. Therefore the data isn’t rescanned for every group from the base table, which leads to a better performance of the whole execution plan.

When you look at the execution plan, you can also see that the query plan contains 3 Stream Aggregate operators (highlighted in red, blue, and green). These 3 operators are calculating the individual grouping sets:

• The blue highlighted operator calculates the grouping set for CustomerID, SalesPersonID, and YEAR(OrderDate).
• The red highlighted operator calculates the grouping set for SalesPersonID and YEAR(OrderDate). In addition it also calculates the grouping set across “everything”.
• The green highlighted operator calculates the grouping set for CustomerID and YEAR(OrderDate).

The idea behind the 2 subsequent Stream Aggregate operators is to calculate so-called Super Aggregates – aggregations of aggregations.

Full Text Index helps to perform complex queries against character data.  These queries can include words or phrase searching.
We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. 
The index can contain up to 1024 columns. 

To create an Index, follow the steps:

1. Create a Full-Text Catalog
2. Create a Full-Text Index
3. Populate the Index

Background

When we want to use the full text index service we need to start the service first else it will raise the errors. So let's start with how to start the service and use the Full text index step by step.

Step 1

Create Table Tbl_Search
(
Id Int Primary Key Identity(1,1),
Title Varchar(500),
[Desc]Varchar(max)
)
Insert some rows in the created table.

Step 2

Now we will create full text index on our database and table for providing search in table. So create the Full Text Catalog on our database by using following query.CREATE FULLTEXT CATALOG FTSearch
In the query above we have created FullText Catalog with the name FTSearch.

Step 3

Now we will create full text index on our table Tbl_Search but for that we require the unique key id or primary key id so find the id of unique or primary key by using following command.SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

This command will display the all constraint names on tables present in our database from the output. Copy the Tbl_Search constraint name for creating a full text index on TblSearch.CREATE FULLTEXT INDEX ON Tbl_Search

(Title, [Desc] LANGUAGE 1033)

KEY INDEX PK__Tbl_Sear__3214EC0700551192

ON FTSearch
In the above statement you can see we are creating a FullText Index on table name with a parameter; this parameter is nothing but the column name of the table on which we want to create the full text index and language 1033 denotes the language English.

Step 4

Now it's time to search the records in a specified indexed table. For that we can write the queries like below.Select * from Tbl_Search Where Contains(Title,'Asp.Net')

Select * from Tbl_Search Where Freetext([Desc],'Asp.Net')
In the preceding queries you can see we have given a where clause with column name which is the column we want to search and what we want to search. The preceding queries retrive the rows of a table which contain ASP.Net in title column and the second query will retrieve the rows of ASP.Net in the desc column.

In some cases your queries gives an error like fdhost cannot be started. That means your FullTextIndex Service is not started; for that you have to first start the Demon Launcher for FullTextIndex service.

Step 5

For starting FullTextIndex service go to SQL Server Tool->SQL Server Configuration Manage->Service->FullTextSearch Demon Launcher if it is stopped then start this service for performing search operation with a contains and FreeText clause and restart the SQL Server instance.

Conclusion

In this way we can use the FullTextIndex on our database.

example:

• e-business—searching for a product on a website:

SELECT product_id   
FROM products   
WHERE CONTAINS(product_description, ”Snap Happy 100EZ” OR FORMSOF(THESAURUS,’Snap Happy’) OR ‘100EZ’)   
AND product_cost < 200 ; 

Recruitment scenario—searching for job candidates that have experience working with SQL Server:SELECT candidate_name,SSN   
FROM candidates   
WHERE CONTAINS(candidate_resume,”SQL Server”) AND candidate_division =DBA;  

 A. Creating a unique index, a full-text catalog, and a full-text index

USE AdventureWorks2012;  
GO  
CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);  
CREATE FULLTEXT CATALOG ft AS DEFAULT;  
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume)   
   KEY INDEX ui_ukJobCand   
   WITH STOPLIST = SYSTEM;  
GO  

Creating a full-text index on several table columns

USE AdventureWorks2012;  
GO  
CREATE FULLTEXT CATALOG production_catalog;  
GO  
CREATE FULLTEXT INDEX ON Production.ProductReview  
 (   
  ReviewerName  
     Language 1033,  
  EmailAddress  
     Language 1033,  
  Comments   
     Language 1033       
 )   
  KEY INDEX PK_ProductReview_ProductReviewID   
      ON production_catalog;   
GO  
Creating a full-text index with a search property list without populating it
USE AdventureWorks2012;  
GO  
CREATE FULLTEXT INDEX ON Production.Document  
  (   
  Title  
      Language 1033,   
  DocumentSummary  
      Language 1033,   
  Document   
      TYPE COLUMN FileExtension  
      Language 1033   
  )  
  KEY INDEX PK_Document_DocumentID  
          WITH STOPLIST = SYSTEM, SEARCH PROPERTY LIST = DocumentPropertyList, CHANGE_TRACKING OFF, NO POPULATION;  
   GO  
Later, at an off-peak time, the index is populated:
ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING AUTO;  
GO  

useful db-scripts

Rename database

• EXEC sp_renamedb 'oldDatabase, 'newDatabase'

• ---------------convert column to row------------------------

declare @col_to_rows TABLE(stu_name VARCHAR(30),subject VARCHAR(10),marks int);
INSERT INTO @col_to_rows VALUES('GEORGE','ECO',77);
INSERT INTO @col_to_rows VALUES('GEORGE','HIS',99);
INSERT INTO @col_to_rows VALUES('GEORGE','MAT',64);
INSERT INTO @col_to_rows VALUES('GEORGE','GEO',85);
INSERT INTO @col_to_rows VALUES('GEORGE','SCI',98);
INSERT INTO @col_to_rows VALUES('ROBERT','ECO',71);
INSERT INTO @col_to_rows VALUES('ROBERT','HIS',90);
INSERT INTO @col_to_rows VALUES('ROBERT','MAT',84);
INSERT INTO @col_to_rows VALUES('ROBERT','GEO',95);
INSERT INTO @col_to_rows VALUES('ROBERT','SCI',58);
INSERT INTO @col_to_rows VALUES('TIMOTHY','ECO',56);
INSERT INTO @col_to_rows VALUES('TIMOTHY','HIS',55);
INSERT INTO @col_to_rows VALUES('TIMOTHY','MAT',67);
INSERT INTO @col_to_rows VALUES('TIMOTHY','GEO',54);
INSERT INTO @col_to_rows VALUES('TIMOTHY','SCI',45);


SELECT stu_name,
max(CASE WHEN subject='ECO' THEN marks ELSE 0 END) ECO,
max(CASE WHEN subject='HIS' THEN marks ELSE 0 END) HIS,
max(CASE WHEN subject='MAT' THEN marks ELSE 0 END) MAT,
max(CASE WHEN subject='GEO' THEN marks ELSE 0 END) GEO,
max(CASE WHEN subject='SCI' THEN marks ELSE 0 END) SCI
FROM @col_to_rows
GROUP BY stu_name

--------------------------------------------------------------------

. A column has some negative values and some positive values. It is required to find the sum of negative numbers and the sum of the positive numbers in two separate columns

--select *from neg_pos

SELECT

SUM(CASE WHEN num < 0 THEN num ELSE 0 END) neg,

SUM(CASE WHEN num > 0 THEN num ELSE 0 END)pos

FROM neg_pos;

-------------------------------------------------
swap values between two rows for a table without using subquery

--ram   2000
--shyam 3000
--alex  5000
--joy   4000
-------------
--ram   2000
--shyam 5000
--alex  3000
--joy   4000
------------------------------------------------

update Employee_Test set EMP_SAL=a.EMP_SAL
from 
(
SELECT
   e1.emp_id, EMP_SAL=
   (case e1.emp_id 
when 2 then (LEAD(e1.EMP_SAL) OVER (ORDER BY e1.emp_id))
when 3 then LAG(e1.EMP_SAL) OVER (ORDER BY e1.emp_id)
else e1.Emp_Sal end
)
FROM Employee_Test e1  
)a
join Employee_Test on Employee_Test.Emp_ID =a.Emp_ID 

select *from Employee_Test
--------------------------------------------------

Create Database script

USE [master]

GO

CREATE DATABASE [AuthenticationDB] ON  PRIMARY 

( NAME = N'AuthenticationDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER008R2\MSSQL\DATA\AuthenticationDB.mdf' , 

   SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 

( NAME = N'AuthenticationDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER008R2\MSSQL\DATA\AuthenticationDB_log.ldf' , 

  SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [AuthenticationDB] SET COMPATIBILITY_LEVEL = 100

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [AuthenticationDB].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

ALTER DATABASE [AuthenticationDB] SET ANSI_NULL_DEFAULT OFF 

GO

ALTER DATABASE [AuthenticationDB] SET ANSI_NULLS OFF 

GO

ALTER DATABASE [AuthenticationDB] SET ANSI_PADDING OFF 

GO

ALTER DATABASE [AuthenticationDB] SET ANSI_WARNINGS OFF 

GO

ALTER DATABASE [AuthenticationDB] SET ARITHABORT OFF 

GO

ALTER DATABASE [AuthenticationDB] SET AUTO_CLOSE OFF 

GO

ALTER DATABASE [AuthenticationDB] SET AUTO_CREATE_STATISTICS ON 

GO

ALTER DATABASE [AuthenticationDB] SET AUTO_SHRINK OFF 

GO

ALTER DATABASE [AuthenticationDB] SET AUTO_UPDATE_STATISTICS ON 

GO

ALTER DATABASE [AuthenticationDB] SET CURSOR_CLOSE_ON_COMMIT OFF 

GO

ALTER DATABASE [AuthenticationDB] SET CURSOR_DEFAULT  GLOBAL 

GO

ALTER DATABASE [AuthenticationDB] SET CONCAT_NULL_YIELDS_NULL OFF 

GO

ALTER DATABASE [AuthenticationDB] SET NUMERIC_ROUNDABORT OFF 

GO

ALTER DATABASE [AuthenticationDB] SET QUOTED_IDENTIFIER OFF 

GO

ALTER DATABASE [AuthenticationDB] SET RECURSIVE_TRIGGERS OFF 

GO

ALTER DATABASE [AuthenticationDB] SET  DISABLE_BROKER 

GO

ALTER DATABASE [AuthenticationDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

GO

ALTER DATABASE [AuthenticationDB] SET DATE_CORRELATION_OPTIMIZATION OFF 

GO

ALTER DATABASE [AuthenticationDB] SET TRUSTWORTHY OFF 

GO

ALTER DATABASE [AuthenticationDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 

GO

ALTER DATABASE [AuthenticationDB] SET PARAMETERIZATION SIMPLE 

GO

ALTER DATABASE [AuthenticationDB] SET READ_COMMITTED_SNAPSHOT OFF 

GO

ALTER DATABASE [AuthenticationDB] SET HONOR_BROKER_PRIORITY OFF 

GO

ALTER DATABASE [AuthenticationDB] SET RECOVERY FULL 

GO

ALTER DATABASE [AuthenticationDB] SET  MULTI_USER 

GO

ALTER DATABASE [AuthenticationDB] SET PAGE_VERIFY CHECKSUM  

GO

ALTER DATABASE [AuthenticationDB] SET DB_CHAINING OFF 

GO

USE [AuthenticationDB]

GO

Script for creating Authentication tables in existing database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[__MigrationHistory](
[MigrationId] [nvarchar](150) NOT NULL,
[ContextKey] [nvarchar](300) NOT NULL,
[Model] [varbinary](max) NOT NULL,
[ProductVersion] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED 
(
[MigrationId] ASC,
[ContextKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetRoles](
[Id] [nvarchar](128) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserClaims](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ClaimType] [nvarchar](max) NULL,
[ClaimValue] [nvarchar](max) NULL,
[User_Id] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserLogins](
[UserId] [nvarchar](128) NOT NULL,
[LoginProvider] [nvarchar](128) NOT NULL,
[ProviderKey] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED 
(
[UserId] ASC,
[LoginProvider] ASC,
[ProviderKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserRoles](
[UserId] [nvarchar](128) NOT NULL,
[RoleId] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED 
(
[UserId] ASC,
[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUsers](
[Id] [nvarchar](128) NOT NULL,
[UserName] [nvarchar](max) NULL,
[PasswordHash] [nvarchar](max) NULL,
[SecurityStamp] [nvarchar](max) NULL,
[Discriminator] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_User_Id] ON [dbo].[AspNetUserClaims]
(
[User_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
  DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserLogins]
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
  DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_RoleId] ON [dbo].[AspNetUserRoles]
(
[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
  DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserRoles]
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
  DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AspNetUserClaims]  WITH CHECK ADD 
  CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY([User_Id])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserClaims] CHECK CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id]
GO
ALTER TABLE [dbo].[AspNetUserLogins]  WITH CHECK ADD  CONSTRAINT 
  [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserLogins] CHECK CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]
GO
ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT 
  [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]
GO
ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT 
  [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]
GO
USE [master]
GO
ALTER DATABASE [AuthenticationDB] SET  READ_WRITE 

GO