May 9, 2012 at 2:19 am
I administrate an application that has been running several years, and had it's database placed on a SQL Server 2005, where the server collation was, Finnish_Swedish_CI_AS. The database had the SQL_Latin1_General_CP1_CI_AS collation then.
I have now moved the app to a new server that supports SQL Server 2008 R2, and moved it's database to a new server as well. This new server has Finnish_Swedish_CI_AI collation and the database has the same collation. When using a text search function within the app, with tha sample string "testv", the result hits will be both "testv" and "testw". I then changed the database collation so be as it was before, SQL_Latin1_General_CP1_CI_AS, but nothing change when I test the search string samples. I like to have a result that only matches my search string. If it contains a 'v' it should NOT also give me results with a 'w'.
Do I need to restart the SQL Server service to let it be able to renew the TEMP databases or what could be wrong with the collation selected?
Appreciate any comments on this.
TIA
May 10, 2012 at 7:52 am
When tables and indexes are created, their internal data structure is effected by whatever collation setting was currently in effect. Similar to Fill Factor, I believe that after changing the default collation of the database, you must then re-create the tables and indexes for them to reflect the new collation; simply changing the default setting won't cause any new behaviour.
This article goes in more detail.
http://sqlserverperformance.idera.com/uncategorized/consider-collations-carefully/
Also, you can experiment with how different collations behave by using the COLLATE query hint. However, query hints are not the actual solution, because the query will only utilize an index with a compatible collation.
set nocount on;
declare @test table ( word varchar(30) not null );
insert into @test ( word )
select 'testv' union all
select 'testw' union all
select 'test';
select * from @test where word = 'testv' collate SQL_Latin1_General_CP1_CI_AS ;
select * from @test where word = 'testv' collate Finnish_Swedish_CI_AS ;
select * from @test where word = 'testv' collate Finnish_Swedish_CI_AI ;
word
------------------------------
testv
word
------------------------------
testv
word
------------------------------
testv
testw
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply