How to sort the GUID or Unique identifer column in SQL

Question

I want to sort the newID column by using ORDER BY, but when I try to order by the id is getting changed each and every time when I execute the query.

I have tired using the CAST operator for converting to VARCHAR and try to sort it. But it is not working.

declare  @temp table
(
    id int identity(1,1),
    newID UNIQUEIDENTIFIER
 )
insert into @temp
SELECT NEWID()
insert into @temp
SELECT NEWID()
insert into @temp
SELECT NEWID()
insert into @temp
SELECT NEWID()

select * from @temp 
select * from @temp order by cast(newID as varchar(40)) asc

id  newID
1   9653de71-34c2-4409-bcee-6809e170e197
2   3f3e7ab8-a516-4dd2-a04b-31feeac8fdea
3   1f1d38b8-3c31-4479-ba48-b71ce8525ea3
4   33f1e2b9-f4c3-4e57-9267-ff729a326318

id  newID
3   1f1d38b8-3c31-4479-ba48-b71ce8525ea3
4   33f1e2b9-f4c3-4e57-9267-ff729a326318
2   3f3e7ab8-a516-4dd2-a04b-31feeac8fdea
1   9653de71-34c2-4409-bcee-6809e170e197

The second table also I need to get sorted same like the first table when using ORDER BY statement.

There's no `order by statement`. Unless you specify an order with `ORDER BY` the server is free to return the data in any form it likes. If you wanted sequential GUID values use [NEWSEQUENTIALID()](https://learn.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql?view=sql-server-2017) instead of `NEWID()`. If you want to order the rows by ID, use `ORDER BY ID` — Panagiotis Kanavos, Aug 01 '19 at 09:50
BTW `IDENTITY` only specifies that the value is autogenerated, it doesn't turn `ID into a primary key nor does it create an index on it — Panagiotis Kanavos, Aug 01 '19 at 09:52
The second table i need to sort in ascending, As of now id is 3,4,2,1, But i need it as 1,2,3,4 — Daniel Stephen, Aug 01 '19 at 10:01
Why on earth you are sorting a table on VARCHAR column rather than integer column? — Ankit Bajpai, Aug 01 '19 at 10:03
becuase you cannot order by newid column, each time it change the order. Pls provide a solution which needs to be in the order 1,2,3,4 always,. You can change varchar or int anything but pls bring the output — Daniel Stephen, Aug 01 '19 at 10:07
Be aware of how [SQL Server orders guids](https://stackoverflow.com/questions/7810602/sql-server-guid-sort-algorithm-why). The byte sort order and byte/display order are different. — Dan Guzman, Aug 01 '19 at 10:10
if i use NEWSEQUENTIALID() instead of newid(), it is throwing error. Please let me know where i need to place query @PanagiotisKanavos — Daniel Stephen, Aug 01 '19 at 10:21
Can you please modify the query and make it sort ascending when i use order by. @DanGuzman — Daniel Stephen, Aug 01 '19 at 10:22
Am I being stupid or do you just need to `order` based on your `id` column here? `select * from @temp order by id` — iamdave, Aug 01 '19 at 10:23
Or are you saying you want the `id` column to simply return the row number of the sorted `newID` values? — iamdave, Aug 01 '19 at 10:25
`NEWSEQUENTIALID()` can only be used as the value of a `DEFAULT` constraint, it cannot be used explicitly in an `INSERT` statement. If you change your column to `newId UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID()` and your `INSERT`s to `INSERT INTO @temp DEFAULT VALUES`, you'll see sequential IDs. However, I suspect even that is not precisely what you *actually* need. Can you take a step back and explain why you're doing things with GUIDs and why the order is important, beyond this simplified example? — Jeroen Mostert, Aug 01 '19 at 10:38
@DanielStephen, `ORDER BY id` will sort in ascending order but the displayed sequence of byte values does will not match the order SQL Server uses for sorting. Although you could swap bytes for the displayed value such that it appears to match the sort order when read from left to right, that would not show the actual GUID values. — Dan Guzman, Aug 01 '19 at 10:39
Basically in our project we are using the GUID for each transaction, sometimes it can be null also. we are using has unique identifier for the column newid. after the inserting into a table, we want to get the same order has it was inserted in the table. But when we do order by for GUID column, it is randomly sorting. I want the same order as it was inserted in the table @JeroenMostert — Daniel Stephen, Aug 01 '19 at 12:59
That is not possible because such an order does not exist on a logical level. There is an on-disk order of the rows, but there is no way to get that order in T-SQL (nor is it really meaningful, as it could change based on updates and deletes). If you need a deterministic order based on when a row was inserted, you *must* use something like an `IDENTITY` column, a sequence or a time stamp so you can sort by that. A regular GUID is of no use in any case -- there is no chronological relation between GUIDs (at least not the version 4 GUIDs generated by most code). — Jeroen Mostert, Aug 01 '19 at 13:09
Again, just to stress this: the order you get back when you do `SELECT * FROM table` is *undetermined*. It could change, and *does* change, based on such things as whether the server chooses a parallel execution plan. To SQL Server, "insert order" is not a meaningful concept and the physical arrangement of rows is a happenstance. — Jeroen Mostert, Aug 01 '19 at 13:12
Ok fine i can understand, But we can't add a column for identity but let me check whether any column is available for timestamp. anyway thanks a lot @JeroenMostert — Daniel Stephen, Aug 01 '19 at 13:13

iamdave · Answer 1 · 2019-08-01T10:54:09.943

What I think you are trying to do:

I think you want to return your newID values, sorted in ascending order with an incrementing row number in the id column.

What I think you are misunderstanding:

The ID of a row does not need to be in any particular order, it just needs to be unique. If you are using the incrementing integer value of your id column as an identifier elsewhere in your solution, then you do not need to worry about the order. This is important because if you were to insert a new newID value that when sorted fell between two existing newID values, some of the id values would have to change to retain the ordering. This would break any relationships based on the id value.

It is important to note here that the int identity(1,1) value automatically increments (not always by 1) for each row as it is inserted. If you insert your data 'out of order' then the value will also be 'out of order'. I think you are misunderstanding what this functionality is for. In short, it doesn't do what you want it to.

You also can order a uniqueidentifier column as is. You will be getting an error because you have called the column newID, which is a reserved keyword within SQL Server. If you want to keep this name (which I suggest you don't), you will need to reference it within square brackets: order by [newID]. Bear in mind that the 'correct' ordering of a uniqueidentifier is not the same as the alphabetical ordering of the value you see on the screen, much like how the numeric ordering of 1, 2, 3, 10, 11, 12 is different to alphabetically ordering the same values as 1, 10, 11, 12, 2, 3.

How to actually get to your desired output:

If on the off chance you really do just want to get the row number of the newID value that is in your table, you can do this with the row_number windowed function:

declare @temp table(id int identity(1,1)
                   ,newID UNIQUEIDENTIFIER
                   );
insert into @temp(newID) values
 ('9653de71-34c2-4409-bcee-6809e170e197')
,('3f3e7ab8-a516-4dd2-a04b-31feeac8fdea')
,('1f1d38b8-3c31-4479-ba48-b71ce8525ea3')
,('33f1e2b9-f4c3-4e57-9267-ff729a326318');

-- Showing the int identity, which increments as new rows are added
select *
from @temp
order by [newID];

-- Using the row_number function to generate the id value at runtime
select row_number() over (order by [newID]) as id
      ,[newID]
from @temp
order by [newID];

Outputs

Using int identity:

+----+--------------------------------------+
| id |                newID                 |
+----+--------------------------------------+
|  2 | 3F3E7AB8-A516-4DD2-A04B-31FEEAC8FDEA |
|  1 | 9653DE71-34C2-4409-BCEE-6809E170E197 |
|  3 | 1F1D38B8-3C31-4479-BA48-B71CE8525EA3 |
|  4 | 33F1E2B9-F4C3-4E57-9267-FF729A326318 |
+----+--------------------------------------+

and using row_number:

+----+--------------------------------------+
| id |                newID                 |
+----+--------------------------------------+
|  1 | 3F3E7AB8-A516-4DD2-A04B-31FEEAC8FDEA |
|  2 | 9653DE71-34C2-4409-BCEE-6809E170E197 |
|  3 | 1F1D38B8-3C31-4479-BA48-B71CE8525EA3 |
|  4 | 33F1E2B9-F4C3-4E57-9267-FF729A326318 |
+----+--------------------------------------+

score 0 · Answer 2 · answered Aug 01 '19 at 13:09

NEWSEQUENTIALID() - This will not generate random sequence id for the GUID. We can use this one instead of Newid().

CREATE TABLE Product_A ( ID uniqueidentifier default NEWSEQUENTIALID(), productname int )

Insert Into Product_A(productname) values(1) Insert Into Product_A(productname) values(2) Insert Into Product_A(productname) values(3) Select * from Product_A

Select * from Product_A order by ID

I have used like this, but i want to use the newsequentialid in column wise not has default values in table creation. But it is not possible to use like that. Any suggestion to convert the newid() to newsequentialid because we can sort after the table insertion

Basically in our project we are using the GUID for each transaction, sometimes it can be null also. we are using has unique identifier for the column newid. after the inserting into a table, we want to get the same order has it was inserted in the table. But when we do order by for GUID column, it is randomly sorting. I want the same order as it was inserted in the table

How to sort the GUID or Unique identifer column in SQL

2 Answers2

What I think you are trying to do:

What I think you are misunderstanding:

How to actually get to your desired output:

Outputs