Utilisation des données spécifiques

Les types de données spécifiques sont ceux qui ne rentrent dans aucune des autres catégories de types de données. Par exemple, pour stocker une donnée comme 1 ou 0 correspondant à des valeurs oui ou non dans une étude de clients, utilisez le type de données bit. Microsoft SQL Server 2005 possède plusieurs types de données qui rentrent dans cette catégorie :

• bit
  Les données bit ne doivent pas être mises entre guillemets simples. Il s'agit de données numériques semblables aux données numériques et entières SQL Server, sauf que seuls les 0 et les 1 peuvent être stockés dans des colonnes bit.

  Les valeurs de chaîne TRUE et FALSE peuvent être converties en valeurs bit, comme illustré ci-dessous.

  SELECT CONVERT (bit, 'true'), CONVERT(bit, 'false')
  

  True est converti en 1 et False en 0.

• sql_variant
  Le type de données sql_variant dans SQL Server permet à une colonne, un paramètre ou une variable de stocker des valeurs de données de différents types de données. Chaque instance d'une colonne sql_variant enregistre la valeur de données et les métadonnées décrivant la valeur : ses type de données de base, taille maximale, échelle, précision et classement.

  La deuxième table de l'exemple suivant contient une colonne sql_variant :

  CREATE TABLE ObjectTable (ObjectID int CONSTRAINT PKObjectTable PRIMARY KEY, ObjectName nvarchar(80), ObjectWeight decimal(10,3), ObjectColor nvarchar(20))
  CREATE TABLE VariablePropertyTable (ObjectID int REFERENCES ObjectTable(ObjectID), PropertyName      nvarchar(100), PropertyValue sql_variant, CONSTRAINT PKVariablePropertyTable PRIMARY KEY(ObjectID, PropertyName))
  

  Pour obtenir les informations de métadonnées relatives à une instance sql_variant spécifique, utilisez la fonction SQL_VARIANT_PROPERTY.

• sysname
  Les instances de SQL Server incluent un type de données défini par l'utilisateur, appelé sysname. sysname est utilisé pour les colonnes de tables, les variables et les paramètres de procédures stockées qui stockent des noms d'objets. La définition exacte de sysname est fonction des règles des identificateurs, de sorte qu'elle varie selon les instances de SQL Server. sysname présente les mêmes fonctions que le type de données nvarchar(128). Les versions de SQL Server jusqu'à la version 6.5 ne géraient que des identificateurs plus petits, de sorte que sysname était défini comme varchar(30).

  Important :
  Dans les bases de données qui respectent la casse ou qui possèdent un classement binaire, sysname n'est reconnu comme type de données système SQL Server que s'il est affiché en minuscules.

• table
  Similaire aux tables temporaires, le type de données table permet de stocker un ensemble de résultats en vue d'un traitement ultérieur. Ce type de données permet uniquement de définir des variables locales de type table et la valeur de retour d'une fonction utilisateur.

  La définition d'une variable de table ou d'une valeur de retour comprend les définitions des colonnes, le type de données, la précision et l'échelle de chaque colonne, ainsi que les contraintes facultatives PRIMARY KEY, UNIQUE, NULL et CHECK. Le type de données ne peut pas être un type de données défini par l'utilisateur.

  Le format des lignes stockées dans une variable de table ou renvoyées par une fonction définie par l'utilisateur doit être défini lorsque la variable est déclarée ou que la fonction est créée. La syntaxe est basée sur la syntaxe CREATE TABLE. Par exemple :

  DECLARE @TableVar TABLE (Cola int PRIMARY KEY, Colb char(3))
  
  INSERT INTO @TableVar VALUES (1, 'abc')
  INSERT INTO @TableVar VALUES (2, 'def')
  
  SELECT * FROM @TableVar
  GO
  

  Les variables de table et les fonctions définies par l'utilisateur qui retournent une table peuvent uniquement être utilisées dans certaines instructions SELECT et INSERT, ainsi que là où les tables sont prises en charge dans les instructions UPDATE, DELETE et DECLARE CURSOR. Les variables de table et les fonctions définies par l'utilisateur qui retournent une table ne peuvent pas être utilisées dans d'autres instructions Transact-SQL.

  Les index ou autres contraintes appliqués à la table doivent être définis dans la variable DECLARE ou dans l'instruction CREATE FUNCTION. Ils ne peuvent pas être appliqués ultérieurement car les instructions CREATE INDEX ou ALTER TABLE ne peuvent pas référencer des variables de table et des fonctions définies par l'utilisateur.

  Pour plus d'informations sur la syntaxe utilisée pour définir les variables table et les fonctions définies par l'utilisateur, consultez DECLARE @local\_variable (Transact-SQL) et CREATE FUNCTION (Transact-SQL).

• timestamp
  Le type de données SQL Server timestamp n'a aucun lien avec les dates et les heures. Les valeurs SQL Server timestamp sont des nombres binaires qui indiquent l'ordre relatif dans lequel ont lieu les modifications dans une base de données. Le type de données timestamp visait initialement à prendre en charge les algorithmes de récupération SQL Server. Chaque fois qu'une page était modifiée, elle était marquée avec la valeur @@DBTS actuelle et @@DBTS était incrémenté. Cela suffisait pour que la récupération détermine l'ordre relatif dans lequel les pages étaient modifiées, mais les valeurs timestamp n'avaient alors pas de lien entre elles.

  Dans SQL Server version 7.0 et SQL Server 2000, @@DBTS est incrémenté uniquement pour être utilisé dans les colonnes timestamp. Si une table contient une colonne timestamp, chaque fois qu'une ligne est modifiée par une instruction INSERT, UPDATE ou DELETE, la valeur timestamp de la ligne est fixée à la valeur @@DBTS actuelle, puis @@DBTS est incrémenté de 1.

  N'utilisez jamais de colonnes timestamp dans les clés, surtout les clés primaires, car la valeur timestamp change à chaque fois que la ligne est modifiée.

  Pour enregistrer les dates et heures des modifications de données effectuées dans une table, utilisez le type de données datetime ou smalldatetime afin d'enregistrer les événements et déclencheurs destinés à mettre à jour automatiquement les valeurs en cas de modification.

• Types de données d'alias
  Les types de données d'alias vous permettent d'étendre un type de données de base SQL Server (tel que varcha) avec un nom descriptif et un format adapté à une utilisation spécifique. L'instruction suivante applique par exemple le type de données défini par l'utilisateur birthday qui autorise les valeurs NULL en utilisant le type de base de données datetime :

  EXEC sp_addtype birthday, datetime, 'NULL'
  

  Choisissez avec précaution les types de base pour l'implémentation des types de données définis par l'utilisateur. Par exemple, aux États-Unis, les numéros de sécurité sociale (SSN) se présentent sous la forme nnn-nn-nnnn. Bien qu'ils contiennent des nombres, ces nombres constituent un identificateur et ne sont pas soumis à des opérations mathématiques. Il est donc courant de créer un type de données Numéros de sécurité sociale défini par l'utilisateur au format varchar et de créer une contrainte CHECK pour appliquer le format des numéros de sécurité sociale stockés dans la table :

  EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL'
  GO
  CREATE TABLE ShowSSNUsage (EmployeeID int PRIMARY KEY, EmployeeSSN SSN, CONSTRAINT CheckSSN CHECK ( EmployeeSSN LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )
  )
  GO
  

  Si les colonnes SSN sont généralement utilisées en tant que colonnes clés dans les index, en particulier dans les index cluster, la taille des clés peut être réduite de 11 à 4 octets si le type de données SSN défini par l'utilisateur est appliqué en utilisant le type de données de base int. Cette réduction de la taille des clés améliore la récupération des données. Le fait que la récupération des données soit plus efficace et qu'il ne soit plus nécessaire d'utiliser la contrainte CHECK va généralement suffire à compenser le traitement supplémentaire requis pour convertir int dans un format caractère lors de l'affichage ou de la modification des valeurs SSN.

Voir aussi

Autres ressources

CREATE FUNCTION (Transact-SQL)
Types de données (Transact-SQL)
DECLARE @local\_variable (Transact-SQL)
SQL_VARIANT_PROPERTY (Transact-SQL)
table (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005