Most DBAs, myself included, install SQL Server with the default server collation SQL_Latin1_General_CP1_CI_AS and all of our table columns get created using this default setting. This tip will look at the performance impacts of querying data with this setting as it compares to querying columns with the collation set to SQL_Latin1_General_CP1_CS_AS.
In order to carry out this small test we will create a simple 3 column table with an integer primary key column as well as two varchar columns. One of the varchar columns will have the collation set to SQL_Latin1_General_CP1_CI_AS and the other will have the collation set to SQL_Latin1_General_CP1_CS_AS. We'll also create an index on both columns. If we don't have an index on the column it would have to do a table scan in both cases and we would not see any difference in performance. Also, in the real world, any heavily queried column would be indexed so it makes sense to do the same for our performance test. Below is the query to create this table and indexes as well the T-SQL to load some random string data into the table.
CREATE TABLE [dbo].[test](
[col1] [bigint] NOT NULL,
[col2] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS,
[col3] [varchar](5) COLLATE SQL_Latin1_General_CP1_CS_AS,
PRIMARY KEY CLUSTERED ([col1] ASC) )
CREATE NONCLUSTERED INDEX [IX_test_col2] ON [dbo].[test] ([col2] ASC)
CREATE NONCLUSTERED INDEX [IX_test_col3] ON [dbo].[test] ([col3] ASC)
DECLARE @i INTEGER
DECLARE @x CHAR(1),@y CHAR(1),@z CHAR(1)
DECLARE @u INT,@v INT,@w INT
WHILE @i < 500000
INSERT INTO [dbo].[test] VALUES (@i,@x+@y+@z,@x+@y+@z)
Test Scenario 1 - Case insensitive query
Let's first take a look at the results when performing a case insensitive search of each of our columns. In this scenario, for the query on the column defined with SQL_Latin1_General_CP1_CI_AS we can simply use the equality operator in our WHERE clause. For the SQL_Latin1_General_CP1_CS_AS column we have two options, either we can use the T-SQL UPPER() function or specify the collation in the WHERE clause. Here are examples of each query.
select * from test where col2='npE'
select * from test where UPPER(col3)='NPE'
select * from test where col3 COLLATE SQL_Latin1_General_CP1_CI_AS='NPE'
Looking at the explain plan for each of these queries we can see that both the query with the function call as well as the one where we specify the collation need to perform an index scan to execute the query. The first query uses an index seek which will most likely execute much faster using fewer resources.
We can confirm the performance by looking at the SQL Trace results. From the chart below we can see that the first query outperforms the other two in every category.
CI Query - CI Column
CI Query - CS Column with Function
CI Query - CS Column with Collation
Test Scenario 2 - Case sensitive query
Now let's take a look at the results when performing a case sensitive search on each of our columns. In this scenario the query of the SQL_Latin1_General_CP1_Cs_AS column is pretty straightforward as we can just use the equality operator in our WHERE clause. For the SQL_Latin1_General_CP1_CI_AS column our only option is to specify the collation in the where clause. Here are examples of each query.
select * from test where col2 COLLATE SQL_Latin1_General_CP1_CS_AS='npE'
select * from test where col3='npE'
Looking at the explain plan for these queries we can see that again the query where we specify the collation needs to perform an index scan to execute the query. The later query uses an index seek which will most likely execute much faster using fewer resources.
We'll again confirm the performance by looking at the SQL Trace results. From the chart below we can see that the second query which uses an index seek outperforms the other query in every category as was the case in our previous scenario.
CS Query - CI Column with Collation
CS Query - CS Column
These simple test scenarios above illustrate how we need to be aware of how our data is being queried by the application. Once we know this we can then design the schema correctly so we can avoid any of these unnecessary index scans.
I believe SQL_Latin1_General_CP1_CI_AS is the default of U.S. But ever since we had a group of consultant developers and dba who worked in our org many years ago, they don't use the default but setup to use Latin1_General_CI_AS ,that becomes our standard when we setup our new servers in our organization. I searched on line a lot about the topic, it seems it is better to use windows collation Latin1_General_CI_AS. instead of SQL collation. SQL_Latin1_General_CP1_CI_AS is there for back compatibility.
I am also interested in how you get the result like the neat two rows table you have using SQL trace, how do you use it?
I guess what you want do say is, if a a table column has case insensitive collation, then any index on it will be , say, in uppercase. So, if you want to perform a case sensitive search, the whole index will be scanned instead of just a precise lookup. Is this correct? Are there other issues ? You are not quite clear about that.
Anyway, it's a good hint to consider collation as a performace factor.
Tuesday, May 06, 2014 - 11:34:06 PM - Ben Snaidero
I guess the real question here, is do we need "npE" to not be the same as "npe" or "NPE" or any of the other combinations. In a case-insensitive collation, they are all the same. If we don't need them to be different, then why would we use a case-sensitive collation. I think that is the point you are trying to make with your conclusion.
I don't think this has anything to do with the collation, but rather it has to do with the fact that you are changing the collation in one query, but not in the other. I would expect a query with a collate command or a function to be slower than the same query without it. Is there a performance issue when running the same query with different collations? For example, is it faster to find an item in a case insensitive column vs a case sensitive column (with no collate command) since there are less possible values to match in the case insensitive column? Also, does the server collation matter since a string in a query would probably use that collation therefore possibly causing a collation change for the query to run?