Indexing on live tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Indexing on live tables

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-24 : 08:54:48

SQL Server 2005, Windows Server 2003.

I need to create some indexes on live tables in our database (tables currently in use), is there anything I should be aware of or any setting I should additionally use?

Also would modifying one of these work?

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_ProductVendor_VendorID')
DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor (VendorID);
GO

----------------------------------------------------
USE AdventureWorks;
GO
IF EXISTS (SELECT name from sys.indexes
WHERE name = N'AK_UnitMeasure_Name')
DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name
ON Production.UnitMeasure(Name);
GO
--------------------------------------

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-24 : 08:56:27

Also what about setting primary/foreign keys

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-24 : 08:56:29

How big is your tables? Take a look at Online indexing.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-24 : 08:57:23

some of the tables contain > 150 million rows

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-24 : 09:10:16

If you don't have enterprise Edition which only supports online indexing, you can apply in offline hours for 150 million records.Remember your TempDB space should be high enough if you are doing online indexing becoz of row versioning and snapshots.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-24 : 13:57:32

Online indexing has many restrictions, check books online for details.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources