Quels sont les cas d’utilisation pour sélectionner CHAR sur VARCHAR en SQL?

Je me rends compte que CHAR est recommandé si toutes mes valeurs ont une largeur fixe. Mais alors quoi? Pourquoi ne pas simplement choisir VARCHAR pour tous les champs de texte juste pour être sûr.

Choisissez généralement CHAR si toutes les lignes auront presque la même longueur . Choisissez VARCHAR lorsque la longueur varie de manière significative. CHAR peut aussi être un peu plus rapide car toutes les lignes ont la même longueur.

Cela varie en fonction de l’implémentation de la firebase database, mais généralement, VARCHAR utilise un ou deux octets de stockage supplémentaires (pour la longueur ou la terminaison) en plus des données réelles. Donc (en supposant que vous utilisez un jeu de caractères à un octet) stockant le mot “FooBar”

  • CHAR (6) = 6 octets (pas de surcharge)
  • VARCHAR (10) = 8 octets (2 octets de surcharge)
  • CHAR (10) = 10 octets (4 octets de surcharge)

En fin de compte, CHAR peut être plus rapide et plus efficace en termes d’espace pour des données de même longueur (avec une différence de longueur de deux caractères).

Remarque : Microsoft SQL a 2 octets de surcharge pour un VARCHAR. Cela peut varier d’un DB à l’autre, mais en général, il faut au moins 1 octet de surcharge pour indiquer la longueur ou l’EOL sur un VARCHAR.

Comme Gaven l’a souligné dans les commentaires, si vous utilisez un jeu de caractères de longueur variable à plusieurs octets comme UTF8, CHAR stocke le nombre maximal d’octets nécessaires pour stocker le nombre de caractères. Donc, si UTF8 a besoin d’au plus 3 octets pour stocker un caractère, alors CHAR (6) sera fixé à 18 octets, même s’il ne stocke que des caractères latin1. Donc, dans ce cas, VARCHAR devient un meilleur choix.

Si vous travaillez avec moi et que vous travaillez avec Oracle, je vous ferais probablement utiliser varchar dans presque toutes les circonstances. L’hypothèse selon laquelle char utilise moins de puissance de traitement que varchar peut être vraie … pour l’instant … mais les moteurs de bases de données s’améliorent avec le temps et ce type de règle générale crée un futur “mythe”.

Autre chose: je n’ai jamais vu de problème de performance car quelqu’un a décidé d’utiliser varchar . Vous ferez beaucoup mieux usage de votre temps pour écrire du bon code (moins d’appels à la firebase database) et du SQL efficace (comment les index fonctionnent-ils, comment l’optimiseur prend-il des décisions plus rapidement qu’en général …).

Pensée finale: J’ai vu toutes sortes de problèmes avec l’utilisation de CHAR , des personnes cherchant «quand elles devraient chercher» ou des personnes recherchant «FOO» lorsqu’elles devraient chercher «FOO (un tas d’espaces ici)». ‘, ou les personnes ne coupant pas les blancs à la fin, ou les bogues avec Powerbuilder ajoutant jusqu’à 2000 blancs à la valeur renvoyée par une procédure Oracle.

Outre les avantages en CHAR performances, CHAR peut être utilisé pour indiquer que toutes les valeurs doivent avoir la même longueur, par exemple une colonne pour les abréviations des états américains.

Char est un peu plus rapide, donc si vous connaissez une colonne que vous SAVEZ avoir une certaine longueur, utilisez char. Par exemple, stocker (M) ale / (F) emale / (U) non connu pour le sexe ou 2 caractères pour un état américain.

Est-ce que NChar ou Char fonctionnent mieux que leurs variantes var?

Bonne question La réponse simple est oui dans certaines situations. Voyons si cela peut être expliqué.

Evidemment, nous soaps tous que si je crée une table avec une colonne de varchar (255) (appelons cette colonne myColumn) et que j’insère un million de lignes, mais ne mets que quelques caractères dans myColumn pour chaque ligne, la table sera beaucoup plus petite. nombre de pages de données nécessaires au moteur de stockage que si j’avais créé myColumn en tant que caractère (255). Chaque fois que je fais une opération (DML) sur cette table et que je demande beaucoup de lignes, ce sera plus rapide lorsque myColumn est varchar car je n’ai pas besoin de déplacer tous ces espaces “supplémentaires” à la fin. Déplacer, comme lorsque SQL Server effectue des sortings internes comme lors d’une opération distincte ou d’union, ou s’il choisit une fusion pendant son plan de requête, etc. Move peut également signifier le temps nécessaire pour transférer les données du serveur vers mon serveur local. PC ou à un autre ordinateur ou partout où il va être consommé.

Mais l’utilisation de varchar entraîne des problèmes. SQL Server doit utiliser un indicateur à deux octets (surdébit) pour connaître, sur chaque ligne, le nombre d’octets de cette ligne dans myColumn. Ce ne sont pas les 2 octets supplémentaires qui posent problème, c’est le fait de devoir “décoder” la longueur des données dans myColumn sur chaque ligne.

Dans mon expérience, il est plus logique d’utiliser char au lieu de varchar sur les colonnes qui seront jointes aux requêtes. Par exemple, la clé primaire d’une table ou une autre colonne qui sera indexée. CustomerNumber sur une table démographique ou CodeID sur une table de décodage ou peut-être OrderNumber sur une table de commandes. En utilisant char, le moteur de requête peut effectuer la jointure plus rapidement car il peut effectuer une arithmétique de pointeur (de manière déterministe) plutôt que de devoir déplacer ses pointeurs d’une quantité variable d’octets lorsqu’il lit les pages. Je sais que je pourrais vous avoir perdu sur cette dernière phrase. Les jointures dans SQL Server sont basées sur l’idée de “prédicats”. Un prédicat est une condition. Par exemple, myColumn = 1 ou OrderNumber <500.

Par conséquent, si SQL Server exécute une instruction DML et que les prédicats ou les clés associés sont une longueur fixe (char), le moteur de requête ne doit pas faire autant de travail pour faire correspondre les lignes d’une table aux lignes. une autre table. Il ne sera pas nécessaire de savoir combien de temps les données sont dans la ligne, puis de parcourir la chaîne pour trouver la fin. Tout cela prend du temps.

Maintenant, gardez à l’esprit que cela peut facilement être mal implémenté. J’ai vu char utilisé pour les champs clés primaires dans les systèmes en ligne. La largeur doit être petite, c’est-à-dire un caractère (15) ou quelque chose de raisonnable. Et cela fonctionne mieux dans les systèmes en ligne, car vous ne récupérez ou n’alimentez généralement qu’un petit nombre de lignes. Donc, avoir à “rsortingm” les espaces que vous obtiendrez dans le jeu de résultats est une tâche sortingviale plutôt que de rejoindre des millions de lignes d’une table à des millions de lignes sur une autre table.

Une autre raison pour laquelle CHAR a du sens sur varchar sur les systèmes en ligne est qu’il réduit les fractionnements de pages. En utilisant char, vous réservez essentiellement (et gaspillez) cet espace, donc si un utilisateur vient plus tard et met plus de données dans cette colonne, SQL lui a déjà alloué de l’espace et il y va.

Une autre raison d’utiliser CHAR est similaire à la deuxième raison. Si un programmeur ou un utilisateur effectue une mise à jour “par lots” sur des millions de lignes, en ajoutant par exemple une phrase à un champ de note, votre DBA ne vous appellera pas au milieu de la nuit. En d’autres termes, cela conduit à une croissance plus prévisible de la taille d’une firebase database.

Donc, il y a 3 façons dont un système en ligne (OLTP) peut bénéficier de char sur varchar. Je n’utilise quasiment jamais char dans un scénario entrepôt / parsing / OLAP, car vous avez en général tellement de données que toutes ces colonnes de caractères peuvent représenter une perte de place.

N’oubliez pas que char peut rendre votre firebase database beaucoup plus volumineuse mais que la plupart des outils de sauvegarde ont une compression de données, de sorte que vos sauvegardes ont généralement la même taille que si vous aviez utilisé varchar. Par exemple, LiteSpeed ​​ou RedGate SQL Backup.

Une autre utilisation est dans les vues créées pour exporter des données dans un fichier de largeur fixe. Disons que je dois exporter certaines données vers un fichier plat pour être lu par un mainframe. C’est une largeur fixe (non délimitée). J’aime stocker les données dans ma table “staging” sous le nom de varchar (consommant ainsi moins d’espace sur ma firebase database), puis utiliser une vue pour tout convertir en équivalent de caractère, la longueur correspondant à la largeur de cette colonne. . Par exemple:

 create table tblStagingTable ( pkID BIGINT (IDENTITY,1,1), CustomerFirstName varchar(30), CustomerLastName varchar(30), CustomerCityStateZip varchar(100), CustomerCurrentBalance money ) insert into tblStagingTable (CustomerFirstName,CustomerLastName, CustomerCityStateZip) ('Joe','Blow','123 Main St Washington, MD 12345', 123.45) create view vwStagingTable AS SELECT CustomerFirstName = CAST(CustomerFirstName as CHAR(30)), CustomerLastName = CAST(CustomerLastName as CHAR(30)), CustomerCityStateZip = CAST(CustomerCityStateZip as CHAR(100)), CustomerCurrentBalance = CAST(CAST(CustomerCurrentBalance as NUMERIC(9,2)) AS CHAR(10)) SELECT * from vwStagingTable 

C’est cool car en interne mes données prennent moins de place car elles utilisent varchar. Mais lorsque j’utilise DTS ou SSIS ou même simplement un copier-coller de SSMS vers Notepad, je peux utiliser la vue et obtenir le nombre exact d’espaces de fin. Dans DTS, nous avions l’habitude d’avoir une fonctionnalité appelée, putain j’oublie que je pense que ça s’appelait “suggest columns” ou quelque chose comme ça. Dans SSIS, vous ne pouvez plus faire cela, vous devez définir fastidieusement le gestionnaire de connexions de fichiers plats. Mais comme vous avez votre configuration d’affichage, SSIS peut connaître la largeur de chaque colonne et peut économiser beaucoup de temps lors de la création de vos tâches de stream de données.

Donc, en bout de ligne … utilisez varchar. Il y a un très petit nombre de raisons d’utiliser des caractères et ce n’est que pour des raisons de performances. Si vous avez un système avec des centaines de millions de lignes, vous remarquerez une différence notable si les prédicats sont déterministes (char), mais pour la plupart des systèmes utilisant char, c’est simplement du gaspillage d’espace.

J’espère que cela pourra aider. Jeff

Il y a des avantages sur le plan de la performance, mais en voici un qui n’a pas été mentionné: la migration en ligne. Avec char, vous réservez tout l’espace à l’avance. Disons que vous avez un caractère (1000) et que vous stockez 10 caractères, vous utiliserez les 1000 caractères de l’espace. Dans un varchar2 (1000), vous n’utiliserez que 10 caractères. Le problème vient lorsque vous modifiez les données. Disons que vous mettez à jour la colonne pour contenir maintenant 900 caractères. Il est possible que l’espace pour développer varchar ne soit pas disponible dans le bloc actuel. Dans ce cas, le moteur de firebase database doit migrer la ligne vers un autre bloc et placer un pointeur dans le bloc d’origine sur la nouvelle ligne du nouveau bloc. Pour lire ces données, le moteur de firebase database doit maintenant lire 2 blocs.
Personne ne peut dire de manière équivoque que varchar ou char sont meilleurs. Il y a un espace pour le compromis temporel et la question de savoir si les données seront mises à jour, surtout s’il y a de fortes chances que cela se développe.

Il y a une différence entre l’optimisation précoce des performances et l’utilisation d’un type de règle de meilleure pratique. Si vous créez de nouvelles tables où vous aurez toujours un champ de longueur fixe, il est judicieux d’utiliser CHAR, vous devriez l’utiliser dans ce cas. Ce n’est pas une optimisation précoce, mais plutôt une règle de base (ou une meilleure pratique).

ie – Si vous avez un champ d’état à 2 lettres, utilisez CHAR (2). Si vous avez un champ avec les noms d’état réels, utilisez VARCHAR.

Je choisirais varchar à moins que la colonne ne stocke une valeur fixe comme le code d’état des États-Unis – qui comporte toujours 2 caractères et que la liste des codes d’états américains valides ne change pas souvent :).

Dans tous les autres cas, même en stockant un mot de passe haché (de longueur fixe), je choisirais varchar.

Why – la colonne de type char est toujours remplie avec des espaces, ce qui fait que la colonne my_column est définie avec la valeur char (5) avec la valeur ‘ABC’ dans la comparaison:

 my_column = 'ABC' -- my_column stores 'ABC ' value which is different then 'ABC' 

faux.

Cette fonctionnalité pourrait conduire à de nombreux bogues irritants au cours du développement et rend les tests plus difficiles.

CHAR prend moins d’espace de stockage que VARCHAR si toutes vos valeurs de données dans ce champ ont la même longueur. Maintenant, en 2009, une firebase database de 800 Go est peut-être la même chose qu’une 810 Go si vous convertissez les VARCHAR en CHAR, mais pour les chaînes courtes (1 ou 2 caractères), CHAR est toujours la meilleure pratique du secteur.

Maintenant, si vous examinez la grande variété de types de données que la plupart des bases de données fournissent même pour les entiers seuls (bit, tiny, int, bigint), il y a des raisons de choisir l’une par rapport à l’autre. Le simple fait de choisir bigint à chaque fois est en fait un peu ignorant des objectives et des utilisations du domaine. Si un champ représente simplement un âge en années, un bigint est excessif. Maintenant, ce n’est pas nécessairement “faux”, mais ce n’est pas efficace.

Mais c’est un argument intéressant, et comme les bases de données s’améliorent avec le temps, on pourrait dire que CHAR vs VARCHAR devient moins pertinent.

Je maintiens le commentaire de Jim McKeeth.

En outre, l’indexation et les parsings de table complètes sont plus rapides si votre table ne contient que des colonnes CHAR. Fondamentalement, l’optimiseur sera capable de prédire la taille de chaque enregistrement s’il ne contient que des colonnes CHAR, alors qu’il doit vérifier la taille de chaque colonne VARCHAR.

En outre, si vous mettez à jour une colonne VARCHAR à une taille supérieure à son contenu précédent, vous pouvez forcer la firebase database à reconstruire ses index (car vous avez forcé la firebase database à déplacer physiquement l’enregistrement sur le disque). Alors que les colonnes CHAR ne se produiront jamais.

Mais vous ne vous soucierez probablement pas de la performance à moins que votre table ne soit énorme.

Rappelez-vous les mots sages de Djikstra. L’optimisation précoce des performances est la racine de tout mal.

C’est le compromis classique entre l’espace et la performance.

Dans MS SQL 2005, Varchar (ou NVarchar pour les langues nécessitant deux octets par caractère, à savoir le chinois) est de longueur variable. Si vous ajoutez à la ligne une fois qu’il a été écrit sur le disque dur, il localisera les données dans un emplacement non cont convenable à la ligne d’origine et entraînera la fragmentation de vos fichiers de données. Cela affectera les performances.

Donc, si l’espace n’est pas un problème, alors Char est meilleur pour les performances, mais si vous souhaitez réduire la taille de la firebase database, varchars est préférable.

Le calcul de la taille réelle requirejse pour une valeur de colonne et l’allocation d’espace pour un Varchar sont limités, donc si vous êtes certain de la durée, il est préférable d’utiliser Char et d’éviter le hit.

Je pense que dans votre cas, il n’y a probablement aucune raison de ne pas choisir Varchar. Cela vous donne une certaine flexibilité et, comme l’ont mentionné un certain nombre de répondants, la performance est maintenant telle que, sauf dans des circonstances très spécifiques, nous, les mortels (contrairement aux administrateurs de bases de données Google), ne verrons pas la différence.

Une chose intéressante à noter en ce qui concerne les types de firebase database est que sqlite (une mini firebase database populaire avec des performances assez impressionnantes) met tout dans la firebase database sous forme de chaîne et tape à la volée.

J’utilise toujours VarChar et je le fais généralement beaucoup plus que ce dont j’ai besoin. Par exemple. 50 pour Prénom, comme vous dites pourquoi ne pas simplement être en sécurité.

Beaucoup de gens ont fait remarquer que si vous connaissez la longueur exacte de la valeur en utilisant CHAR, certains avantages. Mais, bien que le stockage des États américains sous le nom de CHAR (2) soit excellent aujourd’hui, lorsque vous recevez le message des ventes «Nous venons de faire notre première vente en Australie», vous êtes dans un monde de souffrance. J’envoie toujours à surestimer combien de temps je pense que les champs devront être plutôt que de faire une estimation «exacte» pour couvrir les événements futurs. VARCHAR me donnera plus de flexibilité dans ce domaine.

Lors de l’utilisation de valeurs varchar, SQL Server a besoin de 2 octets supplémentaires par ligne pour stocker des informations sur cette colonne, alors que si vous utilisez char, il n’en a pas besoin.

Fragmentation. Char réserve de l’espace et VarChar ne le fait pas. Le partage de page peut être requirejs pour permettre la mise à jour vers varchar.

Dans certaines bases de données SQL, VARCHAR sera rempli à sa taille maximale afin d’optimiser les décalages, ceci afin d’accélérer les parsings et index des tables complètes.

À cause de cela, vous n’avez aucune économie d’espace en utilisant un VARCHAR (200) par rapport à un CHAR (200)

L’utilisation de CHAR (NCHAR) et de VARCHAR (NVARCHAR) entraîne des différences dans la manière dont le serveur de firebase database stocke les données. Le premier introduit des blancs à la fin; J’ai rencontré un problème en l’utilisant avec l’opérateur LIKE dans les fonctions SQL SERVER. Donc, je dois le rendre sûr en utilisant VARCHAR (NVARCHAR) tout le temps.

Par exemple, si nous avons une table TEST (ID INT, Status CHAR (1)) et que vous écrivez une fonction pour répertorier tous les enregistrements avec une valeur spécifique comme celle-ci:

 CREATE FUNCTION List(@Status AS CHAR(1) = '') RETURNS TABLE AS RETURN SELECT * FROM TEST WHERE Status LIKE '%' + @Status '%' 

Dans cette fonction, nous nous attendons à ce que la fonction retourne toutes les lignes lorsque le paramètre par défaut est placé, mais ce n’est pas le cas. Modifiez le type de données @Status à VARCHAR pour résoudre le problème.

Je n’utiliserais JAMAIS de caractères. J’ai eu ce débat avec beaucoup de gens et ils évoquent toujours le cliché fatigué que le char est plus rapide. Eh bien je dis, combien plus rapide? De quoi parle-t-on ici, en millisecondes, secondes et si oui, combien? Vous me dites que parce que quelqu’un réclame ses quelques millisecondes plus rapidement, nous devrions introduire des tonnes de bogues difficiles à corriger dans le système?

Donc, voici quelques problèmes que vous rencontrerez:

Chaque champ sera rempli, donc vous vous retrouverez avec du code pour toujours qui a RTRIMS partout. C’est aussi un énorme gaspillage d’espace disque pour les champs plus longs.

Maintenant, disons que vous avez l’exemple par excellence d’un champ de caractères d’un seul caractère, mais que le champ est facultatif. Si quelqu’un transmet une chaîne vide à ce champ, il devient un espace. Ainsi, lorsqu’une autre application / processus l’interroge, ils obtiennent un seul espace s’ils n’utilisent pas rsortingm. Nous avons eu des documents XML, des fichiers et d’autres programmes, n’affiche qu’un seul espace, dans des champs facultatifs et casse des choses.

Donc, maintenant, vous devez vous assurer que vous transmettez des caractères nuls et non une chaîne vide au champ char. Mais ce n’est pas l’utilisation correcte de null. Voici l’utilisation de null. Disons que vous obtenez un fichier d’un fournisseur

Nom | Sexe | Ville Bob || Los Angeles

Si le sexe n’est pas spécifié, entrez Bob, chaîne vide et Los Angeles dans la table. Maintenant, disons que vous obtenez le fichier et son format change et le genre n’est plus inclus mais était dans le passé.

Nom | Ville Bob | Seattle

Eh bien maintenant que le genre n’est pas inclus, j’utiliserais null. Varchars le supporte sans problèmes.

Char, par contre, est différent. Vous devez toujours envoyer null. Si vous envoyez une chaîne vide, vous obtiendrez un champ contenant des espaces.

Je pourrais continuer encore et encore avec tous les bugs que j’ai dû résoudre depuis les chars et dans environ 20 ans de développement.