SQL Server: Common Table Expression (CTE), ROW_NUMBER(), RANK(), Derived Table, OPENXML()

  • Common Table Expression (CTE) is easy to use since you don’t have to create a table or even drop them after use and you can reference it multiple times and anywhere in the query.  You would use it like this in it’s simplest form:
    WITH Table2 AS
    (
        SELECT *
        FROM Table1
    )
    SELECT *
    FROM Table2

     

 

  • ROW_NUMBER() and RANK() is useful in numbering or ranking the records based on a criteria.  Both of them are used pretty much the same way and below is an example of using RANK() in it’s simplest form:
    SELECT *, RANK() OVER(ORDER BY Grade DESC) AS StudentRank
    FROM StudentGrades

     

 

  • Derived Table allows you to substitute a query in place of a table in the FROM or JOIN clause for example.  Below is one such use:
    SELECT *
    FROM
    (
        SELECT *
        FROM Table1
    ) AS Table2

     

 

  • OPENXML() is useful if you want to view your XML data in a series of rows and columns pretty much like a table.  For more information, click here
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s