Forum Replies Created

Viewing 15 posts - 2,296 through 2,310 (of 7,613 total)

  • Reply To: How to prioritize the phone nos values in three columns.

    SELECT CustID, COALESCE(LandPhone, MobilePhone, OfficePhone) AS Phone

    FROM PHONENOS

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

    September 11, 2020 at 3:37 am

    #3787773

  • Reply To: First Monday of the Month

    Such insanely bloated calendar tables have actually become much closer to the norm.

    It's terrible.  Create a nonwork days table with only the dates in it (you could also add a...

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

    September 11, 2020 at 2:20 am

    #3787770

  • Reply To: Complex Join

    Jeff Moden wrote:

    ScottPletcher wrote:

    And, if forced to work on that SQL statement, I'd still remove the use of ISNULL, no matter what the comments stated.

    So like I asked before... what would...

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

    September 11, 2020 at 2:15 am

    #3787769

  • Reply To: Complex Join

    Jeff Moden wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    While I usually and strongly agree that ISNULL shouldn't be used on table columns in a Join, Where, or other column based criteria matching, "It Depends" is...

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

    September 10, 2020 at 9:37 pm

    #3787752

  • Reply To: Complex Join

    Jeff Moden wrote:

    While I usually and strongly agree that ISNULL shouldn't be used on table columns in a Join, Where, or other column based criteria matching, "It Depends" is always a...

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

    September 10, 2020 at 8:03 pm

    #3787718

  • Reply To: Complex Join

    Jeff Moden wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    Sergiy wrote:

    Still there is a shorter version:

    select * 
    from leftTable  T1
    join rightTable T2 
       on (
         ...

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

    September 10, 2020 at 4:17 pm

    #3787608

  • Reply To: Formatting dates returned in query

    SELECT CONVERT(varchar(10), I.InvoiceDate, 110) AS InvoiceDate

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

    September 9, 2020 at 8:03 pm

    #3787410

  • Reply To: Complex Join

    Jeff Moden wrote:

    Sergiy wrote:

    Still there is a shorter version:

    select * 
    from leftTable  T1
    join rightTable T2 
       on (
          T1.C1 =...

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

    September 9, 2020 at 7:42 pm

    #3787407

  • Reply To: Complex Join

    You never want to use ISNULL() in a WHERE or JOIN clause.  The code may be slightly longer the other way, but it could potentially run much faster.

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

    September 9, 2020 at 7:25 pm

    #3787403

  • Reply To: how to change new schema name from existing schema - Table

    ALTER SCHEMA [dbo] TRANSFER HWData.DesktopSoft_Master;

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

    September 8, 2020 at 3:50 pm

    #3787031

  • Reply To: Complex Join

    Then your original JOIN is fine and likely the cleanest way to do the join.

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

    September 5, 2020 at 5:34 am

    #3786557

  • Reply To: Complex Join

    So you want a NULL in the column to match *every* column on the other table that isn't NULL?  I.e., NULL is like a wildcard match?

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

    September 4, 2020 at 10:31 pm

    #3786512

  • Reply To: Complex Join

    I would think you'd want this:

    T1 join T2

    on ((T1.C1 = T2.C1) OR (T1.C1 IS NULL AND T2.C1 IS NULL))

    and ((T1.C2 = T2.C2) OR (T1.C2 IS NULL AND T2.C2 IS NULL))

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

    September 4, 2020 at 8:51 pm

    #3786494

  • Reply To: allocate / deallocate table

    Cebisa wrote:

    How critical is your application?

    You will have an outage between steps 2 and 3 and if the table has foreign key constraints you cannot use truncate table

    Don't forget to...

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

    September 3, 2020 at 9:14 pm

    #3786136

  • Reply To: allocate / deallocate table

    Steve Jones - SSC Editor wrote:

    As Jeff noted, include TRUNCATE in here.

    I did include TRUNCATE in the trans, from the start.  Not exactly sure how else you want the transaction structured.

     

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

    September 3, 2020 at 8:46 pm

    #3786131

Viewing 15 posts - 2,296 through 2,310 (of 7,613 total)