Forum Replies Created

Viewing 15 posts - 2,086 through 2,100 (of 7,619 total)

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

  • Reply To: how to get index data size?index created and updated time and etc.

    1. Not generally available. If an index was created by adding a PRIMARY KEY constraint, sys.objects would have the create time of that index.

    2. Yes. For example:

    SELECT

    t.name AS table_name, i.name...

    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 4:44 pm

    #3818267

  • Reply To: Multiple tables - View

    Something like below is what you need to do.  If the column names are the same, you will need to make sure each has a different name in the final...

    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 5:39 am

    #3818074

  • Reply To: Inflow / outflow report per day

    Why not create the clustered index on the temp table before it gets loaded?  That would save extra writing / rewriting of data, I would think.

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

    #3817980

  • Reply To: Ideas for Auditing

    Do you mean a separate row for every column that changed?  That's massive overhead.  And if you get the column name(s) dynamically, that's really insane overhead.

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

    #3817979

  • Reply To: If SQL Agent Job takes longer than @X minutes, get notified.

    Have the first step in the job start another job that monitors the starting job.

    For example, if you're trying to monitor JobA, the first step in JobA starts job JobA_monitor...

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

    #3816636

  • Reply To: Inflow / outflow report per day

    milo1981 wrote:

    I want to generate a report which will display day by day the cashflow (total invoices - total payments received).

    Wouldn't "total invoices - total payments received" be the receivable...

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

    #3816635

  • Reply To: How can I exclude records from this table?

    I assumed by "for each Discharge", you wanted to see every discharge, with only their before and after appointments.  If that's not what you wanted, please clarify your requirements.

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

    #3816632

  • Reply To: How to mantain index in SQL and size of Database

    Are you using data compression?  If not, that should be the first thing to check toward reducing total table size.

    If you want to improve overall performance, we'd need to look...

    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 2, 2020 at 8:24 pm

    #3816219

  • Reply To: Converting minutes to hh:mm:ss

    SwePeso wrote:

    SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * 526.30, '00:00:00'), 8)

    I prefer this method too, with the minor change that the column name should be used rather than a hard-coded value:

    SELECT CONVERT(CHAR(8),...

    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 2, 2020 at 8:17 pm

    #3816218

    • Viewing 15 posts - 2,086 through 2,100 (of 7,619 total)