Does anyone know why the SQL Server database that is created for NAV 5.0 is case sensitive?
As far as I remeber, Native base was case sensitive (thus searching / filtering by mask was somewhat clumsy - you had to try several times, usig different combinations of upper / lower case). With SQL, its up to you, when creating a new NAV database - collation, case & accent sensitiveness.
I don’t now remember the defaults, that appear in New Database form, but they should be (are) configurable.
You can change this setting by following this:
Select “Database”,“Alter” from the “File” menu.
Go to the “Sorting” page and remove the “Case sensitive” checkmark.
Remember that you must change the database to “Single User Mode” in the “Options” page before you can change the collation settings.
After this you can filter for text without considering the case of the letters.
Its your choice at database creation time and as was said, it can be changed for an existing database but that can be very lengthy for a large database. (When you install SQL Server itself, you need to specify a collation and therefore decide on case sensitivity for the master and other system databases).
One small thing to note about case sensitivity. If you use a database that is case sensitive, by default of course, filtering in NAV will be case sensitive. But the end user also has the option of doing a case-insenstive filtering by using the @ operator in a filter. But the reverse is not true - if you choose a case-insensitive database then filtering will be case insensitive but there is no way to force a case-sensitive filter in a similar way. So in this respect you could say that case sensitive is more flexible.
But I realise that for convenience sake case insensitivity is often chosen to ignore unnecessary differences in text data entered to the system.
Why they originally chose case sensitivity… heaven knows. Maybe because someone thought that this way indexes could more selective, but this, however, is speculation.
Some background:
The collation determines character set and sort order. This affects queries with “order by”, filters in the where clause as well as the structure of underlying indexes. Comparisons are also affected, for example in join operations. Collations are set at server level, at database level and can also be set at column level.
What if server level collation is different from database level collation?
For example, you NAV DB is case sensitice, the server level collation is not?
This can (in fact, it already has done so) cause some trouble in conjunction with tempdb - tempdb always has the server level collation (same as master).
Can the database collation be changed easily? No.
Any changes you make there only affect future modifications. All existing data maintains the original collation.
However, after the change all data will be checked against the new collation, and this also affects the uniqueness of constraints. The change from case sensitive to case insensitive can lead to unpredictable behavior.
Some reading on this:
http://msdn.microsoft.com/en-us/library/ms175835.aspx
http://support.microsoft.com/kb/325335
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Changing-Database-Collation-and-dealing-with-TempDB-Objects.aspx