This script reindexes all user and system indexes in the
database using the original fillfactor allocated to that
index. It can be executed from the Query Analyzer as stand alone SQL or you could wrap it in a stored procedure.
This example rebuilds all indexes on
all user tables using the original fillfactor
value from the sysindexes table.
David Wootton 11/01/2002
drop table #tmp
declare @msg varchar(500)
select sysobjects.name, sysobjects.id
where sysobjects.type = 'U'
and sysobjects.name not like 'dt_proper%'
order by sysobjects.name
declare reindexcursor cursor for
from sysindexes, #tmp
where sysindexes.id = (select #tmp.id from #tmp where #tmp.id=sysindexes.id)
group by #tmp.name,sysindexes.origfillfactor
having count(#tmp.name) >= 1
order by 1
declare @tname varchar(100)
declare @ffact varchar(10)
fetch next from reindexcursor into @tname,@ffact
WHILE (@@FETCH_STATUS <> -1)
IF (@@FETCH_STATUS <> -2)
print ' '
SELECT @msg = 'DBCC DBREINDEX ('+(@tname ) +', '' '','+(@ffact)+')'
print ' '
FETCH NEXT FROM reindexcursor INTO @tname, @ffact
Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.
SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction.
There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.