Trying to Make combinations within a bracket in SSMS column

  • Ah... bugger.  I thought one of Steve's submittals worked just as well but it misses correct output on some of the items I had in my test table and so had to take down my comment on it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ... and it also comes up with correct answers.

    (As Brian admitted in a later post) my solution did provide the correct answers!

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil Parkin wrote:

    ... and it also comes up with correct answers.

    (As Brian admitted in a later post) my solution did provide the correct answers!

    It's definitely on the right track and does perfect on most entries but one of the requirements the OP had was that if a string had no brackets, to return the whole thing.  That's the only place where your code fails.

    I also had to take down my earlier comment about Steve's code because it also has a couple of failures if you do a little extreme testing with an entry like ABC[123].  I missed the errors earlier because my eyes don't appear to work correctly until I've had a bunch of coffee.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Phil Parkin wrote:

    ... and it also comes up with correct answers.

    (As Brian admitted in a later post) my solution did provide the correct answers!

    It's definitely on the right track and does perfect on most entries but one of the requirements the OP had was that if a string had no brackets, to return the whole thing.  That's the only place where your code fails.

    I also had to take down my earlier comment about Steve's code because it also has a couple of failures if you do a little extreme testing with an entry like ABC[123].  I missed the errors earlier because my eyes don't appear to work correctly until I've had a bunch of coffee.

    Aha, I missed that requirement. Here's a modified version which handles it:

    DROP TABLE IF EXISTS #x;

    CREATE TABLE #x
    (
    SomeText VARCHAR(100)
    );

    INSERT #x
    (
    SomeText
    )
    VALUES
    ('A[CL]')
    ,('CF');

    SELECT *
    FROM #x x;

    WITH Tally (N)
    AS (
    -- 8000 rows (max length of the VARCHAR string)
    -- SQL Prompt formatting off
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) e(n)
    -- SQL Prompt formatting on
    )
    SELECT x.SomeText
    ,Combo = IIF(calcs.StartPos > 0
    ,CONCAT(LEFT(x.SomeText, calcs.StartPos - 1), SUBSTRING(x.SomeText, t.N + calcs.StartPos, 1))
    ,x.SomeText)
    FROM #x x
    CROSS JOIN Tally t
    CROSS APPLY
    (
    SELECT StartPos = CHARINDEX('[', x.SomeText)
    ,EndPos = CHARINDEX(']', x.SomeText)
    ) calcs
    WHERE t.N <= calcs.EndPos - calcs.StartPos - 1
    OR
    (
    calcs.StartPos = 0
    AND t.N = 1
    )
    ORDER BY x.SomeText
    ,t.N;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I have not tested this against any of the other solutions posted so far. It just looked like an interesting problem so I figured that I'd throw my hat into the ring.

    Based on what I've read so far, this will handle all of the requested scenarios plus a few more, including spaces around the bracketed section and constants following the bracketed section...

    IF OBJECT_ID('tempdb..#test_string', 'U') IS NOT NULL 
    BEGIN DROP TABLE #test_string; END;

    CREATE TABLE #test_string (
    id int NOT NULL IDENTITY(1,1),
    string varchar(8000) NOT NULL
    );
    INSERT #test_string(string) VALUES
    ('a[abcd]'),
    ('ABCD[123456]'),
    ('just a test [a]'),
    ('another test [asdfghj]'),
    ('test [asdfghj]ing'),
    ('test [asdfghj] ing'),
    ('ZXCV'),
    ('[QWERTY]');
    GO

    -- SELECT * FROM #test_string ts

    SELECT
    ts.id,
    ts.string,
    --br.[open],
    --br.[close],
    --sec.beg,
    --sec.mid,
    --sec.[end],
    --t.n,
    --bv.bracket_val,
    combo_value = CONCAT(sec.beg, bv.bracket_val, sec.[end])
    FROM
    #test_string ts
    CROSS APPLY ( VALUES (
    NULLIF(CHARINDEX('[', ts.string), 0),
    NULLIF(CHARINDEX(']', ts.string), 0)
    ) ) br ([open], [close])
    CROSS APPLY ( VALUES (
    REPLACE(SUBSTRING(ts.string, 1, ISNULL(br.[open], 8000)), '[', ''),
    REPLACE(SUBSTRING(ts.string, br.[open], br.[close] - br.[open]), '[', ''),
    REPLACE(SUBSTRING(ts.string, ISNULL(br.[close], 8000), 8000), ']', '')
    ) ) sec (beg, mid, [end])
    CROSS APPLY dbo.tfn_Tally_10k(LEN(ISNULL(sec.mid, 0)), 1) t-- this is a "tally function" similar to what Jeff Moden has already posted.
    CROSS APPLY ( VALUES (SUBSTRING(sec.mid, t.n, 1)) ) bv (bracket_val);

     

    • This reply was modified 4 years, 4 months ago by  Jason A. Long.
  • If tvf's are allowed then I'd borrow some of Phil's code

    drop function if exists dbo.fnTIDprefix_Expand;
    go
    create function dbo.fnTIDprefix_Expand(
    @TIDprefix varchar(20))
    returns table as
    return
    select
    iif(br.br_start>0, concat(left(@TIDprefix, br.br_start-1), substring(@TIDprefix, t.n+br.br_start, 1)), @TIDprefix) TIDprefix_Expanded
    from
    (select
    charindex('[', @TIDprefix) br_start,
    charindex(']', @TIDprefix) br_end) br
    cross apply
    dbo.fnTally(1, iif(br.br_start>0, (br.br_end-br.br_start-1), 1)) t;
    go

    /* final query */
    select
    t.TIDPrefix,
    tx.TIDprefix_Expanded
    from
    dbo.test_TID t
    cross apply
    dbo.fnTIDprefix_Expand(t.TIDprefix) tx;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  •  

    CREATE TABLE dbo.t
    (
    TxtVARCHAR(8000)
    );

    INSERT dbo.t (Txt) VALUES
    ('A[CL]'),
    ('B[HQUW]'),
    ('C[1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ]'),
    ('D2'),
    ('E[1]');
    GO
    WITH Result
    AS
    (
    SELECTLEFT(Txt, 1) AS Constant,-- all strings with [...]
    SUBSTRING(Txt, 3, 1) AS Variable,
    SUBSTRING(Txt, 4, LEN(Txt) - 4) AS Rest
    FROM dbo.t
    WHERE SUBSTRING(Txt, 2, 1) = '['
    UNION
    SELECTLEFT(Txt, 1) AS Constant,-- all strings without [...]
    SUBSTRING(Txt, 2, 1) AS Variable,
    '' AS Rest
    FROM dbo.t
    WHERE SUBSTRING(Txt, 2, 1) <> '['
    UNION ALL
    SELECTConstant,
    LEFT(Rest, 1) AS Variable,
    SUBSTRING(Rest, 2, 8000) AS Rest
    FROM Result
    WHERE LEN(Rest) > 0
    )
    SELECT *, CONCAT(Constant, Variable) AS Col
    FROM Result
    ORDER BY Constant;

     

  • carsten.saastamoinen wrote:

    CREATE TABLE dbo.t
    (
    TxtVARCHAR(8000)
    );

    INSERT dbo.t (Txt) VALUES
    ('A[CL]'),
    ('B[HQUW]'),
    ('C[1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ]'),
    ('D2'),
    ('E[1]');
    GO
    WITH Result
    AS
    (
    SELECTLEFT(Txt, 1) AS Constant,-- all strings with [...]
    SUBSTRING(Txt, 3, 1) AS Variable,
    SUBSTRING(Txt, 4, LEN(Txt) - 4) AS Rest
    FROM dbo.t
    WHERE SUBSTRING(Txt, 2, 1) = '['
    UNION
    SELECTLEFT(Txt, 1) AS Constant,-- all strings without [...]
    SUBSTRING(Txt, 2, 1) AS Variable,
    '' AS Rest
    FROM dbo.t
    WHERE SUBSTRING(Txt, 2, 1) <> '['
    UNION ALL
    SELECTConstant,
    LEFT(Rest, 1) AS Variable,
    SUBSTRING(Rest, 2, 8000) AS Rest
    FROM Result
    WHERE LEN(Rest) > 0
    )
    SELECT *, CONCAT(Constant, Variable) AS Col
    FROM Result
    ORDER BY Constant;

    carsten.saastamoinen wrote:

    CREATE TABLE dbo.t
    (
    TxtVARCHAR(8000)
    );

    INSERT dbo.t (Txt) VALUES
    ('A[CL]'),
    ('B[HQUW]'),
    ('C[1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ]'),
    ('D2'),
    ('E[1]');
    GO
    WITH Result
    AS
    (
    SELECTLEFT(Txt, 1) AS Constant,-- all strings with [...]
    SUBSTRING(Txt, 3, 1) AS Variable,
    SUBSTRING(Txt, 4, LEN(Txt) - 4) AS Rest
    FROM dbo.t
    WHERE SUBSTRING(Txt, 2, 1) = '['
    UNION
    SELECTLEFT(Txt, 1) AS Constant,-- all strings without [...]
    SUBSTRING(Txt, 2, 1) AS Variable,
    '' AS Rest
    FROM dbo.t
    WHERE SUBSTRING(Txt, 2, 1) <> '['
    UNION ALL
    SELECTConstant,
    LEFT(Rest, 1) AS Variable,
    SUBSTRING(Rest, 2, 8000) AS Rest
    FROM Result
    WHERE LEN(Rest) > 0
    )
    SELECT *, CONCAT(Constant, Variable) AS Col
    FROM Result
    ORDER BY Constant;

    While that does appear to provide the desired result, it uses a recursive cte that scans the original table twice and produces 257 logical reads.  Please see the following article for why rCTEs (recursive CTEs) aren't a good thing to use for this type of thing... they're actually a form of "Hidden RBAR" on steriods.

    https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes

    Please see my previous code post for a method that only uses a single read (the one page the data lives on).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Not sure if i read it correctly but look into STRING_AGG or STRING_SPLIT

  • Here's my attempt:

    ;with TestData as 
    (
    SELECT *
    FROM (VALUES ('A[kfg]'),
    ('B[lr]'),
    ('Cf'),
    ('D[3r]'),
    ('E[CL]'),
    ('F[HQUW]'),
    ('G[kfg]'),
    ('H][kfg]'),
    ('IJK[123]')
    ) A(Name)
    )
    SELECT *
    ,CASE WHEN L.PosLeftBracket > 1 THEN LEFT(t.Name,L.PosLeftBracket-1) + SUBSTRING(t.Name,L.PosLeftBracket+ Row.RowNum,1)
    ELSE t.Name
    End Output
    FROM TestData t
    CROSS APPLY(VALUES (CHARINDEX('[',t.Name,1))) L(PosLeftBracket)
    CROSS APPLY(VALUES (CHARINDEX(']',t.Name,L.PosLeftBracket+1))) R(PosRightBracket)
    OUTER APPLY(SELECT TOP(CASE WHEN R.PosRightBracket-L.PosLeftBracket > 0 THEN R.PosRightBracket-L.PosLeftBracket -1 ELSE 0 END)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RowNum
    FROM sys.columns) Row
  • dndaughtery wrote:

    Not sure if i read it correctly but look into STRING_AGG or STRING_SPLIT

    Got code?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jonathan AC Roberts wrote:

    Here's my attempt:

    ;with TestData as 
    (
    SELECT *
    FROM (VALUES ('A[kfg]'),
    ('B[lr]'),
    ('Cf'),
    ('D[3r]'),
    ('E[CL]'),
    ('F[HQUW]'),
    ('G[kfg]'),
    ('H][kfg]'),
    ('IJK[123]')
    ) A(Name)
    )
    SELECT *
    ,CASE WHEN L.PosLeftBracket > 1 THEN LEFT(t.Name,L.PosLeftBracket-1) + SUBSTRING(t.Name,L.PosLeftBracket+ Row.RowNum,1)
    ELSE t.Name
    End Output
    FROM TestData t
    CROSS APPLY(VALUES (CHARINDEX('[',t.Name,1))) L(PosLeftBracket)
    CROSS APPLY(VALUES (CHARINDEX(']',t.Name,L.PosLeftBracket+1))) R(PosRightBracket)
    OUTER APPLY(SELECT TOP(CASE WHEN R.PosRightBracket-L.PosLeftBracket > 0 THEN R.PosRightBracket-L.PosLeftBracket -1 ELSE 0 END)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RowNum
    FROM sys.columns) Row

    Nicely done.  My only concern would be that since the DBA won't allow the OP to create the fnTally function, will they also forbid the OP from calling a system view?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Nicely done.  My only concern would be that since the DBA won't allow the OP to create the fnTally function, will they also forbid the OP from calling a system view?

    Can be fixed:

    ;with TestData as 
    (
    SELECT *
    FROM (VALUES ('A[kfg]'),
    ('B[lr]'),
    ('Cf'),
    ('D[3r]'),
    ('E[CL]'),
    ('F[HQUW]'),
    ('G[kfg]'),
    ('H][kfg]'),
    ('IJK[123]')
    ) A(Name)
    )
    SELECT *
    ,CASE WHEN L.PosLeftBracket > 1 THEN LEFT(t.Name,L.PosLeftBracket-1) + SUBSTRING(t.Name,L.PosLeftBracket+ Row.RowNum,1)
    ELSE t.Name
    End Output
    FROM TestData t
    CROSS APPLY(VALUES (CHARINDEX('[',t.Name,1))) L(PosLeftBracket)
    CROSS APPLY(VALUES (CHARINDEX(']',t.Name,L.PosLeftBracket+1))) R(PosRightBracket)
    OUTER APPLY(SELECT TOP(CASE WHEN R.PosRightBracket-L.PosLeftBracket > 0 THEN R.PosRightBracket-L.PosLeftBracket -1 ELSE 0 END)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RowNum
    FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(N),
    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) B(N),
    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) C(N)) Row
  • I do not agree with you. We have discussed it before and your focus is on IO. Your solution is much more cpu heavy if the statement is only rarely executed and needs to be compiled every time. I think that the design will be different if the query will be executed many times each minute.

    I'm editing my script to allow more prefix characters - the same as your script.

    CREATE TABLE dbo.t
    (
    IDINTNOT NULL IDENTITY,
    TxtVARCHAR(8000)
    );

    INSERT dbo.t (Txt) VALUES
    ('A[CL]'),
    ('B[HQUW]'),
    ('C[1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ]'),
    ('D2'),
    ('E[1]'),
    ('FGH[12345]'),
    ('IJKLMN');
    GO
    SET STATISTICS TIME, IO ON;
    GO
    CREATE FUNCTION dbo.fnTally
    /**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
    **********************************************************************************************************************/
    (@ZeroOrOne BIT, @MaxN BIGINT)
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN WITH
    H2(N) AS ( SELECT 1
    FROM (VALUES
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    )V(N)) --16^2 or 256 rows
    , H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
    , H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
    SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
    SELECT TOP(@MaxN)
    N = ROW_NUMBER() OVER (ORDER BY N)
    FROM H8;

    If we execute the 2 statements

    WITH ctePosit 
    AS
    (--==== "Cheater" method to handle just about anything
    SELECT Txt
    ,LeftBower = CHARINDEX('[', Txt + '[')
    ,RightBower = CHARINDEX(']', Txt + '[]')
    FROM dbo.t
    )--==== This gabs the "header" and each character and concatenates them together as requested.
    SELECT --TIDPrefix = p.Txt,
    TIDExpanded = CONCAT(LEFT(p.Txt, LeftBower - 1), SUBSTRING(p.Txt, LeftBower + t.N , 1))
    FROM ctePosit AS p OUTER APPLY dbo.fnTally(1, RightBower - LeftBower - 1) AS t;
    GO
    -------------------------------------------------------------------------------
    WITH Result
    AS
    (
    SELECTID,
    LEFT(Txt, CHARINDEX('[', Txt) - 1) AS Constant,-- all strings with [...]
    SUBSTRING(Txt, CHARINDEX('[', Txt) + 1, 1) AS Variable,
    SUBSTRING(Txt, CHARINDEX('[', Txt) + 2, LEN(Txt) - CHARINDEX('[', Txt) - 2) AS Rest
    FROM dbo.t
    WHERE CHARINDEX('[', Txt) > 0
    UNION
    SELECTID,
    Txt AS Constant,-- all strings without [...]
    NULL AS Variable,
    '' AS Rest
    FROM dbo.t
    WHERE CHARINDEX('[', Txt) = 0
    UNION ALL
    SELECTID,
    Constant,
    LEFT(Rest, 1) AS Variable,
    SUBSTRING(Rest, 2, 8000) AS Rest
    FROM Result
    WHERE LEN(Rest) > 0
    )
    SELECT CONCAT(Constant, Variable) AS Col
    FROM Result;

    and look at the result your solution will use much more cpu.

    Your solution

    SQL Server parse and compile time:

    CPU time = 26 ms, elapsed time = 26 ms.

    (50 rows affected)

    Table 't'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    My solution 

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 2 ms.

    (50 rows affected)

    Table 'Worktable'. Scan count 2, logical reads 289, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

    Table 't'. Scan count 2, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    So your solution use 26 ms, my solution only 2. And my solution have 289 logical read - not expensive reads, because data is in memory, your solution only 1. But have your system a bottleneck - is it IO or cpu!

    In this situation - as always - tally is not the only way to do it! You write in the article, that you reference

    "If you go back and read the paragraph above, the word "quickly" has been braced in quotes because it's not always done quickly. Judging from many of the posts on this and other SQL forums, more and more well meaning folks are counting with Recursive CTE's because they're relatively simple to write and don't require such things as a Tally table to drive them. They have no idea of the performance problem they've just instantiated with their code nor do the people who pick up on the technique. It's becoming a nearly viral problem."

    Developers who ALWAYS think that the tally-solution is the right solution, make a solution that can be more difficult to write and understand/verify and maybe instantiated  cpu-problems!

  • carsten.saastamoinen wrote:

    ... Your solution is much more cpu heavy if the statement is only rarely executed and needs to be compiled every time. I think that the design will be different if the query will be executed many times each minute.

    Just to be clear, the comparison is between 1) creation of the tally function and execution of the query, versus 2) execution of the recursive CTE query?  If so it's not a fair comparison imo because there's not really a need to recompile the function.  Jeff recognizes this by specifying WITH SCHEMABINDING which is a performance optimization that's applicable when the function doesn't reference any physical objects.  If the function doesn't reference physical objects then why recompile it?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 15 posts - 31 through 45 (of 71 total)

You must be logged in to reply to this topic. Login to reply