Sekwencja a tożsamość

Wprowadzono SQL Server 2012 Sequence jako nowa funkcja, taka sama jak w Oracle i Postgres. Gdzie sekwencje są preferowane nad tożsamościami? A po co nam sekwencje?

Myślę, że tutaj znajdziesz odpowiedź

Korzystając z atrybutu tożsamości dla kolumny, można łatwo generować liczby automatycznie zwiększające się (które są często używane jako klucz podstawowy). W przypadku Sekwencji będzie to inny obiekt, który można dołączyć do kolumny tabeli podczas wstawiania. W przeciwieństwie do tożsamości, następna liczba dla wartości kolumny zostanie pobrana z pamięci, a nie z dysku - dzięki temu Sequence jest znacznie szybszy niż Identity. Zobaczymy to w przyszłych przykładach.

A tutaj :

Sekwencje: sekwencje były wymagane przez społeczność SQL Server od lat i zostały uwzględnione w tej wersji. Sekwencja to obiekt zdefiniowany przez użytkownika, który generuje sekwencję liczb. Oto przykład użycia Sequence.

i tutaj również:

Obiekt sekwencji programu SQL Server generuje sekwencję liczb, podobnie jak kolumna tożsamości w tabelach sql. Ale zaletą numerów sekwencyjnych jest to, że obiekt numeru sekwencji nie jest ograniczony do jednej tabeli sql.

a na msdn możesz również przeczytać więcej o użyciu i dlaczego go potrzebujemy ( tutaj ):

Sekwencja to zdefiniowany przez użytkownika obiekt powiązany ze schematem, który generuje sekwencję wartości liczbowych zgodnie ze specyfikacją, z jaką sekwencja została utworzona. Sekwencja wartości liczbowych jest generowana w kolejności rosnącej lub malejącej w określonym przedziale i może być cykliczna (powtarzana) zgodnie z żądaniem. Sekwencje, w przeciwieństwie do kolumn tożsamości, nie są powiązane z tabelami. Aplikacja odwołuje się do obiektu sekwencji, aby otrzymać następną wartość. Relacja między sekwencjami i tabelami jest kontrolowana przez aplikację. Aplikacje użytkownika mogą odwoływać się do obiektu sekwencji i koordynować klucze wartości w wielu wierszach i tabelach.

Sekwencja jest tworzona niezależnie od tabel za pomocą instrukcji CREATE SEQUENCE. Opcje umożliwiają sterowanie przyrostem, wartościami maksymalnymi i minimalnymi, punktem początkowym, możliwością automatycznego ponownego uruchamiania oraz buforowaniem w celu poprawy wydajności. Aby uzyskać informacje na temat opcji, zobacz UTWÓRZ SEKWENCJĘ.

W przeciwieństwie do wartości kolumn tożsamości, które są generowane podczas wstawiania wierszy, aplikacja może uzyskać następny numer kolejny przed wstawieniem wiersza, wywołując funkcję NEXT VALUE FOR. Numer kolejny jest przydzielany, gdy wywoływana jest NEXT VALUE FOR, nawet jeśli numer nigdy nie jest wstawiany do tabeli. Funkcji NEXT VALUE FOR można użyć jako wartości domyślnej dla kolumny w definicji tabeli. Użyj sp_sequence_get_range, aby uzyskać zakres wielu numerów sekwencyjnych jednocześnie.

Sekwencję można zdefiniować jako dowolny typ danych całkowitoliczbowych. Jeśli typ danych nie jest określony, domyślną sekwencją jest bigint.

Sekwencja i tożsamość są używane do generowania automatycznego numeru, ale główna różnica polega na tym, że tożsamość jest zależna od tabeli, a sekwencja jest niezależna od tabeli.

Jeśli masz scenariusz, w którym musisz utrzymywać numer automatyczny globalnie (w wielu tabelach), również musisz ponownie uruchomić interwał po określonej liczbie i musisz go buforować również dla wydajności, tutaj jest miejsce, w którym potrzebujemy sekwencji, a nie tożsamość.

Chociaż sekwencje zapewniają większą elastyczność niż kolumny tożsamości, nie stwierdziłem, że mają jakiekolwiek korzyści z wydajności.

Zauważyłem, że wydajność przy użyciu tożsamości była konsekwentnie 3x szybsza niż przy użyciu sekwencji do wstawiania wsadowego.

Wstawiłem ok. 1,5 mln wierszy i wydajność wyniosła:

• 14 sekund na tożsamość
• 45 sekund na sekwencję

Wstawiłem wiersze do tabeli, która używała obiektu sekwencji za pośrednictwem domyślnej tabeli:

NEXT VALUE for <seq> for <col_name>

a także próbował określić wartość sekwencji w instrukcji select:

SELECT NEXT VALUE for <seq>, <other columns> from <table>

Oba były tym samym czynnikiem wolniej niż metoda tożsamości. Użyłem domyślnej opcji pamięci podręcznej dla sekwencji.

Artykuł, do którego odwołuje się pierwszy link Ariona, pokazuje wydajność wstawiania wiersz po wierszu, a różnica między tożsamością i sekwencją wynosiła od 16,6 sekundy do 14,3 sekundy dla 10 000 wstawień.

Opcja buforowania ma duży wpływ na wydajność, ale tożsamość jest szybsza w przypadku większych wolumenów (+ 1 mln wierszy)

Zobacz ten link, aby uzyskać dogłębną analizę zgodnie z komentarzem utly4life.

Wiem, że to trochę stare, ale chciałem dodać spostrzeżenie, które mnie ugryzło.

Zmieniłem tożsamość na sekwencję, aby uporządkować indeksy. Później dowiedziałem się, że sekwencja nie przenosi się wraz z replikacją. Po skonfigurowaniu replikacji między dwiema bazami danych zacząłem otrzymywać kluczowe naruszenia, ponieważ sekwencje nie były zsynchronizowane. po prostu coś, na co należy uważać przed podjęciem decyzji.

Uważam, że najlepszym zastosowaniem Sekwencji nie jest zastępowanie kolumny tożsamości, ale tworzenie pola typu „Numer zamówienia”.

Innymi słowy, numer zamówienia jest widoczny dla użytkownika końcowego i może zawierać reguły biznesowe. Chcesz, aby był unikalny, ale samo użycie kolumny tożsamości też nie jest poprawne.

Na przykład różne typy zamówień mogą wymagać innej kolejności, więc możesz mieć kolejność dla zamówień internetowych, w przeciwieństwie do zamówień wewnętrznych.

Innymi słowy, nie myśl o Sekwencji jako prostym zastępstwie tożsamości, myśl o niej jako użytecznej w przypadkach, gdy tożsamość nie spełnia wymagań biznesowych.

Ostatnio trzeba było wziąć pod uwagę tożsamość kontra sekwencję. Wygląda na to, że MSFT sugeruje teraz sekwencję, jeśli chcesz zachować tożsamość bez luk. Mieliśmy problem polegający na tym, że istniały ogromne luki w tożsamości, ale na podstawie tego stwierdzenia podkreślenie wyjaśniłoby nasz problem, że SQL buforował tożsamość i po ponownym uruchomieniu utraciliśmy te liczby.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

Kolejne wartości po ponownym uruchomieniu serwera lub innych awariach - SQL Server może buforować wartości tożsamości ze względu na wydajność, a niektóre z przypisanych wartości mogą zostać utracone podczas awarii bazy danych lub ponownego uruchomienia serwera. Może to spowodować luki w wartości tożsamości podczas wstawiania. Jeśli luki są nie do zaakceptowania, aplikacja powinna używać własnego mechanizmu do generowania wartości kluczy. Użycie generatora sekwencji z opcją NOCACHE może ograniczyć luki do transakcji, które nigdy nie zostały zatwierdzone.