Need help with collations - Microsoft SQL Server

Steve Thorpe

I want to create a database where the table names / column names / SP names
are NOTcase sensitive but where the data in the tables is, so that I can
build a unique index where 'test' and TEST' is accepted as different.

I have tried Installing SQL with a Collation designator with the Case
Sensitive option checked
- this caused all sp names / column names / table names to be case
sensitive - not what I want.

I have also tried installing SQL and selecting a SQL Collation and picking
an option from the drop down list. - again this cause everything to be case
sensitive - not desired

Do I have to install SQL with a non case sensitive collation, then set each
column in the table to be case sensitive? What if any are the problems I am
likely to come across?

Thanks

Steve

Jul 20 '05 #1

Subscribe Reply

3483

John Bell

Do I have to install SQL with a non case sensitive collation, then set each column in the table to be case sensitive? What if any are the problems I am likely to come across?

AFAIK this is your only "safe" option. As I am an adocate of using a source
code control system to maintain database object, I would have scripted all
the tables anyhow, and therefore changing colation is a function of the
editor. If you are consistent in changing the colation then I don't envisage
any problems apart from possible data migration problems if they are not
compatible (but I suspect they will be!).You will also have to make sure
that set collations in temporary tables see "Mixed Collation Environments"
in books online.

An alternative would be to force the collation when doing your querysee the
examples for "Collation Precedence" in Books online.

John

Jul 20 '05 #2

Erland Sommarskog

Steve Thorpe (st***********@ nospam.hotmail. com) writes:

I want to create a database where the table names / column names / SP
names are NOTcase sensitive but where the data in the tables is, so that
I can build a unique index where 'test' and TEST' is accepted as
different.
...
Do I have to install SQL with a non case sensitive collation, then set
each column in the table to be case sensitive? What if any are the
problems I am likely to come across?

The best strategy may be to let the server collation be case sensitive,
but create the database with a case-insensitive collation and then declare
each column to be case-insensitive. Then you don't have to bother about
columns in temp tables and table variables, as they will use the system
collation. However names of temp tables will be case-sensitive.

By setting the collation of the database to be case-insensitive,
procedure names, variable names, table names etc are case insensitive.

Note however that variables takes their collation from the database
collation, so if @a = 'TEST' and @b = 'test', @a is equal to @b.

Personally, I'm a strong advocate of that development should always take
place in a case-sensitve environment. If you develop case-insensitive,
and you product is to deployed in a shop where case-sensitivity is the
law, you may get tons of problems.

A collorary of this, is that since it may be a nuisance to remember
whether you called a table OrderDetails or Orderdetails, I stick to
lowercase for all names.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3

Similar topics

1175

Is this by design - compare CHAR BINARY with CHAR

by: Jens Schreiber | last post by:

Helo, I'm using 4.1.8-standard on linux and get this: CREATE TABLE test (field1 CHAR(20) BINARY NOT NULL, field2 CHAR(20) NOT NULL) TYPE=MyISAM; INSERT INTO test VALUES('xxx','yyy') SELECT * FROM test WHERE field1=field2 ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT)

MySQL Database

3623

Slow Query with Date

by: Thomi Baechler | last post by:

Hello Everybody I run the following query against to identical databases. Execution time on the first DB is 0 seconds, on the other 6 seconds! SELECT dbo.HRMABZ.EMPKEY , dbo.HRMABZ.CONNUMB , dbo.HRM_CALENDER.Datum, dbo.HRMABZ.ABZTXT FROM dbo.HRM_CALENDER INNER JOIN dbo.HRMABZ ON dbo.HRM_CALENDER.Datum >= dbo.HRMABZ.ABZDATF

Microsoft SQL Server

13730

CONCAT with IF

by: nickdevx | last post by:

Damn it this "illegal mix of collashit" messages are driving me nuts!! What's wrong with the following statement? SELECT userid, CONCAT(username,' / ',firstname,' ',lastname,' / ', email, IF(activated=1,'',' (NOT YET ACTIVATED)')) AS uname FROM tbuser I get: MySQL Error Occured

MySQL Database

6700

REPLACE function doesn't work with null-bytes

by: enno | last post by:

Dear Community, We have a problem with null-Bytes in varchar-Columns, which are not handled correctly in our application. Therefor we try to filter them out using the Transact-SQL REPLACE function. The Problem was, that the REPLACE-function didn't behave the way we expected. Following Example demonstrates the behavior:

Microsoft SQL Server

9166

French characters are not imported properly with bcp

by: Wael | last post by:

Hi I have a script that uses bcp to import data from an ascii text file into SQL tables. The french characters are not copied properly. They are converted to letters of the alphabet. I tried to change all the fields to nvarchar instead of varchar and nchar instead of char, but I got Greek characters instead. How can I fix this? Here is some code:

Microsoft SQL Server

2007

Problem with executing MySQL queries through PHP.

by: Daz | last post by:

Hi everyone. I was faced with the choice of whether my problem is indeed a PHP problem or a MySQL. I have decided it's a PHP problem as I don't experience the same problem when I execute the same query at the CLI. I am having trouble executing a large query through my PHP script. It takes about 7-11 seconds on average to execute, whereas the same query only takes 0.01 seconds to execute through the CLI.

PHP

3946

Brilliant technology helping those most in need

by: U S Contractors Offering Service A Non-profit | last post by:

Brilliant technology helping those most in need Inbox Reply U S Contractors Offering Service A Non-profit show details 10:37 pm (1 hour ago) Brilliant technology helping those most in need Inbox Reply from Craig Somerford <uscos@2barter.net> hide details 10:25 pm (3 minutes ago)

C / C++

2587

Very odd?? Illegel mix of collations error

by: ryanmhuc | last post by:

I have a table (depth as int, name as varchar). If I run the following query I get NO ERROR SELECT CONCAT(REPEAT(' ', 2) , display_name) as name FROM test If I run this: SELECT CONCAT(REPEAT(' ', depth) , display_name) as name FROM test

MySQL Database

9738

Problem with Greek characters

by: Punkis | last post by:

Hi all, I have a problem with my php and mysql project. I use an auctions software, named phpauction for my project. I import into my database with utf8 encodingm and I can see the greek letters inside the fields. The problem is that when i go to the site for the results, I get question marks instead of the greek characters. I have searched a lot, and i found that there is a solution by adding mysql_query("SET NAMES utf8") after...

MySQL Database

8366

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8888

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8790

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8565

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8650

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6206

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

4372

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2779

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1779

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General