Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - A SQL developer got surprised when he learned that he does not have to use cursors to calculate rank

  • A SQL developer got surprised when he learned that he does not have to use cursors to calculate ranks in SQL Server 2008. He can now just use newly introduced ranking functions. What are the different RANKING functions inside SQL Server 2008 R2 and what are the new functions introduced inside SQL Server 2012?

    Posted on 01-08-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

11  Answers  

Subscribe to Notifications
Previous 1 | 2 Next
  • Score
    7

    SQL Server 2005 and above has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

    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.

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

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

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

    ========== Putting it all together, the following query shows all ranking functions in action using the famous AdventureWorks OLTP database!

    SELECT i.ProductID 
      , ProductName = p.Name 
      , i.LocationID 
      , i.Quantity 
      , RowNumber = ROW_NUMBER() 
                      OVER (PARTITION BY i.LocationID 
                            ORDER BY i.Quantity) 
      , Quartile = NTILE(4) 
                      OVER (PARTITION BY i.LocationID 
                            ORDER BY i.Quantity) 
      , Rank = RANK() 
                      OVER (PARTITION BY i.LocationID 
                            ORDER BY i.Quantity) 
      , DenseRank = DENSE_RANK() 
                      OVER (PARTITION BY i.LocationID 
                            ORDER BY i.Quantity) 
     FROM Production.ProductInventory i 
           INNER JOIN Production.Product p 
                 ON i.ProductID = p.ProductID 
     WHERE i.LocationID in (3, 4, 5) 
     ORDER BY i.LocationID, RowNumber;
    

    ----- here comes the query result ----- (ProductName is intentionally removed for better presentation)

    ProductID   LocID Quantity RowNumber  Quartile Rank DenseRank
    ----------- ----- -------- ---------- ----- ---------- ------
    492         3     17       1          1     1          1
    496         3     30       2          1     2          2
    493         3     41       3          2     3          3
    494         3     49       4          3     4          4
    495         3     49       5          4     4          4
    494         4     12       1          1     1          1
    492         4     14       2          1     2          2
    493         4     24       3          2     3          3
    496         4     25       4          3     4          4
    495         4     35       5          4     5          5
    317         5     158      1          1     1          1
    318         5     171      2          1     2          2
    351         5     179      3          1     3          3
    319         5     184      4          1     4          4
    952         5     192      5          1     5          5
    400         5     260      6          1     6          6
    815         5     265      7          1     7          7
    401         5     283      8          1     8          8
    352         5     300      9          1     9          9
    488         5     318      10         1     10         10
    477         5     323      11         1     11         11
    476         5     324      12         1     12         12
    949         5     336      13         1     13         13
    487         5     337      14         2     14         14
    950         5     342      15         2     15         15
    332         5     344      16         2     16         16
    945         5     347      17         2     17         17
    948         5     347      18         2     17         17
    951         5     348      19         2     19         18
    802         5     350      20         2     20         19
    803         5     356      21         2     21         20
    804         5     363      22         2     22         21
    399         5     366      23         2     23         22
    398         5     372      24         2     24         23
    320         5     372      25         2     24         23
    484         5     374      26         2     26         24
    481         5     374      27         3     26         24
    479         5     390      28         3     28         25
    816         5     406      29         3     29         26
    327         5     408      30         3     30         27
    819         5     409      31         3     31         28
    482         5     427      32         3     32         29
    485         5     427      33         3     32         29
    818         5     428      34         3     34         30
    821         5     432      35         3     35         31
    817         5     443      36         3     36         32
    820         5     446      37         3     37         33
    486         5     515      38         3     38         34
    480         5     515      39         3     38         34
    483         5     531      40         4     40         35
    316         5     532      41         4     41         36
    321         5     540      42         4     42         37
    330         5     548      43         4     43         38
    329         5     558      44         4     44         39
    328         5     568      45         4     45         40
    323         5     568      46         4     45         40
    324         5     568      47         4     45         40
    478         5     568      48         4     45         40
    331         5     574      49         4     49         41
    322         5     587      50         4     50         42
    350         5     622      51         4     51         43
    

    ==========

    SQL Server 2012 introduces new analytical functions PERCENT_RANK(). This function returns relative standing of a value within a query result set or partition.

    The formula to find PERCENT_RANK() is as following:

    PERCENT_RANK() = (RANK() – 1) / (Total Rows – 1)

    Replied on Jan 8 2012 12:44AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    7

    What is Ranking Functions

    Ranking functions are functions that allow you to sequentially number your result set.

    These functions can be used to provide you with a number of different sequential numbering schemes. For example you can number each row in your result set sequentially where the first row has a ranking number of 1, the second row has a ranking of 2, third row has 3, and so on. You can also use these ranking functions to sequentual number groups, so each group would have a numbering scheme of 1,2, 3, and then the next group would start over with 1, 2, 3, etc..

    RANKING functions inside SQL Server 2008 R2


    RANK

    Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

    Syntax: RANK ( ) OVER ( [ < partitionbyclause > ] < orderbyclause > )


    NTILE

    Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

    Syntax: NTILE (integerexpression) OVER ( [ ] < orderby_clause > )

    integerexpression can only reference columns in the PARTITION BY clause. integerexpression cannot reference columns listed in the current FROM clause.


    DENSE_RANK

    Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

    Syntax: DENSERANK ( ) OVER ( [ ] < orderby_clause > )


    ROW_NUMBER

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

    Syntax: ROW_NUMBER ( ) OVER ( [ ] )


    Divides the result set produced by the FROM clause into partitions to which the above functions is applied.

    Determines the order in which the function value is assigned to the rows in a partition.

    Example 1 :: 
    
    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;
    
            /*
        Example 2: 
    
        The following statements create the sample table, and insert  a few sample records for demonstration purposes.
            */
    
            CREATE TABLE SampleData
            (
                RecordId INT IDENTITY(1,1),
                TypeId INT,
                ActivityDate DATETIME,
                MessageText TEXT
            )
            GO
    
            INSERT INTO SampleData
                (TypeId, ActivityDate, MessageText)
            VALUES
                (1, '10/1/2008', 'Test Message (Max for Cat 1)')
    
            INSERT INTO SampleData
                (TypeId, ActivityDate, MessageText)
            VALUES
                (1, '9/28/2008', 'Test Message (Other for Cat 1)')
    
            INSERT INTO SampleData
                (TypeId, ActivityDate, MessageText)
            VALUES
                (2, '10/15/2008', 'Test Message (Max for Cat 2)')
    
            GO
    
            /*
                Use the CTE Expression to get the list of ranked
            entries from the system, partition by to group by type
            */
            WITH RankedData AS
            (
                SELECT
                	SD.RecordId,
                	SD.TypeId,
                	SD.ActivityDate,
                	SD.MessageText,
                    Rank() OVER 
                		(PARTITION BY SD.TypeId 
                			ORDER BY SD.ActivityDate DESC) AS ActivityRank
                FROM SampleData SD
            )
            --Now select results
            SELECT
                RankedData.RecordId,
                RankedData.TypeID,
                RankedData.ActivityDate,
                RankedData.MessageText
            FROM RankedData
            WHERE RankedData.ActivityRank = 1
            ORDER BY TypeId
    
            /*
    Similarly we can apply for all other ranking functions.
           */
    
            GO
    

    **

    Here is the list of new built-in Transact-SQL Functions in SQL Server 2012

    **

    Conversion functions

    * PARSE
    * TRY_CONVERT
    * TRY_PARSE
    

    Date and time functions

    * DATEFROMPARTS
    * DATETIME2FROMPARTS
    * DATETIMEFROMPARTS
    * DATETIMEOFFSETFROMPARTS
    * EOMONTH
    * SMALLDATETIMEFROMPARTS
    * TIMEFROMPARTS
    

    Logical functions

    * CHOOSE
    * IIF
    

    String functions

    * CONCAT
    * FORMAT
    

    Analytical Function

    * CUME_DIST 
    * FIRST_VALUE 
    * LAST_VALUE 
    * LEAD 
    * LAG 
    * PERCENTILE_CONT
    * PERCENTILE_DISC
    


    **

    2012 New SQL Functions Click

    Study Ref. Click 1::

    Examples Ref. Click 2::

    Best Examples Ref. Click 3 ::**

    Thanks

    Yogesh

    Replied on Jan 8 2012 6:27AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    4

    Rank,NTILE,DENSE_RANK and ROW Number are the ranking functions in SQL 2008 R2.

    Couple of new functions in SQL 2012 are-

    Conversion functions

    view sourceprint?1.* PARSE 2.* TRYCONVERT 3.* TRYPARSE Date and time functions

    1.DATEFROMPARTS 2.DATETIME2FROMPARTS 3.DATETIMEFROMPARTS 4.DATETIMEOFFSETFROMPARTS 5.EOMONTH 6.SMALLDATETIMEFROMPARTS 7.TIMEFROMPARTS

    Logical functions

    1.CHOOSE 2.IIF

    String functions

    1.* CONCAT 2.* FORMAT

    Replied on Jan 8 2012 9:12PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    5

    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.

    In SQL Server 2008 R2 Transact-SQL provides the following ranking functions:

    1. RANK
    2. DENSE_RANK
    3. NTILE
    4. ROW_NUMBER

    http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-rownumber-rank-denserank-ntile/ http://msdn.microsoft.com/en-us/library/ms189798(v=SQL.105).aspx

    Microsoft SQL Server 2012 introduces 14 new built-in functions. These new functions are:

    Conversion functions

    • PARSE
    • TRY_CONVERT
    • TRY_PARSE

    Date and time functions

    • DATEFROMPARTS
    • DATETIME2FROMPARTS
    • DATETIMEFROMPARTS
    • DATETIMEOFFSETFROMPARTS
    • EOMONTH
    • SMALLDATETIMEFROMPARTS
    • TIMEFROMPARTS

    Logical functions

    • CHOOSE
    • IIF

    String functions

    • CONCAT
    • FORMAT

    http://blog.sqlauthority.com/2011/09/06/sql-server-denali-conversion-function-parse-a-quick-introduction/ http://blog.sqlauthority.com/2011/09/19/sql-server-denali-date-and-time-functions-datefromparts-datetimefromparts-datetime2fromparts-timefromparts-smalldatetimefromparts-datetimeoffsetfromparts-a-quick-introduc/ http://blog.sqlauthority.com/2010/11/25/sql-server-concat-function-in-sql-server-sql-concatenation/ http://blog.sqlauthority.com/2011/09/11/sql-server-denali-logical-function-choose-a-quick-introduction/

    Replied on Jan 8 2012 10:42PM  . 
    Hardik Doshi
    20 · 9% · 2864
  • Score
    4

    There are 4 ranking functions in SQL server 2008

    1. ROW_NUMBER()
    2. RANK()
    3. DENSE_RANK()
    4. NTILE()

    There around 21 new ranking functions in SQL 2012 besides old 4 ranking functions in SQL 2008

    1. PARSE()
    2. TRY_CONVERT()
    3. TRY_PARSE()
    4. CONCAT()
    5. FORMAT()
    6. IIF()
    7. CHOOSE()
    8. EOMONTH()
    9. TIMEFROMPARTS()
    10. SMALLDATETIMEFROMPARTS()
    11. DATEFROMPARTS()
    12. DATETIMEFROMPARTS()
    13. DATETIME2FROMPARTS()
    14. DATETIMEOFFSETFROMPARTS()
    15. CUME_DIST()
    16. FIRST_VALUE()
    17. LAST_VALUE()
    18. LAG()
    19. LEAD()
    20. PERCENTILE_DISC()
    21. PERCENTILE_CONT()
    Replied on Jan 9 2012 12:02AM  . 
    mahmad
    2377 · 0% · 5
  • Score
    6

    SQL Server 2005 and above has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

    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.

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

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

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

    SQL Server 2012 introduces new analytical functions PERCENT_RANK(). This function returns relative standing of a value within a query result set or partition.

    The formula to find PERCENT_RANK() is as following:

    PERCENT_RANK() = (RANK() – 1) / (Total Rows – 1)

    Other Functions in SQL Server 2012

    Conversion functions

    1.* PARSE

    2.* TRY_CONVERT

    3.* TRY_PARSE

    Date and time functions

    1.* DATEFROMPARTS

    2.* DATETIME2FROMPARTS

    3.* DATETIMEFROMPARTS

    4.* DATETIMEOFFSETFROMPARTS

    5.* EOMONTH

    6.* SMALLDATETIMEFROMPARTS

    7.* TIMEFROMPARTS

    Logical functions

    1.* CHOOSE

    2.* IIF

    String functions

    1.* CONCAT

    2.* FORMAT

    Analytical Function

    1.* CUMEDIST
    2.* FIRST
    VALUE
    3.* LASTVALUE
    4.* LEAD
    5.* LAG
    6.* PERCENTILE
    CONT 7.* PERCENTILE_DISC

    Replied on Jan 9 2012 12:12AM  . 
    SQLShan
    690 · 0% · 48
  • Score
    3

    Rank()

    1. DENSE_RANK()

    2.Row_Number() NTILE()

    New functions in sql server 2012 TRYPARSE() : select tryparse('2011-02-30' as date) OUTPUT :NULL PARSE() : SELECT PARSE('13-12-2011' AS datetime USING 'en-US') AS Result IT THROWS ERROR because it means the 13th month

    TRY_CONVERT() : This is pretty much the same as the well known CONVERT, but again with the NULL output for invalid input strings. SELECT CONVERT(XML, 'Hello World!) AS MyXML FROM ABOVE QUERY IT RETURNS "NULL".

    CONCAT() FORMAT() IIF() CHOOSE()

    DATEFROMPARTS () DATETIME2FROMPARTS () TIMEFROMPARTS () EOMONTH()

    Replied on Jan 9 2012 5:15AM  . 
    sivatataiahmaganti
    1208 · 0% · 21
  • Score
    6

    SQL Server 2008 R2 has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

    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.

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

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

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

    USE AdventureWorks;
    GO
    SELECT c.FirstName, c.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.Contact c
    ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
    ON a.AddressID = c.ContactID
    WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
    

    http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-rownumber-rank-dense_rank-ntile/

    New functions introduced inside SQL Server 2012

    Conversion functions

    PARSE  
    TRY_CONVERT  
    TRY_PARSE
    

    Date and time functions

    DATEFROMPARTS  
    DATETIME2FROMPARTS  
    DATETIMEFROMPARTS  
    DATETIMEOFFSETFROMPARTS  
    EOMONTH  
    SMALLDATETIMEFROMPARTS  
    TIMEFROMPARTS
    

    Logical functions

    CHOOSE  
    IIF
    

    String functions

    CONCAT  
    FORMAT
    

    http://blog.sqlauthority.com/2011/09/21/sql-server-denali-14-new-functions-a-quick-guide/

    Analytical functions

    CUME_DIST  
    FIRST_VALUE  
    LAST_VALUE  
    LEAD  
    LAG  
    PERCENTILE_CONT  
    PERCENTILE_DISC  
    PERCENT_RANK
    

    http://www.codeproject.com/Articles/290251/SQL-SERVER-2012-Summary-of-All-the-Analytic-Functi

    Replied on Jan 9 2012 6:57AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    4

    Following are the Ranking functions in SQL Server 2008 R2;

    RANK

    Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question

    If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

    The sort order that is used for the whole query determines the order in which the rows appear in a result set.

    DENSE_RANK

    Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

    If two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.

    The sort order used for the whole query determines the order in which the rows appear in a result. This implies that a row ranked number one does not have to be the first row in the partition.

    NTILE

    Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

    If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.

    ROWNUMBER

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

    The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

    Replied on Jan 11 2012 6:57AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    6

    Ranking Functions in SQL Server R2 Are:

    • ROWNUMBER()OVER([partitionbyclause] [orderbyclause]) : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. ROWNUMBER 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.
    • RANK()OVER([partitionbyclause] [orderbyclause]): Returns the rank of each row within the partition of a result set. If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.
    • DENSERANK()OVER([partitionbyclause] [orderbyclause]) :Returns the rank of rows within the partition of a result set, without any gaps in the ranking. f two or more rows tie for a rank in the same partition, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSERANK function do not have gaps and always have consecutive ranks.
    • NTILE(integerexpression)OVER([partitionbyclause] [orderbyclause]): Distributes the rows in an ordered partition into a specified number of groups. If the number of rows in a partition is not divisible by integerexpression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.

    Arguments

    < partitionbyclause > : Divides the result set produced by the FROM clause into partitions to which the function is applied.

    < orderbyclause >: Determines the order in which the RANK values are applied to the rows in a partition.

    New Functions In SQL 2012

    1.Conversion and Parsing:

    New functions in this category: TRYCONVERT, PARSE and TRYPARSE.

    TRY_CONVERT: Its like the existing CONVERT function, only when the input value isn’t convertible, instead of generating an error, the function returns a NULL.

    PARSE: The PARSE function in essence does a conversion of an input string to the target type, but unlike CAST and CONVERT, it supports an optional USING clause indicating the culture.

    TRYPARSE: TRYPARSE does the same as PARSE, only when the input isn’t converted to the target type, instead of generating an error, the function returns a NULL.

    2.Date and Time

    New functions in this category: EOMONTH, DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS and TIMEFROMPARTS.

    EOMONTH:The EOMONTH function returns the end of month date corresponding to the input date and time value, with the time set to midnight, retaining the time zone if it exists.

    %FROMPARTS : For each date and time data type, SQL Server Denali provides a FROMPARTS function that allows constructing a value of this type from integer parts. This is useful in general, but also important for migrations from environments like Excel, Access and others that support such functionality.

    3.Logical

    Functions in this category: CHOOSE and IIF.

    CHOOSE:The CHOOSE function accepts an integer input followed by a list of values of any data type, and returns as output the value from the list in the position indicated by the first input. IIF:The IIF function is another function Access supports. It accepts as first input a predicate, as second input an expression to return in case the predicate is true, and as third input an expression to return in case the input is false or unknown.

    4.String Manipulation

    New functions in this category: CONCAT and FORMAT.

    CONCAT:The CONCAT function concatenates the input values into a single result string. The CONCAT function converts NULL inputs to empty strings before concatenation.

    FORMAT:The FORMAT function allows you to format an input value to a character string

    5.Math

    Enhanced function in this category: LOG.

    LOG: SQL 2012 enhances the LOG function by supporting a second argument representing the base.

    New Ranking Functions in SQL Server 2012

    SQL Server Denali improves support for window aggregate functions by adding a window order clause and a window frame clause. This means that you can now restrict a subset of rows within the window partition. SQL Server supports a ROWS option that allows you to frame rows based on an offset from the current row in terms of number of rows. SQL Server also adds limited support for the RANGE option, which allows framing rows based on an offset from the current row in terms of values.

    ROWS option, the following query issued against the sample database TSQL2012, computes the running total quantity for each employee and order month:

    USE TSQL2012; 
    SELECT empid, ordermonth, qty,
      SUM(qty) OVER(PARTITION BY empid
                    ORDER BY ordermonth
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runqty
    FROM Sales.EmpOrders;
    

    SQL Server Denali adds support for window offset functions LAG and LEAD which allow returning elements from a preceding or a following row. It also supports the FIRSTVALUE and LASTVALUE functions which allow returning elements from the first or last row in a window frame. As an example, the following query returns for each customer order the value of the current, previous and next orders:

    SELECT custid, orderdate, orderid, val,
      LAG(val)  OVER(PARTITION BY custid
                     ORDER BY orderdate, orderid) AS prevval,
      LEAD(val) OVER(PARTITION BY custid
                     ORDER BY orderdate, orderid) AS nextval
    FROM Sales.OrderValues;
    

    SQL Server Denali also introduces support for window distribution functions. It adds window rank distribution functions PERCENTRANK (for percentile rank) and CUMEDIST (for cumulative distribution). It also adds window inverse distribution functions PERCENTILEDISC (percentile using discrete distribution model) and PERCENTILECONT (percentile using continuous distribution model). The last two are implemented as window functions and not as grouped ordered set functions.

    As an example, the following query computes the percentile rank and cumulative distribution of student scores per test:

    SELECT testid, studentid, score,
      PERCENT_RANK() OVER(PARTITION BY testid ORDER BY score) AS percentrank,
      CUME_DIST()    OVER(PARTITION BY testid ORDER BY score) AS cumedist
    FROM Stats.Scores;
    

    Reference

    http://www.sumitgarg.net/post/2010/04/23/Ranking-Functions-in-SQL-SERVER-2008.aspx

    http://msdn.microsoft.com/en-us/library/ms189798.aspx

    http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-rownumber-rank-dense_rank-ntile/

    http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/improved-support-window-functions-sql-server-denali-ctp3-140423

    http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-tsql-glance-enhanced-functions-140785

    Replied on Jan 12 2012 6:26AM  . 
    Sineetha
    106 · 2% · 492
Previous 1 | 2 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.