All geek questions in one place

Using a custom encoding scheme instead of a GUID as a primary key

I am increasing the MS Access database on SQL Server. The front client will remain the access application for now (it has about 30 thousand lines of code).

The goal is to ultimately allow database synchronization across multiple servers (not using replication, but possibly a synchronization structure).
Currently, all primary keys in Access tables are auto-incrementing whole surrogates.

I do not ask about the process of increasing, but about whether to use a GUID or other encoding for the PC (I know that I could split the range of numbers on all servers, but I do not want to do this and allow the PK to be created on the client if necessary, for example, offline).

GUID

Pro:

  • standardized format.
  • guaranteed uniqueness (in almost any case)

Minuses:

  • Access is not easy to manipulate, especially when used as filters for subforms or controls.
  • degrades INSERT performance due to their randomness.
  • has more than one representation: string, canonical form, binary, which must be converted.

Custom coding scheme

, , , , , , , , PK , ( / ).

10- , :

  • 8
  • 4
  • 2 34, AZ 2-9, O, 0, 1, I - ( , PK, ).

Pro:

  • , .
  • , ( ).
  • ()

:

  • JOIN
  • INSERT GUID,
  • / UID, .

, GUID ?

+3
2

Access, .

- > Access GUID Number- > Replication Identificator. Access PK GUID, ( ).

INSERT - .

- > , (, ). MSSQL- GUID - "newid()" "newsequenceid()". - - , .

: string, canonical form, binary, .

- > "PRO" :). - - Access MSSQL .

GUID - "" 128- . , JOINs GUID. GUID , , .

, , . , GUID , .

+3

? bigint ? 9,223,372,036,854,775,807 ( )

, - ( bigint GUID/uniqueidentifier). , int bigint , GUID .

+2

Source: https://habr.com/ru/post/1705849/

More articles:


All Articles