Introduction

As a database professional, we might come across in situation where we need to compare data row by row or column wise between two tables which either resides in same database or in same instance or may be in different instance in different server. 

What do you in this situation?

1.) Do you write down script of your own?

2.) Do you use any third party software?

3.) Do you use “TableDiff” utility comes with SQL Server itself?

Third option, out of all of the above, seems good to me as we neither need to invent the zero again by writing down the script by our own nor we need to pay extra money to compare data.

“TableDiff” is one of the wonderful and oldest utility provided by Microsoft. It works fine with SQL Server 2000 to the latest SQL Server edition. However, I am providing you the script and example from my SQL Server 2008 instance.

Getting Ready

Before you move forward, you need to find out two tables whose data you wanted to compare. It might be in publisher/subscriber in replication, it might be in two different databases you are using for scale out or may be anywhere else.

If you don’t have this situation at the moment in your environment, don’t worry, I will be giving a script to raise the scenario to test “TableDiff” utility.

How to do it...

1.)    Open New Query window in you SQL Server

2.)    Create two different database by using following script:

USE master

GO

CREATE DATABASE TableDiffDb1

GO

CREATE DATABASE TableDiffDb2

GO

3.)    Create a sample table in “TableDiffDB1” database with following script

 USE TableDiffDb1

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

      DROP TABLE orders

END

GO

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--inserting 1000 sample rows into table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 1000

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

4.)    Creating “Orders” table in second database by copying 900 records (out of total 1000 records) from “Orders” table from “TableDiffDB1” database by using following script.

USE TableDiffDb2

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

      DROP TABLE orders

END

GO

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--inserting 900 sample rows into table from TableDiffDb1 database's Orders table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 900 OrderDate,Amount,Refno FROM TableDiffDb1.dbo.orders

5.)    Now use following command to see the difference between two tables.

exec master..xp_cmdshell 'tablediff -sourceserver [RITESH-SHAH\MSSQL2008] -sourcedatabase TableDiffDb1 -sourcetable Orders -destinationserver [RITESH-SHAH\MSSQL2008] -destinationdatabase TableDiffDb2 -destinationtable Orders -et Difference -f D:\OrdersDifference.sql'

Replace your server instance name in “SourceServer” and “destinationServer” parameter in above given command and you will get one .SQL file in D drive. Running that SQL file will insert all missing records in “Orders” table of “TableDiffDb2” database as it shows you the list of all missing records there.

There's more...

I would like to draw your attention to some of the facts which can help you if you don’t find “TableDiff” working in your environment.

Remember that “TableDiff.exe”  file resides in installation directory of SQL Server by default which is “C:\Program Files\Microsoft SQL Server\100\COM” in my case.  So, there is chance that “TableDiff” command is not accessible via DOS prompt, you have to set path for “TableDiff” in “ServerVariable”. 

You can reach “ServerVariable” by “MY Computer Properties > Advanced System Settings > Advanced > Environment Variables > System Variables > PATH”

If you find any path under “PATH” in “ServerVariable”, you can put “;” (semicolon) after that path and can add your path for “TableDiff”.

Generally people tend to use “TableDiff” from DOS prompt itself or via .bat (batch file) file but I have used “xp_cmdshell” extended stored procedure to show the use of command right from SQL Server but there may be a chance that “xp_cmdshell” is disable in your environment. If your security constraint allows, you can enable “xp_cmdshell”. For more details about the steps, click here.

Reference: Ritesh Shah

http://www.sqlhub.com
 

Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

MERGE is really a fantastic improvement in SQL Server 2008 which is really underutilized, I have seen many time recently that developers are still using separate DML statement for Insert / Update and Delete where there is a chance they can use MERGE statement of they can use condition based Insert / Update and Delete in one shot. 

This will give performance advantage as complete process is going to read data and process it in one shot rather than performing single statement to table each time you write.

I will give you one small example so that you can see how one can use MERGE statement or which situation we can use MERGE statement in???

Suppose we have one Member’s personal Detail table where we can find Memberid, member name, registration date and expiration date. There is one more table there for Member’s user name and password.

Now, we want to delete those users from memberLogin table whose expiration date has been met, we want to set default password for those member who are not expired right now and we want to make entry of those user who are just registered and id/password is not set yet.

--create Member's personal detail table and insert data in it.

Create Table MemberPersonalDetail

(

MemberID INT Identity(1,1),

MemberName Varchar(20),

RegisterDate date,

ExpirationDate date

)

GO

INSERT INTO MemberPersonalDetail

SELECT 'Ritesh Shah','01/01/2000','12/31/2015' Union ALL

SELECT 'Rajan Shah','02/07/2005','06/20/2011' Union ALL

SELECT 'Teerth Shah','06/22/2011','12/31/2015'

GO

SELECT * FROM MemberPersonalDetail

go

--create Member's login detail table and insert data in it.

CREATE TABLE MemberLoginDetail

(

MemberID INT,

UserName varchar(20),

UserPassword varchar(20)

)

GO

INSERT INTO MemberLoginDetail

SELECT 1,'Ritesh Shah','TestPassword' UNION ALL

SELECT 2,'Rajan Shah','goodluck'

GO

SELECT * FROM MemberLoginDetail

go

--MERGE statement with Insert / Update / Delete.....

--if you just need Insert / update or Insert / delete or Update / Delete anyting

-- you can use any combo

-- I have explained all three DML in one MERGE statement to demonstrate it.

MERGE MemberLoginDetail AS mld

USING (SELECT MemberID,MemberName,ExpirationDate FROM MemberPersonalDetail) AS mpd

ON mld.MemberID = mpd.MemberID

WHEN MATCHED AND mpd.ExpirationDate<getdate() THEN DELETE

WHEN MATCHED THEN UPDATE SET mld.UserPassword = 'DefaultPassword'

WHEN NOT MATCHED THEN

INSERT(MemberID,UserName,UserPassword)

VALUES(mpd.memberID,mpd.MemberName,'DefaultPassword');

GO

--check the table whether operation is successfully done or not.

SELECT * FROM MemberLoginDetail

go

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Filtered Index is nothing but just a feature of Non clustered index which I shown in previous two articles. It is just a non clustered index with WHERE clause in simple terms.

It is mainly used while you have big tables and you used to select only subset of data from that table. Like you have one big customer table and have one field of “Reference Person” in that table, it has NULL value if customer directly comes to us and has reference person’s name, if customer came from any of the reference. In this case if you want only those customers list that has reference person so that we can distribute some sort of consolation to those reference people.

The main advantage of “Filtered Index” is, it will have lower amount of root pages to store the data as it will consider only those rows which cater the needs of “Where” clause of “Filtered Index”.

Less number of pages means reduced storage size.  Since “Filtered Index” has only those data in root pages which caters the need of “Where” clause, means when you perform any DML operation like Insert, Delete or Update, “Filtered Index” will get effect only if it affects the Index Key which comes under the “Where” clause of Index so low maintenance cost. 

BTW, you can’t create “Filtered Index” on View but it will surely get benefit of the “Filtered Index” created on base table.

Let us check the impact of  “Filtered Index” practically.

--create one database which you can delete after running this example

create database SQLHub

GO

USE SQLHub

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('SQLHubFilteredIndex1', 'U') IS NOT NULL BEGIN

      DROP TABLE SQLHubFilteredIndex1

END

GO

--creating table

CREATE TABLE SQLHubFilteredIndex1 (ID INT IDENTITY Primary Key Clustered, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--inserting fack rows into table

INSERT INTO SQLHubFilteredIndex1 (OrderDate, Amount, Refno)

SELECT TOP 100

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

Union All

SELECT TOP 100000

      NULL,

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

--run the following query with execution plan together and see the results in execution plan

--you can see execution plan with the following steps

--first select both of the below given query

--Press Ctrl+M

--press F5

SELECT * from SQLHubFilteredIndex1 where OrderDate is not null

CREATE NONCLUSTERED INDEX idx_SQLHubFilteredIndex1 ON SQLHubFilteredIndex1(OrderDate)

WHERE OrderDate is not null

SELECT * from SQLHubFilteredIndex1 where OrderDate is not null

GO

--if you wish, you can uncomment below code and delete SQLHub database

----use master

----go

----drop database sqlhub

You can see in above screen shot that the same query ran faster after creating index.

if you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

One of my recent projects was dealing in many databases in one SQL Server instance. I required one procedure which can create one Windows authenticated login and associated user in all databases.  After creating users in databases, I needed to assign datareader and datawriter roles to those users. There is not inbuilt functionality in any of the SQL Server version so thought to create one stored procedure for my need. I have used it in my project and now want to share with all of you.

CREATE PROC CreateWindowsLoginAndUser(@FullLoginName sysname, @ActionName sysname)

as

begin

declare @DataBaseName sysname

declare @SQL varchar(255)

declare @Host sysname

declare @Login sysname

set @Host = LEFT(@FullLoginName,  charindex('\',@FullLoginName)-1)

set @Login = Right(@FullLoginName,   LEN(@FullLoginName)-charindex('\',@FullLoginName))

set @SQL = ''

if (@ActionName = 'CREATE')

        set @SQL = 'USE [master] CREATE LOGIN ['+@FullLoginName+'] from windows'

else

        set @SQL = 'USE [master] DROP LOGIN ['+@FullLoginName+']'

exec (@SQL)

declare dbname cursor for

select name from sysdatabases

where name not in ('master','model','msdb','tempdb') and

DATABASEPROPERTYEX(name,'IsInStandBy') = 0 and

DATABASEPROPERTYEX(name,'Status') not in ('OFFLINE','RESTORING','RECOVERING','SUSPECT','EMERGENCY') and

DATABASEPROPERTYEX(name,'Updateability') not in ('READ_ONLY') and

DATABASEPROPERTYEX(name,'UserAccess') = 'MULTI_USER'

order by name

open dbname

fetch next from dbname into @DataBaseName

while (@@fetch_status <> -1)

begin

        if (@@fetch_status <> -2)

        begin

        set @SQL = ''

        if (@ActionName = 'CREATE')

                set @SQL = 'USE ['+@DataBaseName+'] CREATE USER '+@Login+' FOR LOGIN ['+@FullLoginName+']'

        else

                set @SQL = 'USE ['+@DataBaseName+'] DROP USER '+@Login+''

        exec (@SQL)

        set @SQL = ''

        if (@ActionName = 'CREATE')

                set @SQL = 'USE ['+@DataBaseName+'] EXEC sp_addrolemember ''db_datareader'','''+@Login+''''

        exec (@SQL)

        set @SQL = ''

        if (@ActionName = 'CREATE')

                set @SQL = 'USE ['+@DataBaseName+'] EXEC sp_addrolemember ''db_datawriter'','''+@Login+''''

        exec (@SQL)

        end

fetch next from dbname into @DataBaseName

end

close dbname

deallocate dbname

end

go

Once you are done with creating script, you can execute it by following commands.

--to create user, use following TSQL

exec CreateWindowsLoginAndUser 'DomainName\WindowsLoginName','CREATE'

GO

--to drop user, use following TSQL

exec CreateWindowsLoginAndUser ' DomainName\WindowsLoginName','DROP'

GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Today I gave solution for one strange problem in one of the forum; I thought to share that script with all of you. Requirement was something like below:

-- Update all field of Table which is NULL

--NULL data should be populated with NOT NULL value of the same column

Well, this is somehow strange but it was needed so I quickly create one small script with the help of cursor, however, I always avoid cursor as long as possible. I didn’t find any other quick solution at that time.

--create table for demo

if OBJECT_ID('emps','U') is not null drop table emps

CREATE TABLE [dbo].[emps](

      [Name] [varchar](50) NULL,

      [Dept] [varchar](10) NULL,

      [Company] [varchar](15) NULL

) ON [PRIMARY]

 

GO

--insert some data

INSERT INTO emps

SELECT 'RITESH','MIS','CHEM' UNION ALL

SELECT 'RAJAN',NULL,NULL UNION ALL

SELECT NULL,'ACCT','MAR'

GO

 

--script with cursor

declare @SQL nvarchar(max)

DECLARE @ColName VARCHaR(15)

set @SQL=''

 

DECLARE FirstCur CURSOR FORWARD_ONLY

FOR select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='emps'

 

OPEN FirstCur

FETCH FROM FirstCur INTO @ColName

 

WHILE @@FETCH_STATUS=0

BEGIN

      SET @SQL=@SQL+ ' Update Emps SET ' + @ColName + ' = (SELECT top 1 ' + @ColName + ' FROM emps where ' + @ColName + ' is not null) where ' + @ColName + ' is null; '

      FETCH NEXT FROM FirstCur INTO @ColName

END

print @sql

CLOSE FirstCur

DEALLOCATE FirstCur

exec sp_executeSQL @SQL

go

 

--CHECK DATA

select * from emps

Happy Coding!!!!

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com