Forum Replies Created

Viewing 15 posts - 16 through 30 (of 2,458 total)

  • Reply To: Convert teradata REGEXP_INSTR into SQL SERVER

    You can create your own INSTR function leveraging ngrams8k.

    CREATE OR ALTER FUNCTION dbo.Instr8K
    (
      @string   VARCHAR(8000),
      @search   VARCHAR(100),
      @instance INT
    )
    /*
    Created by...
    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 6, 2021 at 4:43 pm

    #3866027

  • Reply To: Can anyone explain the design decision here?

    For cleaner code you can leverage fnTally. Since fnTally leverages TOP, you can exploit row goals as Steve Collins mentioned above.

    CREATE OR ALTER FUNCTION dbo.getYears(@year...
    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    February 18, 2021 at 6:40 pm

    #3846891

  • Reply To: Search Report definitions

    I don't have a server handy at the moment but what you are looking for is in the REPORTSERVER.DBO.CATALOG

    This has the RDL XML (Report Definitions) which you can query. To...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    January 22, 2021 at 3:36 pm

    #3836334

  • Reply To: Tally OH! An Improved SQL 8K “CSV Splitter” Function

    @ Alan,

    I'm honestly a little bit surprised that you're justifying a sort because it has a low row count.  We know how that usually works out.

    I was a little rushed...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    December 27, 2020 at 8:17 pm

    #3824521

  • Reply To: Tally OH! An Improved SQL 8K “CSV Splitter” Function

    apart from the performance that is likely to be quite bad has errors on its construct

    The Construct is a problem but I suspect the performance should be fine, STRING_SPLIT is...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    December 26, 2020 at 1:21 am

    #3824143

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

    there is currently no way to incrementally populate the table.

    ...  a [Huge] fact table that gets regenerated nightly

    This is a design/process issue more than a technical one. I'll put my...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    December 8, 2020 at 6:28 pm

    #3818308

  • Reply To: Execution plan flips from "good" to "bad" at a certain threshold

    A couple things to consider. First, In SSMS 2019 the actual execution plan will show you how much time each portion of the query took. You don't need that here...

    • This reply was modified 5 years, 6 months ago by Alan Burstein. Reason: Added another option - dbo.many

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    November 17, 2020 at 6:46 pm

    #3808893

  • Reply To: can't get Numbers from Name to prevent conversion failed ?

    Grab a copy of PatReplace8K and it should be easy to strip out what you need.

    SELECT 
      pa.*, 
      Clean1 = CAST(ISNULL(pr.newString,0) AS FLOAT),
    ...
    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    November 17, 2020 at 6:20 pm

    #3808879

  • Reply To: Longest Common Substring in an ITVF

    @sgmunson

    What you posted is similar to my first version of this except that I use my ngrams function to generate the tally table. Here's a simplified version of...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    August 4, 2020 at 3:41 am

    #3777074

  • Reply To: string compare again .. :)

    Jeff Moden wrote:

    Agreed.  I said similar in this post from above...

    I missed this.

    The difference is that I did the split using an indexed view. This way the split only happens once,...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    August 3, 2020 at 6:46 pm

    #3776981

  • Reply To: string compare again .. :)

    Jeff Moden wrote:

    Alan Burstein wrote:

    Just as a side note, depending on how often your columns are updated/modified, you can always split the string ahead of time using a permanent tally table like...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    August 3, 2020 at 6:38 pm

    #3776980

  • Reply To: string compare again .. :)

    Just as a side note, depending on how often your columns are updated/modified, you can always split the string ahead of time using a permanent tally table like this:

    

  • This reply was modified 5 years, 10 months ago by Alan Burstein. Reason: fixed the word "don't" so the DML is not red
    • 


  • This reply was modified 5 years, 9 months ago by Alan Burstein. Reason: Added Execution plans, additional notes
    •
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

August 3, 2020 at 1:51 am

#3776744

  • Reply To: Longest Common Substring in an ITVF

    Jeff Moden wrote:

    I'm not seeing any code attached to your last post.  What code are you talking about being "attached"?

    x

     

     

     

     

     

     

     

    Grrr...

    /*********************************** PART #1...
    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    August 1, 2020 at 1:51 am

    #3776470

  • Reply To: Longest Common Substring in an ITVF

    UPDATED 20200803 at 9:30PM

    Ok, 4 1/2 years later I have a something really good to show!

    This code is from one of many articles in various phases of completion which I'll...

    • This reply was modified 5 years, 10 months ago by Alan Burstein. Reason: Typo
    • This reply was modified 5 years, 10 months ago by Alan Burstein.
    • This reply was modified 5 years, 10 months ago by Alan Burstein. Reason: Trying to add files
    • This reply was modified 5 years, 9 months ago by Alan Burstein.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    July 31, 2020 at 7:35 pm

    #3776405

  • Reply To: The Computing Revolution

    Great article Steve. Really informative.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    July 27, 2020 at 4:34 pm

    #3775051

    • Viewing 15 posts - 16 through 30 (of 2,458 total)