Should Users see ID's?

[schoemr]

Hi, what is best practice for ID's? I have userId's , usertypeId's, and more, and some datagridviews in my project have 3 columns of ID's.. Basically everywhere is IDs. It looks maybe nice but I maybe think to remove all ID's so it not visible. But then of some problem happens it will be difficulty for trouble shooting...? What is best practice? What is best method for making app looks smart?

Can some people please share views?

Thanks,
M

[GaryMazzone]

There should be no reason to display the IDs to the user. I normally just hide the column of the grid that they are located in. When I do joins I bring the IDs for each row back at the being of the SQL statement and hide them all

[Sitten Spynne]

A good rule of thumb is you should only show the user something if they absolutely need to know it. Anything else is clutter. A secondary rule of thumb is it can be OK to stash things in a secondary dialog, if they "sometimes" need it.

If it turns out the IDs are useful for troubleshooting, that second rule might be invoked. You might create some feature where if the user takes some action (like double-clicking a row), they get a quick dialog with the IDs for the row that you could use for troubleshooting.

But if the user is only using the IDs when something goes wrong, they really shouldn't be there. It increases the odds they'll somehow create a problem because the IDs are visible.

[Nirwanda]

I generally just keep stuff like this fully visible until the day I "ship" my application (ie begging everyone I know to try it)

[i00]

I use GUIDs instead of numeric IDs, I would suggest others do the same.

This is because a long time ago I had to make a database sync with a master DB ... and using seeded numbers is a pain to do this with since two machines can generate the same ID...
Changing to this after the initial implementation was a complete pain, as you can probably imagine...

If you use a code managed ORM ... such as Linq2SQL (as I do these days), this transition can be much much easier and less bug-prone; but the project I had to convert did not .

Now ... with this project it was incident management and they wanted an auto-incrementing number for the incident number... Basically the incident ID is actually a GUID, and the incident number is a nullable long.
It is nullable as the ID is generated to be the next incident number AFTER it hits the master server, i.e. if your on a device that is sync'ed to the master db, you do not get an incident number right away, it is null, then when the device syncs it gets the next incident number from the server as it syncs the record to it.

Also in my current program incidents numbers are the only kind of number that is "server generated" that is actually shown to the end user.

Kris

[schoemr]

Hi Kris,

Thank you for tell me about GUID. I never know about this before and now I wish I have know it when I start my project.

I was doing much web search for this topic of GUID and many website and at first I could not understand what GUID is really about. And then I find this explain and this person explain really good so that even person like me with no program background can understand. I will post this link here because maybe some other person can need it and find it here one day.

I have learn much things in very short time but sometime all this information at once is too much for me to process. In my language we call baby steps. But I hope to learn much more as I am just starting out. I not do programming for living, it just interest for me. Sometime good people here and like to satisfaction in helping others but also sometimes real jerks that team up against newbies and fight in lil packs like scruffy street dogs. That the downside I guess of this field.. But I am not afraid if lil men.

Once more thank you for tell me about GUID, I will surely study this more!

Michelle

GUID explained: https://betterexplained.com/articles...uide-to-guids/

[jmcilhinney]

I use GUIDs instead of numeric IDs, I would suggest others do the same.

There is a cost to using GUIDs over ints or longs so it's not something everyone should do just because. It's pretty much a must if you have IDs being generated at different sites, unless you use partitions to ensure no duplication, but for your average single-server and especially single-user system, there's no real benefit to using GUIDs.

[i00]

There is a cost to using GUIDs over ints or longs so it's not something everyone should do just because. It's pretty much a must if you have IDs being generated at different sites, unless you use partitions to ensure no duplication, but for your average single-server and especially single-user system, there's no real benefit to using GUIDs.

Yes but I think that GUIDs are more flexible for expansion later if needed, and on occasion I create GUIDs with "other" info in it too.
I have been asked to convert 3 projects over to sync over the years... Was only a pain with one project though as one used GUIDs to start with and another used an ORM so was easy to change the bits that needed changing.... Soooooo glad that I didn't need to go through the same converting pains with those projects.

Oh and a few years ago we had an office close down and we moved our clients transferred from one office to another (we had to merge databases), and have done this several times since... That would have been a complete pain if we used seeded numbers.

I think it is better to code for this as I have come across a need for this in EVERY commercial database that I have worked on!

Kris

[jmcilhinney]

I think it is better to code for this as I have come across a need for this in EVERY commercial database that I have worked on!

It's not something I've ever encountered. I'm certainly not trying to say that it wouldn't or shouldn't happen but, again, it's not something that most people will encounter much, if ever, so to suggest that everyone should use GUIDs just in case seems like bad advice to me.

[GaryMazzone]

GUIDs as Clustered Primary Keys in SQL Server are a performance hit see this by Kimberly Tripp (http://www.sqlskills.com/blogs/kimbe...lustering-key/). She is one of the people you should be listening to in SQL Server performance. This issue might be a little better if you use the SequentialGUID instead of the standard GUID.

I found using GUID as the PK and the FK to another table to be much slower than using a bigint data type. As for the setting the data from multiple computer back to a master you can set the starting point of the identity seed on any machine to a larger number say start 1 machine at -2,000,000,000 and move toward - 1,000,000. Start another machine at -1,000,000 and move to 0 on a third start at 0 and move toward 1,000,000 on the forth start at 1,000,000

[schoemr]

Hi Kris,

I know this off topic from my originally post, but I try to understand this GUID. I have create a simple tables in SQL Server:

Attachment 143721
Attachment 143723
Attachment 143725

And then I i try to work that into application. The first GUID is automatic insert. But for GUID2 (blue) this number is not automatically insert?

Attachment 143727

Thanks,
M

[GaryMazzone]

You create a default for the second GUID2 (DEFAULT = NEWGUID())

[schoemr]

thank you Gary

[Sitten Spynne]

One problem with auto-ID fields that rarely comes up but is a real bother when it does is they can overflow. Sure, it's really rare, but I've been in situations where it was feasible twice, and one of the two times cost a development team of several hundred people two whole weeks of work.

One time, I worked in a shop that used every aspect of the TFS system, from task management to continuous integration. It turns out that, by default, TFS will log every build error and warning generated by continuous integration builds. There were at least 300 developers, and the project was in the "million lines of code" range, so a typical build could easily generate several thousand warnings, with even more in failed builds. TFS was storing each individual build warning in an MSSQL table with an auto-ID integer row. It turns out it only took about a year for our team to overflow that row.

The result? TFS quit accepting builds, which meant no one could check in code. It turns out MS had failed to predict this scenario because deep in their list of hundreds of recommendations they stated, "You should turn this default off". The mitigation? They sent us a mysterious script that they asked us to run. So we did. It ran for 6 days, then crashed. We asked them what to do. They said, "Run it again". So we did. This time it ran for 6 days, and completed. Turns out the "solution" in the script was to turn off that logging setting and delete the table row-by-row before dropping it. Which takes a looooooong time.

(The other story's way less interesting, it's a project that receives billions of messages per year, will support more as time passes, and has an expected lifetime of "forever". I didn't want to be the source of a mysterious, business-halting failure in a system no one remembers 10 years from now.)

So yeah. Occasionally you might find yourself in a scenario where that matters, don't lecture me about the size of a 64-bit unsigned integer. At the very least, know what your database does if you reach that point. You might encounter it one day.

[techgnome]

The system I work on generates a lot of IDs as well ... we've got many tables that are in the million to billion row count - usually in the financial tables, where one transaction is supported by a multiple records in other tables (like the general ledger) ... and that's if there's no adjustments - which can easily generate even more ledger items (first you have to "undo" the previous set, then issue new lines based on the new information). To keep everything easy, the application platform just used GUIDs for all IDs.

Someone mentioned machine specific ranges - how do you manage that? I can see it being a possible solution for a small app that has a couple of users... but when you get into enterprise level situations, where you could have any where from a dozen to a couple thousand users... it just doesn't seem like a viable solution. Most, if not all of our clients expect their historical data to be converted into the new system too ... And while we recommend only rolling over the last 3 fiscal years, there are some that want the last 5 years, and one that wanted the last 10. owch.

I suppose in the end, it's just like anything else in programming - it depends... clearly in Sitten's and my case auto id ints (or longs) just wouldn't be feasible. In a pet project though, where there is only a couple users, then it might make sense. And, yes, looking to the future of what the program could become needs to play into that. At the same time, don't get hung up on it, and make sure the architecture is able to keep up.

-tg

[GaryMazzone]

I have used BIGINT for storing ID in finical system before. They worked with out issue. I found that not using sequential GUID caused table fragmentation that was hard to repair. You do get better results using Sequential but when the server of system restart that is lost and starts again. I have used BigInt as the data type with billions of rows before without issue.


The different ranges were not based on users but on database servers. I had to bring 5 different server back from regional areas and combine in the company headquarters. Each server had a range of IDs it used so when they came together in the main office there was no conflict.

-Gary

[i00]

GUIDs as Clustered Primary Keys in SQL Server are a performance hit see this by Kimberly Tripp (http://www.sqlskills.com/blogs/kimbe...lustering-key/). She is one of the people you should be listening to in SQL Server performance. This issue might be a little better if you use the SequentialGUID instead of the standard GUID.

I found using GUID as the PK and the FK to another table to be much slower than using a bigint data type. As for the setting the data from multiple computer back to a master you can set the starting point of the identity seed on any machine to a larger number say start 1 machine at -2,000,000,000 and move toward - 1,000,000. Start another machine at -1,000,000 and move to 0 on a third start at 0 and move toward 1,000,000 on the forth start at 1,000,000

I always use a guid that is formatted like thus so it is sequential for that exact reason:
yyyyMMdd-HHmm-ssff-xxxx-xxxxxxxxxxxx (x is random)

Kris

[topshot]

For Michelle, I don't think you should worry about using GUID.

As for your original questions, as others have said, only make visible what the user needs to see. In some cases you will want to show an ID (e.g., Project ID, invoice #, etc) but in many cases there is no point.

In terms of troubleshooting, you can write whatever you wish out to your exception logfile or database table so you can see all the relevant values.

[Sitten Spynne]

Yes, a big part of this GUID discussion is off-topic, if not interesting.

i00: That's one of those shaky things that could burn you one day. *shakes finger*

A proper GUID algorithm uses a lot of machine-specific information for a large part of the GUID. That means, in practice, two machines can literally never generate the same GUID because some bits of their entropy are fixed and different. (Not all GUID algorithms do this, and if you're using one of the "bad" ones it's "very rare" rather than "guaranteed".)

Your algorithm makes a large part of its entropy something that could easily be generated by two different machines. The rest is supposedly based on an RNG, which is time-seeded, which could also be identical between two machines. It makes me frown, though for 99% of cases I bet it won't burn you. This is a "computer science" critique but if you told me "I ran it for 1,000 years and had no problem" I can easily imagine programs that make that likely.

Really the important thing about this discussion is:

• Integers/autoincrement is fastest. It's appropriate for almost every application, and using GUIDs instead of those is superstition.
• There's often no good reason for users to see these IDs. Generally, visible IDs have some stricter scheme like "6-digit numbers" or "a combination of the user's name and some numbers". Example: in college, our student IDs were generally "initials + a number indicating the order of enrollment", so you could guess at least half of someone's ID from their initials.
• Sometimes you have reason to believe you'll break autoincrement. GUIDs are appropriate for IDs then. They have a performance hit, but that's better than "remember to run this script every 70 days to reset the autoincrement".
• Writing your own GUID algorithm is fraught with peril, it's about as safe as just using a random Integer unless you've published papers about generating unique IDs that were favorably peer-reviewed in notable journals.

[schoemr]

For Michelle, I don't think you should worry about using GUID.

thank you topshot

[i00]

The xxxx-xxxxxxxxxxxx in mine is the start of the GUID ... and I am yet to have a conflict (would also have to happen within the same millisecond)

Yes, a big part of this GUID discussion is off-topic, if not interesting.
A proper GUID algorithm uses a lot of machine-specific information for a large part of the GUID.

...Also this is incorrect... it only uses the MAC address - and the chance for duplication is actually quite high if you use a VM for eg with the same MAC address as another on another network... and yes this happens (Hyper V I am looking @ you!)...
That said I think that this is a security flaw as it also is reversible!... for V1 anyway...

... .Net and M$ generally use version 4: 5B815BB3-46D4-4E60-94E3-2505AF516598 ... which is PURELY RANDOM BYTES!

... So you would have a 1 in 18446744073709552000 chance of a duplicate PER ms ...
This is actually effectively LESS chance of a collision than the MS one (with a lot of data over time); as that will have a 1 in 21267647932558654000000000000000000000 (1^31 ... I took the version chr out since this will always be the same) .... but this one is against the entire dataset... not just ones in the millisecond!...

Also with the M$ method you will have the issue that GaryMazzone mentioned, basically non-sequential GUIDs as keys are really bad for clustered databases performance-wise (with a lot of data at least).

Kris

[wqweto]

... So you would have a 1 in 18446744073709552000 chance of a duplicate PER ms ...

For your yyyyMMdd-HHmm-ssff-xxxx-xxxxxxxxxxxx format you said xx is random byte and assuming ff is milliseconds you cannot encode full 0-999 interval in a single byte.

So probably you are using DATEPART(MILLISECOND, SYSDATETIME()) / 4 to reduce it to 0-249 range to be able to encode it in the ff byte.

If so the estimate for the duplicate is per 4 ms actually :-))

Cool trick nevertheless!

Edit: Just FYI, your format is *not* producing monotonously increasing GUIDs that would be very useful for integer IDENTITY keys replacement. The format has to be something like this

ddMMyyzz-mmHH-ffss-xxxx-xxxxxxxxxxxx

. . . which produces correct bytes order of 0xzzyyMMddHHmmssffxxxxxxxxxxxxxxxx where yyzz is the yyyy year big-endian encoded (bytes swapped).

Here is a sample code that works on SQL2000 and above

SELECT TOP 100 
        CONVERT(UNIQUEIDENTIFIER,
            CONVERT(VARBINARY(4), (YEAR(GETDATE()) / 255) * 0x1000000 + (YEAR(GETDATE()) & 255) * 0x10000 + MONTH(GETDATE()) * 0x100 + DAY(GETDATE()))
                    + CONVERT(VARBINARY(2), DATEPART(HOUR, GETDATE()) * 0x100 + DATEPART(MINUTE, GETDATE()))
                    + CONVERT(VARBINARY(2), DATEPART(SECOND, GETDATE()) * 0x100 + DATEPART(MILLISECOND, GETDATE()) / 4)
                    + CONVERT(VARBINARY(8), NEWID())) AS ID
        , CONVERT(VARBINARY(4), (YEAR(GETDATE()) / 255) * 0x1000000 + (YEAR(GETDATE()) & 255) * 0x10000 + MONTH(GETDATE()) * 0x100 + DAY(GETDATE()))
                    + CONVERT(VARBINARY(2), DATEPART(HOUR, GETDATE()) * 0x100 + DATEPART(MINUTE, GETDATE()))
                    + CONVERT(VARBINARY(2), DATEPART(SECOND, GETDATE()) * 0x100 + DATEPART(MILLISECOND, GETDATE()) / 4)
                    + CONVERT(VARBINARY(8), NEWID()) AS Raw
FROM    syscolumns

Might want to use SYSDATETIME instead of GETDATE on newer versions (esp. for the MILLISECOND parts).

cheers,
</wqw>

[i00]

ddMMyyzz-mmHH-ffss-xxxx-xxxxxxxxxxxx

Sorry - why would this be better for indexes? ... I mean it seems that that format would be bad for "index bucketing"?

Also you are right ... my ms is ff * 4 - F6 - FF are not used! ... so yea every 4 ms

Kris

[wqweto]

Sorry - why would this be better for indexes? ... I mean it seems that that format would be bad for "index bucketing"?

Opposite is true. Consider this sample query

SELECT  CONVERT(UNIQUEIDENTIFIER, Txt) AS ID
        , CONVERT(BINARY(16), CONVERT(UNIQUEIDENTIFIER, Txt)) AS ByteArray
FROM    (
        SELECT  '20181231-1200-0000-0000-000000000000' UNION ALL
        SELECT  '20190101-1200-0000-0000-000000000000' UNION ALL
        SELECT  '20190130-1200-0000-0000-000000000000' UNION ALL
        SELECT  '20190131-1200-0000-0000-000000000000' UNION ALL
        SELECT  '20190201-1200-0000-0000-000000000000'
        ) s(Txt)
ORDER BY ID

It uses your yyyyMMdd-HHmm-ssff-xxxx-xxxxxxxxxxxx format for dates from 2018-th and 2019-th and tries to order these.

Consider the result of the ORDER BY clause

ID                                   ByteArray
------------------------------------ ----------------------------------
20190101-1200-0000-0000-000000000000 0x01011920001200000000000000000000
20190201-1200-0000-0000-000000000000 0x01021920001200000000000000000000
20190130-1200-0000-0000-000000000000 0x30011920001200000000000000000000
20190131-1200-0000-0000-000000000000 0x31011920001200000000000000000000
20181231-1200-0000-0000-000000000000 0x31121820001200000000000000000000

(5 rows affected)

. . . and notice that first date 2018-12-31 has been ordered last.

The explanation is simple. Just take a look at the second column where these GUIDs are converted to to BINARY data-type and observe the actual bytes. The first DWORD is little-endian encoded (the default for x86 CPU architecture), so yyyyMMdd DWORD is converted to 0xddMMzzyy byte-array and the days become the most significant byte in the memcmp comparison the engine uses. Ouch!

My proposed format takes care of GUID data endianness so that the year becomes the GUID's 2 most significant bytes (*and* big-endian encoded) then 1 byte for months, 1 byte for days etc. so that memcmp used by ORDER BY and "index bucketing" is monotonously ever increasing.

cheers,
</wqw>

[i00]

The explanation is simple. Just take a look at the second column where these GUIDs are converted to to BINARY data-type and observe the actual bytes. The first DWORD is little-endian encoded (the default for x86 CPU architecture), so yyyyMMdd DWORD is converted to 0xddMMzzyy byte-array and the days become the most significant byte in the memcmp comparison the engine uses. Ouch!

The egs that you provided are all in order in both their binary data & the "date guid"...

Can you show me an example of when the two do not order the same?

Kris

[wqweto]

The egs that you provided are all in order in both their binary data & the "date guid"...

Can you show me an example of when the two do not order the same?

Did you see the results from the query I posted above? For instance the last two rows are 2019-01-31 and then 2018-12-31 -- is this in order for you?

Let me post the results from the query again so you can take a closer look at the first column where the encoded dates are clearly not in order.

ID                                   ByteArray
------------------------------------ ----------------------------------
20190101-1200-0000-0000-000000000000 0x01011920001200000000000000000000
20190201-1200-0000-0000-000000000000 0x01021920001200000000000000000000
20190130-1200-0000-0000-000000000000 0x30011920001200000000000000000000
20190131-1200-0000-0000-000000000000 0x31011920001200000000000000000000
20181231-1200-0000-0000-000000000000 0x31121820001200000000000000000000

(5 rows affected)

cheers,
</wqw>

[techgnome]

But... they are in order... for GUIDS... This was something I found with SQL Server... GUIDS don't sort like you would think they should...

Compare the results from the following quieries:

SELECT  CONVERT(UNIQUEIDENTIFIER, Txt) AS ID
        , CONVERT(BINARY(16), CONVERT(UNIQUEIDENTIFIER, Txt)) AS ByteArray
FROM    (
        SELECT  '20181231-1200-0000-0000-000000000000' UNION ALL
        SELECT  '20190101-1200-0000-0000-000000000000' UNION ALL
        SELECT  '20190130-1200-0000-0000-000000000000' UNION ALL
        SELECT  '20190131-1200-0000-0000-000000000000' UNION ALL
        SELECT  '20190201-1200-0000-0000-000000000000'
        ) s(Txt)
ORDER BY ID


SELECT  CONVERT(UNIQUEIDENTIFIER, Txt) AS ID
        , CONVERT(BINARY(16), CONVERT(UNIQUEIDENTIFIER, Txt)) AS ByteArray
FROM    (
        SELECT  '20181231-1200-0000-0000-000000000003' UNION ALL
        SELECT  '20190101-1200-0000-0000-000000000002' UNION ALL
        SELECT  '20190130-1200-0000-0000-000000000001' UNION ALL
        SELECT  '20190131-1200-0000-0000-000000000004' UNION ALL
        SELECT  '20190201-1200-0000-0000-000000000005'
        ) s(Txt)
ORDER BY ID

SELECT  CONVERT(UNIQUEIDENTIFIER, Txt) AS ID
        , CONVERT(BINARY(16), CONVERT(UNIQUEIDENTIFIER, Txt)) AS ByteArray
FROM    (
        SELECT  '20181231-1200-0000-0000-000000000005' UNION ALL
        SELECT  '20190101-1200-0000-0000-000000000004' UNION ALL
        SELECT  '20190130-1200-0000-0000-000000000003' UNION ALL
        SELECT  '20190131-1200-0000-0000-000000000002' UNION ALL
        SELECT  '20190201-1200-0000-0000-000000000001'
        ) s(Txt)
ORDER BY ID

I don't have the ability to run it, so I can't post the results, but.. if you were to run that, you should get three different results... the first sorted the way you're currently seeing it, the second should sort differently, and the third sorted reversed from the first. That's because SQL Server sorts GUIDS based on the last segment - for what ever reason ... threw me off the first couple of times I sorted a table by . GUID col,since I wasn't expecting it... never did find a reason for it, but didn't exactly work too hard at looking for one either.

-tg

[Shaggy Hiker]

TG showed me that some years ago.

Some of the discussion about creating GUIDs is a bit off. There was discussion of the date and the MAC address. Those types of GUIDs do exist, but the most common, current, type of GUID is the Type 4, where every byte is random aside from the type character. What you are talking about are the Type 1 and Type 2 GUIDs, which are not so common anymore. See these:

https://en.wikipedia.org/wiki/Univer...que_identifier

In any case, the ordering is ONLY on the last group, so anything you want to be sequential has to fit into that. As far as I can tell, if the last group is the same for two GUIDs, then the sort order may be the order of creation, or the order of last sorting, or something like that. If the last group is the same, the sorting doesn't then consider the next group, the first group, or any other group.

Interestingly, when I went to quick reply I had the option to Restore Saved Content. I hadn't typed anything, yet, but this thread is a few years old. I hit the restore, and it appears that I had been writing something about the performance of GUIDs, and decided not to post. That un-posted comment has been lingering in this thread for the last two years. It's been in limbo all this time, never quite posted, never quite deleted. Now, I shall finally open the box, and the cat shall live.

[wqweto]

But... they are in order... for GUIDS... This was something I found with SQL Server... GUIDS don't sort like you would think they should...

Which ones are in order? I'm dumping the result of the query with an ORDER BY so the results *are* in order :-))

But you are right and the GUID sorting in SQL Server is totally *not* memcmp compatible which is very bizarre. So my format above is totally not working too. Bummer!

Anyway, here is a link to SQLGuid.cs IComparable implementation and here is the lookup array for the order it uses:

        // Comparison orders.
        private static readonly int[] x_rgiGuidOrder = new int[16] 
        {10, 11, 12, 13, 14, 15, 8, 9, 6, 7, 4, 5, 0, 1, 2, 3};

Indexes 10 to 15 are the 5th (last) group of 6 bytes, 8-9 are the 4th WORD, 6-7 are the 3rd WORD, 4-5 are the 2nd WORD and 0-3 are the 1st DWORD and all of these are big-endian compared -- mind boggling!

cheers,
</wqw>

[techgnome]

They are in order based on how it sorts GUIDS... using the last segment... since the segments were all "000000000000" it "sorted" them in the order it had them in... the original order.

-tg

[wqweto]

The "original order" is not the alphabetical (so called lexicographical) order.

Here is another test query

SELECT  CONVERT(UNIQUEIDENTIFIER, Txt) AS ID
        , CONVERT(BINARY(16), CONVERT(UNIQUEIDENTIFIER, Txt)) AS ByteArray
FROM    (
        SELECT  '00000000-0000-0000-0000-000000000000' UNION ALL
        SELECT  '00000000-0000-0000-0000-000000000011' UNION ALL
        SELECT  '00000000-0000-0000-0000-000000001100' UNION ALL
        SELECT  '00000000-0000-0000-0000-000000110000' UNION ALL
        SELECT  '00000000-0000-0000-0000-000011000000' UNION ALL
        SELECT  '00000000-0000-0000-0000-001100000000' UNION ALL
        SELECT  '00000000-0000-0000-0000-110000000000' UNION ALL
        SELECT  '00000000-0000-0000-0011-000000000000' UNION ALL
        SELECT  '00000000-0000-0000-1100-000000000000' UNION ALL
        SELECT  '00000000-0000-0011-0000-000000000000' UNION ALL
        SELECT  '00000000-0000-1100-0000-000000000000' UNION ALL
        SELECT  '00000000-0011-0000-0000-000000000000' UNION ALL
        SELECT  '00000000-1100-0000-0000-000000000000' UNION ALL
        SELECT  '00000011-0000-0000-0000-000000000000' UNION ALL
        SELECT  '00001100-0000-0000-0000-000000000000' UNION ALL
        SELECT  '00110000-0000-0000-0000-000000000000' UNION ALL
        SELECT  '11000000-0000-0000-0000-000000000000'
        ) s(Txt)
ORDER BY ID

That produces this result

ID                                   ByteArray
------------------------------------ ----------------------------------
00000000-0000-0000-0000-000000000000 0x00000000000000000000000000000000
11000000-0000-0000-0000-000000000000 0x00000011000000000000000000000000
                                             ^ 3
00110000-0000-0000-0000-000000000000 0x00001100000000000000000000000000
                                           ^ 2
00001100-0000-0000-0000-000000000000 0x00110000000000000000000000000000
                                         ^ 1
00000011-0000-0000-0000-000000000000 0x11000000000000000000000000000000
                                       ^ 0
00000000-1100-0000-0000-000000000000 0x00000000001100000000000000000000
                                                 ^ 5
00000000-0011-0000-0000-000000000000 0x00000000110000000000000000000000
                                               ^ 4
00000000-0000-1100-0000-000000000000 0x00000000000000110000000000000000
                                                     ^ 7
00000000-0000-0011-0000-000000000000 0x00000000000011000000000000000000
                                                   ^ 6
00000000-0000-0000-0011-000000000000 0x00000000000000000011000000000000
                                                         ^ 9
00000000-0000-0000-1100-000000000000 0x00000000000000001100000000000000
                                                       ^ 8
00000000-0000-0000-0000-000000000011 0x00000000000000000000000000000011
                                                                     ^ 15
00000000-0000-0000-0000-000000001100 0x00000000000000000000000000001100
                                                                   ^ 14
00000000-0000-0000-0000-000000110000 0x00000000000000000000000000110000
                                                                 ^ 13
00000000-0000-0000-0000-000011000000 0x00000000000000000000000011000000
                                                               ^ 12
00000000-0000-0000-0000-001100000000 0x00000000000000000000001100000000
                                                             ^ 11
00000000-0000-0000-0000-110000000000 0x00000000000000000000110000000000
                                                           ^ 10

(17 rows affected)

I've annotated the byte offsets to confirm the order in x_rgiGuidOrder array above.

The second row of the results can be interpreted like this: the 0x11 part of the GUID in the first column goes to byte at offset 3 in the actual byte array (it's the 4th byte) and is the least significant byte for the comparison. The same for third row and so on.

The most significant part of the GUID is shown on the last row of the results. It's the 10th byte in the byte array and is the beginning of the 5th group of the GUID on the left (not the end of the 5th group).

Based on these results the proposed format for ever increasing GUIDs is xxxxxxxx-xxxx-xxxx-ssff-yyyyMMddHHmm.

cheers,
</wqw>

[dday9]

Why are we having this discussion on a thread from Dec 29th, 2016?

[ChrisE]

the DateTime in Post#24;26;27 part seems to allways be 12:00:00
so I can't see a proper Sort from that


try this with Now() and only the Date..19.01.2019

Option Strict On

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TextBox1.Text = CStr(Now)
    End Sub

    Public Function StoreDateTimeLocal(ByVal value As DateTime) As Int64
        Return value.ToUniversalTime().Ticks
    End Function

    Public Function ReadDateTimeLocal(ByVal value As Int64) As DateTime
        Return New DateTime(value).ToLocalTime()
    End Function


    Public Function StoreDateTimeB(ByVal value As DateTime) As Int64
        Return value.ToBinary
    End Function

    Public Function ReadDateTimeB(ByVal value As Int64) As DateTime
        Return DateTime.FromBinary(value)
    End Function


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        TextBox2.Text = CStr(StoreDateTimeLocal(CDate(TextBox1.Text)))
        TextBox2.Text = CStr(StoreDateTimeB(CDate(TextBox1.Text)))
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        TextBox3.Text = CStr(ReadDateTimeLocal(CLng(TextBox2.Text)))
        TextBox4.Text = CStr(ReadDateTimeB(CLng(TextBox2.Text)))

    End Sub

[wqweto]

the DateTime in Post#24;26;27 part seems to allways be 12:00:00
so I can't see a proper Sort from that[/CODE]

Why would that matter, provided that the more significant date part is different. . .

It's like having 1000123 and 2000123 but still somehow the "low" 123 is preventing comparison -- the difference is in the millions! :-))

cheers,
</wqw>

[ChrisE]

Why would that matter, provided that the more significant date part is different. . .

It's like having 1000123 and 2000123 but still somehow the "low" 123 is preventing comparison -- the difference is in the millions! :-))

cheers,
</wqw>

because just the Date 01.20.2019 would be useless for Banktransactions, there the Time part is a must

[wqweto]

the DateTime in Post#24;26;27 part seems to allways be 12:00:00
so I can't see a proper Sort from that[/CODE]

In every proposed GUID format in this thread there is time part, specified by the HHmm and ssff placeholders.

The time part is *not* fixed to 12:00:00 -- it's only sample data in these posts you are referring to that varies by the Date part so the Time part is irrelevant for the tests I'm running there.

cheers,
</wqw>

[ChrisE]

Hi,

yes your right I see that now

but still I would convert the DateTime part to put inside the GUID = 19.01.2019 11:47:15 would be 636834952350000000

the GUID could look like ...PREFIX-xxxxxxx-xxxxxxx-636834952350000000
probably just a matter of opinion

[wqweto]

but still I would convert the DateTime part to put inside the GUID = 19.01.2019 11:47:15 would be 636834952350000000

the GUID could look like ...PREFIX-xxxxxxx-xxxxxxx-636834952350000000
probably just a matter of opinion

Yes, that's a viable option. Using xxxxxxxx-xxxx-xxxx-1111-201901191629 is a bit more readable on the date part but a similarly arbitrary choice as 2019 in the 5th part of the GUID is in hex and is actually 8217 in dec. Strictly encoding 2019 in hex should have been 0x7E3 which is equally unreadable.

cheers,
</wqw>

[wqweto]

How many ways are there to sort GUIDs? How much time do you have?

Reymond explians even more details (as usual) than current discussion.

cheers,
</wqw>