Gibt es irgendwelche Nachteile, um immer nvarchar (MAX) zu benutzen?

In SQL server 2005 gibt es irgendwelche Nachteile, alle characterfelder nvarchar (MAX) zu machen, anstatt eine Länge explizit anzugeben, zB nvarchar (255)? (Abgesehen von der offensichtlichen, dass Sie nicht in der Lage sind, die Feldlänge auf databaseebene zu begrenzen)

Die gleiche Frage wurde auf MSDN Foren gefragt:

  • Varchar (max) gegen Varchar (255)

Von der ursprünglichen Post (viel mehr Informationen dort):

Wenn Sie data in eine VARCHAR (N) -Spalte speichern, werden die Werte physisch auf die gleiche Weise gespeichert. Aber wenn Sie es in einer VARCHAR (MAX) Spalte speichern, hinter dem imageschirm werden die data als TEXT Wert behandelt. So gibt es eine zusätzliche Verarbeitung, die beim Umgang mit einem VARCHAR (MAX) Wert benötigt wird. (nur wenn die Größe 8000 überschreitet)

VARCHAR (MAX) oder NVARCHAR (MAX) gilt als "großer Werttyp". Große Werttypen werden in der Regel 'out of row' gespeichert. Es bedeutet, dass die datazeile einen pointers auf einen anderen Ort hat, an dem der 'große Wert' gespeichert ist …

Es ist eine faire Frage und er hat abgesehen von der offensichtlichen …

Nachteile könnten sein:

Performance-Implikationen Query-Optimierer verwendet Feldgröße, um den effizientesting Exectution-Plan zu bestimmen

"1. Die Raumallokation in Erweiterungen und Seiten der database sind flexibel.Wenn beim Hinzufügen von Informationen zum Feld mit Update, würde Ihre database einen pointers erstellen müssen, wenn die neuen data länger als die vorherigen eingefügt sind werden zersplittert = niedrigere performance in fast allem, von Index zu löschen, zu aktualisieren und zu fügen. " http://sqlblogcasts.com/blogs/simons/archive/2006/02/28/Why-use-anything-but-varchar_2800_max_2900_.aspx

Integrationsimplikationen – hart für andere Systeme, um zu wissen, wie man mit Ihrer database integrieren kann Unvorhersehbares datawachstum Mögliche securitysprobleme zB können Sie ein System abstürzen, indem Sie den gesamten memoryplatz aufnehmen

Es gibt einen guten Artikel hier: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

Manchmal möchten Sie, dass der datatyp einen Sinn für die darin enthaltenen data erzwingt.

Sagen Sie zum Beispiel haben Sie eine Spalte, die wirklich nicht länger sein sollte als, sagen wir, 20 character. Wenn du diese Spalte als VARCHAR (MAX) definierst, könnte manche Schurkenanwendung eine lange String insert und du würdest es nie wissen oder eine Möglichkeit haben, sie zu verhindern.

Das nächste Mal, wenn Ihre Anwendung diese characterfolge verwendet, unter der Annahme, dass die Länge der characterfolge bescheiden und vernünftig für die Domain ist, die sie darstellt, erleben Sie ein unvorhersehbares und verwirrendes Ergebnis.

Ich überprüfte einige Artikel und finde nützliches Test-Skript von diesem: http://www.sqlservercentral.com/Forums/Topic1480639-1292-1.aspx Dann änderte es, um zwischen NVARCHAR (10) vs NVARCHAR (4000) vs NVARCHAR (MAX ) und ich finde keinen Geschwindigkeitsunterschied bei der Verwendung von vorgegebenen Nummern, aber bei der Verwendung von MAX. Sie können selbst testing. Ich hoffe das hilft.

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

Denken Sie an es als nur ein anderes securitysniveau. Sie können Ihre Tabelle ohne ausländische Schlüsselbeziehungen entcasting – vollkommen gültig – und die Existenz von assoziierten Einheiten vollständig auf der Business-Schicht zu gewährleisten. Allerdings sind Fremdschlüssel als gute Design-Praxis, weil sie eine weitere Einschränkung Ebene hinzufügen, falls etwas auf der Business-Schicht verwechselt. Gleiches gilt für Feldgrößenbegrenzung und nicht mit varchar MAX.

Ein Grund, NICHT zu verwenden, um Max- oder Textfelder zu verwenden, ist, dass Sie keine Online-Index-Wiederherstellungen durchführen können, dh REBUILD WITH ONLINE = ON auch mit SQL server Enterprise Edition.

Basierend auf dem Link, der in der angenommenen Antwort zur Verfügung steht, scheint es, dass:

  1. 100 character, die in einem nvarchar(MAX) -Feld gespeichert sind, werden in einem nvarchar(100) -Feld nicht unterschiedlich zu 100 character gespeichert – die data werden inline gespeichert und Sie haben keinen Overhead zum Lesen und Schreiben von data 'out of row'. Also keine Sorgen da

  2. Wenn die Größe größer als 4000 ist, werden die data automatisch aus der Zeile gespeichert, was Sie wollen. Also keine Sorgen da auch

Aber…

  1. Sie können keinen Index auf einer nvarchar(MAX) erstellen. Sie können die Volltextindizierung verwenden, aber Sie können keinen Index für die Spalte erstellen, um die Abfrageleistung zu verbessern. Für mich, das versiegelt den Deal … es ist ein definitiver Nachteil, immer nvarchar (MAX) zu verwenden.

Schlussfolgerung:

Wenn du eine Art "universelle Saitenlänge" in deiner ganzen database wünschst, die indiziert werden kann und die keinen Platz und Zugriffszeit verschwendet, dann könntest du nvarchar(4000) benutzen.

Das einzige Problem, das ich gefunden habe, war, dass wir unsere Anwendungen auf SQL server 2005 entwickeln und in einem Fall müssen wir SQL server 2000 unterstützen. Ich habe gerade gelernt, die harte Art, dass SQL server 2000 nicht die MAX-Option für Varchar oder nvarchar

Schlechte Idee, wenn Sie wissen, das Feld wird in einem Set-Bereich-5 bis 10 character zum Beispiel. Ich glaube ich würde nur max verwenden wenn ich nicht sicher war, was die Länge wäre. Zum Beispiel wäre eine Telefonnummer nie mehr als eine bestimmte Anzahl von character.

Kannst du ehrlich sagen, dass du so ungewiss über die ungefähre Längenanforderungen für jedes Feld in deinem Tisch bist?

Ich komme doch mal dran – es gibt einige Felder, die ich sicherlich mit varchar (max) betrachten würde.

Interessanterweise sind die MSDN-Dokumente zusammengefasst:

Verwenden Sie varchar, wenn die Größen der Spaltendateneinträge erheblich variieren. Verwenden Sie varchar (max), wenn die Größen der Spaltendateneinträge erheblich variieren und die Größe 8.000 Bytes überschreiten könnte.

Hier ist eine interessante Diskussion über das Thema .

Der Job der database ist es, data zu speichern, damit sie vom Unternehmen genutzt werden können. Ein Teil der Tatsache, dass die data nützlich ist, ist sicherzustellen, dass es sinnvoll ist. Erlauben Sie jemandem, eine unbegrenzte Anzahl von character für ihren Vornamen einzugeben, ist nicht sicherzustellen, sinnvolle data.

Das Erstellen dieser Einschränkungen in die Business-Schicht ist eine gute Idee, aber das stellt nicht sicher, dass die database intakt bleibt. Der einzige path, um zu garantieren, dass die dataregeln nicht verletzt werden, besteht darin, sie auf der untersten Ebene in der database zu durchsetzen.

Ein Problem ist, dass, wenn Sie mit mehreren Versionen von SQL server arbeiten müssen, wird die MAX nicht immer funktionieren. Also, wenn Sie mit Legacy-DB oder einer anderen Situation arbeiten, die mehrere Versionen beinhaltet, ist es besser, sehr vorsichtig zu sein.

Wie oben erwähnt, ist es in erster Linie ein Kompromiss zwischen Lagerung und performance. Zumindest in den meisten Fällen.

Allerdings gibt es mindestens einen anderen Faktor, der bei der Auswahl von n / varchar (Max) über n / varchar (n) berücksichtigt werden sollte. Werden die data indiziert (wie zB ein Nachname)? Da die MAX-Definition als LOB betrachtet wird, ist für die Indexierung nichts als MAX definiert. und ohne einen Index, wird jede search mit den data als Prädikat in einer WHERE-Klausel in einen vollständigen Tabellen-Scan gezwungen werden, was die schlechteste performance ist, die Sie für data-Lookups bekommen können.

1) Der SQL-server muss beim Umgang mit nvarchar (max) vs nvarchar (n) mehr Ressourcen (zugeteilter memory und CPU-time) nutzen, wobei n eine für das Feld spezifische Zahl ist.

2) Was bedeutet das in Bezug auf die performance?

Auf SQL server 2005 habe ich 13.000 Zeilen von data aus einer Tabelle mit 15 nvarchar (max) Spalten abgefragt. Ich habe die Abfragen wiederholt geplant und dann die Spalten zu nvarchar (255) oder weniger geändert.

Die Abfragen vor der Optimierung betrugen bei 2.0858 Sekunden. Die Abfragen nach der Änderung kehrten im Durchschnitt von 1,90 Sekunden zurück. Das war etwa 184 Millisekunden Verbesserung der Grundauswahl * Abfrage. Das ist eine 8,8% ige Verbesserung.

3) Meine Ergebnisse sind in Übereinstimmung mit ein paar anderen Artikeln, die darauf hindeuteten, dass es einen performancesunterschied gab. Abhängig von Ihrer database und der Abfrage kann der Prozentsatz der Verbesserung variieren. Wenn Sie nicht viele gleichzeitige Benutzer oder sehr viele datasätze haben, dann ist der performancesunterschied kein Problem für Sie. Allerdings wird die performancesdifferenz zunehmen, wenn mehr datasätze und gleichzeitige Benutzer zunehmen.

Ich hatte einen udf, der die Saiten gepolstert und die Ausgabe an Varchar (max) gelegt hat. Wenn dies direkt verwendet wurde, anstatt auf die entsprechende Größe für die Säule zurückzusetzen, die angepasst wurde, war die performance sehr schlecht. Ich habe das udf auf eine beliebige Länge mit einer großen Note gesetzt, anstatt sich auf alle Anrufer des udf zu verlassen, um die Saite auf eine kleinere Größe zu casting.

Interessanter Link: Warum VARCHAR verwenden, wenn du TEXT verwenden kannst?

Es geht um PostgreSQL und MySQL, so dass die Performance-Analyse ist anders, aber die Logik für "explizit" noch hält: Warum zwingen Sie sich immer Sorgen um etwas, das relevant ist ein kleiner Prozentsatz der time? Wenn du eine E-Mail-Adresse in einer Variable gespeichert hast, würdest du einen 'String' nicht einen 'String verwenden, der auf 80 character beschränkt ist.

Legacy-Systemunterstützung. Wenn Sie ein System haben, das die data verwendet und es wird erwartet, dass es eine bestimmte Länge ist, dann ist die database ein guter Ort, um die Länge zu erzwingen. Das ist nicht ideal, aber Legacy-Systeme sind irgendwann nicht ideal. = P

Wenn alle data in einer Zeile (für alle Spalten) niemals vernünftigerweise 8000 oder weniger character nehmen würden, dann sollte das Design auf der dataebene dies erzwingen.

Die database-Engine ist viel effizienter halten alles aus Blob-memory. Je kleiner man eine Reihe beschränken kann, desto besser. Je mehr Zeilen Sie in einer Seite besser machen können, desto besser. Die database funktioniert nur besser, wenn sie auf weniger Seiten zugreifen muss.

Der Hauptnachteil, den ich sehen kann, ist, dass wir sagen, dass Sie das haben:

Welches gibt Ihnen die meisten Informationen über die data, die für die Benutzeroberfläche benötigt werden?

Dies

  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] 

Oder dieses?

  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] 

Ein Nachteil ist, dass Sie eine unvorhersehbare Variable entcasting werden, und Sie werden wahrscheinlich ignorieren, anstatt die interne SQL server-datastruktur zu nutzen, die sich schrittweise aus Row (s), Page (s) und Extent (s) zusammensetzt.

Das lässt mich über die datastrukturausrichtung in C nachdenken, und das, was sich der Ausrichtung bewusst ist, gilt allgemein als Good Thing (TM). Ähnliche Idee, unterschiedlicher Kontext.

MSDN Seite für Seiten und Extents

MSDN-Seite für Row-Overflow-data

Meine Tests haben gezeigt, dass es bei der Auswahl Unterschiede gibt.

 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; 

Dies führt zu einem performancesproblem, obwohl es niemals irgendwelche tatsächlichen Probleme verursachen kann, wenn Ihre database klein ist. Jeder datasatz nimmt mehr Platz auf der Festplatte und die database muss mehr Sektoren der Festplatte lesen, wenn Sie durch eine Menge von datasätzen auf einmal suchen. Zum Beispiel könnte ein kleiner Rekord 50 zu einem Sektor passen und ein großer Rekord könnte passen 5. Sie müssten 10 mal so viel data von der Festplatte mit dem großen datasatz lesen.

Es wird imageschirm-Design härter, wie Sie nicht mehr in der Lage sein zu prognostizieren, wie breit Ihre Kontrollen sein sollte.