GUID. and automatic id as primary key in sql databases

Question

SELECT COUNT(*) FROM table_name;

check count count+1 is the new primary key starting point

then keep on incrementing before every insert operation

but what is this GUID? doe sql server provide something where it automatically generates and incremented primary key?

any link to description and any help thanks

Answer 1

create table your table (id int indentity(1,1) primary key, col1 varchar(10) )

will automatically create the primary key for you. Check GUID in the T-SQL, don't have it at hand right now.

Answer 2

I'm not sure if you're also asking about IDENTITY or not- but a GUID is a unique identifier that is (almost) guaranteed to be unique. It can be used on primary keys but isn't recommended unless you're doing offline work or planning on merging databases.

For example a "normal", IDENTITY primary key is

1   Jason
2   Jake
3   Mike

which when merging with another database which looks like

1   Lisa
2   John
3   Sam

will be tricky. You've got to re-key some columns, make sure that your FKs are in order, etc. Using GUIDs, the data looks like this, and is easy to merge:

1FB74D3F-2C84-43A6-9FB6-0EFC7092F4CE    Jason
845D5184-6383-473F-A5D6-4DE98DBFBC39    Jake
8F515331-4457-49D0-A9F5-5814EE7F50BA    Mike    
CE789C89-E01F-4BCE-AC05-CBDF10419E78    Lisa
4D51B568-107C-4B63-9F7F-24592704118F    John
7FA4ED64-7356-4013-A78A-C8CCAB329954    Sam

Note that a GUID takes a lot more space than an INT, and because of this it's recommended to use an INT as a primary key unless you absolutely need to.

Answer 3

There are 3 options

CREATE TABLE A
(
ID INT IDENTITY(1,1) PRIMARY KEY,
... Other Columns
)

CREATE TABLE B
(
ID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
... Other Columns
)

CREATE TABLE C
(
ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
... Other Columns
)

One reason why you might prefer C rather than B would be to reduce fragmentation if you were to use the ID as the clustered index.

Answer 4

Sorry, new to SO here. Still trying to figure out how things work. Moved answer up to new post.

Answer 5

The issue with using count , then count +1 as key, is that were you to delete a record from the middle, you would end up with a duplicate key generated. EG:

Key   Data    
1     A          
2     B
3     C
4     D

now delete B (count becomes 3), and insert E. This tries to make the new primary key as 4, which already exists.

Key   Data    
1        A          
3        C
4        D   <--After delete count = 3 here
4        E   <--Attempted insert with key 4    

You could use primary key and auto increment to make sure you don't have this issue

CREATE TABLE myTable
(
  P_Id int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (P_Id)
)

Or you could use GUID. How GUIDs work is by creating a 128 bit integer (represented as a 32 char hex string)

Key                                      Data
24EC84E0-36AA-B489-0C7B-074837BCEA5D     A
.
.

This results in 2^128 possible values (reaaally large), so the chances of similar values created by one computer is extremely small. Besides there are algorithms to help try and ensure that this doesn't happen. So GUID are a pretty good choice for a key as well.

As for whether you use an integer or a GUID, is usually dependent on application, policy etc.