PIERWSZY
Prawdopodobnie nie trzeba wszystkie trzy kolumny: old_id, external_id, new_id. new_idKolumna, bycia IDENTITY, będzie mieć nową wartość wygenerowaną dla każdego wiersza, nawet po włożeniu do external_id. Ale pomiędzy old_idi external_idsą one prawie wzajemnie wykluczające: albo istnieje już old_idwartość, albo ta kolumna, w obecnej koncepcji, będzie tylko, NULLjeśli użyjesz external_idlub new_id. Ponieważ nie dodasz nowego „zewnętrznego” identyfikatora do już istniejącego wiersza (tj. Takiego, który ma old_idwartość), i nie będzie żadnych nowych wartości old_id, więc może być używana jedna kolumna do obu celów.
Pozbądź się external_idkolumny i zmień nazwę, old_idaby była czymś podobnym old_or_external_idlub czymkolwiek. Nie powinno to wymagać żadnych rzeczywistych zmian w niczym, ale zmniejsza pewne komplikacje. Konieczne może być wywołanie kolumny external_id, nawet jeśli zawiera ona „stare” wartości, jeśli kod aplikacji jest już zapisany do wstawienia external_id.
Dzięki temu nowa struktura jest po prostu:
PkId AS AS COALESCE(old_or_external_id, new_id, -1) PERSISTED NOT NULL,
old_or_external_id INT NULL, -- values from existing record OR passed in from app
new_id INT IDENTITY(2000000, 1) NOT NULL
Teraz dodano tylko 8 bajtów na wiersz zamiast 12 bajtów (zakładając, że nie używasz SPARSEopcji ani kompresji danych). I nie trzeba było zmieniać żadnego kodu, T-SQL ani kodu aplikacji.
DRUGA
Kontynuując tę ścieżkę uproszczenia, spójrzmy na to, co nam pozostało:
old_or_external_idKolumna albo ma już wartości, lub będą miały nową wartość z aplikacji, lub będą lewo NULL.new_idZawsze będzie mieć nową wartość wygenerowaną, ale wartość ta będzie używana tylko wtedy, gdy old_or_external_idkolumna jest NULL.
Nigdy nie ma czasu, kiedy potrzebujesz wartości zarówno w, jak old_or_external_idi w new_id. Tak, będą chwile, gdy obie kolumny będą miały wartości z powodu new_idbycia IDENTITY, ale te new_idwartości są ignorowane. Ponownie te dwa pola wzajemnie się wykluczają. Co teraz?
Teraz możemy zastanowić się, dlaczego tak naprawdę potrzebowaliśmy external_id. Biorąc pod uwagę, że można wstawić do IDENTITYkolumny za pomocą SET IDENTITY_INSERT {table_name} ON;, możesz w ogóle nie wprowadzać żadnych zmian schematu, a jedynie modyfikować kod aplikacji, aby zawijać INSERTinstrukcje / operacje SET IDENTITY_INSERT {table_name} ON;i SET IDENTITY_INSERT {table_name} OFF;instrukcje. Następnie należy określić, do jakiego zakresu początkowego należy zresetować IDENTITYkolumnę (dla nowo wygenerowanych wartości), ponieważ będzie ona musiała znajdować się znacznie powyżej wartości, które wstawi kod aplikacji, ponieważ wstawienie wyższej wartości spowoduje, że następna automatycznie wygenerowana wartość będzie być większa niż bieżąca wartość MAX. Ale zawsze możesz wstawić wartość poniżej wartości IDENT_CURRENT .
Łączenie kolumn old_or_external_idi new_idnie zwiększa również szansy na wystąpienie nakładającej się wartości między wartościami generowanymi automatycznie i wartościami generowanymi przez aplikację, ponieważ celem uzyskania 2, a nawet 3 kolumn jest połączenie ich w wartość klucza podstawowego, i są to zawsze unikalne wartości.
W tym podejściu wystarczy:
Pozostaw tabele jako:
PkId INT IDENTITY(1,1) PRIMARY KEY
Dodaje to 0 bajtów do każdego wiersza zamiast 8, a nawet 12.
- Określ zakres początkowy dla wartości generowanych przez aplikację. Będą one wyższe niż bieżąca wartość MAX w każdej tabeli, ale mniejsza niż to, co stanie się wartością minimalną dla automatycznie generowanych wartości.
- Określ wartość, od której powinien zacząć się automatycznie wygenerowany zakres. Pomiędzy obecną wartością MAX a miejscem na wzrost powinno być dużo miejsca , wiedząc, że górna granica wynosi nieco ponad 2,14 miliarda. Następnie możesz ustawić tę nową minimalną wartość początkową za pomocą DBCC CHECKIDENT .
- Zawiń kod aplikacji WSTAWIANIE
SET IDENTITY_INSERT {table_name} ON;i SET IDENTITY_INSERT {table_name} OFF;instrukcje.
DRUGA, część B
Odmianą podejścia opisanego bezpośrednio powyżej byłoby wprowadzenie wartości wstawiania kodu aplikacji zaczynających się od -1, a następnie zmniejszających się . To pozostawia IDENTITYwartości jako jedyne idące w górę . Korzyścią jest to, że nie tylko nie komplikujesz schematu, ale także nie musisz się martwić, że natrafisz na nakładające się identyfikatory (jeśli wartości wygenerowane przez aplikację trafią do nowego automatycznie wygenerowanego zakresu). Jest to opcja tylko wtedy, gdy nie używasz już ujemnych wartości identyfikatora (i wydaje się, że ludzie rzadko używają wartości ujemnych w automatycznie generowanych kolumnach, więc w większości sytuacji powinno to być prawdopodobne).
W tym podejściu wystarczy:
Pozostaw tabele jako:
PkId INT IDENTITY(1,1) PRIMARY KEY
Dodaje to 0 bajtów do każdego wiersza zamiast 8, a nawet 12.
- Zakres początkowy dla wartości generowanych przez aplikację będzie wynosił
-1.
- Zawiń kod aplikacji WSTAWIANIE
SET IDENTITY_INSERT {table_name} ON;i SET IDENTITY_INSERT {table_name} OFF;instrukcje.
Tutaj nadal musisz to zrobić IDENTITY_INSERT, ale: nie dodajesz żadnych nowych kolumn, nie musisz „ponownie wysyłać” żadnych IDENTITYkolumn i nie ma w przyszłości ryzyka nakładania się.
DRUGA, część 3
Ostatnią odmianą tego podejścia byłoby ewentualnie zamiana IDENTITYkolumn i zamiast tego użycie Sekwencji . Powodem takiego podejścia jest możliwość wprowadzenia wartości wstawiania kodu aplikacji, które są: dodatnie, powyżej zakresu generowanego automatycznie (nie poniżej) i nie ma takiej potrzeby SET IDENTITY_INSERT ON / OFF.
W tym podejściu wystarczy:
- Twórz sekwencje za pomocą CREATE SEQUENCE
Skopiuj IDENTITYkolumnę do nowej kolumny, która nie ma IDENTITYwłaściwości, ale ma DEFAULTOgraniczenie za pomocą funkcji NASTĘPNA WARTOŚĆ DLA :
PkId INT PRIMARY KEY CONSTRAINT [DF_TableName_NextID] DEFAULT (NEXT VALUE FOR...)
Dodaje to 0 bajtów do każdego wiersza zamiast 8, a nawet 12.
- Zakres początkowy dla wartości generowanych przez aplikację będzie znacznie powyżej tego, co Twoim zdaniem zbliży się do wartości wygenerowanych automatycznie.
- Zawiń kod aplikacji WSTAWIANIE
SET IDENTITY_INSERT {table_name} ON;i SET IDENTITY_INSERT {table_name} OFF;instrukcje.
JEDNAK , ze względu na wymaganie, aby kod z jednym SCOPE_IDENTITY()lub @@IDENTITYnadal działał poprawnie, przełączanie na Sekwencje nie jest obecnie opcją, ponieważ wydaje się, że nie ma odpowiednika tych funkcji dla Sekwencji :-(. Smutne!