T-SQL Table variable with case sensitive columns - collate SQL_Latin1_General_CP1_CS_AS

sql-server tsql table-variable collate
gotqn picture gotqn · Aug 13, 2012 · Viewed 9.9k times · Source

Is it possible to have collate SQL_Latin1_General_CP1_CS_AS in table variable columns' definitions?

The reason I want to do this is because I have case sensitive information in my source table but when I insert it in the table variable there is a problem with the primary key (it is clustered) - duplicated values are detected - like 'All' and 'ALL'.

That's why I am trying to find a way to make the table variable columns case sensitive too as the following statement:

SELECT SERVERPROPERTY ('Collation')

gives me: "SQL_Latin1_General_CP1_CI_AS"

Answer

Mikael Eriksson picture Mikael Eriksson · Aug 13, 2012

Yes it is possible. You can specify the collation for each column when you declare your table variable.

declare @T table
(
  Col varchar(20) collate SQL_Latin1_General_CP1_CS_AS
)

Related questions

SELECT INTO a table variable in T-SQL

Got a complex SELECT query, from which I would like to insert all rows into a table variable, but T-SQL doesn't allow it. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. …

Read More
Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More
Table variable error: Must declare the scalar variable "@temp"

I am trying to achieve: declare @TEMP table (ID int, Name varchar(max)) insert into @temp SELECT ID, Name FROM Table SELECT * FROM @TEMP WHERE @TEMP.ID = 1 <--- ERROR AT @TEMP.ID But I'm getting the following error: Must …

Read More