Dois-je concevoir une table avec une key primaire de varchar ou int?

Je sais que c'est subjectif, mais j'aimerais connaître les opinions des gens et, je l'espère, certaines des meilleures pratiques que je peux appliquer lors de la design des structures de table SQL Server.

Personnellement, je pense que la saisie d'une table sur une longueur fixe (max) varchar est un non-non, car cela signifie que la même longueur fixe doit être appliquée à toutes les autres tables qui l'utilisent comme key étrangère. L'utilisation d'un int , éviterait d'avoir à appliquer la même longueur à travers le tableau, ce qui est lié à conduire à l'erreur humaine, à savoir 1 table a varchar (10) , et l'autre varchar (20) .

Cela ressemble à un cauchemar à l'installation initiale, et signifie que le maintien futur des tables est trop lourd aussi. Par exemple, disons que la colonne varchar key devient soudainement 12 caractères au lieu de 10. Vous devez maintenant aller mettre à jour toutes les autres tables, ce qui pourrait être une tâche énorme d'ici quelques années.

Ai-je tort? Ai-je manqué quelque chose ici? Je voudrais savoir ce que les autres pensent de cela et si coller avec int pour les keys primaires est le meilleur moyen d'éviter les cauchemars de maintenance.

    Je recommand fortement d'utiliser un champ INT NOT NULL IDENTITY(1,1) dans chaque table comme key primaire.

    Avec un champ IDENTITY, vous pouvez laisser la database gérer tous les détails pour vous assurer que c'est vraiment unique et que le type de données INT est de 4 octets, et corrigé, c'est donc plus facile à utiliser pour le primaire (et le clustering ) key dans votre table.

    Et vous avez raison – INT est un INT est un INT – il ne changera rien à sa taille, donc vous n'aurez plus jamais à recréer et / ou mettre à jour vos relations foreign keys.

    L'utilisation d'un VARCHAR (10) ou (20) utilise juste trop d'espace – 10 ou 20 octets au lieu de 4, et ce que beaucoup de gens ne savent pas – la valeur de la key de cluster sera répétée sur chaque input d'index sur chaque Un seul index non clusterisé sur la table, donc potentiellement, vous gaspillez beaucoup d'espace (pas seulement sur le disque – ce n'est pas cher – mais aussi dans la memory principale de SQL Server). En outre, étant donné qu'il est variable (peut-être 4, peut-être 20 caractères), il est plus difficile pour le server SQL de maintenir correctement une bonne structure d'index.

    Marc

    Lorsque vous choisissez habituellement la key primaire, vous choisissez également la key groupée. Les deux sont souvent confus, mais vous devez comprendre la différence.

    Les keys primaires sont des éléments métier logiques. La key primaire est utilisée par votre application pour identifier une entité, et la discussion sur les keys primaires consiste principalement à utiliser des keys naturelles ou des keys de substitution . Les liens sont beaucoup plus détaillés, mais l'idée de base est que les keys naturelles sont dérivées d'une propriété d'entité existante comme ssn ou phone number , tandis que les keys de substitution n'ont aucun sens pour l'entité commerciale, comme id ou rowid généralement de type IDENTITY ou une sorte d'uuid. Mon opinion personnelle est que les keys de substitution sont supérieures aux keys naturelles, et le choix devrait toujours être des valeurs d'identité pour des applications locales seulement, des guids pour n'importe quelle sorte de données dissortingbuées. Une key primaire ne change jamais pendant la durée de vie de l'entité.

    Les keys en cluster sont la key qui définit le stockage physique des lignes dans la table. La plupart du time, ils chevauchent la key primaire (l'identificateur d'entité logique), mais cela n'est pas réellement appliqué ni requirejs. Lorsque les deux sont différents, cela signifie qu'il existe un index unique non-cluster sur la table qui implémente la key primaire. Les valeurs de key groupées peuvent changer pendant la durée de vie de la ligne, ce qui entraîne le déplacement physique de la ligne dans la table vers un nouvel location. Si vous devez séparer la key primaire de la key en cluster (et parfois vous le faites), choisir une bonne key en cluster est nettement plus difficile que de choisir une key primaire. Deux facteurs principaux déterminent la design de votre key en cluster:

    1. Le model d'access aux données répandu.
    2. Les considérations de stockage .

    Modèle d'access aux données . Par là, je comprends la façon dont la table est interrogée et mise à jour. N'oubliez pas que les keys en cluster déterminent l'ordre réel des lignes de la table. Pour certains templates d'access, certaines mises en page font toute la différence dans le monde en ce qui concerne la vitesse de requête ou pour mettre à jour la concurence:

    • données actuelles vs archives. Dans de nombreuses applications, datatables appartenant au mois en cours sont fréquemment consultées, tandis que celles du passé sont rarement accessibles. Dans de tels cas, la design de table utilise le partitionnement de table par date de transaction, souvent à l'aide d'un algorithm de window glissante . La partition du mois en cours est conservée sur un groupe de files situé sur un disque rapide, les anciennes données archivées sont déplacées vers des groupes de files hébergés sur un stockage less cher mais plus lent. Évidemment, dans ce cas, la key groupée (date) n'est pas la key primaire (identificateur de transaction). La séparation des deux est dictée par les exigences d'échelle, car l'optimiseur de requêtes sera capable de détecter que les requêtes ne s'intéressent qu'à la partition actuelle et ne regardent même pas les historiques.

    • Traitement de style de queue FIFO. Dans ce cas, la table a deux points chauds: la queue où les insertions se produisent (enqueue), et la tête où les suppressions se produisent (dequeue). La key groupée doit en tenir count et organiser la table de manière à séparer physiquement l'location de la queue et de la tête sur le disque, afin de permettre la concurence entre la mise en queue et la suppression de la file, par exemple. en utilisant une key de command de mise en queue. Dans les files d'attente pures, cette key groupée est la seule key, car il n'y a pas de key primaire sur la table (elle contient des messages , pas des entités ). Mais la plupart du time, la queue n'est pas pure, elle sert également de stockage pour les entités, et la ligne entre la queue et la table est estompée. Dans ce cas, il existe également une key primaire, qui ne peut pas être la key groupée: les entités peuvent être mises en queue, modifiant ainsi la valeur de la key groupée de l'ordre de mise en queue, mais ne peuvent pas modifier la valeur de la key primaire. L'échec de voir la séparation est la principale raison pour laquelle les files d'attente sauvegardées par une table user sont si difficiles à corriger et truffées d'interblocages: parce que la queue et la queue sont entrelacées, la table est localisée à la queue et au début de la file.

    • Traitement corrélé. Lorsque l'application est bien conçue, elle partitionnera le traitement des éléments corrélés entre ses threads de travail. Par exemple, un processeur est conçu pour avoir 8 threads de travail (disons pour correspondre aux 8 CPU sur le server) afin que les processeurs partagent datatables entre eux, par exemple. le travailleur 1 ne prend que les counts nommés A à E, travailleur 2 F à J etc. Dans de tels cas, la table doit être regroupée par le nom du count (ou par une key composée qui a la première position du nom du count) afin que les travailleurs localisent leurs requêtes et mises à jour dans la table. Un tel tableau aurait 8 points chauds distincts, autour de la zone où chaque travailleur se concentre pour le moment, mais l'important est qu'ils ne se chevauchent pas (pas de blocage). Ce type de design est répandu sur les designs OLTP à haut débit et dans les charges de test TPCC, où ce type de partition se reflète également dans l'location memory des pages chargées dans le pool de memory tampon (localité NUMA), mais je m'écarte.

    Considérations de stockage . La largeur de la key clusterisée a d'énormes répercussions sur le stockage de la table. Pour un, la key occupe de l'espace dans chaque page non-feuille de l'tree b-tree, donc une grande key occupera plus d'espace. Deuxièmement, et souvent plus important, est que la key en cluster est utilisée comme key de search par toutes les keys non clustées, de sorte que chaque key non groupée devra stocker la largeur complète de la key en cluster pour chaque ligne. C'est ce qui rend les grosses keys en cluster comme varchar (256) et guids mauvais choix pour les keys d'index en cluster.
    En outre, le choix de la key a un impact sur la fragmentation de l'index clusterisé, affectant parfois considérablement les performances.

    Ces deux forces peuvent parfois être antagonistes, le schéma d'access aux données nécessitant une certaine key groupée importante qui va entraîner des problèmes de stockage. Dans de tels cas, bien sûr, un équilibre est nécessaire, mais il n'y a pas de formule magique. Vous mesurez et vous testez pour arriver au bon endroit.

    Alors, que faisons-nous de tout cela? Commencez toujours par considérer la key groupée qui est également la key primaire de la forme entity_id IDENTITY(1,1) NOT NULL . Séparez les deux et organisez la table en conséquence (par exemple, partition par date) quand c'est approprié.

    Je suis d'accord qu'en général un type de champ INT (ou identité) est le meilleur choix dans la plupart des designs de database "normales":

    • il ne nécessite aucun "algorithm" pour générer l'identifiant / key / valeur
    • vous avez des jointures rapides (er) et l'optimiseur peut travailler beaucoup plus dur sur des gammes et tels sous le capot
    • vous suivez une norme de facto

    Cela dit, vous devez également connaître vos données. Si vous allez souffler à travers un int 32 bits signé, vous devez penser à non signé. Si vous allez souffler à travers cela, peut-être 64 bits ints sont ce que vous voulez. Ou peut-être avez-vous besoin d'un UUID / hash pour faciliter la synchronisation entre les instances / fragments de la database.

    Malheureusement, cela dépend et YMMV mais j'utiliserais certainement un int / identité sauf si vous avez une bonne raison de ne pas le faire.

    Comme vous l'avez dit, la cohérence est la key. J'utilise personnellement des ints non signés. Vous n'allez pas en manquer à less de travailler avec des quantités de données ridicules, et vous pouvez toujours savoir que n'importe quelle colonne key doit être de ce type et vous ne devez jamais chercher la bonne valeur pour des colonnes individuelles.

    En se basant sur cet exercice à d'innombrables resockets et en soutenant le système avec les résultats, il y a quelques réserves à l'énoncé général selon lequel INT est toujours meilleur. En général, à less d'une raison, je serais d'accord avec cela. Cependant, dans les tranchées, voici quelques avantages et inconvénients.

    INT

    • Utilisez à less d'une bonne raison de ne pas le faire.

    GUID

    • Unicité – Un exemple est le cas où il y a une communication à sens unique entre les parties distantes du programme et le côté qui doit initier n'est pas le côté de la database. Dans ce cas, définir un Guid sur le côté distant est sûr lorsque la sélection d'un INT ne l'est pas.
    • Unicité à nouveau – Un scénario plus tiré par les cheveux est un système dans lequel plusieurs clients coexistent dans des bases de données distinctes et il y a migration entre des clients comme des users similaires utilisant une suite de programmes. Si cet user s'inscrit pour un autre programme, son logging user peut être utilisé sans conflit. Un autre scénario est celui où les clients acquièrent des entités les uns des autres. Si les deux sont sur le même système, ils s'attendent souvent à ce que la migration soit plus facile. Essentiellement, toute migration fréquente entre les clients.
    • Difficile à utiliser – Même un programmeur expérimenté ne peut pas se souvenir d'un guid. Lors du dépannage, il est souvent frustrant de devoir copyr et coller des identifiants pour les requêtes, en particulier si la prise en charge est effectuée avec un outil d'access à distance. Il est beaucoup plus facile de se référer constamment à SELECT * FROM Xxx WHERE ID = 7 à SELECT * FROM Xxx OERE ID = 'DF63F4BD-7DC1-4DEB-959B-4D19012A6306'

    • Indexation – L'utilisation d'un index clusterisé pour un champ guid nécessite un réarrangement constant des pages de données et n'est pas aussi efficace pour indexer que des INT ou même des strings courtes. Il peut tuer la performance – ne le faites pas.

    CARBONISER

    • Lisibilité – Bien que la sagesse conventionnelle soit que personne ne devrait figurer dans la database, la réalité des systèmes est que les gens auront access, espérons-le, au personnel de votre organisation. Quand ces gens ne sont pas avertis avec la syntaxe de jointure, une table normalisée avec ints ou guids n'est pas claire sans beaucoup d'autres requêtes. La même table normalisée avec QUELQUES keys de string peut être beaucoup plus utilisable pour le dépannage. J'ai tendance à l'utiliser pour le type de table où je fournis les loggings au moment de l'installation afin qu'ils ne varient pas. Des choses comme StatusID sur une table majeure est beaucoup plus utilisable pour le support lorsque la key est 'Fermé' ou 'En attente' qu'un chiffre. L'utilisation de keys traditionnelles dans ces domaines peut transformer un problème facilement résolu en un problème nécessitant l'aide d'un développeur. Les goulets d'étranglement de ce genre sont mauvais, même s'ils sont causés par l'access à la database d'un personnel douteux.
    • Contraindre – Même si vous utilisez des strings, gardez-les à longueur fixe, ce qui accélère l'indexing et ajoute une contrainte ou une key étrangère pour empêcher la corruption. Parfois, l'utilisation de cette string peut vous permettre de supprimer la table de search et de maintenir la sélection sous forme d'Enum simple dans le code – il est toujours important de contraindre datatables entrant dans ce champ.

    Pour de meilleures performances , 99,999% du time, la key primaire doit être un seul champ entier.

    Sauf si vous avez besoin que la key primaire soit unique sur plusieurs tables d'une database ou sur plusieurs bases de données. Je suppose que vous posez des questions sur MS SQL-Server parce que c'est ainsi que votre question a été taguée. Dans ce cas, pensez à utiliser le champ GUID à la place. Bien que mieux qu'un varchar, la performance du champ GUID n'est pas aussi bonne qu'un integer.

    Utilisez INT. Vos points sont tous valables; Je donnerais la priorité en tant que:

    1. Facilité d'utilisation de la capacité d'incrémentation automatique de SQL – pourquoi réinventer la roue?
    2. Managabilité – vous ne voulez pas avoir à changer le champ key.
    3. Performance
    4. Espace disque

    1 & 2 nécessitent le time / énergie / effort du développeur. 3 & 4 vous pouvez lancer du matériel à.

    Si Joe Celko était là, il aurait des mots durs … 😉

    Je tiens à souligner que les INT en tant que règle absolue ne sont pas toujours appropriées. Supposons que vous ayez une table de véhicule avec tous les types de camions, etc. Disons maintenant que vous avez une table VehicleType. Si vous vouliez get tous les camions, vous pourriez le faire (avec une semence d'identité INT):

     SELECT V.Make, V.Model FROM Vehicle as V INNER JOIN VehicleType as VT ON V.VehicleTypeID = VT.VehicleTypeID WHERE VT.VehicleTypeName = 'Truck' 

    Maintenant, avec un PK Varchar sur VehicleType:

     SELECT Make, Model FROM Vehicle WHERE VehicleTypeName = 'Truck' 

    Le code est un peu plus propre et vous évitez une jointure. Peut-être que la jointure n'est pas la fin du monde, mais si vous n'avez qu'un seul outil dans votre boîte à outils, vous manquez des opportunités pour des gains de performance et des schémas plus propres.

    Juste une pensée. 🙂

    Alors que INT est généralement recommandé, cela dépend vraiment de votre situation.

    Si vous êtes préoccupé par la maintenabilité, alors d'autres types sont tout aussi faisables. Par exemple, vous pourriez utiliser très efficacement un Guid comme key primaire. Il y a des raisons de ne pas le faire, mais la cohérence n'en fait pas partie.

    Mais oui, à less d'avoir une bonne raison de ne pas le faire, un int est le plus simple à utiliser, et le less susceptible de vous causer des problèmes.

    Avec PostgreSQL, j'utilise généralement le type de données "Serial" ou "BigSerial" pour générer des keys primaires. Les valeurs sont auto incrémentées et je trouve toujours des entiers faciles à utiliser. Ils sont essentiellement équivalents à un champ entier MySQL défini sur "auto_increment".

    Il faut réfléchir sérieusement pour savoir si la scope 32 bits est suffisante pour ce que vous faites. Les identifiants d'état de Twitter étaient des INT de 32 bits et ils ont eu des problèmes quand ils étaient épuisés.

    L'utilisation d'un BIGINT ou d'un UUID / GUID dans cette situation est discutable et je ne suis pas un type de database hardcore, mais les UUID peuvent être stockés dans un VARCHAR de longueur fixe sans avoir à changer la taille du champ.

    Nous devons garder à l'esprit que la key primaire d'une table ne doit pas avoir de "logique métier" et qu'elle ne doit être qu'une identité de l'logging auquel elle appartient. Suivant cette règle simple, un int et surtout une identité int est une très bonne solution. En demandant à propos de varchar je suppose que vous voulez dire en utilisant par exemple le "Nom complet" comme une key de la table "personnes". Mais que se passe-t-il si nous voulons changer le nom de "George Something" en "George A. Something"? Et quelle sera la taille du champ? Si nous changeons la taille, nous devons aussi changer la taille de toutes les tables étrangères. Donc, nous devrions éviter la logique sur les keys. Parfois, nous pouvons utiliser l'identifiant social (valeur entière) comme key, mais je l'évite aussi. Maintenant, si un projet a des outlook d'extension, vous devriez aussi utiliser Guids (type SQL uniqueidentifier).

    En gardant à l'esprit que c'est une question assez ancienne, je veux toujours faire valoir la nécessité d'utiliser varchar avec des keys de substitution pour les futurs lecteurs:

    1. Un environnement avec plusieurs machines répliquées
    2. Scénarios où il est nécessaire que l'identifiant d'une ligne à insert soit connu avant qu'il ne soit réellement inséré (c'est-à-dire que le client atsortingbue cet identifiant, pas la database)