Solomon Rutzky

My feedback

  1. 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  · 
  2. 0 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: 1

    <=-=Jan 18 2010 4:09PM=-=>

    Hi,
    Thanks for reporting the issue. These type of errors are however specific to your environment / database. It will be good if you can provide us with a sample database & script that demonstrates the issue. We will also look at the code based on the error message to see what the pontential problem might be. So if you can provide us with a repro that will be great.


    Umachandar, SQL Programmability Team

    <=-=Nov 12 2010 2:08PM=-=>

    Closed the feedback item.


    Umachandar

    Solomon Rutzky commented  · 

    I assume that this was reported for SQL Server 2005 or 2008, which I have not tested. But on SQL Server 2017, the following works just fine:

    ------------------------------------------------
    DECLARE @IssueHistory TABLE ([EventText] NVARCHAR(MAX));
    INSERT INTO @IssueHistory ([EventText]) VALUES (N'dfdfgdfg' + NCHAR(0x0D) + NCHAR(0x0A) + N'sdfs');

    declare @LFCR char(2)
    declare @CRLF char(2)
    declare @CR char(1)
    declare @LF char(1)
    set @LFCR=char(10)+char(13)
    set @CRLF=char(13)+char(10)
    set @CR=char(13)
    set @LF=char(10)

    UPDATE hist
    SET hist.EventText = replace(replace(replace(replace(hist.[EventText], @CRLF, '<br>'), @LFCR, '<br>'), @LF, '<br>'), @CR, '<br>')
    FROM @IssueHistory hist

    SELECT * FROM @IssueHistory;
    ------------------------------------------------

  3. 0 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: 1

    <=-=Jun 24 2014 4:29PM=-=>

    Hello,
    After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. �The reasons for closing these bugs are following:
    1.������ The fix is risky to implement in the current version of the product (service packs)
    2.������ Scenarios reported in the bug are not common enough
    3.������ A viable workaround is available

    Thanks again for reporting the product issue and continued support in improving our product

    Jos de Bruijn – SQL Server PM

    Solomon Rutzky commented  · 

    There is an easy workaround / fix for this: use a binary collation in the REPLACE() function. For example, the following query:

    DECLARE @NullTerminated NVARCHAR(500);
    SET @NullTerminated = N'one' + NCHAR(0) + N'two';

    SELECT @NullTerminated,
    REPLACE(@NullTerminated COLLATE Latin1_General_100_CI_AS, NCHAR(0), N''),
    REPLACE(@NullTerminated COLLATE Latin1_General_100_BIN2, NCHAR(0), N'');

    returns:

    one ... one ... onetwo

  4. 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: 1

    <=-=Nov 17 2016 6:36AM=-=>

    Solomon, such function already exist, NEXT VALUE FOR , see https://msdn.microsoft.com/en-CA/library/ff878370.aspx?f=255&MSPPError=-2147217396, and gets value before insert, not after. Knowing it before is a lot more advantageous than after as with Scope_Identity IMHO

    <=-=Nov 18 2016 10:50AM=-=>

    Hi Solomon,

    Thank you for reaching out to Microsoft.

    The current value of a sequence is exposed by the sys.sequences catalog view (https://technet.microsoft.com/en-us/library/ff877934(v=sql.110).aspx)
    Would this work in your scenario?

    Thanks,
    Panagiotis Antonopoulos

    <=-=Dec 11 2016 3:00PM=-=>

    Hi @Panagiotis. Thanks for the reply. To be honest, I don’t have a current scenario that I need this for. I was just doing some testing with Sequences and thought that it might be nice to have. It would have made what I was doing at the time easier to validate, so I figured others might have run into this as well, especially since SCOPE_IDENTITY() is used so frequently.

    Solomon Rutzky supported this idea  · 
  5. 10 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 » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 
    Solomon Rutzky supported this idea  · 
    Solomon Rutzky commented  · 

    One needs to also be careful not to remove square brackets from non-regular identifiers. That requires verifying both that a) all of the characters used are valid for use in regular identifiers, and b) the identifier being un-delimited is not a reserved word. I came up with an SSMS RegEx expression that is nearly 100% accurate (minor issue is that the Unicode classifications might have changed slightly between version 3.2--which is what SQL Server used to determine the list of valid characters years ago--and the version currently used by .NET). Please see DBA.StackExchange for the RegEx expression for use with Find/Replace in SSMS:

    Does this regex reliably remove square brackets from T-SQL? ( https://dba.stackexchange.com/a/198391/30859 )

  6. 4 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
    Solomon Rutzky commented  · 

    1) there is no such thing as "reserved characters" that require being delimited. But, there is a list of characters that do _not_ require being delimited (names using only these characters are known as "regular" identifiers, while names using characters not in the list are delimited identifiers since they require being delimited). The only known list of such characters (that I am aware of) is the list that I published here: Completely Complete List of Valid T-SQL Identifier Characters ( https://sqlquantumleap.com/reference/completely-complete-list-of-valid-t-sql-identifier-characters )

    2) Please see related item: "Remove Brackets from SSMS script generation" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32907346-remove-brackets-from-ssms-script-generation )

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

    So, I made the mistake of clicking on the "edit" button, and this pathetic, sad excuse of a website decided it would rather delete my comment. Nice. I guess it serves me right for thinking that progress was being made on a site that is clearly only being used by Microsoft (one of the largest, if not _the_ largest, software company on the planet) because someone at UserVoice knows someone at Microsoft.
    ________________________________________________________________________

    My comment was something along the lines of: I don't think this is a bug since it works for me on SQL Server 2017 CU15 using the following test script:
    ------------------------------------
    DECLARE @PythonScript NVARCHAR(MAX);

    SET @PythonScript = N'
    fileToSearch = "\\\\serverName\\shareName\\pathName\\fileName.txt"
    if os.path.exists(fileToSearch):
    print("File exists")
    else:
    print("File does not exist.")
    ';

    EXECUTE sp_execute_external_script
    @language = N'Python',
    @script = @PythonScript;
    ------------------------------------

    It could be a permissions issue (check the "\\server\share" permissions not the "F:\folder" permissions) as the "LaunchPad" service, by default, runs as the "NT SERVICE\MSSQLLaunchPad" account. And that account, even if it can access a local share, might not be able to access a networked share: you might need to add permissions for "computerName$" (or something like that).

    P.S. I only voted for this item because this horrible UserVoice system doesn't provide any other mechanism for notifications.

    Solomon Rutzky 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 » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky 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

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Aug 6 2017 9:14AM=-=>

    I also reproduced the same Problem against

    Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) – 13.0.4202.2 (X64) Dec 13 2016 05:22:44 Copyright © Microsoft Corporation Enterprise Edition (64-bit) on Windows 8.1 Pro 6.3 <X64> (Build 9600: )
    which runs with Server collation
    SQL_Latin1_General_CP1_CI_AS

    That said, it seems to be an old issue and not related to SQL Server 2017 CTP.

    <=-=Sep 15 2017 9:48AM=-=>

    This appears to be a weighting issue that was fixed in the newer Latin1_General Collation available starting in SQL Server 2008:

    SELECT 1 WHERE N’?XXX’ COLLATE SQL_Latin1_General_CP1_CI_AS BETWEEN N’?X’ AND N’?X’;
    SELECT 2 WHERE N’?XXX’ COLLATE Latin1_General_CI_AS BETWEEN N’?X’ AND N’?X’;
    SELECT 3 WHERE N’?XXX’ COLLATE Latin1_General_100_CI_AS BETWEEN N’?X’ AND N’?X’;

    Only “3” will be returned from the queries above.

    Solomon Rutzky commented  · 

    This is a non-issue and can be closed. Comment from Sept 15th, 2017 explains that the newer collations added sort weight definitions for these characters (I believe that is actually my comment ;-).

    P.S. I only voted for this item to get notifications of other comments since this horrible UserVoice system doesn't provide any other mechanism :-( .

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

    Even better would be to use something like:

    %USERPROFILE%\Microsoft SQL Server Local DB\{Instance Name}

    This would help avoid conflicts, and confusion, when multiple versions of LocalDB are installed and the same database name needs to be used across more than one of the versions. Having a single default directory for all versions is beyond annoying.

    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

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

    I realize that the release notes for the RTM / GA release of SSMS 18.0 states that this bug was fixed, but technically speaking, it is not 100% fixed. It takes a few additional steps now, but I am still able to inject my own T-SQL, thanks to another bug that I found while testing this :-)

    Solomon Rutzky commented  · 

    This feature, er, bug ;-) still exists in SSMS 18.0 Preview 7.

    Solomon Rutzky commented  · 

    Please see related suggestion:

    SSMS: Allow forcing case-insensitive matching in Object Explorer filters ( https://feedback.azure.com/forums/908035-sql-server/suggestions/36679522-ssms-allow-forcing-case-insensitive-matching-in-o )

    Solomon Rutzky shared this idea  · 
  13. 7 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 →
    Solomon Rutzky commented  · 

    I am experiencing the same problem using ODBC / SQLCMD version 17 and SQL Server 2019 CTP 2.5 LocalDB.

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

    Here are two more instances where this feature would fix a bug in a system view:

    "Collation conflict … Could not use dbo.sysdac_instances" ( https://dba.stackexchange.com/a/133995/30859 )

    "Error: 468 trying to connect to SQL server with SQL Server management studio" ( https://dba.stackexchange.com/a/239450/30859 )

    Solomon Rutzky commented  · 

    Here is another example where this feature would fix a bug in a system stored procedure:

    xp_logininfo gets "Msg 468, Level 16, State 9: Cannot resolve the collation conflict..." when DB collation doesn't match instance collation ( https://feedback.azure.com/forums/908035-sql-server/suggestions/36324910-xp-logininfo-gets-msg-468-level-16-state-9-can )

    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

    2 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    This ticket can be closed.

    Solomon Rutzky commented  · 

    Actually, the CHARINDEX function, when used with a Vietnamese collation, is returning the correct value. The example code given in the ticket description even shows the correct behavior happening. The problem is merely misunderstanding the linguistic rules of the Vietnamese collation.

    Even though the variables are VARCHAR, the comparison rules are still the Unicode rules due to using a Windows collation (and one of the reasons why everyone should be using Windows collations and not collations with names starting with "SQL_"). And, one of the awesome things that Unicode can do is recognize certain multi-character sequences as being a single unit that behaves differently than the individual characters that make it up. The example string of "thanh" has two instances of this: "th" and "nh". To get an individual "h", it either needs to be by itself, or you need to use a binary collation. Or, you can search for one of those combinations. The following demonstrates all of this:

    --------------------------------------------------------------------------
    SELECT CHARINDEX('h' COLLATE Vietnamese_100_CI_AS, 'thanh');
    -- 0

    SELECT CHARINDEX('th' COLLATE Vietnamese_100_CI_AS, 'thanh') AS [th],
    CHARINDEX('nh' COLLATE Vietnamese_100_CI_AS, 'thanh') AS [nh];
    -- 1 4

    -- "h" by itself:
    SELECT CHARINDEX('h' COLLATE Vietnamese_100_CI_AS, 'greyhound');
    -- 5

    -- no linguistic rules applied to binary collations:
    SELECT CHARINDEX('h' COLLATE Vietnamese_100_BIN2, 'thanh');
    -- 2
    --------------------------------------------------------------------------

    To be clear, all of this is expected behavior for Vietnamese collations. While the Unicode definition files are not the direct source of sorting data in Windows, it's still pretty close. So look at the following file, under the "traditional" section, and you can see the various characters and character combinations:

    https://github.com/unicode-org/cldr/blob/master/common/collation/vi.xml

    In there you can see a combination for "QU". If we try that out, we see the same behavior as we did with "th" and "nh":

    --------------------------------------------------------------------------
    SELECT CHARINDEX('q' COLLATE Vietnamese_100_CI_AS, 'racquet') AS [q],
    CHARINDEX('qu' COLLATE Vietnamese_100_CI_AS, 'racquet') AS [qu];
    -- 0 4
    --------------------------------------------------------------------------

    For more info on working with collations, Unicode, encodings, etc, please visit: https://Collations.Info/

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

    We’ll send you updates on this idea

    9 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    While I did up-vote this suggestion, there's no telling if/when it will be implemented. So, just to put this out there: I have included a SQLCLR aggregate function in my SQL# SQLCLR library ( https://SQLsharp.com/ ). It's called "Agg_JoinPlus", and is only available in the Full (i.e. paid) version, not in the Free version (which does include "Agg_Join" and "Agg_JoinDelim", but neither one handles ordering or duplicate removal). It works as follows:

    SELECT SQL#.Agg_JoinPlus(
    tab.[col], -- Value NVARCHAR(4000)
    N',', -- Delimiter NVARCHAR(4000)
    tab.[col], -- OrderBy NVARCHAR(4000)
    1, -- Ordering TINYINT: 0 = none, 1 = ASC, 2 = DESC
    NULL, -- InitialDelimiter NVARCHAR(4000)
    NULL, -- MinRecordsNeededForInitialDelimiter INT
    NULL, -- FinalDelimiter NVARCHAR(4000)
    NULL, -- MinRecordsNeededForFinalDelimiter INT
    NULL, -- NullReplacement NVARCHAR(4000)
    1, -- RemoveEmptyEntries BIT
    1, -- DuplicateHandling TINYINT: 0 = keep, 1 = remove (i.e. DISTINCT)
    1 -- Use Compression (to save on memory) BIT
    )
    FROM (VALUES ('a'), ('c'), ('b'), ('d'), (''), ('c')) tab(col);
    -- a,c,b,d

    Although it's not free, it does handle removing duplicates, and works with SQL Server as far back as 2008. The only platform is does not run on is Azure SQL Database since that no longer supports SQLCLR.

    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

    4 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    Actually, I ran the example code posted in that Stack Overflow question and found that the @Dummy method does not work. And it's basically the same issue that you reported here: NVARCHAR(MAX) has the problem, but NVARCHAR(1 - 4000) does not. Sad.

    Solomon Rutzky commented  · 

    Hi pbradshaw. It seems that "aggregate concatenation" (i.e. SELECT @x = @x + something) is not actually supported by SQL Server. The recommendation is to use one of the following:
    1) FOR XML
    2) SQLCLR
    3) Cursor

    Please see this answer on DBA.StackExchange for details: https://stackoverflow.com/a/15163136/577765

    That being said, I think there is a minor change that you could make to what you are doing that might "fix" the unexpected optimization issue: add a dummy variable containing an empty string.

    Given your example code:

    DECLARE @str varchar(max) = '';
    DECLARE @Dummy VARCHAR(1) = '';

    SELECT @str += [SomeColumn] + @Dummy + ','
    FROM SomeTable
    WHERE someCondition=true
    ORDER BY someColumn;

    The goal here is to force it to evaluate that expression each time. Anyway, something to try, at least.

    Also, just to put this out there, since SQLCLR is one of the official suggestions, and it works in versions starting at SQL Server 2005, I should mention that I included a SQLCLR aggregate function in my SQL# SQLCLR library ( https://SQLsharp.com/ ). It is called "Agg_JoinPlus", and is only available in the Full (i.e. paid) version, not in the Free version (which does include "Agg_Join" and "Agg_JoinDelim", but neither one handles ordering or duplicate removal). It works as follows:

    SELECT SQL#.Agg_JoinPlus(
    tab.[col], -- Value NVARCHAR(4000)
    N',', -- Delimiter NVARCHAR(4000)
    tab.[col], -- OrderBy NVARCHAR(4000)
    1, -- Ordering TINYINT: 0 = none, 1 = ASC, 2 = DESC
    NULL, -- InitialDelimiter NVARCHAR(4000)
    NULL, -- MinRecordsNeededForInitialDelimiter INT
    NULL, -- FinalDelimiter NVARCHAR(4000)
    NULL, -- MinRecordsNeededForFinalDelimiter INT
    NULL, -- NullReplacement NVARCHAR(4000)
    1, -- RemoveEmptyEntries BIT
    1, -- DuplicateHandling TINYINT: 0 = keep, 1 = remove (i.e. DISTINCT)
    1 -- Use Compression (to save on memory) BIT
    )
    FROM (VALUES ('a'), ('c'), ('b'), ('d'), (''), ('c')) tab(col);
    -- a,c,b,d

    Regarding STRING_AGG (mentioned by Steven in a previous comment):
    1) it started in SQL Server 2017, hence not available in SQL Server 2016 or older
    2) it DOES handle ordering
    3) it does NOT handle duplicate removal ( https://feedback.azure.com/forums/908035-sql-server/suggestions/35243533-support-distinct-for-string-agg )

    Take care, Solomon..

    https://SqlQuantumLeap.com/

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

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
    Solomon Rutzky commented  · 

    Hi Russell. It seems that "aggregate concatenation" (i.e. SELECT @x = @x + something) is not actually supported by SQL Server. The recommendation is to use one of the following:
    1) FOR XML
    2) SQLCLR
    3) Cursor

    Please see this answer on DBA.StackExchange for details: https://stackoverflow.com/a/15163136/577765

    That being said, I think there are some minor changes that you could make to what you are doing that might "fix" the unexpected optimization issue:

    1) In the example you provided, I declared a variable:
    DECLARE @Dummy NVARCHAR(1) = N'';
    and then concatenated that with the first item in the COALESCE (since that one will always be evaluated):
    REPLACE( N'x', N'x', c.COLUMN_NAME + @Dummy)
    I attached it to the column name because that is most likely the item that the optimizer is confused about, and the goal here is to force it to evaluate that expression each time.

    2) Even better than "fixing" the COALESCE would be removing it entirely. It's not really helping, adds complication to the query, and is an extra IF branch being evaluated per each row. Just do:
    SELECT @list = @list + REPLACE( N'x', N'x', c.COLUMN_NAME)
    In which case you don't need the "+ @Dummy". All you need is an extra step after that query to remove a delimiter, assuming you were concatenating one into the string. If you add the delimiter to the beginning of the string (counter-intuitive to most folks, but does make this easier), then you can simply use SUBSTRING to grab all but the first character (since the "length" parameter doesn't care if you request more characters than exist):
    SET @list = SUBSTRING(@list, 2, 80000);
    Here you don't need to calculate the length of the string :-)
    For anyone using SQL Server 2017 or newer, you can also use the new TRIM() built-in function to remove the extra delimiter from the beginning or end of the string:
    SET @list = TRIM(N',' FROM @list);

    Using your demo script, I was able to reproduce the problem on SQL Server 2017 CU14, and either one of those suggestions above "fixed" the problem.

    ALSO:
    1) I would avoid INFORMATION_SCHEMA entirely. Is best to go directly to the system catalog views: sys.objects, sys.tables, sys.columns, etc.
    2) All meta-data should be NVARCHAR, so your variables should also be NVARCHAR and string literals prefixed with an uppercase "N".

    Take care, Solomon..

    https://SqlQuantumLeap.com/

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

    Upvotes: 36

    <=-=Oct 8 2010 11:58AM=-=>

    Hi Matt,
    Thanks for your feedback. We will consider it for a future version of SQL Server. Please note however that the syntax you are proposing doesn’t achieve what you are looking for. The ORDER BY within OVER clause controls the ordering of the window and not the order of rows to the aggregate. We support OVER clause (excluding ORDER BY clause since we don’t have it) already for CLR aggregates. So if we extend OVER clause to support ORDER BY it will work with existing CLR aggregates also.
    ANSI SQL:2008 and earlier has a feature called ordered set functions that use a WITHIN GROUP specification. One such aggregate is PERCENTILE_CONT which looks like:

    PERCENTILE_CONT( ) WITHIN GROUP (ORDER BY )

    Once we support the above syntax for ordered set function, we will consider extending the CLR Aggregate contract to do the…
    Solomon Rutzky supported this idea  · 
  20. 35 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 » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    Please see related suggestion: "Add function to extract Asymmetric Key similar to CERTENCODED for Certificates" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32897317-add-function-to-extract-asymmetric-key-similar-to )

    Please note that while related, these two suggestions are not of equal importance. This suggestion (creating Asymmetric Key from varbinary literal) is a necessity, while the other suggestion (extracting the varbinary representation of the asymmetric key) is a "nice to have".

    Solomon Rutzky commented  · 

    Can someone please take a look at this? I do not believe that it requires that much work (though I do realize that there might be technical factors that I have no knowledge of that complicate this request), but the gains would be HUGE. This is something that really should have rolled out with SQL Server 2005. Being able to create an Asymmetric Key from binary hex bytes (i.e. FROM 0x...) would not only negate the need for trusted assemblies, but it would FINALLY allow folks to deploy from Visual Studio without resorting to setting "TRUSTWORTHY ON", which currently is really the only way to deploy SQLCLR from Visual Studio without jumping through lots of hoops ( https://sqlquantumleap.com/2017/08/09/sqlclr-vs-sql-server-2017-part-2-clr-strict-security-solution-1/ and https://sqlquantumleap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/ ).

    This would not only greatly assist in people being more successful with SQLCLR, but it would greatly reduce the number of databases that are currently set to "TRUSTWORTHY ON". And doesn't Microsoft recommend keeping TRUSTWORTHY OFF? I certainly do ( https://sqlquantumleap.com/2017/12/30/please-please-please-stop-using-impersonation-execute-as/ ). By not allowing Asymmetric Keys to be created inline via a varbinary literal or variable, Microsoft is pushing users to decrease the security of their systems (by setting TRUSTWORTHY ON) while at the same time recommending that they not do that.

    All that is _required_ to make life much, MUCH better for thousands (easily) of your customers is to allow creation of _just_ the public key, similar to creating the Asymmetric Key from an assembly or file. Allowing for the private key to be imported is a nice-to-have for consistency with CREATE CERTIFICATE, but it is absolutely not required as it would not affect these scenarios. Importing the private key would also require more work updating CREATE ASYMMETRIC KEY, and then require updates to ALTER ASYMMETRIC KEY. The effort required for dealing with the private key might not be worth it, hence it can be a separate project. But simply creating the basic Asymmetric Key (with just the public key, just like it currently works when creating from an assembly or file) will be a huge win for what should be not a lot of work. Please?

    PLEASE ? ? ? ? ? ? ? ?

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

Feedback and Knowledge Base