Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 порядок проставления identity значений при alter table add identity  [new]
tunknown
Member

Откуда:
Сообщений: 777
Есть таблица с многими записями изначально без кластерного ключа. Потребовалось добавить identity unique clustered.

В каком порядке проставятся identity значения и можно ли повлиять на порядок?

В Profiler между SQL:StmtStarting/Completed для ALTER видно автоматическое
UPDATE [таблица] SET [поле identity] = DEFAULT
Если кластерный ключ:
  • 1) не задан, порядок проставления identity "произвольный",
  • 2) есть, то согласно ему.

    Описание такого поведения найти не удалось. Есть ли это в официальной документации?
    Если важен порядок, то единственный вариант- перезалить данные?

    Для sql 2005+
  • 24 сен 15, 17:01    [18191119]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    Владислав Колосов
    Member

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

    2: нет
    3: перезалить - да. insert tbl select ... from ... order by ...
    24 сен 15, 17:14    [18191229]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 777
    Владислав Колосов
    2: нет
    Спасибо.

    Какой источник информации? Но можно ли внутренние автоматические запросы сервера считать действующими согласно официальной документации относительно update без order by?
    24 сен 15, 18:02    [18191528]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31978
    tunknown
    Но можно ли внутренние автоматические запросы сервера считать действующими согласно официальной документации относительно update без order by?
    Такой UPDATE из профайлера - это же не настоящий UPDATE, это некая условность, некий внутренний оператор, внутренний механизм. Конечно, к нему описание UPDATE из BOL не применимо.
    24 сен 15, 22:49    [18192642]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    Jaffar
    Member

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

    сначала нужно было добавить 2 поля ID_temp(PK) и собственно ID - пока что просто поле.
    потом делаем поле ID_temp - identity -и сервер его заполняет от балды типа default.


    далее заполняем второе поле как вам нужно
    update tt
    set
    		tt.ID = tt_new.NN
    from Table_ tt
    join (select tt.ID_temp, row_number() over(order by  - /*тут сортируем как вам нужно*/) NN
          from Table_tt2 /**/ ) tt_new on tt_new.ID_temp = tt.ID_temp
    


    далее удаляем поле ID_temp и делаем поле ID - identity primary key clustered и т.п.

    как-то так.
    25 сен 15, 09:18    [18193151]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 777
    Jaffar
    делаем поле ID - identity primary key clustered и т.п.
    Насколько я знаю, нет синтаксиса ALTER TABLE на добавление свойства IDENTITY.

    Пока что мне известен единственный вариант повлиять на значения IDENTITY- это перезалить/пересоздать таблицу, пусть и не явно, через заполнение альтернативного кластерного ключа и упование на недокументированное поведение update без order by.

    Возможно, такое получится сделать через %%ColumnEx(ObjectID = @object_id, Name = @identity_column).SetIdentityNotForRepl или что-то подобное, но на эксперименты нет времени.
    25 сен 15, 10:02    [18193329]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 777
    tunknown
    Насколько я знаю, нет синтаксиса ALTER TABLE на добавление свойства IDENTITY.
    Конечно, к существующему полю.
    25 сен 15, 10:03    [18193338]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    Jaffar
    Member

    Откуда:
    Сообщений: 633
    можно сделать поле identity - и потом выключить его типа

    set identity_insert Table_name ON - т.е. разрешить в него вставлять - а потом после всего выключить.
    25 сен 15, 10:25    [18193449]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 777
    Jaffar
    set identity_insert Table_name ON - т.е. разрешить в него вставлять
    Данные уже в таблице, к IDENTITY столбцу желательно применить UPADTE, но этого в mssql на текущий момент не предусмотрено.
    Без этого только перезаливка тем или иным способом. Другой метод мне неизвестен.
    25 сен 15, 11:05    [18193713]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 8823
    tunknown
    Владислав Колосов
    2: нет
    Спасибо.

    Какой источник информации? Но можно ли внутренние автоматические запросы сервера считать действующими согласно официальной документации относительно update без order by?


    Отчего Вы решили что подсмотренная Вами строка гарантирует какой-то порядок обновления? Источник указать не могу, не помню.
    Факт то, что гарантированный порядок identity будет только в пределах страницы кластерного индекса.
    помню, что порядок гарантируется только для выражения insert into ... select .... Ну, или сами пронумеруйте тем же update, как это написано выше.
    25 сен 15, 11:24    [18193852]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    a_voronin
    Member

    Откуда: Москва
    Сообщений: 4902
    tunknown
    Владислав Колосов
    2: нет
    Спасибо.

    Какой источник информации? Но можно ли внутренние автоматические запросы сервера считать действующими согласно официальной документации относительно update без order by?


    Я бы посоветовал вам вообще не рассчитывать ни на какой порядок IDENTITY. Если вам нужен порядок, то ваш надо взять и сделать нужный вам порядок с помощью команды UPDATE (от ROW_NUMBER() ) . А не ломать голову в документации о некоем порядке IDENTITY.
    25 сен 15, 11:45    [18194042]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31978
    a_voronin
    Если вам нужен порядок, то ваш надо взять и сделать нужный вам порядок с помощью команды UPDATE (от ROW_NUMBER() )
    Так запрёщён UPDATE для идентити поля. Так бы конечно...
    25 сен 15, 14:39    [18195138]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    a_voronin
    Member

    Откуда: Москва
    Сообщений: 4902
    alexeyvg
    a_voronin
    Если вам нужен порядок, то ваш надо взять и сделать нужный вам порядок с помощью команды UPDATE (от ROW_NUMBER() )
    Так запрёщён UPDATE для идентити поля. Так бы конечно...


    А нафига это поле IDENTITY делать вообще? Или надо сначала вставить туда что надо, а потом привинтить к нему identity
    25 сен 15, 14:48    [18195187]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    Shakill
    Member

    Откуда: мск
    Сообщений: 1887
    a_voronin
    надо сначала вставить туда что надо, а потом привинтить к нему identity

    каким образом?
    25 сен 15, 15:01    [18195247]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    o-o
    Guest
    alexeyvg
    a_voronin
    Если вам нужен порядок, то ваш надо взять и сделать нужный вам порядок с помощью команды UPDATE (от ROW_NUMBER() )
    Так запрёщён UPDATE для идентити поля. Так бы конечно...

    Jaffar
    можно сделать поле identity - и потом выключить его типа
    set identity_insert Table_name ON - т.е. разрешить в него вставлять - а потом после всего выключить.
    25 сен 15, 15:23    [18195411]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    a_voronin
    Member

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

    USE TEST 
    GO 
    
    create table table1 (col1 int, col2 varchar(30))
    
    insert into table1 values (100, 'olddata')
    
     
    
     --add identity column
    
    alter table table1 add col3 int identity(1,1)
    
    GO
    
     
    
    --rename or remove old column
    
    exec sp_rename 'table1.col1', 'oldcol1', 'column'
    GO
    
    alter table table1 drop column oldcol1
    
    --rename new column to old column name
    
    exec sp_rename 'table1.col3', 'col1', 'column'
    
    GO
    
     
    
    --add new test record and review table
    
    insert into table1 values ( 'newdata')
    
    select * from table1
    
     GO 
    
     DROP TABLE table1 
    
    25 сен 15, 15:34    [18195484]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    Shakill
    Member

    Откуда: мск
    Сообщений: 1887
    a_voronin, а в чем смысл этого скрипта, где здесь "сначала вставить туда что надо, а потом привинтить к нему identity"? значение 100 же потерялось
    25 сен 15, 17:31    [18196303]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    o-o
    Guest
    tunknown
    В каком порядке проставятся identity значения и можно ли повлиять на порядок?
    [/src]Если кластерный ключ:
  • 1) не задан, порядок проставления identity "произвольный",
  • 2) есть, то согласно ему.

  • проставляет в порядке скана,
    это и на куче явно просматривается
    +
    create table dbo.filler(id int identity primary key clustered, c char(8000));
    go
    
    insert into dbo.filler default values;
    go 200
    
    create table dbo.t (id char(8000), rv rowversion);
    go
    insert into dbo.t(id) values (cast (1 as char));
    insert into dbo.t(id) values (cast (2 as char));
    insert into dbo.t(id) values (cast (3 as char));
    go
    
    drop table dbo.filler;
    go
    
    insert into dbo.t(id) values (cast (4 as char));
    go
    
    
    alter table dbo.t add ident int not null identity (1, 1);
    
    insert into dbo.t(id) values (cast (5 as char));
    insert into dbo.t(id) values (cast (6 as char));
    insert into dbo.t(id) values (cast (7 as char));
    insert into dbo.t(id) values (cast (8 as char));
    
    go
    
    SELECT ident, row_number() over(order by %%physloc%%) AS physloc, 
           rv,
           sys.fn_PhysLocFormatter(%%physloc%%)
    from dbo.t;
    ----
    ident	physloc	rv	(No column name)
    8	1	0x00000000000007D8	(1:153:0)
    4	2	0x00000000000007D4	(1:168:0)
    5	3	0x00000000000007D5	(1:169:0)
    1	4	0x00000000000007D1	(1:170:0)
    2	5	0x00000000000007D2	(1:172:0)
    3	6	0x00000000000007D3	(1:173:0)
    6	7	0x00000000000007D6	(1:174:0)
    7	8	0x00000000000007D7	(1:175:0)
    
    25 сен 15, 18:08    [18196488]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    o-o
    Guest
    o-o
    alexeyvg
    пропущено...
    Так запрёщён UPDATE для идентити поля. Так бы конечно...

    Jaffar
    можно сделать поле identity - и потом выключить его типа
    set identity_insert Table_name ON - т.е. разрешить в него вставлять - а потом после всего выключить.

    alexeyvg прав.
    Апдэйтить не выйдет, это же не инсерт, куда глядели мои глаза
    Приношу извинения по этому поводу
    25 сен 15, 18:41    [18196690]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    Mind
    Member

    Откуда: Лучший город на Земле
    Сообщений: 2322
    tunknown
    Есть таблица с многими записями изначально без кластерного ключа. Потребовалось добавить identity unique clustered.

    В каком порядке проставятся identity значения и можно ли повлиять на порядок?
    А чего вам так сдался этот порядок IDENTITY? У вас что там какая-то бизнес логика завязана на несуществуещее, пока что, поле? Или вы собираетесь по IDENTITY полю определять какую строку вставили первой, а какую второй? Или просто хочется чтобы все записи "по-порядку" шли? А потом еще вы захотите, чтобы при удаление строк, автоматически пробелы убирались?
    25 сен 15, 22:40    [18197625]     Ответить | Цитировать Сообщить модератору
     Re: порядок проставления identity значений при alter table add identity  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 777
    Mind
    У вас что там какая-то бизнес логика завязана на несуществуещее, пока что, поле? Или вы собираетесь по IDENTITY полю определять какую строку вставили первой, а какую второй?
    Да, порядок вставки используется в бизнес-логике. Раньше это была нумерация 1,2,3 в пределах "мастер" поля. Теперь это IDENTITY в пределах таблицы.
    28 сен 15, 10:21    [18202912]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить