When To Use LOWER/UPPER for comparing data in SQL Server – Alternatives

In SQL Server LOWER Can be used to convert uppercase/mixedcase string to lower case string. More about LOWER can be found here

Similarly, UPPER Can be used to convert lowercase/mixedcase string to upper case string. More about UPPER can be found here

Example:
[sql]
DECLARE @String VARCHAR(10)

SET @String = ‘ABcDeFGH’

SELECT @String STRING,LOWER(@String) LowerCaseSrting
[/sql]

Output:
[text]
STRING LowerCaseSrting
———- —————
ABcDeFGH abcdefgh

(1 row(s) affected)
[/text]

Comparing UpperCase and LowerCase strings
[sql]
DECLARE @String VARCHAR(10),@String1 VARCHAR(10)
SET @String = ‘ABcDeFGH’
SET @String1 = ‘abcdefgh’

IF @String = @String1
PRINT ‘Equal’
ELSE
PRINT ‘Not Equal’
[/sql]

Output:
[text]
Equal
[/text]
SQL Server by default creates databases with case insensitive collation. Hence we are getting output as Equal.

Now if we create a database in case sensitive collation and try the above query, we can notice that we get Not Equal as output.

Script:
[sql]
CREATE DATABASE testLower COLLATE SQL_Latin1_General_CP1_CS_AS
GO

USE testLower
GO

DECLARE @String VARCHAR(10),@String1 VARCHAR(10)
SET @String = ‘ABcDeFGH’
SET @String1 = ‘abcdefgh’

IF @String = @String1
PRINT ‘Equal’
ELSE
PRINT ‘Not Equal’

[/sql]

Output:
[text]
Not Equal
[/text]

The comparison will also fail when the tables are created with columns having case sensitive collation.

Script:
[sql]
CREATE TABLE TestTable(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS)
GO
CREATE TABLE ReferTable(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS)
GO

INSERT INTO TestTable(Value)
SELECT ‘lowercase’
UNION ALL
SELECT ‘LowerCase’
UNION ALL
SELECT ‘LOWERCASE’
GO

INSERT INTO ReferTable(Value)
SELECT ‘lowercase’
GO

–This query gives all three rows
–No need of lower
SELECT * FROM TestTable T JOIN ReferTable R
ON T.Value = R.Value

———————————————————
CREATE TABLE TestTable1(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS)
GO
CREATE TABLE ReferTable1(Value VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS)
GO

INSERT INTO TestTable1(Value)
SELECT ‘lowercase’
UNION ALL
SELECT ‘LowerCase’
UNION ALL
SELECT ‘LOWERCASE’
GO

INSERT INTO ReferTable1(Value)
SELECT ‘lowercase’
GO

–This query gives only one row
SELECT * FROM TestTable1 T JOIN ReferTable1 R
ON T.Value = R.Value
[/sql]

In such scenarios as discussed above, we have to specify LOWER along with the column names to compare UpperCase with Lower Case.

[sql]
–Needs Lower to give all the tree rows
SELECT * FROM TestTable1 T JOIN ReferTable1 R
ON LOWER(T.Value) = LOWER(R.Value)
[/sql]

Observations:
LOWER Can be ignored if the database and all the table columns that are specified in the query are in CASE INSENSITIVE Collation.

Alternatives:
We can specify the CASE INSENSITIVE Collation name with the column name to compare the column using Case insensitive Collation.
[sql]
–Alternative to LOWER/UPPER
–Using CASE InSensitive Collation names in the query
SELECT * FROM TestTable1 T JOIN ReferTable1 R
ON T.Value COLLATE SQL_Latin1_General_CP1_CI_AS =
R.Value COLLATE SQL_Latin1_General_CP1_CI_AS
[/sql]

Clean Up:
[sql]
USE master
GO

DROP DATABASE testLower
[/sql]

How to get number of rows in a table without using count function

How to get number of rows in a table without using count function?

In SQL Server you can get the approximate number of rows in the table using the below sample script.

[sql]
USE AdventureWorks
GO
DECLARE @tablename AS sysname = ‘Production.Product’;

DBCC UPDATEUSAGE (0, @tablename)
WITH NO_INFOMSGS;

SELECT [ROWS]
FROM SYS.indexes AS IND
INNER JOIN
SYS.partitions AS PRT
ON IND.object_id = PRT.object_id
AND IND.index_id = PRT.index_id
WHERE IND.object_id = OBJECT_ID(@tablename)
AND IND.index_id <= 1;
[/sql]

This script uses sys.partitions to fetch the number of rows in the table.
More about sys.partitions can be found here http://msdn.microsoft.com/en-us/library/ms175012(v=sql.105).aspx