Collation problem with table variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Collation problem with table variable

Author

Topic

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-06-19 : 09:59:33

Hi,

I wanted to know that do we have specify databas_default collation for table variable also like how we specify for temp table when collation of user database and temp database is different.

SQL SERVER 2005 BOL for @declare <variable> says table variable takes collation of temp db by default.

but following script proved that table variable takes collation of user database instead.

CREATE DATABASE TestCollation COLLATE Arabic_CI_AS

GO

USE TestCollation

GO

SELECT DATABASEPROPERTYEX('TestCollation', 'Collation')

GO

DECLARE @t table(col varchar(100))

SELECT name

, collation

FROM tempdb.dbo.syscolumns

WHERE id IN (SELECT id FROM tempdb.dbo.sysobjects where type = 'U')

GO

USE master

GO

DROP DATABASE TestCollation

Kindly advise as i am confused about this.

Many Thanks in advance.
Hrishikesh.

When solution is simple, God is answering….

Kristen
Test

22859 Posts

Posted - 2007-06-19 : 10:31:06

"@declare <variable> says table variable takes collation of temp db by default."

I thought it was the Collation for the server - but maybe that's splitting hairs and Server === TempDB

We put explicit COLLATE statement on every string datatype (char, varchar, text, and "N" equivalents) whenever we create a table:
CREATE TABLE XXX and #TempTable
and also
DECLARE @TableVar TABLE

Kristen

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2007-06-20 : 01:10:04

Hi Kirsten,

Thanks for the reply but what i am suprised at that my scripts show it takes collation of Database!!!!

and BOL suggests that it takes of tempdb. We are on our way to Internationalise our application so i am bit confused what to do aount table variable.

Many thanks,
Hrishikesh

When solution is simple, God is answering….

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 04:47:24

"We are on our way to Internationalise our application"

Well, IMHO you need to put a COLLATE phrase on every Column in a Table Creation statement that involves a String datatype.

You also need to force the COLLATE of any Comparison BETWEEN databases.

You are then immune to the actual collation of Server / TempDB, and also to potentially different collations in other databases (such as System database, and also databases on Remote/Linked servers, etc.)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources