What's the difference between SQL Replication and SQL Database Mirror?

A.)

Mirroring:-

The Mirror database is not accessible for read or write access.

Replication:-

The Subscriber Database (backup site) is open to reads and writes.

B.)

Mirroring:-

Information flow will be only one way (from Principal to Mirror Server)

Replication:-

Changes can be merged, bi-directional changes can be made, so the information can flow from Publisher to Subscriber and the other way around.

C.)

Mirroring:-

In case of failure of the Principal Database, the Mirror Database will take over the control and will act as Principal and applications can be redirected automatically to connect to this new Principal Server. Very little downtime. No code change required in the application.

Replication:-

In case of failure on Publisher, applications need to be re-directed to the Subscriber manually (in case you really want to do that), requires code change in the app or the connection string.

D.)

Mirroring:-

Almost everything inside the DB is replicated to the DR site, Schema changes can be replicated easily.

Replication:-

You have the option to replicate selected set of tables/SP/functions inside the DB, Schema changes can give some hiccups.

In Short, Mirroring is a good tool for DR (Disaster Recovery) with very little downtime, but the drawback is that the DR site will *not* be accessible to users, whereas Replication can be used to Merge Data between two Servers, can act as a good tool for Reporting purposes as the backup site is accessible to the users, can also act a DR solution.

It all depends on what you need, what are the business requirements , which will help you to choose the right topology in your environment. You can go through SQL Books Online for more details about Mirroring and Replication.

Difference between Row_Number, Rank, Dense_Rank in Sql Server

Row_Number

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

ROW_NUMBER ( ) OVER ([<partition_by_clause>] <order_by_clause>)

Rank 
Returns the rank of each row within the partition of a result set.<br/> The rank of a row is one plus the number of ranks that come before the row in question.

RANK ( )    OVER ([< partition_by_clause >] < order_by_clause >)

Dense_Rank

 Returns the rank of rows within the partition of a result set,<br/> without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

DENSE_RANK ( )    OVER ([<partition_by_clause> ] < order_by_clause > )

NTILE 

Distributes the rows in an ordered partition into a specified number of groups. <br/>The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

NTILE (integer_expression) OVER ([<partition_by_clause>] < order_by_clause >)

Where

<partition_by_clause>

Divides the result set produced by the From clause into partitions to which the Row_Number/ Rank/ Dense_Rank/ Ntile function is applied.

<order_by_clause>

Determines the order in which the Row_Number/ Rank/ Dense_Rank/ Ntile values are applied to the rows in a partition.

We will apply these function on the below customer product table CustProd.

name	Product
cust1	decoder
cust2	cable
cust1	cable
cust2	package
cust3	decoder
cust3	cable

Please see the below snapshot for understanding of these function through example

With partition by product and order by name,

When we use partition by product, then it divides the result on the basis of product, as there are three distinct products then there will be 3 partitions.

After partition, order by name is used, that means, in the partitions Row Number, Rank or Dense Rank will be assigned as per the order of name. Here in the below result we see that rank ,row number and dense rank, all are having same value, It’s because in each partition there are distinct name given, if name would have been repeated for the same product then those records will have same rank and dense rank, but row number would have been same as shown below.

When used order by product instead of name, then we see in the below result that, the Rank and dense Rank were 1, Because we did partition of result by product , that means there will be common product in each partition , and rank and dense rank will also be same for same product.