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

Question

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"

Solution 1

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
)

Solution 2

Yes. It took something like 2 minutes to write the following script:

declare @T table (
    ID int not null,
    Val1 varchar(10) collate SQL_Latin1_General_CP1_CS_AS not null primary key
)

insert into @T(ID,Val1) values (1,'All'),(2,'ALL')

insert into @T(ID,Val1) values (3,'All')

Which first stored two rows, then errors on the second insert statement.