0

Sorry for my English; in our SQL Server 2014 project, we have two tables that are synchronized between a web application and Android devices (they are therefore on all mobiles).

In our databases, these two tables are identified by unique identifiers of type integer as the primary key, but we also wanted a GUID single column that "follows" the data from its creation.

Our table A has these columns:

A_ID int
A_guid nvarchar (50)

And DetailA table:

detailA_ID int
detailA_guid nvarchar (50)
A_guid nvarchar (50) (on mobile only)

GUID columns are used in mobile applications and in synchronization on the SQL server, the "detail" data will look for the SQL Server ID corresponding to "A_guid":

select A_ID 
from A 
where A_guid = detailA.A_guid

All our data has been linked between them via SQL Server credentials. Of course everything works as long as the data is few. "GUID" will soon take a little time. All our GUID columns are in the format:

DEVICE_ID-YYYMMDD- [Function NEWID ()]

Apply an index is not very effective. I thought about changing the format to correspond to :

DEVICE_ID-YYYMMDD_HIS

But I do not know if we will gain speed.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fred
  • 23
  • 3
  • Speed for what? – Caius Jard Apr 19 '19 at 19:29
  • Have you read this post? https://stackoverflow.com/questions/13803326/use-of-non-clustered-index-on-guid-type-column-in-sql-server – David Tansey Apr 19 '19 at 19:36
  • 6
    If you are storing GUIDs why are you using varchar(50)? You should be using uniqueidentifier. – Sean Lange Apr 19 '19 at 19:38
  • A GUID is already globally unique (as per the name); adding a device ID and a date doesn't gain you anything. You'd be better off splitting this up into separate columns (with, per Sean, `UNIQUEIDENTIFIER` for the GUID) and then you can index those columns. You can use a computed column if you need a single string representation of that value, but you shouldn't really need it. If you have a way of actually generating an increasing unique value per device (like the time stamp accurate to a millisecond, for example) that value would index better than a GUID, so you might not need it at all. – Jeroen Mostert Apr 19 '19 at 20:37

2 Answers2

1

As my mates said, if you want to use GUID, then use uniqueidentifier which is a known datatype in SQL Server.

If GUID is not enough for your requirement, but you need to involve more information with it. I would suggest to create an informal table that will hold all required information in separate columns with the correspond GUID. This is better than concreting the GUID in a string with other values.

So rather than doing : DEVICE_ID-YYYMMDD- [Function NEWID ()]

You'll be better doing this : INSERT INTO informationTable (DeviceId, CreatedDate, RecordGUID)

iSR5
  • 3,274
  • 2
  • 14
  • 13
0

As a GUID (Globally Unique Identifiter) is, by definition, unique, you should not need to include DEVICE_ID in the guid columns. If you need that data, store it separately in its own column. This, as mentioned in the comments, will allow you to use the uniqueidentifier datatype, which will improve performance a bit. Definitely, build an index on the guid column, it will be much faster than no index!

(I’m not sure what _HIS refers to, but adding extra characters is not going to make things faster.)

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Thanks for your answer. The DEVICE_ID is used to know the device which has created data. In project all GUID has generated by the same format. We thought that the format which use _HIS (hour, minute, second) would be speeder because it was only number (with this we wouldn't use SQL Server NEWID function anymore but a custom one). – Fred Apr 19 '19 at 20:46