Filter for WordML vs Filter for XML vs Filter for Text

We currently are saving WordML (Word 2003's XML format rather than binary
format) docs into a column of our SQL Server 2005 database and full text
indexing on it. Works okay, but not great. For example, all the XML tags
are indexed... so you find many words that are not in the document (from the
user persepctive).
The fix for that is clear... use an XML filter. (Though how to do that is
not so clear... we tried moving the WordML from a Text column to an XML
column, but it gives errors on illegal characters in the Word doc.)
However, it would seem that even an XML filter will not do nearly as well as
a filter designed for WordML.
Can anyone point me to a WordML full-text-search filter for SQL Server
2005?
Thanks,
Brian
Have you tried storing them as doc's in varbinary (max) or image columns and
indexing them with the Word iFilter.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian" <TargetedConvergence@.newsgroup.nospam> wrote in message
news:%23bHGZ5idHHA.4616@.TK2MSFTNGP03.phx.gbl...
> We currently are saving WordML (Word 2003's XML format rather than binary
> format) docs into a column of our SQL Server 2005 database and full text
> indexing on it. Works okay, but not great. For example, all the XML tags
> are indexed... so you find many words that are not in the document (from
> the user persepctive).
> The fix for that is clear... use an XML filter. (Though how to do that is
> not so clear... we tried moving the WordML from a Text column to an XML
> column, but it gives errors on illegal characters in the Word doc.)
> However, it would seem that even an XML filter will not do nearly as well
> as a filter designed for WordML.
> Can anyone point me to a WordML full-text-search filter for SQL Server
> 2005?
> Thanks,
> Brian
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u2u%23lFjdHHA.4656@.TK2MSFTNGP06.phx.gbl...
> Have you tried storing them as doc's in varbinary (max) or image columns
> and indexing them with the Word iFilter.
No, for our app we need to keep them as .xml files, not .doc files.
But we could put the Word .xml file in a varbinary column if there is a
WordML iFilter out there that would act on the .xml file.
Thanks,
Brian
|||Hi Brian,
have you tried storying your WordML files in an a column of type XML? That
should invoke the XML filter.
Best regards,
-Denis.
"Brian" wrote:

> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u2u%23lFjdHHA.4656@.TK2MSFTNGP06.phx.gbl...
>
> No, for our app we need to keep them as .xml files, not .doc files.
> But we could put the Word .xml file in a varbinary column if there is a
> WordML iFilter out there that would act on the .xml file.
> Thanks,
> Brian
>
>
|||"denistc" <denistc@.discussions.microsoft.com> wrote in message
news:64C9346C-881C-4FC5-928C-1C7751ACECA6@.microsoft.com...
> have you tried storying your WordML files in an a column of type XML? That
> should invoke the XML filter.
Yes, though we've had trouble with some WordML files being rejected.
We've narrowed the issue on that front... any WordML file that has "UTF-8"
in the docheader gets rejected... not sure why though. Also not sure why
Word is creating some files with that encoding. Simply removing the tag
from the docheader (without any other re-encoding) seems to work fine, oddly
enough.
We're a bit nervous to move to the XML column until we understand what
conditions might cause XML to reject a WordML doc coming out of Word...
because without that understanding, we can't be sure it won't happen to our
customers.
Thanks for the suggestion,
Brian
|||"Brian" <TargetedConvergence@.newsgroup.nospam> wrote in message
news:%23OqdVzFgHHA.2396@.TK2MSFTNGP04.phx.gbl...
> Yes, though we've had trouble with some WordML files being rejected.
> We've narrowed the issue on that front... any WordML file that has "UTF-8"
> in the docheader gets rejected... not sure why though. Also not sure why
> Word is creating some files with that encoding. Simply removing the tag
> from the docheader (without any other re-encoding) seems to work fine,
> oddly
> enough.
Haven't done much with WordML myself, but assuming the WordML file has
"UTF-8" in the docheader, I'd be interested to know if it actually is a
UTF-8 file? Or is it possible it is being saved with an incorrect BOM or
invalid (non-UTF-8-encoded) characters in the doc? One of those two would
be my first guess. If so, that might be a bug that needs to be reported to
MS. You might try opening that WordML file in a hex editor to verify what
is actually being stored.

Filter Error: "..processing of filter expression..cannot be perfor

My results return a tinyint column which is either 0,1,2.
When adding a filter to the matrix it seems ok
blah.value = 2
but when running the report i get the following error:
--error--
An error has occured during report processing.
The processing of filter expression for the matrix 'matrix1' cannot be
performed. The comparison failed. Please check the data type returned by the
filter expression.
--enderror--
i have also tried
blah.value = "2"
with no success
anyone know why?a-ha!
this works
Expression
=CInt(Fields!salesGroup.Value)
Operator
=
Value
=2
frankly this is crap
i am returning a number and comparing to a number
- why should i have to convert a number to er a er number?
- why do i have to put an equals in front of the number?
"adolf garlic" wrote:
> My results return a tinyint column which is either 0,1,2.
> When adding a filter to the matrix it seems ok
> blah.value = 2
> but when running the report i get the following error:
> --error--
> An error has occured during report processing.
> The processing of filter expression for the matrix 'matrix1' cannot be
> performed. The comparison failed. Please check the data type returned by the
> filter expression.
> --enderror--
>
> i have also tried
> blah.value = "2"
> with no success
> anyone know why?
>

filter by year

I'm using a datetime column (createddate), and passing a smallint variable
(@.year). what's the best way to filter records that fall in that specific
year (including 01/01 and 12/31 records).
Thanks!Guy
CREATE TABLE #Test
(
col DATETIME NOT NULL PRIMARY KEY
)
INSERT INTO #Test VALUES ('20000101')
INSERT INTO #Test VALUES ('20010101')
INSERT INTO #Test VALUES ('20020101')
INSERT INTO #Test VALUES ('20020102')
INSERT INTO #Test VALUES ('20020103')
INSERT INTO #Test VALUES ('20030101')
DECLARE @.y AS SMALLINT
SET @.y=2002
SELECT * FROM #Test WHERE YEAR(col)=@.y
If it does not help you please post DDL+ sample data + expected result.
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:%23kqBn6bEFHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm using a datetime column (createddate), and passing a smallint variable
> (@.year). what's the best way to filter records that fall in that specific
> year (including 01/01 and 12/31 records).
> Thanks!
>|||Here's one method:
SELECT *
FROM YourTable
WHERE createddate >= DATEADD(YEAR,@.year-2000,'20000101')
AND createddate < DATEADD(YEAR,@.year-1999,'20000101')
David Portas
SQL Server MVP
--|||In addition to the other replies, you might want to implement a calendar
table if you do these kinds of things on a regular basis.
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:%23kqBn6bEFHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm using a datetime column (createddate), and passing a smallint variable
> (@.year). what's the best way to filter records that fall in that specific
> year (including 01/01 and 12/31 records).
> Thanks!
>

Filter by computed column and Join problem

I got this sql statement
SELECT ItemName, ItemId, ItemLocation,
(SELECT COUNT(*)
FROM Table2 t2
Where t1.ItemId = t2.ItemId
) AS ItemCount
FROM ItemTable t1
Is it possible so that I can filter the resultset using ItemCount? (WHERE
ItemCount >5)
I got two tables namely Item and ItemLocation
Item table is a table holding goods and ItemLocation holding goods that sold
at the location.
Item Table: ItemId [PK], ItemName ...
ItemLocation: LocationId [PK] , ItemId [PK]
I issue a SQL statement
SELECT i.ItemId, i.ItemName,
(SELECT il.LocationId
FROM ItemLocation il
WHERE il.ItemId = i.ItemId
AND il.LocationId = 2
) AS LocationId
FROM Item
This T-SQL will returns me with all the Item in ItemTable and LocationId
will be null if it is not in ItemLocation table.
What is the equivalent join statement to produce the same result without
using sub-query?
I tried LEFT JOIN but it not correct, it eliminate NULLs because of a
filter.
SELECT i.ItemId, i.ItemName,
il.LocationId
FROM Item i
LEFT JOIN ItemLocation il
WHERE il.LocationId = 2On Thu, 10 Mar 2005 23:10:33 +0800, Joel Leong wrote:

>I got this sql statement
>SELECT ItemName, ItemId, ItemLocation,
> (SELECT COUNT(*)
> FROM Table2 t2
> Where t1.ItemId = t2.ItemId
> ) AS ItemCount
>FROM ItemTable t1
>Is it possible so that I can filter the resultset using ItemCount? (WHERE
>ItemCount >5)
Hi Joel,
Method 1: repeat the subquery
WHERE (SELECT ...) > 5
Method 2: Use a derived table
SELECT ItemName, ItemId, ItemLocation, ItemCount
FROM (SELECT ItemName, ItemId, ItemLocation,
(SELECT ...) AS ItemCount
FROM ItemTable AS t1) AS derived
WHERE ItemCount > 5
Method 3: Use a join and group by instead of a subquery:
SELECT t1.ItemName, t1.ItemId, t1.ItemLocation,
COUNT(*) AS ItemCount
FROM ItemTable AS t1
INNER JOIN ItemTable AS t2
ON t2.ItemID = t1.ItemID
GROUP BY t1.ItemName, t1.ItemId, t1.ItemLocation
HAVING COUNT(*) > 5
(snip)
>What is the equivalent join statement to produce the same result without
>using sub-query?
>I tried LEFT JOIN but it not correct, it eliminate NULLs because of a
>filter.
>SELECT i.ItemId, i.ItemName,
> il.LocationId
> FROM Item i
>LEFT JOIN ItemLocation il
>WHERE il.LocationId = 2
Try
SELECT i.ItemId, i.ItemName, il.LocationId
FROM Item AS i
LEFT JOIN ItemLocation AS il
ON il.ItemId = i.ItemId
AND il.LocationId = 2
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
SELECT
i.ItemId,
i.ItemName,
il.LocationId
FROM
Item i
LEFT JOIN
ItemLocation il
on i.ItemId = il.ItemId and il.LocationId = 2
AMB
"Joel Leong" wrote:

> I got this sql statement
> SELECT ItemName, ItemId, ItemLocation,
> (SELECT COUNT(*)
> FROM Table2 t2
> Where t1.ItemId = t2.ItemId
> ) AS ItemCount
> FROM ItemTable t1
> Is it possible so that I can filter the resultset using ItemCount? (WHERE
> ItemCount >5)
>
> I got two tables namely Item and ItemLocation
> Item table is a table holding goods and ItemLocation holding goods that so
ld
> at the location.
> Item Table: ItemId [PK], ItemName ...
> ItemLocation: LocationId [PK] , ItemId [PK]
> I issue a SQL statement
> SELECT i.ItemId, i.ItemName,
> (SELECT il.LocationId
> FROM ItemLocation il
> WHERE il.ItemId = i.ItemId
> AND il.LocationId = 2
> ) AS LocationId
> FROM Item
> This T-SQL will returns me with all the Item in ItemTable and LocationId
> will be null if it is not in ItemLocation table.
> What is the equivalent join statement to produce the same result without
> using sub-query?
> I tried LEFT JOIN but it not correct, it eliminate NULLs because of a
> filter.
> SELECT i.ItemId, i.ItemName,
> il.LocationId
> FROM Item i
> LEFT JOIN ItemLocation il
> WHERE il.LocationId = 2
>
>
>
>|||But the result for
>SELECT i.ItemId, i.ItemName, il.LocationId
>FROM Item AS i
>LEFT JOIN ItemLocation AS il
> ON il.ItemId = i.ItemId
> AND il.LocationId = 2
is incorrect because it eliminates all the null row
because of il.Location=2 filter.

>--Original Message--
>On Thu, 10 Mar 2005 23:10:33 +0800, Joel Leong wrote:
>
ItemCount? (WHERE
>Hi Joel,
>Method 1: repeat the subquery
> WHERE (SELECT ...) > 5
>Method 2: Use a derived table
> SELECT ItemName, ItemId, ItemLocation, ItemCount
> FROM (SELECT ItemName, ItemId, ItemLocation,
> (SELECT ...) AS ItemCount
> FROM ItemTable AS t1) AS derived
> WHERE ItemCount > 5
>Method 3: Use a join and group by instead of a subquery:
> SELECT t1.ItemName, t1.ItemId, t1.ItemLocation,
> COUNT(*) AS ItemCount
> FROM ItemTable AS t1
> INNER JOIN ItemTable AS t2
> ON t2.ItemID = t1.ItemID
> GROUP BY t1.ItemName, t1.ItemId, t1.ItemLocation
> HAVING COUNT(*) > 5
>
>(snip)
same result without
NULLs because of a
>Try
>SELECT i.ItemId, i.ItemName, il.LocationId
>FROM Item AS i
>LEFT JOIN ItemLocation AS il
> ON il.ItemId = i.ItemId
> AND il.LocationId = 2
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||On Thu, 10 Mar 2005 16:47:29 -0800, Joel Leong wrote:

>But the result for
>is incorrect because it eliminates all the null row
>because of il.Location=2 filter.
(snip)
Hi Joel,
Did you test this? It should not eliminate the null row, because I moved
the test for il.LocationId = 2 from the WHERE clause to the JOIN clause.
If you did test this and found expected output rows to be eliminated,
then please post a repro script, so that I can investigate what's wrong.
A repro script consists of
* CREATE TABLE statements, including all constraints but excluding
irrelevant columns;
* INSERT statements for a few rows of sample data (just include enough
rows to demonstratie the problem and the expected output);
* The actual statement you executed.
Apart from the repro script, you should also include the output you got
(when run against the sample data included in the repro script), the
output you expected and the result of
SELECT @.@.VERSION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Filter by a concatenated column?

I am using a query in a stored procedure, where the user can dictate which
field they want to sort by. That I can do. What I also want to do is
filter out the null values for the selected field, and one of the fields is
a concatenated result. This is the query:
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
so the stored procedure would be something like this:
CREATE PROCEDURE [dbo].ClientSearch]
@.SearchField as varchar(50)='' AS
Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
where @.SearchField is not null order by @.SearchField
If the user is selecting FullName, I get an error saying the @.SearchField is
an invalid column name -- however this works if other field names are
selected, and it also works if I only want to order by FullName, but not
filter.
Thanks for your help.I hope you are using exec since its a dyamic query.
fullname is a concatenationo of two fields LName + ', ' + FName
therefore LName + ', ' + FName is not null does not work.
make sure that LName is not null and FNAME is not NULL
HTH
Rajesh Peddireddy.
"news.microsoft.com" wrote:

> I am using a query in a stored procedure, where the user can dictate which
> field they want to sort by. That I can do. What I also want to do is
> filter out the null values for the selected field, and one of the fields i
s
> a concatenated result. This is the query:
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> so the stored procedure would be something like this:
> CREATE PROCEDURE [dbo].ClientSearch]
> @.SearchField as varchar(50)='' AS
> Select LName + ', ' + FName as FullName, City, State, Zip from tblClients
> where @.SearchField is not null order by @.SearchField
> If the user is selecting FullName, I get an error saying the @.SearchField
is
> an invalid column name -- however this works if other field names are
> selected, and it also works if I only want to order by FullName, but not
> filter.
> Thanks for your help.
>
>

filter

hi, most of a column values are like xx.00, how to find out the value have
more than 2 decimal (xx.001 or xx.xxx...) Thanks.Are the records not showing up or you only want the records that have more
than 2 decimal places?
What is the data type for the column?
HTH
Jerry
"js" <js@.someone@.hotmail.com> wrote in message
news:eDCvWkSyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> hi, most of a column values are like xx.00, how to find out the value
> have more than 2 decimal (xx.001 or xx.xxx...) Thanks.
>|||What is the datatype of this column? Please include DDL in future so that we
don't have to guess. Try:
SELECT x
FROM t1
WHERE ROUND(x,2,1) <> x
David Portas
SQL Server MVP
--
"js" <js@.someone@.hotmail.com> wrote in message
news:eDCvWkSyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> hi, most of a column values are like xx.00, how to find out the value
> have more than 2 decimal (xx.001 or xx.xxx...) Thanks.
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> Are the records not showing up or you only want the records that have more
> than 2 decimal places?
Yes. I only want to show the records have more than 2 decimal places.
> What is the data type for the column?
Real
Thanks.

FillFactor on Clustered Index

I have a clustered index which is created on an Identity column.
Let's say it has 10,000 pages and each row is about 200 bytes.
Each day about 1,000 rows are inserted.
Is there a formula that or method to determine the optimal FillFactor?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
Hi
Ok each day it insterts 1000 rows are pretty small amount of data. What
about reading the data? I suggets you haveing 90 for FILLFACTOR.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:76a76d9a857ab@.uwe...
>I have a clustered index which is created on an Identity column.
> Let's say it has 10,000 pages and each row is about 200 bytes.
> Each day about 1,000 rows are inserted.
> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
>
|||Since you didn't mention anything about what non-clustered indexes you have (if any) I assume that
you are interested in possible fragmentation in the leaf level of your clustered index (i.e., your
data). You say it is an identity column. Assuming your inserts don't specify a value for the
identity column (sing SET IDENTITY_INSERT), something rarely done, then each row you insert goes to
the end of the clustered index. This mean that you will have no page splits and can leave the
fillfactor to default value (100 percent full).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:76a76d9a857ab@.uwe...
>I have a clustered index which is created on an Identity column.
> Let's say it has 10,000 pages and each row is about 200 bytes.
> Each day about 1,000 rows are inserted.
> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
>
|||Yes Tibor is 100% correct since the clustered index is on an identity
column. But even if it wasn't there is no easy formula per say to determine
the fill factor. It depends a LOT on the values inserted and in what order.
Without any other info you can usually start with 85 or 90 and then watch it
for a while and go from there.
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lgHogk3HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Since you didn't mention anything about what non-clustered indexes you
> have (if any) I assume that you are interested in possible fragmentation
> in the leaf level of your clustered index (i.e., your data). You say it is
> an identity column. Assuming your inserts don't specify a value for the
> identity column (sing SET IDENTITY_INSERT), something rarely done, then
> each row you insert goes to the end of the clustered index. This mean that
> you will have no page splits and can leave the fillfactor to default value
> (100 percent full).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "cbrichards via droptable.com" <u3288@.uwe> wrote in message
> news:76a76d9a857ab@.uwe...
>
|||Thanks Tibor.
I do have non-clustered indexes on this table too, and the inserts will
rarely use SET IDENTITY_INSERT, they [the inserts] will all be done at the
end of the clustered index. Along with the inserts, there will also be
UPDATES and DELETES.
Would it still make sense to set the clustered index FILLFACTOR at or near
100? Or do the UPDATES and DELETES cause a need for the clustered index
FILLFACTOR to leave more available space on the page?
Tibor Karaszi wrote:[vbcol=seagreen]
>Since you didn't mention anything about what non-clustered indexes you have (if any) I assume that
>you are interested in possible fragmentation in the leaf level of your clustered index (i.e., your
>data). You say it is an identity column. Assuming your inserts don't specify a value for the
>identity column (sing SET IDENTITY_INSERT), something rarely done, then each row you insert goes to
>the end of the clustered index. This mean that you will have no page splits and can leave the
>fillfactor to default value (100 percent full).
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
|||As for UPDATE, you cannot change the identity column value with an UPDATE, so and update cannot
cause the row to "move" (for a cl ix over the identity column).
As for DELETE, well it will cause "holes". End result is that the pages aren't as full as they could
be. In the end, you end up with more pages that you would needed. This is easy to see using DBCC
SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebuild the clustered index.
But there's no use to leave empty room on the pages since we already determined that you don't get
external fragmentation over such index.
Note that we have discussed an (clustered) index over an identity column. The table can have other
NC indexes, over other column combinations. And those B-trees will of course have different
fragmentation chacteristica.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:76b38ca86d53a@.uwe...
> Thanks Tibor.
> I do have non-clustered indexes on this table too, and the inserts will
> rarely use SET IDENTITY_INSERT, they [the inserts] will all be done at the
> end of the clustered index. Along with the inserts, there will also be
> UPDATES and DELETES.
> Would it still make sense to set the clustered index FILLFACTOR at or near
> 100? Or do the UPDATES and DELETES cause a need for the clustered index
> FILLFACTOR to leave more available space on the page?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
>
|||Just to be clear on what you are saying, as we are now getting to close to
the final answer of my question regarding the FILLFACTOR on clustered indexes
over an identity column. If I have a clustered index over an identity column,
I can be confident in giving such an index a FILLFACTOR of 100, since we only
perform INSERTS, and DELETEs are very rare? And the UPDATES, even though they
might effect the non-clustered indexes, they will have no effect on the
clustered index?
Tibor Karaszi wrote:[vbcol=seagreen]
>As for UPDATE, you cannot change the identity column value with an UPDATE, so and update cannot
>cause the row to "move" (for a cl ix over the identity column).
>As for DELETE, well it will cause "holes". End result is that the pages aren't as full as they could
>be. In the end, you end up with more pages that you would needed. This is easy to see using DBCC
>SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebuild the clustered index.
>But there's no use to leave empty room on the pages since we already determined that you don't get
>external fragmentation over such index.
>Note that we have discussed an (clustered) index over an identity column. The table can have other
>NC indexes, over other column combinations. And those B-trees will of course have different
>fragmentation chacteristica.
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
|||Very close. One situation that can occur is that an UPDATE result in the new row don't fit on the
current page (say you change a column value from something small to something big). SQL Server would
now need to split this page and link in a new page in the linked list. Then move 50% of the rows
from the old page to the new page and the row now fit on either of these pages. The end result is
... external fragmentation. How frequent this will occur, I can't say, of course. Possibly, or even
probably not frequent enough to worry about. But if you are worried about it, I suggest to start
with 100% and then monitor fragmentation level for the cl index to see if fragmentation increases
over time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:76bc0008d16b1@.uwe...
> Just to be clear on what you are saying, as we are now getting to close to
> the final answer of my question regarding the FILLFACTOR on clustered indexes
> over an identity column. If I have a clustered index over an identity column,
> I can be confident in giving such an index a FILLFACTOR of 100, since we only
> perform INSERTS, and DELETEs are very rare? And the UPDATES, even though they
> might effect the non-clustered indexes, they will have no effect on the
> clustered index?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
>
|||Are you meaning an UPDATE on the clustered index itself, such as an Identity
Insert that updates the identity column the clustered index is created off of,
or are you talking about an UPDATE on one of the non-clustered indexes?
Tibor Karaszi wrote:[vbcol=seagreen]
>Very close. One situation that can occur is that an UPDATE result in the new row don't fit on the
>current page (say you change a column value from something small to something big). SQL Server would
>now need to split this page and link in a new page in the linked list. Then move 50% of the rows
>from the old page to the new page and the row now fit on either of these pages. The end result is
>... external fragmentation. How frequent this will occur, I can't say, of course. Possibly, or even
>probably not frequent enough to worry about. But if you are worried about it, I suggest to start
>with 100% and then monitor fragmentation level for the cl index to see if fragmentation increases
>over time.
>[quoted text clipped - 24 lines]
Message posted via http://www.droptable.com
|||If you update any variable length column with a value larger than it was
originally you run the risk of a page split. Since the row will be larger
overall than it was before the update you may not have enough room on the
page if it was 100% (or close to it) full to begin with to hold the revised
row. So this does not have to be on a column with an index, just any
variable length column.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:76cc88bc95e2b@.uwe...
> Are you meaning an UPDATE on the clustered index itself, such as an
> Identity
> Insert that updates the identity column the clustered index is created off
> of,
> or are you talking about an UPDATE on one of the non-clustered indexes?
> Tibor Karaszi wrote:
> --
> Message posted via http://www.droptable.com
>

FillFactor on Clustered Index

I have a clustered index which is created on an Identity column.
Let's say it has 10,000 pages and each row is about 200 bytes.
Each day about 1,000 rows are inserted.
Is there a formula that or method to determine the optimal FillFactor?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1Hi
Ok each day it insterts 1000 rows are pretty small amount of data. What
about reading the data? I suggets you haveing 90 for FILLFACTOR.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:76a76d9a857ab@.uwe...
>I have a clustered index which is created on an Identity column.
> Let's say it has 10,000 pages and each row is about 200 bytes.
> Each day about 1,000 rows are inserted.
> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>|||Since you didn't mention anything about what non-clustered indexes you have
(if any) I assume that
you are interested in possible fragmentation in the leaf level of your clust
ered index (i.e., your
data). You say it is an identity column. Assuming your inserts don't specify
a value for the
identity column (sing SET IDENTITY_INSERT), something rarely done, then each
row you insert goes to
the end of the clustered index. This mean that you will have no page splits
and can leave the
fillfactor to default value (100 percent full).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:76a76d9a857ab@.uwe...[vbcol
=seagreen]
>I have a clustered index which is created on an Identity column.
> Let's say it has 10,000 pages and each row is about 200 bytes.
> Each day about 1,000 rows are inserted.
> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]|||Yes Tibor is 100% correct since the clustered index is on an identity
column. But even if it wasn't there is no easy formula per say to determine
the fill factor. It depends a LOT on the values inserted and in what order.
Without any other info you can usually start with 85 or 90 and then watch it
for a while and go from there.
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lgHogk3HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Since you didn't mention anything about what non-clustered indexes you
> have (if any) I assume that you are interested in possible fragmentation
> in the leaf level of your clustered index (i.e., your data). You say it is
> an identity column. Assuming your inserts don't specify a value for the
> identity column (sing SET IDENTITY_INSERT), something rarely done, then
> each row you insert goes to the end of the clustered index. This mean that
> you will have no page splits and can leave the fillfactor to default value
> (100 percent full).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "cbrichards via droptable.com" <u3288@.uwe> wrote in message
> news:76a76d9a857ab@.uwe...
>|||Thanks Tibor.
I do have non-clustered indexes on this table too, and the inserts will
rarely use SET IDENTITY_INSERT, they [the inserts] will all be done at t
he
end of the clustered index. Along with the inserts, there will also be
UPDATES and DELETES.
Would it still make sense to set the clustered index FILLFACTOR at or near
100? Or do the UPDATES and DELETES cause a need for the clustered index
FILLFACTOR to leave more available space on the page?
Tibor Karaszi wrote:[vbcol=seagreen]
>Since you didn't mention anything about what non-clustered indexes you have
(if any) I assume that
>you are interested in possible fragmentation in the leaf level of your clus
tered index (i.e., your
>data). You say it is an identity column. Assuming your inserts don't specif
y a value for the
>identity column (sing SET IDENTITY_INSERT), something rarely done, then eac
h row you insert goes to
>the end of the clustered index. This mean that you will have no page splits
and can leave the
>fillfactor to default value (100 percent full).
>
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1|||As for UPDATE, you cannot change the identity column value with an UPDATE, s
o and update cannot
cause the row to "move" (for a cl ix over the identity column).
As for DELETE, well it will cause "holes". End result is that the pages aren
't as full as they could
be. In the end, you end up with more pages that you would needed. This is ea
sy to see using DBCC
SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebui
ld the clustered index.
But there's no use to leave empty room on the pages since we already determi
ned that you don't get
external fragmentation over such index.
Note that we have discussed an (clustered) index over an identity column. Th
e table can have other
NC indexes, over other column combinations. And those B-trees will of course
have different
fragmentation chacteristica.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:76b38ca86d53a@.uwe...[vbcol
=seagreen]
> Thanks Tibor.
> I do have non-clustered indexes on this table too, and the inserts will
> rarely use SET IDENTITY_INSERT, they [the inserts] will all be done at
the
> end of the clustered index. Along with the inserts, there will also be
> UPDATES and DELETES.
> Would it still make sense to set the clustered index FILLFACTOR at or near
> 100? Or do the UPDATES and DELETES cause a need for the clustered index
> FILLFACTOR to leave more available space on the page?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]|||Just to be clear on what you are saying, as we are now getting to close to
the final answer of my question regarding the FILLFACTOR on clustered indexe
s
over an identity column. If I have a clustered index over an identity column
,
I can be confident in giving such an index a FILLFACTOR of 100, since we onl
y
perform INSERTS, and DELETEs are very rare? And the UPDATES, even though the
y
might effect the non-clustered indexes, they will have no effect on the
clustered index?
Tibor Karaszi wrote:[vbcol=seagreen]
>As for UPDATE, you cannot change the identity column value with an UPDATE,
so and update cannot
>cause the row to "move" (for a cl ix over the identity column).
>As for DELETE, well it will cause "holes". End result is that the pages are
n't as full as they could
>be. In the end, you end up with more pages that you would needed. This is e
asy to see using DBCC
>SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebu
ild the clustered index.
>But there's no use to leave empty room on the pages since we already determ
ined that you don't get
>external fragmentation over such index.
>Note that we have discussed an (clustered) index over an identity column. T
he table can have other
>NC indexes, over other column combinations. And those B-trees will of cours
e have different
>fragmentation chacteristica.
>
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1|||Very close. One situation that can occur is that an UPDATE result in the new
row don't fit on the
current page (say you change a column value from something small to somethin
g big). SQL Server would
now need to split this page and link in a new page in the linked list. Then
move 50% of the rows
from the old page to the new page and the row now fit on either of these pag
es. The end result is
... external fragmentation. How frequent this will occur, I can't say, of c
ourse. Possibly, or even
probably not frequent enough to worry about. But if you are worried about it
, I suggest to start
with 100% and then monitor fragmentation level for the cl index to see if fr
agmentation increases
over time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:76bc0008d16b1@.uwe...[vbcol
=seagreen]
> Just to be clear on what you are saying, as we are now getting to close to
> the final answer of my question regarding the FILLFACTOR on clustered inde
xes
> over an identity column. If I have a clustered index over an identity colu
mn,
> I can be confident in giving such an index a FILLFACTOR of 100, since we o
nly
> perform INSERTS, and DELETEs are very rare? And the UPDATES, even though t
hey
> might effect the non-clustered indexes, they will have no effect on the
> clustered index?
> Tibor Karaszi wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]|||Are you meaning an UPDATE on the clustered index itself, such as an Identity
Insert that updates the identity column the clustered index is created off o
f,
or are you talking about an UPDATE on one of the non-clustered indexes?
Tibor Karaszi wrote:[vbcol=seagreen]
>Very close. One situation that can occur is that an UPDATE result in the ne
w row don't fit on the
>current page (say you change a column value from something small to somethi
ng big). SQL Server would
>now need to split this page and link in a new page in the linked list. Then
move 50% of the rows
>from the old page to the new page and the row now fit on either of these pa
ges. The end result is
>... external fragmentation. How frequent this will occur, I can't say, of c
ourse. Possibly, or even
>probably not frequent enough to worry about. But if you are worried about i
t, I suggest to start
>with 100% and then monitor fragmentation level for the cl index to see if f
ragmentation increases
>over time.
>
>[quoted text clipped - 24 lines]
Message posted via http://www.droptable.com|||If you update any variable length column with a value larger than it was
originally you run the risk of a page split. Since the row will be larger
overall than it was before the update you may not have enough room on the
page if it was 100% (or close to it) full to begin with to hold the revised
row. So this does not have to be on a column with an index, just any
variable length column.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:76cc88bc95e2b@.uwe...
> Are you meaning an UPDATE on the clustered index itself, such as an
> Identity
> Insert that updates the identity column the clustered index is created off
> of,
> or are you talking about an UPDATE on one of the non-clustered indexes?
> Tibor Karaszi wrote:
> --
> Message posted via http://www.droptable.com
>sql

FillFactor on Clustered Index

I have a clustered index which is created on an Identity column.
Let's say it has 10,000 pages and each row is about 200 bytes.
Each day about 1,000 rows are inserted.
Is there a formula that or method to determine the optimal FillFactor?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1Hi
Ok each day it insterts 1000 rows are pretty small amount of data. What
about reading the data? I suggets you haveing 90 for FILLFACTOR.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76a76d9a857ab@.uwe...
>I have a clustered index which is created on an Identity column.
> Let's say it has 10,000 pages and each row is about 200 bytes.
> Each day about 1,000 rows are inserted.
> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Since you didn't mention anything about what non-clustered indexes you have (if any) I assume that
you are interested in possible fragmentation in the leaf level of your clustered index (i.e., your
data). You say it is an identity column. Assuming your inserts don't specify a value for the
identity column (sing SET IDENTITY_INSERT), something rarely done, then each row you insert goes to
the end of the clustered index. This mean that you will have no page splits and can leave the
fillfactor to default value (100 percent full).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:76a76d9a857ab@.uwe...
>I have a clustered index which is created on an Identity column.
> Let's say it has 10,000 pages and each row is about 200 bytes.
> Each day about 1,000 rows are inserted.
> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Yes Tibor is 100% correct since the clustered index is on an identity
column. But even if it wasn't there is no easy formula per say to determine
the fill factor. It depends a LOT on the values inserted and in what order.
Without any other info you can usually start with 85 or 90 and then watch it
for a while and go from there.
--
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23lgHogk3HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Since you didn't mention anything about what non-clustered indexes you
> have (if any) I assume that you are interested in possible fragmentation
> in the leaf level of your clustered index (i.e., your data). You say it is
> an identity column. Assuming your inserts don't specify a value for the
> identity column (sing SET IDENTITY_INSERT), something rarely done, then
> each row you insert goes to the end of the clustered index. This mean that
> you will have no page splits and can leave the fillfactor to default value
> (100 percent full).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:76a76d9a857ab@.uwe...
>>I have a clustered index which is created on an Identity column.
>> Let's say it has 10,000 pages and each row is about 200 bytes.
>> Each day about 1,000 rows are inserted.
>> Is there a formula that or method to determine the optimal FillFactor?
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Thanks Tibor.
I do have non-clustered indexes on this table too, and the inserts will
rarely use SET IDENTITY_INSERT, they [the inserts] will all be done at the
end of the clustered index. Along with the inserts, there will also be
UPDATES and DELETES.
Would it still make sense to set the clustered index FILLFACTOR at or near
100? Or do the UPDATES and DELETES cause a need for the clustered index
FILLFACTOR to leave more available space on the page?
Tibor Karaszi wrote:
>Since you didn't mention anything about what non-clustered indexes you have (if any) I assume that
>you are interested in possible fragmentation in the leaf level of your clustered index (i.e., your
>data). You say it is an identity column. Assuming your inserts don't specify a value for the
>identity column (sing SET IDENTITY_INSERT), something rarely done, then each row you insert goes to
>the end of the clustered index. This mean that you will have no page splits and can leave the
>fillfactor to default value (100 percent full).
>>I have a clustered index which is created on an Identity column.
>[quoted text clipped - 3 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1|||As for UPDATE, you cannot change the identity column value with an UPDATE, so and update cannot
cause the row to "move" (for a cl ix over the identity column).
As for DELETE, well it will cause "holes". End result is that the pages aren't as full as they could
be. In the end, you end up with more pages that you would needed. This is easy to see using DBCC
SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebuild the clustered index.
But there's no use to leave empty room on the pages since we already determined that you don't get
external fragmentation over such index.
Note that we have discussed an (clustered) index over an identity column. The table can have other
NC indexes, over other column combinations. And those B-trees will of course have different
fragmentation chacteristica.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:76b38ca86d53a@.uwe...
> Thanks Tibor.
> I do have non-clustered indexes on this table too, and the inserts will
> rarely use SET IDENTITY_INSERT, they [the inserts] will all be done at the
> end of the clustered index. Along with the inserts, there will also be
> UPDATES and DELETES.
> Would it still make sense to set the clustered index FILLFACTOR at or near
> 100? Or do the UPDATES and DELETES cause a need for the clustered index
> FILLFACTOR to leave more available space on the page?
> Tibor Karaszi wrote:
>>Since you didn't mention anything about what non-clustered indexes you have (if any) I assume that
>>you are interested in possible fragmentation in the leaf level of your clustered index (i.e., your
>>data). You say it is an identity column. Assuming your inserts don't specify a value for the
>>identity column (sing SET IDENTITY_INSERT), something rarely done, then each row you insert goes
>>to
>>the end of the clustered index. This mean that you will have no page splits and can leave the
>>fillfactor to default value (100 percent full).
>>I have a clustered index which is created on an Identity column.
>>[quoted text clipped - 3 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Just to be clear on what you are saying, as we are now getting to close to
the final answer of my question regarding the FILLFACTOR on clustered indexes
over an identity column. If I have a clustered index over an identity column,
I can be confident in giving such an index a FILLFACTOR of 100, since we only
perform INSERTS, and DELETEs are very rare? And the UPDATES, even though they
might effect the non-clustered indexes, they will have no effect on the
clustered index?
Tibor Karaszi wrote:
>As for UPDATE, you cannot change the identity column value with an UPDATE, so and update cannot
>cause the row to "move" (for a cl ix over the identity column).
>As for DELETE, well it will cause "holes". End result is that the pages aren't as full as they could
>be. In the end, you end up with more pages that you would needed. This is easy to see using DBCC
>SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebuild the clustered index.
>But there's no use to leave empty room on the pages since we already determined that you don't get
>external fragmentation over such index.
>Note that we have discussed an (clustered) index over an identity column. The table can have other
>NC indexes, over other column combinations. And those B-trees will of course have different
>fragmentation chacteristica.
>> Thanks Tibor.
>[quoted text clipped - 20 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1|||Very close. One situation that can occur is that an UPDATE result in the new row don't fit on the
current page (say you change a column value from something small to something big). SQL Server would
now need to split this page and link in a new page in the linked list. Then move 50% of the rows
from the old page to the new page and the row now fit on either of these pages. The end result is
... external fragmentation. How frequent this will occur, I can't say, of course. Possibly, or even
probably not frequent enough to worry about. But if you are worried about it, I suggest to start
with 100% and then monitor fragmentation level for the cl index to see if fragmentation increases
over time.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:76bc0008d16b1@.uwe...
> Just to be clear on what you are saying, as we are now getting to close to
> the final answer of my question regarding the FILLFACTOR on clustered indexes
> over an identity column. If I have a clustered index over an identity column,
> I can be confident in giving such an index a FILLFACTOR of 100, since we only
> perform INSERTS, and DELETEs are very rare? And the UPDATES, even though they
> might effect the non-clustered indexes, they will have no effect on the
> clustered index?
> Tibor Karaszi wrote:
>>As for UPDATE, you cannot change the identity column value with an UPDATE, so and update cannot
>>cause the row to "move" (for a cl ix over the identity column).
>>As for DELETE, well it will cause "holes". End result is that the pages aren't as full as they
>>could
>>be. In the end, you end up with more pages that you would needed. This is easy to see using DBCC
>>SHOWCONTIG/sys.dm_db:index_physical_stats. To "compact" the pages, you rebuild the clustered
>>index.
>>But there's no use to leave empty room on the pages since we already determined that you don't get
>>external fragmentation over such index.
>>Note that we have discussed an (clustered) index over an identity column. The table can have other
>>NC indexes, over other column combinations. And those B-trees will of course have different
>>fragmentation chacteristica.
>> Thanks Tibor.
>>[quoted text clipped - 20 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>|||Are you meaning an UPDATE on the clustered index itself, such as an Identity
Insert that updates the identity column the clustered index is created off of,
or are you talking about an UPDATE on one of the non-clustered indexes?
Tibor Karaszi wrote:
>Very close. One situation that can occur is that an UPDATE result in the new row don't fit on the
>current page (say you change a column value from something small to something big). SQL Server would
>now need to split this page and link in a new page in the linked list. Then move 50% of the rows
>from the old page to the new page and the row now fit on either of these pages. The end result is
>... external fragmentation. How frequent this will occur, I can't say, of course. Possibly, or even
>probably not frequent enough to worry about. But if you are worried about it, I suggest to start
>with 100% and then monitor fragmentation level for the cl index to see if fragmentation increases
>over time.
>> Just to be clear on what you are saying, as we are now getting to close to
>> the final answer of my question regarding the FILLFACTOR on clustered indexes
>[quoted text clipped - 24 lines]
>> Is there a formula that or method to determine the optimal FillFactor?
--
Message posted via http://www.sqlmonster.com|||If you update any variable length column with a value larger than it was
originally you run the risk of a page split. Since the row will be larger
overall than it was before the update you may not have enough room on the
page if it was 100% (or close to it) full to begin with to hold the revised
row. So this does not have to be on a column with an index, just any
variable length column.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76cc88bc95e2b@.uwe...
> Are you meaning an UPDATE on the clustered index itself, such as an
> Identity
> Insert that updates the identity column the clustered index is created off
> of,
> or are you talking about an UPDATE on one of the non-clustered indexes?
> Tibor Karaszi wrote:
>>Very close. One situation that can occur is that an UPDATE result in the
>>new row don't fit on the
>>current page (say you change a column value from something small to
>>something big). SQL Server would
>>now need to split this page and link in a new page in the linked list.
>>Then move 50% of the rows
>>from the old page to the new page and the row now fit on either of these
>>pages. The end result is
>>... external fragmentation. How frequent this will occur, I can't say, of
>>course. Possibly, or even
>>probably not frequent enough to worry about. But if you are worried about
>>it, I suggest to start
>>with 100% and then monitor fragmentation level for the cl index to see if
>>fragmentation increases
>>over time.
>> Just to be clear on what you are saying, as we are now getting to close
>> to
>> the final answer of my question regarding the FILLFACTOR on clustered
>> indexes
>>[quoted text clipped - 24 lines]
>>>
>>> Is there a formula that or method to determine the optimal
>>> FillFactor?
> --
> Message posted via http://www.sqlmonster.com
>

fill up spaces with dots in a column

Hi,

I currently have a column in a table with data type char and length 500. However, not every column fills up the entire 500 length, and I would like to fill up the rest of the spaces with dots. Is there a setting in SQL to do this? I do not want to use varchar since I want a fixed length with dots at the end. Any ideas?

Thanks,
Alan

I can't really imagine why you would have that requirement, it is kinda backwards.

If your goal is to display the data padded with dots up to a total length of 500, then I'd suggest that you do use a varchar for storage of your data (without dots).
When you want to retrieve it, then you pad it. Something like this;

create table #x (mystring varchar(50) not null)
insert #x
select 'abc' union all
select 'defgh'

select mystring + replicate('.', 50 - len(mystring))
from #x

drop table #x
go

abc...............................................
defgh.............................................

Would that work as an idea for you?

/Kenneth

fill factor question

Would it make sense that on a table with an Identity column, a fill factor
of 100 would be OK sense data will never be inserted into the "middle" of
the table?
SQL2K SP3
TIA, ChrisR
Thatis only one aspect that you have to consider. Anytime you update any
variable columns to a size larger than what the value was when inserted it
may cause a page split.
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:OejxSx29EHA.2032@.tk2msftngp13.phx.gbl...
> Would it make sense that on a table with an Identity column, a fill factor
> of 100 would be OK sense data will never be inserted into the "middle" of
> the table?
> --
> SQL2K SP3
> TIA, ChrisR
>

fill factor question

Would it make sense that on a table with an Identity column, a fill factor
of 100 would be OK sense data will never be inserted into the "middle" of
the table?
SQL2K SP3
TIA, ChrisRThatis only one aspect that you have to consider. Anytime you update any
variable columns to a size larger than what the value was when inserted it
may cause a page split.
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:OejxSx29EHA.2032@.tk2msftngp13.phx.gbl...
> Would it make sense that on a table with an Identity column, a fill factor
> of 100 would be OK sense data will never be inserted into the "middle" of
> the table?
> --
> SQL2K SP3
> TIA, ChrisR
>

fill factor question

Would it make sense that on a table with an Identity column, a fill factor
of 100 would be OK sense data will never be inserted into the "middle" of
the table?
--
SQL2K SP3
TIA, ChrisRThatis only one aspect that you have to consider. Anytime you update any
variable columns to a size larger than what the value was when inserted it
may cause a page split.
--
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:OejxSx29EHA.2032@.tk2msftngp13.phx.gbl...
> Would it make sense that on a table with an Identity column, a fill factor
> of 100 would be OK sense data will never be inserted into the "middle" of
> the table?
> --
> SQL2K SP3
> TIA, ChrisR
>

Fill factor for ever increasing clustered index

If I defined the clustered index on identity column, what will be the
differences between fill factor 100% and 70%?Alan
Fillfactoe specifies how full each page should be. 70 means 70%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
> If I defined the clustered index on identity column, what will be the
> differences between fill factor 100% and 70%?
>|||In addition, I recommend 0 (same as 100) or 100 for an index on an identity column. The value is
ever increasing so there will be no page splits in such an index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
> Alan
> Fillfactoe specifies how full each page should be. 70 means 70%
> full, 100 means 100% full. The only special case is 0, which means the leaf
> level is full, but there is room for one or two rows per page in the upper
> levels of the index tree.
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>|||Except where rows are updated with wider values so they no longer fit on
their pages.
A very common example is "notes" type columns which are very often widened,
causing page splits even on ID based CIXs.
For these scenarios, you might still want to leave some fillfactor space
otherwise your updates might be slowed down badly
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
> In addition, I recommend 0 (same as 100) or 100 for an index on an
> identity column. The value is ever increasing so there will be no page
> splits in such an index.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the
>> leaf
>> level is full, but there is room for one or two rows per page in the
>> upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>|||> Except where rows are updated with wider values so they no longer fit on their pages.
Good point, Greg. One should consider what other columns are in the index (for a cl index, of course
all column) and whether any of those column risk being "widened" by updates.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Except where rows are updated with wider values so they no longer fit on their pages.
> A very common example is "notes" type columns which are very often widened, causing page splits
> even on ID based CIXs.
> For these scenarios, you might still want to leave some fillfactor space otherwise your updates
> might be slowed down badly
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>> In addition, I recommend 0 (same as 100) or 100 for an index on an identity column. The value is
>> ever increasing so there will be no page splits in such an index.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the leaf
>> level is full, but there is room for one or two rows per page in the upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>|||Yes, so this is most of the answer to the OP. I believe he was wondering on
an increasing value if there was any reason to go with 70 vs 100. The answer
has to do with what else are you going to do with the table besides INSERTs.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13ovperrb5uhvd1@.corp.supernews.com...
> Most people forget about this scenario when choosing to go with 100% fill
> factors.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> Except where rows are updated with wider values so they no longer fit on
>> their pages.
>> A very common example is "notes" type columns which are very often
>> widened, causing page splits even on ID based CIXs.
>> For these scenarios, you might still want to leave some fillfactor space
>> otherwise your updates might be slowed down badly
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> Benchmark your query performance
>> http://www.SQLBenchmarkPro.com
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> identity column. The value is ever increasing so there will be no page
>> splits in such an index.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the
>> leaf
>> level is full, but there is room for one or two rows per page in the
>> upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>>
>|||Most people forget about this scenario when choosing to go with 100% fill
factors.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:e6taOTOWIHA.3400@.TK2MSFTNGP03.phx.gbl...
> Except where rows are updated with wider values so they no longer fit on
> their pages.
> A very common example is "notes" type columns which are very often
> widened, causing page splits even on ID based CIXs.
> For these scenarios, you might still want to leave some fillfactor space
> otherwise your updates might be slowed down badly
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ECBF8708-C3A0-4BAE-8138-ED856A3C598A@.microsoft.com...
>> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> identity column. The value is ever increasing so there will be no page
>> splits in such an index.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23by6EJNWIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Alan
>> Fillfactoe specifies how full each page should be. 70 means 70%
>> full, 100 means 100% full. The only special case is 0, which means the
>> leaf
>> level is full, but there is room for one or two rows per page in the
>> upper
>> levels of the index tree.
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:OYuLvsMWIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> If I defined the clustered index on identity column, what will be the
>> differences between fill factor 100% and 70%?
>>
>>
>|||Yes, if for the case of INSERT only, when the last page is full, any further
INSERT will be put into the new page. So there is no difference between 10%,
20% or 70% or even 100%?
If there is DELETE, so there will be some holes in the page? Because the
column is ever increasing. The holes will never be filled unless
re-indexing? If re-indexing, then this is my original question, does the
fill factor affect it?
In my understanding is when the page is splitted, the new page will be
filled by determined by fill factor.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Yes, so this is most of the answer to the OP. I believe he was wondering
on
> an increasing value if there was any reason to go with 70 vs 100. The
answer
> has to do with what else are you going to do with the table besides
INSERTs.
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13ovperrb5uhvd1@.corp.supernews.com...
> > Most people forget about this scenario when choosing to go with 100%
fill
> > factors.
> >
> >> Except where rows are updated with wider values so they no longer fit
on
> >> their pages.
> >>
> >> A very common example is "notes" type columns which are very often
> >> widened, causing page splits even on ID based CIXs.
> >>
> >> For these scenarios, you might still want to leave some fillfactor
space
> >> otherwise your updates might be slowed down badly
> >>
> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
> >> identity column. The value is ever increasing so there will be no page
> >> splits in such an index.
> >>
> >> Alan
> >> Fillfactoe specifies how full each page should be. 70 means 70%
> >> full, 100 means 100% full. The only special case is 0, which means
the
> >> leaf
> >> level is full, but there is room for one or two rows per page in the
> >> upper
> >> levels of the index tree.|||1) it is my understanding that new pages will take the fill factor.
2) Deletes will leave holes in their respective pages. Those holes will be
'filled' when you reindex/defrag.
3) That is my understanding as well.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
> Yes, if for the case of INSERT only, when the last page is full, any
> further
> INSERT will be put into the new page. So there is no difference between
> 10%,
> 20% or 70% or even 100%?
> If there is DELETE, so there will be some holes in the page? Because the
> column is ever increasing. The holes will never be filled unless
> re-indexing? If re-indexing, then this is my original question, does the
> fill factor affect it?
> In my understanding is when the page is splitted, the new page will be
> filled by determined by fill factor.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
> on
>> an increasing value if there was any reason to go with 70 vs 100. The
> answer
>> has to do with what else are you going to do with the table besides
> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in the
>> >> upper
>> >> levels of the index tree.
>|||No a fill factor is ONLY adhered to during a create or rebuilding of an
index. It would be pointless to always keep a page 70% full for example as
you are inserting new rows. If a page splits approximately half of the rows
stay on the existing page and half on the new page.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13p1m1aqmcpbr15@.corp.supernews.com...
> 1) it is my understanding that new pages will take the fill factor.
> 2) Deletes will leave holes in their respective pages. Those holes will
> be 'filled' when you reindex/defrag.
> 3) That is my understanding as well.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference between
>> 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>|||DUH!! You know, having a 2 1/2 year old really does occassionally affect
the adult brain. :-))
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
> No a fill factor is ONLY adhered to during a create or rebuilding of an
> index. It would be pointless to always keep a page 70% full for example as
> you are inserting new rows. If a page splits approximately half of the
> rows stay on the existing page and half on the new page.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will
>> be 'filled' when you reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference between
>> 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was
>> wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer
>> >> fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>>
>|||> If a page splits approximately half of the rows stay on the existing page and half on the new
> page.
... unless the page splits at the "end" (the last row according to the index). Then now pages are
moves, instead just a new page is linked into the linked list, at the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
> No a fill factor is ONLY adhered to during a create or rebuilding of an index. It would be
> pointless to always keep a page 70% full for example as you are inserting new rows. If a page
> splits approximately half of the rows stay on the existing page and half on the new page.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will be 'filled' when you
>> reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any further
>> INSERT will be put into the new page. So there is no difference between 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in the
>> >> upper
>> >> levels of the index tree.
>>
>>
>|||Well that isn't really a split then:). That's business as usual.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>> If a page splits approximately half of the rows stay on the existing page
>> and half on the new page.
> ... unless the page splits at the "end" (the last row according to the
> index). Then now pages are moves, instead just a new page is linked into
> the linked list, at the end.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> No a fill factor is ONLY adhered to during a create or rebuilding of an
>> index. It would be pointless to always keep a page 70% full for example
>> as you are inserting new rows. If a page splits approximately half of
>> the rows stay on the existing page and half on the new page.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will
>> be 'filled' when you reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference between
>> 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because
>> the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does
>> the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was
>> wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer
>> >> fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which
>> >> means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>>
>|||I believe it is recorded as a split event. There are also other special
cases where the split will not always be 50/50. I wrote a series of articles
for SQL Magazine about this.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMTFnWqWIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Well that isn't really a split then:). That's business as usual.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>> If a page splits approximately half of the rows stay on the existing
>> page and half on the new page.
>> ... unless the page splits at the "end" (the last row according to the
>> index). Then now pages are moves, instead just a new page is linked into
>> the linked list, at the end.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> No a fill factor is ONLY adhered to during a create or rebuilding of an
>> index. It would be pointless to always keep a page 70% full for example
>> as you are inserting new rows. If a page splits approximately half of
>> the rows stay on the existing page and half on the new page.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes
>> will be 'filled' when you reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any
>> further
>> INSERT will be put into the new page. So there is no difference
>> between 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because
>> the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does
>> the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was
>> wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with
>> > 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer
>> >> fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on
>> >> an
>> >> identity column. The value is ever increasing so there will be no
>> >> page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which
>> >> means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in
>> >> the
>> >> upper
>> >> levels of the index tree.
>>
>>
>>
>|||> Well that isn't really a split then:). That's business as usual.
Tues, but it is sometimes referred to as a " page split" (even though it really isn't such). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMTFnWqWIHA.5596@.TK2MSFTNGP05.phx.gbl...
> Well that isn't really a split then:). That's business as usual.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:0866CD0D-F637-41D0-8AB3-BB29507196DD@.microsoft.com...
>> If a page splits approximately half of the rows stay on the existing page and half on the new
>> page.
>> ... unless the page splits at the "end" (the last row according to the index). Then now pages are
>> moves, instead just a new page is linked into the linked list, at the end.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23$MU2QfWIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> No a fill factor is ONLY adhered to during a create or rebuilding of an index. It would be
>> pointless to always keep a page 70% full for example as you are inserting new rows. If a page
>> splits approximately half of the rows stay on the existing page and half on the new page.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message news:13p1m1aqmcpbr15@.corp.supernews.com...
>> 1) it is my understanding that new pages will take the fill factor.
>> 2) Deletes will leave holes in their respective pages. Those holes will be 'filled' when you
>> reindex/defrag.
>> 3) That is my understanding as well.
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:O3Dia6YWIHA.4532@.TK2MSFTNGP02.phx.gbl...
>> Yes, if for the case of INSERT only, when the last page is full, any further
>> INSERT will be put into the new page. So there is no difference between 10%,
>> 20% or 70% or even 100%?
>> If there is DELETE, so there will be some holes in the page? Because the
>> column is ever increasing. The holes will never be filled unless
>> re-indexing? If re-indexing, then this is my original question, does the
>> fill factor affect it?
>> In my understanding is when the page is splitted, the new page will be
>> filled by determined by fill factor.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:OVk%23OSWWIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Yes, so this is most of the answer to the OP. I believe he was wondering
>> on
>> an increasing value if there was any reason to go with 70 vs 100. The
>> answer
>> has to do with what else are you going to do with the table besides
>> INSERTs.
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13ovperrb5uhvd1@.corp.supernews.com...
>> > Most people forget about this scenario when choosing to go with 100%
>> fill
>> > factors.
>> >
>> >> Except where rows are updated with wider values so they no longer fit
>> on
>> >> their pages.
>> >>
>> >> A very common example is "notes" type columns which are very often
>> >> widened, causing page splits even on ID based CIXs.
>> >>
>> >> For these scenarios, you might still want to leave some fillfactor
>> space
>> >> otherwise your updates might be slowed down badly
>> >>
>> >> In addition, I recommend 0 (same as 100) or 100 for an index on an
>> >> identity column. The value is ever increasing so there will be no page
>> >> splits in such an index.
>> >>
>> >> Alan
>> >> Fillfactoe specifies how full each page should be. 70 means 70%
>> >> full, 100 means 100% full. The only special case is 0, which means
>> the
>> >> leaf
>> >> level is full, but there is room for one or two rows per page in the
>> >> upper
>> >> levels of the index tree.
>>
>>
>>
>|||If the page is full and a new record is insert, no matter at the beginning,
middle or end, then page split is the result. Then if the fill factor is for
example, 70%, then are the two pages still 50/50 ? Or the 705 fill factor
has effect?|||Fillfactor only applies when building an index. It has no affect on page
splits.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:uAWQrjLXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> If the page is full and a new record is insert, no matter at the
> beginning,
> middle or end, then page split is the result. Then if the fill factor is
> for
> example, 70%, then are the two pages still 50/50 ? Or the 705 fill factor
> has effect?
>|||Thanks.
So if I rebuild the index, do 'all' pages will be filled up determined by
the fill factor?
eg. 70%.
All pages will be filled up 70 %?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23LBNtnLXIHA.5448@.TK2MSFTNGP04.phx.gbl...
> Fillfactor only applies when building an index. It has no affect on page
> splits.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
> news:uAWQrjLXIHA.1208@.TK2MSFTNGP03.phx.gbl...
> > If the page is full and a new record is insert, no matter at the
> > beginning,
> > middle or end, then page split is the result. Then if the fill factor is
> > for
> > example, 70%, then are the two pages still 50/50 ? Or the 705 fill
factor
> > has effect?
> >
> >
>|||As far as possible, all leaf level pages will be filled 70%. The upper
levels of indexes will be filled almost full, unless you ALSO specify the
PAD_INDEX option.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:%234AgUbjYIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Thanks.
> So if I rebuild the index, do 'all' pages will be filled up determined by
> the fill factor?
> eg. 70%.
> All pages will be filled up 70 %?
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23LBNtnLXIHA.5448@.TK2MSFTNGP04.phx.gbl...
>> Fillfactor only applies when building an index. It has no affect on page
>> splits.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
>> news:uAWQrjLXIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> > If the page is full and a new record is insert, no matter at the
>> > beginning,
>> > middle or end, then page split is the result. Then if the fill factor
>> > is
>> > for
>> > example, 70%, then are the two pages still 50/50 ? Or the 705 fill
> factor
>> > has effect?
>> >
>> >
>>
>