InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 02:16:17 GMT20http://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]David.Poole (10/5/2011)[/b][hr]Went to an astonishing presentation by Thomas Kejser at SQL Bits where he demonstrated that IDENTITY and in fact NEWSEQUENTIALID() is far slower that GUIDs on SSDs and this is exacerbated by NUMA architecture.[/quote]I think you are referring to [url]http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generating-scalable-keys/[/url] which is itself an update to [url]http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/09/22/resolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx[/url].It's quite wrong to suggest that IDENTITY or NEWSEQUENTIALID 'are slower' than GUIDs without mentioning that this is only important in the most extreme of circumstances. For the 99.9999% of us who aren't bound by latch contention on the last page of an index on extreme Fusion-IO hardware, other more normal factors such as page splits and non-INSERT performance are likely to be much more of interest than esoteric page latch or sub-latch issues. That's not to take anything away from Thomas' great work here - but it is important to retain a sensible perspective when considering his results.Wed, 05 Oct 2011 21:55:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxWent to an astonishing presentation by Thomas Kejser at SQL Bits where he demonstrated that IDENTITY and in fact NEWSEQUENTIALID() is far slower that GUIDs on SSDs and this is exacerbated by NUMA architecture.The gist of it is that SQL Server has to keep track of where a sequential range is and this means marshalling across all parallelised resources which is very expensive. It is also has something to do with the tail of an index page being used.Unfortunately the acoustics in hall were poor so if any of you attended his session then please fill in the blanks.Keep an eye out for his presentation on the SQLBits website http://sqlbits.com/Sessions/Event9/Finding_the_Limits. The presentation isn't there yet but will be uploaded soon.For those of you who haven't seen one of Thomas's presentations, he works at the extreme end of SQL Server performance and storage. What most people probably regard as science fiction!24TB cube with 1,000 concurrent users and sub-second response times.Wed, 05 Oct 2011 16:31:28 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxMaybe you are interested how to generate sequential guids on the client, as well as other answers/discussions regarding Guids:[url=http://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid/1042719#1042719]Generate Sequential Guids[/url]I hope this is interesting for you.Kind regards,Bernhard KircherWed, 05 Oct 2011 00:02:28 GMTbernhardkircherhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxThanks for taking the time to reply StuR. It does help although I did mostly understand those issues already. Difficulty I have as a developer is that (and someone already alluded to - or just plain stated - this already) I am constantly coming up against [i]solutions[/i] that folk have created in isolation of all other aspects of a system. Often these are just sheer tunnel vision but many times are driven by shortsightedness on the part of management.Wed, 28 Sep 2011 05:05:42 GMTjh-638585http://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]iposner (9/27/2011)[/b][hr]A common problem among developers is an unwillingness to master the entire software stack. If you want great software, you must develop expertise in all layers including DB and OS.[/quote]Plus 1 to that. I am constantly amazed by otherwise smart people on both sides of one software divide or another that are unwilling to understand things that directly affect what they do, and in many cases software projects live or die by these decisions.Tue, 27 Sep 2011 04:58:13 GMTStuRhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]jh-638585 (9/27/2011)[/b][hr]Loved the article and, even more so, the fact that your brainwave has provoked such excellent feedback (haven't read it all yet mind).I'm on the programmer-side and, therefore, no DB expert but while I understand the issues associated isn't one of the key things about [i]Identity[/i] columns that no matter what client (any number of bespoke clients or apps like Excel, etc.) attempts to insert data the uniqueness of the id field will be guaranteed [i]by the database[/i] (single instance).Set me straight here but isn't the alternative that all apps (app developers) must know more about the internal storage of data and isn't that 1. a bad thing 2. open to be abused.Perhaps you suggest all [i]identity/identifier[/i] fields should be calculated somehow using an appropriate guid-type creation function from within the database?I have seen apps that use all sorts of unique record identifier functions that must be called by the client app before inserting data. I have seen many problems with all of these implementations but the consistent thing i noticed was that it was always difficult for other apps to use - most seems to decide that [i]this database[/i] is only to be access from [i]this application[/i] or instances of it. Regardless of database scalability and all that good stuff the idea that any significant database would be accessed by a single application (even a single language) over the course of the lifetime of the data is....[i]limiting[/i].Again, very much enjoyed this [i]lateral thinking[/i] article.[/quote]Hey jh, you are correct in what you assert for a single server environment. The issue here is really what happens when a database table spans multiple SQL Servers, either for temporary capacity or as a part of a failover procedure.It was only a few years ago that this sort of argument was not even contemplated due of cost except in the biggest systems but with the dropping price of hardware & hosting, along with the increasing abilities of SQL Azure, it is becoming more and more of a consideration.Even then the partitioning of a table ("vertical" partitioning rather than "horizontal" I guess) is really only an issue when you need to bring some or all of the partitioned data back onto a single DB server. Where a system is partitioned across multiple SQL Servers for temporary capacity increase, or when a DR site temporarily receives live transactions are examples of this. With identity columns on each server this means you almost always have key collisions.There are ways to get around this, and the guid, sequential guid and generated ID methods are just some of them.Hope this helps!Tue, 27 Sep 2011 04:55:49 GMTStuRhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote]You're absolutely correct! Only several 3, 4, 5, and 6 letter swear words/phrases and just a couple of combinations of double 3 letter swear words and only a few "U" swear words can be spelled out.[/quote]To be honest though we have been running this scheme for a few years (around 300,000 orders since we moved to using it) and although a few have appeared we have never had any comments. I suppose it is quite childish I guess to "spot" swear words in order numbers ;-) - and especially with the hypens they become less obvious.I am the business owner so no one is going to shout at me either!Tue, 27 Sep 2011 01:56:51 GMTnick 2435http://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxA common problem among developers is an unwillingness to master the entire software stack. If you want great software, you must develop expertise in all layers including DB and OS.Tue, 27 Sep 2011 01:55:28 GMTiposnerhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxLoved the article and, even more so, the fact that your brainwave has provoked such excellent feedback (haven't read it all yet mind).I'm on the programmer-side and, therefore, no DB expert but while I understand the issues associated isn't one of the key things about [i]Identity[/i] columns that no matter what client (any number of bespoke clients or apps like Excel, etc.) attempts to insert data the uniqueness of the id field will be guaranteed [i]by the database[/i] (single instance).Set me straight here but isn't the alternative that all apps (app developers) must know more about the internal storage of data and isn't that 1. a bad thing 2. open to be abused.Perhaps you suggest all [i]identity/identifier[/i] fields should be calculated somehow using an appropriate guid-type creation function from within the database?I have seen apps that use all sorts of unique record identifier functions that must be called by the client app before inserting data. I have seen many problems with all of these implementations but the consistent thing i noticed was that it was always difficult for other apps to use - most seems to decide that [i]this database[/i] is only to be access from [i]this application[/i] or instances of it. Regardless of database scalability and all that good stuff the idea that any significant database would be accessed by a single application (even a single language) over the course of the lifetime of the data is....[i]limiting[/i].Again, very much enjoyed this [i]lateral thinking[/i] article.Tue, 27 Sep 2011 01:45:19 GMTjh-638585http://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]nick 2435 (9/20/2011)[/b][hr][quote]Interesting. What are you using to prevent the spelling out of random swear words?[/quote]Not thought of that one - but no one has ever reported any! There's very few that can be spelt with those letters anyhow :-)[/quote]You're absolutely correct! Only several 3, 4, 5, and 6 letter swear words/phrases and just a couple of combinations of double 3 letter swear words and only a few "U" swear words can be spelled out. You should be fine and I'm sure that the users that receive an SO with a swear word in it will marvel at the technical prowess of your company and are sure to create many additional orders with the company just to see which swear words will be generated. Of course, they'll also tell all of the people and maybe even some lawyer friends they know so they can all join in the fun. Yep... I'm sure it'll all be fine and your boss will actually congratulate you for providing so much fun for everyone especially those sour-puss lawyers. ;-)At least remove the vowels and the letter "K" from the mix.Sat, 24 Sep 2011 18:49:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote]Interesting. What are you using to prevent the spelling out of random swear words?[/quote]Not thought of that one - but no one has ever reported any! There's very few that can be spelt with those letters anyhow :-)Tue, 20 Sep 2011 01:11:30 GMTnick 2435http://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]nick 2435 (9/15/2011)[/b][hr]We changed the SO numbering here to be a long set of letters and numbers that are part random and part sequence (based on time) which means the computer just “makes it up” rather than grabbing the next one. What we did was build a 15 digit order number in the following format, using essentially [b]base 30 [/b]- built from 30 easily distinguisable characters (23456789ABCDEFGHJKLMNPRTUVWXYZ)S[b]123-TYP[/b]-QRT-6RE[/quote]Interesting. What are you using to prevent the spelling out of random swear words?Mon, 19 Sep 2011 21:17:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxWell the size is half the size of the GUID.I'm not sure how GUIDs are generated but I'm willing to be there is substantially more calculation involved albeit highly efficient and optimised calculation.As to not being able to come up with something better than GUIDs that implies that GUIDs are at the pinacle of identity generation and can never be surpassed.I have heard anecdotal evidence that GUIDS are only guaranteed unique on the machine on which they are issued. It is improbable that you will get two systems generating contradictory GUIDs but not impossible for it to occur.Just as a matter of interest how do you handle multiple data centres, particularly where there is a large geographic distance between them?Mon, 19 Sep 2011 16:03:31 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]iposner (9/19/2011)[/b][hr]Sergiy - What I'm trying to say is that the original idea of coming up with a more efficient globally unique identifier is good. However its usage in order to come up with a non-standard operational procedure for DR is deeply flawed.[/quote]I cannot see anything "more efficient" in that new identifier.Same size of the value, not very efficient process of generation, additional checks required to prevent duplications...And it's all in sake of succession. As SQL Kiwi (cheers mate!) said - evil is not in GUID, evil is in clustered key on GUID column. It's another extremely bad practice, so common that you'll say I should not challenge it.But I still do.You'll never select a range of GUID's, so it should be [b]never [/b]used for a clustered index.Problem with GUID's is not in lack of succession.It's in architectural flaw of using internally auto-generated identifiers for global identification of business objects. No matter how smart will be your auto-ID it will never be good for the purpose.Mon, 19 Sep 2011 15:37:06 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]iposner (9/19/2011)[/b][hr][quote][b]Colin Barry (9/19/2011)[/b][hr]Thought provoking.Why have a service issue the GUIDs? A small bit of clr code could do what you need and would be fast. You could then let your insert stored procedures transparently create the "identity" of the record and return it to your middle tier layer.[/quote]Absolutely agree - although SOA may be a buzzword on many people's lips, just because you can do something doesn't mean that you should: Not only does the cross-network and cross-process intercommunication introduce additional latency, but the additional components involved in implementing an additional service reduce the MTBF of the entire system.[/quote]Don't confuse SOA with web services. In this case - "service" is just as valid a name for the CLR code as if would be for the web service call previous advocated. SOA also has nothing to do with whether the functionality is "local" or "remote".In short - both would be SOA if the purpose is reusability of the identifier (GUID) functionality.Mon, 19 Sep 2011 09:21:37 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]Colin Barry (9/19/2011)[/b][hr]Thought provoking.Why have a service issue the GUIDs? A small bit of clr code could do what you need and would be fast. You could then let your insert stored procedures transparently create the "identity" of the record and return it to your middle tier layer.[/quote]Absolutely agree - although SOA may be a buzzword on many people's lips, just because you can do something doesn't mean that you should: Not only does the cross-network and cross-process intercommunication introduce additional latency, but the additional components involved in implementing an additional service reduce the MTBF of the entire system.Mon, 19 Sep 2011 08:34:40 GMTiposnerhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxThought provoking.Why have a service issue the GUIDs? A small bit of clr code could do what you need and would be fast. You could then let your insert stored procedures transparently create the "identity" of the record and return it to your middle tier layer.Mon, 19 Sep 2011 08:26:43 GMTColin Barryhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxSergiy - What I'm trying to say is that the original idea of coming up with a more efficient globally unique identifier is good. However its usage in order to come up with a non-standard operational procedure for DR is deeply flawed.Mon, 19 Sep 2011 05:26:03 GMTiposnerhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxispooner,Does anything from your post make the approach less wrong?If not, then what was that about?I'm aware of the common practices.Cursors, loops, denormalized tables, etc. - used as common practices by 90% of "normal" developers.Does it justify use of those approaches?Mon, 19 Sep 2011 05:04:20 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxSergiy - It isn't the job of programmers/developers to reinvent the wheel of infrastructure: There are a number of infrastructure topologies that already exist to deal with datacentre destruction scenarios, including database mirroring, log shipping and clustering.In the assessment of any disaster recovery (DR) strategy, there are trade-offs that have to be made. These include cost, service levels of how long it should take to failover, how much data it is tolerable to lose and how much of a performance hit one is willing to take if one opts for a synchronous data replication topology (be that at SAN level, synchronous mirroring or otherwise) if there is zero tolerance for data loss. Remember also that a DR strategy is one that should be implemented only when there is a total destruction of the primary site environment, so a well run operational department will probably only every implement the plan as part of a DR training exercise.Again, as smart as programmers think they are, the people that design these products are much more familiar with the issues surrounding DR (including the need for standardisation of DR plans across systems) than programmers are and they see DR in the context of enterprise operational procedures, not as custom processes for individual applications.In short - if you went into any well-run DBA team and told them you had a different process to implement for DR than the other hundred or so systems they had to support, they'd tell you where to go.Mon, 19 Sep 2011 02:55:08 GMTiposnerhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxWhat about this scenario:[quote]Site A is functioning well and persists orders 1 to 10Site B is passive but kept up-to-date so effectively it has copies of orders 1 to 10.Lightning strikes [b]after order 10 is placed on Site A but before it's copied to Site B[/b].Site B becomes active while repairs take place on Site ABecause Order 10 is not available from Site B User has to resubmit it.It happens not straight away, so the which was Order 10 on Site A gets number 12 on Site B.At the same time another, totally irrelevannt order takes Number 10 on Site B.[/quote]Now, what will happen when Site A comes back to life?GUID will prevent orders "No.10" from overlapping, but same order with No.10 on Site A and No.12 on Site B will still be duplicated.There will be 2 identical deliveries instead of one, 2 following invoices instead of one, missing payment, penalties, disputes, etc.Whole idea of using GUID or any other kind of auto-generated "across different instances" identifiers is faulty.Unique entries of orders or any other business objects must be identified by natural keys defined by business logic, nothing else.IDENTITY is perfectly good as an internal identifier within a database.Just don't include it into replication process. Let target database generate its own IDs'.Sun, 18 Sep 2011 21:25:00 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote]Since there tend to be many more FK records than PK records in your average database structure, the bloat compounds further, impacting performance as it does so.[/quote]Wow, thank God someone finally brought that up! I've dealt with this situation before as well with many different "remote" databases synchronizing into 1 central database with... composite keys. The only thing that could be done was to roll our own synchronization app. The central store had to "re-key" EVERYTHING to it's own server ID and then send those keys back out to the remote servers. Fun!Fri, 16 Sep 2011 02:47:57 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]gahayden (9/15/2011)[/b][hr]I have to agree with SSC Eights. Why not use a composite key?For many years, when storage was expensive, single fields were often used to depict mutiple entities and there continue to be numerous examples of this in accounting and manufacturing. Similar stratagy was applied when DOS limited file naming to 8 characters.Why go back there? What's wrong with 2 fields?[/quote]Everything's wrong with two columns: You see, it's not an issue of storage, but the performance impact of using suboptimal datatypes. Using more than one column compounds the problem as both columns, if used as a primary key, have to be present in foreign key columns referencing the primary key. Since there tend to be many more FK records than PK records in your average database structure, the bloat compounds further, impacting performance as it does so.Thu, 15 Sep 2011 16:50:17 GMTiposnerhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxround trip of scope_identity()???? it's a return value of your method call. Please don't feed the bears.Thu, 15 Sep 2011 16:06:37 GMTtelcogodhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxTo answer a few points raised in this discussion thread: -Good spot on the 84,400. It is a typo but the figure for 4 and 40 years respectively are correct.This illustrates why it is a good idea to write up the full experiment and show your working as the entire thing is up for peer review.Good spot also on the time synchronisation. This suggests we need three things1. An understanding of when the clocks get resynced and how.2. A mechanism for pausing the key generator in the event of it getting wound back3. An alerting mechanism should the server clock get too far our of sync with the master clock which indicates you have a hardware problem on your server.If obfuscation of the key is required then this will only apply when the key is exposed by the application and then it is the responsibility of the application to present the key in the obfuscated manner. It shouldn't affect the actual value of the key.I'd 2nd the comment that you shouldn't have to develop a coding solution to an infrastructure problem but I don't think standing back and saying "not my problem guv" is the answer either.I've been in the situation where a large legacy estate had to be shifted to separate data centres in a very short period of time. The only way this could be achieved in the timescales was by adding a ServerID field to every non-reference table. The stored procs had to do a lookup of the @@SERVERNAME against a master table of servers to determine the serverID. Not nice, and it broke when the server naming policy changed causing horrendous difficulties.The 14th byte does not have to be used to signify servers, it merely signifies the key generator being used and in this case it is 1 of 10. In my case syncing up the data centres in real time is a challenging proposition.Geoclustering is one option but it isn't a panacea for all ills.Mirroring has caused us problems with IO for busier DBs and I'm not sure how happy I would be putting high transaction loads across a mirror with a few hundred miles between data centres.It is a bit off topic but I'm firmly of the opinion that you have to design your resilience, DR and failover solution as a foundation stone. It isn't something you can bolt on later.Why don't I use a composite key?Because the data model gets very complicated very quickly and actually the component parts are not of any interest. They serve solely to provide a mechanism for generating a key.The purpose of the key generator is to provide as generic a generator as possible. It doesn't rely on a particular DB platform and as we have a hetrogenous environment it opens up a range of possibilities.We did consider having multiple DB servers all incrementing by 1000 but starting from different seed points. It works well for most circumstances but you have to make sure that you don't wreck the seed value accidentally.Realistically you can push a vast amount of traffic through SQL Server before you need to worry about an external key generator. either that or you move to a cloud solution where you have lots of small independent nodes all doing their own thing.If you cluster a GUID then you deserve everything that comes your way. Even if you don't cluster them you get huge fragmentation issues they just don't impact as great as when they are clustered.I found out after writing this article that a number of systems use a very similar routine for key generation which is extremely reassuring as external validation always is.Thu, 15 Sep 2011 14:11:00 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxThe question of fragmentation is an interesting one. Assuming we don't use a GUID as the clustering key, what's the issue here? It is in the nature of non-clustered indexes to fragment - which is why FILLFACTOR exists.Queries against a GUID column are overwhelmingly likely to be singleton lookups - which aren't affected by fragmentation. I can't think of a scenario where one would want to range-scan GUIDs - and it's disk read-ahead performance for (partial) scans that can suffer if fragmentation is high.As far as size is concerned, sure a GUID is 16 bytes - the same size as a single NCHAR(8) column, or two BIGINT columns - but that's really not that big. Just using row-versioning adds 14 bytes per row and we don't hear too many complaints about that.Like everything, GUIDs have their advantages and disadvantages. There will certainly be edge cases where it makes sense to invent a new keying system, but the standard range of tools seem perfectly adequate for most scenarios.Thu, 15 Sep 2011 12:46:04 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxThanks for putting this article together.Thu, 15 Sep 2011 12:38:18 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]sqlservercentral-622386 (9/15/2011)[/b][hr]If you want to avoid the perfromance problems of using GUIDs as keys, and you do your security properly, then use sequential GUIDs which are already built into SQL Server for preciseley this purpose. If you don't want to generate the ids in-database, the same funciton is available on the client, or app layer, in windows api.SQL: NEWSEQUENTIALID()Client: UuidCreateSequential() [/quote]The problem with UuidCreateSequential in a distributed environment is you will still get fragmentation. Your data will all cluster together according the client that generated the id instead of always appending to the end of the index. That's why I wrote my own [url=http://www.developmentalmadness.com/archive/2010/10/13/sequential-guid-algorithm-ndash-improving-the-algorithm.aspx]Comb algorithm[/url], to allow distributed ids that reduce fragmentation. You'll always have some fragmentation if you're generating your ids on the client because you can't guarantee the order they will be inserted in because you're looking at a race condition. However, in my opinioin UuidCreateSequential is only useful when you have a single source that is generating the ids.Thu, 15 Sep 2011 11:58:19 GMTdevelopmentalmadnesshttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxI have to agree with SSC Eights. Why not use a composite key?For many years, when storage was expensive, single fields were often used to depict mutiple entities and there continue to be numerous examples of this in accounting and manufacturing. Similar stratagy was applied when DOS limited file naming to 8 characters.Why go back there? What's wrong with 2 fields? Thu, 15 Sep 2011 10:50:17 GMTgahaydenhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]mark hutchinson (9/15/2011)[/b][hr]PhilRather than recreating the UNIX epoch problem of a ~40 year clock cycle, use a combined key.[/quote]Then again, aren't 128 bit bigints on their way already for some next version of SQL Server? This should take care of another 60x 2^64 years or am I too easy on the math here?Anyway, the first thing I thought when I read this article was sequential guids, as some fellow posters already posted but I couldn't find a reference to a usable implementation so here's what I used in my previous company. All honors for this go to Leonid Shirmanov who put it on the web: [url]http://www.shirmanov.com/2010/05/generating-newsequentialid-compatible.html[/url]Leonid's example uses the UuidCreateSequential function from the Windows API: the same as SQL Server uses when you default a column's value to NEWSEQUENTIALID(). So it's rock solid.The example from Leonid's site helped me to reduce a busy server's CPU from averaging 40% to averaging 3%. Page splits went down and performance went up. You can put it in a CLR UDF, and/or have your apps use it in code, or put an assembly into the GAC so you can use it anywhere.The only downside is that this still uses 16 byte GUIDs as opposed to 8 byte integers, the upside is that a client app doesn't need to call an external service.Thu, 15 Sep 2011 10:18:20 GMTJosbournehttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]Martin Bastable (9/15/2011)[/b][hr]If you are using identity columns and are using multiple servers, you can set each server to use its own `bank of numbers`. E.g.Server1: identity seed = 00,000,000,000Server2: identity seed = 10,000,000,001Server3: identity seed = 20,000,000,001etc.[/quote]I think the point of this article is to avoid doing something like this. What happens if you happen to run out of numbers within a particular bank, or use a partitioning scheme that eventually exhausts the available numbering space? With the scheme that the article proposes, you know that you have a large timeframe in which you don't have to worry about numbering conflicts.Thu, 15 Sep 2011 10:07:47 GMTjbnvhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxPhilGlad I could help. I also think I spotted a typo in the article, but upon a re-read, don't see it.Rather than recreating the UNIX epoch problem of a ~40 year clock cycle, use a combined key.2958465 is the long integer value for #12/31/9999# (9999-12-31 ISO date)This can easily fit into three bytes. If you make this the high order three bytes of the key, followed by the milliseconds for that day (since midnight), you get a sequential key good until the Microsoft date epoch. If you wanted more random data, you could make the date data the low order bytes.If security is a concern, you could encrypt the datetime stamp.Alternatively, you could go with a multi-column key with the different database server identification being one of the columns and a regular row identity value being the other (PK) column.btw...there's a good Y2K joke about Bill Gates being awakened from cryogenic suspension just in time to help society with the Y10K crisis.Thu, 15 Sep 2011 09:47:35 GMTmark hutchinsonhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxIf you are using identity columns and are using multiple servers, you can set each server to use its own `bank of numbers`. E.g.Server1: identity seed = 00,000,000,000Server2: identity seed = 10,000,000,001Server3: identity seed = 20,000,000,001etc.or:Server1: identity seed = 1,000Server2: identity seed = 1,001Server3: identity seed = 1,002increment set to 1,000etc.Note the above is an example, you would of course plan on your range size, projected usage, etc. to make sure server number usage didn't crash into each other :)Then if a server goes down and the others keep going, there will not be a problem with clashes when you sync your databases back up again.Of course the above isn't a perfect solution, but it's an option, fast, simple, etc.M. :)Thu, 15 Sep 2011 09:28:31 GMTMartin Bastablehttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]mark hutchinson (9/15/2011)[/b][hr]There are 86400 seconds in a day, not 84400 that you stated in the article. You should probably change some numbers.[/quote]Well spotted!Thu, 15 Sep 2011 09:11:32 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxThere are 86400 seconds in a day, not 84400 that you stated in the article. You should probably change some numbers.Thu, 15 Sep 2011 08:55:20 GMTmark hutchinsonhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]Paxman (9/15/2011)[/b][hr]By looking at the other posts, I think that some are confusing Primary Keys, which should only be used for referential integrity and not used by humans, and Reference Keys, which are used by humans. Don't worry if your Primary Key is readable, it should not be used by humans in the first place.[/quote]And of course, choice of clustering can be independent as well.Thu, 15 Sep 2011 08:49:00 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspxBy looking at the other posts, I think that some are confusing Primary Keys, which should only be used for referential integrity and not used by humans, and Reference Keys, which are used by humans. Don't worry if your Primary Key is readable, it should not be used by humans in the first place.Thu, 15 Sep 2011 08:41:48 GMTPaxmanhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]iposner (9/15/2011)[/b][hr][quote][b]hxchi (9/15/2011)[/b][hr]To synchronize the two server clocks to 1 millisecond precision is not a trivial task.[/quote]Especially because Windows internal system clock has a default resolution of 3 milliseconds![/quote]I don't see the importance of this. If records are being generated on both servers, maybe you think that there could be collisions, but byte 14 would always be different between the servers, so it just could not happen.Thu, 15 Sep 2011 07:46:26 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]Pascal Declercq (9/15/2011)[/b][hr]Quote: "What happens after 40 years (well actually nearer 60 years)? Well, it will be job security for my great-grandchildren so I'm not going to worry about it."I thought we IT guys had learned our lesson after the millenium bug ...[/quote]I think I learnt two things.1. Make sure you will be pushing up the daisies when your solution breaks.2. There is a hell of a lot of money to be made in such occurencies:hehe:Thu, 15 Sep 2011 07:39:37 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic1175396-60-1.aspx[quote][b]hxchi (9/15/2011)[/b][hr]To synchronize the two server clocks to 1 millisecond precision is not a trivial task.[/quote]Especially because Windows internal system clock has a default resolution of 3 milliseconds!Thu, 15 Sep 2011 07:34:39 GMTiposner