Under compatibility level 150, in both SQL Server 2019 and Azure SQL Database, you now can use batch mode for CPU-bound analytic type workloads without requiring columnstore indexes. There is no action needed to turn on batch mode aside from being on the proper compatibility mode. You also have the ability to enable it as a database scoped configuration option (as shown below), and you can hint individual queries to either use or not use batch mode (also shown below). If you recall in my earlier blogs on columnstore, it is batch mode in conjunction with page compression that drastically increases query performance. This feature, Batch Mode on Rowstore, allows all operators enabled for batch mode to operate as such in SQL Server.
 

What does this mean? It means that query operations can process data faster, more efficiently and mimic what makes columnstore so fast. Instead of reading row by row (row store mode) it can read in chunks i.e. batches of 900 rows at a time. The performance impact of this can be tremendous which effectively uses CPU more efficiently.
 
Just like columnstore this only benefits analytic type workloads or data warehouses, as mentioned above. This is meant for aggregations and joins that process thousands of rows. It will not benefit you when processing singleton lookups. If where clause that does not look up a range of values and is just satisfying predicates, then batch mode does not provide a benefit.
 
How does the engine know when to use batch mode? According to docs.microsoft.com the query processor uses heuristics and will make decision based on three checks. An initial check on tables sizes, operators used and cardinality estimates. Then the optimize checks to see if there are cheaper plans it can use. If no alternative better plans are available, the optimizer will choose batch mode. There are some limitations that will prevent the use of batch mode such as, in-memory OLTP tables or for any index other than B-Trees or on-disk heaps. It will also not work for LOB columns including sparse and XML columns.
 
You can easily decipher when batch mode is used to run query inside the operator’s properties. Let’s see a demo.
 
To demo I want to first show you a plan NOT using Batch Mode on Row Store, so let’s turn the feature off because as I mentioned earlier it is already enabled for compatibility mode 150 by default. Run the below database scope configuration script to turn it off.
    USE AdventureworksDW2016CTP3 
    GOALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF; 

Now let’s run this query and make sure we capture the execution plan.
    SELECT FS.[ProductKey], 
           AVG([SalesAmount]), 
           [UnitPrice] 
    FROM [dbo].[FactResellerSalesXL] FS 
        JOIN dbo.DimProduct DP 
            ON DP.ProductKey = FS.ProductKey 
    GROUP BY FS.[ProductKey], 
                [UnitPrice] 

Note the Table Scan. By hovering over it you can see the operator’s properties and see the Actual Execution Mode says ROW and it processed 11,669,653 rows.

Now let’s run it again in Batch. Instead of changing compatibility lets just turn on the feature with an OPTION HINT.

SELECT FS.[ProductKey], 
       AVG([SalesAmount]), 
       [UnitPrice] 
FROM [dbo].[FactResellerSalesXL] FS 
    JOIN dbo.DimProduct DP 
        ON DP.ProductKey = FS.ProductKey 
GROUP BY FS.[ProductKey], 
            [UnitPrice] 
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE')); 

You can clearly see the optimizer chose to use BATCH mode based on our HINT. In addition, you can see it ran significantly faster at only 405 ms versus 1.119s using row mode. In general, we’ve seen queries that benefit from batch mode running in almost half of what row mode performance is and columnstore in batch mode performance.

Let’s go ahead and change back to the default Batch Mode again for our database just to prove it would have used batch mode without the use of our hint. Run the below and look at the plan.

    ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON; 
    GO 
     
    SELECT FS.[ProductKey], 
           AVG([SalesAmount]), 
           [UnitPrice] 
    FROM [dbo].[FactResellerSalesXL] FS 
        JOIN dbo.DimProduct DP 
            ON DP.ProductKey = FS.ProductKey 
    GROUP BY FS.[ProductKey], 
                [UnitPrice] 

BINGO! There you have it!

If you are already using compatibility mode 150 you are already taking advantage of this feature and may not even realized. For those that have not made the leap to 2019 I highly recommend it, if only for this little gem which is one of the many reasons why you should upgrade.

The title sounds like something that you would see for a boxing match. However, in reality it is a misleading title. The Max Degree of Parallelism and the Cost Threshold for Parallelism SQL Server settings actually work more together than they do against each other.

These two settings actually define the how many and the when in regards to parallel execution plans. The Max Degree of Parallelism (MDop) simply defines the number of processors/cores that SQL Server will use when the optimizer determines parallelism is needed. The Cost Threshold for Parallelism is cost threshold of when the SQL Server will use parallelism. The cost is the overall cost the optimizer determines for each query and SQL Server will use parallelism if the cost is above the threshold value.
 

The recommended settings for MDop is the number of cores not to exceed 8. However, when setting this, it is important to continue to monitor the system to see if the change has caused an improvement. The default value is 0.
 
The recommended setting for the Cost Threshold for Parallelism is 25 to 50 and has a default value of 5. Yes, the default setting is too low. By changing this, SQL Server will reduce the number of smaller queries that may use parallelism. Just like the MDop setting, it is important to monitor the server after making a change to see if an improvement is make. There have been many times where simply changing this setting from the default, I have seen the CPU utilization drop from close to 100% to less than 10%. This in no way means you will see the same improvement, just what I have seen.
 
Neither one of these settings require a reboot of the service when changing. It is also important to keep in mind that if the MDop is set to 1, SQL Server will ignore the Cost Threshold for Parallelism setting.
 
This snippet of code can be used to query server configuration settings
    SELECT CONVERT(VARCHAR(60), name) AS 'Name' 
    , value 
    , description 
    FROM sys.configurations

I was working with a client and they had set up one sql server for an ETL process. When we tried to get the data from  the database we got the error:
"Could not find server 'server name' in sys.servers in SQL Server"

How to resolve this
First you need to check if the server exists in sys servers,
select name from sys.servers 

You will get the servers list here, if the server does not exist in the list, then add it using the command,
EXEC sp_addlinkedserver @server = 'New_Server_Name' 

Once the server is added to the linked server, then you can log in like this,
EXEC sp_addlinkedsrvlogin 'New_Server_Name','false',NULL,'USERNAME','PASSWORD' 

Now you can do whatever you want to do, you can use your local server now,
exec [New_Server_Name].[Database_Name].dbo.Procedure_NAME

Finally, you can drop this server from the linked server list using this command,
sp_dropserver 'New_Server_Name', 'droplogins' 

There's so many new capabilities to play around and explore in SQL Server 2019. One of the new capabilities that has caught our attention is the introduction of JSON support. In this post, I will get started in creating a JSON document using SQL Server 2019.

SQL Server 2016 will support JSON (JavaScript Object Notation). JSON is an open, text-based exchange format based on JavaScript’s object literal notation. JSON is a popular data-interchange format used in modern web and mobile applications, as well for storing unstructured data. It is supported in several DB/NoSQL engines and this improvement will enable developers to put some JSON processing logic in the SQL Server that will enable them to parse, query, analyze, and update JSON data.

Step 1

In this post, you will query a standard table and then convert it into JSON format. For this example, you are querying the standard AdventureWorks database:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]

Sample output for rows inside SQL Server Management Studio looks like below:

Step 2

Now, you will add the FOR JSON AUTO support to get the same output in JSON format.

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON AUTO

The output looks like below:

[
{
"PersonType":"EM",
"FirstName":"X",
"MiddleName":"Y",
"LastName":"Z",
"EA":[{"EmailAddress":"[email protected]"}]
},
{"PersonType":"EM",
"FirstName":"A",
"MiddleName":"B",
"LastName":"C",
"EA":[{"EmailAddress":"[email protected]"}]
}
]

Step 3

You can also generate JSON using the PATH option like:

SELECT TOP(2) PP.[PersonType], PP.[FirstName], PP.[MiddleName], PP.[LastName], EA.[EmailAddress]
FROM Person.Person PP
INNER JOIN [Person].[EmailAddress] EA
ON EA.[BusinessEntityID] = PP.[BusinessEntityID]
FOR JSON PATH

The output for this query would look little different like:

[
{"PersonType":"EM", "FirstName":"X”, “MiddleName":"Y”, “LastName":"Z”, “EmailAddress":"[email protected]"},{"PersonType":"EM”, “FirstName":"A”, “MiddleName":"B”, “LastName":"C”, “EmailAddress":"[email protected]"}
]

Now that you are able to generate few simple output using JSON keyword.

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Hi all, In this blog, I will explain to you how to apply a script or a query to multiple databases in a single execution using a database name. I get all database names, create a loop, and execute a command that I want to do.

Here I am using 2 databases, EnterpriseGL and GR8. I get all database names using a loop, and after that, I get database names one by one, put a script with a name, and execute the script.
  BEGIN TRAN 
    
  CREATE TABLE #TempGETDBLIST 
     ( 
        [NAME] NVARCHAR(255) NULL, 
        [ROWID] INT IDENTITY NOT NULL 
     ) 
           INSERT INTO #TempGETDBLIST (NAME) 
    
        --unomment which database use want to apply 
       --SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%EnterpriseGL%'  -- get all GL databases 
        --SELECT NAME FROM master.dbo.sysdatabases WHERE name like '%GR8%' --get all GR8 databases 
    
          SELECT '#TempGETDBLIST',* FROM #TempGETDBLIST 
           DECLARE @Flag INT = 1 
    
  WHILE (@Flag <= (SELECT COUNT(1) FROM #TempGETDBLIST)) 
  BEGIN 
        DECLARE @ABC NVARCHAR(50), @query NVARCHAR(max) 
        SET @ABC =(SELECT Name FROM #TempGETDBLIST WHERE ROWID = @Flag) 
        SET @query = 'USE '+ @ABC +' select top 1 * from SystemConfiguration' 
    
     EXECUTE( @query ) 
     SET @Flag = @Flag + 1 
    
  END 
    
  DROP TABLE #TempGETDBLIST 
  ROLLBACK TRAN

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

The SQL 'Is Null' statement returns the specified value if the expression is NULL, otherwise, it returns the expression. If the expression is NOT NULL, then this function returns the expression.

Syntax
    SELECT ISNULL(NULL, 'Csharpcorner');   

This replaces Null with the specified replacement value. 
 
A null statement doesn't specify that the Boolean result is negated. The predicate reverses its return values, returning True if the value is not Null, and False if the value is a Null statement.
 
Syntax
    ISNULL ( check_expression , replacement_value ) 

This is the expression returned if the check_expression is NULL. The replacement_value must be of a type that is implicitly convertible to the type of check_expression.
 
Using IsNull with Avg in SQL statement
 
The following example finds the average of the OrderDetails.  It substitutes the value 8 for all NULL entries in the orderId column of theOrderDetails table.
 
Syntax
    USE sample ;     
    GO     
    SELECT AVG(ISNULL(OrderId, 8))     
    FROM OrderDetails ;     
    GO     

Example

Using ISNULL in SQL
The following example selects the description, discount percentage, minimum quantity, and maximum quantity for all special offers in the sample. If the maximum quantity for a particular special offer is NULL, the MaxQty shown in the result set is 0.00. 
 
Syntax
    USE sample ;     
    GO     
    SELECT OrderId , OrderName, Orderaddress , ISNULL(OrderId, 0.00) AS 'Max Quantity'     
    FROM OrderDetails;     
    GO   

Testing for NULL in a Where clause
Do not use ISNULL to find NULL values; use IS NULL instead. The following example finds all OrderDetails that have NULL in the ordername, orderAddress, OrderDate  column. Note the space between IS and NULL.
 
Syntax
    -- Uses sample     
    SELECT OrderName, orderAddress, OrderDate     
    FROM  OrderDetails     
    WHERE OrderId  IS NULL;  

A SQL cursor is a database object that is used to retrieve data from a result set one row at a time. A SQL cursor is used when the data needs to be updated row by row. This article explains everything about SQL cursors. In this article, we will learn the following:

•     Introduction to SQL cursor
•     Cursor life cycle
•     Why and when use a cursor
•     How to implement cursors
•     What are the limitation of SQL cursor
•     How can we replace a SQL Cursor

SQL Cursor Life Cycle
The following steps are involced in a SQL cursor life cycle.

    Declaring Cursor
    A cursor is declared by defining the SQL statement.

    Opening Cursor
    A cursor is opened for storing data retrieved from the result set.

    Fetching Cursor
    When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

    Closing Cursor
    The cursor should be closed explicitly after data manipulation.

    Deallocating Cursor
    Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.

Why use a SQL Cursor?
In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors.

In programming, we use a loop like FOR or WHILE to iterate through one item at a time, the cursor follows the same approach and might be preferred because it follows the same logic.
SQL Cursor Syntax
    DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]  
    [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]  
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]  
    [ TYPE_WARNING ] FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]  

Cursor Example
The following cursor is defined for retrieving employee_id and  employee_name from Employee table.The FETCH_STATUS value is 0 until there are rows.when all rows are fetched then  FETCH_STATUS becomes 1.
    use Product_Database 
    SET NOCOUNT ON;   
     
    DECLARE @emp_id int ,@emp_name varchar(20),   
        @message varchar(max);   
     
    PRINT '-------- EMPLOYEE DETAILS --------';   
     
    DECLARE emp_cursor CURSOR FOR    
    SELECT emp_id,emp_name   
    FROM Employee 
    order by emp_id;   
     
    OPEN emp_cursor   
     
    FETCH NEXT FROM emp_cursor    
    INTO @emp_id,@emp_name   
     
    print 'Employee_ID  Employee_Name'      
     
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        print '   ' + CAST(@emp_id as varchar(10)) +'           '+ 
            cast(@emp_name as varchar(20)) 
     
         
        FETCH NEXT FROM emp_cursor    
    INTO @emp_id,@emp_name   
      
    END    
    CLOSE emp_cursor;   
    DEALLOCATE emp_cursor;   

The Output of the above program will be as follows

SQL Server
What are the limitations of a SQL Cursor

A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes.

Cursors can be faster than a while loop but they do have more overhead.

Another factor affecting cursor speed is the number of rows and columns brought into the cursor. Time how long it takes to open your cursor and fetch statements.

Too many columns being dragged around in memory, which are never referenced in the subsequent cursor operations, can slow things down.

The cursors are slower because they update tables row by row.
How can we replace SQL Cursors
There's one replacement for cursors in SQL server joins.

Suppose we have to retrieve data from two tables simultaneously by comparing primary keys and foreign keys. In these types of problems, the cursor gives very poor performance as it processes through each and every column. On the other hand using joins in those conditions is feasible because it processes only those columns which meet the condition. So here joins are faster than cursors.

The following example explains the replacement of cursors through joins.

Suppose, we have two tables, ProductTable and Brand Table. The primary key of BrandTable is brand_id which is stored in ProductTable as foreign key brand_id. Now suppose, I have to retrieve brand_name from BrandTable using foreign key brand_id from ProductTable. In these situations cursor programs will be as follows,
    use Product_Database 
    SET NOCOUNT ON;   
     
    DECLARE @brand_id int    
    DECLARE @brand_name varchar(20)  
     
     
    PRINT '--------Brand Details --------';   
     
    DECLARE brand_cursor CURSOR FOR    
    SELECT distinct(brand_id) 
    FROM ProductTable;  
     
    OPEN brand_cursor   
     
    FETCH NEXT FROM brand_cursor    
    INTO @brand_id   
     
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        select brand_id,brand_name from BrandTable where brand_id=@brand_id 
    --(@brand_id is of ProductTable) 
         
        FETCH NEXT FROM brand_cursor    
    INTO @brand_id  
      
    END    
    CLOSE brand_cursor;   
    DEALLOCATE brand_cursor;   

The Output of the above program will be as follows

SQL Server
The same program can be done using joins as follows,
Select distinct b.brand_id,b.brand_name from BrandTable b inner join
ProductTable p on b.brand_id=p.brand_id

The Output of the above program will be as follows

SQL Server
As we can see from the above example, using joins reduces the lines of code and gives faster performance in case huge records need to be processed.

The Unique constraint statement ensures that all values in a column are different. Both the Unique and Primary Key constraints provide a guarantee for uniqueness for a column or set of columns. A Primary key constraint automatically has a unique constraint in SQL. However, you can have many unique constraints per table, but only one primary key constraint per table.

 
We can create a unique constraint in SQL Server 2019 (15.x) by using SQL server management studio or SQL to ensure no duplicate values are entered in specific columns that do not participate in a primary key. Creating a unique constraint automatically creates a corresponding unique index in SQL.  
 
SQL Server Unique constraints allow you to ensure that the data stored in a column, or a group of columns, is unique among the rows in a table. 
 
Syntax
    CREATE TABLE EmployeeName  (       
     EmpID int NOT NULL UNIQUE,       
      EmpName varchar(255) NOT NULL,        
    );    

The above query creates a table with the name "EmployeeName"  and column name EmpID which is both Not Null and Unique(i.e we cannot have empty or duplicate data) and EmpName
 
Using Unique constraint on create table statement
 
Syntax
    CREATE TABLE Employee  (     
     EmpID int NOT NULL UNIQUE,     
      EmpName varchar(255) NOT NULL,     
      EmpFirstName varchar(255),     
      EmpLastname varchar(255),     
      EmpAge int     
    );    

The above query created a table with the name "Employee" and the first column name EmpId is Not NULL and UNIQUE, other column name EmpName,EmpFirstName,EmpLastname, EmpAge
 
Using Unique constraint on alter table
 
Syntax
    ALTER TABLE Employee      
    ADD UNIQUE (EmpID);    

 The above query with add a column EmpID and make it UNIQUE.
 
"Unique" is used to signify a Unique constraint, and also to define a unique name a Unique constraint,on multiple columns.
 
Syntax
    ALTER TABLE Employee      
    ADD CONSTRAINT UC_Employee UNIQUE (EmpID,EmpLastName);    

The above query will add EmpID and EmpLastName columns into the UC_Employee table, with the Unique Constraint
 
Using Drop a Unique constraint statement
 
Use the following example to drop a Unique constraint:
 
Syntax 
    ALTER TABLE Employee      
    DROP CONSTRAINT UC_Employee;    

The above query will remove the "Unique" Constrain from the Employee table.
 
Using SQL Server Management Studio in Unique Constraint
 
To create a unique constraint statement:
    In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.
    On the Table Designer menu, click Indexes/Keys.
    In the Indexes/Keys dialog box, click Add.
    In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.
    On the File menu, click Save table name.

Using unique constraint in SQL
 
To create a unique constraint,

    In Object Explorer, connect to an instance of Database Engine.
    On the Standard bar, click New Query.
    Copy and paste the following example into the query window and click Execute. This example creates the table SampleDetails and creates a unique constraint on the column TransactionID.

Syntax
    USE sample ;       
    GO       
    CREATE TABLE SampleDetails       
     (       
       TransactionID int NOT NULL,        
       CONSTRAINT AK_TransactionID UNIQUE(TransactionID)        
    );        
    GO      

The above query will create a table SampleDetails in the sample database, with TransactionID and the AK_TransactionID constraint which makes TransactionID unique
 
To create a unique constraint on an existing table
    In Object Explorer, connect to an instance of Database Engine.
    On the Standard bar, click New Query.
    Copy and paste the following example into the query window and click Execute. The example creates a unique constraint on the columns PasswordHash and PasswordSalt in the table Person.Password. 

Syntax
    USE sample         
    GO       
    ALTER TABLE Person.Password        
    ADD CONSTRAINT AK_Password UNIQUE (PasswordHash, PasswordSalt);        
    GO       

The above query appends the table Person.Password in the sample database , with the AK_Password CONSTRAINT which makes PasswordHash amd PasswordSalt unique.
 
To create a unique constraint in a new table
    In Object Explorer, connect to an instance of Database Engine.
    On the Standard bar, click New Query.
    Copy and paste the following example into the query window and click Execute. The example creates a table and defines a unique constraint on the column TransactionID.

Syntax 
    USE sample;       
    GO       
    CREATE TABLE Production.TransactionHistoryArchive2       
    (       
       TransactionID int NOT NULL,       
       CONSTRAINT AK_TransactionID UNIQUE(TransactionID)       
    );       
    GO      

The above query uses the sample database and creates a table with the name "Production.TransactionHistoryArchive2" and column name TransactionID and CONSTRAINT AK_TransactionID UNIQUE column name is TransactionID.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Sometimes, we have a scenario when we want to get a common set of rows from 2 different result sets. For example, we have 2 queries and both returns employees record. If we want to find who all employees are present in both result sets, that time we can use INTERSECT to get the result. Below is the graphical representation of how INTERSECT works.

If you see in the above graphical representation, the left 2 circles have the B and C letters in common. The left side picture shows how the 2 circles have B and C letters common which is nothing but an intersection.

Now let's see how it works in the database.

So we are going to create 2 tables, EmpTable and ManagerEmp and then we will insert records in them.
CREATE TABLE EmpTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50)) 
CREATE TABLE ManagerTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50)) 
 
INSERT INTO EmpTable 
SELECT EmpName='John',City='Stamford',Title='Operator' 
 
INSERT INTO EmpTable 
SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer' 
 
INSERT INTO EmpTable 
SELECT EmpName='Smith',City='Wilton',Title='Driver' 
 
 
INSERT INTO ManagerTable 
SELECT EmpName='Mike',City='Wilton',Title='Driver' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Smith',City='Wilton',Title='Driver' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Jonathan',City='Armonk',Title='Accountant' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Warner',City='Stamford',Title='Customer Service' 
 
INSERT INTO ManagerTable 
SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer' 

Now run below the query to find out the common employees in both tables.
SELECT * FROM EmpTable 
 
SELECT * FROM ManagerTable 
 
-- INTERSECTION 
SELECT * FROM EmpTable 
INTERSECT 
SELECT * FROM ManagerTable 

Here is the output.

If you see below, in both the "EmpTable" and the "ManagerTable" tables, Luis and Smith both are employees. To join these 2 queries with INTERSECT, it gave these 2 names.

One thing is to remember here is both the tables/result sets should have the same columns and the same datatype for those columns, otherwise, it may give you "Conversion failed when converting...." if the data type does not match.

ERROR - "Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS within the up to operation."

Don’t panic if you get this error while joining your tables. there's an easy way to solve this. It happens because of the different collation settings on 2 columns we are joining.

The first step is to figure out what are the two collations that have caused the conflicts.
Let us assume that collation has not been set at the column level and instead at the db level. Then, we've to execute two straightforward statements as below.
Statements

• Select DATABASEPROPERTYYEX('DB1',N'Collation')
  
• Select DATABASEPROPERTYYEX('DB2',N'Collation') 

One more thing to make a note of here is that if you are on SharePoint, you will get an error as following.

Latin_General_CI_AS_KS_WS.
 
If you are on any other database and use the default settings, you may get this SQL_Latin_General_CP1_CI_AS.

Now, we have to do something similar to CAST, called Collate (FOR Collation).

Refer to the example below.
      select * from Demo1.dbo.Employee emp 
    join Demo2.dbo.Details dt 
    on (emp.email =dt.email COLLATE SQL_Latin_General_CP1_CI_AS) 

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.