Forum Replies Created

Viewing 15 posts - 541 through 555 (of 7,619 total)

  • Reply To: PIVOT, is there a better way to do this? 

    SELECT 
        ID,
        MAX(CASE WHEN Referrence = 'OrderNo' THEN Value END) AS OrderNo,
        MAX(CASE WHEN Referrence = 'OrderDate'...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 13, 2023 at 8:29 pm

    #4149213

  • Reply To: PIVOT, is there a better way to do this?

    Here's the "standard" cross-tab method:

    SELECT 
        ID,
        MAX(CASE WHEN Referrence LIKE 'ON%' THEN Referrence END) AS OrderNo,
        MAX(CASE...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 13, 2023 at 6:59 pm

    #4149182

  • Reply To: "ID" primary key from two separate tables as a foreign key in a new table

    RonKyle wrote:

    The use of the prefixed "TBL_" is a design error so bad it has a name; it's called the tibble.

    This is not a design error.  It does not violate...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 8, 2023 at 8:18 pm

    #4147262

  • Reply To: Are the posted questions getting worse?

    I use

    ;WITH

    just to help prevent possible errors.  Sure, I could be technically correct and blame someone else when there was a problem with the code, but I prefer to avoid...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 8, 2023 at 3:20 pm

    #4147140

  • Reply To: "ID" primary key from two separate tables as a foreign key in a new table

    Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    RonKyle wrote:

    I would not prefix a view with tbl.  In any case I rarely use views in an OLTP environment.  I do make extensive use of them in...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 8, 2023 at 3:17 pm

    #4147137

  • Reply To: "ID" primary key from two separate tables as a foreign key in a new table

    RonKyle wrote:

    I would not prefix a view with tbl.  In any case I rarely use views in an OLTP environment.  I do make extensive use of them in my OLAP...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 8, 2023 at 2:57 pm

    #4147085

  • Reply To: To Drop or Not to Drop (the temp table question)

    Brandie Tarvin wrote:

    ScottPletcher wrote:

    I believe that SQL renames temp tables rather than dropping them, esp. for stored procs.  That way, when the proc runs again, it doesn't have to fully recreate...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 7, 2023 at 2:54 pm

    #4146585

  • Reply To: To Drop or Not to Drop (the temp table question)

    I believe that SQL renames temp tables rather than dropping them, esp. for stored procs.  That way, when the proc runs again, it doesn't have to fully recreate the table...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 7, 2023 at 2:35 pm

    #4146581

  • Reply To: "ID" primary key from two separate tables as a foreign key in a new table

    jcelko212 32090 wrote:

    >> The professorID is likely both the natural ID for the professor and the identity of the row. <<

    The professor_id is probably a tax identification number of some kind....

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 6, 2023 at 8:10 pm

    #4146242

  • Reply To: Group by issue

    Once again, unusable data.  We need CREATE TABLE and INSERT statement(s), not a picture/"splat" of data on a screen.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 6, 2023 at 3:57 pm

    #4146153

  • Reply To: Replacement for char and ascii functions in natively compiled stored procedures

    So go ahead and do it.  Not sure specifically how a tally table is going to help here.  Besides, a tight loop should likely perform well enough on a string...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 5, 2023 at 3:46 am

    #4145645

  • Reply To: Replacement for char and ascii functions in natively compiled stored procedures

    I think this code follows all the restrictions for natively compiled procs:

    declare @byte tinyint;
    declare @bad_chars char(33);
    declare @new_string varchar(255);
    declare @string varchar(255);
    declare @string_len tinyint;
    declare @string_replacement_char char(1);

    set @bad_chars = '['...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 5, 2023 at 2:41 am

    #4145631

  • Reply To: "ID" primary key from two separate tables as a foreign key in a new table

    CreateIndexNonclustered wrote:

    I agree that you should only store attributes as numeric types if you plan on doing math on them, but if you are using only surrogate keys as you...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 3, 2023 at 7:09 pm

    #4145236

  • Reply To: "ID" primary key from two separate tables as a foreign key in a new table

    I agree that the model is not technically valid.  But I take it this is more a class / fictional "design" than a real-world implementation.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 2, 2023 at 8:29 pm

    #4144867

  • Reply To: "ID" primary key from two separate tables as a foreign key in a new table

    For many practical reasons, just use an int like the rest of the world does.  Besides which, the SQL DDL Celko gave above: "allow stuff like "sauerkraut" to be a[n...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    February 2, 2023 at 8:28 pm

    #4144866

Viewing 15 posts - 541 through 555 (of 7,619 total)