Solomon Rutzky

My feedback

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

    We’ll send you updates on this idea

    8 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    planned  ·  Matteo Taveggia responded

    Thanks for the reporting the issue. We’ll take a look at it and prioritize accordingly.

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This is still an issue for SSMS v18.5 (SQL Server Management Studio - 15.0.18330.0)

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This is still an issue for SSMS v18.0 RC1 (SQL Server Management Studio 15.0.18098.0)

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This is still an issue for SSMS v18.0 Preview 7 (SQL Server Management Studio 15.0.18092.0).

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This is still an issue for SSMS v18.0 Preview 6 (SQL Server Management Studio 15.0.18075.0).

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This is still an issue for SQL Server Management Studio v17.9.1 (14.0.17289.0) and SSMS v18.0 Preview 5 (SQL Server Management Studio 15.0.18068.0).

    Solomon Rutzky supported this idea  · 
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This is still an issue in SSMS 17.5:

    SELECT 'a' COLLATE CATALOG_DEFAULT;

    That statement works just fine, but IntelliSense indicates that it is an invalid Collation.

    An error occurred while saving the comment
  2. 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 →
    Solomon Rutzky supported this idea  · 
  3. 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 →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Excellent suggestion. Though I would go one step further and request implementing the full timezone / DST history table so that all (or at least most) historical dates can be calculated correctly and reliably (similar to what you are doing in your "SQL Server Time Zone Support" project: https://github.com/mj1856/SqlServerTimeZoneSupport ).

    Solomon Rutzky supported this idea  · 
  4. 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 →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Additional context:

    The problem in its simplest form is with the following statement (taken from the column definition for "collation_name" as found via `EXEC sp_helptext N'sys.system_columns';` ):

    ```
    SELECT COLUMNPROPERTYEX(-593, N'physical_name', 'collation');
    ```

    Please also see:

    * Using metadata to troubleshoot metadata ( https://sqlblog.org/2019/12/18/metadata-meets-metadata )
    * Msg 6522, Level 16 but no error message selecting from sys.all_columns ( https://stackoverflow.com/a/45764557/577765 )

    I suspect the issue is either a missing or incorrect collation_id value in the hidden [mssqlsystemresource] database for that particular column of those two specific objects (i.e. IDs -593 and -103085222).

    Solomon Rutzky 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

    0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky shared this idea  · 
  6. 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 →
    Solomon Rutzky shared this idea  · 
  7. 2 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
    Solomon Rutzky commented  · 

    Hello again. I finally found the documentation:

    • "nvarchar concatenation / index / nvarchar(max) inexplicable behavior" ( https://stackoverflow.com/a/15163136/577765 )

    • "Concatenating strings in SQL Server, or undefined behaviour by design" ( https://marc.durdin.net/2015/07/concatenating-strings-in-sql-server-or-undefined-behaviour-by-design/ ) -- be sure to read through the comments on this one

    • "Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location" ( https://web.archive.org/web/20140724144829/http://support.microsoft.com/kb/287515 )

    A comment from "Michele" on the second / middle link indicates that the following should do the trick on SQL Server 2017 or newer:

    -------------------------------
    SELECT STRING_AGG(ec.Description, ',') WITHIN GROUP (ORDER BY ec.SortIndex)
    FROM BadConcat ec
    WHERE ec.BadConcatID in (1, 2, 3);
    -------------------------------

    Take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

    Solomon Rutzky supported this idea  · 
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Hi @Ralf . I don't _think_ this is actually a bug. Sure, I get that this type of string concatenation does not always yield the expected result, but I seem to recall reading in a couple of other places that this type of string concatenation (i.e. using "+" or "+=" in a `SELECT` statement) was _not_ officially supported. Meaning, if it works, then great, but if not, then it wasn't something they were going to fix. I can't find any mention of that in the documentation ( https://docs.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-equal-transact-sql and https://docs.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-transact-sql ), but I remember seeing a discussion of it somewhere.

    P.S. I am voting for this issue, but only because this God-awful UserVoice feedback system provides no other way to get notifications of additional comments.

  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 →
    Solomon Rutzky shared this idea  · 
  9. 17 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 →
    Solomon Rutzky supported this idea  · 
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Hello Greg. Regarding your statement of:

    > When using UTF-8 in SQL Server 2019 and later, specifying a column as varchar(10) means that it can store up to 10 bytes internally within the database, not up to 10 characters.

    That is not exactly correct. The `n` in `VARCHAR(n)` has always referred to "bytes" and not "characters". The confusion is mostly likely due to one "character" always being one "byte" _PRE_-SQL Server 2000. Starting in SQL Server 2000, Windows collations were added, some of which were Double-Byte Character Sets (DBCS) that can store some 1-byte characters, and many 2-byte characters. Many of us (especially in the "West") don't encounter these code pages / character sets that often, but those DBCS code pages include Chinese, Japanese, and Korean, which is not insignificant in their potential usage.

    Microsoft has an incredible amount of documentation, so sometimes certain technical details such as "characters" vs "bytes" don't get updated.

    For a complete description of "characters" vs "bytes" across the various string datatypes, please see:

    "How Many Bytes Per Character in SQL Server: a Completely Complete Guide" ( https://sqlquantumleap.com/2019/11/22/how-many-bytes-per-character-in-sql-server-a-completely-complete-guide/ )

    And, for a thorough analysis of the new UTF-8 collations, please see:

    "Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?" ( https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/ )

    P.S. I am voting for this as this horrible UserVoice system provides no other mechanism to subscribe to comments. However, I am not sure that I support the idea given the possible negative consequences to storage and query performance. When defining a table, SQL Server needs to know the max number of bytes that could be stored there. This value is stored internally in the meta-data for each column, and is used when determining max row size and calculating memory grants for queries. Since UTF-8 could be up to 4 bytes per character (for all Supplementary Characters, same as with UTF-16 / NVARCHAR), should specifying 10 "characters" equate to 40 bytes? So then the max would be `VARCHAR(2000 characters)` to stay within the 8000 byte limit? Currently you can create a table with up to 8 `VARCHAR(1000)` columns, but you would then only be able to use at most 4 `VARCHAR(2000 characters)` columns, right? I guess that's not so bad. But what about memory grants? I believe SQL Server estimates 50% fill on variable-length datatypes, so this would impact performance on some systems. This part is a bit tricky, which is why it's problematic to use `VARCHAR(MAX)` and/or `NVARCHAR(MAX)` for all string columns, even if the data is always small enough to fit on the data page. I'm not saying that this can't work, it's just not as simple as it appears to be at first glance. It gets even more complicated when dealing with languages that use multiple code points to form a single displayable character via combining characters. Either way, Oracle, which supports `(n BYTE)` and `(n CHAR)` for string type sizes, discusses some of this conflict here:

    https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#i45694

    Take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

  10. 259 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    17 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
  11. 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    So, 4.5 years later and I just figured out _why_ parameters using the LOB types — `NVARCHAR(MAX)`, `VARBINARY(MAX)`, and `XML` — are not allowed to have default values in SQLCLR modules:

    1. Unlike default values for parameters in T-SQL modules (which are parsed out of the module definition for some strange reason), default values for parameters in SQLCLR modules are stored in the `default_value` field in `sys.parameters`.
    2. The `default_value` field in `sys.parameters` is a `SQL_VARIANT` datatype.
    3. The `SQL_VARIANT` datatype cannot store / contain the LOB types

    OK, so that makes sense in terms of those datatypes not cleanly fitting into the `SQL_VARIANT` column where the parameter values are stored. HOWEVER, we still do absolutely need support for parameter defaults for those 3 datatypes, so how about we just accept a _limited_ default value. Meaning, the actual need for > 8000 bytes for a default value is, at most, rare if not actually "never" in reality. So, just store whatever default value is provided as long as it's <= 8000 bytes. Any value that is > 8000 should throw an error stating that the maximum size for a default value is 8000 bytes, even if the datatype itself can store more. Outside of unit tests intended to verify this behavior, I don't think anyone will ever receive this error. Typically the default values are either NULL, empty string, or short values. I'm not sure anyone would ever notice the 8000 byte limitation.

    So, fairly easy to implement, correct? Just need an additional IF condition for SQLCLR T-SQL wrapper objects to check if value is > 8000 bytes, and if true, then raise the error, else proceed as normal given that the <= 8000 byte value will fit just fine.

    PLEASE!!!!!

    Thanks 😺 and take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

    An error occurred while saving the comment
    Solomon Rutzky supported this idea  · 
  12. 56 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 →
    Solomon Rutzky supported this idea  · 
  13. 5 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 →
    Solomon Rutzky supported this idea  · 
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This is very similar to a request I made a couple of years ago and received less-than-ideal feedback on:

    Add "type" and "type_desc" fields to sys.databases DMV (to make life easier on EVERYONE) ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32897959-add-type-and-type-desc-fields-to-sys-databases )

    I was hoping to go one step further than just knowing that it was a "MS Shipped" DB: to also know a basic "type" of DB, whether it was system, or demo, or feature-specific (e.g. for SSIS, SSRS, Replication, etc).

    Perhaps my request was too complicated and this one might be easier to accomplish. In either case, something/anything is better than the current nothing :-).

  14. 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 →
    Solomon Rutzky 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

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky 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

    1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Agreed. And, the Remediation "Description" for this assessment has DB_CHAINING misspelled as "DB_CHANING" (for both QueryValidatedRulesV2 and BackEndQueryValidatedRules). If it helps, "cross db ownership chaining", VA2120, is marked as "high".

    Solomon Rutzky supported this idea  · 
  17. 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 →
    Solomon Rutzky supported this idea  · 
  18. 542 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    27 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
    Solomon Rutzky commented  · 

    Regarding the comment by @Anonymous (immediately below):

    NO!!! Please do 𝘯𝘰𝘵 use a multi-statement TVF (especially using a WHILE loop). There have been numerous articles / posts / etc comparing various string splitting methods in SQL Server, and the WHILE loop is one of the slowest, if not 𝘵𝘩𝘦 slowest.

    If you can't or won't use the built-in STRING_SPLIT, then use one of the following two choices:

    1) Pure T-SQL: "Tally OH! An Improved SQL 8K “CSV Splitter” Function" (be sure to see related discussion) ( https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function )

    2) SQLCLR: there are many examples if you want to code your own, or you can simply download/install SQL# ( https://SQLsharp.com?ref=fa_32902852 ), which I wrote, as the Free version contains: String_Split and String_SplitInts. Use "SplitInts" if you are parsing a list of INTs as it is optimized for that. And, if your list is guaranteed to be <= 4000 characters (I am simplifying here for practicality), then use the "4k" version as that is also optimized for non-MAX data.

    Take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

    Solomon Rutzky supported this idea  · 
  19. 7 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    5 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 3

    <=-=Dec 28 2016 7:28PM=-=>

    Thank you for submitting this feedback. We have reproduced the behavior for certificate objects that you’ve described and are now investigating the appropriate resolution. We will update this Connect item as additional information is available.

    Thanks again!
    Steven Green
    SQL Server tools team

    <=-=Feb 21 2017 12:04PM=-=>

    I have this issue as well. It makes creating a homogenous test environment amongst our development team more difficult.

    <=-=Feb 21 2017 12:09PM=-=>

    Ref: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/04dd415c-047e-421d-97fe-038328b54a9a/create-certificate-from-binary?forum=ssdt

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Please see (and vote for) related issue regarding Asymmetric Keys:

    "Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32896978-allow-asymmetric-key-to-be-created-from-binary-hex?tracking_code=59a149c86262ef9e840b5b17bcee2c89 )

    Take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

    Solomon Rutzky 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

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Hi there. I believe this is just an issue with very old versions of collations. You are using the version 80 collations that came with SQL Server 2000. Unless needed for backwards compatibility, anyone using SQL Server 2008 and newer should be using _at least_ the version 100 collations, and for Japanese, the version 140 collations if using SQL Server 2017 or newer.

    The query below, adapted from your original query, shows that the issue was fixed in the version 100 collations:

    -----------------------------------------------------------------
    SELECT
        REPLACE(N'Chachach' COLLATE Czech_CS_AS, N'ጠ', N'#') AS [Czech_CS_AS],
        REPLACE(N'Chachach' COLLATE Czech_100_CI_AI, N'ጠ', N'#') AS [Czech_100_CI_AI],
        REPLACE(N'Chachach' COLLATE Slovak_CS_AS, N'ጠ', N'#') AS [Slovak_CS_AS],
        REPLACE(N'Chachach' COLLATE Slovak_100_CI_AI, N'ጠ', N'#') AS [Slovak_100_CI_AI];
    /*
    Czech_CS_AS  |  Czech_100_CI_AI  |  Slovak_CS_AS  |  Slovak_100_CI_AI
    #hachach       |  Chachach             |  #hachach         |  Chachach
    */
    -----------------------------------------------------------------

    No need to show the binary collation as that will always match code points to only themselves. Also, it is best to use the "_BIN2" collations instead of the older and not entirely correct for sorting "_BIN" collations.

    Please see the following post of mine as it has info relevant to this issue that might help explain some things, especially sections 2 (Different Versions) and 3 (Different Binary Comparison Types):

    "Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2)" ( https://sqlquantumleap.com/2019/03/13/differences-between-the-various-binary-collations-cultures-versions-and-bin-vs-bin2/ )

    This bug report can probably be closed.

    Take care,
    Solomon...
    https://SqlQuantumLift.com/
    https://SqlQuantumLeap.com/
    https://SQLsharp.com/

    P.S. I'm only voting for this suggestion to get notifications because it's the only mechanism that this horrible UserHasNoVoice platform provides.

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

Feedback and Knowledge Base