SQL Server Performance Forum – Threads Archive
sp_helpsort
hello everyone about the sorting order in setting up new instances. if I was using the default one(case incensive etc) now we would like to install a new instance with latin-general binary and move the database over what are the affect ? beside the search for abc in table contain Abc ABC aBc abc will return only one record instead of 4 thanks everyone May the best cheaters winCheck BOL topic "Using Binary Collations" Binary is the fastest sorting order, and it is case sensitive, but it can yield unexpected sort orders. If Binary is selected, the Case-sensitive, Accent-sensitive, Kana-sensitive, and Width-sensitive options are not available. For more information, see Windows Collation Sorting Styles. Binary Collations
Binary collations sort and compare data in SQL Server based on the bit pattern for each character. Each binary collation in SQL Server maps to a specific language locale and ANSI code page, and each performs case-sensitive and accent-sensitive data sorts. Binary collations provide the fastest data sorts. For more information, see Windows Collation Sorting Styles and Using Binary Collations.
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Think about logins
quote:The sort order of server A may be case insensitive, and the sort order of server B may be case sensitive. In this case, the users must type all the letters in the passwords as uppercase letters after you transfer the logins and the passwords to the instance on server B.
Fyi one of the KBAquote:
During a transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the destination server is case-sensitive, you must enter all alphabetical characters in passwords as uppercase characters after the transfer of logins to the destination server. If the sort order of the source server is case-sensitive and the sort order of the destination server is case-insensitive, you will not be able to log in with the logins transferred using the procedure outlined in this article, unless the original password contains no alphabetical characters or unless all alphabetical characters in the original password are uppercase characters. If both servers are case-sensitive or both servers are case-insensitive, you will not experience this problem. This is a side effect of the way that SQL Server handles passwords.
Satya SKJDuring a transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the destination server is case-sensitive, you must enter all alphabetical characters in passwords as uppercase characters after the transfer of logins to the destination server. If the sort order of the source server is case-sensitive and the sort order of the destination server is case-insensitive, you will not be able to log in with the logins transferred using the procedure outlined in this article, unless the original password contains no alphabetical characters or unless all alphabetical characters in the original password are uppercase characters. If both servers are case-sensitive or both servers are case-insensitive, you will not experience this problem. This is a side effect of the way that SQL Server handles passwords.
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
thank you very much now I have a clearer understanding May the best cheaters win
]]>