Forum Replies Created

Viewing 15 posts - 391 through 405 (of 7,619 total)

  • Reply To: Searching in date field provides inconsistent results

    Once you get rid of the LOWER on the vehicle type, then the clustering key could be ( VehicleType, ValidFrom, ValidUntil ).

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

    August 24, 2023 at 6:24 pm

    #4270346

  • Reply To: Searching in date field provides inconsistent results

    For best performance, you want to stop using NULL so you don't have to do ISNULL() as part of the WHERE.  Yes, you will have to go back and change...

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

    August 24, 2023 at 6:14 pm

    #4270344

  • Reply To: How to get max date for each column in SQL Server 

    SELECT LEFT(devicename, month_in_date - 2) AS device, MAX(CAST(devicedate AS datetime)) AS max_device_date
    FROM #test1
    CROSS APPLY (
        SELECT PATINDEX('%[0-9]/%', devicename) AS month_in_date_prelim
    ) AS ca1
    CROSS APPLY (
    ...

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

    August 23, 2023 at 5:45 pm

    #4269356

  • Reply To: Extract value before one symbol and stop after another one in SQL

    I think CHARINDEXes might be somewhat more efficient here:

    ;WITH data AS (
        SELECT rl_event_id = 'AB123456_BlahBlah_BESTSELLER_blahBlah'
    )
    SELECT rl_event_id, SUBSTRING(rl_event_id, pos_of_second_underscore + 1, pos_of_third_underscore - pos_of_second_underscore...

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

    August 23, 2023 at 3:05 pm

    #4269221

  • Reply To: question related to disk space on sql server drives

    The db that table was in already had space allocated but that had not been used yet.  Therefore, SQL can allocate that space to a table without having to 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".

    August 22, 2023 at 7:54 pm

    #4268374

  • Reply To: Server memory question

    RAM doesn't look too short there, but personally I'd add more if you could.  RAM is the cheapest overall performance boost you can get.  And bump up tempdb size 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".

    August 22, 2023 at 2:53 pm

    #4268215

  • Reply To: TSQL Question

    dup, removed

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

    August 22, 2023 at 2:49 pm

    #4268212

  • Reply To: TSQL Question 

    Update T --<<-- *must* use T here (the alias and NOT the table name) to be safe
    SET ACTIVE=CASE WHEN S.user IS NULL THEN 'N' ELSE 'Y' END
    FROM...

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

    August 22, 2023 at 2:47 pm

    #4268210

  • Reply To: Performance related issue in the report after adding new table in the sp .

    (1) Cluster the "FF" table on ( FileDate, ISIN ) (if it's not already clustered that way).

    (2) Some of the JOIN conditions are out of place.  Change this code:

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

    August 22, 2023 at 2:41 pm

    #4268207

  • Reply To: Server memory question

    Not enough info provided for us to be able to say.  (1) Is SQL indicating that it's short of memory?  (2) How large are  your databases? (3) How many simultaneous...

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

    August 21, 2023 at 5:42 pm

    #4267364

  • Reply To: Multicolumn indexes and fragmentation

    It's likely the fragmentation is having some effect on overall performance by causing some small additional I/O.

    To clarify, for a single keyed lookup, the index performance is not affected at...

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

    August 18, 2023 at 2:48 pm

    #4264679

  • Reply To: Join on Column A when Column B is Null

    Select * from Table T1

    JOIN TableT2 on (T1.A IS NOT NULL AND T1.A =T2.A) OR (T1.A IS NULL AND T1.B = T2.B)

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

    August 15, 2023 at 3:02 pm

    #4261837

  • Reply To: NVARCHAR(MAX) to VARCHAR(MAX)

    Super Cat wrote:

    Also how do I check if the table has had any options set for SP_TABLEOPTIONS such as 'LARGE VALUE TYPES OUT OF ROW'. I doubt this would be the...

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

    August 10, 2023 at 2:48 pm

    #4257848

  • Reply To: NVARCHAR(MAX) to VARCHAR(MAX)

    When you ALTER a column, SQL creates a new column to store the data, it doesn't re-use the existing space for the new column.  You'll need to do some type...

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

    August 9, 2023 at 2:49 pm

    #4256882

  • Reply To: Run Stored Procedure on the first Monday if each month of Tues if Mon Holiday

    Jeff Moden wrote:

    ScottPletcher wrote:

    My other feeling was that having it run multiple times (2, 3, 4?) at the start of the month could be deceptive / confusing to people looking at...

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

    August 6, 2023 at 9:45 pm

    #4254367

    • Viewing 15 posts - 391 through 405 (of 7,619 total)