Post reply

Beginning SQL Server - Limit Your Queries

  • July 30, 2004 at 4:52 pm

    #59719

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist

  • August 22, 2004 at 5:52 pm

    #520111

    Perfect for a newbie like me!

    -gol

  • April 14, 2006 at 8:08 am

    #632629

    Isn't this bad since it will lock the rows you're viewing?  I started out with EM, but now I couldn't live without query analyzer.

     

     

  • April 14, 2006 at 9:59 am

    #632655

    The link to part one goes to the part 2 page. Is it supposed to go to this:

    http://www.sqlservercentral.com/columnists/sjones/beginningsqlserver2000administrationpart1.asp

    ?

  • April 14, 2006 at 11:25 am

    #632672

    I think it was a great article for beginners. I also think the EM is a great tool for beginners to start with and Query Analyzer should be tought as the next step up.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • April 17, 2006 at 8:42 am

    #632769

    I think the only thing you should teach is how to lock themselves out of they system until they get some training.

  • April 17, 2006 at 10:12 am

    #632795

    Thanks Steve. Great article. I have a question the might be slightly off topic. What would be the best way in sql server 2000 to write a stored proc that takes the row limit as an argument?

     

    CREATE PROCEDURE MyTestProc

    @rowlimit

    AS

     

    ??????

    -Kevin Williams

  • April 18, 2006 at 11:07 am

    #632911

    You could do something like this...

    Set Rowcount @rowlimit  

select col1, col2 from myTable where...







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 18, 2006 at 11:12 am

    #632912

    Thanks. Worked great.

  • April 18, 2006 at 11:16 am

    #632913

    Just be careful. There are certain circumstances where Set RowCount is ignored. Also, the RowCount will stay set within your procedure until the procedure either ends or you turnit off (Set ROWCOUNT = 0 turns it off).

    If not used carefully, Set RowCount can cause you a lot of problems, and you won't realize it until it's too late.

    Personally, I would advocate building the query dynamically and using Exec() or sp_executesql to execute it.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • April 18, 2006 at 11:25 am

    #632918

    Right...here's how you would do this:

    Set @rowlimit = 10
exec('select top ' +  @rowlimit + ' col1, col2 from myTable where...')







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 18, 2006 at 11:32 am

    #632919

    Thanks.

    Seems strange that this syntax does not work...

    select top @varname * from ...

    and this one does

    set rowcount @varname

    Sometimes I feel like I am jumping through hoops e.g., dynamic query, etc.., to get something simple done.

  • April 18, 2006 at 11:35 am

    #632922

    I don't think there's any of us here that would disagree with you there. But on the other hand, if it was all simple, anyone would be able to do our job effectively and we would be paid less.  Or worse, I'd have to work as a developer instead of a DBA.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • April 18, 2006 at 11:36 am

    #632923

    Here's the (much read) link that'll unravel the mysteries of dynamic sql for you..

    the curse & blessings of dynamic sql







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 18, 2006 at 11:38 am

    #632925

    hey..hey..go easy...I, for one, belong to the "developer" world and wouldn't trade places with a dba...







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 15 posts - 1 through 15 (of 19 total)

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