Can SQL database be made case insensitive

mr.mcgoo

I've noticed that Navision 4 SQL DB is case sensitive, which is a pain for lazy developers like me.
Is it possible or recommended to change this to case insensitive.

Cheers

ara3n

File->database->alter->collation tab->uncheck case-sensetive.

mr.mcgoo

Thanks Rashed,
I know how to do it from a SQL point of view, my question really relates to whether or not it has any affect on Navision itself.

Cheers

ara3n

The programming language is not case sensitive. So I don't know why you want to change it. The only places where this will be an issue is if you filter on text fields, or sort.

mr.mcgoo

Thanks Rashed,
The installation of Navision set the DB to Case Sensitive, which means coding SQL against it (which we do with Reporting Services) becomes very tedious because tables and fields have to be coded with exactly the same case as they are set in Navision. e.g. Select * from Cronus$customer will fail, I have to code select * from CRONUS$Customer.

We are still in the testing phase, so I may try it. I'm not a Navision developer so I wasn't sure if there was a specific reason to do with C-Side for it being case sensitive.

mr.mcgoo

Thanks Rashed,
The installation of Navision set the DB to Case Sensitive, which means coding SQL against it (which we do with Reporting Services) becomes very tedious because tables and fields have to be coded with exactly the same case as they are set in Navision. e.g. Select * from Cronus$customer will fail, I have to code select * from CRONUS$Customer.

We are still in the testing phase, so I may try it. I'm not a Navision developer so I wasn't sure if there was a specific reason to do with C-Side for it being case sensitive.

ara3n

Writing sql statements for reporting services has nothing to do with C/Side. It's just how SQL works.

ara3n

The development environment is not case sensitive. Is it just searching for tables that slows you down?

mr.mcgoo

Hi Rashed,
It's just typing the queries that is more problemmatic. All SQL object names have to be specified with the exact case when a database is case sensitive.
It's just an annoyance really. Performance is not affected.

As well as Reporting Services, we are writing a web front end to handle order processing, so there is a fair amount of SQL scripting we are writing against the Navision DB, inevitably most of the code we write fails initially because we've missed a capital letter in something. If there is no good reason for it being case sensitive then I'll change it. I just didn't want to do it to find out that C-Side expects it to be case sensitive.

ara3n

If you make the db not case sensitive, It will not change the sql queries. I believe you still have to write the queries in correct case.

mr.mcgoo

Hi Rashed, thanks for taking the time on this.

As far as the case sensitivity goes, Navision is the only database on my server that is like this. I've copied the database to three different servers and it's the same on each.
The databases collation is Latin1_General_CS_AI (i.e. Case Sensitive, Accent Insensitive) every other database is Latin1_General_CI_AS (Case Insensitive, Accent Sensitive)

The difference is that normally, any one of the queries below will work, but with a case sensitive database only the first one works;

select Name from [CRONUS UK Ltd_$Customer]
select NAME from [CRONUS UK Ltd_$Customer]
select Name from [Cronus UK Ltd_$CusToMer]

I am a SQL developer, so I'm aware of this behaviour and how to code for it, it's just a pain that's all. Especially if there is no good reason for it.
It's easy to change the collation so I'll give it a whirl anyway!