Explain an OVER clause












3















I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the ascii value on each character.



If I am reading the query correctly, effectively, 3 CTEs are being used to prepare a table of 1 column containing 10,000 rows, each with the value '0'.



A fourth CTE is defined as follows:



cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)


Subsequently, this CTE is joined to a table containing a column with the strings of interest, with the following select:



SELECT n, SUBSTRING(LastName, n, 1), ASCII( SUBSTRING(LastName, n, 1))


That is, row number n, then the nth character in LastName, then the ascii value of that character.



My questions relate to the over clause in the CTE above.



Essentially, what exactly is it doing?



If we are querying row_number from 10,000 identical rows, why do we need an order by clause at all? Why is the order by put into an over clause rather than as an order by clause for the select statement - especially as the over clause isn't even specifying any partition? (I presume this means the window over which row_number operates is the full 10,000 rows?) And what does it mean to order by select null?










share|improve this question



























    3















    I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the ascii value on each character.



    If I am reading the query correctly, effectively, 3 CTEs are being used to prepare a table of 1 column containing 10,000 rows, each with the value '0'.



    A fourth CTE is defined as follows:



    cteTally(n) AS(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
    FROM E4
    )


    Subsequently, this CTE is joined to a table containing a column with the strings of interest, with the following select:



    SELECT n, SUBSTRING(LastName, n, 1), ASCII( SUBSTRING(LastName, n, 1))


    That is, row number n, then the nth character in LastName, then the ascii value of that character.



    My questions relate to the over clause in the CTE above.



    Essentially, what exactly is it doing?



    If we are querying row_number from 10,000 identical rows, why do we need an order by clause at all? Why is the order by put into an over clause rather than as an order by clause for the select statement - especially as the over clause isn't even specifying any partition? (I presume this means the window over which row_number operates is the full 10,000 rows?) And what does it mean to order by select null?










    share|improve this question

























      3












      3








      3








      I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the ascii value on each character.



      If I am reading the query correctly, effectively, 3 CTEs are being used to prepare a table of 1 column containing 10,000 rows, each with the value '0'.



      A fourth CTE is defined as follows:



      cteTally(n) AS(
      SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
      FROM E4
      )


      Subsequently, this CTE is joined to a table containing a column with the strings of interest, with the following select:



      SELECT n, SUBSTRING(LastName, n, 1), ASCII( SUBSTRING(LastName, n, 1))


      That is, row number n, then the nth character in LastName, then the ascii value of that character.



      My questions relate to the over clause in the CTE above.



      Essentially, what exactly is it doing?



      If we are querying row_number from 10,000 identical rows, why do we need an order by clause at all? Why is the order by put into an over clause rather than as an order by clause for the select statement - especially as the over clause isn't even specifying any partition? (I presume this means the window over which row_number operates is the full 10,000 rows?) And what does it mean to order by select null?










      share|improve this question














      I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the ascii value on each character.



      If I am reading the query correctly, effectively, 3 CTEs are being used to prepare a table of 1 column containing 10,000 rows, each with the value '0'.



      A fourth CTE is defined as follows:



      cteTally(n) AS(
      SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
      FROM E4
      )


      Subsequently, this CTE is joined to a table containing a column with the strings of interest, with the following select:



      SELECT n, SUBSTRING(LastName, n, 1), ASCII( SUBSTRING(LastName, n, 1))


      That is, row number n, then the nth character in LastName, then the ascii value of that character.



      My questions relate to the over clause in the CTE above.



      Essentially, what exactly is it doing?



      If we are querying row_number from 10,000 identical rows, why do we need an order by clause at all? Why is the order by put into an over clause rather than as an order by clause for the select statement - especially as the over clause isn't even specifying any partition? (I presume this means the window over which row_number operates is the full 10,000 rows?) And what does it mean to order by select null?







      sql-server t-sql window-functions






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked 4 hours ago









      youcantryreachingmeyoucantryreachingme

      2987




      2987






















          2 Answers
          2






          active

          oldest

          votes


















          2














          ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause.
          If you try to write it without the ORDER BY you will get a syntax error.



          SELECT ROW_NUMBER() OVER()
          FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
          -- Msg 4112, Level 15, State 1, Line 1
          -- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.


          The trick with the subquery was discovered by someone who blogged about it, as a performance optimization.
          SQL Server always performs a sort operation since constants are not allowed for the ORDER BY clause:



          SELECT ROW_NUMBER() OVER(ORDER BY NULL)
          FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
          -- Msg 5309, Level 16, State 1, Line 1
          -- Windowed functions, aggregates and NEXT VALUE FOR functions
          -- do not support constants as ORDER BY clause expressions.


          And neither are integers which are treated as indices:



          SELECT ROW_NUMBER() OVER(ORDER BY 1)
          FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
          -- Msg 5308, Level 16, State 1, Line 1
          -- Windowed functions, aggregates and NEXT VALUE FOR functions
          -- do not support integer indices as ORDER BY clause expressions.


          Turns out that due to some glitch in the code, you can circumvent this limitation by using a subquery, which for some reason is allowed, and eliminates the sort operator.



          SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
          FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);


          No Sort Operator



          You can use any constant in the sub query, the NULL is probably a reminiscent of the habit of using SELECT NULL within EXISTS predicates, which in the early history of SQL Server had a performance impact as opposed to * or any other column expression, as the optimizer wasn't smart enough to ignore it.



          HTH






          share|improve this answer
























          • Thank you. Sure does. I can see most of my questions fall over like a house of cards on "windows functions require a mandatory order by clause". The MS docs on the over clause say order by is optional - but I guess this means partitions might be used without window functions (and not require an order by). Nice username!

            – youcantryreachingme
            2 hours ago






          • 1





            Not all window functions require an ORDER BY. Only ranking window functions - ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(). Providing a PARTITION BY or not is irrelevant.

            – ypercubeᵀᴹ
            2 hours ago













          • You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);

            – Erik Darling
            35 mins ago



















          2














          The OVER clause sets the ordering (and partitioning if PARTITION BY is included) of the row set before applying the selected Windowing function. As you can use multiple Window functions in a single query, each requires its own partitioning and ordering to ensure the data is returned as desired.



          In your example, ROW_NUMBER() is being used to generate a sequential row number for each row in the CTE. SELECT NULL is used because there is no particular order required but an ORDER BY clause is required for a windowing function.



          Another way of achieving the same thing would be using an IDENTITY column, however, that has other implications and requires changes to an existing table, or a temp table to be created. The ROW_NUMBER window function in a CTE allows for this identity to be generated on the fly.



          To answer your specific question:




          Essentially, what exactly is it doing?




          It is ordering the rows in E4 'randomly' before applying the ROW_NUMBER() window function to that result set to produce a list of row numbers equal to the number of rows in E4. OVER can be translated as "Fetch me a result set with this ordering and partitioning and apply (OVER) this window function to that result set independent of the ordering in the main SELECT statement."



          More info: OVER Clause






          share|improve this answer























            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "182"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230328%2fexplain-an-over-clause%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            2














            ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause.
            If you try to write it without the ORDER BY you will get a syntax error.



            SELECT ROW_NUMBER() OVER()
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 4112, Level 15, State 1, Line 1
            -- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.


            The trick with the subquery was discovered by someone who blogged about it, as a performance optimization.
            SQL Server always performs a sort operation since constants are not allowed for the ORDER BY clause:



            SELECT ROW_NUMBER() OVER(ORDER BY NULL)
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 5309, Level 16, State 1, Line 1
            -- Windowed functions, aggregates and NEXT VALUE FOR functions
            -- do not support constants as ORDER BY clause expressions.


            And neither are integers which are treated as indices:



            SELECT ROW_NUMBER() OVER(ORDER BY 1)
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 5308, Level 16, State 1, Line 1
            -- Windowed functions, aggregates and NEXT VALUE FOR functions
            -- do not support integer indices as ORDER BY clause expressions.


            Turns out that due to some glitch in the code, you can circumvent this limitation by using a subquery, which for some reason is allowed, and eliminates the sort operator.



            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);


            No Sort Operator



            You can use any constant in the sub query, the NULL is probably a reminiscent of the habit of using SELECT NULL within EXISTS predicates, which in the early history of SQL Server had a performance impact as opposed to * or any other column expression, as the optimizer wasn't smart enough to ignore it.



            HTH






            share|improve this answer
























            • Thank you. Sure does. I can see most of my questions fall over like a house of cards on "windows functions require a mandatory order by clause". The MS docs on the over clause say order by is optional - but I guess this means partitions might be used without window functions (and not require an order by). Nice username!

              – youcantryreachingme
              2 hours ago






            • 1





              Not all window functions require an ORDER BY. Only ranking window functions - ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(). Providing a PARTITION BY or not is irrelevant.

              – ypercubeᵀᴹ
              2 hours ago













            • You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);

              – Erik Darling
              35 mins ago
















            2














            ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause.
            If you try to write it without the ORDER BY you will get a syntax error.



            SELECT ROW_NUMBER() OVER()
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 4112, Level 15, State 1, Line 1
            -- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.


            The trick with the subquery was discovered by someone who blogged about it, as a performance optimization.
            SQL Server always performs a sort operation since constants are not allowed for the ORDER BY clause:



            SELECT ROW_NUMBER() OVER(ORDER BY NULL)
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 5309, Level 16, State 1, Line 1
            -- Windowed functions, aggregates and NEXT VALUE FOR functions
            -- do not support constants as ORDER BY clause expressions.


            And neither are integers which are treated as indices:



            SELECT ROW_NUMBER() OVER(ORDER BY 1)
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 5308, Level 16, State 1, Line 1
            -- Windowed functions, aggregates and NEXT VALUE FOR functions
            -- do not support integer indices as ORDER BY clause expressions.


            Turns out that due to some glitch in the code, you can circumvent this limitation by using a subquery, which for some reason is allowed, and eliminates the sort operator.



            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);


            No Sort Operator



            You can use any constant in the sub query, the NULL is probably a reminiscent of the habit of using SELECT NULL within EXISTS predicates, which in the early history of SQL Server had a performance impact as opposed to * or any other column expression, as the optimizer wasn't smart enough to ignore it.



            HTH






            share|improve this answer
























            • Thank you. Sure does. I can see most of my questions fall over like a house of cards on "windows functions require a mandatory order by clause". The MS docs on the over clause say order by is optional - but I guess this means partitions might be used without window functions (and not require an order by). Nice username!

              – youcantryreachingme
              2 hours ago






            • 1





              Not all window functions require an ORDER BY. Only ranking window functions - ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(). Providing a PARTITION BY or not is irrelevant.

              – ypercubeᵀᴹ
              2 hours ago













            • You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);

              – Erik Darling
              35 mins ago














            2












            2








            2







            ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause.
            If you try to write it without the ORDER BY you will get a syntax error.



            SELECT ROW_NUMBER() OVER()
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 4112, Level 15, State 1, Line 1
            -- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.


            The trick with the subquery was discovered by someone who blogged about it, as a performance optimization.
            SQL Server always performs a sort operation since constants are not allowed for the ORDER BY clause:



            SELECT ROW_NUMBER() OVER(ORDER BY NULL)
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 5309, Level 16, State 1, Line 1
            -- Windowed functions, aggregates and NEXT VALUE FOR functions
            -- do not support constants as ORDER BY clause expressions.


            And neither are integers which are treated as indices:



            SELECT ROW_NUMBER() OVER(ORDER BY 1)
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 5308, Level 16, State 1, Line 1
            -- Windowed functions, aggregates and NEXT VALUE FOR functions
            -- do not support integer indices as ORDER BY clause expressions.


            Turns out that due to some glitch in the code, you can circumvent this limitation by using a subquery, which for some reason is allowed, and eliminates the sort operator.



            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);


            No Sort Operator



            You can use any constant in the sub query, the NULL is probably a reminiscent of the habit of using SELECT NULL within EXISTS predicates, which in the early history of SQL Server had a performance impact as opposed to * or any other column expression, as the optimizer wasn't smart enough to ignore it.



            HTH






            share|improve this answer













            ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause.
            If you try to write it without the ORDER BY you will get a syntax error.



            SELECT ROW_NUMBER() OVER()
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 4112, Level 15, State 1, Line 1
            -- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.


            The trick with the subquery was discovered by someone who blogged about it, as a performance optimization.
            SQL Server always performs a sort operation since constants are not allowed for the ORDER BY clause:



            SELECT ROW_NUMBER() OVER(ORDER BY NULL)
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 5309, Level 16, State 1, Line 1
            -- Windowed functions, aggregates and NEXT VALUE FOR functions
            -- do not support constants as ORDER BY clause expressions.


            And neither are integers which are treated as indices:



            SELECT ROW_NUMBER() OVER(ORDER BY 1)
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
            -- Msg 5308, Level 16, State 1, Line 1
            -- Windowed functions, aggregates and NEXT VALUE FOR functions
            -- do not support integer indices as ORDER BY clause expressions.


            Turns out that due to some glitch in the code, you can circumvent this limitation by using a subquery, which for some reason is allowed, and eliminates the sort operator.



            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
            FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);


            No Sort Operator



            You can use any constant in the sub query, the NULL is probably a reminiscent of the habit of using SELECT NULL within EXISTS predicates, which in the early history of SQL Server had a performance impact as opposed to * or any other column expression, as the optimizer wasn't smart enough to ignore it.



            HTH







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered 2 hours ago









            SQLRaptorSQLRaptor

            2,3361319




            2,3361319













            • Thank you. Sure does. I can see most of my questions fall over like a house of cards on "windows functions require a mandatory order by clause". The MS docs on the over clause say order by is optional - but I guess this means partitions might be used without window functions (and not require an order by). Nice username!

              – youcantryreachingme
              2 hours ago






            • 1





              Not all window functions require an ORDER BY. Only ranking window functions - ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(). Providing a PARTITION BY or not is irrelevant.

              – ypercubeᵀᴹ
              2 hours ago













            • You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);

              – Erik Darling
              35 mins ago



















            • Thank you. Sure does. I can see most of my questions fall over like a house of cards on "windows functions require a mandatory order by clause". The MS docs on the over clause say order by is optional - but I guess this means partitions might be used without window functions (and not require an order by). Nice username!

              – youcantryreachingme
              2 hours ago






            • 1





              Not all window functions require an ORDER BY. Only ranking window functions - ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(). Providing a PARTITION BY or not is irrelevant.

              – ypercubeᵀᴹ
              2 hours ago













            • You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);

              – Erik Darling
              35 mins ago

















            Thank you. Sure does. I can see most of my questions fall over like a house of cards on "windows functions require a mandatory order by clause". The MS docs on the over clause say order by is optional - but I guess this means partitions might be used without window functions (and not require an order by). Nice username!

            – youcantryreachingme
            2 hours ago





            Thank you. Sure does. I can see most of my questions fall over like a house of cards on "windows functions require a mandatory order by clause". The MS docs on the over clause say order by is optional - but I guess this means partitions might be used without window functions (and not require an order by). Nice username!

            – youcantryreachingme
            2 hours ago




            1




            1





            Not all window functions require an ORDER BY. Only ranking window functions - ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(). Providing a PARTITION BY or not is irrelevant.

            – ypercubeᵀᴹ
            2 hours ago







            Not all window functions require an ORDER BY. Only ranking window functions - ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(). Providing a PARTITION BY or not is irrelevant.

            – ypercubeᵀᴹ
            2 hours ago















            You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);

            – Erik Darling
            35 mins ago





            You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);

            – Erik Darling
            35 mins ago













            2














            The OVER clause sets the ordering (and partitioning if PARTITION BY is included) of the row set before applying the selected Windowing function. As you can use multiple Window functions in a single query, each requires its own partitioning and ordering to ensure the data is returned as desired.



            In your example, ROW_NUMBER() is being used to generate a sequential row number for each row in the CTE. SELECT NULL is used because there is no particular order required but an ORDER BY clause is required for a windowing function.



            Another way of achieving the same thing would be using an IDENTITY column, however, that has other implications and requires changes to an existing table, or a temp table to be created. The ROW_NUMBER window function in a CTE allows for this identity to be generated on the fly.



            To answer your specific question:




            Essentially, what exactly is it doing?




            It is ordering the rows in E4 'randomly' before applying the ROW_NUMBER() window function to that result set to produce a list of row numbers equal to the number of rows in E4. OVER can be translated as "Fetch me a result set with this ordering and partitioning and apply (OVER) this window function to that result set independent of the ordering in the main SELECT statement."



            More info: OVER Clause






            share|improve this answer




























              2














              The OVER clause sets the ordering (and partitioning if PARTITION BY is included) of the row set before applying the selected Windowing function. As you can use multiple Window functions in a single query, each requires its own partitioning and ordering to ensure the data is returned as desired.



              In your example, ROW_NUMBER() is being used to generate a sequential row number for each row in the CTE. SELECT NULL is used because there is no particular order required but an ORDER BY clause is required for a windowing function.



              Another way of achieving the same thing would be using an IDENTITY column, however, that has other implications and requires changes to an existing table, or a temp table to be created. The ROW_NUMBER window function in a CTE allows for this identity to be generated on the fly.



              To answer your specific question:




              Essentially, what exactly is it doing?




              It is ordering the rows in E4 'randomly' before applying the ROW_NUMBER() window function to that result set to produce a list of row numbers equal to the number of rows in E4. OVER can be translated as "Fetch me a result set with this ordering and partitioning and apply (OVER) this window function to that result set independent of the ordering in the main SELECT statement."



              More info: OVER Clause






              share|improve this answer


























                2












                2








                2







                The OVER clause sets the ordering (and partitioning if PARTITION BY is included) of the row set before applying the selected Windowing function. As you can use multiple Window functions in a single query, each requires its own partitioning and ordering to ensure the data is returned as desired.



                In your example, ROW_NUMBER() is being used to generate a sequential row number for each row in the CTE. SELECT NULL is used because there is no particular order required but an ORDER BY clause is required for a windowing function.



                Another way of achieving the same thing would be using an IDENTITY column, however, that has other implications and requires changes to an existing table, or a temp table to be created. The ROW_NUMBER window function in a CTE allows for this identity to be generated on the fly.



                To answer your specific question:




                Essentially, what exactly is it doing?




                It is ordering the rows in E4 'randomly' before applying the ROW_NUMBER() window function to that result set to produce a list of row numbers equal to the number of rows in E4. OVER can be translated as "Fetch me a result set with this ordering and partitioning and apply (OVER) this window function to that result set independent of the ordering in the main SELECT statement."



                More info: OVER Clause






                share|improve this answer













                The OVER clause sets the ordering (and partitioning if PARTITION BY is included) of the row set before applying the selected Windowing function. As you can use multiple Window functions in a single query, each requires its own partitioning and ordering to ensure the data is returned as desired.



                In your example, ROW_NUMBER() is being used to generate a sequential row number for each row in the CTE. SELECT NULL is used because there is no particular order required but an ORDER BY clause is required for a windowing function.



                Another way of achieving the same thing would be using an IDENTITY column, however, that has other implications and requires changes to an existing table, or a temp table to be created. The ROW_NUMBER window function in a CTE allows for this identity to be generated on the fly.



                To answer your specific question:




                Essentially, what exactly is it doing?




                It is ordering the rows in E4 'randomly' before applying the ROW_NUMBER() window function to that result set to produce a list of row numbers equal to the number of rows in E4. OVER can be translated as "Fetch me a result set with this ordering and partitioning and apply (OVER) this window function to that result set independent of the ordering in the main SELECT statement."



                More info: OVER Clause







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered 3 hours ago









                HandyDHandyD

                939112




                939112






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230328%2fexplain-an-over-clause%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Knooppunt Holsloot

                    Altaar (religie)

                    Gregoriusmis