About
This is a new SQL Server 2000 feature, which is available when using
SQL Server 2000 Enterprise Edition only.
Due to distributed partitioned views, SQL Server 2000 now occupies first
place in the TPC-C tests.
The result set of the indexed view is persistent in the database
and indexed for fast access. Because indexed views depend on base
tables, you should create indexed views with the SCHEMABINDING option
to prevent table or column modification that would invalidate
the view.
Furthermore, using views instead of heavy-duty queries can reduce
network traffic and can be used to facilitate permission management.
Using this option can reduce the time it takes to create an index, but it does
increase the amount of disk space used to create an index.
For example, using the CREATE INDEX statement with the DESC option
(descending order) can increase the speed of queries, which return
rows in the descending order. Ascending order is used by default.
In SQL Server 2000, you can create indexes on computed columns. To create
an index on a computed column, the computed column must be deterministic,
precise, and cannot have text, ntext, or image data types.
The text, ntext, and image values are stored on the Text/Image pages,
by default. This option specifies that small text, ntext, and image
values will be placed on the Data pages with other data values in a
data row.
This can increase the speed of read and write operations and reduce the
amount of space used to store small text, ntext, and image data values.
You can set the 'text in row' table option by using the sp_tableoption
stored procedure.
The table variable is a new SQL Server 2000 feature. The table variables
require less locking and logging resources than temporary tables, so
table variables should be used whenever possible.
For example, if you need to make cascading deletes or updates, you can
specify ON DELETE or ON UPDATE clause in the REFERENCES clause of
the CREATE TABLE and ALTER TABLE statements.
The cascading referential integrity constraints are much more efficient
than triggers and can boost performance.
SANs are more reliable than LANs or WANs and support high levels of
messaging traffic by lowering CPU loads and message latency.
The user-defined functions (UDFs) contain one or more Transact-SQL
statements that can be used to encapsulate code for reuse. Using UDFs
can reduce network traffic.
Because SQL Server 2000 can support up to a maximum of 64 gigabytes (GB)
of physical memory, you can purchase the appropriate server box and get
all the advantages of the hardware platform.
DBCC INDEXDEFRAG statement is an online operation. Unlike DBCC DBREINDEX,
DBCC INDEXDEFRAG does not hold locks long term and thus will not block
running queries or updates. So, try to use the DBCC INDEXDEFRAG command
instead of DBCC DBREINDEX whenever possible.
The INSTEAD OF trigger is a new SQL Server 2000 feature. These triggers
can be used to enforce business rules when constraints cannot be used.
Note: Because triggers are more resource expensive, use constrains
instead of triggers whenever possible.
Here are fourteen little known tips that you can use to ensure your SQL Server 2000 databases are performing in the most efficient manner possible.
1. For very large databases, use distributed partitioned views to
partition tables horizontally across multiple servers.
2. Use indexed views to increase the speed of your queries.
3. Consider using the WITH SORT_IN_TEMPDB option when you create an
index and when tempdb is on a different set of disks than the user database.
4. You can specify whether the index keys are stored in ascending or
descending order.
5. Consider creating index on computed columns.
6. Consider setting the 'text in row' table option.
7. Use table variables instead of temporary tables.
8. Use cascading referential integrity constraints instead of triggers,
whenever possible.
9. If you work with SQL Server 2000 Enterprise Edition, use SAN (System
Area Network) protocols instead of LAN (Local Area Network) or WAN (Wide Area
Network).
10. Use user-defined functions to encapsulate code for reuse.
11. Set the 'awe enabled' server configuration option to 1 if you work
with SQL Server 2000 Enterprise or Developer edition and have more than
4 gigabytes (GB) of physical memory.
12. Use the DBCC CHECKCONSTRAINTS statement if you need to check the
integrity of a specified constraint or all constraints on a specified
table.
13. Use the DBCC INDEXDEFRAG statement to defragment clustered and
secondary indexes of the specified table or view.
14. You can use INSTEAD OF triggers to perform enhance integrity checks
on the data values.
»
See All Articles by Columnist Alexander Chigrik
Alexander Chigrik offers fourteen useful tips for ensuring your SQL Server 2000 databases are performing in the most efficient manner possible.