SET NOCOUNT, XACT_ABORT, QUOTED_IDENTIFIER ON
go
USE collationbase
go
DROP DATABASE IF EXISTS collationtest    
go
CREATE DATABASE collationtest
go
USE collationtest
go
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1
go
USE collationbase
go
DECLARE @collation sysname, 
        @testno    tinyint,
        @runorder  int,
        @label     varchar(20),
        @stmt      nvarchar(MAX),
        @d         datetime2(3),
        @i         int,
        @tookms    smallint,
        @collcur   CURSOR,
        @testcur   CURSOR,
        @dummy     int

        -- Test words for the point lookups.
DECLARE @unicode1  nvarchar(50) = N'vskočijo',
        @unicode2  nvarchar(50) = N'solmizacijskima',
        @unicode3  nvarchar(50) = N'Dobrljanom',
        @unicode4  nvarchar(50) = N'finih',
        @unicode5  nvarchar(50) = N'molznikovi',
        @unicode6  nvarchar(50) = N'Aleutčankini',
        @unicode7  nvarchar(50) = N'Dopplerjevimi',
        @unicode8  nvarchar(50) = N'nadledvična',
        @unicode9  nvarchar(50) = N'Wiesbadenčankinimi',
        @unicode10 nvarchar(50) = N'svetovali'


SET @collcur = CURSOR STATIC FOR
   SELECT collation, testno, runorder 
   FROM   collations
   WHERE  done = 0
     AND  cast(collationproperty(collation, 'CodePage') as int) = 0
   ORDER  BY runorder

OPEN @collcur

WHILE 1 = 1
BEGIN 
   FETCH @collcur INTO @collation, @testno, @runorder
   IF @@fetch_status <> 0
      BREAK

   RAISERROR ('Collation %s, testno %d, runorder %d.', 0, 1, @collation, @testno, @runorder) WITH NOWAIT

   -- Clear anything from any interupted test.
   DELETE results 
   WHERE  collation = @collation 
     AND  testno = @testno


   USE collationtest

   -- Create the test tabels and load them.
   SELECT @stmt = 'CREATE TABLE unicodewords(id     int          NOT NULL,
                             word   nvarchar(50) COLLATE ' + @collation + ' NOT NULL,
                             data   int          NOT NULL,
                             CONSTRAINT pk_unicodewords PRIMARY KEY(id)
   )'
   EXEC(@stmt)
   INSERT unicodewords (id, word, data)
      SELECT id, word, data
      FROM   collationbase.dbo.worddata

   SELECT @stmt = 'CREATE TABLE randomunicode(id         int            NOT NULL,
                              randomtext nvarchar(2000) COLLATE ' + @collation + ' NOT NULL,
                              CONSTRAINT pk_randomunicode PRIMARY KEY (id)
   )'
   EXEC(@stmt)

   INSERT randomunicode (id, randomtext)
      SELECT id, randomtext
      FROM   collationbase..randomtext

   SELECT @stmt = '
   CREATE TABLE unicodekeys (word nvarchar(50) COLLATE ' + @collation + ' NOT NULL,
                             data int          NOT NULL,
                             CONSTRAINT pk_unicodekeys PRIMARY KEY (word) 
   )'
   EXEC(@stmt)
   INSERT unicodekeys (word, data)
      SELECT word, SUM(data)
      FROM   unicodewords
      GROUP  BY word

    -- Cache warmup.
    SELECT @dummy = COUNT(DISTINCT data) FROM unicodekeys
    SELECT @dummy = COUNT(*) FROM randomunicode WHERE randomtext LIKE N'abc%'

    -- Start cursor for tests.
    SET @testcur = CURSOR FOR
       SELECT label, stmt
       FROM   collationbase..teststmts
       WHERE  isunicode = 1
       ORDER  BY id
       
    OPEN @testcur
    
    WHILE 1 = 1
    BEGIN
       FETCH @testcur INTO @label, @stmt
       IF @@fetch_status <> 0
          BREAK

       BEGIN TRY
          -- PRINT @stmt
          SELECT @d = sysdatetime()
          EXEC (@stmt)
          SELECT @tookms = datediff(ms, @d, sysdatetime())
       END TRY
       BEGIN CATCH
          ; THROW
       END CATCH

       INSERT collationbase..results(collation, testno, label, isunicode, tookms)
          VALUES (@collation, @testno, @label, 1, @tookms)
    END

    -- The lookup tests
    SET @i = 0
    SELECT @d = sysdatetime()
    WHILE datediff(ms, @d, sysdatetime()) < 400
    BEGIN
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode1
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode2
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode3
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode4
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode5
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode6
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode7
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode8
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode9
       SELECT @dummy = data FROM unicodekeys WHERE word = @unicode10
       SELECT @i += 10
    END
    INSERT collationbase..results(collation, testno, label, isunicode, tookms)
       VALUES(@collation, @testno, 'LOOKUP', 1, @i)


    -- Drop the test tables.
    DROP TABLE unicodewords, unicodekeys, randomunicode

    -- Move out of the database-
    USE collationbase

    UPDATE collations
    SET    done = 1
    WHERE  collation = @collation
      AND  testno    = @testno
END