Forum Replies Created

Viewing 15 posts - 2,656 through 2,670 (of 7,614 total)

  • Reply To: Get Insert & Update counts from Merge $Action with outer query insert - SQL serv

    You don't need multiple counts.

    DROP TABLE IF EXISTS #actions;
    CREATE TABLE #actions ( action nvarchar(10) NULL )

    MERGE
    ...
    OUTPUT $ACTION into #actions
    ...

    DECLARE @insert_count int
    DECLARE @update_count int

    SELECT @insert_count = SUM(CASE WHEN...

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

    October 24, 2019 at 10:05 pm

    #3691824

  • Reply To: Clustered index - lots of locks

    Keys locks are normal with a clustered index (in fact, they must be key locks, since you can't get rid locks on a ci).  But I wouldn't expect that many...

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

    October 24, 2019 at 7:45 pm

    #3691755

  • Reply To: Case when then

    I think something like this is what you need:

    select @v1 = max(case when type = 1 then amt end),
    @v2 = max(case when type = 2 then amt...

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

    October 24, 2019 at 3:51 pm

    #3691701

  • Reply To: Clustered index - lots of locks

    No, doesn't sound normal.  How did you determine the locking that was occurring?  Does the table have a lot of partitions?

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

    October 24, 2019 at 3:47 pm

    #3691700

  • Reply To: How to know the sql server environment run time

    No, that is purely the physical name.

    On a cluster, SERVERPROPERTY('MachineName') shows the instance name.

    For a non-clustered named instance, if that doesn't show the instance name, then use:

    SERVERPROPERTY('InstanceName')

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

    October 22, 2019 at 5:59 pm

    #3691078

  • Reply To: How to know the sql server environment run time

    Isn't the physical machine name different?

    SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

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

    October 22, 2019 at 4:35 pm

    #3691058

  • Reply To: Inserting Data

    Is the report not able to do a LEFT JOIN instead of an INNER JOIN?  That would be the standard way to do that, without having to create a dummy...

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

    October 22, 2019 at 2:58 pm

    #3691016

  • Reply To: Selecting data from two columns

    You might want to consider assigning an alias to the modified value, so that if it ever changes later the definition of it is only in 1 place:

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

    October 21, 2019 at 8:01 pm

    #3690734

  • Reply To: SQL Query with CTE or other...!

    Maybe this will give you what you want:

    --** Data set up ***************************************************************
    CREATE TABLE dbo.questions (
        question_id int PRIMARY KEY,
        question varchar(1000)...

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

    October 21, 2019 at 5:19 pm

    #3690687

  • Reply To: Is SSIS really that bad?

    SSIS is a good (enough) tool, for what it does.  It is actually very good at moving data around, to/from SQL Server and other platforms as well.

    SSIS also has another...

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

    October 21, 2019 at 4:50 pm

    #3690674

  • Reply To: NOOB question with Case statement

    case

    when columnA <> 0

    then columnB = 1

    else columnB

    end

    The main thing to understand about CASE is that every result from a CASE must be a single value.  The expression leading to...

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

    October 17, 2019 at 5:34 pm

    #3689845

  • Reply To: how to rollback transaction explicitly

    Certain errors by default won't fail the entire transaction/batch.  Before the transaction, use:

    SET XACT_ABORT ON

    to make SQL fail the entire transaction if an error like that occurs.

     

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

    October 15, 2019 at 9:48 pm

    #3689120

  • Reply To: Execution Plan vs Statistics I/0?

    I think we may have interpreted the q differently.  I took "statistics i/o" to mean the results from:

    SET STATISTICS IO ON;

    The logical i/o results are directly comparable, aren't they?

    I think maybe...

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

    October 15, 2019 at 5:52 pm

    #3688992

  • Reply To: Execution Plan vs Statistics I/0?

    Really it's best to use them in combination.

    High I/O numbers tell you to look for better ways to do related part(s) of a query.  The execution plan shows you what...

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

    October 15, 2019 at 2:21 pm

    #3688893

  • Reply To: Question on triggers

    Something along these lines:

    ALTER TRIGGER [dbo].[trig_UpdatePlacementCount]
    ON [dbo].[DebtorHistory]
    AFTER INSERT, UPDATE
    AS
    BEGIN
    SET NOCOUNT ON;

    UPDATE DI
    SET placement_count = dh.file_no_count
    FROM dbo.DebtorInfo DI
    INNER JOIN inserted i ON i.file_no = DI.file_no
    INNER JOIN (
     ...

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

    October 14, 2019 at 2:28 pm

    #3688503

    • Viewing 15 posts - 2,656 through 2,670 (of 7,614 total)