Solomon Rutzky

My feedback

  1. 8 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 →

    Upvotes: 24

    <=-=Jan 4 2011 4:40PM=-=>

    Hi Bob,

    Thanks for the feedback. We’ll consider fixing this in a future release. Can you tell me more about the scenario? What UDAgg were you implementing and why? Feel free to contact me by email if you want.

    Best regards,
    Eric Hanson
    Program Manager, SQL Server Query Processing
    eric.n.hanson@microsoft.com

    <=-=Jan 4 2011 5:15PM=-=>

    Sure Eric, there are a few that I can think of. This actually came about because of the following forum question:
    http://social.technet.microsoft.com/Forums/en-US/sqlnetfx/thread/957a5b94-c7d0-49d8-928d-7cccff14b0c6. I realized that the sort was required because of choice of stream aggregate. And he can’t put on every index possible to get rid of the sort.

    Second is that the spatial aggregates in Denali would need this funtionality. Related to that is that there’s a vendor product that consists of a library of UDAs, Fuzzy Logix (http://www.fuzzyl.com/in-database_analytics.php#) that could benefit from this flexibility as…

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This is still an issue in SQL Server 2017 CU12 and SQL Server 2019 CTP 2.2 😿.

    Solomon Rutzky supported this idea  · 
  2. 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
    Solomon Rutzky commented  · 

    Hi there. Can someone _please_ take a look at this? It has been 3.5 years since it was reported, and while there is clearly not much (or any) resources being devoted to improving SQLCLR, this fix should be _very_ low risk, if not no risk. It is merely adding a dictionary entry (i.e. "ALLOW_LOCALDB_IN_PARTIAL_TRUST") to the App Domain setup for all App Domains, or at least all user App Domains. This change doesn't _do_ anything. It is just a simple entry that will not even been seen by 99.99% of functionality. This isn't code, just configuration. I can't think of a single reason to not add this entry. It clearly was something that was intended to be added but was forgotten about. This is a very simple change that would greatly improve the usability of SQL Server Express LocalDB, and that's a good thing, right? 8-D, Please???? With a new version (2019) coming out soon, this is the perfect time to do this. Please??????

    An error occurred while saving the comment
    Solomon Rutzky supported this idea  · 
  3. 5 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 →
    planned  ·  Matteo Taveggia responded

    Thanks for filing this request. We’ll take a look at it and prioritize.

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

    Hello Erland. I have been able to reproduce this, and have some additional details to share that might help the SQL Server team locate and fix the issue. It certainly seems to be a code-path intended to verify some aspect(s) of the environment, based on the fact that the input file isn't even required to exist in order to get this error (though if it does exist, then it will be checked to verify if it has a Byte Order Mark (BOM) in order to determine if the "DataFileType" setting is correct, and if not it will tell you how completely wrong you are and that it is smarter and will do whatever it was going to do anyway). This bug is also present in "OPENROWSET(BULK...)". BCP (which uses ODBC v13) works just fine: "-w" for UTF-16 files, and "-c -C 65001" for UTF-8 files.

    -------------------------------------------------------------------------------
    CREATE TABLE #BulkInsertFail ([Col1] ROWVERSION); -- wouldn't work anyway
    BULK INSERT #BulkInsertFail FROM 'bob';
    /*
    -- I only get this first error on SQL Server 2019 CTP 2.2
    Msg 4860, Level 16, State 1, Line XXXXX
    Cannot bulk load. The file "bob" does not exist or you don't have file access rights.

    -- I get this error on both SQL Server 2017 and 2019
    Msg 2775, Level 17, State 12, Line XXXXX
    The code page 65001 is not supported by the server.
    */

    -- The following file has a BOM indicating UTF-16LE, read correctly by Notepad++
    BULK INSERT #BulkInsertFail FROM 'C:\TEMP\UTF16LE-Import.txt';
    -- The code page 65001 is not supported by the server.

    SELECT * FROM OPENROWSET(BULK 'bob', SINGLE_BLOB ) tab(col);
    -- The code page 65001 is not supported by the server.
    -- Using SINGLE_CLOB or SINGLE_NCLOB did not help

    -- The following file has a UTF-8 BOM, but it doesn't help
    BULK INSERT #BulkInsertFail FROM 'C:\TEMP\UTF8-Import.txt'
    WITH (DATAFILETYPE = 'Char', CODEPAGE = '65001');
    -- The code page 65001 is not supported by the server.
    -------------------------------------------------------------------------------

    And for BULK INSERT, I did try every combination of DATAFILETYPE = 'CHAR' | 'WIDECHAR' | 'NATIVE' | 'WIDENATIVE' and/or CODEPAGE = 'ACP' | 'OEM' | 'RAW' | '1252' | '65001'. Yes, several of those options wouldn't work anyway; I was just trying to see if I could cause a change in behavior. But, the only change was merely that sometimes I would get an informational message along the lines of: "Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature." All this proves is that the FileType verification happens before the environmental check that blows up.

    I tested on:
    1) Microsoft SQL Server 2019 (CTP2.2) - 15.0.1200.24
    2) Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24

    And just FYI: SQLCLR file system access does correctly default to UTF-8, can correctly detect the encoding from the BOM (if present), and can use any specified encoding if no BOM is present.

  5. 8 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    Aaron, based on the two links found in the FAQ that you linked to, it is unclear what constitutes a "long string". Nothing that I have read there indicates any "recommended" upper-limit to the effectiveness of SCSU as compared to a general purpose method. Still, I can say that:

    1) I don't think the actual algorithm matters, as long as the compression is transparent and doesn't come with much, if any, performance hit (overall)

    2) One strength of SCSU, and a related downside to using many other algorithms, is that SCSU is designed to _also_ work well with short strings as it does not have the compression overhead that some other algorithms have (such as GZIP, which is what COMPRESS() uses). For example:

    SELECT COMPRESS('a');
    -- 0x1F8B08000000000004004B040043BEB7E801000000

    3) even if SCSU (which is already implemented in the code since it works for non-MAX NVARCHAR) were extended only for in-row NVARCHAR(MAX), leaving off-row data for a separate project, we would still be better off than we are today (with no compression for NVARCHAR(MAX)), and better off than using UTF-8 for compression (against Unicode recommendations).

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Yes, this would be wonderful! Thanks for suggesting it.

    On a relate note, 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/ ) for a detailed analysis of the new UTF-8 feature.

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

    We’ll send you updates on this idea

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

    FYI: the bug report for the new errors in the updated SQL Server 2016 LocalDB is:

    https://feedback.azure.com/forums/908035-sql-server/suggestions/36486376-sqllocaldb-utility-2016-returns-formatmessagew-fa

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Thanks Brendon.

    I just checked again and this appears to have been fixed in a recent update. Versions 13.0.5026.0 and 14.0.3045.24 both report the full list of installed versions without any errors, and without me having to edit the registry to change 13.0 to 13.1 under InstalledVersions. I now get back the following:

    Microsoft SQL Server 2012 (11.0.7462.6)
    Microsoft SQL Server 2014 (12.0.6024.0)
    Microsoft SQL Server 2016 (13.0.5026.0)
    Microsoft SQL Server 2017 (14.0.3045.24)

    Of course, version 13.0.5026.0 (i.e. SQL Server 2016) is now broken in other ways (ways that it was not broken before). It cannot display the help info, error messages, or even output from using the "i {instance_name}" option. I guess I will open a separate bug for that :-(.

    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

    started  ·  2 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Here are some examples showing different ways that can produce what you are seeing:

    ---------------------------------------------------------------------
    SELECT CONVERT(VARBINARY(20), N'Łódź') AS [UTF-16];
    -- 0x4101F30064007A01 (UTF-16)

    SELECT CONVERT(VARBINARY(20), 'Łódź' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [UTF-8];
    -- 0xC581C3B364C5BA (UTF-8)
    SELECT CONVERT(NVARCHAR(20), 0xC581C3B364C5BA) AS [UTF-8bytesAsUTF-16];
    -- 臅돃앤º

    SELECT CONVERT(VARBINARY(20), 'L?dz' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [UTF-8];
    -- 0x4C3F647A (UTF-8)
    SELECT CONVERT(NVARCHAR(20), 0x4C3F647A) AS [UTF-8bytesAsUTF-16];
    -- 㽌穤

    DECLARE @CP1252bytes VARBINARY(20) = 0x655DE18593E7A9A433E39398;
    DECLARE @ViewTheBytes TABLE
    (
    CP1252 VARCHAR(20) COLLATE Latin1_General_100_CI_AS_SC,
    UTF8 VARCHAR(20) COLLATE Latin1_General_100_CI_AS_SC_UTF8
    );
    INSERT INTO @ViewTheBytes VALUES (@CP1252bytes, @CP1252bytes);
    SELECT * FROM @ViewTheBytes;

    -- CP1252: e]ᅓ穤3㓘
    -- UTF8: e]ᅓ穤3㓘
    ---------------------------------------------------------------------

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Hi Erland. I don't see an attached repro script so I can't see exactly what you are seeing (if you can send it to me that would be great: https://sqlquantumleap.com/contact ).

    Question: is the database's default collation UTF8 or non-UTF8? That _might_ have some impact here.

    The Asian characters (not always Chinese, and not even always Asian) are most likely the result of the UTF-8 bytes being interpreted as if they were UTF-16. If you provide the characters that you are seeing and also what you expected to see, I can confirm or clarify. It is also possible to get mangled text by reading Extended ASCII code page data as if it were UTF-8, which would give a mix of characters that look correct and potentially some that don't. Whatever combination of bytes and incorrect interpretation is causing this, it is a phenomenon commonly referred to as mojibake ( https://en.wikipedia.org/wiki/Mojibake ).

    Also, keep in mind that the font being used by the command prompt window in which you are running SQLCMD plays a role in how any Unicode output is displayed. Different fonts display different ranges of characters, and even for the characters that a particular font does have a mapping for, there is no guarantee that BIDI and/or combining properties are implemented correctly (or at all).

    Unfortunately, this issue is yet another example of the huge potential of unintended consequences resulting from sticking 1 - 4 byte Unicode data into a datatype that previously only allowed non-Unicode, 1 - 2 byte data. Rather than spending more developer time on destabilizing the SQL Server code base in order to get this to work, the UTF-8 collations should be fully removed and any additional time should be devoted to implementing Unicode Compression for the NVARCHAR(MAX) datatype ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32686957-unicode-compression-nvarchar-max ). That would provide far greater benefit to many more customers, and with fewer drawbacks.

    I have added this issue to the CTP 2.2 Update section of my UTF-8 analysis: https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/#UpdateCTP22

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

    We’ll send you updates on this idea

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

    Hi Erland. I think this bug is misstated. Given that:

    a) there are no binary UTF-8 collations ( https://feedback.azure.com/forums/908035-sql-server/suggestions/35583976-utf-8-in-sql-2019-binary-utf-8-collations-are-mis )

    and

    b) a BIN2 collation is required for AE Deterministic encryption ( https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017#feature-details )

    I would say that the bug is that the Always Encrypted wizard allows you to select a VARCHAR column using a UTF8 collation. There is a minor, related documentation bug since this is not explicitly stated as being a known restriction in the SQL Server 2019 Release Notes ( https://docs.microsoft.com/en-us/sql/sql-server/sql-server-ver15-release-notes?view=sql-server-ver15#utf-8-collations ). There are a few things missing from that list, actually.

    I have not tested this yet myself, but if you are allowed to do deterministic encryption on an NVARCHAR column using a UTF8 collation because it would pick the BIN2 version of that collation, that makes sense as it is the same change as it would have been if the original collation was Finnish_Swedish_100_CS_AS_SC (going along with your example).

    However, in your example, I believe it is incorrect to say that it's expected to go from Finnish_Swedish_100_CS_AS_SC_UTF8 to Finnish_Swedish_100_BIN2 since that would force a code page conversion (from 65001 to 1252) which could easily require change the bytes in those columns (prior to encryption).

    The resolution here would be to:

    1) update SSMS to disallow VARCHAR columns using a UTF8 collation from applying a deterministic encryption, and

    2) if / when UTF-8 BIN2 collations are released, then update SSMS again to once again allow selecting VARCHAR columns using a UTF8 collation for deterministic encryption (assuming, of course, that the UI correctly chooses the UTF8 BIN2 collation--and hopefully there is only 1 of those so hopefully that will be an easy choice ;-)

    I have added this issue to the CTP 2.2 Update section of my UTF-8 analysis: https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/#UpdateCTP22

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

    Hi there. This bug appears to be fixed in CTP 2.2, and might have even been fixed in CTP 2.1. What is certain is that I have tested this on CTP 2.2 and the query does not hang. I suspect that this issue is a duplicate of what I reported here:

    https://feedback.azure.com/forums/908035-sql-server/suggestions/35606671-utf-8-in-sql-2019-an-error-occurred-while-executi

    The issue had to do with NULL values in VARCHAR / CHAR columns using the new "_UTF8" collations. In your test, there are two such columns that have NULL values:

    [ExecutionGroupID] [varchar](255)
    [TERMINAL_ID] char(25)

    This issue can most likely be closed as "Resolved".

  10. 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  · 

    Actually, I am not sure why the "collate catalog_default" clause was added to the CREATE temp table statement. I had thought that it was to fix potential issues when used within Contained databases, but this can't be since:

    a) the instance-level meta-data (i.e. syslogins.loginname) is still in the original instance-level collation (nothing changed there for Contained DBs ; it's _not_ CATALOG_DEFAULT) so this system stored proc only works within Contained DBs _if_ the instance-level collation is Latin1_General_100_CI_AS_KS_WS_SC (which is what CATALOG_DEFAULT equates to within Contained DBs), and

    b) Windows Logins and Groups that are direct to a Contained DB (without having a Login defined mapped in SQL Server) are actually Users, not Logins, so there is nothing to return from this proc even if the instance-level collation is Latin1_General_100_CI_AS_KS_WS_SC (yes, I verified this in a Contained DB on an instance using Latin1_General_100_CI_AS_KS_WS_SC as the instance-level collation).

    All that being said, implementing my suggestion of an INSTANCE_DEFAULT pseudo-collation (option #3 in the main post), and using that in the CREATE temp table statement, is still the best, most reliable, most flexible solution for this problem and any others dealing with instance-level object names (or non-name strings, but I think most use-cases deal with names).

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

    4 comments  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    This is still an issue in SQL Server 2019 (CTP2.2) - 15.0.1200.24

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    So, I found some non-"@@" functions that also cause this issue. I tested the following on the same two SQL Server versions mentioned in the previous comment, except that I did not test "CURRENT_TRANSACTION_ID()" on SQL Server 2012 as it was not available in that version.

    -- The following all return 1 for XACT_STATE() when there is no active transaction:
    SELECT XACT_STATE(), CURRENT_REQUEST_ID();
    SELECT XACT_STATE(), CURRENT_TRANSACTION_ID()

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    I tested the following on SQL Server 2017 Developer CU7 (14.0.3026.27) and 2012 Express Local DB SP4-GDR (11.0.7462.6), both returning the same results.

    -- The following all return 1 for XACT_STATE() when there is no active transaction:
    SELECT XACT_STATE(), @@CONNECTIONS;
    SELECT XACT_STATE(), @@CPU_BUSY
    SELECT XACT_STATE(), @@DATEFIRST;
    SELECT XACT_STATE(), @@DBTS;
    SELECT XACT_STATE(), @@DEF_SORTORDER_ID;
    SELECT XACT_STATE(), @@DEFAULT_LANGID;
    SELECT XACT_STATE(), @@IDENTITY;
    SELECT XACT_STATE(), @@IDLE;
    SELECT XACT_STATE(), @@IO_BUSY;
    SELECT XACT_STATE(), @@LANGID;
    SELECT XACT_STATE(), @@LANGUAGE;
    SELECT XACT_STATE(), @@LOCK_TIMEOUT;
    SELECT XACT_STATE(), @@MAX_CONNECTIONS;
    SELECT XACT_STATE(), @@MAX_PRECISION;
    SELECT XACT_STATE(), @@MICROSOFTVERSION;
    SELECT XACT_STATE(), @@NESTLEVEL;
    SELECT XACT_STATE(), @@OPTIONS;
    SELECT XACT_STATE(), @@PACK_RECEIVED;
    SELECT XACT_STATE(), @@PACK_SENT;
    SELECT XACT_STATE(), @@PACKET_ERRORS;
    SELECT XACT_STATE(), @@PROCID;
    SELECT XACT_STATE(), @@REMSERVER;
    SELECT XACT_STATE(), @@SERVERNAME;
    SELECT XACT_STATE(), @@SERVICENAME;
    SELECT XACT_STATE(), @@SPID;
    SELECT XACT_STATE(), @@TEXTSIZE;
    SELECT XACT_STATE(), @@TIMETICKS;
    SELECT XACT_STATE(), @@TOTAL_ERRORS;
    SELECT XACT_STATE(), @@TOTAL_READ;
    SELECT XACT_STATE(), @@TOTAL_WRITE;
    SELECT XACT_STATE(), @@VERSION;

    There might be scenarios beyond these internal variables / functions that cause XACT_STATE() to erroneously return 1, but so far I have only tested all available "@@" variables / functions.

    Solomon Rutzky supported this idea  · 
    An error occurred while saving the comment
  12. 9 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 » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    To be clear, a "BeforeSqlBuildEvent" target that occurs just prior to the "SqlBuild" target would fit within the current naming convention and allow for signing DLLs prior to them being grabbed as input for generating either the _Create script or the incremental deployment script. The current "PreDeployEvent" that is called prior to the "SqlDeploy" target works for the incremental deployment script only, it is called too late in the process to help with the _Create.sql script.

    Also, breaking up the "SqlDeploy" such that an event exists between the creation of the incremental deployment script and the execution of it ("BeforeSqlDeployment" or "BeforeSqlDeployExection") would also help in making last-chance modifications to the incremental deployment script (or anything else, really) before it is executed on SQL Server.

    Thanks.

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

    Also, I just noticed that in the SQL Server 2019 installer UI, there are buttons for:

    "< Back" and "Next >"

    which are in the correct, intuitive order.

    Solomon Rutzky shared this idea  · 
  14. 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 →
    Solomon Rutzky shared this idea  · 
  15. 11 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 » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 10

    <=-=Aug 6 2008 10:15AM=-=>

    Hello,

    Thank you for sharing your ideas. As you say this one is somewhat less revolutionary than not having a default collation at all; you’re also right that the major obstacle in doing it is a back-compat. That being said the idea is still being discussed. I will keep you informed on the results of the discussion.

    Thanks,
    Krzysztof Kozielczyk

    <=-=Aug 6 2008 12:07PM=-=>

    I would be intrigued to know, what exactly is this backwards compatibility issue about? The only I can think of is existing scripts for unattended install, but I would expect such scripts to require modifications for new versions of SQL Server anyway. Is there something I have overlooked. I would appreaciate if you could details this, either here, or via private note over email.

    <=-=Mar 17 2011 11:24AM=-=>

    Hello Erland,

    Thank you for submitting this suggestion, but we’re trying to…

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

    PLEASE move forward with this request. It is absolutely absurd that even with SQL Server 2019 in CTP 2.1 at the moment, the default Instance-level Collation for systems with an English OS locale is still SQL_Latin1_General_CP_CI_AS. That is a horrible Collation. There is no _good_ reason for not using the most recent equivalent Windows Collation: Latin1_General_100_CI_AS_SC (the _SC option to support Supplementary Characters came out with SQL Server 2012 and this issue was originally submitted in 2008).

    Yes, there will be some minor backwards compatibility issues, but only for instances that are having existing DBs restored to them that already have the old SQL Server Collation. But that is a problem that diminishes over time as new projects adopt the newer (and much better) default Collation. By keeping with the same old SQL_Latin1_General_CP1_CI_AS Collation, Microsoft is ensuring that these problems INCREASE over time, making it harder and harder to move away from this old default as more and more projects are created with it because people didn't know better and that they should have chosen at least Latin1_General_100_CI_AS_SC if not some other Windows Collation that is more appropriate to their needs.

  16. 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 →

    Upvotes: 88

    <=-=Aug 8 2008 11:02AM=-=>

    Hello,

    Thank you for sharing your ideas with us. We are aware of this limitation of SQL Server 2008 and we’re considering fixing it in the next release.

    Thanks,
    Krzysztof Kozielczyk

    <=-=Feb 1 2012 2:24AM=-=>

    Hi, will this be a feature of the SQL Server 2012?

    <=-=Apr 16 2012 7:18AM=-=>

    I’ve just tried out the ALTER DATABASE on SQL Server 2012 RTM and the issue still exists. Any computed columns in the database still prevent the database collation from being changed.

    <=-=May 8 2012 5:48PM=-=>

    Any chance of changing “ALTER DATABASECOLLATE …” to have three options?
    1. NEW
    the current behaviour – new columns have the new collation
    2. REPORT
    change database collation and report columns that now have different collation
    3. ALL
    change database collation and all column collations in that database, dealing with any pesky constraint issues.

    Many users…

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    There is a fairly easy way to change the Collations of the instance, and all databases, and all columns (well, most): the undocumented "sqlservr.exe -q" method.

    I have a very detailed write-up of this method, including what it does and doesn't do, and what the ramifications can be for changing the collation at various levels, especially for VARCHAR data that can have the Code Page changed yet not go through a Code Page conversion, _potentially_ corrupting data (depends on several factors). Please see:

    Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? ( https://sqlquantumleap.com/2018/06/11/changing-the-collation-of-the-instance-and-all-columns-across-all-user-databases-what-could-possibly-go-wrong/ )

  17. 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 →

    Upvotes: 7

    <=-=May 27 2006 2:47PM=-=>

    More generally, the request would be to be able to create indexes on expresions.

    But it just so happens that this is possible today:

    CREATE TABLE mycollatedtable (a varchar(20) COLLATE Finnish_Swedish_CS_AS NOT NULL,
    b AS a COLLATE Finnish_Swedish_CI_AI PERSISTED)
    go
    CREATE INDEX CS_AS_ix ON mycollatedtable(a)
    CREATE INDEX CI_AI_ix ON mycollatedtable(b)
    go
    INSERT mycollatedtable (a) VALUES (‘WWW’)
    go
    SELECT * FROM mycollatedtable WHERE a = ‘VVV
    SELECT * FROM mycollatedtable WHERE b = ‘VVV
    go
    DROP TABLE mycollatedtable

    <=-=May 28 2006 6:17AM=-=>

    Erland,

    I agree that building an index on a computed column is an option but, generally speaking, a bad one. It changes the schema and can break existing applications. When dozens of applications interact with an enterprise system, allowing the physical implementation to cascade to the logical is bad. As bas as old file based…

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Yes, it would be nice to add the optional COLLATE keyword to CREATE INDEX / ALTER INDEX.

    It doesn't need to be that this request is turned into allowing for creating an index on an expression as that is something different, even if there is some overlap since an expression can have the COLLATE keyword applied. Simply allowing for swapping the Collation of the column for one specified in the CREATE / ALTER INDEX statement seems like a natural fit that is hopefully not too complicated :-).

    Solomon Rutzky supported this idea  · 
  18. 0 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
    Solomon Rutzky commented  · 

    This request is a complete duplicate of the following item and can be closed:

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32892337-collation-in-indexes

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    There is an easy enough work-around for this: create a non-persisted computed column that applies COLLATE to some column, and index the non-persisted computed column. For example:

    ALTER TABLE SchemaName.TableName
    ADD [ColumnNameFrench] AS ([ColumnName] COLLATE French_100_CI_AS_SC);

    CREATE NONCLUSTERED INDEX [IX_TableName_ColumnNameFrench]
    ON SchemaName.TableName ([ColumnNameFrench] ASC);

  19. 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 →

    Upvotes: 2

    <=-=Mar 28 2011 3:03PM=-=>

    Hello,
    Thanks for reporting the issue. We are investigating the problem and will let you know what we find.


    Umachandar, SQL Programmability Team

    <=-=Jul 7 2011 7:25PM=-=>

    Hi,

    We have looked at the issues and you are right in both of your observations.

    For issue number 1, we agree that the current behavior is not the best, but at the same time, it is dangerous for us to change it as there might be other customers who depend on this behavior (possibly even without knowing it), so we are hesitant to change this. As a workaround, you can explicitly specify the collation when using the variables to avoid depending on the collation from the database, like this:

    IF @var collate = ‘something’ SELECT 1 ELSE SELECT 2

    For issue 2, we agree that IS [NOT] NULL comparison should not require collation…

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

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

    Hi Patrick. I never said that the version 100 Collations fix everything, just that they were much improved over the previous versions. You could try the newest set, the version 140 Collations, that are only for Japanese. The version 100 Collations find 5840 characters matching nothing / empty string, yet Japanese_XJIS_140_CI_AS only found 3375.

    An error occurred while saving the comment
    Solomon Rutzky commented  · 

    Hi Patrick. There is actually no bug here, at least not anymore. This issue was solved when SQL Server 2008 was released, which introduced the version 100 collations (e.g. Latin1_General_100_CI_AS). Those collations added sort weights to A LOT of characters that previously had no sort weight assigned to them.

    The following examples prove this:

    -----------------------------------------------------------
    SELECT 1 WHERE NCHAR(0x3358)='' COLLATE Korean_Wansung_CI_AS; -- 1
    SELECT 2 WHERE NCHAR(0x33ff)='' COLLATE Korean_Wansung_CI_AS; -- 2
    SELECT 3 WHERE NCHAR(0x3400)='' COLLATE Korean_Wansung_CI_AS; -- 3
    SELECT 4 WHERE NCHAR(0x4DFF)='' COLLATE Korean_Wansung_CI_AS; -- 4

    SELECT 5 WHERE NCHAR(0x3358)='' COLLATE Korean_90_CI_AS; -- 5
    SELECT 6 WHERE NCHAR(0x33ff)='' COLLATE Korean_90_CI_AS; -- 6
    SELECT 7 WHERE NCHAR(0x3400)='' COLLATE Korean_90_CI_AS; -- nothing!!
    SELECT 8 WHERE NCHAR(0x4DFF)='' COLLATE Korean_90_CI_AS; -- 8

    SELECT 9 WHERE NCHAR(0x3358)='' COLLATE Korean_100_CI_AS; -- nothing!!
    SELECT 10 WHERE NCHAR(0x33ff)='' COLLATE Korean_100_CI_AS; -- nothing!!
    SELECT 11 WHERE NCHAR(0x3400)='' COLLATE Korean_100_CI_AS; -- nothing!!
    SELECT 12 WHERE NCHAR(0x4DFF)='' COLLATE Korean_100_CI_AS; -- nothing!!

    SELECT 13 WHERE NCHAR(0x3358)='' COLLATE Latin1_General_100_CI_AS; -- nothing!!
    SELECT 14 WHERE NCHAR(0x33ff)='' COLLATE Latin1_General_100_CI_AS; -- nothing!!
    SELECT 15 WHERE NCHAR(0x3400)='' COLLATE Latin1_General_100_CI_AS; -- nothing!!
    SELECT 16 WHERE NCHAR(0x4DFF)='' COLLATE Latin1_General_100_CI_AS; -- nothing!!
    -----------------------------------------------------------

    Some notes:
    1) If you are concerned with CJK characters, then it is probably best to use a Chinese/Japanese/Korean collation that would have more accurate linguistic rules for those characters.

    2) It is best to use the newest version of whatever collation you are wanting. The newest, as of SQL Server 2017, are the version 140 collations, but those are only the Japanese collations. Most others have a highest version of 100.

    3) It is best to NOT use the SQL Server collations (names starting with "SQL_") as they are mostly hold-overs from pre-SQL Server 2000 which introduced the non-versioned Windows collations. Another disadvantage of the SQL_ collations is how indexes on VARCHAR columns are invalidated when filtered with NVARCHAR data (please see: "Impact on Indexes When Mixing VARCHAR and NVARCHAR Types" -- https://sqlquantumleap.wordpress.com/2017/07/10/impact-on-indexes-when-mixing-varchar-and-nvarchar-types/ ).

    4) Don't use _BIN collations as they became effectively obsolete with the release of SQL Server 2005. Use _BIN2 instead as they provide more accurate sorting.

    This issue can be closed.

Feedback and Knowledge Base