Skip to content

Instantly share code, notes, and snippets.

@Sebazzz
Created January 14, 2017 11:41
Show Gist options
  • Save Sebazzz/9e9f3b6823e0fa2eede94b5b8b2527f4 to your computer and use it in GitHub Desktop.
Save Sebazzz/9e9f3b6823e0fa2eede94b5b8b2527f4 to your computer and use it in GitHub Desktop.
Repro for bug of collation issue in SQL Server 2012 (see: http://dba.stackexchange.com/q/160975/114952). Repro also on localdb v11.
/****** Object: Database [CollationTest] Script Date: 14-1-2017 12:40:46 ******/
CREATE DATABASE [CollationTest]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'CollationTest', FILENAME = N'C:\Users\Sebastiaan\CollationTest.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'CollationTest_log', FILENAME = N'C:\Users\Sebastiaan\CollationTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [CollationTest] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [CollationTest].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [CollationTest] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [CollationTest] SET ANSI_NULLS OFF
GO
ALTER DATABASE [CollationTest] SET ANSI_PADDING OFF
GO
ALTER DATABASE [CollationTest] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [CollationTest] SET ARITHABORT OFF
GO
ALTER DATABASE [CollationTest] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [CollationTest] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [CollationTest] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [CollationTest] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [CollationTest] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [CollationTest] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [CollationTest] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [CollationTest] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [CollationTest] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [CollationTest] SET DISABLE_BROKER
GO
ALTER DATABASE [CollationTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [CollationTest] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [CollationTest] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [CollationTest] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [CollationTest] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [CollationTest] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [CollationTest] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [CollationTest] SET RECOVERY SIMPLE
GO
ALTER DATABASE [CollationTest] SET MULTI_USER
GO
ALTER DATABASE [CollationTest] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [CollationTest] SET DB_CHAINING OFF
GO
ALTER DATABASE [CollationTest] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [CollationTest] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
/****** Object: Table [dbo].[Component] Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Component](
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Component] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Product] Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ProductComponentConcentration] Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductComponentConcentration](
[ProductId] [int] NOT NULL,
[ComponentId] [int] NOT NULL,
[Concentration] [float] NOT NULL,
CONSTRAINT [PK_ProductComponentConcentration] PRIMARY KEY CLUSTERED
(
[ProductId] ASC,
[ComponentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ProductStatement] Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductStatement](
[ProductId] [int] NOT NULL,
[StatementId] [int] NOT NULL,
CONSTRAINT [PK_ProductStatement] PRIMARY KEY CLUSTERED
(
[ProductId] ASC,
[StatementId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: UserDefinedFunction [dbo].[QueryConcatenationString] Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sebastiaan Dammann
-- Create date: 2017-01-14
-- Description: Repro for collation problem
-- =============================================
CREATE FUNCTION [dbo].[QueryConcatenationString]
(
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
RETURN NCHAR(13) + NCHAR(10)
END
GO
/****** Object: Table [dbo].[Statement] Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Statement](
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Statement] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: UserDefinedFunction [dbo].[QueryData] Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sebastiaan Dammann
-- Create date: 2017-01-14
-- Description: Repro for collation conflict
-- =============================================
CREATE FUNCTION [dbo].[QueryData]
(
)
RETURNS TABLE
AS
RETURN
(
WITH
cteCmtCauses AS (
SELECT ProductId = p.Id,
Name = s.Name
FROM Product p
INNER JOIN ProductStatement ps ON ps.ProductId = p.Id
INNER JOIN Statement s ON s.Id = ps.StatementId
UNION ALL
SELECT ProductId = p.Id,
Name = c.Name
FROM Product p
INNER JOIN ProductComponentConcentration pc ON pc.ProductId = p.Id
INNER JOIN Component c ON c.Id = pc.ComponentId
),
cteCmtCausesConcat AS (
SELECT ProductId = p.Id,
Concated = (
-- Here the issue happens
SELECT CONCAT(N'|', cte.Name COLLATE DATABASE_DEFAULT, dbo.QueryConcatenationString())
FROM cteCmtCauses cte
WHERE cte.ProductId = p.Id
FOR XML PATH(N''), TYPE
)
FROM Product p
)
SELECT p.Id, p.Name, cteCmtCausesConcat.Concated
FROM Product p
INNER JOIN cteCmtCausesConcat ON cteCmtCausesConcat.ProductId = p.Id
)
GO
ALTER DATABASE [CollationTest] SET READ_WRITE
GO
@srutzky
Copy link

srutzky commented Jan 14, 2017

Please make the following 3 changes to make the script more accurate:

  1. Add COLLATE Latin1_General_CI_AS to the CREATE DATABASE statement, at the end of it, right before the GO.
  2. Add:
    USE [CollationTest];  
    GO
    
    after the last ALTER DATABASE / GO
  3. Change the return type of [dbo].[QueryConcatenationString] to be NCHAR(2).

Thanks :-)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment