Category: Data Design

Recently I had a problem where i need to search for;

1. Foo
2. foo

SQL Server default behavior is to do case insensitive search. so if run a query like this;

IF OBJECT_ID('tempdb..#FooTable') IS NOT NULL DROP TABLE #FooTable
SELECT x.* 
INTO #FooTable
FROM
(
	SELECT 1 AS Id, 'Foo' AS UserName
	UNION ALL
	SELECT 2 As Id, 'foo' AS UserName
) x

I will get two records back.

To do a case sensitive search, do this;

SELECT UserName
FROM #FooTable
WHERE 1=1
AND UserName = 'foo' COLLATE SQL_Latin1_General_CP1_CS_AS

Result would be;

For join operations, let’s create another table;

--Now add a second table, say salary
IF OBJECT_ID('tempdb..#FooSalaryTable') IS NOT NULL DROP TABLE #FooSalaryTable
SELECT x.* 
INTO #FooSalaryTable
FROM
(
	SELECT 1 AS Id, 'Foo' AS FooId, 4000 AS UserSalary
	UNION ALL
	SELECT 2 As Id, 'foo' As FoodId, 6000 AS UserSalary
) x

SELECT * FROM #FooSalaryTable

This is join query;

--example with join
SELECT 
	x.UserName, y.UserSalary
FROM #FooTable x
JOIN #FooSalaryTable y on x.UserName = y.FooId COLLATE SQL_Latin1_General_CP1_CS_AS
WHERE 1=1
AND x.UserName = 'foo' COLLATE SQL_Latin1_General_CP1_CS_AS

and results;

For more info, follow these articles;

https://stackoverflow.com/questions/1831105/how-to-do-a-case-sensitive-search-in-where-clause-im-using-sql-server

https://www.mssqltips.com/sqlservertip/1032/case-sensitive-search-on-a-case-insensitive-sql-server/

https://stackoverflow.com/questions/17172175/add-column-to-temp-table-invalid-column-name-decimal

There are situations where you might receive this error message “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.”. Even when using a TRY CATCH block in the stored procedure and a ROLLBACK in the catch block, this error still occurs. One reason this is not captured is because fatal errors are not caught and the transaction remains open. In this tip we will look at how you can handle this issue.

Refer to this article for solution;

Recently i had to search for a specific column in database. My assumption was that the column name would be same as the column name in parent table. For example Person table PersonID column would be name same in all reference tables.

USE MyDatabase
--find all columns with a specific name in database
SELECT  OBJECT_SCHEMA_NAME(ac.object_id) SchemaName, 
        OBJECT_NAME(ac.object_id) TableName, 
        ac.name as ColumnName, tp.name DataType
FROM sys.all_columns ac
INNER JOIN sys.types tp ON tp.user_type_id = ac.user_type_id
WHERE ac.name = 'YourColumnName'
GO

Resources

Read more here…

This is a handy list of basic commands;

Append a new column to table

ALTER TABLE table_name
ADD column_name data_type column_constraint;

Append multiple columns to a table

ALTER TABLE table_name
ADD 
    column_name_1 data_type_1 column_constraint_1,
    column_name_2 data_type_2 column_constraint_2,
    ...,
    column_name_n data_type_n column_constraint_n;

Create a new table

CREATE TABLE table_name (
    colulmn_name data_type_1 IDENTITY PRIMARY KEY,
    colulmn_name data_type_2 NOT NULL,
    column_name data_type_3  NULL
);