the GUID column of SQL server 2008

Guid is a horrible, worst case choice for a clustered index. You are splitting pages every time a new record is inserted, leading to fragmentation and inefficient lookups. A clustered index key should be narrow and monotomically increasing. Use an identity column for the clustered key, and have another index for the GUID that you use to actually locate records. You will find that space requirements decrease, and performance increases.

David

aneeshattingal:

You mean using SEQUENTIALID() for the value on that colume instead of GUID ? That function only availble on SQL 2012 or SQL 2008 SP3?

anyoneis:

so you mean on that colume with GUID to replace with identity colume? or SEQUENTIALID() as what aneeshattingal said ?

I don't understand why spliting page everytime a new record is insert, please explain further. It seems that rebuilt index can help on this if defragementation can help, right?

DBA100.



DBA100.

Dear all,

Do Oracle have this kind of problem ?

We also want to know if Oracle has advantage in this area over MS SQL?

DBA100.

>>That function only availble on SQL 2012 or SQL 2008 SP3?<<
You really need to do your homework.  It is available since SQL Server 2005.

anyoneis:

This make a lot of sense to me as the GUID originally is not in order, that's why a new GUID created, we don't where the clustered key created and it MAY have a lotof chance that it has to insert between physical page, as the clustered key will be very large, page split will happen, is that right ?

I think this one can also help: http://www.sql-server-performance.com/2011/tsql-sql-server-2011-features/4/.

DBA100.

marrowyung, I think you understand it.

I had not seen the Sequence object before. It looks interesting, but I am not sure where I would use it.

David

David,

Thanks.

Thanks for everyone.

DBA100.

I attended the TechEd about the SEQUENTIALID(), it exists since SQL 2012.

I attended the TechEd about the SEQUENTIALID(), it exists since SQL 2012.
Then you appear to have totally misunderstood what was presented:  
NEWSEQUENTIALID() has been around since SQL Server 2005 and unlike NEWID() is a an incremental GUID that avoids some of the problems of using NEWID() as a clustered index.  You can read about it here:
http://msdn.microsoft.com/en-us/library/ms189786(v=sql.90).aspx

What SQL Server 2012 introduced was a SEQUENCE object (not a function).  This allows you to create an incremental numeric (not a GUID) that is global (and not tied to a table).  You can read about it here:
http://msdn.microsoft.com/en-us/library/ff878091.aspx

what I read is "ry using SEQUENTIALID() instead.. "

it looks so familiar to me once I read the SQL 2012 information.

The articles say:

"Creates a GUID that is greater than any GUID previously generated by this function on a specified computer"

This means the GUID will becomes longers and the colume need larger and I don't understand that. please explain your idea.

not longer in size. but larger (higher) in regards to the plain > comparation.

I am wondering how NEWSEQUENTIALID()  can solve this problem .

any example ?

see uniqueidentifier about the "format":
http://msdn.microsoft.com/en-us/library/ms187942.aspx

internally, it's some datetime part, some machine "id" part, and some "random" part.
for the newsequentialid, this is solved a bit differently, but still ...
hope this helps

angelIII,

"but still ..."

oh,, it means it still got problem.

We are trying to take it off as this is the old design. AHAHAHA

well, as written previously:
UUID field is usually not the field to be the clustered index.
change that, and the problem should be solved?!

angelIII,

I trust you all well and I dont' think we will do this any more as as previously stated, We are trying to take it off as this is the old design.

Thanks.