Как бы вы реализовали последовательности в Microsoft SQL Server?

Столбец идентичности примерно аналогичен последовательности.

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

BEGIN TRANSACTION  
SELECT number from plain old table..  
UPDATE plain old table, set the number to be the next number  
INSERT your row  
COMMIT  

Но не делай этого. Блокировка была бы плохой ...

Я начал с SQL Server, и для меня схема «последовательности» Oracle выглядела как взлом. Я предполагаю, что вы идете с другой стороны и к себе, а scope_identity () выглядит как взлом.

Преодолей это. Находясь в Риме, делайте то же, что и римляне.

Я полностью согласен и сделал это в прошлом году над проектом.

Я только что создал таблицу с названием последовательности, текущим значением и величиной приращения.

Затем я создал 2 процедуры, чтобы добавить и удалить их. И 2 функции, чтобы перейти к следующему и получить текущее.

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

1. Выполните вставку через хранимую процедуру, которая возвращает вновь вставленное значение ключа.
2. Реализуйте последовательность на стороне клиента (чтобы вы знали новый ключ перед вставкой)

Если я делаю генерацию ключей на стороне клиента, я использую GUID люблю. Я считаю, что они чертовски красивы.

row["ID"] = Guid.NewGuid();

Эта линия должна быть где-то на капоте спортивного автомобиля.

Последовательности, реализованные в Oracle, требуют вызова базы данных перед вставкой. идентификаторы, реализованные в SQL Server, требуют вызова базы данных после вставки.

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

Я предполагаю, что ваша реляционная модель основана на искусственных ключах, и в этом контексте я предлагаю следующее наблюдение:

Мы никогда не должны пытаться наделять искусственные ключи смыслом; их единственная цель должна заключаться в связывании связанных записей.

Что вам нужно в отношении данных для заказа? может ли это быть обработано в представлении (презентации) или это истинный атрибут ваших данных, который необходимо сохранить?

Если вы используете SQL Server 2005, у вас есть возможность использовать Row_Number

Создайте рабочую таблицу с идентификатором на ней.

Перед загрузкой промежуточной таблицы обрежьте и повторно установите идентификатор, чтобы он начинался с 1.

Загрузите свой стол. Каждая строка теперь имеет уникальное значение от 1 до N.

Создайте таблицу, содержащую порядковые номера. Это может быть несколько строк, по одной для каждой последовательности.

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

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

Другая проблема со столбцами идентификаторов заключается в том, что если у вас есть несколько таблиц, в которых порядковые номера должны быть уникальными, столбец идентификаторов не работает. И, как упоминает Кори Трейджер, реализация последовательности типа «ролл-сам» может вызвать некоторые проблемы с блокировкой.

Наиболее прямо эквивалентными решениями, по-видимому, является создание таблицы SQL Server с одним столбцом для идентификатора, который заменяет отдельный тип объекта «последовательность». Например, если в Oracle у вас будет две таблицы из одной последовательности, такой как Dogs <- sequence object -> Cats, тогда в SQL Server вы должны создать три объекта базы данных, все таблицы, такие как Dogs <- Pets с столбцом идентификаторов -> Кошки. Вы должны вставить строку в таблицу Pets, чтобы получить порядковый номер, в котором вы обычно используете NEXTVAL, а затем вставить в таблицу Dogs или Cats, как обычно, когда вы получаете фактический тип домашнего животного от пользователя. Любые дополнительные общие столбцы могут быть перемещены из таблиц Dogs / Cats в таблицу супертипов Pets, с некоторыми последствиями: 1) будет одна строка для каждого порядкового номера, 2) любые столбцы, которые невозможно заполнить при получении порядкового номера, будут должны иметь значения по умолчанию и 3) для получения всех столбцов потребуется соединение.

Sql Server 2012 представил SEQUENCE объекты, который позволяет генерировать последовательные числовые значения, не связанные с какой-либо таблицей.

Создать их несложно:

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;

Пример использования их перед прошивкой:

DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Schema.SequenceName;
-- Some work happens
INSERT Schema.Orders (OrderID, Name, Qty)
  VALUES (@NextID, 'Rim', 2) ;

См. Мой блог, чтобы подробно узнать, как использовать последовательности:

http://sqljunkieshare.com/2011/12/11/sequences-in-sql-server-2012-implementingmanaging-performance/

Рассмотрим следующий фрагмент.

CREATE TABLE [SEQUENCE](
    [NAME] [varchar](100) NOT NULL,
    [NEXT_AVAILABLE_ID] [int] NOT NULL,
 CONSTRAINT [PK_SEQUENCES] PRIMARY KEY CLUSTERED 
(
    [NAME] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE CLAIM_IDS (@sequenceName varchar(100), @howMany int)
AS
BEGIN
    DECLARE @result int
    update SEQUENCE
        set
            @result = NEXT_AVAILABLE_ID,
            NEXT_AVAILABLE_ID = NEXT_AVAILABLE_ID + @howMany
        where Name = @sequenceName
    Select @result as AVAILABLE_ID
END
GO

В SQL Server 2012 вы можете просто использовать

CREATE SEQUENCE

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

Вот пример (обратите внимание, что параметр MAXRECURSION важен):

DECLARE @MinValue INT = 1;
DECLARE @MaxValue INT = 1000;

WITH IndexMaker (IndexNumber) AS
(
    SELECT 
        @MinValue AS IndexNumber
    UNION ALL SELECT 
        IndexNumber + 1
    FROM
        IndexMaker
    WHERE IndexNumber < @MaxValue
)
SELECT
    IndexNumber
FROM
    IndexMaker
ORDER BY
    IndexNumber
OPTION 
    (MAXRECURSION 0)

Для решения этой проблемы я использовал таблицу «Последовательности», в которой хранятся все мои последовательности, и хранимую процедуру «nextval».

Таблица sql:

CREATE TABLE Sequences (  
    name VARCHAR(30) NOT NULL,  
    value BIGINT DEFAULT 0 NOT NULL,  
    CONSTRAINT PK_Sequences PRIMARY KEY (name)  
);

PK_Sequences используется только для того, чтобы быть уверенным, что никогда не будет последовательностей с таким же именем.

Хранимая процедура Sql:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'nextVal') AND type in (N'P', N'PC')) DROP PROCEDURE nextVal;  
GO  
CREATE PROCEDURE nextval  
    @name VARCHAR(30)  
AS  
    BEGIN  
        DECLARE @value BIGINT  
        BEGIN TRANSACTION  
            UPDATE Sequences  
            SET @value=value=value + 1  
            WHERE name = @name;  
            -- SELECT @value=value FROM Sequences WHERE name=@name  
        COMMIT TRANSACTION  
        SELECT @value AS nextval  
    END;  

Вставьте несколько последовательностей:

INSERT INTO Sequences(name, value) VALUES ('SEQ_Workshop', 0);
INSERT INTO Sequences(name, value) VALUES ('SEQ_Participant', 0);
INSERT INTO Sequences(name, value) VALUES ('SEQ_Invoice', 0);  

Наконец, получите следующее значение последовательности,

execute nextval 'SEQ_Participant';

Некоторый код C# для получения следующего значения из таблицы последовательности,

public long getNextVal()
{
    long nextval = -1;
    SqlConnection connection = new SqlConnection("your connection string");
    try
    {
        //Connect and execute the select sql command.
        connection.Open();

        SqlCommand command = new SqlCommand("nextval", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "SEQ_Participant";
        nextval = Int64.Parse(command.ExecuteScalar().ToString());

        command.Dispose();
    }
    catch (Exception) { }
    finally
    {
        connection.Dispose();
    }
    return nextval;
}

Как sqljunkiesshare состояния, последовательности были добавлены в SQL Server 2012. Вот как это сделать в графическом интерфейсе. Это эквивалент:

CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;

1. В Обозреватель объектов разверните папку Программируемость
2. В папке Программируемость щелкните правой кнопкой мыши Последовательности папка, как показано ниже:

3. Подчеркнуты значения, которые необходимо обновить, чтобы получить эквивалент приведенного выше оператора SQL, однако я бы рассмотрел изменяя их в зависимости от ваших потребностей (см. примечания ниже).

Заметки:

• Начальное значение по умолчанию, Минимальное значение и Максимальное значение определялись диапазоном типа данных, который в данном случае был int. См. Здесь дополнительные диапазоны типов данных, если вы хотите использовать что-то, кроме int.

• Довольно велика вероятность, что вы захотите, чтобы ваша последовательность начиналась с 1, и вы также может потребоваться минимальное значение 1.

С помощью SQL вы можете использовать эту стратегию;

CREATE SEQUENCE [dbo].[SequenceFile]
AS int
START WITH 1
INCREMENT BY 1 ;

и прочитайте уникальное следующее значение этого SQL

SELECT NEXT VALUE FOR [dbo].[SequenceFile]

СДЕЛКА БЕЗОПАСНА! Для версий SQLServer до 2012 года ... (спасибо Matt G.) В этом обсуждении не хватает одной вещи - безопасности транзакций. Если вы получаете число из последовательности, этот номер должен быть уникальным, и никакое другое приложение или код не должны иметь возможность получить этот номер. В моем случае мы часто извлекаем уникальные числа из последовательностей, но фактическая транзакция может занимать значительный промежуток времени, поэтому мы не хотим, чтобы кто-либо другой получил то же число до того, как мы зафиксируем транзакцию. Нам нужно было имитировать поведение последовательностей оракулов, где номер был зарезервирован при извлечении. Мое решение - использовать xp_cmdshell для получения отдельного сеанса / транзакции в базе данных, чтобы мы могли немедленно обновить последовательность для всей базы данных, даже до завершения транзакции.

--it is used like this:
-- use the sequence in either insert or select:
Insert into MyTable Values (NextVal('MySequence'), 'Foo');

SELECT NextVal('MySequence');

--you can make as many sequences as you want, by name:
SELECT NextVal('Mikes Other Sequence');

--or a blank sequence identifier
SELECT NextVal('');

Решение требует единой таблицы для хранения используемых значений последовательности и процедуры Это создает вторую автономную транзакцию, чтобы гарантировать, что параллельные сеансы не запутаются. У вас может быть столько уникальных последовательностей, сколько вам нужно, они называются по имени. Пример кода ниже изменен, чтобы не запрашивать пользователя и отметку даты в таблице истории последовательностей (для аудита), но я думал, что менее сложный вариант лучше ;-).

  CREATE TABLE SequenceHolder(SeqName varchar(40), LastVal int);

GO
CREATE function NextVAL(@SEQname varchar(40))
returns int
as
begin
    declare @lastval int
    declare @barcode int;

    set @lastval = (SELECT max(LastVal) 
                      FROM SequenceHolder
                     WHERE SeqName = @SEQname);

    if @lastval is null set @lastval = 0

    set @barcode = @lastval + 1;

    --=========== USE xp_cmdshell TO INSERT AND COMMINT NOW, IN A SEPERATE TRANSACTION =============================
    DECLARE @sql varchar(4000)
    DECLARE @cmd varchar(4000)
    DECLARE @recorded int;

    SET @sql = 'INSERT INTO SequenceHolder(SeqName, LastVal) VALUES (''' + @SEQname + ''', ' + CAST(@barcode AS nvarchar(50)) + ') '
    SET @cmd = 'SQLCMD -S ' + @@servername +
              ' -d ' + db_name() + ' -Q "' + @sql + '"'
    EXEC master..xp_cmdshell @cmd, 'no_output'

    --===============================================================================================================

    -- once submitted, make sure our value actually stuck in the table
    set @recorded = (SELECT COUNT(*) 
                       FROM SequenceHolder
                      WHERE SeqName = @SEQname
                        AND LastVal = @barcode);

    --TRIGGER AN ERROR 
    IF (@recorded != 1)
        return cast('Barcode was not recorded in SequenceHolder, xp_cmdshell FAILED!! [' + @cmd +']' as int);

    return (@barcode)

end

GO

COMMIT;

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

--- LOOSEN SECURITY SO THAT xp_cmdshell will run 
---- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
---- To update the currently configured value for advanced options.
RECONFIGURE
GO
---- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
---- To update the currently configured value for this feature.
RECONFIGURE
GO

—-Run SQLServer Management Studio as Administrator,
—- Login as domain user, not sqlserver user.

--MAKE A DATABASE USER THAT HAS LOCAL or domain LOGIN! (not SQL server login)
--insure the account HAS PERMISSION TO ACCESS THE DATABASE IN QUESTION.  (UserMapping tab in User Properties in SQLServer)

—grant the following
GRANT EXECUTE on xp_cmdshell TO [domain\user] 

—- run the following:
EXEC sp_xp_cmdshell_proxy_account 'domain\user', 'pwd'

--alternative to the exec cmd above: 
create credential ##xp_cmdshell_proxy_account## with identity = 'domain\user', secret = 'pwd'


-—IF YOU NEED TO REMOVE THE CREDENTIAL USE THIS
EXEC sp_xp_cmdshell_proxy_account NULL;


-—ways to figure out which user is actually running the xp_cmdshell command.
exec xp_cmdshell 'whoami.exe'  
EXEC xp_cmdshell 'osql -E -Q"select suser_sname()"'
EXEC xp_cmdshell 'osql -E -Q"select * from sys.login_token"'