Forum Replies Created

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

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

    You should be able to script this out.  Make sure the "Script indexes" option is on.  Then Script the table; that should give you DDL for the table and all...

    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 5:57 pm

    #3932839

  • Reply To: Have a total by Entity instead of Line total

    OK.  You said "instead of" so I figured you didn't want the other details in the new query.

    Maybe try:

    GROUP BY entity,groupname,description,effdate WITH ROLLUP

    You can get rid of total lines 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".

    September 24, 2021 at 5:55 pm

    #3932838

  • Reply To: Have a total by Entity instead of Line total

    Not 100% sure what you need, but probably:

    SELECT  entity
            ,description
            ,[Total] =...

    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 5:32 pm

    #3932825

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

    Do you have an index on:

    CorporationHistory ( CorporationID, EffectiveDate )?

    If not, you need one.

    I specialize in tuning SQL Server.  It's easy to overlook indexes if you don't have a lot...

    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 5:23 pm

    #3932819

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

    Jonathan AC Roberts wrote:

    Jeff Moden wrote:

    I'd be real careful with that.  It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations.  I...

    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:04 pm

    #3932797

  • Reply To: Wait type LATCH_EX [NESTING_TRANSACTION_FULL] - and blocking.

    Create all temp tables separately from loading them.  Bonus: you could then also properly cluster the temp tables before loading them.

    The direct load of table with SELECT ... INTO #temp...

    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:49 pm

    #3932779

  • 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, 11 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

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