Forum Replies Created

Viewing 15 posts - 2,071 through 2,085 (of 7,613 total)

  • Reply To: Creating indexes on a very large table takes over 5 hours

    (I originally used the wrong quote with my comments here, so I've repeated them below but with the correct quote first.)

    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, 2020 at 10:28 pm

    #3819556

  • Reply To: Perf hit for Increasing tran log frequency

    A lot of places do tran log backups every 15 minutes.

    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, 2020 at 5:55 pm

    #3819478

  • Reply To: comma issues in sql statement

    Jeff Moden wrote:

    ScottPletcher wrote:

    Jeff Moden wrote:

    ScottPletcher wrote:

    Convoluted method, don't do it.

    Only to those that think so.

    No.  The human brain is designed to recognize patterns.  Sticking with a familiar patterns drastically speeds up...

    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, 2020 at 5:28 pm

    #3819471

  • Reply To: Perf hit for Increasing tran log frequency

    1 min is just way too often.  Especially for only 1000 rows per day.  That's a miniscule load for SQL Server.

    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, 2020 at 5:05 pm

    #3819467

  • Reply To: comma issues in sql statement

    Jeff Moden wrote:

    ScottPletcher wrote:

    Convoluted method, don't do it.

    Only to those that think so.

    No.  The human brain is designed to recognize patterns.  Sticking with a familiar patterns drastically speeds up recognition of...

    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, 2020 at 4:11 pm

    #3819442

  • Reply To: Extract data with variable column names and order

    aaron.reese wrote:

    Thanks Scott, you have waaay too much time on your hands.

    That was effectivley what I was going to do but rather than create 300 views, just dump the data...

    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, 2020 at 3:19 pm

    #3819416

  • Reply To: Creating indexes on a very large table takes over 5 hours

    Jeff Moden wrote:

    @pamkagel ,

    Ok... you said that the entire table is generated nightly.  That makes me want to assume that you're not actually doing any updates or additional inserts to 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".

    December 11, 2020 at 3:09 pm

    #3819411

  • Reply To: Extract data with variable column names and order

    That is, something like this.  I had to set up directly usable data myself to verify the code, which, btw, took me longer than writing the code itself!  In 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".

    December 10, 2020 at 2:25 pm

    #3818986

  • Reply To: Extract data with variable column names and order

    I would create a view matching each AttributeType.  I think that would be the easiest to use.  Is there some reason that wouldn't work for what you need here?

    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 10, 2020 at 2:02 pm

    #3818962

  • Reply To: how to check all the delete and update rules on the foreign keys tables

    Steve Collins wrote:

    The lazy programmers' way to delete from complex data structures is to get a data comparison tool to generate the SQL DELETE transaction.  Where I work we've used 2...

    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 10, 2020 at 2:44 am

    #3818816

  • Reply To: Creating indexes on a very large table takes over 5 hours

    I'm also trying to improve overall performance, not just the load time.

    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, 2020 at 10:10 pm

    #3818758

  • Reply To: Temporal Tables - rownumber field

    Jeffrey Williams wrote:

    What do you mean by row number?  How is that column created and populated - is it really an identity column?  If it is really an identity column 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".

    December 9, 2020 at 9:56 pm

    #3818755

  • Reply To: Using a bit flag in Case statement in Where clause

    NEVER use ISNULL() in a WHERE or JOIN.

    Instead, in this specific case, use this method:

    select * from #OutputTable

    where (@SeasonalAddressFlag = 0 OR SeasonalAddressID IS NOT NULL)

    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, 2020 at 4:37 pm

    #3818671

  • Reply To: Index Column Order – Be Happy!

    where the Primary Key/Clustered Index is based on SalesOrderID (identity column)

    And that's the issue.

    If you will often query by OrderDate BETWEEN ..., then cluster the table on:

    ( OrderDate, SalesOrderID )

    in...

    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 8, 2020 at 10:49 pm

    #3818405

  • Reply To: Creating indexes on a very large table takes over 5 hours

    I'd recommend trying the approach below.

    Specifically, the code below:

    1. Changes the fillfactor to 99.  80 is way too small (unless you do massive updates to almost the entire table later?!...

    • This reply was modified 5 years, 6 months ago by ScottPletcher. Reason: Added ONLINE = OFF to list of changes I made. I forgot to mention this before, but I did make the 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".

    December 8, 2020 at 10:28 pm

    #3818395

Viewing 15 posts - 2,071 through 2,085 (of 7,613 total)