0

I have a GUID with the GUID Column ordered in ASCE and being used as a clustered primary key. My question is can I use the Guid and explicitly order by the Guid column to bring back the result in time ordered fasion?

I am assuming it is in the correct order after looking at the createdOn fields which seem to be ordered correctly when I order by the GUID. I am guessing this is done because the GUID is already in time ordered as part of creating the GUID and that it has ASCE in it as well.

Will i definitely it in the right order by time or shall I use created date? I am guessing ordering by the GUID as the clustered Key would be quicker but can anyone give me your thoughts to go with that or created? Or will it give the same outcome which I am guessing it does.

The reason I need it in order is to assign a surrogate key for data warehousing by giving it a Big int (1,1) so it can be join on the surrogate keys rather than the natural keys which are the guids. But want to make sure the surrogate keys are always given to the correct guid by explicitly stating the order by

abs786123
  • 581
  • 2
  • 11
  • 24
  • The sorting algorithm is explained in http://stackoverflow.com/q/7810602/11683. Adding https://msdn.microsoft.com/en-us/library/ms189786.aspx to that, we can conclude the answer must be "yes, if all guids being sorted were created with `NEWSEQUENTIALID` within the same Windows session, otherwise no". – GSerg Feb 12 '17 at 20:02
  • I'm not following your reasoning on why you need this. Surrogate keys should be arbitrary and no meaning should be ascribed to them other than uniquely identifying that record in the data set. – Ben Thul Feb 12 '17 at 21:10
  • I thought that too Ben, that surrogate keys are no nonsense keys. But my manager said he wants the next layer up for surrogate keys to be used for joins and get away from using natural keys altogether. From that point surrogate keys will be used for joins. Which I was non the wiser – abs786123 Feb 12 '17 at 21:15
  • So just out of curiosity do surrogate keys have to tie into a particular guid or does it not matter at all? – abs786123 Feb 12 '17 at 21:20

0 Answers0