tempdb Database

The tempdb is a  system database which is a global resource, available to all users connected to the instance of SQL Server and is used to hold the following:

• Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
• Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for sorting.

tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database

Advantages

       One Of The important feature of tempdb is Temporary tables and table variables may be cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention. 

Disadvantages:-

      Some basic drawbacks in te,pdb include we cant drop the db,creation of database snapshot is not possible,cant change the database owner(Its owned by dbo.) ,Setting the database to OFFLINE

Using 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.

SQL Server 2005 has total of 4 ranking function.

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

2.RANK () OVER ()
Returns the rank of each row within the partition of a result set.

3.DENSE_RANK () OVER ()
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

4.NTILE (integer_expression) OVER ()
Distributes the rows in an ordered partition into a specified number of groups