• (unregistered)

    This is a joke, right?

    Right?!?

    Please tell me you're not serious.

  • (unregistered)

    Sadly, this is no joke. I know this because I've seen code like this. A LOT of code like this.

     

  • (cs) in reply to

    First off, everybody knows that you can't tighten screws with a common multimeter -- the probes are the wrong shape. You need an oscilloscope for that.

  • (cs)

    Mm, It's the new 0.5 tier development paradigm.

  • (unregistered)

    There are dueling WTF's on this one.

    Note the lack of an ORDER BY clause on the SQL SELECT.

  • (cs) in reply to

    It's real allright.  I'm the one who sent it in.  And there definitely is more where that came from.

  • (cs) in reply to Stan Rogers
    Stan Rogers:
    First off, everybody knows that you can't tighten screws with a common multimeter -- the probes are the wrong shape. You need an oscilloscope for that.


    Everyone knows you hammer screws in with a Crescent® wrench (spanner). [;)]
  • (unregistered)

    Oh, the WTFs are plenty.

    He qualifies the columns with the table names in the JOIN (StaffDepartments.DepartmentID and Staff_DepartmentLink.DeptID), but for some reason, not in the SELECT clause.

    In one instance, he's saying "src As 'rowsrc'", then two lines down, there's a similar select statement, but with no single quotes around rowsrc. (Until I read this, I had no idea that single quotes around a table alias name would even compile in SQL Server.)

    And, as someone else pointed out, no ORDER BY on #HTML, or even an identity column to order by. So there's no guarantee the <SELECT> tag will come before the rest of the HTML.

    It's also important to note that even if #HTML was created with an identity column, and then sorted on that identity column, THAT STILL WOULD NOT GUARANTEE THAT THE HTML WOULD BE RETURNED IN THE RIGHT ORDER. SQL generates values for the identity column however it sees fit, regardless of the ORDER BY in the select statement you used to insert the data. This is a common misconception that unfortunately gets lost on a lot of SQL Server developers until their reports are suddenly out of order. (Or in the case of this coder, he gets a nice plain text list of departments instead of a dropdown.)

     

  • (unregistered)

    Wow, this is a great design. Who needs a server-side web programming language at all? When the web server makes a request, just pass it along to SQL Server, and let that dynamically generate the page directly to the browser. Excellent!

    Man, and everyone was telling me to use PHP or something. Why bother?

  • (unregistered)

    <FONT style="BACKGROUND-COLOR: #efefef">This looks ok to me.  At least it is re-usable.  What if you have more than one site that needs this drop down list?  This way the code only has to be one place, in the stored procedure.</FONT>

    <FONT style="BACKGROUND-COLOR: #efefef">Additionally, since it is in a stored procedure it will be pre-compiled on the sql server as opposted to being interpreted if it was in ASP.  Compiled code is always faster than interpreted code.</FONT>

    <FONT style="BACKGROUND-COLOR: #efefef">It also combines UI generation with retrieval of data, making it easier to maintain.</FONT>

  • (unregistered) in reply to

    Tomorrows WTF...

    " <FONT style="BACKGROUND-COLOR: #efefef">This looks ok to me.  At least it is re-usable.  What if you have more than one site that needs this drop down list?  This way the code only has to be one place, in the stored procedure.</FONT>

    <FONT style="BACKGROUND-COLOR: #efefef">Additionally, since it is in a stored procedure it will be pre-compiled on the sql server as opposted to being interpreted if it was in ASP.  Compiled code is always faster than interpreted code.</FONT>

    <FONT style="BACKGROUND-COLOR: #efefef">It also combines UI generation with retrieval of data, making it easier to maintain."</FONT>

  • (unregistered)

    Oh, by the way, if you think this is rich, wait'll you see what atrocities this type of nimrod will commit in Yukon when you can have .NET in the stored procedure.

    WTF!?!?

  • (unregistered)

    I am speechless. [:|]

    I didn't even know this couldn't be done.

  • (cs)

    I'm proud to say that I've written something like that not only once before but twice.  The wtf was that no one yelled at me for doing something so stupid.  My excuse is that I was in college at the time and had never touched a database before doing that.  The stored procedures used values in the database to assign different widths on div tags so that they looked like a graphs.

  • (unregistered)

    Back in my younger programming days, whenever I had a dynamic ddl I always did something like this. Thankfully, I'm not so much of an idiot now :P

  • (cs)

    At least he's using stored proc's.....[:P]

  • (unregistered)

    Quote:

    "It's also important to note that even if #HTML was created with an identity column, and then sorted on that identity column, THAT STILL WOULD NOT GUARANTEE THAT THE HTML WOULD BE RETURNED IN THE RIGHT ORDER. SQL generates values for the identity column however it sees fit, regardless of the ORDER BY in the select statement you used to insert the data. This is a common misconception that unfortunately gets lost on a lot of SQL Server developers until their reports are suddenly out of order. (Or in the case of this coder, he gets a nice plain text list of departments instead of a dropdown.)"

    Uh, WTF?

    You are mistaken on this issue; in fact, it is one of the recommended practices by Microsoft themselves for paging recordsets...

    Anyhow, when you create an IDENTITY column, you must specify seed and increment.

    By definition, this will INCREMENT the value (not "generate values however it sees fit"). This has nothing to do with the gaps you are probably observing in your own usage, most of which are due to deleted data or aborted transactions.

    Whoever taught you this "however it sees fit" misconception should be posted on here in a separate WTF.

    Regards,

    An Angry DBA

     

  • (cs)

    <FONT color=#0000ff>>IF</FONT> ((SELECT COUNT(*) FROM Staff_DepartmentLink) > 0)

    One of my favorite pet-peeves- asking for the precise # of records merely to find out if there are any records at all.

    On large tables in an OLTP setup, this can be just murder to performance.

    --

    And if I hear the whole "storedProcs are precompiled, thus faster" thing I'm going to scream.

    -If you can't measure the difference- it's probably not a valid thing to worry about in most cases.

  • (unregistered) in reply to
    :

    By definition, this will INCREMENT the value (not "generate values however it sees fit"). This has nothing to do with the gaps you are probably observing in your own usage, most of which are due to deleted data or aborted transactions.

    Whoever taught you this "however it sees fit" misconception should be posted on here in a separate WTF.

    Regards,

    An Angry DBA

    Dear Mr. Angry DBA,

    Please check out this page: http://www.aspfaq.com/show.asp?id=2120

    Under the section labeled "#Temp table", you will see this description:

    "The code inserts the entire base table into a #temp table with an IDENTITY column, then uses that column to determine 'rank.' Note that the behavior we see here is taken for granted; it is neither documented nor guaranteed to obey the ORDER BY clause when inserting the rows into the #temp table. The optimizer will insert the rows in whatever order it deems fit, which means that the values in the columns could certainly be out of order. Luckily, it just so happens that it works right so much more often than it fails, that it is virtually reliable."

     

  • (unregistered)

    Oh my gosh... My company not only generates select boxes in a similair way, but generates the entire web page directly from the database using PL/SQL.  The Original authors are very defensive about it, and some to this day (despite many developers complaining about the maintenance issues and the lack of ease of change) don't understand why it's a bad idea to embed bussiness logic and html right there in a procedure on your database.  It pains me to think that Model View Controllers have been around for around 30 years now, but people still don't get it.

  • (cs)

    Actually, I've done things somewhat like that myself --- except I limited to just the single key line ("SELECT DISTINCT '<option value="' + CONVERT(varchar(6), DeptID) + '">' + DepartmentName + '</option>' As src
    FROM StaffDepartments" etc) and handled the <select> and static options in the ASP page.  Building & then returning the #temp table is just pointless overhead.

     


     

  • (cs) in reply to
    :
    Under the section labeled "#Temp table", you will see this description:

    ...The optimizer will insert the rows in whatever order it deems fit, which means that the values in the columns could certainly be out of order. 

    What just proves that the original statement

    SQL generates values for the identity column however it sees fit, regardless of the ORDER BY in the select statement you used to insert the data.
    was in fact WRONG.  The problem is with inserting, not with the identity.

  • (unregistered)

    <FONT style="BACKGROUND-COLOR: #ffffff">Oh god... having *horrible* flashbacks to maintaining an Oracle WebDB application. Pages and pages and pages of HTML all generated by PL/SQL... </FONT>

    <FONT style="BACKGROUND-COLOR: #ffffff"><sob></FONT>

  • (cs) in reply to JamesCurran
    JamesCurran:
    [image]  wrote:
    Under the section labeled "#Temp table", you will see this description:

    ...The optimizer will insert the rows in whatever order it deems fit, which means that the values in the columns could certainly be out of order. 

    What just proves that the original statement

    SQL generates values for the identity column however it sees fit, regardless of the ORDER BY in the select statement you used to insert the data.
    was in fact WRONG.  The problem is with inserting, not with the identity.

    Exactly. If you do a 'bulk' insert (SELECT Something FROM Somewhere INTO #tempTable) then you might get 'unexpected behaviour', if you do INSERT INTO... INSERT INTO... INSERT INTO... then your code will do exactly what you want :).

  • (unregistered)

    <FONT style="BACKGROUND-COLOR: #efefef">I've seen a ton of ASP code that mixed SQL statements in with the presentation logic.  This is the first time I've seen it go the other direction.  </FONT>

    <FONT style="BACKGROUND-COLOR: #efefef">In my last job, all our code laboriously built SQL statements with string concatenation.  </FONT><FONT style="BACKGROUND-COLOR: #efefef">M</FONT><FONT style="BACKGROUND-COLOR: #efefef">y old boss hated stored procedures, for reasons I never figured out.  She used to say, "They're a maintenance nightmare."  I have no idea what she meant by that.  </FONT>

    <FONT style="BACKGROUND-COLOR: #efefef">She also refused to use SQL Server's relational integrity features, saying "It's just a bunch of overhead.  Anyway, if you code the app right, you don't need it." </FONT>

    My reply was:  "I had a ten year career fixing bad data.  A database that refuses to let itself be corrupted is a wonderful thing!" 

  • (unregistered) in reply to JamesCurran
    JamesCurran:
    [image]  wrote:
    Under the section labeled "#Temp table", you will see this description:

    ...The optimizer will insert the rows in whatever order it deems fit, which means that the values in the columns could certainly be out of order. 

    What just proves that the original statement

    SQL generates values for the identity column however it sees fit, regardless of the ORDER BY in the select statement you used to insert the data.
    was in fact WRONG.  The problem is with inserting, not with the identity.

    Semantics. The point is, you can't count on an identity field when you're ordering your resultset just because you put an ORDER BY on the select statement that populated the temp table. Believe me, I have seen this method of ordering results fail spectacularly.

     

  • (cs)

    oh goddamn!  I hate that shit.  We had some guys at my last job build up dynamic HTML emails in stored procs too!  Ack!

  • (cs)

    At least when the hunt was over, the fix was easy :)

  • (unregistered)

    Quote:

    "Mr. Angry DBA,

    Please check out this page: <FONT color=#800080>http://www.aspfaq.com/show.asp?id=2120</FONT>

    Under the section labeled "#Temp table", you will see this description:"

    Yes, I saw the description. I also _read_ it. Somehow I managed to get the actual data from the sentences. You must be a n00b if you:

    a.) formed an impression from a 4 year old article

    b.) failed to follow up on that impression by checking actual SQL Server documentation or Microsoft papers

    c.) continued this thread without having done (b).

    WTF?

    The real issue involves the manner in which SQL Server performs the INSERT phase, during which the ORDER BY of the rows from the source SELECT is apparently not explicitly guaranteed to be honored.

    Now, to be sure, the order of the "Departments" selected may vary, since they may be inserted in whatever order is expedient for the storage engine. This I have not verified. However, the WTF I was referring to (which you also missed, apparently), was that there was no ORDER BY clause on the final SELECT. This may result in the HTML <select> tags coming mixed in with the "Department" names. The order of the HTML <option> elements is much less critical than a complete failure of the user control to render.

    How you made the next induction about the non-guaranteed behavior of IDENTITY columns probably has Socrates rolling over in his grave. If IDENTITY columns performed the way you think they do, with no guarantee as to the incremental behavior, the server would spend all day figuring out which IDENTITY column comes next. It's not a matter of what's expedient in this case, it's a matter of "which number comes next?", period. Sequential columns do have the nasty habit of behaving sequentially.

    WTF & RTFM.

    Regards,

    Mr. Angry DBA

     

  • (unregistered) in reply to
    :
    [image]  wrote:

    By definition, this will INCREMENT the value (not "generate values however it sees fit"). This has nothing to do with the gaps you are probably observing in your own usage, most of which are due to deleted data or aborted transactions.

    Whoever taught you this "however it sees fit" misconception should be posted on here in a separate WTF.

    Regards,

    An Angry DBA

    Dear Mr. Angry DBA,

    Please check out this page: http://www.aspfaq.com/show.asp?id=2120

    Under the section labeled "#Temp table", you will see this description:

    "The code inserts the entire base table into a #temp table with an IDENTITY column, then uses that column to determine 'rank.' Note that the behavior we see here is taken for granted; it is neither documented nor guaranteed to obey the ORDER BY clause when inserting the rows into the #temp table. The optimizer will insert the rows in whatever order it deems fit, which means that the values in the columns could certainly be out of order. Luckily, it just so happens that it works right so much more often than it fails, that it is virtually reliable."

     

  • (unregistered)

    Yeah I see the problem here.... He should have created a user defined function to handle this....sheesh what was he thinking?  :-P

    Was requested to write a procedure like this once...I laughed.

  • (unregistered) in reply to

    <FONT style="BACKGROUND-COLOR: #efefef">"</FONT>failed to follow up on that impression by checking actual SQL Server documentation or Microsoft papers"

    If you rely on Microsoft documentation as the final word on what their server products do or don't do, I would be terrified if my company hired you on as a DBA. I often find developer-run communities, maintained by the people who actually have to live with and work with these products, to be far more accurate and informative.

    Anyway, what difference does it make if the article is four years old? It is still 100% accurate in that section, because I just recently witnessed this behavior myself in production code running on the latest released version of SQL Server.

    "the WTF I was referring to (which you also missed, apparently), was that there was no ORDER BY clause on the final SELECT."

    I didn't miss it. Go back and re-read the post. I mentioned it right above the paragraph you decided to attack.

    "How you made the next induction about the non-guaranteed behavior of IDENTITY columns probably has Socrates rolling over in his grave."

    As I already said, semantics. If you took what I posted to mean that there's somehow a flaw in the nature of identity columns that causes breaks in sequential numbering, then you must view the world in an extremely literal manner.

     

  • (cs)

    It's times like these that make me glad that my boss doesn't want to touch the technical stuff.

    Born and raised in the COBOL era, she keeps herself on the high level and leaves all the Web + DB issues to us developers.

  • (unregistered)

    Astounding. Simply astounding. It's times like these when I get to feel really, really smart.

  • (cs)

    I swear at least once a week there comes a WTF that starts people off on the debate about the fastest way to execute something, via web-based scripting code or a precompiled stored procedure in a database. WAKE UP PEOPLE! Let's get back to the issue at hand: the retard that mashed his hands on the keyboard in just the right order to get the code we saw at the top of our pretty little screens.

    Seriously, why the hell does every WTF turn into a techie-supernerd war to prove who knows the most about assembly and processor optimizations? I concede that everyone reading this site is FAR smarter than me, there's no need to try prove it EVERY DAY. Can we get back to making fun of the hapless idiots that leave behind gems like this that will someday send a starry-eyed newbie into intense therapy?

  • (cs) in reply to Manni
    Manni:

    I swear at least once a week there comes a WTF that starts people off on the debate about the fastest way to execute something, via web-based scripting code or a precompiled stored procedure in a database. WAKE UP PEOPLE! Let's get back to the issue at hand: the retard that mashed his hands on the keyboard in just the right order to get the code we saw at the top of our pretty little screens.

    Seriously, why the hell does every WTF turn into a techie-supernerd war to prove who knows the most about assembly and processor optimizations? I concede that everyone reading this site is FAR smarter than me, there's no need to try prove it EVERY DAY. Can we get back to making fun of the hapless idiots that leave behind gems like this that will someday send a starry-eyed newbie into intense therapy?



    Goes to show where all the WTF stuff comes from. [:P]
  • (unregistered)

    must...resist...using...fist...of...DEATH!

  • (unregistered)

    Quote:

    Anonymous said:

    "SQL generates values for the identity column however it sees fit, regardless of the ORDER BY in the select statement you used to insert the data."

    Now you pretend you did not say this? GMAFB.

    The person who created this WTF did the following:

    1.) Inserted the start tag for the <select> element.

    2.) Inserted, via a SELECT statement, a list of Departments, the order of which cannot be relied upon

    3.) Inserted the end tag for the <select> element.

    Now, I don't care what planet you are from or what "SAM's" book you read; if this person uses an ORDER BY clause on the final SELECT statement, and orders on the IDENTITY column in ascending order, the data will most assuredly be selected in the following order:

    1.) The start tag for the <select> element first

    2.) The <option> elements second, albeit perhaps in an unreliable order

    3..) The end tag for the <select> element lastly

    If the ORDER BY is not used, it could come out like so (or any other permutation):

    1.) The end tag for the <select> element

    2.) The start tag for the <select> element

    3.) The unpredictably-ordered list of <option> elements

    4.) Any mixture thereof, including having the <select> element tags interspersed between the <option> element rows

    My point is that the WTF here is more related to the FINAL SELECT. As long as the final SELECT includes an ORDER BY, anyone familiar with SQL Server internals will bet the farm on the fact that the former series of events will occur, and the control would render every time. The latter series COULD NOT POSSIBLY HAPPEN. What we have here is a fairly common error. It is an error that would affect the order of the <option> elements ONLY, as long as the ORDER BY was used on the final SELECT. It would not prevent the application from functioning. It also has F**K ALL to do with how the IDENTITY value are generated.

    You asserted that SQL Server would, and I quote (again):

    "[generate] values for the identity column however it sees fit, regardless of the ORDER BY in the select statement you used to insert the data"

    This is a mischaracterization of the facts, and I'm sad to see you squirm just because I have shined a light of knowledge on your ineptitude. My wish is merely to stamp out bad information so that some other n00b doesn't adopt it as "fact" the way that you have done, and begin spouting it on boards to look strong.

    As an aside, I do not "rely" on MS docs entirely. I am fully aware of my responsibilities to my customers to know the facts in cases, many times through the scars of hard-fought trench battles. I was merely pointing out to you the incongruity of your assertions. On the one hand, you say the issue results from the mechanism which generates IDENTITY values in an "expedient" manner, yet on the other hand you scream that you did not say that very thing. Let us point out the real cause of the mis-ordering of Departments, which is actually the manner in which the data engine handles the ORDER BY during the INSERT.

    YES, the order of the Departments may be spurious, but this is because of the manner in which the storage engine CHOOSES TO INSERT THEM. This has nothing whatever to do with "generating values for the identity column however it sees fit". Nothing could describe the problem more poorly, as several other souls have pointed out to you, yet you insist on saying that you never said what I am quoting here.

    Anyhow, it's your world; I'm just a squirrel. Enjoy that. Keep believing that IDENTITY columns are generated "however the server sees fit". I'll keep signing the big contracts while you deal with compacting & repairing the Access files, er, databases, er...whatever...

    Mr. Angry DBA

     

     

  • (cs) in reply to memorex
    memorex:

    <FONT color=#0000ff>>IF</FONT> ((SELECT COUNT(*) FROM Staff_DepartmentLink) > 0)

    One of my favorite pet-peeves- asking for the precise # of records merely to find out if there are any records at all.

    On large tables in an OLTP setup, this can be just murder to performance.

    --

    And if I hear the whole "storedProcs are precompiled, thus faster" thing I'm going to scream.

    -If you can't measure the difference- it's probably not a valid thing to worry about in most cases.

     

    How can this be done in a faster way? I've been thinking of a way to Google this question, but I get loads of irrelevant answers... If you could point me in the right direction I would be thankful.

    Drak

  • (unregistered)

    Drak,

    Not to justify this WTF architecture, but I will answer your question.

    Here's an example of what should have been done. No "SELECT COUNT(*)" nonsense, no temp table, no IDENTITY controversy. 30 seconds of thought. 3 minutes of coding. I figure the n00b that wrote this owes me 7 dollars now.

    CREATE PROCEDURE GetDepartmentDropDown
    AS

    SET NOCOUNT ON

    /*Use EXISTS here, for better performance than "SELECT COUNT(*)"*/
    IF NOT EXISTS
     (
     SELECT * FROM Staff_DepartmentLink
     )
     BEGIN
      SELECT '<i>No Departments Defined</i>' AS rowsrc
     END
    ELSE
     BEGIN
      /*
      -Select from derived table which contains values to properly guarantee sort order
      -SortKey is used to define the type of data, i.e.
       1 = begin tag for <select> element
       2 = first <option> element, which is also the default selection
       3 = <option> elements for each Department
       4 = end tag for <select> element
      -DepartmentName is used as a secondary sort to ensure proper sorting of the Department names
      -rowsrc is the actual HTML code returned to the caller
      */
      SELECT
       T.rowsrc
      FROM
       (
       SELECT
        1 AS SortKey,
        '' AS DepartmentName,
        '<SELECT name="DepartmentID">' AS rowsrc
       
       UNION ALL
       
       SELECT
        2 AS SortKey,
        '' AS DepartmentName,
        '<option value="-1" SELECTED>(None Selected)</option>' AS rowsrc
       
       UNION ALL
       
       SELECT
        DISTINCT
        3 AS SortKey,
        SD.DepartmentName,
        '<option value="' + CONVERT(varchar(6), SDL.DeptID) + '">' + SD.DepartmentName + '</option>' AS rowsrc
       FROM
        StaffDepartments AS SD
        INNER JOIN Staff_DepartmentLink AS SDL ON
         SD.DepartmentID = SDL.DeptID
       
       UNION ALL

       SELECT
        4 AS SortKey,
        '' AS DepartmentName,
        '</SELECT>' AS rowsrc
       ) AS T
      ORDER BY
       T.SortKey ASC,
       T.DepartmentName ASC
     END

     

    Regards,

    Mr. Angry DBA

    (Who's your daddy now Mr. IDENTITY?)

     

  • (unregistered)

    Oh my God that's the funky shit!!! [:D]

  • (unregistered)

    Sad as it may look, this performed a hell of a lot better on the first ASP versions.  Text concatenation was just terribly slow on pre 3.0 versions.


    Although the middle tier could solve that problem :)

  • (cs) in reply to

    2Angry DBA, and Anonymous :)

    Just thought I'd mention: there was no identity column for the creation of #TEMP table in the original code... So in a way you're both right.

    No identity (no clustered index) the order of returned data is not guaranteed...

  • (cs)

    Yeah wtf... Unfortunately I've seen such code before. Even in some huge systems. DBA's should never be allowed to build a solution nor decide how to build it.... NEVER!

  • (unregistered)

    it gives new meaning to the term 'data-bound control' [:)]

    btw if I was doing a code audit would this count as HTML or SQL code ?

  • (unregistered) in reply to Drak
    Drak:
    memorex:

    <FONT color=#0000ff>>IF</FONT> ((SELECT COUNT(*) FROM Staff_DepartmentLink) > 0)

    How can this be done in a faster way?

    IF EXISTS(SELECT something, anything FROM Staff_DepartmentLink) BEGIN
  • (unregistered) in reply to

    The forum seems to be pretty broken on Safari (sorry for the above HTML) :P

  • (unregistered) in reply to SysRq2000

    Have to disagree with you on that one.  My DBA mate is currently struggling with a .NET programmer who doesn't want to use properties in any of his classes 'cos in his words "you don't have properties in Java so I don't want to use them in .NET".

    And before anyone makes a VB programmer comment, this geezer is using C#

     

  • (cs) in reply to

    Thanks Mr Angry DBA and 'Mr safari [:D]'.

    I didn't know this was a faster way. Luckily I'm not a DBA, but I'm glad I learned something today.

    By the way: Properties ARE quite slow sometimes in .Net, so if pure performance is what you need I suggest not using them. But for readability and for ease of use of libraries by other people they are what you want.

  • (unregistered) in reply to Drak
    Drak:

    By the way: Properties ARE quite slow sometimes in .Net, so if pure performance is what you need I suggest not using them. But for readability and for ease of use of libraries by other people they are what you want.

    Are they? They get compiled to a get_property() and set_property() method, isn't that how it's done by default in Java? So why are they slow? I seriously don't know?

     

     

Leave a comment on “Logical Tiers? That Makes No Sense! ”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article