Quelles sont les principales différences de performances entre les types de données varchar et nvarchar SQL Server?

L'espace disque n'est pas le problème... mais la mémoire et les performances. Le Double de la page lit, double la taille de l'index, étrange COMME et = constante de comportement etc

avez-vous besoin de stocker le chinois etc script? Oui ou non...

Et à partir de MS BOL " Stockage et Effets sur les Performances de l'Unicode "

Modifier :

récente question so soulignant à quel point mauvais nvarchar la performance peut l'être...

SQL Server utilise élevé CPU lors de la recherche à l'intérieur de type nvarchar chaînes

soyez cohérent! Joindre un VARCHAR à NVARCHAR a un grand succès.

nvarchar va avoir d'importants frais généraux dans la mémoire, le stockage, le jeu de travail et l'indexation, donc si les spécifications dictent qu'il sera vraiment jamais être nécessaire, ne vous embêtez pas.

Je n'aurais pas une règle" toujours nvarchar " dure et rapide parce qu'elle peut être un gaspillage complet dans de nombreuses situations - en particulier ETL de ASCII/EBCDIC ou identificateurs et colonnes de code qui sont souvent des clés et des clés étrangères.

sur l'autre hand, Il ya beaucoup de cas de colonnes, où je serais sûr de poser cette question tôt et si je n'obtenais pas une réponse dure et rapide immédiatement, je ferais la colonne nvarchar.

pour votre application, nvarchar est très bien parce que la taille de la base de données est petite. Dire "toujours utiliser nvarchar" est une simplification à outrance. Si vous n'êtes pas tenu de stocker des choses comme le Kanji ou d'autres personnages fous, utilisez VARCHAR, il va utiliser beaucoup moins d'espace. Mon prédécesseur à mon poste actuel a conçu quelque chose en utilisant NVARCHAR quand il n'était pas nécessaire. Nous l'avons récemment commuté en VARCHAR et avons enregistré 15 Go sur cette table (il était fortement écrit). En outre, si vous avez alors un indice sur cette table et vous voulez inclure cette colonne ou faire un indice composite, vous avez juste fait votre taille de fichier d'index plus grand.

il suffit d'être réfléchi dans votre décision; dans le développement de SQL et les définitions de données il semble qu'il y ait rarement une" réponse par défaut " (autre que d'éviter les curseurs à tout prix, bien sûr).

j'hésite à ajouter une autre réponse ici car il y en a déjà pas mal, mais il faut faire quelques remarques qui n'ont pas été faites ou qui n'ont pas été faites clairement.

d'Abord: Faire pas toujours utiliser NVARCHAR . C'est une attitude / approche très dangereuse, et souvent coûteuse. Et il n'est pas mieux de dire jamais utilisez des curseurs" car ils sont parfois les moyens les plus efficaces pour résoudre un problème particulier, et le travail commun autour de faire une boucle WHILE sera presque toujours plus lent qu'un correctement curseur fait.

la seule fois où vous devez utiliser le terme" toujours "est quand vous conseillez de"toujours faire ce qui est le mieux pour la situation". Bien sûr, c'est souvent difficile à déterminer, surtout quand on essaie d'équilibrer les gains à court terme dans le temps de développement (gestionnaire: "nous avons besoin de cette fonctionnalité -- que vous ne connaissiez pas jusqu'à seulement maintenant, il y a une semaine!") avec des coûts de maintenance à long terme (directeur qui avait initialement fait pression sur l'équipe pour qu'elle réalise un projet de 3 mois dans un sprint de 3 semaines: "Pourquoi avons-nous ces problèmes de performance? Comment aurions-nous pu faire X qui n'a pas de flexibilité? On ne peut pas se permettre un sprint ou deux pour arranger ça. Que pouvons-nous faire en une semaine pour revenir à nos priorités? Et nous avons vraiment besoin de passer plus de temps dans le design pour que cela ne se reproduise pas!").

Second: la réponse de @gbn aborde quelques points très importants à prendre en considération lors de la prise de certaines décisions de modélisation de données lorsque le chemin n'est pas à 100% clair. Mais il y a encore plus à considérer:

• taille des fichiers du journal des transactions
• temps nécessaire pour répliquer (si on utilise la réplication)
• le temps qu'il faut pour ETL (si ETLing)
• temps nécessaire pour expédier les journaux vers un système à distance et de restauration (si vous utilisez l'envoi de Journaux)
• taille des sauvegardes
• le temps qu'il faut pour compléter la sauvegarde
• le temps qu'il faut pour faire une restauration (cela pourrait être important un jour; -)
• de la taille nécessaire pour la base de données tempdb
• la performance de déclencheurs (pour les tables inserted et deleted qui sont stockées dans la base de données tempdb)
• la performance de la ligne de contrôle de version (si vous utilisez SNAPSHOT ISOLATION, depuis la version stocker est dans tempdb)
• possibilité d'obtenir un nouvel espace disque lorsque le CFO dit qu'ils viennent de dépenser 1 million de dollars sur un SAN l'année dernière et donc ils n'autoriseront pas un autre 250k $ pour le stockage supplémentaire
• durée des opérations D'insertion et de mise à jour
• durée de la maintenance de l'index
• etc, etc,etc.

Perdre de l'espace a un énorme effet de cascade sur l'ensemble du système. J'ai écrit un article allant dans le détail explicite sur ce sujet: disque est bon marché! ORLY? (inscription gratuite obligatoire; désolé je n'ai pas de contrôle que de la politique).

troisième: alors que certaines réponses se concentrent incorrectement sur l'aspect "ceci est une petite application", et certains suggèrent correctement "d'utiliser ce qui est approprié", aucun des les réponses ont fourni une orientation réelle à L'O. P. Un détail important mentionné dans la Question Est qu'il s'agit d'une page web pour leur école. Grand! Nous pouvons donc suggérer que:

• les Champs pour les Étudiants et/ou de la Faculté des noms probablement être NVARCHAR depuis le, au fil du temps, il est seulement plus susceptibles que les noms d'autres cultures, seront à l'affiche jusqu'en ces lieux.
• mais pour l'adresse et les noms de ville? Le but de l'application n'était pas indiqué (cela aurait été utile), mais en supposant que les enregistrements d'adresse, s'il y en a, se rapportent uniquement à une région géographique particulière (c.-à-d. une seule langue / culture), alors utilisez VARCHAR avec la Page de Code appropriée (qui est déterminée à partir de la compilation du champ).
• si l'on stocke des codes ISO D'état et/ou de pays (il n'est pas nécessaire de stocker INT / TINYINT car les codes ISO sont de longueur fixe, lisible par l'homme, et bien, standard :) utilisez CHAR(2) pour les codes à deux lettres et CHAR(3) pour les codes à trois lettres. Et envisager d'utiliser une Collation binaire comme Latin1_General_100_BIN2 .
• si vous stockez des codes postaux (c.-à-d. des codes postaux), utilisez VARCHAR car il s'agit d'une norme internationale de ne jamais utiliser de lettre en dehors de A-Z. Et oui, utilisez toujours VARCHAR même si vous stockez seulement des codes postaux américains et non INT, car les codes postaux ne sont pas des numéros, Ce sont des chaînes, et certains d'entre eux ont un"0". Et envisager d'utiliser un Collation binaire telle que Latin1_General_100_BIN2 .
• si vous stockez des adresses e-mail et/ou des URL, utilisez NVARCHAR car ces deux-là peuvent maintenant contenir des caractères Unicode.
• et ainsi de suite....

Quatrième: Maintenant que vous avez NVARCHAR prise de données jusqu'à deux fois plus d'espace que nécessaire pour les données qui s'intègre parfaitement dans VARCHAR ("va très bien" = ne pas se transformer en "?") et en quelque sorte, comme si par magic, l'application s'est développée et maintenant il ya des millions d'enregistrements dans au moins un de ces domaines où la plupart lignes sont standard ASCII, mais certains contiennent des caractères Unicode donc vous devez garder NVARCHAR , considérez ce qui suit:

1. si vous utilisez SQL Server 2008 - 2016 RTM et sont sur Enterprise Edition, ou si vous utilisez SQL Server 2016 SP1 (qui a rendu la Compression de données disponible dans tous les editions) ou plus récent, alors vous pouvez activer Compression de données . La Compression de données peut (mais ne sera pas "toujours") compresser les données Unicode dans les champs NCHAR et NVARCHAR . Les facteurs déterminants sont les suivants:

   1. NCHAR(1 - 4000) et NVARCHAR(1 - 4000) utiliser le Schéma de Compression Standard Unicode , mais seulement à partir de SQL Server 2008 R2, ET seulement DANS la LIGNE de données, pas de DÉBORDEMENT! Cela semble être mieux que l' algorithme régulier de compression ligne / PAGE.
   2. NVARCHAR(MAX) et XML (et je suppose aussi VARBINARY(MAX) , TEXT , et NTEXT ) à des données EN LIGNE (pas de ligne de MÉTIER ou de DÉBORDEMENT de pages) peuvent au moins être compressé, mais pas LIGNE comprimé. Bien sûr, la compression de PAGE dépend de la taille de la valeur en ligne: j'ai testé avec VARCHAR (MAX) et j'ai vu que 6000 lignes de caractère / octet ne comprendraient pas, mais 4000 lignes de caractère/octet faire.
   3. toutes les données hors ligne, LOB ou OVERLOW = pas de Compression pour vous!

2. si vous utilisez SQL Server 2005, ou 2008 - 2016 RTM et pas sur Enterprise Edition, vous pouvez avoir deux champs: un VARCHAR et un NVARCHAR . Par exemple, disons que vous stockez des URLs qui sont la plupart du temps tous les caractères ASCII de base (valeurs 0 - 127) et qui sont donc compatibles avec VARCHAR , mais qui ont parfois une Unicode caractère. Votre schéma peut inclure les 3 champs suivants:

     ...
     URLa VARCHAR(2048) NULL,
     URLu NVARCHAR(2048) NULL,
     URL AS (ISNULL(CONVERT(NVARCHAR([URLa])), [URLu])),
     CONSTRAINT [CK_TableName_OneUrlMax] CHECK (
                       ([URLa] IS NOT NULL OR [URLu] IS NOT NULL)
                   AND ([URLa] IS NULL OR [URLu] IS NULL))
   );
   

   dans ce modèle, vous seulement sélectionnez dans la colonne [URL] calculé. Pour insérer et mettre à jour, vous déterminez quel champ utiliser en voyant si la conversion modifie la valeur entrante, qui doit être de NVARCHAR type:

   INSERT INTO TableName (..., URLa, URLu)
   VALUES (...,
           IIF (CONVERT(VARCHAR(2048), @URL) = @URL, @URL, NULL),
           IIF (CONVERT(VARCHAR(2048), @URL) <> @URL, NULL, @URL)
          );
   

3. vous pouvez décompresser les valeurs entrantes en VARBINARY(MAX) puis décompresser sur le sortie:

   • pour SQL Server 2005 - 2014: vous pouvez utiliser SQLCLR. SQL# (une bibliothèque SQLCLR que j'ai écrit) vient avec Util_GZip et Util_GUnzip dans la version libre
   • pour SQL Server 2016 et les versions plus récentes: vous pouvez utiliser les fonctions intégrées COMPRESS et DECOMPRESS , qui sont aussi GZip.

4. si vous utilisez SQL Server 2017 ou plus récent, vous pouvez chercher à faire de la table un index Columnstore groupé.

5. bien que ce ne soit pas encore une option viable, SQL Server 2019 introduit le support natif pour UTF-8 dans les types de données VARCHAR / CHAR . Il y a actuellement trop de bogues pour qu'il puisse être utilisé, mais s'ils sont corrigés, alors c'est une option pour certains scénarios. Veuillez voir mon post", Support natif UTF-8 dans SQL Server 2019: Sauveur ou faux prophète? ", pour une analyse détaillée de cette nouvelle fonctionnalité.

comme votre application est petite, il n'y a essentiellement aucune augmentation de coût appréciable à l'utilisation de nvarchar sur varchar, et vous économisez vous-même des maux de tête potentiels sur la route si vous avez un besoin de stocker des données unicode.

ces dernières années, tous nos projets ont utilisé NVARCHAR pour tout, car tous ces projets sont multilingues. Données importées de sources externes (p. ex. un fichier ASCII, etc.) est convertie en Unicode avant d'être insérée dans la base de données.

Je n'ai pas encore rencontré de problèmes liés à la performance dans les grands indices, etc. Les index utilisent plus de mémoire, mais la mémoire est bon marché.

Si vous utilisez des procédures stockées ou construire SQL à la volée s'assurer que toutes les constantes de chaîne sont préfixées avec N (par exemple SET @foo = N'Hello world.";) ainsi la constante est aussi Unicode. Cela évite toute conversion de type de chaîne à l'exécution.

YMMV.

Généralement parlant; commencer par le type de données le plus cher qui a le moins de contraintes. mise en production . Si la performance commence à être un problème, découvrez ce qui est réellement stocké dans ces colonnes nvarchar . Y a-t-il des personnages qui ne rentreraient pas dans varchar ? Si non, passez en varchar. N'essayez pas de pré-optimiser avant de savoir où est la douleur. Mon avis est que le choix entre nvarchar/varchar n'est pas ce qui va ralentir votre application dans un avenir prévisible. Il y aura d'autres parties de l'application où l'accord de performance vous donnera beaucoup plus bang for the bucks .

je peux parler d'expérience à ce sujet, méfiez-vous de nvarchar . À moins que vous ne l'exigiez absolument ce type de champ de données détruit la performance sur une base de données plus grande. J'ai hérité d'une base de données qui me faisait mal en termes de performances et d'espace. Nous avons pu réduire de 70% la taille d'une base de données de 30 Go! Il y a eu d'autres modifications apportées pour aider à la performance, mais je suis sûr que le varchar 's aidé de façon significative avec cela aussi bien. Si votre base de données a le potentiel de se développer les tableaux à un million + dossiers restent loin de nvarchar à tout prix.

je m'occupe de cette question au travail souvent:

• FTP flux de stocks et les prix - les descriptions de poste et d'autres textes ont été dans nvarchar quand varchar a bien fonctionné. Convertir ceux-ci en varchar réduit la taille des fichiers presque en moitié et vraiment aidé avec les téléchargements.

• le scénario ci-dessus a bien fonctionné jusqu'à ce que quelqu'un mette un caractère spécial dans la description de l'article (peut-être marque de commerce, ne se souvient pas)

Je n'utilise toujours pas nvarchar à chaque fois sur varchar. S'il y a un doute ou un potentiel pour des caractères spéciaux, j'utilise nvarchar. Je trouve que j'utilise varchar surtout quand je suis dans le contrôle à 100% de ce qui peuplent le champ.

pourquoi, dans toute cette discussion, N'a-t-il pas été fait mention de L'UTF-8? Être capable de stocker la portée unicode complète des caractères ne signifie pas qu'il faut toujours allouer deux octets par caractère (ou "point de code" pour utiliser le terme UNICODE). Tous les ASCII sont UTF-8. Est-ce que SQL Server vérifie les champs VARCHAR () que le texte est strictement ASCII (c'est-à-dire le bit d'octet zéro)? J'espère que non.

si vous voulez alors stocker unicode et voulez la compatibilité avec les anciennes applications ASCII uniquement, je pense que L'utilisation de VARCHAR () et UTF-8 serait la solution miracle: elle n'utilise plus d'espace quand elle en a besoin.

pour ceux qui ne connaissent pas L'UTF-8, puis-je recommander a primer .

il y aura des cas exceptionnels où vous voudrez délibérément restreindre le type de données pour vous assurer qu'il ne contient pas des caractères d'un certain ensemble. Par exemple, j'avais un scénario où je devais stocker le nom de domaine dans une base de données. L'Internationalisation des noms de domaine n'étant pas fiable à l'époque, il était préférable de limiter les entrées au niveau de base et d'éviter tout problème potentiel.

si vous utilisez NVARCHAR juste parce qu'une procédure stockée système l'exige, l'occurrence la plus fréquente étant inexplicablement sp_executesql , et votre SQL dynamique est très long, vous seriez mieux du point de vue de la performance faire toutes les manipulations de chaîne (concaténation, remplacement, etc.) dans VARCHAR , puis convertir le résultat final en NVARCHAR et l'introduire dans le paramètre proc. Alors non, n'utilisez pas toujours NVARCHAR !