Debugger For SQL

  • I worked last winter at a shop with a flavor of SQL Server 2005 with debugging capabilities - within SQL Server itself I could right-click on any stored procedure and step into it.

    The company's product was software developed in Microsoft Access with a SQL Server 2005 backend - and they had copies of Microsoft software (office, SQL Server 2005, etc.) available to install.

  • what i find most frustrating is that debugging requires you to be a member of sys admin. now, most developers are advised against doing development as a sys admin, but in order to debug they need to be a sys admin...seems to be a slight paradox there.

    right now we are looking at installing sql express on developer machines so that they can debug locally.

    ----------------------
    https://thomaslarock.com

  • GSquared (8/14/2008)


    andrew gothard (8/12/2008)


    Well - usually in QA once it's written. Prototype in MS - then you get that sinking feeling when a colleague comes up to you baffled that someone normally so arsey about layout suddenly seems to have started setting out code like an inebriated baboon ......

    Personal view is that if you set out code in a certain way, that should be maintained however you open it. And thinking about it, my irritation with Microsoft in this respect goes back to Access version 1.0

    The layout will stay consistent unless you open it in the GUI query designer. Since that tool also tends to break certain types of queries by rewriting them without telling you, after you click Save, I don't use it.

    When you want to edit a view or other query, don't use Design, use Edit. Your layout will stay the way you left it.

    I use it most of the time when developing new queries as it's an excellent prototyping tool. I'm paid as a developer, not a typist - so if I can get something done faster by using this to generate the code I'm going to use - even if it takes some reformatting (if I could convince people to buy a copy of Redgate's refactor I'd be even more efficient) then I'm there! I've been to interviews where I've been given exercises to do "How did you write it as fast?! And you've even fully qualified everything!"

    "I didn't type it out - you're not taking on a typist, I used query designer to develop the basic code"

    "Oh, I just type it out - it's better"

    "Better how? You reckon it's going to run faster on that basis?"

    "Thanks for coming"

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • Yeah, that's the View/Query Designer. It's awful and NO ONE should EVER use it. (there's another one of those rules, again)

    Sorry - totally disagree. Give me one good reason why not? You give me a query where you have a complex set of resultsets you need to combine and I can develop it faster and more accurately then anyone taking the 'hardcore macho' approach of typing every part of the query by hand by prototyping it using QD.

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • Who ever said type completely by hand? Management studio can generate all sorts of stubs for queries that are decently formatted and are far faster than typing. (and are fully qualified and devoid of spelling mistakes)

    I have a copy of SQL Prompt and I wouldn't do serious SQL work without it. The speed improvement is amazing. With that (or the SQL 2008 version) and some management studio tricks, I can probably write a query as fast as you can design one.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • andrew gothard (8/15/2008)


    Yeah, that's the View/Query Designer. It's awful and NO ONE should EVER use it. (there's another one of those rules, again)

    Sorry - totally disagree. Give me one good reason why not? You give me a query where you have a complex set of resultsets you need to combine and I can develop it faster and more accurately then anyone taking the 'hardcore macho' approach of typing every part of the query by hand by prototyping it using QD.

    One reason: I've seen it actually break queries by rewriting them after the save command was issued.

    Here's the specific situation I was dealing with:

    A query that involved a six-tier self-join on a table, and joins from each level to other sub-tables.

    The query was originally hand-written in Query Analyzer, and worked as desired and expected.

    A dev opened it in the Query Designer, added one column, saved it, and it rewrote all the self-joins from a chain into a single join to the first instance. This resulted in the query not returning what was wanted at all. Completely broke it.

    My first assumption was that the dev had done something wrong, but that turned out to not be the case.

    I repeated this experiment several times, and had others test it as well, and it consistently broke the query in the same way every time.

    This was tested in Enterprise Manager on SQL 2000.

    When I upgraded to SQL 2005 and Management Studio, I tested it again. It still generated the same problem.

    I just tested it again, and it does not appear to have done it this time, but it did reformat the query pretty heavily.

    Here's what I wrote:

    create table SelfJoin (

    ID int identity primary key,

    ParentID int references dbo.SelfJoin (ID),

    Col1 varchar(100))

    go

    create table SelfJoinSub (

    ID int identity primary key,

    SelfJoinID int references dbo.SelfJoin,

    Col1 varchar(100))

    go

    create view SelfJoinView

    as

    select

    sj1.id as sj1_id, sj1.parentid as sj1_parentid, sj1.col1 as sj1_col1, sjs1.col1 as sjs1_col1,

    sj2.id as sj2_id, sj2.parentid as sj2_parentid, sj2.col1 as sj2_col1, sjs2.col1 as sjs2_col1,

    sj3.id as sj3_id, sj3.parentid as sj3_parentid, sj3.col1 as sj3_col1, sjs3.col1 as sjs3_col1

    from dbo.SelfJoin sj1

    left outer join dbo.SelfJoin sj2

    on sj1.id = sj2.parentid

    left outer join dbo.SelfJoin sj3

    on sj2.id = sj3.parentid

    and sj2.id is not null

    left outer join dbo.SelfJoinSub sjs1

    on sjs1.selfjoinid = sj1.id

    left outer join dbo.SelfJoinSub sjs2

    on sjs2.selfjoinid = sj2.id

    and sj2.id is not null

    left outer join dbo.SelfJoinSub sjs3

    on sjs3.selfjoinid = sj3.id

    and sj3.id is not null

    go

    create view SelfJoinView2

    as

    select

    sj1.id as sj1_id, sj1.parentid as sj1_parentid, sj1.col1 as sj1_col1, sjs1.col1 as sjs1_col1,

    sj2.id as sj2_id, sj2.parentid as sj2_parentid, sj2.col1 as sj2_col1, sjs2.col1 as sjs2_col1,

    sj3.id as sj3_id, sj3.parentid as sj3_parentid, sj3.col1 as sj3_col1, sjs3.col1 as sjs3_col1

    from dbo.SelfJoin sj1

    left outer join dbo.SelfJoin sj2

    on sj1.id = sj2.parentid

    left outer join dbo.SelfJoin sj3

    on sj2.id = sj3.parentid

    and sj2.id is not null

    left outer join dbo.SelfJoinSub sjs1

    on sjs1.selfjoinid = sj1.id

    left outer join dbo.SelfJoinSub sjs2

    on sjs2.selfjoinid = sj2.id

    and sj2.id is not null

    left outer join dbo.SelfJoinSub sjs3

    on sjs3.selfjoinid = sj3.id

    and sj3.id is not null

    go

    insert into dbo.selfjoin (col1)

    select 'First Entry'

    insert into dbo.selfjoin (parentid, col1)

    select 1, 'Second Entry'

    insert into dbo.selfjoin (parentid, col1)

    select 2, 'Third Entry'

    insert into dbo.selfjoinsub(selfjoinid, col1)

    select 1, 'First Sub'

    union all select 2, 'Second Sub'

    union all select 3, 'Third Sub'

    go

    select *

    from dbo.SelfJoinView

    select *

    from dbo.SelfJoinView2

    I opened up View2 in the designer, had it add an Order By Ascending on the first column, and it reformatted the query to this:

    ALTER VIEW [dbo].[SelfJoinView2]

    AS

    SELECT TOP (100) PERCENT

        sj1.ID AS sj1_id, sj1.ParentID AS sj1_parentid, sj1.Col1 AS sj1_col1, sjs1.Col1 AS sjs1_col1,

        sj2.ID AS sj2_id, sj2.ParentID AS sj2_parentid, sj2.Col1 AS sj2_col1, sjs2.Col1 AS sjs2_col1,

        sj3.ID AS sj3_id, sj3.ParentID AS sj3_parentid, sj3.Col1 AS sj3_col1, sjs3.Col1 AS sjs3_col1

    FROM dbo.SelfJoin AS sj1 LEFT OUTER JOIN

    dbo.SelfJoin AS sj2 ON sj1.ID = sj2.ParentID LEFT OUTER JOIN

    dbo.SelfJoin AS sj3 ON sj2.ID = sj3.ParentID AND sj2.ID IS NOT NULL LEFT OUTER JOIN

    dbo.SelfJoinSub AS sjs1 ON sjs1.SelfJoinID = sj1.ID LEFT OUTER JOIN

    dbo.SelfJoinSub AS sjs2 ON sjs2.SelfJoinID = sj2.ID AND sj2.ID IS NOT NULL LEFT OUTER JOIN

    dbo.SelfJoinSub AS sjs3 ON sjs3.SelfJoinID = sj3.ID AND sj3.ID IS NOT NULL

    ORDER BY sj1_id

    Note that this time I only tried it with a 3-level chain, not 6-level, so it's not a full test. Six is what failed before.

    I've also run into other people who have had other problems with queries being incorrectly re-written. I don't remember what the problems were, but they were actual code problems with the query, not just the completely unreadable layout that it enforces.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GilaMonster (8/15/2008)


    Who ever said type completely by hand?

    Not you, I'm sure - I do know people who have that attitude, though

    Management studio can generate all sorts of stubs for queries that are decently formatted and are far faster than typing. (and are fully qualified and devoid of spelling mistakes)

    I have a copy of SQL Prompt and I wouldn't do serious SQL work without it. The speed improvement is amazing. With that (or the SQL 2008 version) and some management studio tricks, I can probably write a query as fast as you can design one.

    Possibly - but you're you; how many other people? (;p

    Depends - I've tried SQL prompt, and I'm sure it's fine in most cases - try the bugger with something like SAP, BaaN or some call centre software etc where you may have over 20k tables, and it's a total productivity killer (mind, that's a bit arsey, the tool's not designed for that - I'd consider if for anything I've designed, the max number of tables I've ever put together is about 250, it'd be cool for that).

    The tool I can't praise too highly is refactor though. You get some vile abomination with almost nothing properly qualified, every query on one line, all in lower case with no gaps between commas, and often keywords - and within 2 minutes you can have something decently set out, hey that's magic (ok, the code's still crap - but at least you can read the bit's you need to destroy). And I *still* cannot get my management to buy it for the buttons it costs! Hell - in terms of developer time it can pay for it's self in 2 weeks.

    Only thing it lacks is the ability to reverse assenine aliases

    "I know, someone's spent time carefully naming the tables, views etc in this database so they're meaningful, boooorinrg, I'll call them t1, t2, t3 and t4, 'cos that'll save me typing"

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • andrew gothard (8/15/2008)


    Yeah, that's the View/Query Designer. It's awful and NO ONE should EVER use it. (there's another one of those rules, again)

    Sorry - totally disagree. Give me one good reason why not?

    Well, the discussion was about formatting and the formatting reasons are: 1) it generate horribly formatted code, and 2) any attempt to reformat it to something acceptable, is undone every time you try to edit with it.

    But there other reasons too, big ones like the fact that it cannot parse and manage a lot of perfectly good SQL Select statements, or it cannot see or add either the system views or the INFORMATION_SCHEMA views and little reasons like it's deceptive treatment of '*'.

    You give me a query where you have a complex set of resultsets you need to combine and I can develop it faster and more accurately then anyone taking the 'hardcore macho' approach of typing every part of the query by hand by prototyping it using QD.

    Well, if you mean using it to jump-start writing a query, I guess that's OK, but in my experience, I lost more time reformatting what it produced into something readable than I gained by being able to drop the tables and columns into it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Back to the original question about the SQL Server debugger. My question is why would one ever use such a tool? I have been writing SQL since 1990 and stored procedures since 93, and have never had a use for a debugger.

    If there is flow of control and iterations, I think that this is a design flaw and that that kind of logic should be in application programs, not imbedded in stored procedures.

    Of course, the worse offender are the system stored procedures, although the later versions are going to statements instead of sps (create login vs sp_addlogin).

    SQL = Scarcely Qualifies as a Language

  • Carl I am not sure what you are trying to say, but SQL in todays world is being looked as a powerful development tool.It would not be correct to say that a query written would have only simple CURD statements.There are scenarios like while using a table variable and looping through them you might need to know what value of the loop are you in and many such scenarios , atleast to the bare minimum to see if your params are passed and treated as expected.

    Comments ?

  • I am not sure what you are trying to say, but SQL in todays world is being looked as a powerful development tool.

    It would not be correct to say that a query written would have only simple CURD statements.

    There are scenarios like while using a table variable and looping through them you might need to know

    what value of the loop are you in and many such scenarios

    , atleast to the bare minimum to see if your params are passed and treated as expected.

    [/code]

    And that is why I get paid the big bucks to come in and fix the inappropriate usage of stored procedures.

    Here is one senario that is constantly posted at these forums and is in Jeff Moden's article

    "Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays"

    at http://www.sqlservercentral.com/articles/T-SQL/63003/

    The technical solution is excellent but the architural design is flawed and "array" should not be passed to a stored procedure.

    The optimal architecture is for the application tier to formats a single SQL command batch that such as this:

    set nocount on

    set xact_abort on

    create table #spname (v varchar(255) not null primary key)

    insert into #sp

    select 'Element01' union all

    select 'Element02' union all

    select 'Element03'

    exec dbo.spname @anyotherparameters

    The stored procedure then can process the temporary table with standard SQL and there is no need

    for the T-SQL statements such as while, variables, or text parsing, and therefore, no need for a debugger.

    SQL = Scarcely Qualifies as a Language

  • Lemme get this straight, Carl... you think it's better for the app to write and pass data in the forum of Unioned SELECT statements than to pass a comma seperated string? Whatcha gonna do when you hit the limit for Union? 🙂

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

  • I've rarely used the debugger, in part because it was slightly flaky in 2000. But I did find a few uses where it was helpful.

    I'd disgree on your solution as being optimal. It works in some places, not in others.

  • From Jeff Modem:

    you think it's better for the app to write and pass data in the forum of Unioned SELECT statements than to pass a comma seperated string?

    Yes, absolutely and I have confirmed this with benchmarks.

    Whatcha gonna do when you hit the limit for Union?

    The limit for union is 1024 in the default configuration, otherwise you get error

    Server: Msg 8621, Level 17, State 88, Line 2

    Internal Query Processor Error: The query processor ran out of stack space during query optimization.

    But, there is has an easy workaround by having multiple insert statements for each group of 1024 values.

    Of course, there is a limit on the batch size, which is 256Mb.

    SQL = Scarcely Qualifies as a Language

  • Sure would like it if you'd post your benchmark 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)

Viewing 15 posts - 16 through 30 (of 56 total)

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