- Salman Beg
- Dec 17, 2019
- 82.1k
- 0
- 5
Introduction In this blog, I will discuss the collate clause and also discuss how the expression type 'int' is invalid for collate clause error in the SQL server. The collate clause is used for case sensitive and case insensitive searches in the columns of the SQL server.
There are two types of collate clause present:
- SQL_Latin1_General_CP1_CS_AS for case sensitive.
- SQL_Latin1_General_CP1_CI_AS for case insensitive.
If we apply a case sensitive clause to a column, then for example, ‘a’ and ‘A’, will be different. But in the case of case insensitive, irrespective of any character or string, it will work.
By default, the collate clause will take SQL_Latin1_General_CP1_CI_AS (case insensitive).
To check whether a particular column is applied case sensitive or case insensitive, we can check under collation column, which is shown in below figure:
Now, let's create a table. Initially we will not apply any of the collate clauses.
The create table script is as shown below:
- CreateTableDetails
- (
- Idint,
- Namevarchar(10)
- )
In the above create table script, I didn’t apply any collate clause. Let’s check whether it applies default case insensitive clause or not and which is shown in the below figure:
Now, let’s change the collate clause from case insensitive to case sensitive in the name column.
The alter script is as shown below:
- alterTableDetails
- altercolumnNamevarchar(10)collateSQL_Latin1_General_CP1_CS_AS
And which is shown in the below figure:
But, now we want to apply either case sensitive or case insensitive collation to our id Column of data type int, whose alter Statement is as below:
- alterTableDetails
- altercolumnIdintcollateSQL_Latin1_General_CP1_CS_AS
When we run the above code, then "Expression type int is invalid for COLLATE clause". This error will generate because we cannot apply the collate clause to a column whose data type is int, shown in the below figure:
SQL_Latin1_General_CP1_CI_AS or SQL_Latin1_General_CP1_CS_AS collate clause only applies to those columns whose data type is char, varchar, nvarchar, and text.
But, if we apply to the collate clause to any other data type then it will give "Expression type int is invalid for COLLATE clause" error because case sensitive or case insensitive doesn’t apply to an int data type.
To resolve this above error we shouldn't apply any collate clause to int, float, decimal, etc.
Next Recommended ReadingRow_Number Function With PARTITION BY Clause In SQL Server
