Forum Replies Created

Viewing 15 posts - 61 through 75 (of 7,612 total)

  • Reply To: Timeout Error while executing a TSQL statement in Sql server Express Edition

    (1) If you have a lot of dbs, make the $IDENTITY column a primary key in the table, so you have a seek instead of a scan

    (2) DBCC CHECKDB can...

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

    December 21, 2024 at 8:09 am

    #4502790

  • Reply To: Moving DB to new drive

    1. No
    2. No

    And, that is the correct method for moving dbs to a new drive.

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

    December 17, 2024 at 7:34 pm

    #4499775

  • Reply To: how to generalize the LAG function so it keeps looking until a value is found?

    IF you're on SQL 2022, you can add "IGNORE NULLS" to the LAG function.

    Otherwise you can use an OUTER APPLY with a SELECT TOP (1).  For example:

    SELECT...

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

    December 16, 2024 at 3:31 pm

    #4499116

  • Reply To: Query is filling Tempdb

    First, you need to correct the query to use the alias in the UPDATE, not the original table name.  Other things to consider if still having problems:

    (1) How large 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".

    December 11, 2024 at 6:51 pm

    #4496810

  • Reply To: Trigger After Update to Update the Updated Table

    AFTER UPDATE is the more current syntax, so I would go with that.

    I would also (1) take advantage of the UPDATE() function and (2) allow the trigger to properly set...

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

    December 9, 2024 at 3:42 pm

    #4495528

  • Reply To: Which selection criteria is more efficient.

    It depends.  You'd need to look at the available indexes and whether SQL uses them for the query.  So, look at the query plan and see what it "tells" 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".

    December 9, 2024 at 3:21 pm

    #4495514

  • Reply To: NC index creation duration question

    To reduce space usage in that db, you can specify SORT_IN_TEMPDB = ON in the index create.

    Also, to save time, pre-allocate additional log space, if needed.  If you must get...

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

    December 9, 2024 at 3:18 pm

    #4495513

  • Reply To: Group Delete

    The CostKeyDeletes tables should be clustered on CostKey, not on ID.  It's best for it to match the original table's key.  You can keep the ID, if you want, just...

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

    December 6, 2024 at 7:19 pm

    #4494310

  • Reply To: sql query to check item

    Why are you re-posting the same q?

    https://www.sqlservercentral.com/forums/topic/sql-query-to-check-the-status-change-for-an-item#post-4492292

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

    December 2, 2024 at 7:25 pm

    #4492293

  • Reply To: Tracing a performance issue

    The SELECT INTOs will hold locks.  Those might be causing issues.

    One good way to avoid that is to an initial SELECT TOP (0) ... INTO to create the table, then...

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

    December 2, 2024 at 6:41 pm

    #4492285

  • Reply To: Simple Junction table design question

    Wow, that's exceptional.  I've never been at a place that had, say an "order" table vs. an "orders" 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".

    November 29, 2024 at 5:42 am

    #4490686

  • Reply To: Sql query to check change for an item

    I wasn't sure StatusDate included a time.  If it's just a date, it's not safe to ORDER BY it.

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

    November 27, 2024 at 3:04 pm

    #4489938

  • Reply To: Simple Junction table design question

    I believe the more common, and better, naming for tables is plural.  I can't think of a major relational dbms that uses singular system table names.

    For example: sys.objects (SQL Server),...

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

    November 27, 2024 at 2:52 pm

    #4489926

  • Reply To: Simple Junction table design question

    Yep, definitely remove ID.  The key to a "relationship" table should be the relationship keys, NOT an ID column.

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

    November 26, 2024 at 10:09 pm

    #4489569

  • Reply To: Sql query to check change for an item 

    ;WITH ctePrevPrStatus AS (
        SELECT *, LAG(PrStatus) OVER(PARTITION BY PrID ORDER BY StatusID) AS PrPrevStatus
        FROM dbo.tblPresentationStatus
    )
    SELECT *
    FROM ctePrevPrStatus
    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".

    November 26, 2024 at 7:26 pm

    #4489505

Viewing 15 posts - 61 through 75 (of 7,612 total)