APSQL

SQL Server 2008: Nouveaux types de données

SQL Server 2008 propose de nombreuses améliorations et nouveautés tant au niveau de l'administration que de la programmation. Cet article, permet de mettre en avant quelques uns de ces apports au niveau du Transact SQL et plus exactement les nouveautés apportées au niveau du stockage des informations. En effet les données manipulées évoluent sans cesse et il est maintenant tout à fait logique de travailler avec des documents word, excel, ..., des documents scannés, des coordonnées géographiques. Ces informations ont bien du mal à trouver leur place dans le modèle relationnel classique. SQL Server 2008 propose donc une gestion optimisé de ces éléments avec les types: goemetry, geography et filestream. Mais ce n'est pas la seule évolution proposée par SQL Server 2008 au niveau des données. En effet SQL Server 2008 propose une gestion plus fine des données de type date et heure en proposant différents types de données. SQL Server 2008 offre également la possibilité de gérer correctement les données hiérarchiques par l'intermédiaire du type hierarchyid et les différentes méthodes associées.

Mais avant de commencer à présenter ces différents points, il est important, de souligner, que lorsque vous écrivez du code Transact SQL depuis SQL Server Management  Studio (SSMS) le complément automatique du code est maintenant actif. L'avantage est double car il évite les erreurs de saisies et permet également d'écrire plus rapidement le code. Il n'y a maintenant plus de doute possible quand à l'orthographe exact d'une variable ou bien le nom d'une méthode.

Les dates et les heures

La gestion des dates et des heures est un problème connu de la part de tous. Ce problème devient encore un peu plus complexe lorsqu'il s'agit de faire correspondre des dates pour des utilisateurs qui travaillent avec des créneaux horaire différents, ce qui est le cas pour nos voisin d'outre-manche.

D'un autre côté il est parfois simplement nécessaire de stocker une information de type heure/minutes/ secondes alors que parfois il s'agit de stocker une information avec une précision limitées au jour.

Pour palier à toutes ces problématiques SQL Server 2008 introduit une nouvelle génération de types date et heures. Ce nouvel ensemble de type vient en complément  du type datetime existant. Les types introduits par SQL Server 2008 sont:

  • datetime2 qui offre plus de précision que le simple type datetime.
  • date qui permet  de stocker uniquement une date (jour, mois et année)  et donc aucune information de type heures, minutes ou secondes.
  • time qui permet de stocker des informations de type heure, minutes, secondes et fraction de secondes avec une précision de 100 nanosecondes.
  • datetimeoffset qui permet de stocker une information date et heure ainsi que le décalage par rapport à l'heure GMT.

Les types date et time permettent d'optimiser le stockage mais également d'avoir des informations plus cohérentes et plus facile à manipuler. Par exemple, il est plus facile de comparer 2 dates au format date que si elles sont définies au format datetime en effet le format datetime contient en plus les heures, minutes et secondes et donc la comparaison au niveau des jours nécessite un formatage préalable des données.

Le script suivant permet d'illustrer les différents types. Comme cet exemple est fait sur une version CTP de SQL Server il est normal que le résultat soit au format US.

declare @jour date= getdate();
declare @heure time=getdate();
declare @date datetime2=getdate();
select jour=@jour,heure=@heure,date=@date;
jour            heure                date
----------      ----------------          ---------------------------
2008-01-04      15:00:54.5500000     2008-01-04 15:00:54.5500000

Les données hiérarchiques

Les données organisées de façon hiérarchique sont nombreuses comme par exemple un organigramme d'entreprise ou une règle d'assemblage de pièces. La gestion de telles arborescences avec de simple type entier est plus complexes qu'il n'y parait et particulièrement l'extraction. SQL Server 2008 propose avec le type hierarchyid et toutes les méthodes associées pour manipulées facilement les données organisées de cette façon.

Ce nouveau type de données fait plus qu'apporter le support nécessaire pour extraire les données suivant un ordre hiérarchique. En effet avec les données de ce type, il est possible de définir des index depthfirst qui permet d'aboutir le plus rapidement possible au niveau feuille de la hiérarchie et des index breathfirst qui permettent de faire un parcours complet d'un même niveau de la hiéirachie avant d'accéder aux données d'un niveau inférieure. 

Associé également au type hierarchyID des méthodes permettes de connaitre le nœud parent (GetAncestor) d'un élément, les nœuds enfants (GetDescendant), d'accéder directement au nœud racine (GetRoot) et bien d'autre encore pour manipuler efficacement les données hiérarchiques.

Dans l'exemple présenté ci-dessous, une table est définie avec une colonne définie sur le type hierarchyid. Pour l'insertion de la racine de la hiérarchie la méthode GetRoot est utilisée. Par la suite les nœuds enfants sont positionnés par rapport à la racine et leur niveau est obtenu par l'intermédiaire de GetDescendant.

create table salaries(
  id int identity (1,1),
  position hierarchyId,
  nom nvarchar(80),
  poste nvarchar(80));
go
insert into salaries (position, nom, poste)
  values (hierarchyId::GetRoot(), 'DUPOND','chef');
declare @patron hierarchyid;
select @patron=hierarchyid::GetRoot() from salaries;
declare @drh hierarchyid;
set @drh=@patron.GetDescendant(null, null);
insert into salaries (position, nom, poste) 
  values  (@drh,'BERLODIOT','DRH');
insert into salaries (position, nom, poste)  values 
  (@patron.GetDescendant(@drh, null),'MICHALON','Comptable');
insert into salaries (position, nom, poste) 
  values  (@drh.GetDescendant(null, null),'BERNAUD','Assistante');
select position.ToString(),* from dbo.salaries;

Il est alors possible de manipuler les données en utilisant les méthodes IsDescendant et GetAncestor. Par exemple, la requête présentée ci-dessous permet d'identifier les descendants directs du chef.

declare @chef hierarchyid;
select @chef=position from salaries where poste='chef';
select *
  from salaries
  where position.GetAncestor(1)=@chef;

Le type FILESTREAM

Les données simples (ou structurées) telles qu'elles apparaissent dans de nombreuses applications de gestion sont de plus en plus associées à des données non structurées comme des documents qui possèdent un format complexe. En effet les images, par exemple, sont de plus en plus présentes et permette une meilleure compréhension des données. Les sons et les vidéos sont également de plus en plus présents. En plus de ces  données multimédia, il existe des documents à associer aux données relationnelles. La gestion de tous ces éléments directement dans la base de données peut poser des problèmes de manipulation des données, mais surtout alourdi considérablement  les tâches administratives de gestion de l'espace disque et complique les opérations de sauvegarde restauration de la base de données. C'est pour cela que les informations de ce type sont souvent stockées directement sur le système de fichiers. Cette solution offre beaucoup de souplesse quand à la gestion des données non structurées mais par contre les documents ne sont plus liées aux données structurées ce qui pose de nombreux soucis de synchronisation des données. Pour essayer de tirer le meilleur des deux solutions, SQL Server 2008 propose l'attribut FILESTREAM sur les données de types blob(varbinary). Avec cet attribut, le server de base de données autorise le stockage des données de type blob sur le système de fichier. Ainsi le volume global de la base de données reste raisonnable mais les données structurées sont bien associées aux données non structurées au niveau même de la base.

L'utilisation de FILESTREAM peut ouvrir une brèche dans la sécurité du serveur, aussi cette option n'est pas disponible par défaut, et il est nécessaire de l'activer par l'intermédiaire de la procédure sp_filestream_configure.

exec sp_filestream_configure 
@enable_level=3,@share_name="PartageFileStream"

La commande net share permet de s'assurer que le partage est bien actif.

dossier objet Filestream

Pour permettre à une base d'utiliser ce partage FILESTREAM, il est nécessaire de définir un "groupe de fichiers" spécifiques. Ce groupe de fichier peut être défini lors de la création de la base ou bien en ajout sur une base active. Le groupe FileStream doit référencer un dossier qui existe. L'exemple suivant illustre comment modifier une base existante.

alter database vague2008
  add filegroup GroupeFileStream contains filestream;
go
alter database vague2008 
  add file(
    name=FileStreamDB,
    filename=N'c:\demoFS\test'
  ) to filegroup GroupeFileStream;

Maintenant que la mise en place est faite il est possible de definir une table avec une colonne de type filestream. Cette opération est illustrée par le script suivant. De même quelques valeurs sont insérés dans la table. Cette insertion permet au passage d'illustrer la nouvelle syntaxe de la commande insert qui permet d'ajouter plusieurs lignes à l'aide d'une seule instruction.

create table contact(
  id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
  nom nvarchar(80),
  photo varbinary(max) filestream);
go
insert into contact(id,nom, photo) 
  values (newid(),'Durand', cast('Photo Durand' as varbinary(max))),
         (newid(),'Dupond', cast('Photo Dupond' as varbinary(max)));

Il est alors intéressant de visualiser le contenu du dossier référencer par le groupe de fichier destiné à recevoir les données de type FILESTREAM.

dossier objet Filestream

Les données spatiales

L'apparition et l'intégration dans le monde professionnel de la cartographie en ligne et des systèmes GPS permet de faire gagner un temps précieux. Cependant l'utilisation de ces techniques nécessite de posséder les coordonnées géographiques exactes. Par exemple un commercial souhaite trouver en même temps que les coordonnées postales, téléphoniques et internet d'un client ses coordonnées géographique afin de pouvoir les envoyer directement vers son GPS ou bien d'imprimer le plan d'accès. Ce besoin implique que les informations géographiques soient présentes dans la base de données. Pour répondre à ce type de problématique, SQL Server 2008 propose le type geography qui permet de stocker la latitude et la longitude d'un point. Les données de type longitude et latitude ne conviennent que lorsque la rotondité de la terre doit être prise en compte. Lorsqu'il s'agit de repérer un élément sur un plan (de quartier, de maison, de montage, …) il est préférable de travailler avec des données géométrique habituelle (x et y). Pour ce type de données SQL Server 208 dispose du type geometry.

L'utilisation de ces types ne nécessite pas de configuration particulière au niveau du serveur.

Associé à ces types des méthodes permettent de retrouver les informations relatives aux différents points.

Le code suivant illustre la modification de la table contact pour y ajouter les coordonnées géographique du contact.

alter table contact
  add lieu geography null;

Puis les coordonnées géographiques sont ajoutées à l'aide d'une requête update classique:

update contact
set lieu=geography::STGeomFromText(
'POINT( 48.85833405966226 2.294383049011243)',4326)
where nom='Durand';

Ces types de données permettent de gérer les informations de façon plus pertinente et de traiter les données plus efficacement. SQL Server 208 apporte de nombreuses nouveautés et permet à SQL Server de réaliser une avancée conséquente.