Y at-il des inconvénients à toujours utiliser nvarchar (MAX)?

La même question a été posée sur les forums MSDN:

• Varchar (max) vs Varchar (255)

De l'article original (beaucoup plus d'informations là-bas):

Lorsque vous stockez des données dans une colonne VARCHAR (N), les valeurs sont stockées physiquement de la même manière. Mais lorsque vous le stockez dans une colonne VARCHAR (MAX), derrière l'écran, datatables sont traitées comme une valeur TEXT. Il y a donc un traitement supplémentaire nécessaire pour traiter une valeur VARCHAR (MAX). (seulement si la taille dépasse 8000)

VARCHAR (MAX) ou NVARCHAR (MAX) est considéré comme un «type de grande valeur». Les types de grande valeur sont généralement stockés en dehors des rangs. Cela signifie que la ligne de données aura un pointeur vers un autre location où la «grande valeur» est stockée …

C'est une question juste et il a fait état à part l'évidence …

Les inconvénients pourraient inclure:

Répercussions sur les performances L'optimiseur de requêtes utilise la taille du champ pour déterminer le plan d'exécution le plus efficace.

"1. L'allocation d'espace dans les extensions et les pages de la database sont flexibles.Ainsi, lorsque vous ajoutez des informations au champ en utilisant update, votre database devrait créer un pointeur si les nouveldatatables sont plus longues que les précédentes. devenir fragmenté = performance inférieure dans presque tout, de l'index à supprimer, mettre à jour et insère. " http://sqlblogcasts.com/blogs/simons/archive/2006/02/28/Why-use-anything-but-varchar_2800_max_2900_.aspx

Incidences sur l'intégration – Difficile pour les autres systèmes de savoir comment intégrer votre database Croissance imprévisible des données Problèmes de security possibles, p. Ex. Vous pourriez faire planter un système en occupant tout l'espace disque

Il y a un bon article ici: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

Parfois, vous voulez que le type de données impose un certain sens aux données qu'il contient.

Supposons par exemple que vous ayez une colonne qui ne devrait pas dépasser 20 caractères. Si vous définissez cette colonne comme VARCHAR (MAX), une application malveillante pourrait y insert une longue string et vous ne saurez jamais, ou avez un moyen de l'empêcher.

La prochaine fois que votre application utilisera cette string, dans l'hypothèse où la longueur de la string est modeste et raisonnable pour le domaine qu'elle représente, vous rencontrerez un résultat imprévisible et confus.

J'ai vérifié quelques articles et find le script de test utile de ceci: http://www.sqlservercentral.com/Forums/Topic1480639-1292-1.aspx Alors l'a changé pour comparer entre NVARCHAR (10) vs NVARCHAR (4000) contre NVARCHAR (MAX ) et je ne trouve pas de différence de vitesse lorsque j'utilise des nombres spécifiés mais en utilisant MAX. Vous pouvez tester par vous-même. Espérons cette aide.

 SET NOCOUNT ON; --===== Test Variable Assignment 1,000,000 times using NVARCHAR(10) DECLARE @SomeSsortingng NVARCHAR(10), @StartTime DATETIME; --===== SELECT @startTime = GETDATE(); SELECT TOP 1000000 @SomeSsortingng = 'ABC' FROM master.sys.all_columns ac1, master.sys.all_columns ac2; SELECT testTime='10', Duration = DATEDIFF(ms,@StartTime,GETDATE()); GO --===== Test Variable Assignment 1,000,000 times using NVARCHAR(4000) DECLARE @SomeSsortingng NVARCHAR(4000), @StartTime DATETIME; SELECT @startTime = GETDATE(); SELECT TOP 1000000 @SomeSsortingng = 'ABC' FROM master.sys.all_columns ac1, master.sys.all_columns ac2; SELECT testTime='4000', Duration = DATEDIFF(ms,@StartTime,GETDATE()); GO --===== Test Variable Assignment 1,000,000 times using NVARCHAR(MAX) DECLARE @SomeSsortingng NVARCHAR(MAX), @StartTime DATETIME; SELECT @startTime = GETDATE(); SELECT TOP 1000000 @SomeSsortingng = 'ABC' FROM master.sys.all_columns ac1, master.sys.all_columns ac2; SELECT testTime='MAX', Duration = DATEDIFF(ms,@StartTime,GETDATE()); GO 

Pensez-y comme juste un autre niveau de security. Vous pouvez concevoir votre table sans relation de key étrangère – parfaitement valide – et assurer l'existence d'entités associées entièrement sur la couche de gestion. Cependant, les foreign keys sont considérées comme de bonnes pratiques de design, car elles ajoutent un autre niveau de contrainte au cas où quelque chose gâcherait la couche de gestion. Il en va de même pour la limitation de la taille du champ et n'utilisant pas varchar MAX.

Sur la base du lien fourni dans la réponse acceptée, il apparaît que:

1. 100 caractères stockés dans un champ nvarchar(MAX) seront stockés pas différent de 100 caractères dans un champ nvarchar(100) – datatables seront stockées en ligne et vous n'aurez pas la surcharge de lecture et d'écriture des données 'hors ligne'. Donc pas de soucis là-bas.

2. Si la taille est supérieure à 4000, datatables seront automatiquement stockées «hors ligne», ce que vous voulez. Donc, pas de soucis là non plus.

Toutefois…

3. Vous ne pouvez pas créer d'index sur une colonne nvarchar(MAX) . Vous pouvez utiliser l'indexing de text intégral, mais vous ne pouvez pas créer d'index sur la colonne pour améliorer les performances de la requête. Pour moi, cela scelle le deal … c'est un désavantage certain d'utiliser toujours nvarchar (MAX).

Conclusion:

Si vous voulez une sorte de "longueur de string universelle" dans toute votre database, qui peut être indexée et qui ne gâchera pas l'espace et le time d'access, vous pouvez utiliser nvarchar(4000) .

Une raison pour ne pas utiliser les champs max ou text est que vous ne pouvez pas effectuer de reconstruction d'index en ligne, c'est-à-dire REBUILD WITH ONLINE = ON même avec SQL Server Enterprise Edition.

Le seul problème que j'ai trouvé était que nous développons nos applications sur SQL Server 2005, et dans un cas, nous devons soutenir SQL Server 2000. Je viens d'apprendre, à la dure que SQL Server 2000 n'aime pas l'option MAX pour varchar ou nvarchar.

Mauvaise idée quand vous savez que le champ sera dans une plage définie – 5 à 10 caractères par exemple. Je pense que je n'utiliserais max que si je n'étais pas sûr de la longueur. Par exemple, un numéro de téléphone ne dépassera jamais un certain nombre de caractères.

Pouvez-vous honnêtement dire que vous n'êtes pas certain de la longueur approximative requirejse pour chaque champ de votre tableau?

Je comprends votre point cependant, il y a quelques champs que je considérerais certainement en utilisant varchar (max).

Fait intéressant, les documents MSDN résument assez bien:

Utilisez varchar lorsque les tailles des inputs de données de colonne varient considérablement. Utilisez varchar (max) lorsque les tailles des inputs de données de colonne varient considérablement et que la taille peut dépasser 8 000 octets.

Il y a une discussion intéressante sur la question ici .

Le travail de la database consiste à stocker des données afin qu'elles puissent être utilisées par l'entreprise. Une partie de l'utilité de ces données est de s'assurer qu'elles sont significatives. Permettre à quelqu'un d'entrer un nombre illimité de caractères pour son prénom n'assure pas des données significatives.

Construire ces contraintes dans la couche de gestion est une bonne idée, mais cela ne garantit pas que la database restra intacte. La seule façon de garantir que les règles de données ne sont pas violées consiste à les appliquer au niveau le plus bas possible dans la database.

Un problème est que si vous devez travailler avec plusieurs versions de SQL Server, le MAX ne fonctionnera pas toujours. Donc, si vous travaillez avec des bases de données héritées ou toute autre situation impliquant plusieurs versions, il vaut mieux être très prudent.

Comme indiqué ci-dessus, il s'agit principalement d'un compromis entre stockage et performance. Au less dans la plupart des cas.

Cependant, il y a au less un autre facteur à prendre en count lorsque vous choisissez n / varchar (Max) sur n / varchar (n). Est-ce que datatables vont être indexées (comme, disons, un nom de famille)? Comme la définition MAX est considérée comme un object LOB, tout ce qui est défini comme MAX n'est pas disponible pour l'indexing. et sans index, toute search impliquant datatables en tant que prédicat dans une clause WHERE va être forcée dans une parsing de table complète, qui est la pire performance que vous pouvez get pour les searchs de données.

1) Le server SQL devra utiliser plus de ressources (memory allouée et time CPU) en traitant avec nvarchar (max) vs nvarchar (n) où n est un nombre spécifique au champ.

2) Qu'est-ce que cela signifie en ce qui concerne la performance?

Sur SQL Server 2005, j'ai interrogé 13 000 lignes de données à partir d'une table avec 15 colonnes nvarchar (max). J'ai chronométré les requêtes à plusieurs resockets et ensuite changé les colonnes à nvarchar (255) ou less.

Les requêtes avant l'optimization étaient en moyenne à 2.0858 secondes. Les requêtes après le changement sont returnnées en moyenne 1,90 secondes. C'était environ 184 millisecondes d'amélioration à la requête select * de base. C'est une amélioration de 8,8%.

3) Mes résultats concordent avec quelques autres articles qui indiquaient qu'il y avait une différence de performance. En fonction de votre database et de la requête, le pourcentage d'amélioration peut varier. Si vous n'avez pas beaucoup d'users simultanés ou de très nombreux loggings, la différence de performance ne sera pas un problème pour vous. Cependant, la différence de performance augmentera avec l'augmentation du nombre d'loggings et d'users simultanés.

J'ai eu un udf qui rembourré les strings et mettre la sortie à varchar (max). Si cela a été utilisé directement au lieu de revenir à la taille appropriée pour la colonne en cours d'ajustement, les performances étaient très médiocres. J'ai fini par mettre le udf à une longueur arbitraire avec une grosse note au lieu de countr sur tous les appelants de l'udf pour redéfinir la string à une taille plus petite.

Lien intéressant: Pourquoi utiliser un VARCHAR quand vous pouvez utiliser TEXT?

Il s'agit de PostgreSQL et de MySQL, donc l'parsing des performances est différente, mais la logique de "l'explicite" tient toujours: Pourquoi vous forcer à toujours vous soucier de quelque chose qui est pertinent un petit pourcentage du time? Si vous avez enregistré une adresse e-mail dans une variable, vous utiliseriez une «string» et non une «string limitée à 80 caractères».

support du système hérité. Si vous avez un système qui utilise datatables et qu'il est prévu que la longueur soit longue, la database est un bon endroit pour en appliquer la longueur. Ce n'est pas idéal, mais les systèmes existants ne sont parfois pas idéaux. = P

Si toutes datatables d'une rangée (pour toutes les colonnes) ne prendraient raisonnablement pas 8000 caractères ou less, la design de la couche de données devrait l'appliquer.

Le moteur de database est beaucoup plus efficace en gardant tout hors du stockage blob. Plus vous pouvez restreindre une rangée, mieux c'est. Le plus de lignes vous pouvez entasser dans une page le mieux. La database fonctionne mieux lorsqu'elle doit accéder à less de pages.

Mes tests ont montré qu'il y a des différences lors de la sélection.

 CREATE TABLE t4000 (a NVARCHAR(4000) NULL); CREATE TABLE tmax (a NVARCHAR(MAX) NULL); DECLARE @abc4 NVARCHAR(4000) = N'ABC'; INSERT INTO t4000 SELECT TOP 1000000 @abc4 FROM master.sys.all_columns ac1, master.sys.all_columns ac2; DECLARE @abc NVARCHAR(MAX) = N'ABC'; INSERT INTO tmax SELECT TOP 1000000 @abc FROM master.sys.all_columns ac1, master.sys.all_columns ac2; SET STATISTICS TIME ON; SET STATISTICS IO ON; SELECT * FROM dbo.t4000; SELECT * FROM dbo.tmax; 

Le principal inconvénient que je peux voir est que disons que vous avez ceci:

Lequel vous donne le plus d'informations sur datatables nécessaires pour l'interface user?

Ce

  CREATE TABLE [dbo].[BusData]( [ID] [int] IDENTITY(1,1) NOT NULL, [RecordId] [nvarchar](MAX) NULL, [CompanyName] [nvarchar](MAX) NOT NULL, [FirstName] [nvarchar](MAX) NOT NULL, [LastName] [nvarchar](MAX) NOT NULL, [ADDRESS] [nvarchar](MAX) NOT NULL, [CITY] [nvarchar](MAX) NOT NULL, [County] [nvarchar](MAX) NOT NULL, [STATE] [nvarchar](MAX) NOT NULL, [ZIP] [nvarchar](MAX) NOT NULL, [PHONE] [nvarchar](MAX) NOT NULL, [COUNTRY] [nvarchar](MAX) NOT NULL, [NPA] [nvarchar](MAX) NULL, [NXX] [nvarchar](MAX) NULL, [XXXX] [nvarchar](MAX) NULL, [CurrentRecord] [nvarchar](MAX) NULL, [TotalCount] [nvarchar](MAX) NULL, [Status] [int] NOT NULL, [ChangeDate] [datetime] NOT NULL ) ON [PRIMARY] 

Ou ca?

  CREATE TABLE [dbo].[BusData]( [ID] [int] IDENTITY(1,1) NOT NULL, [RecordId] [nvarchar](50) NULL, [CompanyName] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [ADDRESS] [nvarchar](50) NOT NULL, [CITY] [nvarchar](50) NOT NULL, [County] [nvarchar](50) NOT NULL, [STATE] [nvarchar](2) NOT NULL, [ZIP] [nvarchar](16) NOT NULL, [PHONE] [nvarchar](18) NOT NULL, [COUNTRY] [nvarchar](50) NOT NULL, [NPA] [nvarchar](3) NULL, [NXX] [nvarchar](3) NULL, [XXXX] [nvarchar](4) NULL, [CurrentRecord] [nvarchar](50) NULL, [TotalCount] [nvarchar](50) NULL, [Status] [int] NOT NULL, [ChangeDate] [datetime] NOT NULL ) ON [PRIMARY] 

Un inconvénient est que vous allez concevoir autour d'une variable imprévisible, et vous allez probablement ignorer au lieu de tirer parti de la structure de données SQL Server interne, composée progressivement de Row (s), Page (s) et Extent (s).

Ce qui me fait penser à l'alignment de la structure de données en C, et que le fait d'être conscient de l'alignment est généralement considéré comme une bonne chose. Idée similaire, context différent.

MSDN page pour Pages et Extensions

Page MSDN pour datatables de dépassement de ligne

Cela entraînera un problème de performance, bien que cela ne puisse jamais causer de problèmes réels si votre database est petite. Chaque logging occupera plus d'espace sur le disque dur et la database devra lire plus de secteurs du disque si vous searchz beaucoup d'loggings à la fois. Par exemple, un petit logging pourrait correspondre à 50 à un secteur et un grand logging pourrait en contenir 5. Vous aurez besoin de lire 10 fois plus de données à partir du disque en utilisant le grand logging.

Cela rendra la design de l'écran plus difficile car vous ne pourrez plus prédire la largeur de vos controls.