CREATE statement

  • sgehret 2557

    Valued Member

    Points: 66

    Hi,

    It's official. I'm an idiot. I have my local SQL Server setup to create data files on my D drive due to space issues. But I connected to a database on another server to test this question. Big duh! on my part. When I finally figured it out and ran it on my Server and saw the correct results.

    Sorry for any inconvenience. Thanks for your information.

    Take care,

    Scott

  • chgn01

    Hall of Fame

    Points: 3530

    Did I am misleading for the answer? Why so many answers are wrong?

    How other members answeredCorrect answers: 55% (374)

    Incorrect answers: 45% (302)

    Total attempts: 676

    --------------------------------------------------------------------------------

    Query #1 12%

    --------------------------------------------------------------------------------

    Query #2 14%

    --------------------------------------------------------------------------------

    Query #3 7%

    --------------------------------------------------------------------------------

    Query #4 71%

    --------------------------------------------------------------------------------

    Query #5 26%

    --------------------------------------------------------------------------------

    Query #6 69%

    Total scores and standings

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • Miles Neale

    SSChampion

    Points: 13147

    chgn01 (3/8/2013)


    Did I am misleading for the answer? Why so many answers are wrong?

    How other members answeredCorrect answers: 55% (374)

    Incorrect answers: 45% (302)

    Total attempts: 676

    The QotD is for learning and a daily challenge to make us think. What you gave was an opportunity for both. The vast majority of those answering the question did not post. Those who did post are those who like to have a voice in the process or think something is to be gained by posting. But again most of the folks read the question got it right or wrong and then went about their normal day.

    Was it misleading? Hey, it was a question, and it got us thinking and talking, and thank you for that.

    M.

    Not all gray hairs are Dinosaurs!

  • TomThomson

    SSC Guru

    Points: 104772

    Miles Neale (3/8/2013)


    L' Eomot Inversé (3/8/2013)


    Revenant (3/7/2013)


    I am nominating this for the Most Misguided QotD of the Year Award.

    I think it's a poor fifth in that race. 😎

    Tom,

    This being early March, your comment says volumes about the QotD so far this year.

    The thing I must say about all those questions is that each has given the community a great platform for discussion about topics and uses that might not have taken place without the poor questions.

    M.

    It's important to remember that it's sometimes the misguided questions that give rise to the best discussions - in particular, misguided question will often result in a really excellent educational post from Hugo, and I don't imagine that I'm the only person who has benefited from his comments.

    Tom

  • TomThomson

    SSC Guru

    Points: 104772

    chgn01 (3/8/2013)


    Did I am misleading for the answer?

    NO, you are not misleading in any way, the only thing genuinely wrong with the question is using OBJECTID when DBID would have been better - if that was intended as obfuscation then it was horribly wrong, but if - as seems more likely - it was just a mstake then because it was harmless in context it was acceptable, not a problem.

    Why so many answers are wrong?

    I don't dare to make an assertion as to why as mant as 45% of people answering were wrong, but I will admit to suffering from a suspicion that close to half of DBAs haven't a clue about writing SQL - they understand some of the comand line utilities and the things they can do by using the menus or right clicking in the obje ct explorer window of SSMS but T-SQL is beyond their comprehension, because it's a programming language and they know nothing of programming.

    One of the good things about questions like yours is that it may teach these people a little about T-SQL. So thank you very much for the question.

    Tom

  • amit_adarsh

    Ten Centuries

    Points: 1273

    Thanks for Good observation/question 😎

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Hmmm, not sure why some people are so critical about this question. Okay, there is an issue with OBJECT_ID instead of DB_ID in query 2, but other than that the question looks okay to me.

    I am disappointed to see the advice to enclose CREATE PROC in EXEC, making it dynamic. That is really horrible. You lose colorcoding of keywords, you lose intellisense syntax checking (and intellisense autocomplete, for those who have that feature enabled), and you can very easily make mistakes with doubling all the single quotemarks.

    Here are the recommended patterns:

    1. Create proc if it doesn't exist, or change it if it does

    IF OBJECT_ID('dbo.procname, 'P') IS NOT NULL

    DROP PROC dbo.procname;

    GO

    CREATE PROC dbo.procname

    (@Parm1 int, ...)

    AS

    -- code goes here

    GO

    2. Same as above, but use ALTER instead of DROP/CREATE, so that permissions are retained

    IF OBJECT_ID('dbo.procname, 'P') IS NULL

    EXEC (N'CREATE PROC dbo.procname AS SELECT 1;'); -- Create dummy procedure to be altered in the next step

    GO

    ALTER PROC dbo.procname

    (@Parm1 int, ...)

    AS

    -- code goes here

    GO

    (You could even have only the dummy CREATE PROC in the first batch and ignore the error message)

    3. To mimic the QotD task: create if it doesn't exist, leave unchanged otherwise (though I fail to see a real-world use case for this):

    IF OBJECT_ID('dbo.procname, 'P') IS NOT NULL

    RAISERROR ('procedure already exists, aborting', 20, 1) WITH LOG;

    GO

    CREATE PROC dbo.procname

    (@Parm1 int, ...)

    AS

    -- code goes here

    GO

    The severity of 20 in the raiserror forces SQL Server to drop the connection, so that the next batch is not executed. The WITH LOG is required for errors of that severity.

    As an alternative, you could simply execute only the second batch and ignore the error message you get if the procedure already exists.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • TomThomson

    SSC Guru

    Points: 104772

    Hugo Kornelis (3/11/2013)


    Hmmm, not sure why some people are so critical about this question. Okay, there is an issue with OBJECT_ID instead of DB_ID in query 2, but other than that the question looks okay to me.

    Even that is really a non-issue, because it makes no difference in context (the database was dropped in query 1, so it changing OBJECT_ID to DB_ID leaves still gets NULL). My guess is that those who are complaining are complaining because they got it wrong. It looks to me like a good question, with a good answer.

    I am disappointed to see the advice to enclose CREATE PROC in EXEC, making it dynamic. That is really horrible. You lose colorcoding of keywords, you lose intellisense syntax checking (and intellisense autocomplete, for those who have that feature enabled), and you can very easily make mistakes with doubling all the single quotemarks.

    Me too. I didn't comment on it only because I didn't think I would avoid being offensive if I did. :ermm:

    Tom

  • Sathishk

    Mr or Mrs. 500

    Points: 598

    Miles Neale (3/8/2013)


    chgn01 (3/8/2013)


    Did I am misleading for the answer? Why so many answers are wrong?

    How other members answeredCorrect answers: 55% (374)

    Incorrect answers: 45% (302)

    Total attempts: 676

    The QotD is for learning and a daily challenge to make us think. What you gave was an opportunity for both. The vast majority of those answering the question did not post. Those who did post are those who like to have a voice in the process or think something is to be gained by posting. But again most of the folks read the question got it right or wrong and then went about their normal day.

    Was it misleading? Hey, it was a question, and it got us thinking and talking, and thank you for that.

    M.

    +1

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    great question...

    different one..

  • sqlnaive

    SSCoach

    Points: 17435

    Definitely different question.

  • Arno Kwetters

    Hall of Fame

    Points: 3335

    That explanation is loud and clear.

Viewing 12 posts - 31 through 42 (of 42 total)

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