What Ranking Functions are available in SQL Sever?

What is ROW_NUMBER function?

The ROW_NUMBER function assigns a number from 1 to n based on a user-specified sorting
order. ROW_NUMBER does not account for ties within the result set, so if you have
rows with the same values within the column(s) that you are ordering by, repeated
calls to the database for the same result set can produce different row numbering.

T-SQL Syntax for ROW_NUMBER

ROW_NUMBER() OVER (ORDERBY <Column_Name>)

ROW_NUMBER() OVER (PARTITION BY <Column_Name>)

Examples of ROW_NUMBER

The following examples returns the salesperson along with his or her year-to-date sales and
is numbered in descending order according to the year-to-date sales amount:

What is NTILE function?

NTILE is used to divide a result set into approximately equal groups. For example,
if you wanted to split a result set into six groups with approximately the same
number of rows in each group, you could use NTILE(6).

T-SQL Syntax for NTILE

NTILE(integer_expression) OVER (ORDERBY <Column_Name>)

NTILE(integer_expression) OVER (PARTITION BY <Column_Name>)

Examples of NTILE

The following examples show how NTILE can be used to segment a result set:

Introduction

GROUPING SET were introduced in SQL Server 2008, which allow multiple groupings
to be returned in one record set. In this article I explained the answers for the
questions which raises to everybody’s mind who hear this for the first time.

What is GROUPING SETS?

GROUPING SETS were added in SQL Server 2008 to give you greater flexibility when
defining SELECT statements that include aggregate functions. We can fetch multiple
groups set by executing just one SQL query. Although there are other alternates
to get multiple group sets with one SQL query for example by using ROLLUP and CUBE
clauses. But GROUPING SETS provide more flexibility than other alternates.

GROUPING SETS Equivalents

A GROUP BY clause that uses GROUPING SETS can generate a result set equvalent to
that generated by a UNION ALL of multiple simple GROUP BY clauses. GROUPING SETS
can generate a result equivalent to that generated by a simple GROUP BY, ROLLUP
or CUBE operation. Different combinations of GROUPING SETS, ROLLUP, or CUBE can
generate equivalent result sets.

Introduction

This article basically illustrates the concept of Data type synonyms which are included in SQL Server 2005 for SQL-92 compatibility.

What are Data Type Synonyms?

Data type synonyms can be used instead of the corresponding base data type name in data definition language (DDL) statements, such as CREATE TABLE, CREATE PROCEDURE, or DECLARE @variable. However, after the object is created, the synonyms have no visibility. When the object is created, the object is assigned the base data type that is associated with the synonym. There is no record that the synonym was specified in the statement that created the object.
All objects that are derived from the original object, such as result set columns or expressions, are assigned the base data type. All subsequent metadata functions performed on the original object and any derived objects will report the base data type, not the synonym. This behavior occurs with metadata operations, such as sp_help and other system stored procedures, the information schema views, or the various data access API metadata operations that report the data types of table or result set columns.

FirstName is actually assigned an varchar(20) data type, and all subsequent metadata functions will report the column as an varchar(20) column. The metadata functions will never report them as a character varying(20) column.
Similarly, LastName is an nvarchar(20) data type column and Makes is a float column.

Conclusion

This is an introduction to the Data Type Synonyms in SQL Server. The idea is to get familiar with another way of writing DDL queries using Data Type Synonyms.