SQL Server Difference FAQs-7

1.Difference between Checkpoint and Lazy Writer

S.No	CheckPoint	Lazy Writer
1	Flush dirty pages to Disk	Flush dirty pages to disk
2	Flush only Data pages to disk	Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)
3	Default, Occurs approximately every 1 minute	Occurs depending upon memory pressure and resource availability
4	Can be managed with sp_confige -recovery interval option	It is lazy, Sql server manages by its own.
5	Does not check the memory pressure	Monitor the memory pressure and try maintain the available free memory.
6	Crash recovery process will be fast to read log as data file is updated.	No role in recovery
7	Occurs for any DDL statement	Occurs per requirement
8	Occurs before Backup/Detach command	Occurs per requirement
9	Depends upon the configuration setting, we can control.	Works on Least recent used pages and removed unused plans first, no user control.
10	For simple recovery it flush the tlog file after 70% full.	No effect on recovery model.
11	Can manually /Forcefully run command “Checkpoint”	No command for Lazy Writer
12	Very Less performance impact	No performance impact

2.Difference between Mirroring and Log Shipping

S.No	Mirroring	Log Shipping
1	Principle can have single mirror	Multiple stand by servers can be possible.
2	Generally good to have 10 DB’s for one server	No limit
3	No data loss and can be used as high availability like Clustering	May be some data loss as per schedule. And secondary server takes some manual work and time to be primary
4	Read log read and transfer the committed transaction through endpoints.	Transfer the log back up and restored at standby server.
5	Only committed transaction	Committed as well as uncommitted and whole log backup restores.
6	PAGE repair is possible if principle database page gets corrupt	N/A
7	Mirrored DB can only be accessed using snapshot DB	Secondary server can be reporting server (read-only)
8	Principle and Mirror server should have same edition	Primary and secondary server should be compatible server for restore.
9	Require FULL recovery model	Require  FULL or Bulk-Logged recovery model
10	Requires Sql Server 2005 SP1 or higher – Enterprise or Developer Editions	Enterprise edition for Sql Server 2000 and even Standard edition for 2005 can works
11	Immediate data moved depending on SEND and WAIT queue	Can control the flow of data by scheduling jobs
12	As Immediate data moves, user error reflects at mirrored DB	As delay in data transfer can avoided user error.

3.Difference between Change Track and Change Data Capture – CDC in SQL Server 2008

S.No	Change Track	Change Data Capture
1	It is about fact: It captures only the fact as the tracking table has changed. It does NOT capture the data. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that do not require the historical information, there is far less storage overhead because of the changed data not being captured	It is about the Data: Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed.
2	Storage: Internal tables are placed on the same filegroup as the parent entity. You could use the sys.internal_tables catalog view to show all the internal tables and parent entities. For example: select name, object_name(parent_id) as parent_object from sys.internal_tables	Storage: When change data capture is enabled for a database, a few things are added to the database, including a new schema (called cdc), some metadata tables, and a trigger to capture Data Definition Language (DDL) events. The two function names are, respectively, fn_cdc_get_all_changes_ and fn_cdc_get_net_changes_, with the capture instance name appended. Note that (like the change tracking feature) this functionality requires the table to have a primary key or other unique index.
3	Supported on “Simple” recovery model also. It is recommended that you use snapshot isolation when change tracking is enabled. Snapshot isolation itself can add significant workload overhead and requires much more careful management of tempdb.	Prevents Log truncation. Forces full logging of some bulk operations. One major point to note here is that once change data capture is enabled, the transaction log behaves just as it does with transactional replication—the log cannot be truncated until the log reader has processed it. This means a checkpoint operation, even in SIMPLE recovery mode, will not truncate the log unless it has already been processed by the log reader.
4	It uses synchronous tracking mechanism. once a database is enabled for change tracking, a version number is instituted, which allows ordering of operations	Change Data Capture (CDC) uses the asynchronous process that reads the transaction log.
5	Change Tracking has minimal impact on the system.	It has almost nil impact as it asynchronous mechanism reads from the transaction log.
6	It uses TempDB heavily	It uses transaction log.
7	DDL Restriction: There are restrictions on the DDL that can be performed on a table being tracked. The most notable restriction is that the primary key cannot be altered in any way. The other restriction worth calling out here is that an ALTER TABLE SWITCH will fail if either table involved has change tracking enabled.	No such DDL restriction
8	SQL Agent not needed	t requires SQL Agent to be running. SQL Agent Job & Transaction Replication: Two SQL Agent jobs may be created: the capture job and the cleanup job. I say "may be created" because the capture job is the same as the one used for harvesting transactions in transactional replication. If transactional replication is already configured, then only the cleanup job will be created and the existing log reader job will also be used as the capture job
9	Permission required to enable: SYSADMIN	Permission required to enable: DBOwner

4.Difference between Index Rebuild and Index Reorganize in SQL Server 2005

S.No	Index Rebuild	Index Reorganize
1	Index Rebuild drops the existing Index and Recreates the index from scratch.	Index Reorganize physically reorganizes the leaf nodes of the index.
2	Rebuild the Index when an index is over 30% fragmented.	Reorganize the Index when an index is between 10% and 30% fragmented.
3	Rebuilding takes more server resources and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions.	Always prefer to do Reorganize the Index.
4	T-SQL for Rebuilding all Indexes of a particular table. USE AdventureWorks; GO ALTER INDEX ALL ON HumanResources.Employee REBUILD GO	T-SQL for Reorganize all Indexes of a particular table. USE AdventureWorks; GO ALTER INDEX ALL ON HumanResources.Employee REORGANIZE GO

Note: If fragmentation is below 10%, no action required.

5.Difference between User -defined SP and System-defined SP

S.No	User-defined SP	System-defined SP
1	Once we create User defined SP in one database i.e available to only that database directly.i.e we cannot call it from some other DB’s directly	System defined sp are available in master DB.These sp’s can be directly called from any DB
2	UDSP will be used to fulfill the user requirements	SDSP will be used for managing sql server