locked
Can I use a COLLATE clause in a temp table definition? RRS feed

  • Question

  • Can someone provide an example of using a COLLATE clause when defining a temporary table?  My goal is to get the temp table's definition to use the database_default value for collation so I don't get errors regarding collation conflicts in joins.
    Tuesday, May 13, 2014 3:04 PM

Answers

  • Here is a sample from one of my stored procedures:

    IF OBJECT_ID('TempDb..#Inventory', 'U') IS NOT NULL DROP TABLE #inventory;
        CREATE TABLE #inventory
            (
              pri_key INT PRIMARY KEY NONCLUSTERED ,
              ref_no NUMERIC(17, 0) ,
              locatn_id INT ,
              date_time datetime ,
              fifo_rank INT ,
              department CHAR(10) COLLATE DATABASE_DEFAULT ,
              category CHAR(10) COLLATE DATABASE_DEFAULT ,
              item CHAR(10) COLLATE DATABASE_DEFAULT ,
              invent_id INT ,
              trans_type CHAR(1) COLLATE DATABASE_DEFAULT ,
              quantity NUMERIC(8, 2) ,
              unit_cost MONEY
            )

    BTW, I have a very long article that I think also uses such temp tables

    T-SQL: FIFO Inventory Problem - Cost of Goods Sold


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Fanny Liu Thursday, May 22, 2014 5:44 AM
    Tuesday, May 13, 2014 3:15 PM

All replies

  • Here is a sample from one of my stored procedures:

    IF OBJECT_ID('TempDb..#Inventory', 'U') IS NOT NULL DROP TABLE #inventory;
        CREATE TABLE #inventory
            (
              pri_key INT PRIMARY KEY NONCLUSTERED ,
              ref_no NUMERIC(17, 0) ,
              locatn_id INT ,
              date_time datetime ,
              fifo_rank INT ,
              department CHAR(10) COLLATE DATABASE_DEFAULT ,
              category CHAR(10) COLLATE DATABASE_DEFAULT ,
              item CHAR(10) COLLATE DATABASE_DEFAULT ,
              invent_id INT ,
              trans_type CHAR(1) COLLATE DATABASE_DEFAULT ,
              quantity NUMERIC(8, 2) ,
              unit_cost MONEY
            )

    BTW, I have a very long article that I think also uses such temp tables

    T-SQL: FIFO Inventory Problem - Cost of Goods Sold


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Fanny Liu Thursday, May 22, 2014 5:44 AM
    Tuesday, May 13, 2014 3:15 PM
  • What is the issue? Collation can be real tricky (and only one person understands it in the entire world).

    You can use the COLLATE specification with CREATE TABLE for temporary table.

    You don't have to COLLATE DATABASE_DEFAULT since that is the db default anyhow, nonetheless it works. Columns inherit the database default collation.

    CREATE TABLE #Product(
    	[ProductID] [int] IDENTITY(1,1) NOT NULL,
    	[ProductName] [varchar](40) COLLATE Traditional_Spanish_ci_ai,
    	[SupplierID] [int] NULL,
    	[CategoryID] [int] NULL,
    	[QuantityPerUnit] [nvarchar](20) NULL,
    	[UnitPrice] [money] NULL,
    	[UnitsInStock] [smallint] NULL,
    	[UnitsOnOrder] [smallint] NULL,
    	[ReorderLevel] [smallint] NULL,
    	[Discontinued] [bit] NOT NULL)
    
    GO
    
    CREATE TABLE #Product1(
    	[ProductID] [int] IDENTITY(1,1) NOT NULL,
    	[ProductName] [varchar](40) COLLATE DATABASE_DEFAULT,
    	[SupplierID] [int] NULL,
    	[CategoryID] [int] NULL,
    	[QuantityPerUnit] [nvarchar](20) NULL,
    	[UnitPrice] [money] NULL,
    	[UnitsInStock] [smallint] NULL,
    	[UnitsOnOrder] [smallint] NULL,
    	[ReorderLevel] [smallint] NULL,
    	[Discontinued] [bit] NOT NULL)
    GO

    Collation blog: http://www.sqlusa.com/bestpractices2005/collatedatabasedefault/



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Tuesday, May 13, 2014 3:15 PM
  • The issue is to avoid collation errors, specifically, "Cannot resolve the collation conflict ..." errors from a JOIN involving a temp table.

    In my experience the default collation for a temp table's column is the SQL Server's tempdb collation and not the database's collation.  See for example,
    http://stackoverflow.com/questions/9648987/default-collation-of-temporary-tables

    Tuesday, May 13, 2014 3:49 PM
  • Thanks.  If the temp table is created in a stored procedure, let me ask what is probably an "obvious" question.

    Is the database referred to by the DATABASE_DEFAULT value in a COLLATE clause within the stored procedure always the database in which the stored procedure resides?
    Tuesday, May 13, 2014 3:53 PM
  • Yes. The DATABASE_DEFAULT is the DB which is opened when this stored procedure is run. Most likely it's the DB where this SP resides, but it may be that you are currently in one DB and running SP from another DB (with full syntax). In this case it will be the DB which is currently used.

    So,

    USE myDB

    execute myOtherDB.dbo.mySP

    -------

    The DATABASE_DEFAULT collation will be for myDB.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 13, 2014 4:00 PM
  • Question is whether you're trying to set collation same as collation of your original db or tempdb (in case they're different)



    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Tuesday, May 13, 2014 4:07 PM
  • COLLATE DATABASE_DEFAULT clause resolves the issue (collation error) when used in a query.

    It will not resolve the issue when used in table definition.

    If you do Address varchar(100) COLLATE DATABASE_DEFAULT is the same as Address varchar(100) .



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Tuesday, May 13, 2014 4:19 PM
  • It will if we need to compare temp tables with regular tables in SELECT statement. That was the reason I made this change in the stored procedure to generate temp table using correct collation for the DB.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 13, 2014 4:57 PM
  • I was referring to COLLATE DATABASE_DEFAULT in CREATE TABLE: it does not do anything, it will not solve anything.

    SELECT statement has collation conflict if the tempdb collation is different from the application database. More specifically if two columns in an operation has different collation.

    The real solution is: all databases (all columns) with the same collation in a SQL Server instance.

    Tempdb inherits collation from the model database. 

    Model database collation can only be change with reinstall.

    See below that COLLATE DATABASE_DEFAULT in CREATE TABLE does not solve the issue.

    USE master;
    GO
    CREATE DATABASE [testCollate] COLLATE Latin1_General_100_CI_AS_KS_WS_SC;
    GO
    USE testCollate;
    GO
    SELECT * INTO awProduct from AdventureWorks2008.Production.Product;
    GO
    -- (504 row(s) affected)
    
    CREATE TABLE #Product ( ID INT, ProductName varchar(100) COLLATE DATABASE_DEFAULT);
    GO
    INSERT #Product VALUES (1, 'Bearing Ball');
    GO
    --(1 row(s) affected)
    SELECT * FROM awProduct aw INNER JOIN #Product t
             ON aw.Name = t.ProductName;
    GO
    /*
    Msg 468, Level 16, State 9, Line 17
    Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and 
    "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    */
    SELECT * FROM awProduct aw INNER JOIN #Product t
             ON aw.Name = t.ProductName COLLATE DATABASE_DEFAULT;
    GO
    -- (1 row(s) affected)
    DROP DATABASE testCollate;
    GO
    DROP TABLE #Product;
    GO




    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012






    Tuesday, May 13, 2014 5:10 PM
  • Yep. That was my point. Just using DATABASE_DEFAULT in CREATE TABLE doesnt help. The best thing would be to use COLLATE clause  in the comparison statement to override the collation to avoid conflicts.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, May 13, 2014 5:15 PM
  • In the article at
    T-SQL: FIFO Inventory Problem - Cost of Goods Sold
    is it intentional that the temp table named #Inventory
    did not use the
    COLLATE DATABASE_DEFAULT
    clauses?

    CREATE TABLE [dbo].[#Inventory](
          [pri_key] [int] IDENTITY(1,1) NOT NULL,
          [ref_no] [numeric](17, 0) NOT NULL,
          [locatn_id] [int] NOT NULL,
          [date_time] [datetime] NOT NULL,
          [fifo_rank] [bigint] NULL,
          [department] [char](10) NOT NULL,
          [category] [char](10) NOT NULL,
          [item] [char](10) NOT NULL,
          [invent_id] [int] NOT NULL,
          [trans_type] [char](1) NOT NULL,
          [quantity] [numeric](8, 2) NOT NULL,
          [unit_cost] [money] NOT NULL
    ) ON [PRIMARY]

     
    Tuesday, May 13, 2014 5:29 PM
  • If you use DATABASE_DEFAULT in the CREATE TABLE for creating temp table which then will be used in SELECT statement involving regular tables, it will be enough, although adding COLLATE DATABASE_DEFAULT in the SELECT statement for character columns will be an extra precaution.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 13, 2014 5:31 PM
  • Hi Naomi,

    Can you change my example to illustrate your point? Thanks.



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Tuesday, May 13, 2014 5:58 PM
  • Can you show an example of adding COLLATE DATABASE_DEFAULT in the SELECT statement for character columns ?

    Tuesday, May 13, 2014 6:00 PM
  • Kalman has it in his example (see code 2). I think I stand corrected as I also got the error. 

    Also, in the article it seems to be a bug, I am not sure why the COLLATE is not used. I'll re-check the article.

    UPDATE. I fixed the article. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Tuesday, May 13, 2014 6:13 PM
    Tuesday, May 13, 2014 6:07 PM
  • I was referring to COLLATE DATABASE_DEFAULT in CREATE TABLE: it does not do anything, it will not solve anything.

    SELECT statement has collation conflict if the tempdb collation is different from the application database. More specifically if two columns in an operation has different collation.

    The real solution is: all databases (all columns) with the same collation in a SQL Server instance.

    Tempdb inherits collation from the model database. 

    Model database collation can only be change with reinstall.

    See below that COLLATE DATABASE_DEFAULT in CREATE TABLE does not solve the issue.

    USE master;
    GO
    CREATE DATABASE [testCollate] COLLATE Latin1_General_100_CI_AS_KS_WS_SC;
    GO
    USE testCollate;
    GO
    SELECT * INTO awProduct from AdventureWorks2008.Production.Product;
    GO
    -- (504 row(s) affected)
    
    CREATE TABLE #Product ( ID INT, ProductName varchar(100) COLLATE DATABASE_DEFAULT);
    GO
    INSERT #Product VALUES (1, 'Bearing Ball');
    GO
    --(1 row(s) affected)
    SELECT * FROM awProduct aw INNER JOIN #Product t
             ON aw.Name = t.ProductName;
    GO
    /*
    Msg 468, Level 16, State 9, Line 17
    Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and 
    "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    */
    SELECT * FROM awProduct aw INNER JOIN #Product t
             ON aw.Name = t.ProductName COLLATE DATABASE_DEFAULT;
    GO
    -- (1 row(s) affected)
    DROP DATABASE testCollate;
    GO
    DROP TABLE #Product;
    GO




    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Setting the COLLATE to DATABASE_DEFAULT does make a difference.  When you create a column in permanent table table (either by CREATE TABLE or by ALTER TABLE ADD) and you don't specify the collation of the columnthat column gets the default collation of the current the current database.   If you create a temporary table, the columns in the temporary table get the default collation of tempdb (which is also the default collation of master and model).  However, you can use DATABASE_DEFAULT to force the collation to match the default collation of the current database.  

    You can see that it makes a difference by running the following code (which is much the same as yours except I use a CREATE TABLE to create the awProduct table.  Then when you use DATABASE_DEFAULT for the temp table it works fine, but when you don't use DATABASE_DEFAULT you get the collation conflict error.

    USE master;
    GO
    CREATE DATABASE [testCollate] COLLATE Latin1_General_100_CI_AS_KS_WS_SC;
    GO
    USE testCollate;
    GO
    CREATE TABLE awProduct(Name nvarchar(100));
    INSERT awProduct(Name) SELECT Name from AdventureWorks2012.Production.Product;
    GO
    -- (504 row(s) affected)
    
    CREATE TABLE #Product ( ID INT, ProductName varchar(100));
    GO
    INSERT #Product VALUES (1, 'Bearing Ball');
    GO
    --(1 row(s) affected)
    SELECT 'Without DATABASE_DEFAULT the following select will get error'
    GO
    SELECT * FROM awProduct aw INNER JOIN #Product t
        ON aw.Name = t.ProductName;
    /*
    Msg 468, Level 16, State 9, Line 4
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.
    */
    GO
    DROP TABLE #Product;
    
    CREATE TABLE #Product ( ID INT, ProductName varchar(100) COLLATE DATABASE_DEFAULT);
    GO
    INSERT #Product VALUES (1, 'Bearing Ball');
    GO
    --(1 row(s) affected)
    SELECT 'With DATABASE_DEFAULT the following select will work'
    GO
    SELECT * FROM awProduct aw INNER JOIN #Product t
        ON aw.Name = t.ProductName;
    /*
    Name       ID     ProductName
    Bearing Ball    1 Bearing Ball
    */
    GO
    use master
    go
    drop database testCollate
    go
    drop table #Product

    So then, of course, the question is, why did the code you gave fail when you used DATABASE_DEFAULT?  The reason is you used SELECT INTO to create the awProduct table.  When you use SELECT INTO, the new table has the same columns as the columns in your SELECT clause.  The columns have the same attributes as the source (datatype, length, etc).  In particular, they get the same COLLATION.  So when you did that SELECT INTO, you got the collation of the columns in your AdventureWorks2008.Production.Product table (which in your case is SQL_Latin1_General_CP1_CI_AS).  But by using the DATABASE_DEFAULT clause in the creation of #Product, you get the collation of your current database, which is Latin1_General_100_CI_AS_KS_WS_SC.  So you get the collation error.

    It is true you can avoid that error by specifying the COLLATE clause in your select statement.  But you don't want to do that.  If you compare two column of different collations by using the COLLATE clause, SQL is unable to use any indexes you have on those columns.

    Of course, it is best if all the databases and all the columns in  your instance have the same collation.  Then you avoid all of this discussion.  But if you have databases that have a different collation than the model database in your instance, then DATABASE_DEFAULT is very useful when creating temp tables to make sure the collations of columns in that temp table match the default collation of the database you are currently attached to.

    Tom


    • Proposed as answer by Naomi N Tuesday, May 13, 2014 7:43 PM
    Tuesday, May 13, 2014 7:36 PM
  • Below is what I see now.  Does  [trans_type]  need the COLLATE DATABASE_DEFAULT clause?

    CREATE TABLE [dbo].[#Inventory](
          [pri_key] [int] IDENTITY(1,1) NOT NULL,
          [ref_no] [numeric](17, 0) NOT NULL,
          [locatn_id] [int] NOT NULL,
          [date_time] [datetime] NOT NULL,
          [fifo_rank] [bigint] NULL,
          [department] [char](10) COLLATE DATABASE_DEFAULT NOT NULL,
          [category] [char](10) COLLATE DATABASE_DEFAULT NOT NULL,
          [item] [char](10) COLLATE DATABASE_DEFAULT NOT NULL,
          [invent_id] [int] NOT NULL,
          [trans_type] [char](1) NOT NULL,
          [quantity] [numeric](8, 2) NOT NULL,
          [unit_cost] [money] NOT NULL
    ) ON [PRIMARY]
    

    Tuesday, May 13, 2014 7:44 PM
  • Thanks for the explanation, Tom. I had a suspicion that Kalman's code failed due to SELECT INTO logic, but didn't have time to test. You explained it very nicely.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 13, 2014 7:44 PM
  • Thanks!    Is there an article online (perhaps an MSDN one?) that explains this as well as you just did?

    Tuesday, May 13, 2014 7:50 PM
  • Yes, it does need it. I'll add this change as well, thanks.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 13, 2014 7:54 PM
  • Another fascinated collation thread! In summary:

    USE dbX;

    GO

    CREATE TABLE #Product ( ID INT, ProductName varchar(100) );
    GO

    Inherits the collation of tempdb.

    CREATE TABLE #Product ( ID INT, ProductName varchar(100) COLLATE DATABASE_DEFAULT);
    GO

    Inherits the collation of dbX.

    SELECT ProductID, Name INTO awProduct from AdventureWorks2008.Production.Product;
    GO

    Inherits the collation of AdventureWorks2008.Production.Product.Name .



    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Tuesday, May 13, 2014 10:04 PM
  • Does this issue and solution also apply to table variables?
    Wednesday, May 14, 2014 2:44 PM
  • I just made a quick test and table variable inherits current DB default collation for character columns. So, if you want to use that temp table in that DB, you don't need to add COLLATION clause in the declare @t table statement.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, May 14, 2014 3:03 PM
  • Does this issue and solution also apply to table variables?
    Nope table variables are created in memory rather than inside tempdb. So this issue wont work.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, May 14, 2014 3:06 PM
  • Thanks.  I would guess that specifying COLLATE clauses would be allowed in a table variable, and that DATABASE_DEFAULT would have the an effect consistent with using it for temp tables.  Please let me know if that is not correct.
    Wednesday, May 14, 2014 3:10 PM
  • Yes, you can specify COLLATE clause when creating table variable.

    DECLARE @t TABLE (col VARCHAR(20) collate SQL_Latin1_General_CP1_CI_AS)
    
    INSERT INTO @t
    VALUES ('Test')
    	,('Shirt')
    
    SELECT *
    FROM SiriusSQL.dbo.items i
    INNER JOIN @t T ON i.descrip LIKE '%' + col + '%'

    However, table variable inherits default database collection of the database which is active when it's created.

    E.g.

    use testCollate
    
    DECLARE @t TABLE (col VARCHAR(20)) -- collate SQL_Latin1_General_CP1_CI_AS)
    
    INSERT INTO @t
    VALUES ('Test')
    	,('Shirt')
    
    use SiriusSQL
    SELECT *
    FROM SiriusSQL.dbo.items i
    INNER JOIN @t T ON i.descrip LIKE '%' + col + '%'

    Gives an error:

    Msg 468, Level 16, State 9, Line 12
    Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and "SQL_Latin1_General_CP1_CI_AS" in the like operation.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Wednesday, May 14, 2014 3:17 PM
    Wednesday, May 14, 2014 3:16 PM
  • Nope table variables are created in memory rather than inside tempdb. So this issue wont work.

    Incorrect on the first account - table variables are tables in tempdb. They are not memory only. That is a common misconception.

    The other point is correct - table variables inherit database collation, although it may be different if you have a database in compatibility mode 80, as in SQL 2000, table variables used the system collation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 14, 2014 3:23 PM
  • The other point is correct - table variables inherit database collation, although it may be different if you have a database in compatibility mode 80, as in SQL 2000, table variables used the system collation.

    I tested this now. There is no difference with compatibility level; table variables in SQL 2005 and later always use the database collation as the default.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 14, 2014 10:16 PM
  • Hi All,

    My SQL Server has different Collation and User Databases have Different Collation. 

    I have many Stored Procedure like this and want to know how can I fix them:

    SELECT * INTO #TEMP FROM Table1.

    Now in this case, #Temp table will have Server Collation and User Table1 will have database collation. There is any way I can force #Temp Table to use User database Collation in which it is getting used. 

    I know one way to CREATE TABLE #temp(column_1 VARCHAR(2) COLLATE database_default) but I don't want to go and change all my Stored Procedure. 


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, July 17, 2020 6:28 AM
  • My SQL Server has different Collation and User Databases have Different Collation. 

    I have many Stored Procedure like this and want to know how can I fix them:

    SELECT * INTO #TEMP FROM Table1.

    Now in this case, #Temp table will have Server Collation and User Table1 will have database collation. There is any way I can force #Temp Table to use User database Collation in which it is getting used. 

    I know one way to CREATETABLE #temp(column_1VARCHAR(2)COLLATE database_default)* but I don't want to go and change all my Stored Procedure. *

    We can surely answer your question. But please start a new thread, and don't piggyback on a six year old thread.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 17, 2020 8:18 AM
  • I also suggest to start a new thread for your question. You would still need to modify your procedures one way or another. You can explicitly cast your char columns to correct collation as part of the CAST syntax. You can also add COLLATE clause when comparing char columns.

    In any case, some changes in the procedures are required.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, July 17, 2020 1:29 PM