Forum Replies Created

Viewing 15 posts - 2,281 through 2,295 (of 7,619 total)

  • Reply To: Function Code

    As to the actual code, a more straightforward way is to CAST to date, which will perforce remove the time value, then CAST back to datetime (this probably isn't strictly...

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

    #3790984

  • Reply To: sql server 2016 performance tuning

     

    Getting rid of NOLOCK will only hurt performance, never help it.  That's not an endorsement of nor objection to NOLOCK in these specific statements, just a general statement of fact. ...

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

    #3790188

  • Reply To: slow query performance

    Quote: "We couldn't do much about the database design as it is a vendor specific database."

    Understood.  Lol, the only thing potentially worse than a "programmer (non)'designed' table" is a "vendor...

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

    #3790138

  • Reply To: Insert Trigger

    Try below.  Sorry, I don't have time right now to explain the code.

    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO

    CREATE TRIGGER [dbo].[Dupl_Rec]
    ON [dbo].[Table A]
    AFTER INSERT 
    AS
    SET NOCOUNT ON;

    INSERT INTO dbo.Audit...

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

    #3789857

  • Reply To: slow query performance

    vsamantha35 wrote:

    DesNorton wrote:

    According to your table structure, there is no index on [MULE_BATCH_ID].  This means that SQL has to do a table scan to find the record.

    You are doing a...

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

    #3789814

  • Reply To: SQL Query - FULL JOIN multiple tables but return NULL results

    That is normal behavior for a FULL JOIN.  You need to adjust the final SELECT to reflect the fact that any table's results could be NULL, like so:

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

    #3789655

  • Reply To: Indexing help

    You didn't provide nearly enough info to analyze your index needs.

    However, keep in mind that SQL strongly favors equal (=) comparisons over other types.  So, if your conditions are "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 19, 2020 at 4:02 am

    #3789594

  • Reply To: Indexing help

    Mr. Brian Gale wrote:

    But something to keep in mind is that indexes are not really designed to be a performance tool

    Uh, no, they are designed exclusively to help performance.  That is, increased...

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

    #3789593

  • Reply To: Stored procedure using multi-valued optional parameter

    Btw, you're wasting bytes making that column nvarchar rather than varchar, since the only values possible don't include any non-standard chars.

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

    #3789592

  • Reply To: Wanting to learn

    Grant Fritchey wrote:

    ScottPletcher wrote:

    Much better would have been to create an SSMS server group(s) with all the relevant servers in it(them), then fire off the script for a given server group(s)...

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

    #3789198

  • Reply To: SQL 2014 Table Truncated Inadvertently

    DDL triggers are a great option.  I use them in nearly every db to prevent actions we don't want taken.  As examples, in some dbs we don't want objects created...

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

    #3789196

  • Reply To: Wanting to learn

    Much better would have been to create an SSMS server group(s) with all the relevant servers in it(them), then fire off the script for a given server group(s) whenever needed. ...

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

    #3789000

  • Reply To: Wanting to learn

    I agree.   I'd strongly urge to concentrate on the core aspects of SQL Server first, then learn about PowerShell later if you still want to.  There's enough to learn 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".

    September 16, 2020 at 5:30 am

    #3788754

  • Reply To: SQL 2014 Table Truncated Inadvertently

    For new db(s), the support team can "notify itself".  It's easy enough to check every day to see if a new db has been created, whether a user tells 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 16, 2020 at 4:54 am

    #3788749

  • Reply To: How to stop database restore that survived reboot showing progress in error log

    SQL Server must pre-format log files (setting each page's non-control data to binary zeros, I think).  That will take quite a while for 95GB (apparently 3 hours on your system...

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

    #3788629

    • Viewing 15 posts - 2,281 through 2,295 (of 7,619 total)