Работа над максимальными столбцами SQL Server ограничивает размер записи 1024 и 8kb

Ответ 1

Максимальный предел столбцов SQL Server

Байт на короткий столбец строки 8000

Bytes per GROUP BY, ORDER BY 8,060

Байт на строку 8,060

Столбцы на индексный ключ 16

Столбцы для внешнего ключа 16

Столбцы на первичный ключ 16

Столбцы для одной таблицы 1,024

Столбцы на ширину таблицы 30 000

Столбцы в инструкции SELECT 4,096

Столбцы на инструкцию INSERT 4096

Столбцы в инструкции UPDATE (широкие таблицы) 4096

Когда вы объединяете столбцы varchar, nvarchar, varbinary, sql_variant, или CLR, определяемые пользователем, которые превышают байты 8,060 для каждой строки, рассмотрите следующее:

Превышение предела размера строки в размере 8 060 байт может повлиять на производительность, поскольку SQL Server по-прежнему поддерживает ограничение в 8 Кбайт на страницу. Если комбинация столбцов типа varchar, nvarchar, varbinary, sql_variant или CLR превышает этот предел, SQL Server Database Engine перемещает столбец записи с наибольшей шириной на другую страницу в блоке распределения ROW_OVERFLOW_DATA, сохраняя при этом 24- байт на исходной странице. Перемещение больших записей на другую страницу происходит динамически, поскольку записи продлеваются на основе операций обновления. Операции обновления, которые сокращают записи, могут привести к тому, что записи будут возвращены на исходную страницу в блоке выделения IN_ROW_DATA. Кроме того, запрос и выполнение других операций выбора, таких как сортировки или объединения в больших записях, содержащих данные переполнения строк, замедляет время обработки, поскольку эти записи обрабатываются синхронно, а не асинхронно.

Следовательно, при создании таблицы с несколькими столбцами varchinary, nvarchar, varbinary, sql_variant или CLR, определенными пользователем, рассмотрите процент строк, которые могут перетекать, и частота, с которой данные переполнения могут быть запрошенным. Если во многих строках данных переполнения строк могут возникать частые запросы, рассмотрите возможность нормализации таблицы, чтобы некоторые столбцы были перемещены в другую таблицу. Затем это может быть запрошено в асинхронной операции JOIN.

• Длина отдельных столбцов должна по-прежнему находиться в пределах 8 000 байт для varchar, nvarchar, varbinary, sql_variant и CLR определяемые пользователем столбцы типов. Только их общая длина может превышать 8 060-байтовый предел строки таблицы.
• Сумма столбцов других типов данных, включая char и nchar данные, должен находиться в пределах лимита в 8 060 байт. Большие данные объекта также освобождаются от ограничения в размере 8 060 байт.
• Ключ индекса кластерного индекса не может содержать столбцы varchar которые имеют существующие данные в блоке распределения ROW_OVERFLOW_DATA. Если кластерный индекс создается в столбце varchar и существующий данные находятся в блоке выделения IN_ROW_DATA, последующие вставки или действия обновления в столбце, которые будут вытеснять данные вне строки, будут потерпеть неудачу. Дополнительные сведения о единицах распределения см. В таблице и Организация индексов.
• Вы можете включать столбцы, содержащие данные переполнения строк в качестве ключа или unkey столбцы некластеризованного индекса.
• Предел размера записи для таблиц, использующих разреженные столбцы, составляет 8018 байт. Когда преобразованные данные плюс существующие данные записи превышают 8018 байт, возвращается сообщение MSSQLSERVER ERROR 576. Когда столбцы преобразованный между разреженными и несравненными типами, Database Engine поддерживает копия данных текущей записи. Это временно удваивает память что требуется для записи..
• Чтобы получить информацию о таблицах или индексах, которые могут содержать данных переполнения строк, используйте динамику sys.dm_db_index_physical_stats функции управления.

Создание таблицы с n числом столбцов и типом данных Nvarchar

CREATE Proc [dbo].[CreateMaxColTable_Nvarchar500]
(@TableName nvarchar(100),@NumofCols int)
AS
BEGIN

DECLARE @i INT
DECLARE @MAX INT
DECLARE @SQL VARCHAR(MAX)
DECLARE @j VARCHAR(10)
DECLARE @len int
SELECT @i=1
SELECT @[email protected]
SET @SQL='CREATE TABLE ' + @TableName + '('

WHILE @i<[email protected]

BEGIN
select @j= cast(@i as varchar)
SELECT @SQL= @SQL+'X'[email protected]  +' NVARCHAR(500) , '
SET @i = @i + 1
END
select @len=len(@SQL)

select  @SQL = substring(@SQL,0,@len-1)


SELECT @SQL= @SQL+ ' )'

exec (@SQL)

END

Для получения дополнительной информации вы можете посетить эти ссылки:

http://msdn.microsoft.com/en-us/library/ms186981%28SQL.105%29.aspx?PHPSESSID=tn8k5p1s508cop8gr43e1f34d2

http://technet.microsoft.com/en-us/library/ms143432.aspx

Но, пожалуйста, расскажите, почему вам нужен стол с таким количеством столбцов? Я думаю, вам стоит подумать о перепроектировании базы данных.

Ответ 2

Это просто невозможно. См. Внутри механизма хранения: Анатомия записи

Предположим, что ваша таблица похожа на это.

CREATE TABLE T1(
    col_1 varchar(8000) NULL,
    col_2 varchar(8000) NULL,
    /*....*/
    col_999 varchar(8000) NULL,
    col_1000 varchar(8000) NULL
) 

Тогда даже строка со всеми значениями NULL будет использовать следующее хранилище.

• 1 бит состояния бит A
• 1 бит состояния бит B
• 2 байта смещения столбца
• 125 байт NULL_BITMAP (1 bit за столбец для 1000 столбцов)

Итак, это уже гарантированный 129 байт, уже использованный (оставляя 7,931).

Если какой-либо из столбцов имеет значение, которое не является NULL или пустой строкой, вам также понадобится место для

• Число байтов с переменной длиной в 2 байта (оставляя 7,929).
• Где угодно - от 2 до 2000 байт для массива смещения столбца.
• Сами данные.

Массив смещения столбца потребляет 2 байта на столбец переменной длины кроме, если этот столбец и все последующие столбцы также равны нулю. Поэтому обновление col_1000 заставит использовать все 2000 байтов, тогда как обновление col_1 будет просто использовать 2 байта.

Таким образом, вы можете заполнить каждый столбец 5 байтами данных и при учете 2 байтов в массиве смещения столбцов, который добавит до 7000 байт, который останется в оставшемся 7,929.

Однако хранящиеся вами данные составляют 102 байта (51 nvarchar символов), поэтому это может быть сохранено за пределами строки с 24-байтовым указателем на фактические данные, оставшиеся в строке.

FLOOR(7929/(24 + 2)) = 304

Таким образом, лучшим случаем было бы то, что вы могли бы хранить 304 столбца данных этой длины, а именно, если вы обновляете от col_1, col_2, .... Если col_1000 содержит данные, то вычисление

FLOOR(5929/24) = 247

Для NTEXT расчет аналогичен, кроме он может использовать 16-байтовый указатель, который позволит вам сжать данные в несколько дополнительных столбцов

FLOOR(7929/(16 + 2)) = 440

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

Script для проверки этого

DROP TABLE T1

/* Create table with 1000 columns*/
DECLARE @CreateTableScript nvarchar(max) = 'CREATE TABLE T1('

SELECT @CreateTableScript += 'col_' + LTRIM(number) + ' VARCHAR(8000),'
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND 1000
ORDER BY number

SELECT @CreateTableScript += ')'

EXEC(@CreateTableScript)

/* Insert single row with all NULL*/
INSERT INTO T1 DEFAULT VALUES


/*Updating first 304 cols succeed. Change to 305 and it fails*/
DECLARE @UpdateTableScript nvarchar(max) = 'UPDATE T1 SET  '

SELECT @UpdateTableScript += 'col_' + LTRIM(number) + ' = REPLICATE(1,1000),'
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND 304
ORDER BY number

SET @UpdateTableScript = LEFT(@UpdateTableScript,LEN(@UpdateTableScript)-1)
EXEC(@UpdateTableScript)

Ответ 3

Наличие таблицы с 1.000 столбцами говорит вам, что в дизайне базы данных есть что-то очень плохое.

Я унаследовал проект, в котором одна из таблиц имела более 500 столбцов, и после более чем одного года я все еще не могу значительно уменьшить его, потому что мне придется переработать 90% приложения.

Итак, переработайте свою БД, пока не стало слишком поздно.

Ответ 4

Таблица Макс. столбцов за "не всюду": 1,024 Таблица Max Columns per 'wide: 30 000

Хотя то, что в точности соответствует этому числу для одной таблицы? Настоятельно рекомендуется несколько раз разбивать таблицу по вертикали, чтобы повысить производительность и упростить разработку.

Ответ 5

Создание таблицы с n числом столбцов и типом данных Nvarchar

CREATE Proc [dbo].[CreateMaxColTable_Nvarchar500]
(@TableName nvarchar(100),@NumofCols int)
AS
BEGIN

DECLARE @i INT
DECLARE @MAX INT
DECLARE @SQL VARCHAR(MAX)
DECLARE @j VARCHAR(10)
DECLARE @len int
SELECT @i=1
SELECT @[email protected]
SET @SQL='CREATE TABLE ' + @TableName + '('

WHILE @i<[email protected]

BEGIN
select @j= cast(@i as varchar)
SELECT @SQL= @SQL+'A'[email protected]  +' NVARCHAR(500) , '
SET @i = @i + 1
END
select @len=len(@SQL)

select  @SQL = substring(@SQL,0,@len-1)


SELECT @SQL= @SQL+ ' )'

exec (@SQL)

END

Ответ 6

Ограничения для каждой строки на сервере SQL.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

содержит подробные сведения

Ответ 7

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

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