Indexing

Find Duplicate Indexes - Episode 1 (The Early SQL Versions)

  • Script

The excellent script contributed by G.R. Preethiviraj Kulasingham (Preethi) Sri Lanka (contributed 2/20/03, modified 5/22/03) identifies duplicate indexes for the database on which the script is run.  It requires User Defined Functions, a feature restricted to SQL Server 2000.This version achieves similar results without using UDFs or creating any other permanent objects. It therefore works […]

4 (1)

2003-05-28

741 reads

DB Reindex all Databases

  • Script

This script reindexes all tables in all databases.  Execute the script with the desired fill factor and it will do the rest.  There is an option to exclude databases from the reindex. This makes it easy to reindex all newly created databases. Great for the Development environment when Developers are creating database all the time.

2003-03-23

1,712 reads

Selective Defrag/Reindex and Log

  • Script

SQL 2000 OnlyThis script will create a stored procedure and a table in the master database. Simply run the stored procedure and supply a database name as a parameter. It will do the following:1. Gather/Log DBCC SHOWCONTIG statistics (pre defrag)2. Defragment all user indexes in the database3. Gather/Log DBCC SHOWCONTIG statistics (post defrag)4. DBCC DBREINDEX […]

5 (3)

2004-11-11 (first published: )

2,195 reads

Dynamic DBCC DBREINDEX

  • Script

This script uses sysobjects and sysindexes to ID all tables in a database that are indexed, and/or clustered.  It then runs through per table all of the indexes and runs DBREINDEX with their fillfactor.  This eliminates having to maintain a list of all indexes and having code fail when it tries to modify indexes that […]

2002-12-19

1,719 reads

Smart Index Manipulation system

  • Script

We are constantly having to drop indexesfrom tables while repopulating the datain the table. We then have to rebuild the indexes as they were before dropping them.This requires writing a custom drop and recreate index script for each unique situation. If we tryto write scripts ahead of time as soon as the indexing schemechanges on […]

2002-08-06

932 reads

Index Locking Strategy

  • Script

This script returns the index locking strategy informationfor all user tables in a database. It also generates the required TSQL statement to alter locking strategies if required. These statements require CTRL-C and CTRL-V individually into query analyser for execution. Results are best viewed in text only. This TSQL can be wrapped in a stored procedure […]

4 (1)

2002-07-04

1,375 reads

Blogs

Why are my permissions missing?

By

You’d be amazed (or maybe you wouldn’t) how often I hear this: “I used...

Suggested Topics in Power Virtual Agents

By

Power Virtual Agents empowers subject matter experts to build intelligent conversational bots, using a...

Abuse, Patience, and Understanding

By

I’ve been struggling with an author for a few months. I tried to work...

Read the latest Blogs

Forums

Setting up and using dbt (Data Build Tool) with SQL Server

By David Fundakowski

dbt, or Data Build Tool, is a piece of software that allows analysts to...

Elevated Thread! Best Home of Screen Printing

By mariakeith

Elevated is the best place for screen printing services in the USA. The event...

SSRS 2016 data source - Azure SQL DB not working

By PJ_SQL

Hello! I am trying to create data source to connect to Azure SQL DB...

Visit the forum

Ask SSC

SQL Server Q&A from the SQLServerCentral community

Get answers