fill factor value

I do have table like this

(

uniqueid int

,scandate datetime

,courseno varchar(10)

)

this table has two indexes:

primary key clustered index on uniqueid, scandate

non-clustered index on courseno asc, uniqueid asc, courseno asc

this table can have 10 million records. there are no updates to this table. only data is inserted and selected from this table.

when i created these indexes the default fill factor value is 0. should i change the fill factor.

Thanks

sandeep, see BOL http://msdn2.microsoft.com/en-us/library/ms177459.aspx

Quick answer is probably OK, but read the article to determine if something in your environment (disk space limitation, etc) might want you to change it...

Fill Factor confusion

Turn away pure key zealots

I have a clustered index that starts with an INT IDENTITY(1,1) column and therefore you can only add data to the end of the cluster. What I'm confused about is the relationship between this and the fill factor. In a normal fill factor scenario you'd be worried about inserts causing page splits but if you can only append to this cluster does this mean I should set the factor to 100% even if I'm expecting a large number of inserts? Basically I don't understand what happens when you run out of space on a page on a B-tree if it's based upon an ever increasing number.

For an ever-increasing index the fill factor of 100 should indeed not increase the page splits.

It might cause hot spots having a clustered index on the ever-increasing key but after how many inserts this will become a problem really depends on your hardware (might even be several thousands a second).

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

You are correct. 100% fill factor will not cause page splits on an index that is an identity column. But why would you create a clustered index over an identity column. Yes, I understand that loading data is very fast. Data retrieval and table joins would be costly.You should use natural keys with an identity column if a primary key is required.

|||

Thanks for the answers, re-assuring that I hadn't lost the plot.

re: TheBrick

I'm not telling the whole story that's why, just concentrating on the fill factor & identity issue. That's also why I said to look-away now

FYI the key is something like <Identity> + <natural key> where the idenitity is used as the join for all sorts of relationships. I won't go into the in's and out's but don't fret it needs to be there for this specific case. Yes it breaks rules, but sometimes you have to. Also there are other (obviously nonclustered) indexes that represent the more natural relationships. All this muddies the waters when asking about 100% fill factors hence I didn't include it

File name with sp_xml_preparedocument - Not working ?

Is it legal to give filename to sp_xml_preparedocument

Can i do

DECLARE @.hdoc int DECLARE @.doc varchar(1000) SET @.doc ='C:/XML/myXmlFile.xml' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.doc -- Remove the internal representation. exec sp_xml_removedocument @.hdoc

I know the XML is well formed because when the paste the file in @.doc above it worksI tried doing it and i get the following error: The XML parse error 0xc00ce556 occurred on line number 1, near the XML text "C:\XML_Processing\Test.xml".Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1The error description is 'Invalid at the top level of the document.'.Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1sp_xml_removedocument: The value supplied for parameter number 1 is invalid. The XML is :<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>


Hemanshu:

I think the argument to the sp_xml_preparedocument stored procedure is that actual XML text and NOT a file reference. Please give a look to the SP_XML_PREPAREDOCUMENT stored procedure in books online.

You might be able to do something like:

Code Snippet

select * from openrowset( BULK 'C:/XML/myXmlFile.xml' SINGLE_BLOB)

to fetch the data. Give a look to OPENROWSET in books online.