-- First, I'll create a test database. Without a collation specified, 
-- it will use the server's default collation.
CREATE DATABASE TestAdventureWorks
go

-- Verify the database collation
SELECT DATABASEPROPERTYEX('TestAdventureWorks', 'Collation')
go

-- database is set to SQL_Latin1_General_CP1_CI_AS as expected
-- this is a case-insensitive database

USE TestAdventureWorks
go

SELECT LastName collate database_default AS LastName
	, FirstName collate database_default AS FirstName
	, MiddleName collate database_default AS MiddleName
INTO MyTestContacts
FROM Adventureworks.Person.Contact
go

SELECT * 
FROM MyTestContacts 
WHERE Lastname = 'Adams'
go  -- (86 row(s) affected)

SELECT * 
FROM MyTestContacts 
WHERE Lastname = 'adams'
go -- (86 row(s) affected)

SELECT * 
FROM MyTestContacts 
WHERE Lastname COLLATE Latin1_General_CS_AS_KS_WS = 'Adams'
go  -- (86 row(s) affected)

SELECT * 
FROM MyTestContacts 
WHERE Lastname COLLATE Latin1_General_CS_AS_KS_WS = 'adams'
go -- (0 row(s) affected)

-- Next, create a view:
CREATE VIEW ContactLastNameCaseSensitive
AS
SELECT LastName COLLATE Latin1_General_CS_AS_KS_WS AS CSName
FROM MyTestContacts
go

SELECT * 
FROM ContactLastNameCaseSensitive
WHERE CSName = 'Adams'
go  -- (86 row(s) affected)

SELECT * 
FROM ContactLastNameCaseSensitive
WHERE CSName = 'adams'
go -- (0 row(s) affected)

-- Now, what if you want to get to this data through an index:
CREATE INDEX ChangeCollation ON MyTestContacts (LastName COLLATE Latin1_General_CS_AS_KS_WS)
but... this is not supported:
--Msg 156, Level 15, State 1, Line 1
--Incorrect syntax near the keyword 'COLLATE'.
go

-- So, how can you do this? Through either a computed column OR an indexed view:
ALTER TABLE MyTestContacts
ADD 
	CSName AS LastName COLLATE Latin1_General_CS_AS_KS_WS
go

SELECT * 
FROM MyTestContacts 
WHERE CSName = 'Adams'
go  -- (86 row(s) affected)

SELECT * 
FROM MyTestContacts 
WHERE CSName = 'adams'
go -- (0 row(s) affected)

CREATE INDEX CSNameInd ON MyTestContacts (CSName)
go

SELECT * 
FROM MyTestContacts 
WHERE CSName = 'Barlow'
go  -- (1 row(s) affected)

SELECT * 
FROM MyTestContacts 
WHERE CSName = 'barlow'
go -- (0 row(s) affected)

SELECT LastName collate database_default AS LastName
	, FirstName collate database_default AS FirstName
	, MiddleName collate database_default AS MiddleName
INTO MyTestContacts2
FROM Adventureworks.Person.Contact
go

ALTER TABLE MyTestContacts2
ALTER COLUMN LastName nvarchar(100) COLLATE Latin1_General_CS_AS_KS_WS
go

CREATE INDEX CSNameInd on MyTestContacts2 (LastName)
go

-- let's use a query that's highly selective (selective enough to use the index):
SELECT * 
FROM MyTestContacts2
WHERE LastName = N'Barlow'
go  -- (86 row(s) affected)

SELECT * 
FROM MyTestContacts2 
WHERE LastName = N'barlow'
go -- (0 row(s) affected)

UPDATE MyTestContacts2
 SET LastName = N'barlow'
 WHERE LastName = N'Barlow' 

SELECT * FROM MyTestContacts2
WHERE Lastname like N'b%'

SELECT * FROM MyTestContacts2
WHERE Lastname NOT LIKE N'B%'
go -- (18768 row(s) affected)

SELECT * FROM MyTestContacts2
WHERE Lastname NOT LIKE N'%%'
order by Lastname
go -- (18765 row(s) affected)

--ok - i'm intrigued as well
SELECT * FROM (SELECT * FROM MyTestContacts2
WHERE Lastname NOT LIKE N'B%') AS Bs
WHERE Lastname like '%B%'
go


SELECT * FROM MyTestContacts2
WHERE Lastname not like N'%A%'
	AND Lastname not like N'%B%'
	AND Lastname not like N'%C%'
	AND Lastname not like N'%D%'
	AND Lastname not like N'%E%'
	AND Lastname not like N'%F%'
	AND Lastname not like N'%G%'	
	AND Lastname not like N'%H%'	
	AND Lastname not like N'%I%'
	AND Lastname not like N'%J%'
	AND Lastname not like N'%K%'
	AND Lastname not like N'%L%'
	AND Lastname not like N'%M%'
	AND Lastname not like N'%N%'
	AND Lastname not like N'%O%'	
	AND Lastname not like N'%P%'	
	AND Lastname not like N'%Q%'
	AND Lastname not like N'%R%'
	AND Lastname not like N'%S%'
	AND Lastname not like N'%T%'
	AND Lastname not like N'%U%'
	AND Lastname not like N'%V%'
	AND Lastname not like N'%W%'	
	AND Lastname not like N'%X%'	
	AND Lastname not like N'%Y%'
	AND Lastname not like N'%Z%'
go	


select ascii(charindex(
sp_help MyTestContacts2

SELECT CHARINDEX('[A-Z]', LastName COLLATE Latin1_General_CS_AS_KS_WS)
FROM person.contact

WHERE DocumentID =6;
GO