How do you select all the rows that contain uppercase characters only? There sre three ways to do this
1 Compare with BINARY_CHECKSUM
2 Use COLLATE
3 Cast to varbinary

Let’s first create the table and also some test data

CREATE TABLE #tmp ( x VARCHAR(10) NOT NULL )

 Text ```1 2 3 4 5 6 7 8 ``` ```INSERT INTO #tmp SELECT 'Word' UNION ALL SELECT 'WORD' UNION ALL SELECT 'ABC' UNION ALL SELECT 'AbC' UNION ALL SELECT 'ZxZ' UNION ALL SELECT 'ZZZ' UNION ALL SELECT 'word' ```
```INSERT INTO #tmp
SELECT 'Word' UNION ALL
SELECT 'WORD' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'AbC' UNION ALL
SELECT 'ZxZ' UNION ALL
SELECT 'ZZZ' UNION ALL
SELECT 'word' ```

if we want only the uppercase columns then this is supposed to be our output

WORD
ABC
ZZZ

Let’s get started, first up is BINARY_CHECKSUM

 Text ```1 2 3 ``` ```SELECT x FROM #TMP WHERE BINARY_CHECKSUM(x) = BINARY_CHECKSUM(UPPER(x)) ```
```SELECT x
FROM #TMP
WHERE BINARY_CHECKSUM(x) = BINARY_CHECKSUM(UPPER(x)) ```

Second is COLLATE

 Text ```1 2 3 ``` ```SELECT x FROM #TMP WHERE x = UPPER(x) COLLATE SQL_Latin1_General_CP1_CS_AS ```
```SELECT x
FROM #TMP
WHERE x = UPPER(x) COLLATE SQL_Latin1_General_CP1_CS_AS ```

Third is Cast to varbinary

 Text ```1 2 3 ``` ```SELECT x FROM #TMP WHERE CAST(x AS VARBINARY(10)) = CAST(UPPER(x) AS VARBINARY(10)) ```
```SELECT x
FROM #TMP
WHERE CAST(x AS VARBINARY(10)) = CAST(UPPER(x) AS VARBINARY(10)) ```

Of course if you database is already case sensitive you can just do the following

 Text ```1 2 3 ``` ```SELECT x FROM #TMP WHERE UPPER(x) = x ```
```SELECT x
FROM #TMP
WHERE UPPER(x) = x ```

That will work, how do you find out what collation was used when your database was created? You can use DATABASEPROPERTYEX for that. I use the model DB here because when you create a new DB by default it inherits all the properties from the model DB.
When I run this

 Text ```1 ``` `SELECT DATABASEPROPERTYEX( 'model' , 'collation' ) `
`SELECT DATABASEPROPERTYEX( 'model' , 'collation' ) `

I get this as output: SQL_Latin1_General_CP1_CI_AS

What does all that junk mean? Well let’s run the following function (yes those are 2 colons :: )

 Text ```1 2 3 4 ``` ```  SELECT * FROM ::fn_helpcollations () WHERE NAME ='SQL_Latin1_General_CP1_CI_AS' ```
```
SELECT *
FROM ::fn_helpcollations ()
WHERE NAME ='SQL_Latin1_General_CP1_CI_AS' ```

The description column contains this info

Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data