SQL Server 2005's new added Ranking functions

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

Transact-SQL provides the following ranking functions:

RANK	NTILE
DENSE_RANK	ROW_NUMBER

Examples

The following shows the four ranking functions used in the same query. For function specific examples, see each ranking function.

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
   ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'
   ,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'
   ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'
   ,NTILE(4) OVER (ORDER BY a.PostalCode) AS 'Quartile'
   ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
   INNER JOIN Person.Person p
       ON s.BusinessEntityID = p.BusinessEntityID
   INNER JOIN Person.Address a
       ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
   AND SalesYTD <> 0;

Here is the result set.

FirstName	LastName	Row Number	Rank	Dense Rank	Quartile	SalesYTD	PostalCode
Michael	Blythe	1	1	1	1	4557045.0459	98027
Linda	Mitchell	2	1	1	1	5200475.2313	98027
Jillian	Carson	3	1	1	1	3857163.6332	98027
Garrett	Vargas	4	1	1	1	1764938.9859	98027
Tsvi	Reiter	5	1	1	2	2811012.7151	98027
Shu	Ito	6	6	2	2	3018725.4858	98055
José	Saraiva	7	6	2	2	3189356.2465	98055
David	Campbell	8	6	2	3	3587378.4257	98055
Tete	Mensa-Annan	9	6	2	3	1931620.1835	98055
Lynn	Tsoflias	10	6	2	3	1758385.926	98055
Rachel	Valdez	11	6	2	4	2241204.0424	98055
Jae	Pak	12	6	2	4	5015682.3752	98055
Ranjit	Varkey Chudukatil	13	6	2	4	3827950.238	98055

select Nth to Mth record from a table

select top 10 * from (select top 40 * from tb desc) asc select top 40 * from A where id not in(select top 30 id from A) which of above 2 is more efficient? why? in sql 2005+ ,you should use SELECT col1, col2 FROM ( SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM MyTable ) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

Relationships Between Classes

Relationships Between Classes

In a class diagram, obviously you can't have classes just floating around; you need to see the relationship between them. The following table shows the kinds of relationships between classes, their notation, and what they mean.

Sr. No.	Relation	Symbol	Description
1	Association		When two classes are connected to each other in any way, an association relation is established. For example: A "student studies in a college" association can be shown as:
1 a.	Multiplicity		An example of this kind of association is many students belonging to the same college. Hence, the relation shows a star sign near the student class (one to many, many to many, and so forth kind of relations).
1 b.	Directed Association		Association between classes is bi-directional by default. You can define the flow of the association by using a directed association. The arrowhead identifies the container-contained relationship.
1 c.	Reflexive Association	No separate symbol. However, the relation will point back at the same class.	An example of this kind of relation is when a class has a variety of responsibilities. For example, an employee of a college can be a professor, a housekeeper, or an administrative assistant.
2	Aggregation		When a class is formed as a collection of other classes, it is called an aggregation relationship between these classes. It is also called a "has a" relationship.
2 a.	Composition		Composition is a variation of the aggregation relationship. Composition connotes that a strong life cycle is associated between the classes.
3	Inheritance/Generalization		Also called an "is a" relationship, because the child class is a type of the parent class. Generalization is the basic type of relationship used to define reusable elements in the class diagram. Literally, the child classes "inherit" the common functionality defined in the parent class.
4	Realization		In a realization relationship, one entity (normally an interface) defines a set of functionalities as a contract and the other entity (normally a class) "realizes" the contract by implementing the functionality defined in the contract.