Hi All..
I'm having a bit of a problem with using collations in my MS
SQLServer2000..
The problem I have, is that I have 1 DB in 1 country,but I need to
support multible languages in the same table.. where I have stored
standart text using a Varchar..
The current collations doesnt support the polish and Danish language..
so what Collation will enable me to do that on the the table.. ?
I have thought about using this :
SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
for me..
Any Help ?
Best Regards
Lars Roed
*** Sent via Developersdex http://www.codecomments.com ***
<Lars> wrote in message news:ew$wqONbFHA.1660@.tk2msftngp13.phx.gbl...
> I'm having a bit of a problem with using collations in my MS
> SQLServer2000..
> The problem I have, is that I have 1 DB in 1 country,but I need to
> support multible languages in the same table.. where I have stored
> standart text using a Varchar..
> The current collations doesnt support the polish and Danish language..
> so what Collation will enable me to do that on the the table.. ?
> I have thought about using this :
> SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
> for me..
> Any Help ?
Go for nvarchar (unicode)
Regards, Wojtek
|||Hi there..
So using an Nvarchar will override the use of the collation.. and make
sure that the polish and estern european letters will remain intact ?
Thanx in advance
*** Sent via Developersdex http://www.codecomments.com ***
|||Nvarchar(Unicode) might be the best solution as suggested, but be aware of
the differencies compare to non-unicode. Unicode needs tvice the space and
the maximum size of a nchar or nvarchar column is 4000 characters versus
8000 characters for char and varchar. This might not at all be an issue for
you, but I find it usefull to be aware of.
If you haven't done it already it might be worth reading about collations in
Books On Line
Regards
Steen
Lars wrote:
> Hi there..
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact ?
> Thanx in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||<Lars> wrote in message news:OVyAOiPbFHA.2876@.TK2MSFTNGP10.phx.gbl...
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact ?
You will be able to store characters that do not occur simultaneously in
specific code pages (collations).
Converting varchar to nvarchar AFAIK should not corrupt any characters.
Collations occur in nvarchar columsn also, because they are responsible not
only for code pages. See BOL for more details.
Regards, Wojtek
Showing posts with label collations. Show all posts
Showing posts with label collations. Show all posts
Sunday, February 12, 2012
Collations problems !
Hi All..
I'm having a bit of a problem with using collations in my MS
SQLServer2000..
The problem I have, is that I have 1 DB in 1 country,but I need to
support multible languages in the same table.. where I have stored
standart text using a Varchar..
The current collations doesnt support the polish and Danish language..
so what Collation will enable me to do that on the the table.. ?
I have thought about using this :
SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
for me..
Any Help '
Best Regards
Lars Roed
*** Sent via Developersdex http://www.codecomments.com ***<Lars> wrote in message news:ew$wqONbFHA.1660@.tk2msftngp13.phx.gbl...
> I'm having a bit of a problem with using collations in my MS
> SQLServer2000..
> The problem I have, is that I have 1 DB in 1 country,but I need to
> support multible languages in the same table.. where I have stored
> standart text using a Varchar..
> The current collations doesnt support the polish and Danish language..
> so what Collation will enable me to do that on the the table.. ?
> I have thought about using this :
> SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
> for me..
> Any Help '
Go for nvarchar (unicode)
Regards, Wojtek|||Hi there..
So using an Nvarchar will override the use of the collation.. and make
sure that the polish and estern european letters will remain intact '
Thanx in advance
*** Sent via Developersdex http://www.codecomments.com ***|||Nvarchar(Unicode) might be the best solution as suggested, but be aware of
the differencies compare to non-unicode. Unicode needs tvice the space and
the maximum size of a nchar or nvarchar column is 4000 characters versus
8000 characters for char and varchar. This might not at all be an issue for
you, but I find it usefull to be aware of.
If you haven't done it already it might be worth reading about collations in
Books On Line
Regards
Steen
Lars wrote:
> Hi there..
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact '
> Thanx in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***|||<Lars> wrote in message news:OVyAOiPbFHA.2876@.TK2MSFTNGP10.phx.gbl...
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact '
You will be able to store characters that do not occur simultaneously in
specific code pages (collations).
Converting varchar to nvarchar AFAIK should not corrupt any characters.
Collations occur in nvarchar columsn also, because they are responsible not
only for code pages. See BOL for more details.
Regards, Wojtek
I'm having a bit of a problem with using collations in my MS
SQLServer2000..
The problem I have, is that I have 1 DB in 1 country,but I need to
support multible languages in the same table.. where I have stored
standart text using a Varchar..
The current collations doesnt support the polish and Danish language..
so what Collation will enable me to do that on the the table.. ?
I have thought about using this :
SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
for me..
Any Help '
Best Regards
Lars Roed
*** Sent via Developersdex http://www.codecomments.com ***<Lars> wrote in message news:ew$wqONbFHA.1660@.tk2msftngp13.phx.gbl...
> I'm having a bit of a problem with using collations in my MS
> SQLServer2000..
> The problem I have, is that I have 1 DB in 1 country,but I need to
> support multible languages in the same table.. where I have stored
> standart text using a Varchar..
> The current collations doesnt support the polish and Danish language..
> so what Collation will enable me to do that on the the table.. ?
> I have thought about using this :
> SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
> for me..
> Any Help '
Go for nvarchar (unicode)
Regards, Wojtek|||Hi there..
So using an Nvarchar will override the use of the collation.. and make
sure that the polish and estern european letters will remain intact '
Thanx in advance
*** Sent via Developersdex http://www.codecomments.com ***|||Nvarchar(Unicode) might be the best solution as suggested, but be aware of
the differencies compare to non-unicode. Unicode needs tvice the space and
the maximum size of a nchar or nvarchar column is 4000 characters versus
8000 characters for char and varchar. This might not at all be an issue for
you, but I find it usefull to be aware of.
If you haven't done it already it might be worth reading about collations in
Books On Line
Regards
Steen
Lars wrote:
> Hi there..
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact '
> Thanx in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***|||<Lars> wrote in message news:OVyAOiPbFHA.2876@.TK2MSFTNGP10.phx.gbl...
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact '
You will be able to store characters that do not occur simultaneously in
specific code pages (collations).
Converting varchar to nvarchar AFAIK should not corrupt any characters.
Collations occur in nvarchar columsn also, because they are responsible not
only for code pages. See BOL for more details.
Regards, Wojtek
Collations problems !
Hi All..
I'm having a bit of a problem with using collations in my MS
SQLServer2000..
The problem I have, is that I have 1 DB in 1 country,but I need to
support multible languages in the same table.. where I have stored
standart text using a Varchar..
The current collations doesnt support the polish and Danish language..
so what Collation will enable me to do that on the the table.. ?
I have thought about using this :
SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
for me..
Any Help '
Best Regards
Lars Roed
*** Sent via Developersdex http://www.developersdex.com ***<Lars> wrote in message news:ew$wqONbFHA.1660@.tk2msftngp13.phx.gbl...
> I'm having a bit of a problem with using collations in my MS
> SQLServer2000..
> The problem I have, is that I have 1 DB in 1 country,but I need to
> support multible languages in the same table.. where I have stored
> standart text using a Varchar..
> The current collations doesnt support the polish and Danish language..
> so what Collation will enable me to do that on the the table.. ?
> I have thought about using this :
> SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
> for me..
> Any Help '
Go for nvarchar (unicode)
Regards, Wojtek
I'm having a bit of a problem with using collations in my MS
SQLServer2000..
The problem I have, is that I have 1 DB in 1 country,but I need to
support multible languages in the same table.. where I have stored
standart text using a Varchar..
The current collations doesnt support the polish and Danish language..
so what Collation will enable me to do that on the the table.. ?
I have thought about using this :
SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
for me..
Any Help '
Best Regards
Lars Roed
*** Sent via Developersdex http://www.developersdex.com ***<Lars> wrote in message news:ew$wqONbFHA.1660@.tk2msftngp13.phx.gbl...
> I'm having a bit of a problem with using collations in my MS
> SQLServer2000..
> The problem I have, is that I have 1 DB in 1 country,but I need to
> support multible languages in the same table.. where I have stored
> standart text using a Varchar..
> The current collations doesnt support the polish and Danish language..
> so what Collation will enable me to do that on the the table.. ?
> I have thought about using this :
> SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
> for me..
> Any Help '
Go for nvarchar (unicode)
Regards, Wojtek
Collations Problem
I have recently migrated a SQL Server 6.5 DB to SQL 2000.
On a particular table i added a new varchar ( [field29] - see below)
now when changing a record in this table, the performance is greatly reduced.
In SQL Enterprise manager, doing a return all rows, and then amending a record here, i get the following message :
"the entire resultset must be returned before this row can be updated. This operation is in progress and may take a long time due to the size of the result set".
The table has 300,000 records. The update takes about 20secs.
After this has completed, the performance is ok, as long as the window remains open. SQL Server memory also grows significantly. It appears that the entire recordset is cached.
Is this related to Collations?
([Field1] is the Primary Key)
any ideas?
CREATE TABLE [dbo].[Tabletest]
(
[field1] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field2] [smallint] NOT NULL ,
[field3] [datetime] NOT NULL ,
[field4] [datetime] NULL ,
[field5] [datetime] NULL ,
[field6] [datetime] NULL ,
[field7] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field8] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field9] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field10] [varchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field11] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field12] [smallint] NOT NULL ,
[field13] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field15] [smallint] NULL ,
[field16] [smallint] NULL ,
[field17] [smallint] NULL ,
[field18] [smallint] NULL ,
[field19] [smallint] NULL ,
[field20] [smallint] NULL ,
[field21] [smallint] NULL ,
[field22] [varchar] (60) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field23] [smallint] NOT NULL ,
[field24] [bit] NOT NULL ,
[field25] [datetime] NULL ,
[field26] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field27] [int] NOT NULL ,
[field28] [smallint] NULL ,
[field29] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
)Use WHERE clause, you do not need to see 300,000 records when you are changing one :)
On a particular table i added a new varchar ( [field29] - see below)
now when changing a record in this table, the performance is greatly reduced.
In SQL Enterprise manager, doing a return all rows, and then amending a record here, i get the following message :
"the entire resultset must be returned before this row can be updated. This operation is in progress and may take a long time due to the size of the result set".
The table has 300,000 records. The update takes about 20secs.
After this has completed, the performance is ok, as long as the window remains open. SQL Server memory also grows significantly. It appears that the entire recordset is cached.
Is this related to Collations?
([Field1] is the Primary Key)
any ideas?
CREATE TABLE [dbo].[Tabletest]
(
[field1] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field2] [smallint] NOT NULL ,
[field3] [datetime] NOT NULL ,
[field4] [datetime] NULL ,
[field5] [datetime] NULL ,
[field6] [datetime] NULL ,
[field7] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field8] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field9] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field10] [varchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field11] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
[field12] [smallint] NOT NULL ,
[field13] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field15] [smallint] NULL ,
[field16] [smallint] NULL ,
[field17] [smallint] NULL ,
[field18] [smallint] NULL ,
[field19] [smallint] NULL ,
[field20] [smallint] NULL ,
[field21] [smallint] NULL ,
[field22] [varchar] (60) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field23] [smallint] NOT NULL ,
[field24] [bit] NOT NULL ,
[field25] [datetime] NULL ,
[field26] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
[field27] [int] NOT NULL ,
[field28] [smallint] NULL ,
[field29] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
)Use WHERE clause, you do not need to see 300,000 records when you are changing one :)
Collations in SSAS. How should it work?
Hi SSAS gurus,
Could you tell me, please, what collation gets a new dimension (cube), that collation isn't specified in create script, from Server collation or from data base collation? In the MSDN there isn't clear information related to it.
If I create a new database from script, where only the collation of the database specified, all dimension and cubes get collation from server, but not from database.
But in the SQL Server DB Engine all the objects inherit collation from database not from server.
How can I specify the collation of the data base object in the SSAS project? Is there in UI any place to set collation of database in the project?
Analysis Server implements general mechanism of static inheritance. Object's properties not specified in the create statement are getting their values based on pre-defined defaults or based on the value of parent object.
This way, the database is going to be created with server collation and any object within database keeps inheriting the database collation.
As for the collaiton exposed in the project UI, couldnt find anywhere I could change it myself :)
Simple workaround is to add following XML tag to your %ProjectName%.database file <Collation>Latin_general</Collation>
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Edward,
I have following problem (probably a bug). I have spicified the collation Latin_General_Bin for the database in the database script . But the server has collation Latin_General_CI. In my script neither cubes no dimensions don't have collation specified. After script execution, all created cubes and dimesions have collation inherited from server, but not from database, es expected :-(
I have found how to set collation for the databese in UI :-(. In the BI Studio Menu Database -> Edit Database. Then in Properties window you can set collation for the database.
|||You are probably right. This could be a bug. Though I would check one more thing.
Make sure when you are deploying, the database does not exist on the server. Deploying over existing database wouldnt change it's collation.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Collations and simple import from a csv-file
Hello. Here a two different problems that occur one and a while when I try to import a textfile to SQL Server 2005.
I have a flat file connection to a csv-file that originally is a export from an AS4000 DB2 database This csv-file is defined as a variable length file Why do SSIS automatically interpret the length of each column as varchar(50)? It does not matter if a define the same file as a fixed lenght file. The problem is that I will get a warning that information in columns will be truncated. I would like to do a direct export to the SQL Server 2005 table with shorter varchar fields. I can solve this by using the task for transforming data types but this only works on the text fields. This task cannot transform a string to a decimal or an integer column in the SQL Server 2005 table. Is there no other way than having a staging table between the text file and the SSIS-data pipe? I also get a lot of collation or code page errors even if we set the receiving columns to nvarchar and nchar. Is there any good article on this subject? Code page errorsLast question. Is there parameter support in the data reader source connection?
I appreciate all help.
Regards
Thomas Ivarsson
You can control the data types and lengths that SSIS uses for flat files by going to the Advanced area in the flat file connection manager. It will show you the columns defined, their data types, and their lengths, and you can alter the values to match what you want.
Parameter support in the data reader source: I don't believe that it does, but you can create the SQL that you want by building it in a variable, then assigning the variable to the Data Reader Source.SQL Command property using an expression.
|||Thank's for the answere jwelch. I have actually tried the advanced properties and have set the flat file connection data types according to the target table data types.
Perhaps I have another error but this configuration will work?
I will have a look at your advice regarding the data reader source.
Regards
Thomas Ivarsson
collations and nvarchar
Should a programmer bother using nvarchar for a person's name (special chracters allowed) if I still use SQL_Latin1_General_CP1_CI_AS?
I don't think you can get ascent with Varchar because those characters are not ASCII that is the reason Greek and other similar languages comes with separate code pages and collations.
http://msdn2.microsoft.com/en-us/library/ms144250.aspx
Collations ... blah!
What collation setting(s) do I need to set with RebuildM to get the
SQL_Latin1_General_CP1_CI_AS collation?
3rd time going and I can't seem to set/select the right configuration.
Thanks,
MorganFound it...
Dictionary Order, case insensitive for use with 1252 Char Set.
Thanks,
Morgan
"Morgan" <mfears@.spamcop.net> wrote in message
news:ueb0z8jxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> What collation setting(s) do I need to set with RebuildM to get the
> SQL_Latin1_General_CP1_CI_AS collation?
> 3rd time going and I can't seem to set/select the right configuration.
>
> Thanks,
> Morgan
>
SQL_Latin1_General_CP1_CI_AS collation?
3rd time going and I can't seem to set/select the right configuration.
Thanks,
MorganFound it...
Dictionary Order, case insensitive for use with 1252 Char Set.
Thanks,
Morgan
"Morgan" <mfears@.spamcop.net> wrote in message
news:ueb0z8jxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> What collation setting(s) do I need to set with RebuildM to get the
> SQL_Latin1_General_CP1_CI_AS collation?
> 3rd time going and I can't seem to set/select the right configuration.
>
> Thanks,
> Morgan
>
Collations
Hi,
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ?, ? and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ?, ? and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?
> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.
> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
> Ah okay - so I should simply leave it "as is", right?
>
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ?, ? and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ?, ? and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?
> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.
> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
> Ah okay - so I should simply leave it "as is", right?
>
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.
Collations
Hi,
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?
> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.
> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
>> Hi Dylan,
>> Basically you collation should be the local environment where it is.
>> So if its in the UK it should be english etc.
> Ah okay - so I should simply leave it "as is", right?
>> With regards to your text, what you need to do is have the
>> appropriate datatype's for your tables.
>> For storing text that you would want as not only english, but another
>> language, instead of using a datatype of say char(10) you would use
>> nchar(10) this N, ensures that the type is unicode and not ANSI.
>> Text conversions of char can have mistakes, yet nchar represents the
>> characters as they should.
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>> One important note, using nchar instead of char, will consume twice
>> as much space as char, so just something to bear in mind.
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?
> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.
> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
>> Hi Dylan,
>> Basically you collation should be the local environment where it is.
>> So if its in the UK it should be english etc.
> Ah okay - so I should simply leave it "as is", right?
>> With regards to your text, what you need to do is have the
>> appropriate datatype's for your tables.
>> For storing text that you would want as not only english, but another
>> language, instead of using a datatype of say char(10) you would use
>> nchar(10) this N, ensures that the type is unicode and not ANSI.
>> Text conversions of char can have mistakes, yet nchar represents the
>> characters as they should.
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>> One important note, using nchar instead of char, will consume twice
>> as much space as char, so just something to bear in mind.
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.
Friday, February 10, 2012
Collation Recommendation
I work for an international company and we have to join between databases in different locations. I want a situation where the collations are the same so that we don't have to worry about stating which collation to use in cross server queries. Can someone tell me what we shoud choose. We can't right now change the system to use unicode. If we choose windows collation then does it take the collation of the os on the server? Will we have a problem if different servers have different Windows collations? Is it better to choose a Sql collation and make sure everyone chooses the same one? Thanks for your help.What other DB platforms/collations will you be working with?
Do you have the ability/authority to dictate the collation used at these other sites?
I'm not sure what you mean by 'Windows collation'. When creating a new DB in SQL2k, you have the ability to set the code page/collation for that db and 'windows collation' is not an option.|||I can dictate what collation the other servers should use. When you setup the Sql Server you can choose either windows collation and the sort order or choose a Sql Collation and the sort order. What I want to know is whether or not it will simplify things to choose the Sql Collation. It seems to me that if I use the windows collation I may run into problems if the windows collation of the os is different on the other servers.|||Unless I have a reason not to, I use the default (SQL_Latin1_General_CP1_CI_AS). The 1252 character set, case insensitive, dictionary order, accent sensitive.
Being an international company, though , you may wish to use the 850 (multi-linqual) character set, but I'd stick with case insensitive, dictionary order, accent sensitive, unless you have another reason not to.
Unicode would be preferable, but you've already mentioned that you cannot use it.|||Hi!
I am looking for help concerning collation problem, so if You can help me I would be thankful...
I am changing from sql7 to sql2k and i would like to be sure i have maintaned the same sort order etc. i had in sql7:
Unicode data sorting
-------
Locale ID = 33280
case sensitive
Sort Order Description
----------------------------------------
Character Set = 4, cp1250
Code page 1250
Sort Order = 80, bin_cp1250
Binary Sort Order for the Code Page 1250 Character Set
Characters, in Order
----------------------------------------
! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@. A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~
_
_
So i think in sql2k i should set:
Polish_BIN
i need to have bin sorting and polish letters.
on collation list:
select * from ::fn_helpcollations()
there is not available:
sql_latin1_general_cp1250_bin
Please let me know if i am right or not.
Thanks in advance
by the way anybody knows how to get the list of ordered characters used by the sql server 2k same as the one extracted from the sql7. standard procedure sp_helpsort was used to receive the result at the start of this message, but for sql2k gives slightly different answer as:
only: Polish, binary sort
?
Do you have the ability/authority to dictate the collation used at these other sites?
I'm not sure what you mean by 'Windows collation'. When creating a new DB in SQL2k, you have the ability to set the code page/collation for that db and 'windows collation' is not an option.|||I can dictate what collation the other servers should use. When you setup the Sql Server you can choose either windows collation and the sort order or choose a Sql Collation and the sort order. What I want to know is whether or not it will simplify things to choose the Sql Collation. It seems to me that if I use the windows collation I may run into problems if the windows collation of the os is different on the other servers.|||Unless I have a reason not to, I use the default (SQL_Latin1_General_CP1_CI_AS). The 1252 character set, case insensitive, dictionary order, accent sensitive.
Being an international company, though , you may wish to use the 850 (multi-linqual) character set, but I'd stick with case insensitive, dictionary order, accent sensitive, unless you have another reason not to.
Unicode would be preferable, but you've already mentioned that you cannot use it.|||Hi!
I am looking for help concerning collation problem, so if You can help me I would be thankful...
I am changing from sql7 to sql2k and i would like to be sure i have maintaned the same sort order etc. i had in sql7:
Unicode data sorting
-------
Locale ID = 33280
case sensitive
Sort Order Description
----------------------------------------
Character Set = 4, cp1250
Code page 1250
Sort Order = 80, bin_cp1250
Binary Sort Order for the Code Page 1250 Character Set
Characters, in Order
----------------------------------------
! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@. A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~
_
_
So i think in sql2k i should set:
Polish_BIN
i need to have bin sorting and polish letters.
on collation list:
select * from ::fn_helpcollations()
there is not available:
sql_latin1_general_cp1250_bin
Please let me know if i am right or not.
Thanks in advance
by the way anybody knows how to get the list of ordered characters used by the sql server 2k same as the one extracted from the sql7. standard procedure sp_helpsort was used to receive the result at the start of this message, but for sql2k gives slightly different answer as:
only: Polish, binary sort
?
Collation problem with SQL 2000
I need to migrate an application from SQL 7.0 to SQL 2000. I know
that they use different collations, and this causes problems when
7.0 applications are moved to 2000. I would like to find a
permanent fix (so I'm not happy with using "COLLATE" in future
stored procedures) and I'd like to keep the default 2000 collation
(Latin1_General_CI_AS) in order to avoid problems if the
application needs to be moved to another 2000 server in the future.
So the only option is to change the collation of the restored DB.
What is the most efficient way of doing this?
Thanks,
that they use different collations, and this causes problems when
7.0 applications are moved to 2000. I would like to find a
permanent fix (so I'm not happy with using "COLLATE" in future
stored procedures) and I'd like to keep the default 2000 collation
(Latin1_General_CI_AS) in order to avoid problems if the
application needs to be moved to another 2000 server in the future.
So the only option is to change the collation of the restored DB.
What is the most efficient way of doing this?
Thanks,
Marco"Marco" <mdi00@.hotmail.com> wrote in message
news:1106659656.008295.140310@.c13g2000cwb.googlegr oups.com...
>I need to migrate an application from SQL 7.0 to SQL 2000. I know
> that they use different collations, and this causes problems when
> 7.0 applications are moved to 2000. I would like to find a
> permanent fix (so I'm not happy with using "COLLATE" in future
> stored procedures) and I'd like to keep the default 2000 collation
> (Latin1_General_CI_AS) in order to avoid problems if the
> application needs to be moved to another 2000 server in the future.
> So the only option is to change the collation of the restored DB.
> What is the most efficient way of doing this?
> Thanks,
> Marco
ALTER DATABASE can change a database's collation, and ALTER TABLE can change
a single column's collation. I seem to remember changing the database's
collation once and discovering that it didn't change the collation of
existing tables, only new ones, so make sure you test properly first.
Simon|||Marco (mdi00@.hotmail.com) writes:
> I need to migrate an application from SQL 7.0 to SQL 2000. I know
> that they use different collations, and this causes problems when
> 7.0 applications are moved to 2000. I would like to find a
> permanent fix (so I'm not happy with using "COLLATE" in future
> stored procedures) and I'd like to keep the default 2000 collation
> (Latin1_General_CI_AS) in order to avoid problems if the
> application needs to be moved to another 2000 server in the future.
> So the only option is to change the collation of the restored DB.
> What is the most efficient way of doing this?
You will need to bulk out the data, build the database from scripts
and reload data.
But I would only do this if this agrees with business requirements. If
you don't have an SQL 2000 installation, you can install with the
collation of your preference. If there already is an SQL 2000 instance
on the box with a different collation, one option is to install a
second instance on the box.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)