L. Szozda

My feedback

  1. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
  2. 565 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    28 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 72

    <=-=Mar 10 2016 11:26AM=-=>

    It’s a shame that this was submitted as just a “suggestion”. It should actually be listed as a “bug” because there’s only a comparatively small set of use cases where enumeration of the result set of elements is not important.

    <=-=Mar 11 2016 12:47PM=-=>

    I agree that an order column is required; one example use case is where two lists are passed in, and ordinal positions in one list correspond to positions in the other.

    <=-=Mar 11 2016 3:12PM=-=>

    Please see the related suggestion: STRING_SPLIT needs “RemoveEmptyEntries” option, like String.Split in .NET ( https://connect.microsoft.com/SQLServer/feedback/details/2462002/ ).

    <=-=Mar 12 2016 12:02PM=-=>

    This kind of function is primarily needed for de-serializing previously serialized arrays of values of any type format-able as text.
    I therefore recommend to have the result set of this function work excellent with this use-case.

    With de-serialized arrays there is a need to…

    An error occurred while saving the comment
    L. Szozda commented  · 

    I strongly aggree that SQL Server should support this feature(it is available in PostgreSQL UNNEST() WITH ORDINALITY).

    As a workaround OPENJSON and KEY could be used:

    DECLARE @str NVARCHAR(MAX) = 'Ben|10, Bob|325, Susan|7, Michael|99';

    SELECT s.col, PersonName, Amount,rn,
    RunningTotal = SUM(Amount) OVER(ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM (SELECT @str) s(col)
    CROSS APPLY (SELECT
    PersonName = LEFT(TRIM(Value), CHARINDEX('|', TRIM (Value))-1)
    ,Amount = CAST(RIGHT(TRIM(Value), CHARINDEX('|', REVERSE (TRIM(Value)))-1) AS INT)
    ,value
    ,rn = [key]
    FROM OPENJSON(JSON_QUERY(
    REPLACE(CONCAT('["',s.col,'"]'), ', ', '","')))
    ) A;

    https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=74b6144f702edcffce5e338c0686cd00

    Full story: https://stackoverflow.com/questions/56742851/join-together-multiple-columns-split-by-a-character-in-sql/56742939#56742939

    L. Szozda supported this idea  · 
  3. 21 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  Azure Synapse Analytics » SQL/Polybase  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 
  4. 43 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  Azure Synapse Analytics » SQL/T-SQL  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 
  5. 67 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    planned  ·  0 comments  ·  Azure Synapse Analytics » SQL/T-SQL  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 
  6. 124 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    9 comments  ·  Azure Synapse Analytics » SQL/Loading  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 
  7. 213 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    planned  ·  11 comments  ·  Azure Synapse Analytics » SQL/Other  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 
  8. 444 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    24 comments  ·  Azure Synapse Analytics » SQL/T-SQL  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 
  9. 147 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    21 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 3

    <=-=Jan 8 2018 12:16PM=-=>

    Thanks for the suggestion.
    We’ll consider it for a future release.

    Are there specific limitations imposed when you have the filegroup, e.g., no database snapshot, that hold you back from creating the filegroup?
    Or is it only the feeling of not being able to turn back?


    Jos de Bruijn – Database Systems PM

    L. Szozda supported this idea  · 
  10. 447 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    14 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 
  11. 336 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  9 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    L. Szozda commented  · 

    This feature is a real game changer and allows to implement things that were virtually impossible/unreadable.

    One more example is capping running total: https://stackoverflow.com/a/53994970/5070879

    L. Szozda supported this idea  · 
  12. 4 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda shared this idea  · 
  13. 17 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 
    An error occurred while saving the comment
    L. Szozda commented  · 

    Workaround - adding blanks for each expression:

    ;WITH personFruits
    AS
    (
    SELECT 'John' AS [Person], 'Apple' AS [Fruit] UNION
    SELECT 'John' AS [Person], 'Banana' AS [Fruit] UNION
    SELECT 'John' AS [Person], 'Kiwi' AS [Fruit] UNION
    SELECT 'Jane' AS [Person], 'Apple' AS [Fruit] UNION
    SELECT 'Jane' AS [Person], 'Strawberry' AS [Fruit] UNION
    SELECT 'Jane' AS [Person], 'Watermellon' AS [Fruit] UNION
    SELECT 'Jane' AS [Person], 'Grape' AS [Fruit] UNION
    SELECT 'Dale' AS [Person], 'Kiwi' AS [Fruit] UNION
    SELECT 'Dale' AS [Person], 'Watermellon' AS [Fruit]
    )
    SELECT
    p.Person
    ,STRING_AGG(p.Fruit, ', ') WITHIN GROUP (ORDER BY p.Fruit) AS [FruitsWithComma]
    ,STRING_AGG(''+p.Fruit, '; ') WITHIN GROUP (ORDER BY p.Fruit) AS [FruitsWithSemicolon]
    ,STRING_AGG('' + '' + p.Fruit, ' - ') WITHIN GROUP (ORDER BY p.Fruit) AS [FruitsWithDash]
    FROM
    personFruits AS p
    GROUP BY
    p.Person
    ORDER BY
    p.Person

    https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b13d8d262884d0f2b703e7a7845746ef

    Kudos: https://stackoverflow.com/questions/52533487/string-agg-not-behaving-as-expected/52534215#52534215

  14. 3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda shared this idea  · 
  15. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    L. Szozda commented  · 
    L. Szozda shared this idea  · 
  16. 6 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 
  17. 4 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    L. Szozda commented  · 

    In my opinion there is no error here because your query is logically equivalent to:

    delete from foo where JobHistoryId in (select JobHistoryId from #data)
    <=>
    delete from foo where JobHistoryId in (select foo.JobHistoryId from #data)

    You should always prefix table columns:

    delete from foo where JobHistoryId in (select #data.JobHistoryId from #data)

    https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ca1f5784a3bd08389cf3a4dc2cc484bf

    Similar question on SO: https://stackoverflow.com/a/50955287

  18. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda shared this idea  · 
  19. 9 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    L. Szozda commented  · 

    I couldn't agree more. This kind of syntax could be very useful. It is supported by PostgreSQL(dollar quoting) and Oracle(text literals):
    https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
    https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#i42617

    Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=512513a4003aeb850073ff3fc6f9a26a

    Sample usage:
    - all kind of nested SQL strings
    - dynamic SQL(just copying static SQL code into dynamic without doubling quotes)
    - sp_execute_external_script (R or Python)
    - ad-hoc code generation like `SELECT FORMATMESSAGE('SELECT tab_name = ''%s'', cnt = COUNT(*) FROM %s', QUOTENAME(name), QUOTENAME(name))
    FROM sys.tables`

    L. Szozda supported this idea  · 
  20. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Azure portal  ·  Flag idea as inappropriate…  ·  Admin →
    L. Szozda supported this idea  · 

Feedback and Knowledge Base