Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shantheguy
Starting Member
22 Posts |
 Posted - 2013-07-30 : 03:24:59
|
| 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
52326 Posts |
|
|
shantheguy
Starting Member
22 Posts |
Posted - 2013-07-30 : 05:18:37
|
| So merge will log for every insert and update iam doing? which recovery model could be better.. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-30 : 07:04:46
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-07-30 : 13:33:56
|
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 MVPhttp://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... |
|
|
|
shantheguy
Starting Member
22 Posts |
Posted - 2013-08-01 : 06:18:02
|
| So comparatively which one is better SCD or Merge for around 150000 records of data every day? Please let me know |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-01 : 07:39:09
|
| Merge as it works on batched and not on row by row (See Jeffs explanation in last post)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
SCD VS Merge