Can SQL database be made case insensitive

mr.mcgoo · 2006-11-06

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 · 2006-11-06

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

mr.mcgoo · 2006-11-06

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 · 2006-11-06

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 · 2006-11-06

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 · 2006-11-06

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 · 2006-11-06

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

ara3n · 2006-11-06

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

mr.mcgoo · 2006-11-06

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 · 2006-11-06

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 · 2006-11-06

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!

Howdy, Stranger!

Quick Links

Categories

Can SQL database be made case insensitive

Comments