Quote:> 1. The tables for which the constraints (e.g. pk, fk) are
> How can I check them in the enterprise manager? I am able
> to see the columns in the tables and their indexes, but
> not the constraints. Would you know how I can check them?
Right click on a table, select Design Table from the menu,
click the Manage Relationships... button.
Quote:> 2. My customer's DBA purged their all tables from a db
> instance into a txt file, such as the one that I attach
> here. Would you happen to know how to do this as well?
In Enterpriase Manager, right click on the database name,
select All Tasks, Generate SQL Scripts...
Quote:> 3. For the constraint which looks like:
> CONSTRAINT [pk_Ptnr_PtnrId] PRIMARY KEY CLUSTERED
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> What implications do CLUSTERED and FILLFACTOR have on the
> key itself, given that the db server is set in a clustered
> db environment.
Clustered index has nothing to do with server clustering.
The clustered index stores rows in the order of the
key values. The leaf level of the index is actually
the data page.
A table can have only one clustered index (but many
nonclustered indexes), so you should pick it wisely.
Think about how the data will be accessed when deciding
on the clustered index. Using the Northwind..Orders table
as an example, if orders are usually accessed by customer,
then these queries will benefit from a clustered index
on the CustomerID. If however, orders are always accessed
by OrderID, then that clustered index is not much help.
A clustered index on OrderID would be more useful.
A clustered index can speed up range queries.
By default, SQL Server creates the primary key as a
clustered index if the keyword "nonclustered" is omitted.
However, a primary key may not always be the best choice
for a clustered index (see above).
Fill factor means that when the index is created, SQL
Server only fills the leaf-level index page to the
percentage indicated, instead of filling it up. In
your example, it means that leaf index pages will be 90%
filled, leaving 10% of the space on the page for future
inserts. If no fill factor is specified, SQL Server
leaves room for one insert on each index leaf page.
Fill factors can be helpful on tables that experience
many inserts in a short period of time. Start out
without specifiying any fill factors and then, after
thorough testing, only use them if there is a
If you work with fill factors, you need to rebuild
your indexes frequently to restore the free space
on each page.