Forum Replies Created

Viewing 15 posts - 31 through 45 (of 7,618 total)

  • Reply To: SQL Query Performance with Left Join

    If the condition is on a LEF T JOIN, moving it to the WHERE will effectively convert the LEFT JOIN to an INNER 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".

    April 30, 2025 at 2:18 pm

    #4586022

  • Reply To: How do you keep track of the purpose for SQL Logins?

    I use a table.  (Longtime DBA, so that comes naturally to me.)

    Basically, you need a table for the passwords, anyway, right?  Of course the table's encrypted and in a restricted...

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

    April 24, 2025 at 9:45 pm

    #4583766

  • Reply To: Why would using a variable vs Hard Value impact a Query's Performance

    SQL Server can maintain statistics that "tell" it how many rows are in given ranges of key data (histogram data).  For hard-coded, SQL can of course then pick a plan...

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

    April 20, 2025 at 3:09 pm

    #4581547

  • Reply To: Find all records in a table whose values when summed up will equal another value

    My function by default returns all matches, but you have options to prioritize fewer rows matching or more rows matching.

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

    April 15, 2025 at 2:18 pm

    #4579353

  • Reply To: Find all records in a table whose values when summed up will equal another value

    I have a function that does that.  It could also return 400 and (either) 600.  You'd have to specify how to pick one over the other.

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

    April 14, 2025 at 9:39 pm

    #4578966

  • Reply To: Migration related issue

    Make sure statistics for all large tables are updated.

    Review query plans to check for potential performance issues.

     

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

    April 14, 2025 at 2:50 pm

    #4578838

  • Reply To: Replace calculation in cursor with set based logic

    A quirky update seems to work, although that requires adding a Calculation column to the original table and making sure the original table is clustered by PricingDate, viz:

    CREATE TABLE #Test...

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

    April 9, 2025 at 6:23 pm

    #4576176

  • Reply To: t-SQL help..

    I think you might have to read the table again to do what you need to do.  Probably best to just do an INNER JOIN, though:

    select  t2.ID, t2.LINEX, t1.*

    from #tmp1...

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

    April 8, 2025 at 2:58 pm

    #4575294

  • Reply To: String or Binary Truncation Error Strange Behavior (SQL 2019)

    Create a temporary table to load into, with len of 5+, and see if any rows with data longer than 3 bytes are actually getting loaded.

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

    March 27, 2025 at 9:13 pm

    #4568733

  • Reply To: Fax/Phone Area code Substring

    Because you used a numeric data type (1) rather than a string.  Do this instead:

    ...
    )
    SELECT 
    country_code,order_phone,
    case 
    when country_code IN ('US','CA') and len(order_phone) = 10 then '1' --<<--
    when...

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

    March 20, 2025 at 8:00 pm

    #4564022

  • Reply To: Server Role for User Database Access

    Add that user to the "model" db -- with whatever permissions you want -- and they will automatically be added to every new db.

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

    March 20, 2025 at 1:50 pm

    #4563821

  • Reply To: Eliminate a function by writing out the code is now throwing an error 

    SELECT TOP (1) reg
    FROM ( VALUES(@var1), (@var2) ) AS datetimes(reg)
    ORDER BY reg DESC

    This approach will be really useful if you ever have to add a 3rd (4th,...

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

    March 19, 2025 at 6:16 pm

    #4563155

  • Reply To: Search all databases on one server instance to find a specific view 

    DROP TABLE IF EXISTS #view_names;
    CREATE TABLE #view_names ( 
        view_name nvarchar(100) PRIMARY KEY
        );
    INSERT INTO #view_names
    VALUES('>--->YOUR_VIEW_NAME_TO_SEARCH_FOR_GOES_HERE<---<')
    /*, ('another_view_name_could_go_here') ... */

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

    March 17, 2025 at 2:45 pm

    #4561363

  • Reply To: cte using POWER and Numbers table causes arithmetic overflow

    Exact code works for me in SQL 2022.

    Maybe use bigint rather than int!?:

    declare @status as bigint=83457678; 
    ;with nums(num, [2power_Num], bitcheck)
    as
    (
    SELECT Num, POWER(2, CAST(Num AS bigint)), (@status &...

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

    March 13, 2025 at 6:13 pm

    #4558926

  • Reply To: Deadlocks after index rebuild

    It's also possible that you were missing critical index(es) that were forcing full scans of the table.  Every issue is not necessarily always exactly the same as one that happened...

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

    March 6, 2025 at 12:22 am

    #4552167

Viewing 15 posts - 31 through 45 (of 7,618 total)