Post reply

printing 1 to 100 in SQL Server as a batch....

  • June 29, 2009 at 5:03 pm

    #1017304

    -- 🙂

    SELECT TOP (100) N FROM dbo.Tally ORDER BY N ASC;

    More 'fun':

    SELECTTOP (100)

    ROW_NUMBER() OVER (ORDER BY C1.[object_id]) AS N,

    FROMmaster.sys.columns C1

    ORDERBY A, (1 / CASE WHEN COUNT(*) OVER () = 100 THEN 1 ELSE 0 END);

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 29, 2009 at 8:43 pm

    #1017333

    Paul White (6/29/2009)

    ...

    More 'fun':

    SELECTTOP (100)

    ROW_NUMBER() OVER (ORDER BY C1.[object_id]) AS N,

    FROMmaster.sys.columns C1

    ORDERBY A, (1 / CASE WHEN COUNT(*) OVER () = 100 THEN 1 ELSE 0 END);

    I'm not sure what you are getting at with that code, especially since it has 2 syntax errors (the comma after the "AS N" and there is no column A).

    What is this code in the ORDER BY clause meant to do?

    (1 / CASE WHEN COUNT(*) OVER () = 100 THEN 1 ELSE 0 END);

    Is it meant to do something different than this?

    SELECT TOP (100)

    ROW_NUMBER() OVER (ORDER BY C1.[object_id]) as N

    FROM

    master.sys.columns C1

    ORDER BY

    1

  • June 29, 2009 at 9:52 pm

    #1017350

    Intentional obfuscation of purpose through the joyful infusion of unnecessary code just for the fun of it all. 😉

    --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)

  • June 29, 2009 at 10:34 pm

    #1017365

    Michael Valentine Jones (6/29/2009)

    I'm not sure what you are getting at with that code, especially since it has 2 syntax errors (the comma after the "AS N" and there is no column A)

    Didn't mean to confuse you, this is how it should look: (The code was part of a bigger CTE construction which didn't quite survive the edit and copy/paste operation)

    SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY C1.[object_id]) AS N

    FROM master.sys.columns C1

    ORDER BY N, (1 / CASE WHEN COUNT(*) OVER () = 100 THEN 1 ELSE 0 END);

    Michael Valentine Jones (6/29/2009)

    What is this code in the ORDER BY clause meant to do?

    I'll enlighten you in a second.

    Michael Valentine Jones (6/29/2009)

    Is it meant to do something different than this?

    SELECT TOP (100)

    ROW_NUMBER() OVER (ORDER BY C1.[object_id]) as N

    FROM

    master.sys.columns C1

    ORDER BY

    1

    Ha! Er, yeah - it raises an error if there aren't enough rows in the system table 😀

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 29, 2009 at 10:38 pm

    #1017366

    Jeff Moden (6/29/2009)

    Intentional obfuscation of purpose through the joyful infusion of unnecessary code just for the fun of it all. 😉

    I can see why you'd suspect that of me; but no, not this time 😎

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 30, 2009 at 6:25 am

    #1017517

    Paul White (6/29/2009)

    Ha! Er, yeah - it raises an error if there aren't enough rows in the system table 😀

    Use Master.sys.sysColumns instead... it'll have enough rows. 😉

    --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)

  • June 30, 2009 at 8:19 am

    #1017599

    Jeff Moden (6/30/2009)

    Paul White (6/29/2009)

    Ha! Er, yeah - it raises an error if there aren't enough rows in the system table 😀

    Use Master.sys.sysColumns instead... it'll have enough rows. 😉

    You're a funny man, Mr. Moden :w00t: :w00t:

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 30, 2009 at 8:33 am

    #1017610

    Ack.... :hehe: wasn't trying to be funny on that one. Master.sys.sysColumns will normally contain over 10,000 rows of system related column info even on brand new installations. 🙂

    --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)

  • June 30, 2009 at 8:35 am

    #1017612

    Jeff Moden (6/30/2009)

    Ack.... :hehe: wasn't trying to be funny on that one. Master.sys.sysColumns will normally contain over 10,000 rows of system related column info even on brand new installations. 🙂

    You *do* know it's deprecated? 😉

    edit: Over to Lynn!

    FWIW I always cross join my sys.columns at least once; it was Gail's post originally which kicked the whole thing about row counts off. The ORDER BY - with error generation - was just to prove a point: you can engineer it so it doesn't fail silently if someone has hacked their master database about. I wouldn't use it, but I could.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 30, 2009 at 8:48 am

    #1017622

    Damn... can't keep up with all the stuff Microsoft wants to take away anymore. Didn't know that. I knew that dbo.sysColumns was going away, just not sys.sysColumns. If they keep deprecating everything useful and replacing it with some of the junk I've seen, pretty soon MS-SQL is going to look a lot like some of the other RDBMS's that I absolutely hate.

    --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)

  • June 30, 2009 at 8:53 am

    #1017629

    Lynn Pettis (6/29/2009)

    Jeff Moden (6/29/2009)

    Lynn Pettis (6/29/2009)

    And that will work until the SQL Server 2000 system table "views", like sys.syscolumns, go away in a future version od SQL Server. They have been depreciated.

    I'm pretty sure that Master.dbo.SysColumns is the 2000 compatability view... not Master.Sys.SysColumns. Has Master.Sys.SysColumns also been deprecated? Haven't been keeping up on these.

    master.dbo.syscolumns == master.sys.syscolumns and, yes, they are depreciated.

    Quoted to aid Jeff's memory 🙂

    Oh and I quite agree on the whole deprecation issue.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 30, 2009 at 9:00 am

    #1017635

    Paul White (6/30/2009)

    Lynn Pettis (6/29/2009)

    Jeff Moden (6/29/2009)

    Lynn Pettis (6/29/2009)

    And that will work until the SQL Server 2000 system table "views", like sys.syscolumns, go away in a future version od SQL Server. They have been depreciated.

    I'm pretty sure that Master.dbo.SysColumns is the 2000 compatability view... not Master.Sys.SysColumns. Has Master.Sys.SysColumns also been deprecated? Haven't been keeping up on these.

    master.dbo.syscolumns == master.sys.syscolumns and, yes, they are depreciated.

    Quoted to aid Jeff's memory 🙂

    Oh and I quite agree on the whole deprecation issue.

    Yep, posted that back around post #50 on this thread. 😉

  • June 30, 2009 at 9:05 am

    #1017642

    Paul White (6/29/2009)

    Michael Valentine Jones (6/29/2009)

    I'm not sure what you are getting at with that code, especially since it has 2 syntax errors (the comma after the "AS N" and there is no column A)

    Didn't mean to confuse you, this is how it should look: (The code was part of a bigger CTE construction which didn't quite survive the edit and copy/paste operation)

    SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY C1.[object_id]) AS N

    FROM master.sys.columns C1

    ORDER BY N, (1 / CASE WHEN COUNT(*) OVER () = 100 THEN 1 ELSE 0 END);

    Michael Valentine Jones (6/29/2009)

    What is this code in the ORDER BY clause meant to do?

    I'll enlighten you in a second.

    Michael Valentine Jones (6/29/2009)

    Is it meant to do something different than this?

    SELECT TOP (100)

    ROW_NUMBER() OVER (ORDER BY C1.[object_id]) as N

    FROM

    master.sys.columns C1

    ORDER BY

    1

    Ha! Er, yeah - it raises an error if there aren't enough rows in the system table 😀

    Paul

    OK, I understand now, but it might be worthwhile to include a comment about the purpose in code like that. 🙂

    Here is my favorite way of putting an error generator inline in code, since it allows you to create an custom error message of sorts. By attempting to covert the error message to an int, SQL Server passes through the text that you are trying to convert and displays the custom error message. I first started using it in UDFs where you can't use RAISERROR.

    SELECT TOP (100)

    ROW_NUMBER() OVER (ORDER BY C1.[object_id]) AS N

    FROM

    master.sys.columns C1

    ORDER BY

    N,

    CASE WHEN COUNT(*) OVER () = 100 then ''

    -- Generate custom error message using invalid integer conversion

    else convert(int,char(13)+Char(10)+char(13)+Char(10)+char(13)+Char(10)+

    '***** Not enough rows in number table source master.sys.columns ***'+

    char(13)+Char(10)+char(13)+Char(10)+char(13)+Char(10)) end;

    Results:

    [font="Courier New"]Server: Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value '

    ***** Not enough rows in number table source master.sys.columns ***

    ' to data type int.[/font]

    By the way, I think your error check may have a problem. "COUNT(*) OVER ()" returns the rows in the table, not the rows returned by top.

  • June 30, 2009 at 9:05 am

    #1017643

    Yep... I saw that, Lynn. Thanks. I actually thought it was a type-o. Now I know better.

    Guess I'll have to fall back a bit and start using sys.All_Columns instead.

    Does anyone know if Master.dbo.spt_Values has been deprecated?

    --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)

  • June 30, 2009 at 9:22 am

    #1017659

    Michael Valentine Jones (6/30/2009)

    OK, I understand now, but it might be worthwhile to include a comment about the purpose in code like that. 🙂

    Heh. It would be better never to write code like that at all! 🙂

    I've only ever used that twice before - both times on blogs - and always with ROW_NUMBER in the ORDER BY, not COUNT(*) OVER(). COUNT(*) OVER () is something I try to avoid anyway - just look at the plan it often produces. It's similar to a recursive CTE!

    You are right about COUNT(*) counting all the rows (of course!) so, for the sake of completeness, here's a fixed version: (with a new and more exciting error message!)

    DECLARE@Rows BIGINT;

    SET@Rows = 100;

    ;WITHRowGenerator

    AS(

    SELECT TOP (@Rows)

    ROW_NUMBER() OVER (ORDER BY C1.[object_id]) AS N

    FROM master.sys.columns C1

    )

    SELECTN,

    COUNT(*) OVER () AS A

    FROMRowGenerator

    ORDERBY

    N ASC,

    SQRT(COUNT(*) OVER () - @Rows);

    That trick with the CASE is clever. I guess my only small concern would be that it depends on SQL Server not evaluating the ELSE when a prior condition is satisfied. I came across something similar a few weeks ago on another forum - with a CONTAINSTABLE full-text predicate which had worked fine on 2005 but broke on 2008. The cause was expression-evaluation order.

    My favoured approach to error-generation in a function is to read or write memory outside my address space, so crashing the server*.

    Paul

    * (weak) joke

    edit: So, I managed to put the expression in the SQRT backwards - lol.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 15 posts - 61 through 75 (of 79 total)

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