SQL Server 2005 introduced the NEWSEQUENTIALID() function, with some fanfare. I could never see it being interesting in any way, and I still don't.
The argument was that there were so many performance problems being caused by developers using GUIDs as primary keys in tables, and those primary keys also ended up being the clustering keys for the tables (doesn't have to be that way but that's the default behavior). The random order was then causing big fragmentation issues when INSERT operations were performed.
The logic was that NEWSEQUENTIALID() would give you an ever-increasing value that was like a GUID. But for me, it missed the main reason for the GUID values in the first place.
If you look at why people use GUID values, regardless of if it's to support cross database or server merges or replication or whatever, it's all about being able to generate a value that's unique without having to have any central thing that's issuing the numbers. By comparison, if I use an IDENTITY value or if I use a SEQUENCE as a default, I have to go to the database to get the value before I can use it.
With a GUID, I can just create one, assemble a bunch of things in memory that use it, then throw them all into the database without caring whether they'll collide with anything already in the database, or at least that's the thinking.
ie: the core logic is that it can be done outside the database and without referring to the database.
The fundamental problem with NEWSEQUENTIALID() (even ignoring its other flaws) is that the only way you can use it is as a default value for a column inside the SQL Server database. You can't even just SELECT it.
If you're going to go to the database to get a value, you might as well just get an int or a bigint in the first place. Getting a "pretend" GUID is of no value at all.