Est-ce qu'il y a un avantage à utiliser varchar(500) au lieu de varchar(8000) ?

Question

J'ai lu à ce sujet sur les forums MSDN et ici et je ne suis toujours pas clair. Je pense que c'est correct : Varchar(max) sera stocké en tant que type de données texte, ce qui comporte des inconvénients. Donc, disons que votre champ sera fiablement sous les 8000 caractères. Comme un champ BusinessName dans ma table de base de données. En réalité, un nom d'entreprise sera probablement toujours en dessous (sortant un numéro de mon chapeau) de 500 caractères. Il semble que de nombreux champs varchar que je rencontre tombent bien en dessous du compte de caractères 8k.

Donc, devrais-je faire de ce champ un varchar(500) au lieu de varchar(8000) ? D'après ce que je comprends de SQL, il n'y a pas de différence entre les deux. Donc, pour simplifier les choses, je voudrais définir tous mes champs varchar comme varchar(8000). Est-ce que cela comporte des inconvénients ?

Lié : Taille des colonnes varchar (Je n'ai pas senti que celui-ci répondait à ma question).

Answer 1

Un exemple où cela peut faire une différence est que cela peut empêcher une optimisation des performances qui évite d'ajouter des informations de versionnement de ligne aux tables avec des déclencheurs après.

Cela est couvert par Paul White ici

La taille réelle des données stockées est sans importance - c'est la taille potentielle qui importe.

De même, si vous utilisez des tables optimisées en mémoire depuis 2016, il est possible d'utiliser des colonnes LOB ou des combinaisons de largeurs de colonnes qui pourraient potentiellement dépasser la limite en ligne mais avec une pénalité.

Les colonnes (Max) sont toujours stockées hors ligne. Pour les autres colonnes, si la taille de ligne de données dans la définition de table peut dépasser 8,060 octets, SQL Server déplace la ou les colonnes de longueur variable les plus grandes hors ligne. Encore une fois, cela ne dépend pas de la quantité de données que vous stockez là-bas.

Cela peut avoir un grand effet négatif sur la consommation de mémoire et les performances

Un autre cas où la sur-déclaration des largeurs de colonnes peut faire une grande différence est si la table sera jamais traitée en utilisant SSIS. La mémoire allouée pour les colonnes de longueur variable (non BLOB) est fixée pour chaque ligne dans un arbre d'exécution et est conforme à la longueur maximale déclarée des colonnes, ce qui peut entraîner une utilisation inefficace des tampons mémoire (exemple). Bien que le développeur de package SSIS puisse déclarer une taille de colonne plus petite que la source, cette analyse est mieux réalisée à l'avance et appliquée là-bas.

Dans le moteur SQL Server lui-même, un cas similaire est que lors du calcul de l'allocation de mémoire pour les opérations de tri, SQL Server suppose que les colonnes varchar(x) consommeront en moyenne x/2 octets.

Si la plupart de vos colonnes varchar sont plus remplies que cela, cela peut conduire à ce que les opérations de tri débordent vers tempdb.

Dans votre cas, si vos colonnes varchar sont déclarées comme 8000 octets mais ont en réalité des contenus bien inférieurs, votre requête se verra allouer de la mémoire dont elle n'a pas besoin, ce qui est évidemment inefficace et peut entraîner des attentes pour les allocations mémoire.

Cela est abordé dans la Partie 2 du Webcast des Ateliers SQL 1 téléchargeable ici ou voir ci-dessous.

use tempdb;

CREATE TABLE T(
id INT IDENTITY(1,1) PRIMARY KEY,
number int,
name8000 VARCHAR(8000),
name500 VARCHAR(500))

INSERT INTO  T 
(number,name8000,name500)
SELECT number, name, name /*<--Same contents in both cols*/
FROM master..spt_values

SELECT id,name500
FROM T
ORDER BY number

SELECT id,name8000
FROM T
ORDER BY number

Answer 2

D'un point de vue du traitement, cela ne fera pas de différence d'utiliser varchar(8000) par rapport à varchar(500). C'est plutôt une sorte de "bonne pratique" de définir une longueur maximale qu'un champ doit contenir et de faire de votre varchar cette longueur. C'est quelque chose qui peut être utilisé pour aider à la validation des données. Par exemple, faire en sorte qu'une abréviation d'état soit composée de 2 caractères ou qu'un code postal soit composé de 5 ou 9 caractères. C'était autrefois une distinction plus importante lorsque vos données interagissaient avec d'autres systèmes ou interfaces utilisateur où la longueur du champ était critique (par exemple, un ensemble de données de fichiers plats mainframe), mais de nos jours je pense que c'est plus une habitude qu'autre chose.

Answer 3

Il existe quelques inconvénients aux grandes colonnes qui sont un peu moins évidentes et pourraient vous surprendre un peu plus tard :

• Toutes les colonnes que vous utilisez dans un INDEX - ne doivent pas dépasser 900 octets
• Toutes les colonnes dans une clause ORDER BY ne doivent pas dépasser 8060 octets. C'est un peu difficile à comprendre puisque cela ne s'applique qu'à certaines colonnes. Voir SQL 2008 R2 Row size limit exceeded pour plus de détails)
• Si la taille totale de la ligne dépasse 8060 octets, vous obtenez un "débordement de page" pour cette ligne. Cela peut affecter les performances (Une page est une unité d'allocation dans SQLServer et est fixée à 8000 octets + un peu de surcharge. Le dépasser ne sera pas grave, mais c'est perceptible et vous devriez essayer de l'éviter si vous le pouvez facilement)
• De nombreuses autres structures de données internes, tampons et enfin vos propres variables et variables de table doivent toutes refléter ces tailles. Avec des tailles excessives, une allocation de mémoire excessive peut affecter les performances

En règle générale, essayez d'être conservateur avec la largeur de la colonne. Si cela devient un problème, vous pouvez facilement l'élargir pour répondre aux besoins. Si vous remarquez des problèmes de mémoire plus tard, réduire ultérieurement une colonne large peut devenir impossible sans perdre de données et vous ne saurez pas par où commencer.

Dans votre exemple des noms d'entreprises, réfléchissez à l'endroit où vous prévoyez de les afficher. Y a-t-il vraiment de la place pour 500 caractères ?? Si ce n'est pas le cas, il est peu utile de les stocker de cette manière. http://en.wikipedia.org/wiki/List_of_companies_of_the_United_States liste certains noms d'entreprises et le maximum est d'environ 50 caractères. Je mettrais donc 100 comme maximum pour la colonne. Peut-être plus proche de 80.

Answer 4

En dehors des meilleures pratiques (réponse de BBlake)

• Vous recevez des avertissements sur la taille maximale de ligne (8060) octets et la largeur de l'index (900 octets) avec le DDL
• Le DML se bloquera si vous dépassez ces limites
• Le PADDING ANSI ON est la valeur par défaut, donc vous pourriez finir par stocker beaucoup d'espaces vides

Answer 5

Idéalement, vous voudriez aller plus petit que ça, jusqu'à une longueur raisonnable (500 n'est pas une taille raisonnable) et vous assurer que la validation du client attrape lorsque les données vont être trop grandes et envoie une erreur utile.

Alors que le varchar n'est pas vraiment réservé d'espace dans la base de données pour l'espace inutilisé, je me souviens que des versions de SQL Server ont eu un problème avec les lignes de base de données plus larges que certains nombres d'octets (je ne me souviens pas du nombre exact) et en fait jeter les données qui ne rentrent pas. Un certain nombre de ces octets étaient réservés pour des choses internes à SQL Server.