Rev 85 | Rev 88 | Go to most recent revision | Show entire file | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed
| Rev 85 | Rev 86 | ||
|---|---|---|---|
| Line 245... | Line 245... | ||
| 245 | <h3 id="gen_uuidv8_sqlserver">Generate SQL server sortable time-based (version 8) UUID</h3> |
245 | <h3 id="gen_uuidv8_sqlserver">Generate SQL server sortable time-based (version 8) UUID</h3> |
| 246 | 246 | ||
| 247 | <p><i>The sorting of UUIDs in SQL Server is rather confusing and incompatible with UUIDv6 and UUIDv7.<br> |
247 | <p><i>The sorting of UUIDs in SQL Server is rather confusing and incompatible with UUIDv6 and UUIDv7.<br> |
| 248 | Therefore this method developed by <a href="https://www.hickelsoft.de/">HickelSOFT</a> |
248 | Therefore this method developed by <a href="https://www.hickelsoft.de/">HickelSOFT</a> |
| 249 | generates UUIDs which are sortable by SQL Server.<br> |
249 | generates UUIDs which are sortable by SQL Server.<br> |
| 250 | They have a time resolution of 1 milliseconds combined with 18 bits of random data.</i><br> |
250 | They have a time resolution of 1 milliseconds combined with 16 bits of random data.</i><br> |
| 251 | <a href="https://gist.github.com/danielmarschall/7fafd270a3bc107d38e8449ce7420c25">C# implementation</a> | |
251 | <a href="https://gist.github.com/danielmarschall/7fafd270a3bc107d38e8449ce7420c25">C# implementation</a> | |
| 252 | <a href="https://github.com/danielmarschall/uuid_mac_utils/blob/master/includes/uuid_utils.inc.php">PHP implementation</a> |
252 | <a href="https://github.com/danielmarschall/uuid_mac_utils/blob/master/includes/uuid_utils.inc.php">PHP implementation</a> |
| 253 | </p> |
253 | </p> |
| 254 | 254 | ||
| 255 | <script> |
255 | <script> |
| Line 257... | Line 257... | ||
| 257 | document.getElementById("uuidv8_sqlserver_info_button").style.display = "none"; |
257 | document.getElementById("uuidv8_sqlserver_info_button").style.display = "none"; |
| 258 | document.getElementById("uuidv8_sqlserver_info").style.display = "block"; |
258 | document.getElementById("uuidv8_sqlserver_info").style.display = "block"; |
| 259 | } |
259 | } |
| 260 | </script> |
260 | </script> |
| 261 | <p><a id="uuidv8_sqlserver_info_button" href="javascript:show_uuidv8_sqlserver_info()">Show format</a> |
261 | <p><a id="uuidv8_sqlserver_info_button" href="javascript:show_uuidv8_sqlserver_info()">Show format</a> |
| 262 | <pre id="uuidv8_sqlserver_info" style="display:none">Version 2: Resolution of 1 milliseconds, random part of 18 bits, UTC time, 48 bit random "signature", UUIDv8 conform: |
262 | <pre id="uuidv8_sqlserver_info" style="display:none">Version 2: Resolution of 1 milliseconds, random part of 16 bits, UTC time, 48 bit random "signature", UUIDv8 conform: |
| 263 | - 16 bit Random data |
263 | - 16 bit Random data |
| 264 | - 8 bit UTC Milliseconds transformed from 1000ms to 0..255, deviation -2ms..2ms (hex encoded) |
264 | - 8 bit UTC Milliseconds transformed from 1000ms to 0..255, deviation -2ms..2ms (hex encoded) |
| 265 | - 8 bit UTC Seconds (hex encoded) |
265 | - 8 bit UTC Seconds (hex encoded) |
| 266 | - 16 bit UTC Minute of the day (1..1440, hex encoded) |
266 | - 16 bit UTC Minute of the day (1..1440, hex encoded) |
| 267 | - 4 bit UUID version 8 |
267 | - 4 bit UUID version 8 |
| 268 | - 12 bit UTC Day of the year (1..366, hex encoded) |
268 | - 12 bit UTC Day of the year (1..366, hex encoded) |
| 269 | - 2 bit UUID Variant (0b10) |
269 | - 2 bit UUID Variant (0b10) |
| 270 | - 2 bit Random data |
270 | - 2 bit Unused (must be zero) |
| 271 | - 12 bit UTC Year (hex encoded) |
271 | - 12 bit UTC Year (hex encoded) |
| 272 | - 48 bit Signature 0x5ce32bd83b96 |
272 | - 48 bit Signature 0x5ce32bd83b96 |
| 273 | 273 | ||
| 274 | Version 1: Resolution of 1 milliseconds, random part of 16 bits, local timezone, 48 zero bit "signature", NOT UUIDv8 conform: |
274 | Version 1: Resolution of 1 milliseconds, random part of 16 bits, local timezone, 48 zero bits "signature", NOT UUIDv8 conform: |
| 275 | - 16 bit Random data |
275 | - 16 bit Random data |
| 276 | - 8 bit Generator's local timezone Milliseconds transformed from 1000ms to 0..255, deviation -4ms..0ms (hex encoded) |
276 | - 8 bit Generator's local timezone Milliseconds transformed from 1000ms to 0..255, deviation -4ms..0ms (hex encoded) |
| 277 | - 8 bit Generator's local timezone Seconds (BCD encoded) |
277 | - 8 bit Generator's local timezone Seconds (BCD encoded) |
| 278 | - 8 bit Generator's local timezone Minute (BCD encoded) |
278 | - 8 bit Generator's local timezone Minute (BCD encoded) |
| 279 | - 8 bit Generator's local timezone Hour (BCD encoded) |
279 | - 8 bit Generator's local timezone Hour (BCD encoded) |