Forum Replies Created

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

  • 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

  • Reply To: Dimensional table and Stored Procedure

    I've added comments to your code below.  Some of the code is invalid and/or doesn't make sense.

    yrstruly wrote:

    create procedure AppendAccountCommissionHistory
    as
    begin
      begin
        insert into...

    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 6:02 pm

    #4144817

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

    You could create a combined table.  I assume (perhaps wrongly, if so, correct me) that you will have fewer than 10 professors in the table and therefore you intend that...

    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 3:15 pm

    #4144765

  • Reply To: How to get number of unique months from table

    SELECT ZipCode, COUNT(DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0, UCLogTimeStamp), 0)) AS 'No. months'

    FROM #mytable

    GROUP BY ZipCode

    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".

    January 31, 2023 at 3:41 pm

    #4143767

  • Reply To: Case statement with time

    Phil Parkin wrote:

    pedroccamara wrote:

    I'm so sorry guys, wrong CASE

    (CASE WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 10 AND 16 THEN 'Lunch' WHEN CAST(DH.OPENING_HOUR_H AS INT) BETWEEN 16 AND 19 THEN 'Snack' ELSE 'Dinner'...

    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".

    January 29, 2023 at 5:20 pm

    #4143018

  • Reply To: Case statement with time

    Sorry, but a picture of data is useless to us.  Please post usable data, that is, a CREATE TABLE and INSERT statement(s).

    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".

    January 28, 2023 at 9:52 pm

    #4142876

  • Reply To: Context_Info across databases

    It should work I think.  But 0x55555 is an odd number of chars, so the value would actually get set to 0x055555.

    Try setting it to 0x555555, that should work more...

    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".

    January 27, 2023 at 3:36 pm

    #4142470

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