Forum Replies Created

Viewing 15 posts - 901 through 915 (of 7,619 total)

  • Reply To: query execution plan differ when run as a single query vs. in a sp

    You need to look at your stand-alone (non-proc) query again.  It seems to have an INNER JOIN rather than a LEFT JOIN, at least according to the query plan.

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

    April 28, 2022 at 1:47 am

    #4023381

  • Reply To: Is it correct that MS SQL automatically retrieves data to optimize the "SELECT"

    Maybe it will.  SQL can pre-fetch data.  It depends on the specifics of the situation, including your edition of 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".

    April 27, 2022 at 8:24 pm

    #4023264

  • Reply To: Which command will using to delete all rows from table truncate or delete ?

    Since TRUNCATE is more efficient, try it first.

    If it doesn't work, then use DELETE.

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

    April 27, 2022 at 8:23 pm

    #4023263

  • Reply To: Concat Column of multiple records

    STRING_AGG isn't available before SQL 2016, right?

    Select T1.Itemcode,T1.name ,
        stuff((select ', ' + docno 
            from [order]...

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

    April 27, 2022 at 3:30 pm

    #4023106

  • Reply To: My calculation with a CASE statement isn\'t working

     

    Rather than a CASE, use the built-in REPLICATE function:

    DECLARE @MAX_naam AS varchar(MAX) 
    SET @MAX_naam = 22 --(SELECT SUBSTRING(MAX(naam),LEN(MAX(naam))-1,LEN(MAX(naam))) FROM incident); -- = 22
    DECLARE @maxnaam_length AS int =...

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

    April 26, 2022 at 9:30 pm

    #4022698

  • Reply To: Log file with different formats

    jhonhernandez300 wrote:

    I know a little bit of Sql but not about this topic.

    Ah, but this is a SQL Server forum.  Your q is somewhat off-topic here.  You might want 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".

    April 26, 2022 at 6:29 pm

    #4022632

  • Reply To: Pre allocating free space to db growth

    Right, because reorganize only ever needs 1 extra work page, the in_tempdb option is really meaningless for reorg.

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

    April 25, 2022 at 7:22 pm

    #4022021

  • Reply To: sql query help 

    SELECT SubmissionGuid
    FROM #Subs
    GROUP BY SubmissionGuid
    HAVING MAX(CASE WHEN QuoteStatus = 1 THEN 1 ELSE 0 END) = 0

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

    April 25, 2022 at 7:20 pm

    #4022020

  • Reply To: Pre allocating free space to db growth

    To avoid using the user db for index rebuild work space, look into specifying SORT_IN_TEMPDB = ON as part of the index REBUILD.  This is especially true if you have...

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

    April 25, 2022 at 5:42 pm

    #4021976

  • Reply To:

    I'll assume you have an existing clustered index on the table.  If you do, most likely you'll want to use a clustered columnstore index instead.  To do that, follow these...

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

    April 25, 2022 at 5:31 pm

    #4021970

  • Reply To: Transaction Log Growth During a Delete

    For best performance, make sure you have the log table properly clustered.  Specifically, if you intend to delete by log time, as is quite typical, then cluster the table 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".

    April 25, 2022 at 2:55 pm

    #4021861

  • Reply To: How to get the first and last dates of all the months of the current year.

    Ed B wrote:

    Rather than adding a month and taking away a day to  get the end of month (which is how I have always done it) there is a function called...

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

    April 22, 2022 at 8:03 pm

    #4020714

  • Reply To: Find Maximum Qty of fruits (regardless of fruit) which could be bought by $50

    I suspect there's some way to do this without recursion, but for now, here's a method using recursion.

    DECLARE @amount_to_spend decimal(9, 2)
    SET @amount_to_spend = 50.00

    ;WITH cte_products_by_price AS (
    ...

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

    April 22, 2022 at 8:00 pm

    #4020711

  • Reply To: How to get the first and last dates of all the months of the current year.

     

    ;WITH
    cte_tally10 AS (
        SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
    ),
    cte_tally100 AS (
        SELECT 0 AS number UNION ALL
        SELECT...

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

    April 22, 2022 at 7:23 pm

    #4020708

  • Reply To: Handle TSQL Session exections terminated by users

    I don't think you don't need an explicit COMMIT and ROLLBACK.  In SQL Server, by default each SQL statement that modifies data will be in its own transaction.

    Specify "SET XACT_ABORT...

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

    April 21, 2022 at 2:41 pm

    #4020021

Viewing 15 posts - 901 through 915 (of 7,619 total)