SELECT * FROM ( ... ) X

  • Hi all!

    I'm trying to understand how does this work. I've read a thread here about the X thing but it wasn't useful at all.

    Given this example:

    DECLARE @PAGE INT=1
    WHILE (@PAGE<=5)
    BEGIN
    DECLARE @ROWS INT=5
    SELECT * FROM ( SELECT ROWNUM = ROW_NUMBER()
    OVER (ORDER BY CLI_COD), *
    FROM CLIENTE) X
    WHERE
    ROWNUM > (@ROWS * (@PAGE -1)) AND
    ROWNUM <= (@ROWS * (@PAGE -1)) + @ROWS
    SET @PAGE+=1
    END
    GO

    which shows clients in pages of five rows each, I can't quite understand what the X does, where one would expect a table name. Nonetheless, this works and does what it is expected to do (except for the last empty row, I think it could be solved by shorting the WHILE to < 5 instead of <= 5).

    Thank you in advance.

    Cheers,

    Ale

  • The reason for the Derived Table "X" is so that they can use a WHERE clause to control how may rows are returned much like would be done using a CTE in set-based code.  The ROWNUM columns in the WHERE clause should (IMHO) have an "X." before each of them.

    I'll also tell you that I don't understand why anyone would do 5 clustered index scans to get 5 consecutive pages.  Why not do one scan to get the 5*5 or 25 pages all at once and then divide those paltry 25 pages up?

    Also, consider the use of OFFSET and FETCH NEXT in the ORDER BY instead of using ROW_NUMBER, etc.  It'll make for some nice clean code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thank you Jeff.

    It was just an example code from a book I'm reading intended to exemplify the use of WHILE in TSQL. Maybe it's not the best example, but anyway it made me think about the X there. BTW you were right about the X before the ROWNUM in the WHERE condition, though it works the same with or without them (of course with them the code is more clear).

    The 5 rows division was just because the CLIENTE table has a small amount of rows (exactly 20 rows).

    I'll try the changes suggested in the ORDER BY clause, and I'll also check on derived tables because I wasn't aware of their existance...

    Cheers,

    Alejandro

  • FatsGordon wrote:

    Thank you Jeff.

    It was just an example code from a book I'm reading intended to exemplify the use of WHILE in TSQL.

    Alejandro

    You'll find very few cases in T-SQL where WHILE provides the optimum solution, if all you are doing is returning sets of data. Should you find yourself writing WHILE loops or CURSORs, please consider posting additional questions here. The likelihood is that someone will suggest a solution which performs far better.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • You can do it in a more straight forward way using OFFSET:

    DECLARE @PAGE INT = 1;

    WHILE (@PAGE <= 5)
    BEGIN
    DECLARE @ROWS INT = 5;
    SELECT *
    FROM CLIENTE
    ORDER BY CLI_COD
    OFFSET (@PAGE - 1) * @ROWS ROWS FETCH NEXT @ROWS ROWS ONLY
    SET @PAGE += 1;
    END;
    GO
  • Thank you each and everyone for all the input on this matter, I started asking for what it turned out to be derived tables and you ended up tuning an example query from a book, which is leading me in turn to analize other aspects that I also wasn't aware of (I'm a newbie to SQL Server and though I was using other RDBMS in the past and also programmed some SP many many years ago in the late '90s, my 'expertise' was lately reduced to perform just SELECTs... and now I need to revisit all what I somewhat have lost).

    About the last answer from Jonathan, it would look like this (in order to fully understand it):

    @PAGE = 1

    ...

    OFFSET (1 - 1) * 5 ROWS ==> OFFSET 0 ROWS

    FETCH NEXT 5 ROWS ONLY

    ...

    And so on up to:

    @PAGE=5

    ...

    OFFSET (5 - 1) * 5 ROWS ==> OFFSET 20 ROWS

    FETCH NEXT 5 ROWS ONLY

    And here I have a question: what happens when it tries to fetch non-existing rows? I mean internally. Because when I execute it, it just ends up ok throwing no errors.

    Sorry for my dumb question, I'm trying to learn and understand.

    Thank you.

    Cheers,

    Alejandro

  • FatsGordon wrote:

    Thank you Jeff.

    It was just an example code from a book I'm reading intended to exemplify the use of WHILE in TSQL. Maybe it's not the best example, but anyway it made me think about the X there. BTW you were right about the X before the ROWNUM in the WHERE condition, though it works the same with or without them (of course with them the code is more clear).

    The 5 rows division was just because the CLIENTE table has a small amount of rows (exactly 20 rows).

    I'll try the changes suggested in the ORDER BY clause, and I'll also check on derived tables because I wasn't aware of their existance...

    Also, Jonathan is spot on in using the OFFSET/FETCH NEXT method... it greatly simplifies the code although it doesn't spectacularly improve performance and calling it 5 times in a WHILE loop is still an issue.  That means that it will do a scan up to at least the lowest page 5 times.  It would be much better to realize they're 5 adjacent pages and to read all the data for those five pages as a single 25 row "book" and then split that result up into the desired 5 pages using a loop.

    Cheers,

    Alejandro

    "Derived Tables" come in many forms (CTE, Select in a FROM clause, VALUEs clause).  The VALUES clause is also known as a "Table Valued Constructor".  Lot's of names for the same and similar things and the "X" is called a "Table Alias".  The reason why the code worked without "X." notation (2 part naming in this case) work on RowNum is because RowNum was a unique name in the entire query.  It's not actually a good idea to leave such things off any column, unique or otherwise, when you have Joins or Derived Tables, etc, because it's a pain to figure things out when troubleshooting.

    On the fetch of non-exiting rows not causing any type of error... it wasn't an error that SQL Server can or that you'd even want  it error on.  You'd have to test @@ROWCOUNT to verify that it returned the correct number of rows and then raise a custom error.  Without tell it what an error, SQL Server/T-SQL can't tell if it's ok or not because you might even be looking for no rows to be returned.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • p.s.  Since you've not been around since the 90's, you might want to have a look at the introduction to a technique that is extremely useful for a whole lot of things.  The article tells you how it works and provides a couple of simple examples.  I use the basis of the method in a large amount of my code and, certainly for the very rapid generation of test data to do studying like your doing.  The two links below the first one provide an introduction to that, as well.

    https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

    https://www.sqlservercentral.com/articles/generating-test-data-part-1-generating-random-integers-and-floats-1

    https://www.sqlservercentral.com/articles/generating-test-data-part-2-generating-sequential-and-random-dates

    All 3 article emphasize that the most basic aspect of a SELECT is that it's actually a loop behind the scenes and so you can mostly avoid the explicit RBAR of While loops and Cursors and the hidden RBAR of recursive CTEs (rCTEs).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thank you Jeff! I will certainly read those articles.

    And again, thank you all for your kind answers, you are a great team!

    Cheers,

    Alejandro

  • There has been a fair bit of high level stuff about how to use X, but no low level stuff about why it is there and why it is needed.

    The SQL language is based around doing things to tables. In order to manipulate a table it has to have a name. This is where the X comes in. The full SQL syntax would be SELECT * FROM (...) AS X which makes it a bit more obvious that X is being used as a table name. Or to be more precise it is being used as an alias for a table name.

    If you look at SELECT A,B,C FROM MYDB.DBO.MYTABLE AS X it is easy to see that X is used here as a table alias.

    Now that you gave the alias, it can be put to use. In both examples the alias is useful in a WHERE clause, such as WHERE X.A = 5 or in a join WHERE X.A = Y.B

    Giving a table an alias can help with making WHERE clauses easier to read. If you have a derived table as in the OP, then the derived table does not have any name of its own so a table alias becomes mandatory.  This is the case regardless of any WHERE clauses.

    There are some aspects of the SQL language that seem overly strict. IMHO requiring a table alias for a derived table when no subsequent WHERE clauses exist is an example of being overly strict. But we can only work with the tools we have.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply