Martin Smith

My feedback

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

    We’ll send you updates on this idea

    26 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…

    Martin Smith supported this idea  · 
  2. 4 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 →
    Martin Smith shared this idea  · 
  3. 1 vote
    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 →
    Martin Smith shared this idea  · 
  4. 397 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    29 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Martin Smith supported this idea  · 
    An error occurred while saving the comment
    Martin Smith commented  · 

    Hopefully this will be implemented for Read Scale-Out replicas too.

  5. 3 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
    Martin Smith commented  · 

    Your case may be different but reminiscent of the behaviour here https://stackoverflow.com/a/8713467/73226

  6. 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
    Martin Smith commented  · 

    It is always dangerous to assume that the compute scalar in the SELECT list will be evaluated after any filters in the query. See this migrated item for discussion on this https://feedback.azure.com/forums/908035-sql-server/suggestions/32912431-sql-server-should-not-raise-illogical-errors

    In your case you can add a NULLIF to the divisor to replace any zeros so regardless of when it is evaluated you wont get an error

  7. 2 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 →
    An error occurred while saving the comment
    Martin Smith commented  · 

    One workaround would be to change the "RETURN -1" to return a bigint explicitly as here https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b1ee50b2f31d0901171897e8cb3a940b but that shouldn't be needed

  8. 7 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 →
    An error occurred while saving the comment
    Martin Smith commented  · 

    Also repros on Build="15.0.4003.23"

    Martin Smith supported this idea  · 
  9. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Martin Smith commented  · 

    Does the query reference a scalar UDF? If so disabling inlining on the UDF may resolve this

  10. 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
    Martin Smith commented  · 

    This is not correct. Your proposed improved grammar would mean the dot could be supplied without the schema

  11. 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
    Martin Smith commented  · 

    This is not a bug. You should use a supported string aggregation method. Some responses compiled from the old connect site are here https://stackoverflow.com/a/15163136/73226. As you are on 2019 you should use STRING_AGG

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

    We’ll send you updates on this idea

    3 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Martin Smith commented  · 

    Thanks Dimitri,

    The scenario where this actually came up was slightly more complicated than the string literal in the repro but happy to confirm that it all works as expected when the source is a column from a different table than the insert target too.

    --All good here
    DECLARE @Staging TABLE(A INT, CSV VARCHAR(50));
    INSERT INTO @Staging VALUES (1, '1;2;3');

    DECLARE @Final TABLE(A INT, B INT);

    INSERT INTO @Final
    SELECT A, value
    FROM @Staging
    CROSS APPLY STRING_SPLIT(CSV, ';')
    OPTION(USE HINT('ENABLE_QUERY_OPTIMIZER_HOTFIXES'))

    An error occurred while saving the comment
    Martin Smith commented  · 

    Hi Dinakar,

    There isn't a question. It is feedback for something that should be changed.

    For execution plans that change data and use `STRING_SPLIT` the rows don't flow straight into the insert/update operator but instead get spooled first.

    This spool is unnecessary and harms performance.

    I assume that there is likely some property that can be set on the function that indicates that it does not do any tabular data access so the spool is not required.

    Martin

    Martin Smith shared this idea  · 
  13. 1 vote
    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 →
    Martin Smith shared this idea  · 
  14. 11 votes
    Vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    1 comment  ·  Azure Cosmos DB » Portal  ·  Flag idea as inappropriate…  ·  Admin →

    Thanks for your suggestion.

    The index transformation value use see in the header is actually not always an accurate value. There are four replicas in a replica set, each with it’s own copy of the index. When this header is returned it can from a different partition, each which may be at its own percentage of progress during an index rebuild.

    This is something we hope to provide at some point but is on our backlog. Will mark as unplanned for now and change if this moves in the future.

    thanks.

    Martin Smith shared this idea  · 
  15. 13 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 →
    Martin Smith supported this idea  · 
  16. 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 » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment An error occurred while saving the comment
    Martin Smith commented  · 

    As pointed out by Paul White here https://dba.stackexchange.com/a/231632/3690 the column alteration is a red herring and is not neccessary to reproduce this bug.

    A simpler repro is below (all rows are returned despite none of them meeting the predicate on either id2 or status)

    CREATE TABLE #example
    (
    id INT IDENTITY(1, 1),
    barcode CHAR(22),
    id2 INT,
    statusId TINYINT,
    INDEX cci_example CLUSTERED COLUMNSTORE,
    INDEX ix_example (barcode)
    );

    INSERT #example
    (barcode,
    id2,
    statusId)
    SELECT TOP (100) barcode = '5',
    id2 = NULL,
    statusId = 1
    FROM sys.all_columns c1

    DECLARE @filter DECIMAL = 5,
    @barcode CHAR(22) = '5',
    @id2 INT = NULL;

    SELECT *
    FROM #example WITH (INDEX = ix_example)
    WHERE statusId = 999
    AND barcode = @barcode
    AND id2 = @id2;

    DROP TABLE #example

    Martin Smith supported this idea  · 
  17. 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 →
    Martin Smith shared this idea  · 

Feedback and Knowledge Base