Ms sql column add: SQL Server ALTER TABLE ADD Column
изменить таблицу — Как добавить столбец в TSQL после определенного столбца?
Это абсолютно возможно. Хотя вы не должны этого делать, если не знаете, с чем имеете дело.
Мне понадобилось около 2 дней, чтобы понять это.
Вот хранимая процедура, в которую я ввожу:
— имя базы данных
(имя схемы «_» для удобочитаемости)
— имя таблицы
—столбец
— тип данных столбца
(добавляемый столбец всегда нулевой, иначе вы не сможете вставить)
— положение нового столбца.
Поскольку я работаю с таблицами из набора инструментов SAM (а некоторые из них имеют > 80 столбцов), типичная переменная не сможет содержать запрос. Это заставляет потребность во внешнем файле. Теперь будьте осторожны, где вы храните этот файл и у кого есть доступ на уровне NTFS и сети.
Ура!
ИСПОЛЬЗОВАНИЕ [мастер] ИДТИ /****** Объект: StoredProcedure [SP_Set].[TrasferDataAtColumnLevel] Дата сценария: 27.08.2014 14:59:30 ******/ УСТАНОВИТЕ ANSI_NULLS ВКЛ. ИДТИ УСТАНОВИТЕ QUOTED_IDENTIFIER НА ИДТИ СОЗДАЙТЕ ПРОЦЕДУРУ [SP_Set].[TrasferDataAtColumnLevel] ( @база данных varchar(100), @таблица varchar(100), @столбец varchar(100), @позиция внутри, @тип данных varchar(20) ) КАК НАЧИНАТЬ установить нулевой счет на исполнить (' объявить @oldC varchar(200), @oldCDataType varchar(200), @oldCLen int, @oldCPos int создать таблицу Test (dummy int) объявить @columns varchar(max) = '''' объявить @columnVars varchar(max) = '''' объявить @columnsDecl varchar(max) = '''' объявить @printVars varchar(max) = '''' DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR выберите column_name, data_type, character_maximum_length, ORDINAL_POSITION из ' + @database + '.INFORMATION_SCHEMA.COLUMNS, где table_name = ''' + @table + ''' OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @oldC, @oldCDataType, @oldCLen, @oldCPos, ПОКА @@FETCH_STATUS = 0 BEGIN если(@oldCPos = ' + @position + ') начинать exec(''изменить таблицу Test add [' + @column + '] ' + @datatype + ' null'') конец if(@oldCDataType != ''отметка времени'') начинать установить @columns += @oldC + '', '' установить @columnVars += ''@'' + @oldC + '', '' если(@oldCLen равно нулю) начинать if(@oldCDataType != ''уникальный идентификатор'') начинать set @printVars += '' print convert('' + @oldCDataType + '',@'' + @oldC + '')'' установить @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + '', '' exec(''изменить таблицу Test add ['' + @oldC + ''] '' + @oldCDataType + '' null'') конец еще начинать set @printVars += '' print convert(varchar(50),@'' + @oldC + '')'' установить @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + '', '' exec(''изменить таблицу Test add ['' + @oldC + ''] '' + @oldCDataType + '' null'') конец конец еще начинать если(@oldCLen < 0) начинать установить @oldCLen = 4000 конец установить @printVars += '' печать @'' + @oldC set @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + ''('' + convert(character,@oldCLen) + ''), '' exec(''изменить таблицу Test add ['' + @oldC + ''] '' + @oldCDataType + ''('' + @oldCLen + '') null'') конец конец если существует (выберите имя_столбца из INFORMATION_SCHEMA.
[TrasferDataAtColumnLevel]
(
@база данных varchar(100),
@таблица varchar(100),
@столбец varchar(100),
@позиция внутри,
@тип данных varchar(20)
)
КАК
НАЧИНАТЬ
установить нулевой счет на
исполнить ('
объявить @oldC varchar(200), @oldCDataType varchar(200), @oldCLen int, @oldCPos int
создать таблицу Test (dummy int)
объявить @columns varchar(max) = ''''
объявить @columnVars varchar(max) = ''''
объявить @columnsDecl varchar(max) = ''''
объявить @printVars varchar(max) = ''''
DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
выберите column_name, data_type, character_maximum_length, ORDINAL_POSITION из ' + @database + '.INFORMATION_SCHEMA.COLUMNS, где table_name = ''' + @table + '''
OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO @oldC, @oldCDataType, @oldCLen, @oldCPos, ПОКА @@FETCH_STATUS = 0 BEGIN
если(@oldCPos = ' + @position + ')
начинать
exec(''изменить таблицу Test add [' + @column + '] ' + @datatype + ' null'')
конец
if(@oldCDataType != ''отметка времени'')
начинать
установить @columns += @oldC + '', ''
установить @columnVars += ''@'' + @oldC + '', ''
если(@oldCLen равно нулю)
начинать
if(@oldCDataType != ''уникальный идентификатор'')
начинать
set @printVars += '' print convert('' + @oldCDataType + '',@'' + @oldC + '')''
установить @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + '', ''
exec(''изменить таблицу Test add ['' + @oldC + ''] '' + @oldCDataType + '' null'')
конец
еще
начинать
set @printVars += '' print convert(varchar(50),@'' + @oldC + '')''
установить @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + '', ''
exec(''изменить таблицу Test add ['' + @oldC + ''] '' + @oldCDataType + '' null'')
конец
конец
еще
начинать
если(@oldCLen < 0)
начинать
установить @oldCLen = 4000
конец
установить @printVars += '' печать @'' + @oldC
set @columnsDecl += ''@'' + @oldC + '' '' + @oldCDataType + ''('' + convert(character,@oldCLen) + ''), ''
exec(''изменить таблицу Test add ['' + @oldC + ''] '' + @oldCDataType + ''('' + @oldCLen + '') null'')
конец
конец
если существует (выберите имя_столбца из INFORMATION_SCHEMA.
COLUMNS, где имя_таблицы = «Тест» и имя_столбца = «фиктивный»)
начинать
изменить таблицу Тестовый манекен столбца отбрасывания
конец
FETCH NEXT FROM MY_CURSOR INTO @oldC, @oldCDataType, @oldCLen, @oldCPos END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR
set @columns = reverse(substring(reverse(@columns), charindex('','',reverse(@columns)) +1, len(@columns)))
set @columnVars = reverse(substring(reverse(@columnVars), charindex('','',reverse(@columnVars)) +1, len(@columnVars)))
set @columnsDecl = reverse(substring(reverse(@columnsDecl), charindex('','',reverse(@columnsDecl)) +1, len(@columnsDecl)))
set @columns = replace(replace(REPLACE(@columns, '' '', ''''), char(9) + символ (9), '' ''), символ (9), '''')
set @columnVars = replace(replace(REPLACE(@columnVars, '' '', ''''), char(9) + char(9),'' ''), char(9), '''')
set @columnsDecl = replace(replace(REPLACE(@columnsDecl, '' '', ''''), char(9) + char(9),'' ''),char(9), '''')
set @printVars = REVERSE(substring(reverse(@printVars), charindex(''+'',reverse(@printVars))+1, len(@printVars)))
создать табличный запрос (id int identity(1,1), string varchar(max))
вставить в значения запроса (''declare'' + @columnsDecl + ''
DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR '')
вставить в значения запроса (''select'' + @columns + '' from ' + @database + '.
_.' + @table + ''')
вставить в значения запроса (''OPEN MY_CURSOR FETCH NEXT FROM MY_CURSOR INTO '' + @columnVars + '' WHILE @@FETCH_STATUS = 0 BEGIN '')
вставить в значения запроса (@printVars)
вставить в значения запроса ( '' вставить в тест ('')
вставить в значения запроса (@columns)
вставить в значения запроса ( '') значения ( '' + @columnVars + '')'')
вставить в значения запроса (''FETCH NEXT FROM MY_CURSOR INTO '' + @columnVars + '' END CLOSE MY_CURSOR DEALLOCATE MY_CURSOR'')
объявить @path varchar(100) = ''C:\query.sql''
объявить @query varchar(500) = ''bcp "выбрать строку из запроса по id" queryout '' + @path + '' -t, -c -S '' + @@servername + '' -T''
exec master..xp_cmdshell @query
set @query = ''sqlcmd -S'' + @@servername + '' -i '' + @path
EXEC xp_cmdshell @запрос
установить @query = ''del'' + @path
exec xp_cmdshell @запрос
удалить таблицу ' + @database + '._.' + @стол + '
выберите * в ' + @database + '._.' + @table + ' из теста
запрос на удаление таблицы
падение таблицы Тест')
END
sql server — Почему простая команда ALTER TABLE так долго работает с таблицей с полнотекстовым индексом?
У меня есть большая (~67 миллионов строк) таблица «имя-значение» с полнотекстовой индексацией столбца DataValue .
Если я попытаюсь выполнить следующую команду:
ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 NOT NULL;
Он выполняется в течение 1 часа 10 минут и по-прежнему не завершается в таблице VisitorData , которая содержит около 67 миллионов строк.
- Почему это занимает так много времени и не завершается?
- Что я могу с этим поделать?
Подробнее о таблице:
CREATE TABLE [dbo].[VisitorData](
[VisitorID] [int] НЕ NULL,
[имя_данных] [varchar](80) НЕ NULL,
[DataValue] [nvarchar](3800) НЕ NULL,
[EncryptedDataValue] [varbinary](max) NULL,
[VisitorDataID] [int] IDENTITY(1,1) NOT NULL,
ОГРАНИЧЕНИЕ [PK_VisitorData_VisitorDataID] ПЕРВИЧНЫЙ КЛЮЧ КЛАСТЕРИРОВАННЫЙ (
[VisitorDataID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ПЕРВИЧНЫЙ],
CONSTRAINT [UNQ_VisitorData_VisitorId_DataName] UNIQUE NONCLUSTERED (
[ID посетителя] ASC,
[ИмяДанных] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ПЕРВИЧНЫЙ]
) НА [ПЕРВИЧНОМ]
ИДТИ
ИЗМЕНИТЬ ТАБЛИЦУ [dbo]. [VisitorData]
ДОБАВИТЬ ОГРАНИЧЕНИЕ [UNQ_VisitorData_VisitorDataID] UNIQUE NONCLUSTERED (
[VisitorDataID] ASC
)
WITH (PAD_INDEX = ВЫКЛ., STATISTICS_NORECOMPUTE = ВЫКЛ., SORT_IN_TEMPDB = ВЫКЛ.,
IGNORE_DUP_KEY = ВЫКЛ, ONLINE = ВЫКЛ, ALLOW_ROW_LOCKS = ВКЛ,
ALLOW_PAGE_LOCKS = ВКЛ) ВКЛ [ПЕРВИЧНЫЙ]
ИДТИ
ИЗМЕНИТЬ ТАБЛИЦУ [dbo].[VisitorData]
С ЧЕКОМ ДОБАВИТЬ
CONSTRAINT [FK_VisitorData_Visitors] ВНЕШНИЙ КЛЮЧ([VisitorID])
ССЫЛКИ [dbo].[Посетители] ([VisitorID])
ИДТИ
ИЗМЕНИТЬ ТАБЛИЦУ [dbo].[VisitorData]
ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_VisitorData_Visitors] GO
СОЗДАТЬ ПОЛНОТЕКСТНЫЙ КАТАЛОГ DBName_VisitorData_Catalog WITH ACCENT_SENSITIVITY = ON
СОЗДАТЬ ПОЛНОТЕКСТОВЫЙ ИНДЕКС ПО VisitorData (язык DataValue 1033)
ИНДЕКС КЛЮЧА UNQ_VisitorData_VisitorDataID
ON DBName_VisitorData_Catalog
С АВТОМАТИЧЕСКИМ ОТСЛЕЖИВАНИЕМ ИЗМЕНЕНИЙ
ИДТИ
[VisitorData]
ДОБАВИТЬ ОГРАНИЧЕНИЕ [UNQ_VisitorData_VisitorDataID] UNIQUE NONCLUSTERED (
[VisitorDataID] ASC
)
WITH (PAD_INDEX = ВЫКЛ., STATISTICS_NORECOMPUTE = ВЫКЛ., SORT_IN_TEMPDB = ВЫКЛ.,
IGNORE_DUP_KEY = ВЫКЛ, ONLINE = ВЫКЛ, ALLOW_ROW_LOCKS = ВКЛ,
ALLOW_PAGE_LOCKS = ВКЛ) ВКЛ [ПЕРВИЧНЫЙ]
ИДТИ
ИЗМЕНИТЬ ТАБЛИЦУ [dbo].[VisitorData]
С ЧЕКОМ ДОБАВИТЬ
CONSTRAINT [FK_VisitorData_Visitors] ВНЕШНИЙ КЛЮЧ([VisitorID])
ССЫЛКИ [dbo].[Посетители] ([VisitorID])
ИДТИ
ИЗМЕНИТЬ ТАБЛИЦУ [dbo].[VisitorData]
ПРОВЕРИТЬ ОГРАНИЧЕНИЕ [FK_VisitorData_Visitors] GO
СОЗДАТЬ ПОЛНОТЕКСТНЫЙ КАТАЛОГ DBName_VisitorData_Catalog WITH ACCENT_SENSITIVITY = ON
СОЗДАТЬ ПОЛНОТЕКСТОВЫЙ ИНДЕКС ПО VisitorData (язык DataValue 1033)
ИНДЕКС КЛЮЧА UNQ_VisitorData_VisitorDataID
ON DBName_VisitorData_Catalog
С АВТОМАТИЧЕСКИМ ОТСЛЕЖИВАНИЕМ ИЗМЕНЕНИЙ
ИДТИ
Типы ожидания, возникающие во время выполнения команды ALTER TABLE , — это LCK_M_SCH_M (модификация схемы), согласно приведенным ниже результатам запроса:
select * from sys.
