Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Смена типа с int на bigint.  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4257
Пытаюсь исправить ошибки проэктировщиков базы.

С констрейнами просто, что делать со schema binding? Там прорва функций и view чудовишного размера.

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"


Ткните меня в FAQ если я проглядел?
29 авг 18, 20:38    [21658083]     Ответить | Цитировать Сообщить модератору
 Re: Смена типа с int на bigint.  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
Lepsik
С констрейнами просто, что делать со schema binding?
Убивать объекты, менять таблицы, создавать объекты заново.
Либо альтерить объекты без schemabinding, потом обратно.

Можно попробовать грязный хак (лично не пробовал):
  • Перезапустить сервер в single user mode и соедениться через DAC - появится возможность модификации данных в системных таблицах.
  • Информация о привязке к схеме хранится в столбце status (набор флагов) таблицы sys.sysschobjs.
  • В единой транзакции изменить status нужных объектов, альтерить таблицы, восстанавить status.
  • 29 авг 18, 21:23    [21658105]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    msLex
    Member

    Откуда:
    Сообщений: 7730
    Lepsik
    Пытаюсь исправить ошибки проэктировщиков базы.

    С констрейнами просто, что делать со schema binding? Там прорва функций и view чудовишного размера.

    -- disable all constraints
    EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
    
    -- enable all constraints
    exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
    



    Ткните меня в FAQ если я проглядел?


    1.Экспортируете проект в ssdt
    2. Меняете тип поля
    3. Генерите скрипт деплоя.
    29 авг 18, 21:25    [21658108]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    msLex
    Member

    Откуда:
    Сообщений: 7730
    invm
    Lepsik
    С констрейнами просто, что делать со schema binding?
    Убивать объекты, менять таблицы, создавать объекты заново.
    Либо альтерить объекты без schemabinding, потом обратно.

    Можно попробовать грязный хак (лично не пробовал):
  • Перезапустить сервер в single user mode и соедениться через DAC - появится возможность модификации данных в системных таблицах.
  • Информация о привязке к схеме хранится в столбце status (набор флагов) таблицы sys.sysschobjs.
  • В единой транзакции изменить status нужных объектов, альтерить таблицы, восстанавить status.

  • Ага и вьюхи имеют тип int у полей.
    29 авг 18, 21:27    [21658111]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9123
    msLex
    Ага и вьюхи имеют тип int у полей.
    Ничего не мешает перед восстановлением статуса выполнить sp_refreshsqlmodule
    29 авг 18, 21:41    [21658117]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 30800
    invm
    Можно попробовать грязный хак (лично не пробовал):
  • Перезапустить сервер в single user mode и соедениться через DAC - появится возможность модификации данных в системных таблицах.
  • Информация о привязке к схеме хранится в столбце status (набор флагов) таблицы sys.sysschobjs.
  • В единой транзакции изменить status нужных объектов, альтерить таблицы, восстанавить status.
  • ИМХО быстрее пересоздать, чем такой ужас :-) Это же быстро.
    29 авг 18, 22:23    [21658141]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    msLex
    Member

    Откуда:
    Сообщений: 7730
    alexeyvg
    ИМХО быстрее пересоздать, чем такой ужас :-) Это же быстро.

    если есть материализованные представления (агрегаты), может очень сильно затянуться.

    ps хотя в этом случае "грязный хак" в лучшем случае не сработает, а в худшем поломает базу.
    30 авг 18, 11:09    [21658568]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Glebanski
    Member

    Откуда: Msk ->NL
    Сообщений: 306
    При таком раскладе может проще новую базу создать и туда все постепенно перелить?
    30 авг 18, 12:11    [21658668]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    256k
    Member

    Откуда: с.Торчилово, Псковская обл.
    Сообщений: 437
    Какая исходная задача?
    30 авг 18, 12:53    [21658740]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Lepsik
    Member

    Откуда: glubinka
    Сообщений: 4257
    Общее решение такое.

    CREATE TABLE #fvplist(id int not null identity primary key, name sysname);   -- save list of modifyed functions
    GO
    CREATE PROCEDURE ViewRemoveSchemaBinding @ViewName sysname
    AS
    BEGIN
        SET NOCOUNT ON
        DECLARE @PositionShemaBinding INT
        DECLARE @Command NVARCHAR(MAX)
        DECLARE @typeobj varchar(8) = (select type from sys.objects WHERE name=@ViewName)
        DECLARE @nametypeobj  sysname = 
        (
            SELECT CASE WHEN @typeobj = 'P' THEN 'PROCEDURE'
                        WHEN @typeobj = 'V' THEN 'VIEW'
                        WHEN @typeobj IN ('FN', 'IF', 'FT', 'TF') THEN 'FUNCTION' END
        )
        SELECT @Command = OBJECT_DEFINITION(OBJECT_ID(@ViewName));
        SET @PositionShemaBinding = CHARINDEX('WITH SCHEMABINDING', @Command)
    
        IF NOT @PositionShemaBinding = 0 BEGIN
            -- WITH SCHEMA BINDING IS PRESENT... Let's remove it !
            SET @Command = STUFF(@Command, CHARINDEX('WITH SCHEMABINDING', @Command), LEN('WITH SCHEMABINDING'), '/*$$$*/');
            SET @Command = REPLACE(@Command, 'CREATE ' + @nametypeobj, 'ALTER ' + @nametypeobj);
    
            EXECUTE sp_executesql @Command
            INSERT INTO #fvplist (name) VALUES (@ViewName);
        END
    END
    GO
    
    CREATE PROCEDURE ViewAddSchemaBinding @ViewName sysname
    AS
    BEGIN
        SET NOCOUNT ON
        DECLARE @PositionShemaBinding INT
        DECLARE @Command NVARCHAR(MAX)
        DECLARE @ObjectName VARCHAR(MAX)
        DECLARE @typeobj varchar(8) = (select type from sys.objects WHERE name=@ViewName)
        DECLARE @nametypeobj  sysname = 
        (
            SELECT CASE WHEN @typeobj = 'P' THEN 'PROCEDURE'
                        WHEN @typeobj = 'V' THEN 'VIEW'
                        WHEN @typeobj IN ('FN', 'IF', 'FT') THEN 'FUNCTION' END
        )
    
        SELECT  @Command = OBJECT_DEFINITION(OBJECT_ID(@ViewName)),
                @ObjectName = OBJECT_NAME(OBJECT_ID(@ViewName));
    
        SET @PositionShemaBinding = PATINDEX('%WITH SCHEMABINDING%', @Command)
    
        IF @PositionShemaBinding = 0 BEGIN
            -- WITH SCHEMA BINDING IS NOT PRESENT... Let's add it !
            SET @Command = STUFF(@Command, CHARINDEX('/*$$$*/', @Command), LEN('/*$$$*/'), ' WITH SCHEMABINDING ');
            SET @Command = REPLACE(@Command, 'CREATE ' + @nametypeobj, 'ALTER ' + @nametypeobj);
    
            EXECUTE sp_executesql @Command
            DELETE FROM #fvplist WHERE name = @ViewName;
        END
    END
    GO
    PRINT '1. remove schema binding'
    EXEC ViewRemoveSchemaBinding 'fn_group_device_reports_int'
    EXEC ViewRemoveSchemaBinding 'fn_doc_usage_sum_byuserdate'
    EXEC ViewRemoveSchemaBinding 'fn_all_usage_bydate'
    EXEC ViewRemoveSchemaBinding 'doc_detail_col_all'
    
    GO
    PRINT '2. drop relations'
    ALTER TABLE printjob_details DROP CONSTRAINT pk_printjob_details
    GO
    ALTER TABLE printjob_details DROP CONSTRAINT fk_printjob_details
    GO
    ALTER TABLE printjob_details ALTER COLUMN job_id bigint NOT NULL
    GO
    .....
    PRINT '3. restore relations'
    ---------------------------------------------------------------------------------------------
    ALTER TABLE job ADD CONSTRAINT pk_job PRIMARY KEY ( job_id )
    GO
    .....
    PRINT '4. restore schema binding'
    SET NOCOUNT ON
    DECLARE @fvpName sysname
    DECLARE fvpName CURSOR LOCAL FOR SELECT name FROM #fvplist ORDER BY id DESC
    OPEN fvpName
    FETCH NEXT FROM fvpName INTO @fvpName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC ViewAddSchemaBinding @fvpName
        FETCH NEXT FROM fvpName INTO @fvpName
    END
    Close fvpName
    DeAllocate fvpName
    GO
    DROP PROCEDURE ViewAddSchemaBinding;
    GO
    DROP PROCEDURE ViewRemoveSchemaBinding;
    GO
    
    30 авг 18, 19:13    [21659325]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    a_voronin
    Member

    Откуда: Москва
    Сообщений: 4381
    Lepsik,

    У вас там сколько строк?

    Создавайте рядом вторую колонку. Порциями копируйте в неё данные. Потом поменяйте имя с первой.

    Это если 100 млн строк на высоконагруженной базе, которую вы не хотите останавливать.
    31 авг 18, 11:23    [21659874]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    a_voronin
    Lepsik,

    У вас там сколько строк?

    Создавайте рядом вторую колонку. Порциями копируйте в неё данные. Потом поменяйте имя с первой.

    Это если 100 млн строк на высоконагруженной базе, которую вы не хотите останавливать.
    \
    а с identity что делать?
    31 авг 18, 11:25    [21659883]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 36695
    TaPaK
    а с identity что делать?
    На сиквенс заменить.
    31 авг 18, 12:18    [21660047]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Lepsik
    Member

    Откуда: glubinka
    Сообщений: 4257
    a_voronin
    Lepsik,

    У вас там сколько строк?
    Это если 100 млн строк на высоконагруженной базе, которую вы не хотите останавливать.


    Вы всегда делаете upgrade софта не останавливая серверов ?
    Даже на биржах для этого окна находят.

    А так у нас таки да - миллиарды транзакций.
    31 авг 18, 16:18    [21660467]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7399
    Lepsik,

    можно и без остановки, зависит от потребностей.
    31 авг 18, 16:47    [21660533]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    Lepsik,

    автор
    А так у нас таки да - миллиарды транзакций.

    ога, все два млрд, за всю жизнь...
    31 авг 18, 16:48    [21660539]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 30800
    Lepsik
    Вы всегда делаете upgrade софта не останавливая серверов ?
    Даже на биржах для этого окна находят.
    Это же зависит от бизнеса.
    Понятно, что на бирже не будут делать апгрейд без остановки сервиса, но есть бизнесы, для которых перерыв недопустим.
    У меня за много лет работы были разные варианты.
    Понятно, для задачи ТС можно найти кучу решений как с остановкой, так и без остановки, разной степени трудоёмкости и требований к ресурсам, всё таки поле в базе поменять, как говорится, "не бином ньютона"
    31 авг 18, 17:02    [21660554]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Lepsik
    Member

    Откуда: glubinka
    Сообщений: 4257
    TaPaK
    Lepsik,

    автор
    А так у нас таки да - миллиарды транзакций.

    ога, все два млрд, за всю жизнь...


    Amazon, Walmart,... легко
    31 авг 18, 18:29    [21660675]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Гулин Федор
    Member

    Откуда: МИНСК
    Сообщений: 1202
    Lepsik,
    Смешно я занималя обратной задачей в MySQL bigint --> int

    чтобы урезать размер DB
    - на 30% кстати урезал (мог бы и больше - но не дали апрува кое где smallint поставтить)

    было 3 скритпа
    1 Drop FK
    2 Alter Table
    3 Restore FK

    генерил сам - что обнаружилось в MySQL
    В Alter table надо сувать всем меняем столбцы в 1 Alter Table
    был случай когда 8-10 столбцов в одной таблице менялось - соотвесвтенно усокрение в раз 8 было
    а в SQL Server я потом искал - не нашел такого - чьтобы в 1 стейменте можно было так
    извратиться наверно можно Select * into from - в временную таблицу и потом назад - но тут надо думать

    запускалось разово - в окно - часа 2-3 точно работало.
    31 авг 18, 19:36    [21660746]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Glebanski
    Member

    Откуда: Msk ->NL
    Сообщений: 306
    Lepsik,

    Не знаю, что там у вас за сервак, но на нашем апгрейд c datetime2(3) на (7) шёл сутки. В таблице 12 млрд. Длина записи 50 байт примерно.
    Это так, для референса.
    1 сен 18, 23:53    [21661484]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Владимир Затуливетер
    Member

    Откуда:
    Сообщений: 427
    Glebanski
    Lepsik,
    Не знаю, что там у вас за сервак, но на нашем апгрейд c datetime2(3) на (7) шёл сутки. В таблице 12 млрд. Длина записи 50 байт примерно.
    Это так, для референса.

    Видимо вы себе можете позволить сутки простоя...
    Но для других выход есть:
    a_voronin
    Создавайте рядом вторую колонку. Порциями копируйте в неё данные. Потом поменяйте имя с первой.
    2 сен 18, 19:37    [21661883]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Руслан Дамирович
    Member

    Откуда: Резиновая нерезиновая
    Сообщений: 940
    Владимир Затуливетер
    Но для других выход есть:
    a_voronin
    Создавайте рядом вторую колонку. Порциями копируйте в неё данные. Потом поменяйте имя с первой.

    MSы конечно дураки, раз все изменения проводят через временную таблицу, это ж сколько операций - SELECT INTO, DROP, sp_rename, CONSTRAINTS/INDEXES. То ли дело ALTER TABLE DROP COLUMN...
    3 сен 18, 10:56    [21662390]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9123
    Руслан Дамирович
    MSы конечно дураки, раз все изменения проводят через временную таблицу, это ж сколько операций - SELECT INTO, DROP, sp_rename, CONSTRAINTS/INDEXES. То ли дело ALTER TABLE DROP COLUMN...
    А вы в курсе, что ALTER TABLE DROP COLUMN данные не затрагивает?
    А вы посмотрели какой скрипт генерирует студия на удаление столбца?
    3 сен 18, 11:36    [21662437]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Glebanski
    Member

    Откуда: Msk ->NL
    Сообщений: 306
    Владимир Затуливетер
    Видимо вы себе можете позволить сутки простоя...
    Но для других выход есть:
    a_voronin
    Создавайте рядом вторую колонку. Порциями копируйте в неё данные. Потом поменяйте имя с первой.

    :) Ну вот так устроился, в выхи никто в базу не лезет. А входное барахло в очереди подождет.

    ТС, думаю, и так справится. Просто "for completeness" совета, надо бы тогда еще на новую колонку индекс повесить. А то, если порциями копировать, как он узнает, что уже проапдейчено и не NULL? Можно конечно и "на бумажке" записывать или даже мини-фреймворк сделать.
    3 сен 18, 11:54    [21662460]     Ответить | Цитировать Сообщить модератору
     Re: Смена типа с int на bigint.  [new]
    Руслан Дамирович
    Member

    Откуда: Резиновая нерезиновая
    Сообщений: 940
    invm
    А вы в курсе, что ALTER TABLE DROP COLUMN данные не затрагивает?

    Полезу-ка я почитаю справку, раз уж такое дело - похоже я что-то упустил.
    P.S. Вот прям только колонки не удалял, да и каждый ген не смотрел, но когда смотрел / формировал - всегда формировался скрипт через вторую таблицу с переименованием.
    3 сен 18, 16:04    [21662844]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить