Uniqueidentifier vs IDENTITÉ vs Code Matériau-qui est le meilleur choix pour la clé primaire?

Qui est le meilleur choix pour la clé primaire dans SQL Server?

Il y a un exemple de code:

par exemple

CREATE TABLE new_employees
   (employeeId   UNIQUEIDENTIFIER      DEFAULT NEWID(),
   fname      VARCHAR(20) )
GO
INSERT INTO new_employees(fname) VALUES ('Karin')
GO

Les colonnes d'identité

par exemple

 CREATE TABLE new_employees
 (
  employeeId int IDENTITY(1,1),
  fname varchar (20)
 );

 INSERT new_employees
    (fname)
 VALUES
    ('Karin');

[Code](ou Code de commerce,et qui de l'identité d'un matériau. par exemple, numéro de client)

par exemple

CREATE TABLE new_employees(
    [ClientId] [varchar](20) NOT NULL,
    [fName] [varchar](20) NULL      
 )

 INSERT new_employees
    (ClientID, fname)
 VALUES
    ('C0101000001',--customer identifier，e.g.'C0101000001' a user-defined code.
     'Karin');

Merci de me donner quelques conseils pour le choix de la clé primaire de la trois types de colonnes d'identité,ou d'autres choix.

Merci!

OriginalL'auteur huoxudong125 | 2013-12-09

21

GUID peut sembler être un choix naturel pour votre clé primaire - et si vraiment vous devez, vous pourriez probablement défendre l'idée de l'utiliser pour la CLÉ PRIMAIRE de la table. Ce que je recommanderais fortement de ne pas faire c'est d'utiliser le GUID colonne comme clé de cluster, SQL Server par défaut, à moins que vous dire précisément ce qu'il ne pas.

Vous avez vraiment besoin de garder deux questions en dehors:
1. la clé primaire est une logique de la construction de l'un des candidats les touches de manière unique et fiable identifie chaque ligne dans la table. Cela peut être n'importe quoi, vraiment - un INT, un GUID, une chaîne de ramasser ce qui fait le plus de sens pour votre scénario.
2. la clé de cluster (la ou les colonnes qui définissent la "index cluster" sur la table) - c'est un physique de stockage liées à la chose, et ici, un petit, stable, de plus en plus de type de données est votre meilleur choix - INT ou BIGINT que votre option par défaut.
Par défaut, la clé primaire dans une table SQL Server est également utilisé en tant que clé de cluster - mais qui n'a pas besoin d'être de cette façon! J'ai personnellement vu de grands gains de performance lors de la rupture de la précédente GUID primaire /clé cluster en deux touches - le primaire (logique) touche sur le GUID, et le clustering (commande) la clé sur un autre INT IDENTITY(1,1) colonne.

Comme Kimberly Tripp - le Reine de l'Indexation - et d'autres ont déclaré un grand nombre de fois un GUID comme la clé de cluster n'est pas optimale, car en raison de son caractère aléatoire, il va conduire à des page et la fragmentation de l'index et, généralement, de la mauvaise performance.

Oui, je sais - il y a newsequentialid() dans SQL Server 2005 et jusqu' - mais même cela n'est pas réellement et pleinement séquentielle et donc aussi souffre des mêmes problèmes que les GUID - juste un peu moins bien en évidence.

Puis, il y a un autre problème à prendre en compte: la clé de cluster sur une table sera ajouté à chaque entrée sur chaque et tous les index non cluster sur votre table ainsi - ainsi, vous voulez vraiment vous assurer qu'il est aussi petit que possible. En règle générale, un INT avec+ de 2 milliards de lignes devrait être suffisant pour la grande majorité des tables - et par rapport à un GUID comme la clé de cluster, vous pouvez vous épargner des centaines de méga-octets de stockage sur disque et en mémoire du serveur.

Rapide calcul à l'aide de INT vs GUID primaire et de clé de clustering:
- Table de Base avec 1'000'000 lignes (3,8 MO vs 15.26 MO)
- 6 index non-cluster (22.89 MO vs 91.55 MO)
TOTAL: 25 MO vs 106 MO - et ce n'est que sur une seule table!

Peu plus de nourriture pour la pensée - d'excellentes choses par Kimberly Tripp - le lire, le relire, le digérer! C'est le Serveur SQL de l'indexation de l'évangile, vraiment.
Sauf si vous avez un très bonne raison, je dirais d'utiliser un INT IDENTITY pour presque tous les "vrais" tableau de données par défaut pour leur clé primaire - c'est unique, c'est stable, (ne change jamais), c'est étroit, c'est croissant tous les de bonnes propriétés que vous voulez avoir dans une clé de cluster pour une performance rapide et fiable de vos tables SQL Server!

Si vous avez quelques "naturel" valeur de la clé qui a également toutes ces propriétés, alors vous pourriez aussi utiliser à la place d'une clé de substitution. Mais deux des chaînes de longueur variable de max. De 20 caractères chacune ne répondent pas à ces exigences, à mon avis.

OriginalL'auteur marc_s
5

IDENTITÉ

PROS
1. de stockage de petite empreinte;
2. optimale joindre /indice de performance (par exemple, pour l'intervalle de temps requêtes, la plupart des lignes récemment inséré sera sur un nombre limité de pages);
3. très utile pour l'entreposage de données;
4. type de données natif de l'OS, et facile à travailler dans toutes les langues;
5. facile à déboguer;
6. généré automatiquement (récupéré via SCOPE_IDENTITY() plutôt que assignée);
7. pas être mis à jour (bien que certains considèrent cela comme un inconvénient, curieusement).
CONTRE
1. ne peut pas être fiable "prédit" par les applications qui ne peuvent être récupérées après l'INSERTION;
2. besoin d'un complexe système dans les environnements multi-serveur, puisque l'IDENTITÉ n'est pas autorisé dans certaines formes de la réplication;
3. peut être dupliqué, si ce n'est explicitement définie à CLÉ PRIMAIRE.
4. si une partie de l'index cluster sur la table, cela peut créer une insertion hot-spot;
5. propriétaire et non directement portable;
6. seulement unique au sein d'une seule table;
7. des écarts peuvent survenir (par exemple, avec un roulé de transaction), ce qui peut causer un peu de poulet de style alarmes.
GUID

PROS
1. depuis qu'ils sont {plus ou moins} garantie unique, plusieurs tables/bases de données/instances/serveurs/réseaux/centres de données peut générer de manière indépendante, puis a fusionné sans heurts;
2. requis pour certaines formes de la réplication;
3. peuvent être générés à l'extérieur de la base de données (par exemple, par une application);
4. distribué des valeurs de prévenir les hot-spot (tant que vous n'avez pas de cluster de cette colonne, qui peut conduire à des taux anormalement élevé de fragmentation).
CONTRE
1. la plus large de type de données conduit à une baisse du rendement de l'indice (si en cluster, chaque insert presque garanti pour "sale", une autre page), et une augmentation des exigences en matière d'entreposage;
2. lourd à déboguer (où userid = {BAE7DF4-DDF-3RG-5TY3E3RF456AS10});
3. mis à jour (il faut propager les modifications, ou d'empêcher l'activité au total);
4. sensible au temps des restaurations dans certains environnements (par exemple, l'heure d'été restaurations);
5. GROUPE, et d'autres opérations sur les ensembles souvent besoin de CAST/CONVERTIR;
6. pas tous les langages et environnements de soutien Guid;
7. il n'y a pas de déclaration comme SCOPE_GUID() pour déterminer la valeur qui a été générée, par exemple en fonction NEWID();
OriginalL'auteur Muhammad Nasir
2

Une chose que vous aurez besoin à considérer dans la conception de vos tables est si vous aurez besoin de répliquer, tesson, ou autrement transférer vos données d'un endroit à un autre. Peut-être que les données sont générées par d'autres applications et être en phase avec la vôtre. Un exemple de cela serait une application mobile qui crée de données, puis synchronise avec un serveur. Si quoi que ce soit ou qui est peut-être vrai, alors UNIQUEIDENTIFIER serait le bon choix pour utiliser pour vos de clé primaire.

La UNIQUEIDENTIFIER type de données est terrible pour les performances lorsqu'il est utilisé comme un index cluster. Oui, vous pouvez utiliser newsequentialid(), mais cela ne vous aidera pas si les valeurs sont générées sur d'autres appareils. Le consensus semble être que les index en cluster sont mieux utilisés avec un séquentielle et étroit le type de données comme un INT ou BIGINT.

Si vous n'êtes pas concerné par des problèmes de stockage, alors vous pouvez essayer en utilisant une combinaison d'une IDENTITY clé de cluster et UNIQUEIDENTIFIER clé primaire. Créer un clé de cluster IDENTITY colonne et l'utiliser pour votre index cluster (mais pas en tant que clé primaire). Inserts encore être faits de façon séquentielle et il satisfait le désir d'être un étroit type de données. Maintenant, vous pouvez utiliser un UNIQUEIDENTIFIER que votre clé primaire. Cela vous permettra de vous déplacer, se reproduire, et/ou de l'éclat à vos données lorsque vous en avez besoin.

La clé de cluster n'a pas d'autres fins que de garder vos inserts séquentielle et à ce que tous les autres index non cluster point à la recherche de données pour une requête donnée. Le clé de cluster est complètement jeter et peut être régénéré lorsque les données sont déplacées, les reproduire et/ou fragmenté depuis l'unicité est assurée par le UNIQUEIDENTIFIER clé primaire.

Voici un excellent article qui montre ce qui se passe en interne lors de l'utilisation d'une IDENTITÉ par rapport à un type UNIQUEIDENTIFIER pour votre index cluster.
- Efficace Index En Cluster
OriginalL'auteur rmblstrp
1

Les guid sont grandes mais ont l'avantage d'être unique partout: ce tableau ou que, de ce serveur, ou que, si vous avez le GUID puis tout le reste est connaissable. Si c'est utile pour vous, très bien, mais vous devrez payer pour les frais généraux, et de continuer à payer et payer et payer....

Codes matériau vraiment travailler pour de petites immuable clés, comme les couleurs ou les codes de classification et la comme. R sera toujours rouge, G est vert, c'est un octet, etc.

Colonnes d'identité dans leur propre quand il ne peut pas être un matériau de code, ou le naturel est composé de plusieurs codes matières ensemble, ou la clé naturelle est déjà composé d'autres colonnes d'identité et/ou Guid, ou la clé naturelle est mutable. Oui, vous pouvez utiliser un GUID, mais une colonne de type integer est beaucoup plus efficace à tous les égards.

Une autre option disponible dans SQL 2012 sont des séquences, comme une sorte de base de données au niveau de la colonne d'identité. C'est une belle maison à mi-chemin entre les Guid et les colonnes d'identité, dans le sens qu'une séquence peut être utilisée dans de nombreux tableaux, de sorte qu'à partir d'une valeur donnée, et pas seulement la ligne est connaissable, mais la table aussi--mais vous pouvez toujours utiliser un INT ou BIGINT (ou SMALLINT!) si vous pensez que ce sera suffisant pour vos données. C'est chouette pour certaines fins, un peu comme un id de l'objet dans le monde orienté objet.

Être conscient que beaucoup ou la lumière-poids de l'Orm s'attendre à des tables d'avoir une seule colonne de clé primaire, de préférence, une colonne de type entier, et peut ne pas bien jouer avec quelque chose, mais un INT IDENTITÉ PK.

OriginalL'auteur Peter Radocchia

Vous devez vous connecter pour publier un commentaire.