Replication in SQL Server.
Replication in SQL Server is a way of getting a copies of your data in all multiple places at a single time.In SQL Server there have been Different forms of Replication.
1.Snaphot.
2.Transactional.
3.Transactional with updating subscribers.
4.Merge.
Snapshot.
Snapshot is a technique in SQL simple to copy of a database.The Original database has to be locked.which also made as good as unusable while the snapshot is generated.This results in copy of database is identical.
These Forms are normally unused by themselves.Instead they are serve as an starting point for other forms of a replications.A snapshot to get another copy up and running and then use some different form of replication to keep the entire copy uptodate all the time.
Transactional.
This is a other form of replication ,i.e data flows from a master as "publisher" to one or more "subscribers".If any changes that has been made at subscribers are not pushed back to publisher.
Raw data is not replicated.
Instead of that,transactions from the publisher's transaction log which are sent to any other subscriber,these transactions to bring their copy of data uptodate.
Transactional with updating subscribers.
In this the variation of a transactional replication allows any subscribers to send their transcations to the publisher as such.
There can be absolutely no provision for any data conflicts.
This system works when an subscriber databases are only updating an subset of data.
As such each sunscriber can have their own database.such as related data of specific geographic region.
Merge.
This Forms is more complex of all replication that allows each participating copy of database that can be both publisher and subscriber,such as both fully read-write.
As special resolution modules are required to any data conflicts.
Data which changes in more than ne location at same time.
A default as merge resolver lets change with most recent times.
Normalization
Its a process of organazing the colums (attributes) and tables of database to reduce any redundancy and improve data integrity.A relational database is considered in "normalized" if it meets third normal form.
Stored procedure
A stored procedure is a set of SQL statements that has been created and stored in database.
Ex: create procedure dbo.sp_nameas
select * from mytable.name
GO
Functions
In SQl Server it creates a user-defined function on SQL Server and also Azure SQL Database.
Ex: Create function myfunction(
@FirstName nvarchar(50),
@LastName nvarchar(50)
)
returns varchar(100)
as
Begin return (Select @FirstName + ' ' + @LastName);
end
Sub Queries in SQL
A subquery is a query with in another query and embedded within a where clause.
Ex:Select column_name[,column_name] from table1 [,table2] where column_name operator (select column_name [,column_name] from table1[,table2] [where])