Case-Sensitive SQL Database

hedegaard

Hi,
I have some SQL Databases running Case-Sensitive=TRUE with NA3.70W1 on top. DB size 130gb. So performance matters :-)

I do not have much experience with this feature. I am considering setting the databases back to default Case-Sensitive=FALSE.

I have tried setting a test database to Case-Sensitive =False and found following:
- Using Find will find all matches regardless of case and "Match Case" setting
- The SQL Statement fired to the database is not changed (Client-mon). It is actually the "Match Case" option in the Find dialog box that decides the SQL statement. So unless "Match Case" = TRUE in the Find dialog the nasty SQL statement is used. But SQL might perform better even if the SQL statement fired is the same?

There is a big difference in the SQL statements depending on the Case sensitivity. E.g. Searching a text field for 'anders'.
With "Match Case"=False

"Text felt" LIKE '%[AaªÁáÀàâÃã][NnÑñ][DdÐð][EeÉéÈèÊêËë][Rr][SsŠš]%'

With "Match Case"=True

"Text felt" LIKE '%anders%'

Questions:
- are there any pitfalls?
- will it give a performance increase to set Case-Sensitive=FALSE?
- is there a way to always set "Match Case" = TRUE on the Find dialog?
- any other experiences that I can learn from?


thanks
Anders H.

kriki

For performance:In File=>Database=>Alter Database=>Tab Options: Put Allow find as you type=FALSE.
This makes that in Navision, in the find-function, the "find as you type" is not possible. The "find as you type"-function is a real performance killer.
For the rest, search the forum for more info on performance there is a lot of it.

DenSter

You said "some databases". Does that mean you have a bunch of Navision databases, and all of them together are 130GB? Is this a production environment or your development box with all your customer databases on it?

Sounds to me like you need to take a close look at your set up and start thinking about optimizing your SQL Server performance. There's a bunch of things that you can do, before setting the database's case sensitivity. That is the least of what you can do, and I doubt that it will make any difference other than mae a small impact on ad hoc queries, and for that it is easier to disable find as you type.