Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Collation conflicts are encountered when u try cross database query or cross server query or joining tables, because joining tables might have different collation settings. You must handle these query with specifying appropriate collation while joining to tables having different collation data. Let Replicate the issue with following example.

Create following tables with script given below.

CREATE TABLE Department_Branch
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](40) NULL,[NoOfBranch] TinyInt NOT NULL
)
GO 
Insert Into Department_Branch Values('IT',40),('BIOLOGY',23),
('FINANCE',21),('MEDICAL',12),('TRANSPORT',5)
Go
 
CREATE TABLE [dbo].[Department_Account](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](60) Collate SQL_Latin1_General_CP1_CI_AI NULL,
[TotalSalary] [decimal](18, 0) NULL,
[TotalEmployee] [int] NULL
)
GO
Insert Into Department_Account Values('IT',5700000,76),('BIOLOGY',8900000,290),
('FINANCE',4500000,900),('MEDICAL',560000,17),('TRANSPORT',700000,15)
Go

Here you have two tables Department_Branch with column DepartmentName (has default collation “SQL_Latin1_General_CP1_CI_AS” and Department_Account with column DepartmentName with  Collation “SQL_Latin1_General_CP1_CI_AI”. Now Let join these to table and check the output.

Select A.DepartmentName,A.NoOfBranch,B.TotalEmployee,B.TotalSalary
From Department_Branch A
Join Department_Account B On A.DepartmentName=B.DepartmentName
Go

Output :

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Now to resolve above error you must specify collation.

Select A.DepartmentName,A.NoOfBranch,B.TotalEmployee,B.TotalSalary
From Department_Branch A Join Department_Account B 
On A.DepartmentName=B.DepartmentName Collate SQL_Latin1_General_CP1_CI_AS
Go

Output :

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

Inserting explicit value for identity column in a table can not be done directly as identity column is bound to rule of incrementing them with specified number. If you try to insert value explicitly you encounter following error message.

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'TableName' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So when you need to insert explicit value to identity column in a table you must set IDENTITY_INSERT ON for that table. Lets see following example.

Create table with following script.

Create Table Department_Master 
(
Id Int Identity(1,1),
DepartmentName Varchar(60),
IsActive bit default 1,
CreatedOn Datetime Default Getdate()
)
Go

I have Department_Master where “Id” column is an identity column which automatically inserts values into this column. Lets see what happens when you try to insert ID column value explicitly.

Try Following Insert Statement.

Insert Into Department_Master(Id,DepartmentName,IsActive,CreatedOn)
Values(1,'Biology',1,GETDATE())
Go

Output :

You can see the output.

Now to overcome this issue you must set Identity_Insert On for table specified. Let do that and check insert.

Set Identity_Insert Department_Master On
Go
Insert Into Department_Master(Id,DepartmentName,IsActive,CreatedOn)
Values(1,'Biology',1,GETDATE())
Go
Set Identity_Insert Department_Master Off
Go

Here I have set Identity_Insert On Before inserting the values and set Identity_Insert off after inserting the value. So you Set it off as next values should come automatically as defined in Identity Rule for that column.

Also you must note that in a database you can only set Identity_Insert On for a single table only. If you try to set Identity_Insert On for more then one table you might encounter following error.

Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'TableName’. Cannot perform SET operation for table 'TableName'.

So Make sure you set Identity_Insert Off once you have inserted Id value explicitly by setting Identity_Insert On.

Nested Transaction In SQL Server

SQL Server facilitate us to define nested explicit transaction. We may need to define nested transaction in many situation, so before you define or use nested transaction you must be aware of behavior of nested transaction.

Create tables from following script to perform following tests.

Create Table Parent_Table(Id int Identity(1,1),DataValue Varchar(20))
Go 
Create Table Child_Table(Id Int Identity(1,1),DataValue Varchar(20))
Go

This example explains that rolling back outer transaction will rollback all the inner transaction irrespective of whether those were committed or rolled back.

Start A Transaction and call it parent transaction

Begin Transaction Parent

Insert Data Into Parent Table

Insert Into Parent_Table Values('Parent')

Start another transaction and call it child transaction

Begin Transaction Child; --Start Child Transaction

Insert Data into Child Table

Insert Into Child_Table Values('Child')

Now Commit Child Transaction

Commit Transaction Child

Now Rollback  Parent Transaction

Rollback Transaction Parent

So in above code steps we have committed the child transaction and rolled back the parent transaction which implies that data should be inserted into parent table but here this is not the case rolling back outer transaction will rollback all the inner transaction (even if it was committed). So this is quite important and must be kept in mind while working or designing nested transaction. You can use following query to see data in parent and child table and you will see that no data exists in both the tables

Select * From Parent_Table
Go

Select * From Child_Table
Go

How To Move TEMPDB In SQL Server

In this blog post we will see how can we move location of tempdb database files in sql server. Moving of database may be required for many reasons like for security purpose or space constraints or etc. As tempdb is system database it cannot be detached or attached and cannot be restored like user databases. You need to be more alert when you are planning to move tempdb database files.

Let’s see how.

Step -1 :

Check Current Location Of Tempdb Files.

Use Tempdb
Go
Select * From SysFiles
Go

Output :

You Must Note Down These Path as you need to copy MDF and LDF files to new location later on.

Step – 2:

Move Tempdb Database File locations

Alter Database Tempdb 
Modify File(Name='Tempdev', FileName='D:\Tempdb.MDF')
Go

Alter Database Tempdb 
Modify File(Name='templog', FileName='D:\Templog.LDF')
Go

Above two queries moves location of MDF and LDF files. See Following screen shot for more clarification.

Step – 3 :

Now You have changed the location of files but to activate new location you must restart the SQL Server instance Service. Before you start SQL Server Instance you need to do following 2 steps.

Verification of new file locations.

So we can check that new filenames or paths are correct if you think the path or filename is incorrect then you can run above queries to move files again with correct filename or path and check.

Step – 4

Once you have verified the new locations you need to move tempdb files ( Data and Log File) to new location. So you can copy tempdb data and log file i.e. (MDF and LDF) file from OLD Location to New Location.

Step – 5

Now after moving the files you need to restart SQL Server Services. Until You restart SQL Will use old tempdb files.