JediSQL

My feedback

  1. 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 →
    JediSQL supported this idea  · 
  2. 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 →
    JediSQL shared this idea  · 
  3. 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL supported this idea  · 
  4. 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 →
    JediSQL supported this idea  · 
  5. 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 →
    JediSQL supported this idea  · 
  6. 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 →
    JediSQL supported this idea  · 
  7. 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL supported this idea  · 
  8. 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL supported this idea  · 
  9. 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 →
    JediSQL supported this idea  · 
  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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL commented  · 

    Just convert all NULLs to empty strings and check for empty strings:

    IF (ISNULL(MyColumn, '') = '')...

  11. 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 →
    JediSQL commented  · 

    I would like to be able to specify a local network location for my settings. That way, for my servers that are really firewalled away from the internet, I could still get to my settings.

    JediSQL supported this idea  · 
  12. 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 » Other  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL supported this idea  · 
  13. 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL shared this idea  · 
  14. 200 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    15 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL supported 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

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL shared this idea  · 
  16. 2 votes
    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 →
    JediSQL supported this idea  · 
    JediSQL commented  · 

    Jason,

    1) For this query:

    SELECT ID, ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Value) RowNum
    FROM table_with_key

    2) SQL Server happens to return this result set:

    ID RowNum
    ----------- --------------------
    1 1
    2 2
    3 3
    4 4
    5 1
    6 2
    7 3
    8 4

    3) Do you understand that this is also a complely correct, alternate result set for the query?

    ID RowNum
    ----------- --------------------
    4 1
    2 2
    1 3
    3 4
    7 1
    6 2
    5 3
    8 4

    4) Since it is, then you middle-level query is allowed to return the result set after it:

    SELECT ID
    FROM (
    SELECT ID, ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Value) RowNum
    FROM table_with_key
    ) S1
    WHERE RowNum = 1

    ID
    -----------
    4
    7

    5) And then your full query would return:

    Value ID
    ------ -------
    0 4
    1 7

    6) This is a valid result for the query you wrote. Is it a valid result for you business requirements?

    7) Since all possible combinations of ID and RowNum are possible at step (3), then all rows from the table are valid results. SQL Server has no way of knowing which one of the posibilities you actually wanted. Your expectation of result that is always based on (1, 5) is misplaced. You asked an ambiguous question, and the server's response covered all the possiblities. Maybe the bug is that the un-indexed table gives only two rows.

    JediSQL commented  · 

    It is weird that you "ORDER BY Value". There are only two values for the Value column.
    The SQL Standard allows a database engine to pick any order it wants when there are multiple rows for a sort value.

    SELECT ID, ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Value) RowNum
    FROM table_no_key

    For the above statement, since there is no order specified for ID, it is just happenstance that RowNum gets values generated in ID order; that is not guaranteed.

    In database theory, a database engine creates a full cross product of the potential join, and then removes rows that do not match the actual join and where conditions.

    Maybe SQL Server, with the keyed table, is generating a cross product that has all possible orderings of (PARTITION BY Value ORDER BY Value), leading to returning all data.

    If you change it to (PARTITION BY Value ORDER BY ID), then both queries return the same results. I'm guessing that getting the rows with the lowest ID per Value is what you want.

  17. 33 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 →
    JediSQL commented  · 

    BTW, I got the same results in SQL Server 2017 and SQL Server 2019

    JediSQL commented  · 

    I was able to reproduce with both table variables and real tables.
    If "INDEX c CLUSTERED (c1)" is removed, then the weird behavior does not occur.

    There is another case where absence or presence of an index changes results:
    https://feedback.azure.com/forums/908035-sql-server/suggestions/37823092-inconsistent-results-based-on-primary-key-columns
    Maybe these are caused by a related index flaw.

  18. 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 →
    JediSQL commented  · 

    This has been an annoyance for many years now, at least back to SQL Server 2008.

    JediSQL supported this idea  · 
  19. 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 » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL supported this idea  · 
  20. 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL commented  · 

    I guess it could be helpful to show both. For my SQL Server 2016 instances it would be useful to see (13.0 SP2 CU5).

    JediSQL shared this idea  · 
← Previous 1 3 4 5 6 7 8

Feedback and Knowledge Base