USE tempdb;

---------------------------------

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'dbo.test_collation')
                    AND type IN ( N'U' ) ) 
    DROP TABLE dbo.test_collation;

CREATE TABLE dbo.test_collation (name VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI); -- case-INsensitive collation
CREATE UNIQUE INDEX [uix] ON dbo.test_collation (name);

INSERT INTO dbo.test_collation(name)VALUES  ('ABC');
INSERT INTO dbo.test_collation(name)VALUES  ('Abc'); -- error, as expected with a case=INsensitive collation

---------------------------------

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'dbo.test_collation')
                    AND type IN ( N'U' ) ) 
    DROP TABLE dbo.test_collation;

CREATE TABLE dbo.test_collation (name VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS) -- case-sensitive collation
CREATE UNIQUE INDEX [uix] ON dbo.test_collation (name)

INSERT INTO dbo.test_collation(name)VALUES  ('ABC')
INSERT INTO dbo.test_collation(name)VALUES  ('Abc')  -- no error, as expected with a case=sensitive collation
GO