Classements et pages de code

In-Memory OLTP impose des restrictions sur les pages de code prises en charge pour les colonnes (var)char dans les tables optimisées en mémoire, ainsi que sur les collations prises en charge utilisées dans les index et les procédures stockées compilées en mode natif.

La page de codes d’une valeur (var)char détermine le mappage entre les caractères et la représentation d’octets stockée dans la table. Par exemple, avec la page de codes Windows Latin 1 (1252 ; la valeur par défaut de SQL Server), le caractère « a » correspond à l’octet 0x61.

La page de codes d’une valeur (var)char est déterminée par le classement associé à la valeur. Par exemple, l'interclassement SQL_Latin1_General_CP1_CI_AS a la page de code associée 1252.

Le classement d’une valeur est hérité du classement de base de données ou peut être spécifié explicitement à l’aide du mot clé COLLATE. Le classement de base de données ne peut pas être modifié si la base de données contient des tables optimisées en mémoire ou des procédures stockées compilées en mode natif. L’exemple suivant définit le classement de base de données et crée une table qui a une colonne avec un autre classement. La base de données utilise le classement sans respect de la casse latine.

Les index peuvent uniquement être créés sur des colonnes de chaîne s’ils utilisent un classement BIN2. La variable LastName utilise le classement BIN2. FirstName utilise la valeur par défaut de la base de données, qui est CI_AS (insensible à la casse, sensible aux accents).

CREATE DATABASE IMOLTP  
  
ALTER DATABASE IMOLTP ADD FILEGROUP IMOLTP_mod CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE IMOLTP ADD FILE( NAME = 'IMOLTP_mod' , FILENAME = 'c:\data\IMOLTP_mod') TO FILEGROUP IMOLTP_mod;  
--GO  
  
--  set the database collations  
ALTER DATABASE IMOLTP COLLATE Latin1_General_100_CI_AS  
GO  
  
--  
USE IMOLTP   
GO  
  
-- create a table with collation  
CREATE TABLE Employees (  
  EmployeeID int NOT NULL ,   
  LastName nvarchar(20) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX IX_LastName NONCLUSTERED,   
  FirstName nvarchar(10) NOT NULL ,  
  CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED HASH(EmployeeID)  WITH (BUCKET_COUNT=1024)  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)  
GO  

Les restrictions suivantes s’appliquent aux tables optimisées en mémoire et aux procédures stockées compilées en mode natif :

• Les colonnes (var)char dans les tables optimisées pour la mémoire doivent utiliser le classement de la page de code 1252. Cette restriction ne s’applique pas aux colonnes n(var)char. Le code suivant récupère tous les interclassements 1252 :

  -- all supported collations for (var)char columns in memory-optimized tables  
  select * from sys.fn_helpcollations()  
  where collationproperty(name, 'codepage') = 1252;  
  

  Si vous devez stocker des caractères non latins, utilisez des colonnes n(var)char.

• Les index sur les colonnes (n)(var)char ne peuvent être spécifiés qu’avec des classements BIN2 (voir le premier exemple). La requête suivante récupère toutes les intercalations BIN2 prises en charge :

  -- all supported collations for indexes on memory-optimized tables and   
  -- comparison/sorting in natively compiled stored procedures  
  select * from sys.fn_helpcollations() where name like '%BIN2'  
  

  Si vous accédez à la table via Transact-SQL interprété, vous pouvez utiliser le mot clé pour modifier le COLLATE classement avec des expressions ou des opérations de tri. Consultez le dernier exemple de cet exemple.

• Les procédures stockées compilées en mode natif ne peuvent pas utiliser des paramètres, des variables locales ou des constantes de chaîne de type (var)char si le classement de base de données n’est pas un classement de page de codes 1252.

• Toutes les expressions et opérations de tri à l’intérieur des procédures stockées compilées en mode natif doivent utiliser des classements BIN2. L’implication est que toutes les comparaisons et opérations de tri sont basées sur les points de code Unicode des caractères (représentations binaires). Par exemple, tout tri respecte la casse (l''ordre alphabétique place 'Z' avant 'a'). Si nécessaire, utilisez des Transact-SQL interprétés pour des tris et comparaisons insensibles à la casse.

• La troncation des données UTF-16 n’est pas prise en charge dans les procédures stockées compilées en mode natif. Cela signifie que les valeurs n(var)char(n) ne peuvent pas être converties en type n(var)char(i), si i<n, si le classement a _SC propriété. Par exemple, les éléments suivants ne sont pas pris en charge :

  -- column definition using an _SC collation  
   c2 nvarchar(200) collate Latin1_General_100_CS_AS_SC not null   
  -- assignment to a smaller variable, requiring truncation  
   declare @c2 nvarchar(100) = '';  
   select @c2 = c2  
  

  Les fonctions de manipulation de chaînes, telles que LEN, SUBSTRING, LTRIM et RTRIM avec des données UTF-16, ne sont pas prises en charge dans les procédures stockées compilées en mode natif. Vous ne pouvez pas utiliser ces fonctions de manipulation de chaîne pour les valeurs n(var)char qui ont un classement _SC.

  Déclarez des variables à l’aide de types suffisamment volumineux pour éviter la troncation.

L’exemple suivant montre quelques-unes des implications et solutions de contournement pour les limitations de classement dans In-Memory OLTP. L’exemple utilise la table Employees spécifiée ci-dessus. Cet exemple répertorie tous les employés. Notez que pour LastName, en raison du classement binaire, les noms majuscules sont triés avant le minuscule. Par conséquent, « Thomas » est antérieur à « nolan », car les caractères majuscules ont des points de code inférieurs. FirstName a une collation insensible à la casse. Par conséquent, le tri est par lettre de l’alphabet, et non par point de code des caractères.

-- insert a number of values  
INSERT Employees VALUES (1,'thomas', 'john')  
INSERT Employees VALUES (2,'Thomas', 'rupert')  
INSERT Employees VALUES (3,'Thomas', 'Jack')  
INSERT Employees VALUES (4,'Thomas', 'annie')  
INSERT Employees VALUES (5,'nolan', 'John')  
GO  
  
-- ===========  
SELECT EmployeeID, LastName, FirstName FROM Employees  
ORDER BY LastName, FirstName  
GO  
  
-- ===========  
-- specify collation: sorting uses case-insensitive collation, thus 'nolan' comes before 'Thomas'  
SELECT * FROM Employees  
ORDER BY LastName COLLATE Latin1_General_100_CI_AS, FirstName  
GO  
  
-- ===========  
-- retrieve employee by Name  
-- must use BIN2 collation for comparison in natively compiled stored procedures  
CREATE PROCEDURE usp_EmployeeByName @LastName nvarchar(20), @FirstName nvarchar(10)  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = N'us_english'  
)  
  SELECT EmployeeID, LastName, FirstName FROM dbo.Employees  
  WHERE   
    LastName = @LastName AND  
    FirstName COLLATE Latin1_General_100_BIN2 = @FirstName  
  
END  
GO  
  
-- this does not return any rows, as EmployeeID 1 has first name 'john', which is not equal to 'John' in a binary collation  
EXEC usp_EmployeeByName 'thomas', 'John'  
  
-- this retrieves EmployeeID 1  
EXEC usp_EmployeeByName 'thomas', 'john'  

Voir aussi

In-Memory OLTP (OptimisationIn-Memory)