Forum Replies Created

Viewing 15 posts - 166 through 180 (of 7,626 total)

  • Reply To: How To Approach Adding A primary Key To An Existing Table

    My main advice would also be not to modify the table structure unless you really need to (I deliberately put the code in comments that (if you wanted to) changed...

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

    July 24, 2024 at 9:37 pm

    #4439667

  • Reply To: How To Approach Adding A primary Key To An Existing Table

    It's nice that there's already an index on dDateIn. I would suggest something like this:

    --Setup

    (A1) Create a new table, with a usable clustered index. Add data compression to 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".

    July 24, 2024 at 7:26 pm

    #4439650

  • Reply To: How To Approach Adding A primary Key To An Existing Table

    If the table is just a heap (you said there's no PK, but that doesn't necessarily mean there's no clustered index), as you know, you'll have to rewrite the whole...

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

    July 24, 2024 at 4:02 pm

    #4439593

  • Reply To: Deadlocks with UPDATE statements using serializable transaction isolation level

    I think if you do the UPDATE and lookup all in one go, you won't have dups nor deadlocks:

    ...

    UPDATE dbo.Reference_Numbers
    SET @NextReferenceNo = NexNextReferenceNo = NextReferenceNo + 1...

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

    July 24, 2024 at 3:25 pm

    #4439578

  • Reply To: Sessions and CPU Threads

    No.  If they're sleeping, and don't have any active tasks, they are not using up CPU.  They are taking a small amount of memory.

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

    July 16, 2024 at 5:35 pm

    #4436770

  • Reply To: UPSERT question for performance and efficiency

    I've generally found MERGE to be less efficient than UPSERT.  Others maybe not.

    You could generate the necessary WHERE clauses and CASE clauses to conditionally UPDATE 200 columns.

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

    July 11, 2024 at 10:09 pm

    #4435383

  • Reply To: Order By on a single table raises Ambiguous column name error

    Because * SELECTs all columns, which would include hMy.  Try this instead:

    SELECT hMy, *
    FROM dbo.WF_HEADER
    WHERE 1=1
    ORDER BY 1 --<<--

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

    July 8, 2024 at 9:47 pm

    #4434331

  • Reply To: SSRS report Query Optimization

    For one thing, don't force only LOOP joins, YIKES!

    To tell you anything else, would need much more details on the tables and parameters.

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

    July 8, 2024 at 8:20 pm

    #4434308

  • Reply To: Split fields sql 2017

    The data seemed inconsistent / "impossible" to me.  How can the left-most 50 bytes be '3006' when that doesn't appear in the original value at all??

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

    July 3, 2024 at 9:46 pm

    #4433281

  • Reply To: How to improve index rebuild for very large table.

    Don't do online, do offline.  That should be ok if you have scheduled down time anyway.

    Otherwise, hard to say without more details.  As stated above, you may not really need...

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

    July 2, 2024 at 1:45 pm

    #4432963

  • Reply To: Logic similar to EXIST to make all values the same if it meets a criteria 

    SELECT DISTINCT UPC, STARTDATE, [MASTERCHAINNAME], REPLENTYPE,
    CASE WHEN [REPLENTYPE] IS NULL THEN 'Not in CKB' 
    WHEN [REPLENTYPE] = 'NONE' THEN 'Non Replenishable'
    ELSE 'Replenishable' END AS 'Replentype'
    FROM dbo.tablename t1
    WHERE...

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

    July 1, 2024 at 9:58 pm

    #4432821

  • Reply To: Help with pivot table query with a where clause

    Assuming that AVALUE is based on the ID value (that is, the same ID always has the same AVALUE), then you can leave AVALUE out of the GROUP BY.  If...

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

    June 28, 2024 at 5:50 pm

    #4432228

  • Reply To: How To Work-A-Round Not being able too use CASE within ORDER BY

    Or take advantage of SQL's inherent capabilities by using the sql_variant data type (yes, technically you don't need the second CAST):

    SELECT DISTINCT 
        ...

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

    June 27, 2024 at 1:56 pm

    #4431889

  • Reply To: Flexable/Effective means to join the Nth record

    The above general approach should work, there just may be a more efficient way that's not popping into my head right now.

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

    June 21, 2024 at 8:44 pm

    #4430643

  • Reply To: Flexable/Effective means to join the Nth record

    We could brute force it; I'll try to think of a better way when I get a chance:

    ;WITH cte_person AS (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY...

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

    June 21, 2024 at 7:05 pm

    #4430629

Viewing 15 posts - 166 through 180 (of 7,626 total)