Re: How to handle PK field using Int datatype when moved to a new serv

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

..NET sequential guids are not the same as SQL Servers as there are some bytes swapped.

http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx

So if order is important you need to swap the bytes.

John

"sloan" <sloan@xxxxxxxxx> wrote in message news:O2ghqxx2JHA.3544@xxxxxxxxxxxxxxxxxxxxxxx
//
uniqueidentifier. .......... this type is fairly large, and it also has it problems with
fragmentation etc. //


I need to do a write up about this idea that I implemented.

I took the newsequentialid() (tsql) idea and put it on the clientside.
And it has helped with bulk inserts tremendously because of the lack of fragmentation.

C# code below.


But I agree, stick with bigint (unless int satisfies your requirements then stick with int) unless you have a reason to go to GUID.
I have alot of replication and db merging, so I pay the performance price for GUID because of the other reason.
However, my MyCompany.Guid.NewGuid() code has really helped performance wise. We've done about 100 millions rows (or more(??)) and the code below has just kept on working.
As illuded to, sometimes I create the relationships in the client code, and therefore do not want to wait for the "what did you just insert and get back as the surrogatekey(uuid)?".....
I build the relationships then jam the rows into the db using xml.






namespace MyCompany
{
public static class Guid
{


/*

Original Reference for Code:
http://www.pinvoke.net/default.aspx/rpcrt4/UuidCreateSequential.html


*/



[DllImport("rpcrt4.dll", SetLastError = true)]
static extern int UuidCreateSequential(out System.Guid guid);


public static System.Guid NewGuid() //This just makes a copy and paste easy.... System.Guid.NewGuid() becomes MyCompany.Guid.NewGuid()
{
return CreateSequentialUUID();
}


public static System.Guid CreateSequentialUUID()
{
const int RPC_S_OK = 0;
System.Guid g;
int hr = UuidCreateSequential(out g);
if (hr != RPC_S_OK)
throw new ApplicationException("UuidCreateSequential failed: " + hr);
return g;
}
}
}






"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns9C13E27C75B5EYazorman@xxxxxxxxxxxx
Sam (Sam@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I realize MS came up with uniqueidentifier field for this situation but
this datatype is pretty large and I found out that indexes on
uniqueidentifiers run slower too.

I'm designing a new database with a table that will have millions of
records in it. Is there anything I can do at this point that will help
me down the road with the PK field with an Int data type?

First of all, when you design your database, you should look for natural
primary keys. For instance, if you have a table with currencies, the PK
of that table should be char(3) and hold the currency code, and nothing
else.

Now, there are many entities for which you cannot find any (practically
usable) primary key, and even if you can, there may be good reason to
use a surrogate key.

For surrogate keys, you can use int, bigint or uniqueidentifier. As you
note, this type is fairly large, and it also has it problems with
fragmentation etc. This is not to say that this type is useless, but
you should only use it if there is a special reason to. There are a few
features in SQL Server that requires table to have a GUID column:
replication and filestream are the two that comes in mind. It is also
popular to use guids in web applications, because the guid can then be
generated client-side, and the web app does not have to pick up the
key from the server.

As for int/bigint, if you only have millions of rows, stay with int.
But if you expect 100 million rows, go bigint. It also depends on how
you will insert and delete rows. If the table will stay at 10 million
rows, because you prune one million rows per week, you will hit the
int ceiling at some time. One thing to keep in mind that at a later point
to change to bigint can be painful.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx




.

Relevant Pages