RANKING Functions

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.

 EXAMPLES:

---------------

Suppose you are having table like below

Example :1

select ROW_NUMBER() over(order by salary)ROW_NUMBER,RANK()over(order by salary)

RANK,DENSE_RANK() over(order bysalary)DENSE_RANK,* from emp

Example :2

select ROW_NUMBER()over (order by empname)ROW_NUMBER,RANK()over (order byempname)RANK,DENSE_RANK()over (order by empname)DENSE_RANK,* from emp

Example :3

select ROW_NUMBER()over(partition by empname order by salary)ROW_NUMBER,RANK()

over(partitionby empname order by salary)RANK,DENSE_RANK()over(partition by

 empname order bysalary)DENSE_RANK,* from emp

Example :4

select ROW_NUMBER()over(partition by salary order by empname)ROW_NUMBER,RANK()

over(partitionby salary order by empname)RANK,DENSE_RANK()over(partition by 

salary order byempname)DENSE_RANK,* from emp

Example :5

select ROW_NUMBER()over(partition by empname order by empname)ROW_NUMBER,RANK()

over(partitionby empname order by empname)RANK,DENSE_RANK()

over(partition by empname order byempname)DENSE_RANK,* from emp

Example :6

select ROW_NUMBER()over(partition by salary order by salary)

ROW_NUMBER,RANK()over(partitionby salary order by salary)

RANK,DENSE_RANK()over(partition by salary order bysalary)

DENSE_RANK,* from emp

SSIS Package Deployment

Step 1:
          Right click on the solution in solution explorer and click properties.In the left pane of Property window click Deployment Utility and in right pane set CreateDeploymentUtility property to True and click OK.

Step 2:

Right click on Solution in solution explorer and click Build.

Step 3:

Go to the Package path and then go to \bin\deployment folder.Double click the Integration Services Deployment Manifest file.

Step 4:
There are two types of Deployment File system deployment and Sql Server Deployment.As Sql server deployment is safe we can go with this.click next.Now specify the target machine server name and credentials and click browse and select the path.click next.

Step 5:

Now select the path in which the dependencies of package will install and then click next.In this window check the summary and click finish.Now you are done with your deployment.You can check by log in in target server Sql server Management studio.connect to integration services and under stored packages.You can find your deployed package.

Configuration of Gmail Smtp in Database mail in Sql Server 2008

Configuration of Gmail Smtp in Database mail in Sql Server 2008

Step 1:
           Connect to Sql server 2008.Expand Management in Object Explorer and right click on Database Mail and click Configure Database Mail 

Step 2:
select first option and click next.
       

Step 3:

Give any ProfileName for ex: "AlerNotify" and Click Add and then click New Account.



Step 4:



EmailAddress: vagalla.suresh@gmail.com
Display name: VAGALLA
Reply e-mail:vagalla.suresh@gmail.com
Server name: smpt.gmail.com
Port number:  587

Basic authentication

Username: vagalla.suresh@gmail.com
password: my gmail password
confirm password: my gmail password

Step 5:
Make that as default profile. and click Next and then Next and click Finish.


Step 6;
 close

Step 7:
  we can test  by right click on Database mail  and click send mail.