SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SCD VS Merge
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shantheguy
Starting Member

India
21 Posts

Posted - 07/30/2013 :  03:24:59  Show Profile  Reply with Quote
Have a doubt in Merge. are there any drawbacks using Merge statement. As it is a TSQL statement will it log all operations in transaction log? or what kind of operations it will log. is it good to use in sql server where log file space is less for a 10000 records in a daily load. currently using SCD type 1 want to implement Merge.

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/30/2013 :  03:56:40  Show Profile  Reply with Quote
whether it will cause logfile to grow or not depends on recovery model used and also how frequently your log backups are configured.

This is a way to apply minimal logging with MERGE

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2010/06/03/minimal-logging-and-merge-statement.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shantheguy
Starting Member

India
21 Posts

Posted - 07/30/2013 :  05:18:37  Show Profile  Reply with Quote
So merge will log for every insert and update iam doing? which recovery model could be better..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/30/2013 :  07:04:46  Show Profile  Reply with Quote
simple recovery model will not cause log file to record every activity as it truncates the log upon each checkpoint. But if you want point in time restore you would need to use full recovery model to log details of each operation and should have a good backup strategy in place for log files to make sure it doesnt grow to huge sizes.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
769 Posts

Posted - 07/30/2013 :  13:33:56  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

simple recovery model will not cause log file to record every activity as it truncates the log upon each checkpoint. But if you want point in time restore you would need to use full recovery model to log details of each operation and should have a good backup strategy in place for log files to make sure it doesnt grow to huge sizes.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Every transaction is logged - regardless of recovery model. In simple and bulk recovery models, if the operation meets the minimally logged requirements then there will be minimal log entries.

The only difference will be when the VLF's in the log file are available for reuse. In simple, they will be available after the transaction is committed and a checkpoint has occurred. In full and bulk they will be available after the transaction is committed and a log backup occurs.

The way the SCD is built, each row entered would be considered a separate transaction. Using MERGE it will be a single transaction for all 10000 rows. Whether or not that will cause a problem depends on how large the log currently is - how large it needs to be for those 10000 rows, the recovery model in use, whether or not the transaction meets minimally logged requirements, etc...
Go to Top of Page

shantheguy
Starting Member

India
21 Posts

Posted - 08/01/2013 :  06:18:02  Show Profile  Reply with Quote
So comparatively which one is better SCD or Merge for around 150000 records of data every day? Please let me know
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/01/2013 :  07:39:09  Show Profile  Reply with Quote
Merge as it works on batched and not on row by row (See Jeffs explanation in last post)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000