The key problems about SQL, the use of time and GUID

I design a module, due to consider the future of migration and ensure data and other system data synchronization. So in the design of the primary key does not use identity columns. The use of GUID as a primary key, but the feeling of GUID as a primary key, too long, and no law, when the amount of data, according to the primary key is a big problem, and even the clustered index can't do not?
To solve these problems, I write a function, used to generate their own primary key. Because time is continuous, so the generated by time and GUID, but GUID is too long, so only intercepted in front of eight characters, constitute the new primary key. Methods similar to the following:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(100),GETDATE(),21),'-',''),':',''),'.',''),' ','')+'_'+left(CAST(NEWID() AS VARCHAR(36)),8)

Do not know if this is good, can have what problem did not teach, hope to have the experience of predecessors, thank you~

Started by Winnie at February 06, 2016 - 4:06 PM

Do not use newid () generation, can use CREATE TABLE a (a UNIQUEIDENTIFIER) this generation, but I think with the service key to make the primary key is good

Posted by Dana at February 16, 2016 - 4:25 PM

This is a primary key in GUID, out migration to other database, such as Oracle, or string a, and if the primary key, the clustered index key is it right? Also has no effect?

Posted by Winnie at March 01, 2016 - 5:22 PM

And now I'm out of the 20131017115630280_43AD9A64 has 26 characters, address physical address and the sort of the clustered index is not consistent.?

Posted by Winnie at March 13, 2016 - 5:28 PM

Used to generate newid, is really the only, but takes 36 bytes, is too long.,

You are using +newid, then take the first 8, will occupy 26 characters, also many, it is a little regularity, at least the front part can know the date and time, can also achieve uniqueness.

Such a consideration is a compromise, says UNIQUEIDENTIFIER, also can achieve uniqueness, occupies 16 bytes, but the value is in disorder, also meaningless values, not suitable for your situation.

Posted by Bernie at March 28, 2016 - 5:35 PM

You are right, because the date part of you is increasing, so the clustered index and the actual sequence, is consistent.

Posted by Bernie at April 08, 2016 - 6:26 PM

Compared with newid (UNIQUEIDENTIFIER) advantage is still a bit sequence, decreased to some extent makes the possibility index fragmentation.

Posted by Dana at April 18, 2016 - 7:02 PM

Value of type UNIQUEIDENTIFIER generation seems to be disordered ha, returns the result is disordered

Posted by Bernie at April 27, 2016 - 7:30 PM

In fact, not completely disordered
CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID()) 
INSERT INTO myTable DEFAULT VALUES
GO 10

SELECT * FROM myTable

/*
ColumnA
------------------------------------
360E7FF1-EF36-E311-BEAB-A41731BD8A1E
370E7FF1-EF36-E311-BEAB-A41731BD8A1E
380E7FF1-EF36-E311-BEAB-A41731BD8A1E
390E7FF1-EF36-E311-BEAB-A41731BD8A1E
3A0E7FF1-EF36-E311-BEAB-A41731BD8A1E
3B0E7FF1-EF36-E311-BEAB-A41731BD8A1E
3C0E7FF1-EF36-E311-BEAB-A41731BD8A1E
3D0E7FF1-EF36-E311-BEAB-A41731BD8A1E
3E0E7FF1-EF36-E311-BEAB-A41731BD8A1E
3F0E7FF1-EF36-E311-BEAB-A41731BD8A1E
*/

Posted by Dana at May 05, 2016 - 8:26 PM

OK, this can not only ensure the overall aggregation index and orderly, avoid page splits, and have certain readability

Posted by Albert at May 19, 2016 - 8:55 PM

So is continuous, but is not suitable for many kinds of situations
First, for sometimes I according to the needs of Mr. into a primary key, and then to the table in the insert, then it must be used to generate the newid (), or that the client using c# (Guid.NewGuid) to generate, it will destroy the ordering of the original, because the NEWSEQUENTIALID () this function only for the table field values of Default
And even without the case above, that as business needs in sync with the Oracle data, that because Oracle is not uniqueidentifier, only varchar that sort or disorderly in fall over.

So this is only one-sided, feel or not to play a role

Posted by Winnie at May 30, 2016 - 8:59 PM

Well, this I use is in fact a precedent, others have been used, whistling, it seems that I have nothing to worry about, you can have a look below, originally called "COMB (Combine)" type
You may have a look

Posted by Winnie at June 14, 2016 - 9:08 PM

If I were you, we design three fields, GUID is too long, never mind.
1 automatic numbering, each machine each number
2 GUID
3 the client ID, data source machine ID
Be careful:
Automatic number and sequence of each machine's data is not consistent,
But with an ID client data is consistent with the order.

Posted by Bartley at June 17, 2016 - 9:26 PM

Wanted to think, this is a problem, the problem is uniqueidentifier is sixteen m, only 16 bytes, and I like that is a string, using stored varchar (26), a character that is 2 bytes, 52 bytes, that was a waste of space, so it still doesn't work, these, after using "COMB (Combine)" type.
As for the efficiency, can refer to this brother's test

Posted by Winnie at June 24, 2016 - 9:42 PM

So I said the business key would be more appropriate

Posted by Dana at November 26, 2016 - 10:19 AM

Ask for advice, service key you say refers to as the "COMB (Combine)" type ?

Posted by Winnie at December 09, 2016 - 11:15 AM

May be, if a company I do ERP, inside the table almost with no increment key, is No. + other what to identify, the composite primary key even went to the Oracle is right. And there is no duplication or conflict basically

Posted by Dana at December 15, 2016 - 11:42 AM

Suggestions:
1 the use of business data as a primary key
2 each row of data increase the guid column, for data replication and migration. 

[AdventureWorks2012].[Sales].[SalesOrderHeader] It has a [rowguid] column. 

Posted by Denny at December 30, 2016 - 12:31 PM

Posted by Chelsea at January 02, 2017 - 1:02 PM

Why not just in time, bigint type

Posted by Douglas at January 09, 2017 - 1:10 PM

If the typical business system, are mostly based on creation time field on a clustered index.?

Posted by Albert at January 13, 2017 - 2:20 PM

I've seen it, but I also see little

Posted by Dana at January 14, 2017 - 1:29 PM

Oh, of course, can not be directly used in time, when the master table insert, insert a main table, from the table into multiple, hypothesis 10, that at that point in time, even if it is accurate to the subtle ffff the 10 generation ID will be repeated, causing insertion error acridine, maybe would you say that thread to sleep for a few milliseconds, that this is how bad. Think about it, or go to work will know, this is just one possible situation

Posted by Winnie at January 14, 2017 - 3:12 PM