Post reply

script to select top x rows

  • November 14, 2003 at 12:39 pm

    #106821

    Does anyone have a script which will select the top x rows from every user table in a database?

    Thanks in advance for any suggestions.

    Regards

    Gary

  • November 14, 2003 at 1:08 pm

    #482126

    sp_msforeachtable 'SELECT TOP 5 * FROM ?'

    --Jonathan



    --Jonathan

  • November 14, 2003 at 1:51 pm

    #482131

    Thanks Jonathan! This was very helpful

    Gary

  • November 17, 2003 at 7:40 am

    #482211

    Gary,

    Just curious, what is the purpose of your query?

    I think, unless you use an 'order by clause', the rows selected will be sorted by the first column of each table (whatever that happens to be). - Might not be the top 5 you are looking for.

    Could you let us know why you need this query? (OR, if I am mistaken??)

    Thanks

    Ram Achar

    Please let us know why you need this query.

  • November 17, 2003 at 7:54 am

    #482214

    quote:

    Gary,

    Just curious, what is the purpose of your query?

    I think, unless you use an 'order by clause', the rows selected will be sorted by the first column of each table (whatever that happens to be). - Might not be the top 5 you are looking for.

    Could you let us know why you need this query? (OR, if I am mistaken??)

    Thanks

    Ram Achar

    Please let us know why you need this query.

    Actually, they'll be ordered by the clustered index (but don't tell anyone I wrote that, as it's not admitted by Microsoft).

    I assumed he just wanted a sample of the data; that's one of the only uses I have for TOP *.

    --Jonathan



    --Jonathan

  • November 17, 2003 at 2:03 pm

    #482308

    You're right, Jonathan. Another analyst is looking a SQL 2K database to analyze the schema. He just wanted a few sample records of every table, so the order is irrelevant to him.

    Regards,

    Gary

  • November 21, 2003 at 4:09 am

    #483039

    quote:

    Actually, they'll be ordered by the clustered index (but don't tell anyone I wrote that, as it's not admitted by Microsoft).

    And admitted it is not for a right reason. I always thought that default ordering by clustered index is the case but it bit me pretty hard. If query optimiser decides that it can benefit from parallel query execution it will start multiple threads each one returning subsets ordered by clustered index but subsets are going to be interwoven.

    For example, assuming that clustered index contains INDENTIY(1,1) you might get something like that:

    1

    2

    3

    4

    5

    20

    21

    22

    23

    24

    6

    7

    8

    9

    ...

    --

    georged

    Edited by - georgedo on 11/21/2003 04:10:34 AM


    --
    George

Viewing 7 posts - 1 through 6 (of 6 total)

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