SQL Fill Factor

SQL Fill Factor
Learn about SQL Fill Factor, and come out the summary.
Correct me if I wrong on SQL Fill Factor

- Fill factor setting define how full the page should be fill when index is created.

- Use fill factor to prevent frequent page split for high update/insert operation.

- Fill factor setting Will not take affect if table no has data when we define fill factor on table.

- If table has data when index created/rebuilt, Create/rebuilt index operation will redistribute data in leaf page based on the fill factor define.

- Low fill factor will slower data selection operation as data span across more page compare to high fill factor.

- Low fill factor will use more disk space as well.

- For table with high insert/update operation, high fill factor will cause page split and operation will be slow.

- For data will be added to the end of the table, fill factor should be default 0. Example index with identity column.

Reference
Fill Factor, Microsoft, http://msdn.microsoft.com/en-us/library/ms177459.aspx

Fill Factor, Microsoft, http://msdn.microsoft.com/en-us/library/aa933139%28v=sql.80%29.aspx

SQL Table no have index key

I use the below script to search
1. SQL table without clustered index
2. SQL table without any index

-- this only show table without clustered index
SELECT
sys.tables.name,
sys.indexes.name
FROM sys.tables
left join sys.indexes
ON sys.tables.object_id = sys.indexes.object_id
WHERE
isnull(sys.indexes.name,'')=''
and sys.tables.name = 'tablename'
ORDER BY
sys.tables.name

--this will show table without any index
SELECT
a.name,
FROM sys.tables a
left join sys.indexes b
ON a.object_id = b.object_id
WHERE
isnull(b.name,'')=''
--and sys.tables.name = 'PackageServerPathLink'
and not exists (select 1 from sys.indexes c where isnull(name,'')<>''
and c.object_id=b.object_id)
ORDER BY
a.name