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

    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.

  2. 30 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.

  3. 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  · 
  4. 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 →
    JediSQL supported this idea  · 
  5. 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  · 
  6. 343 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    126 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL commented  · 

    Visual Studio 2019 Community Edition has a SQL debugger that, according to a developer on my team that uses the SSMS debugger, is the same as the SSMS debugger.

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

    Upvotes: 127

    <=-=Jun 23 2015 8:37AM=-=>

    I’m the first to post a useful comment. This must make me special.

    Seriously though, this would be an excellent solution to having to create a new “scratchdb” to hold my interim ETL data. This would be a major plus in simplifying design of a high performance app.

    <=-=Jul 3 2015 5:04AM=-=>

    In 2014, memory optimized tables, and delayed durability can be used help mitigate these Issues. However neither of this are always completely viable solutions. Brent’s proposed solution is likely the simplest way to achieve this with the least amount of unwanted impact. It is important to note that other platforms implement similar functionality as well. Notably Oracle.

    <=-=Nov 29 2016 3:58PM=-=>

    There are so many good things about this suggestion. I am amazed that SQL does not have the capability to turn off logging for certain tables that you define as no…

    JediSQL supported this idea  · 
  8. 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 shared 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

    0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL shared this idea  · 
  10. 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 →

    Upvotes: 8

    <=-=Oct 19 2016 4:13PM=-=>

    Thanks for your feedback Mark. A change for this is coming with SQL server 2016 SP1.

    -SSRS team

    <=-=Oct 19 2016 4:13PM=-=>

    Thanks for your feedback Mark. A change for this is coming with SQL server 2016 SP1.

    -SSRS team

    <=-=Jan 24 2017 3:17PM=-=>

    We have an SSRS 2016 SP1 Developer Edition instance running in native mode, and we still do not see descriptions on the report tiles.

    <=-=Aug 1 2017 1:39AM=-=>

    We are using SQL server 2016 SP CU3 and I don’t see any description on my reports, do you know if the change have been done ?

    JediSQL supported this idea  · 
  11. 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  · 
  12. 3 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL supported this idea  · 
  13. 8 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 agree. If diagrams are being removed from SSMS because diagrams are a "developer thing," then moving it to SSDT in the developer's Visual Studio tool makes a lot of sense.
    I would like any new tool to read & write existing sys.diagrams data. I have hundreds of diagrams in dozens of database per my organization's documentation policy. I really like the existing diagrams being connected to the database so that column changes in tables do not require someone to manually refresh the diagrams to keep them up to date.
    If we don't get something that can, at least, present sys.diagrams-based diagrams, then upgrading to SQL Server 2019 will result in Microsoft capriciously throwing away all that documentation I have generated over the last 16 years.

    JediSQL supported this idea  · 
  14. 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  · 
  15. 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  · 
  16. 292 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    Upvotes: 249

    <=-=Oct 4 2016 2:15PM=-=>

    This is similar to my feedback located here: https://connect.microsoft.com/SQLServer/feedback/details/2769130/sql-2016-temporal-tables-with-triggers. The solution I suggest is similar to this, and could be used in many different cases developing using SQL server.

    <=-=Jan 10 2017 9:43AM=-=>

    I would love this too. The issue is that many/most applications don’t use Windows Auth in the connection to SQL Server (connection pooling issues, etc), so SQL can’t get the User information

    <=-=Jan 10 2017 9:51AM=-=>

    @Sanford

    Just to clarify, I am not asking for an “automatically store user name” feature. I’m asking for “automatically store whatever I want, based on whatever expression I provide.” So the fact that the user name may or may not be available is not really relevant.

    That said, there are plenty of workarounds for various situations. I imagine that if you’re using a shared connection, you can get some notion of “user” some other…

    JediSQL commented  · 

    Now that I have used temporal tables in SQL 2017, I can see an implementation of this using the "GENERATED ALWAYS AS" key phrase introduced for temporal tables.

    JediSQL commented  · 

    @(Anonymous, June 07, 2018 03:58/03:57). You are missing the point. Since your MyTest table only has defaults, a malicious/errant developer can still force whatever data they want into the table. The point is to define the table so that ONLY SQL Server's internal processes can populate the values (much like an IDENTITY column). This way the database designer can fully enforce true, run-time values.

    JediSQL supported this idea  · 
    JediSQL commented  · 

    I have thought about this kind of thing a lot myself. There should be one expression for INSERT and one expression for UPDATE (and optional single expression for INSERT, UPDATE). In the constraint definition (or however it might be implemented), there should be an option where the column will IGNORE or REJECT any values explicitly provided to an INSERT or UPDATE statement. IGNORE would be like IGNORE_DUP_KEY where an inert warning is printed, and REJECT would cause a level 16 RAISERROR and block the statement. This would give the DBA full control and be used prevent errant/malicious code from creating deceptive audit data.

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

    With Office 365, if you let your paid subscription expire, Microsoft lets you continue to operate the applications in read-only mode to view the documents that you created while you were a paid subscriber.

    With SQL Server 2000 through SQL Server 2017, I have created hundreds of diagrams in dozens of databases as part of my organization's database documentation policy. With Microsoft discontinuing diagram support with SQL Server 2019/SSMS 18, Microsoft is essentially throwing away all my documentation without my consent.

    Please seriously consider implementing a documentation-only mode for database diagrams (as I have posted in "SSMS 2018 Database Diagrams - Display Only" https://feedback.azure.com/forums/908035-sql-server/suggestions/37442596-ssms-2018-database-diagrams-display-only ).

    JediSQL supported this idea  · 
  18. 91 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    53 comments  ·  SQL Server » Other  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL commented  · 

    With Office 365, if you let your paid subscription expire, Microsoft lets you continue to operate the applications in read-only mode to view the documents that you created while you were a paid subscriber.

    With SQL Server 2000 through SQL Server 2017, I have created hundreds of diagrams in dozens of databases as part of my organization's database documentation policy. With Microsoft discontinuing diagram support with SQL Server 2019/SSMS 18, Microsoft is essentially throwing away all my documentation without my consent.

    Please seriously consider implementing a documentation-only mode for database diagrams (as I have posted in "SSMS 2018 Database Diagrams - Display Only" https://feedback.azure.com/forums/908035-sql-server/suggestions/37442596-ssms-2018-database-diagrams-display-only).

    JediSQL commented  · 

    MS has not properly used its own language with this. Deprecated means planning for removal in a future release. Diagrams and debugging have been *discontinued* (without the usual MS advance notice via deprecation).

    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

    1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    JediSQL commented  · 

    With Office 365, if you let your paid subscription expire, Microsoft lets you continue to operate the applications in read-only mode to view the documents that you created while you were a paid subscriber.

    With SQL Server 2000 through SQL Server 2017, I have created hundreds of diagrams in dozens of databases as part of my organization's database documentation policy. With Microsoft discontinuing diagram support with SQL Server 2019/SSMS 18, Microsoft is essentially throwing away all my documentation without my consent.

    Please seriously consider implementing a documentation-only mode for database diagrams (as I have posted in "SSMS 2018 Database Diagrams - Display Only" https://feedback.azure.com/forums/908035-sql-server/suggestions/37442596-ssms-2018-database-diagrams-display-only).

    JediSQL shared this idea  · 
  20. 4 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 commented  · 

    I freshly installed SSMS 18.0 GA (15.0.18118.0) in Windows Server 2109 (Release ID 1809).

    I find:
    >> New query windows have no color set for their status bar, for both single and multiple connection windows.
    >> If I set colors for the two windows type (in the Tools -> Options dialog box), they work in the session where the color was set.
    >> When I exit and re-launch SSMS, the color settings are lost.

    I would say the issue still exists.

    I have used (Registered Servers -> Registered Server Node Context Menu -> Properties… -> Edit Server Registration Properties dialog box -> Connection Properties tab -> Use Custom color) to set my status bar colors for many years now. That still works properly. This is what I thought this feedback item was about (I did not read it carefully).

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

Feedback and Knowledge Base