Как уменьшить размер таблицы SQL Server, который вырос из-за изменения типа данных

У меня есть таблица на SQL Server 2005 размером около 4 ГБ.

(около 17 миллионов записей)

Я сменил одно из полей типа данных char(30) на char(60) (всего 25 полей, большинство из которых char(10), поэтому объем пространства char составляет около 300)

Это привело к удвоению таблицы (более 9 гб)

Затем я изменил char(60) на varchar(60), а затем выполнил функцию, чтобы вырезать лишние пробелы из данных (чтобы уменьшить среднюю длину данных в поле до примерно 15)

Это не уменьшило размер таблицы. Сокращение базы данных тоже не помогло.

За исключением фактического воссоздания структуры таблицы и копирования данных (это 17 миллионов записей!) существует ли менее решительный способ вернуть размер обратно?

Ответ 1

Ну, ясно, что ты не возвращаешься!: -)

Когда вы изменили текстовые поля на CHAR (60), все они заполнены до объема пробелами. Таким образом, ВСЕ ваши поля теперь имеют длину 60 символов.

Изменение этого параметра на VARCHAR (60) не поможет - поля все еще имеют длину 60 символов....

Что вам действительно нужно сделать, так это запустить функцию TRIM по всем полям, чтобы уменьшить их до их обрезанной длины, а затем уменьшить базу данных.

После того, как вы это сделали, вам нужно ЗАПРЕЩАЕТ ваш кластерный индекс, чтобы вернуть часть этого потерянного пространства. Кластеризованный индекс действительно там, где живут ваши данные - вы можете его перестроить следующим образом:

ALTER INDEX IndexName ON YourTable REBUILD

По умолчанию ваш первичный ключ - ваш кластеризованный индекс (если вы не указали иное).

Марк

Ответ 2

Вы не очищали и не уплотняли какие-либо данные даже с "сокращенной базой данных".

DBCC CLEANTABLE

Возвращает пространство из столбцов переменной длины в таблицах или индексированных представлениях.

Тем не менее, простая перестройка индекса, если есть кластерный индекс, также должен делать это

ALTER INDEX ALL ON dbo.Mytable REBUILD

Проработанный пример от Тони Роджерсона

Ответ 3

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

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

Ответ 4

У меня была аналогичная проблема здесь SQL Server, конвертирующий NTEXT в NVARCHAR (MAX), который был связан с изменением ntext на nvarchar (max).

Мне нужно было сделать UPDATE MyTable SET MyValue = MyValue, чтобы заставить его красиво изменить все.

Это, очевидно, занимает довольно много времени с большим количеством записей. Было несколько предложений о том, как лучше это делать. Они ключевыми были временным флагом, указанным, если это было сделано или нет, а затем обновлялось несколько тысяч за раз в цикле, пока это не было сделано. Это означало, что у меня был "некоторый" контроль над тем, как он работает.

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

Ответ 5

В качестве альтернативы вы можете выполнить полную перестройку таблицы, чтобы обеспечить отсутствие лишних данных в любом месте:

CREATE TABLE tmp_table(<column definitions>);
GO
INSERT INTO tmp_table(<columns>) SELECT <columns> FROM <table>;
GO
DROP TABLE <table>;
GO
EXEC sp_rename N'tmp_table', N'<table>';
GO

Конечно, все становится сложнее с идентификацией, индексами и т.д. и т.д.