Now ain't that just slicker than can be. Thanks Charles.
Tim

>the primary key index does not have to be clustered. I am glad you brought this up,though. Since I use Strataframe's Data Deployment Toolkit ( which is an SMO based schema wrangler ) I handle my keys there and didn't realize that in SSMS if you create a primary key it is in fact automatically clustered.
>
>To change this :
>
>Right click the index ( not the key or the column ) and select DROP AND CREATE TO in a script window. Then change clustered to nonclustered and run it.
>
>

>USE [Membership]
>GO
>
>/****** Object:  Index [PK_ContactMethod]    Script Date: 05/26/2010 10:39:41 ******/
>IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ContactMethod]') AND name = N'PK_ContactMethod')
>ALTER TABLE [dbo].[ContactMethod] DROP CONSTRAINT [PK_ContactMethod]
>GO
>
>USE [Membership]
>GO
>
>/****** Object:  Index [PK_ContactMethod]    Script Date: 05/26/2010 10:39:41 ******/
>ALTER TABLE [dbo].[ContactMethod] ADD  CONSTRAINT [PK_ContactMethod] PRIMARY KEY nonCLUSTERED 
>(
>	[cPK_ContactMethod] ASC
>)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
>GO
>
>

>
>
>>Hi Charles,
>>
>>>>
>>>>That is what I do now, but if I understand correctly the guids are not inserted at the end of the table and causes possible page splits. The NewSequentialID assures they are sequential and get added to the end of the table. Don't they have to be added at the table for that to work?
>>>>Tim
>>>
>>>One of the big issues is clustered indexes, which actually cause records to be inserted in the sequence. If the guids are non sequential they will get inserted into the middle someplace and that means rewriting a lot of pages on each insert. hence, if you have UID keys, don't use a clustered index on them. A sequential guid will put new records at the end, so the clustered index is fine, but requires a trip to the server as that is where it is generated (there is an algorithm out their for generating them - or something very close - locally )
>>>
>>>I'm surprised MM .net doesn't handle parent/child temporary keys so only one trip is needed for the save. Sounds like the kind of thing Kevin would implement. Since I saw that feature in SF I use identity keys where in VFE I always used guids.
>>
>>
>>As I am learning about this issue I see the problem but not sure how to apply what I learned. I think I read that when you set your column as a primary key it is automatically a Clustered Index. I don't see much value in using Sequential ID's as much as I see value in making the primary key not be a clustered index. Since I don't do complicated searches on the primary key and only return a single record, the clustered index doesn't seem to offer any value.
>>
>>There are things in MM that I am not aware of and so I couldn't say. I would be able to implement that ok if needed, but trying to make sure if I really need to. I learned to really like Guid from being a VFE person and clearly from Mike and Toni; but I still see the value in them. I do use identity int for tables that don't have real data such as lookup tables and such. It is just so much easier for me to add a guid key to a parent record and easily add the same as foreign key in children and then save everything in a transaction.
>>
>>Thanks for your input, still learning and will be forever.
>>Tim