Forum Replies Created

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

  • Reply To: Moving DB to new drive

    More technically, you need permissions for whatever account SQL Server is running under.

    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 2, 2025 at 6:42 pm

    #4509055

  • Reply To: Using a RETURN

    stevec883 wrote:

    ... How does the @SINGLE_RETURN cause the messages to accumulate? ...

    Steve

    It doesn't.  It's the "+=" that causes the msgs to "accumulate" (concatenate).

    For example, if you do this:

    DECLARE @msg varchar(max);

    DECLARE...

    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 1, 2025 at 11:16 pm

    #4508577

  • Reply To: need help with assigning a value to a variable

    You can check for NULL as well in either format:

    DECLARE @LATEST_DATE date;

    SELECT @LATEST_DATE = ISNULL(MAX(MY_DATE), ...) FROM DBO.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".

    December 27, 2024 at 3:06 pm

    #4505824

  • Reply To: Testing whether TRY_CONVERT is losing precision

    I think you'll have to check for that yourself, maybe something like this:

    SELECT CASE WHEN TRY_CONVERT(DECIMAL(9,6),'6.125') <> TRY_CONVERT(DECIMAL(5,2),'6.125') THEN 'Loss of precision error' ELSE 'Good' END

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

    December 26, 2024 at 10:29 pm

    #4505364

  • Reply To: need help with assigning a value to a variable

    Either below; the first  is more typical:

    DECLARE @LATEST_DATE DATE;

    SELECT @LATEST_DATE = MAX(MY_DATE) FROM DBO.TABLE

    --or:

    SET @LATEST_DATE = (SELECT MAX(MY_DATE) FROM DBO.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".

    December 25, 2024 at 7:36 pm

    #4504752

  • Reply To: Vendor claims unused table space impacts performance

    It depends.  If the unused space is significant and is embedded in pages that are being read, i.e. contain live data, then it could slow down access (somewhat) because 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".

    December 23, 2024 at 7:17 pm

    #4503754

  • 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

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