Есть ли недостатки в постоянном использовании nvarchar (MAX)?

Единственная проблема, которую я обнаружил, заключалась в том, что мы разрабатываем наши приложения на SQL Server 2005, и в одном случае мы должны поддерживать SQL Server 2000. Я только что узнал, что SQL Server 2000 не любит параметр MAX для varchar или nvarchar .

Это справедливый вопрос, и он заявил, помимо очевидного ...

К недостаткам можно отнести:

Последствия для производительности Оптимизатор запросов использует размер поля для определения наиболее эффективного плана выполнения.

"1. Распределение пространства в расширении и страницах базы данных является гибким. Таким образом, при добавлении информации в поле с помощью обновления ваша база данных должна будет создать указатель, если новые данные длиннее, чем предыдущие вставленные. Это файлы базы данных будут стать фрагментированным = низкая производительность практически во всем, от индекса до удаления, обновления и вставки ». http://sqlblogcasts.com/blogs/simons/archive/2006/02/28/Why-use-anything-but-varchar_2800_max_2900_.aspx

Последствия интеграции - другим системам трудно понять, как интегрироваться с вашей базой данных Непредсказуемый рост данных Возможные проблемы с безопасностью, например вы можете вывести систему из строя, занимая все дисковое пространство

Здесь есть хорошая статья: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

Тот же вопрос был задан на форумах MSDN:

• Варчар (макс) против Варчара (255)

Из исходного сообщения (там гораздо больше информации):

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

У меня был udf, который дополнял строки и помещал вывод в varchar (max). Если бы это использовалось напрямую, вместо того, чтобы возвращаться к соответствующему размеру регулируемой колонны, производительность была бы очень низкой. Я закончил тем, что поместил udf в произвольную длину с большой примечанием, вместо того, чтобы полагаться на всех вызывающих udf, чтобы повторно преобразовать строку к меньшему размеру.

Это вызовет проблемы с производительностью, хотя может никогда не вызвать реальных проблем, если ваша база данных мала. Каждая запись будет занимать больше места на жестком диске, и базе данных потребуется читать больше секторов диска, если вы просматриваете множество записей одновременно. Например, небольшая запись может уместиться в 50 секторов, а большая запись может уместиться в 5. Вам нужно будет прочитать в 10 раз больше данных с диска, используя большую запись.

Это усложнит дизайн экрана, так как вы больше не сможете предсказать, насколько широкими должны быть элементы управления.

Интересная ссылка: Зачем использовать VARCHAR, если можно использовать ТЕКСТ?

Речь идет о PostgreSQL и MySQL, поэтому анализ производительности отличается, но логика «явности» все еще сохраняется: зачем заставлять себя всегда беспокоиться о чем-то, что актуально в небольшом проценте случаев? Если вы сохранили адрес электронной почты в переменной, вы должны использовать «строку», а не «строку, ограниченную 80 символами».

Одна из проблем заключается в том, что если вам приходится работать с несколькими версиями SQL Server, MAX не всегда будет работать. Поэтому, если вы работаете с устаревшими базами данных или в любой другой ситуации, которая включает несколько версий, вам лучше быть очень осторожными.

Иногда вам нужно, чтобы тип данных придавал некоторый смысл содержащимся в нем данным.

Скажем, например, у вас есть столбец, длина которого не должна превышать, скажем, 20 символов. Если вы определите этот столбец как VARCHAR (MAX), какое-нибудь мошенническое приложение может вставить в него длинную строку, и вы никогда не узнаете об этом или не найдете способ предотвратить это.

В следующий раз, когда ваше приложение будет использовать эту строку, при условии, что длина строки является скромной и разумной для домена, который она представляет, вы получите непредсказуемый и запутанный результат.

Плохая идея, когда вы знаете, что поле будет в заданном диапазоне от 5 до 10 символов, например. Думаю, я бы использовал max только в том случае, если не был уверен, какой будет длина. Например, телефонный номер никогда не может содержать больше определенного количества символов.

Можете ли вы честно сказать, что не уверены в примерных требованиях к длине каждого поля в вашей таблице?

Я понимаю вашу точку зрения - есть некоторые поля, которые я бы определенно рассмотрел с помощью varchar (max).

Интересно, что Документы MSDN довольно хорошо резюмирует:

Use varchar when the sizes of the column data entries vary considerably. Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

Есть интересное обсуждение проблемы здесь.

Думайте об этом как о еще одном уровне безопасности. Вы можете спроектировать свою таблицу без отношений внешнего ключа - что совершенно корректно - и обеспечить существование связанных сущностей полностью на бизнес-уровне. Однако внешние ключи считаются хорошей практикой проектирования, потому что они добавляют еще один уровень ограничений на случай, если что-то не так на бизнес-уровне. То же самое касается ограничения размера поля и без использования varchar MAX.

поддержка устаревших систем. Если у вас есть система, которая использует данные, и ожидается, что она будет определенной длины, тогда база данных - хорошее место для обеспечения длины. Это не идеально, но устаревшие системы иногда не идеальны. = P

Если все данные в строке (для всех столбцов) никогда не будут разумно занимать 8000 или меньше символов, тогда дизайн на уровне данных должен обеспечить это.

Ядро базы данных намного эффективнее, позволяя хранить все вне хранилища BLOB-объектов. Чем меньше вы можете ограничить строку, тем лучше. Чем больше строк вы сможете втиснуть на страницу, тем лучше. База данных просто работает лучше, когда ей требуется доступ к меньшему количеству страниц.

Причина, по которой НЕ следует использовать поля max или text, заключается в том, что вы не можете выполнить перестройка индекса в Интернете, т.е. REBUILD WITH ONLINE = ON даже с SQL Server Enterprise Edition.

Задача базы данных - хранить данные, чтобы их могло использовать предприятие. Частью того, чтобы сделать эти данные полезными, является их значимость. Разрешение кому-либо вводить неограниченное количество символов для своего имени не обеспечивает значимых данных.

Встраивание этих ограничений в бизнес-уровень - хорошая идея, но это не гарантирует, что база данных останется нетронутой. Единственный способ гарантировать, что правила данных не нарушаются, - это обеспечить их соблюдение на самом низком уровне базы данных.

Главный недостаток, который я вижу, заключается в том, что, допустим, у вас есть это:

Какой из них дает вам больше всего информации о данных, необходимых для пользовательского интерфейса?

Этот

            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]

Или это?

            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]

Одним из недостатков является то, что вы будете проектировать вокруг непредсказуемой переменной и, вероятно, проигнорируете вместо того, чтобы использовать преимущества внутренней структуры данных SQL Server, которая постепенно состоит из строк, страниц и экстентов.

Это заставляет меня думать о выравнивание структуры данных в C и о том, что осведомленность о выравнивании обычно считается хорошей вещью (TM). Похожая идея, другой контекст.

Страница MSDN для Страницы и экстенты

Страница MSDN для Данные о переполнении строк

1) SQL-серверу придется использовать больше ресурсов (выделенная память и время процессора) при работе с nvarchar (max) vs nvarchar (n), где n - это число, специфичное для поля.

2) Что это означает в отношении производительности?

В SQL Server 2005 я запросил 13 000 строк данных из таблицы с 15 столбцами nvarchar (max). Я повторно рассчитал время запросов, а затем изменил столбцы на nvarchar (255) или меньше.

Запросы до оптимизации составляли в среднем 2,0858 секунды. Запросы после изменения возвращались в среднем за 1,90 секунды. Это было примерно на 184 миллисекунды улучшения по сравнению с базовым запросом select *. Это улучшение на 8,8%.

3) Мои результаты совпадают с результатами нескольких других статей, в которых указывалось, что была разница в производительности. В зависимости от вашей базы данных и запроса процент улучшения может варьироваться. Если у вас не так много одновременных пользователей или очень много записей, разница в производительности не будет для вас проблемой. Однако разница в производительности будет увеличиваться по мере увеличения количества записей и одновременных пользователей.

Я проверил несколько статей и нашел здесь полезный тестовый скрипт: http://www.sqlservercentral.com/Forums/Topic1480639-1292-1.aspx Затем изменил его, чтобы сравнить между NVARCHAR (10) против NVARCHAR (4000) против NVARCHAR (MAX), и я не нахожу разницы в скорости при использовании указанных чисел, но при использовании MAX. Вы можете протестировать самостоятельно. Надеюсь, это поможет.

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

Судя по ссылке, указанной в принятом ответе, кажется, что:

1. 100 символов, хранящихся в поле nvarchar(MAX), будут сохранены так же, как 100 символов в поле nvarchar(100) - данные будут храниться внутри, и у вас не будет накладных расходов на чтение и запись данных «вне строки». Так что не беспокойтесь.

2. Если размер больше 4000, данные будут автоматически сохраняться вне строки, что вам и нужно. Так что здесь тоже не о чем беспокоиться.

Тем не мение...

3. Вы не можете создать индекс для столбца nvarchar(MAX). Вы можете использовать полнотекстовое индексирование, но вы не можете создать индекс для столбца для повышения производительности запроса. Для меня это закрепляет сделку ... всегда использовать nvarchar (MAX) - явный недостаток.

Заключение:

Если вам нужна «универсальная длина строки» для всей базы данных, которую можно индексировать и которая не будет тратить впустую пространство и время доступа, тогда вы можете использовать nvarchar(4000).

Как было указано выше, это прежде всего компромисс между объемом памяти и производительностью. По крайней мере, в большинстве случаев.

Однако есть по крайней мере еще один фактор, который следует учитывать при выборе n / varchar (Max) вместо n / varchar (n). Будут ли данные индексироваться (например, фамилия)? Поскольку определение MAX считается LOB, то все, что определено как MAX, недоступно для индексации. а без индекса любой поиск, включающий данные в качестве предиката в предложении WHERE, будет вынужден выполнять сканирование полной таблицы, что является худшей производительностью, которую вы можете получить при поиске данных.

Мои тесты показали, что при выборе есть отличия.

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;

сначала я подумал об этом, но потом подумал еще раз. Это влияет на производительность, но в равной степени это служит формой документации, чтобы иметь представление о том, какого размера поля на самом деле. И он применяется, когда эта база данных находится в более крупной экосистеме. На мой взгляд, главное - быть снисходительным, но только в пределах разумного.

Хорошо, вот мои чувства просто по вопросу о логике бизнес-уровня и уровня данных. Это зависит от того, является ли ваша БД общим ресурсом между системами, которые разделяют бизнес-логику, тогда, конечно, кажется естественным местом для обеспечения такой логики, но это не ЛУЧШИЙ способ сделать это, ЛУЧШИЙ способ - предоставить API, это позволяет взаимодействие, которое необходимо протестировать, и сохраняет бизнес-логику на своем месте, сохраняет развязку систем, поддерживает развязку уровней внутри системы. Если, однако, ваша база данных должна обслуживать только одно приложение, тогда давайте подумаем AGILE, что теперь правда? дизайн на данный момент. Если и когда такой доступ необходим, предоставьте API для этих данных.

однако очевидно, что это просто идеальный вариант: если вы работаете с существующей системой, скорее всего, вам нужно будет сделать это по-другому, по крайней мере, в краткосрочной перспективе.

Начиная с SQL Server 2019, NVARCHAR (МАКС.) по-прежнему не поддерживает SCSU «Сжатие Unicode» - даже при хранении с использованием встроенного хранилища данных. SCSU был добавлен в SQL Server 2008 и применяется к любым таблицам и индексам, сжатым ROW / PAGE.

Таким образом, NVARCHAR (MAX) может принимать до двух раз больше физического дискового пространства как поле NVARCHAR (1..4000) с тем же текстовым содержимым⁺ - даже когда нет хранится в LOB. Отходы, не относящиеся к SCSU, зависят от представленных данных и языка.

Реализация сжатия Unicode:

SQL Server uses an implementation of the Standard Compression Scheme for Unicode (SCSU) algorithm to compress Unicode values that are stored in row or page compressed objects. For these compressed objects, Unicode compression is automatic for nchar(n) and nvarchar(n) columns [and is never used with nvarchar(max)].

С другой стороны, сжатие PAGE (с 2014 года) по-прежнему применяется к столбцам NVARCHAR (MAX) если, они записываются как данные In-Row ... поэтому отсутствие SCSU ощущается как «отсутствие оптимизации». В отличие от SCSU, результаты сжатия страницы могут сильно различаться в зависимости от общих начальных префиксов (т. Е. Повторяющихся значений).

Однако использование NVARCHAR (MAX) по-прежнему может быть «быстрее» даже при более высоких затратах ввода-вывода с такими функциями, как OPENJSON, из-за исключения неявного преобразования. Эти накладные расходы на неявное преобразование зависят от относительной стоимости использования и от того, касаются ли поля до или после фильтрации. Та же проблема преобразования существует при использовании параметров сортировки UTF-8 2019 в столбце VARCHAR (MAX).

Использование NVARCHAR (1-4000) также требует N * 2 байтов из ~ 8000 байтовой квоты строки, в то время как NVARCHAR (MAX) требует только 24 байта. Общий дизайн и использование необходимо рассматривать вместе, чтобы учесть конкретные детали реализации.

⁺ В моей базе данных / данных / схеме, используя два столбца (объединенные при чтении), можно было сократить использование дискового пространства примерно на 40%, при этом все еще поддерживая переполнение текстовых значений. SCSU, несмотря на свои недостатки, является удивительно умным и малоиспользуемым методом хранения Unicode с более эффективным использованием пространства.