Forum Replies Created

Viewing 15 posts - 1,261 through 1,275 (of 7,613 total)

  • Reply To: Using (or not) query HINTS to speed up slow statement

    #5 is completely wrong approach.  You should never use ISNULL() in a WHERE clause.  Instead move the condition from the WHERE clause to the LEFT JOIN.  You need to that...

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

    September 24, 2021 at 3:27 pm

    #3932769

  • Reply To: Using (or not) query HINTS to speed up slow statement

    cmartel 20772 wrote:

    ... All required indexed are defined ...

    Can't just take your word for that, still need to see DDL for the tables, including all index definitions.

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

    September 24, 2021 at 2:58 pm

    #3932754

  • Reply To: How show only first friday of every month

    Steve Collins wrote:

    You ran a comparison?

    Would we need to?  A single math calc vs iterating thru string generations and comparisons?

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

    September 24, 2021 at 4:11 am

    #3932401

  • Reply To: How show only first friday of every month

    Jeff Moden wrote:

    ScottPletcher wrote:

    Sure, it's above; I posted it years ago, when this q was first asked.

    Actually I should adjust it again, you only need to calc the very first Friday,...

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

    September 23, 2021 at 10:29 pm

    #3932357

  • Reply To: Full Recovery Database restores as Simple Recovery

    Just out of curiosity, what is the recovery model on the model db?

    I'm wondering if the db got created then restored over ?!

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

    September 22, 2021 at 9:50 pm

    #3932000

  • Reply To: Get Particular Date from a Month

    You don't need to iterate/loop thru values looking for 'Tuesday', and personally I wouldn't.  I find all such code much harder to adjust later, and generally less efficient.

    I guess you...

    • This reply was modified 4 years, 9 months ago by ScottPletcher. Reason: Adjusted code to add variable for relative week# within month

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

    September 22, 2021 at 8:30 pm

    #3931990

  • Reply To: Reducing Temp Table Scans when joining two temp tables using OR conditions

    Jeff Moden wrote:

    ScottPletcher wrote:

    https://techcommunity.microsoft.com/t5/sql-server/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in/ba-p/385537

    Table IndexesRows in table   Hints               With or Without TF...

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

    September 22, 2021 at 5:12 am

    #3931716

  • Reply To: Reducing Temp Table Scans when joining two temp tables using OR conditions

    https://techcommunity.microsoft.com/t5/sql-server/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in/ba-p/385537

    Table IndexesRows in table   Hints               With or Without TF 610 ...

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

    September 22, 2021 at 4:46 am

    #3931712

  • Reply To: How show only first friday of every month

    Sure, it's above; I posted it years ago, when this q was first asked.

    Actually I should adjust it again, you only need to calc the very first Friday, from 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".

    September 21, 2021 at 10:55 pm

    #3931649

  • Reply To: How show only first friday of every month

    Jeff Moden wrote:

    Steve Collins wrote:

    Fwiw in 2021.  In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month.  Then in the 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".

    September 21, 2021 at 10:47 pm

    #3931647

  • Reply To: Issue with the format of calculation

    I believe the calc should be:

    CAST(((f.TOTFCST - f.TOTHIST) * 100.00) / f.TOTHIST AS decimal(5, 2))

    I have no idea at all why you are subtracting 1 from the quotient in your...

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

    September 21, 2021 at 10:43 pm

    #3931645

  • Reply To: How show only first friday of every month

    Steve Collins wrote:

    Fwiw in 2021.  In the FROM clause use dbo.fnTally and CROSS APPLY (both twice) to hierarchically generate the first 7 days of each month.  Then in the WHERE clause...

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

    September 21, 2021 at 6:48 pm

    #3931595

  • Reply To: Reducing Temp Table Scans when joining two temp tables using OR conditions

    Jeff Moden wrote:

    If you add the clustering before you add the data, make sure that you use WITH(TABLOCK) on the INSERT INTO statement to help take advantage of "Minimal Logging", which...

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

    September 21, 2021 at 6:47 pm

    #3931594

  • Reply To: Reducing Temp Table Scans when joining two temp tables using OR conditions

    You need to cluster tables #SmallerRange and #LargerRange on the the JOIN columns: ( VID, IorO, OtherLocationID ).  If those 3 column values are unique in one/both of the tables,...

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

    September 21, 2021 at 6:21 pm

    #3931587

  • Reply To: Find all SPs, FNs, and VWs where particular string occurs

    SELECT OBJECT_NAME(object_id) AS object_name, OBJECTPROPERTYEX(object_id, 'BaseType') AS object_type

    FROM sys.sql_modules

    WHERE definition LIKE N'%<string_you''re_looking_for>%'

    ORDER BY 2, 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".

    September 20, 2021 at 12:41 am

    #3931014

Viewing 15 posts - 1,261 through 1,275 (of 7,613 total)