Replication in SQL Server

Replication
- process of distributing data from one database to another on the same server or servers connected through LAN or the internet.
- synchronize data between databases that may be at remote location.
- maintain copies of database at more than one locations.
- It can be used for load balancing of server by maintaining area specific data independently and replicate later to have single version of data

Types of Replication

1. Snapshot Replication
2. Merge Replication
3. Transactional Replication

Snapshot Replication
- Snapshot of entire data is copied from publisher to the subscriber's database on regular
interval.
- This kind of replication is used for replicating data that doesn’t change frequently.
- It is used where the source can manage with out of date data.
- This replication is considered when amount of data to be replicated is small.

Merge Replication
- This allows both publisher and subscriber to work independently, online or offline and merges
the changes later.
- Here changes are track on both publisher and subscriber and then merged.

Transactional Replication
- This kind is used when changes are frequent.
- The replication agent monitors the changes at the publisher and distributes the changes to the
subscribers.
- Its required where up to date data is required.

Cursors in SQL Server

Cursors
-It is a special programming construct that allows data to be manipulated on a row-by-row basis.
-They point to a certain location within a record set and allow the operator to move forward (and sometimes backward, depending upon the cursor type) through the results one record at a time.
-A cursor is used to access the result set stored in the memory on execution of a query.


Steps to use Transact-SQL Cursor.

Declare the cursor,
Open the cursor,
Fetch record row by row,
Close cursor,
Deallocate cursor.


Example of a cursor

Declare @EmpId int
Declare curEmp CURSOR READ_ONLY FOR SELECT EmpId FROM Employee
Open curEmp
Fetch next from curEmp into @EmpId
While @@FETCH_STATUS = 0
Begin
Print @EmpId
Fetch next from curEmp into @EmpId
End
Close curEmp
Deallocate curEmp


Different types of cursors

1. DYNAMIC: It reflects changes happened on the table while scrolling through the row.

2. STATIC: It works on snapshot of record set and disconnects from the server. This kind
doesn’t reflects changes happened on the table while scrolling through the row.

3.KEYSET: In this kind, new record is not reflected, but data modification can be seen.
Define the cursor lock types.

SQL Server supports three types of cursor namely Transact-SQL server cursor, API server cursor, and client cursor.


Disadvantages/limitation of the cursor
- consume network resources.
- Close cursor when it is not required.
- You shouldn’t forget to deallocate cursor after closing it.
- Cursor requires a network roundtrip each time it fetches a record.

Avoid cursors
- Using the SQL while loop
- User defined functions
- Using CTE(Common Table Expression) Using WITH Clause.
-Temporary table
- Table variable
- You should fetch least number of records.
- You should use FORWARD ONLY option when there is no need to update rows.

Differences between User Defined Functions and Stored Procedures

User Defined Functions

1 UDF are simpler to invoke than Stored Procedures from inside another SQL statement.
2 SP's are Complex to Invoke.
3 UDF's must be prefaced with the owner name, DBO in this case. Not mandatory.
4 When call function then the parameter must transmit full.
5 A UDF is always used to return a value or a table object.
6 Function return type could be scalar or table or table values(SQL Server).
7 Function returns only one value at a time
8 We can call the functions in SQL statements (select max(sal) from emp)
9 Function do not return the images, text.
10 Functions are used for computations.
11 Functions MUST return a value.
12 Function parameters are always IN.
13 UDF can be used in the SQL Statements anywhere in the WHERE/HAVING/SELECT section.
14 UDF's only can have input parameters.


Stored Procedures

1 Stored Procedure is pre compiled execution plan Functions are not precompiled.
2 Stored procedure returns always integer value by default zero.
3 Stored Procedure returns more than one value at a time.
4 We cannot call Stored Procedures in SQL Statements.
5 Function do not return the images, text. Stored Procedure returns all.
6 Procedures can be used for performing business logic.
7 Functions MUST return a value. Procedures need not be.
8 Function parameters are always IN, no OUT is possible Stored procedures can have input and output parameters.
9 Stored procedures cannot be used WHERE/HAVING/SELECT section .