Alter Index For All Tables/indexes In A DB

Oct 22, 2007



In SQL Server 2000 one could DBReindex every index that exists in a given database. You can do the same in SQL Server 2005. But how can this be done with the new Alter Index command? It does not allow me to pass in a variable for the object. Any ideas on how to get this done in with Alter Index in 2005? Thanks!

This I can't get to work:

DECLARE
@TableName nvarchar(100)
SET @TableName = 'Account'

USE database;
GO
ALTER INDEX ALL ON @TableName
REBUILD
GO




USE RZTQ5OL02

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT Name from sys.sysobjects where type = 'U'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC DBREINDEX(@TableName,' ',90)



FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

View 3 Replies


ADVERTISEMENT

Move Indexes Via ALTER INDEX

Mar 13, 2007

I was checking out an indexes property via SQL 2005 Management Studioand it looks like I can move the index to other filegroups via thedrop down.I checked the BOL ALTER INDEX and I didnt see an argument for thisaction. I see stuff about PARTITION, but I think that is for rowpartitioning??I want to move existing indexes from the PRIMARY filegroup to a newfile group just for indexes, which is called INDEXES.Can this be done via ALTER INDEX or some other way?TIARob

View 1 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).

Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.

I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.

As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?

View 5 Replies View Related

Using Alter Statements To Create Indexes?

Apr 19, 2006

I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.

Any help is appreciated.

View 3 Replies View Related

ALTER INDEX

Jan 2, 2007

hey guys for some reason when i run this:

USE WebPageLog;
GO
ALTER INDEX PK_TNS_report ON TNS_report
REBUILD;
GO

then i check index fragmentation its still the same 50% why is that?

View 3 Replies View Related

Alter Index

Nov 5, 2007

I am getting an error below when trying to use online indexing.
The error returned list a series of datatypes not supported for ONLINE=ON
The problem is the datatype referred in the table is varbinary which is not listed.
Also I created a test table with a varbinary index and all is good in the hood.
Can someone help with the below error.
Thanks in advance.

Database: Adventureworks
Microsoft SQL Server 2005 - 9.00.2047.00 (X64) Apr 14 2006 01:11:53 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

ALTER INDEX ALL
ON Production.[Document] REBUILD
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 2)

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'PK_Document_DocumentID' because the index contains column 'Document' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

Thank you,
Adam

View 9 Replies View Related

Alter Index ... Rebuild

May 1, 2008

If the index to be rebuilt is a clustered index, will all non-clustered indexes be rebuilt also by rebuilding only the clustered index.

View 7 Replies View Related

ALTER INDEX Blocking

Sep 14, 2007

Hi all

We are still testing SQL 2005. We changed all the CREATE INDEX jobs to ALTER INDEX. we ran the index jobs and noticed a lot of blocking. The jobs eventually completed successfully though. The spids were blocking themselves. And this was on and off. I qould query sysprocesses and see the blocking intermittently. I didnt notice this when we run the same indexing jobs in 2000. Is this the way ALTER INDEX works in 2005 or is this something we need to be concerned about? We have build 3186. I need to mention that the jobs finished rather quicker as compared to 2000. And the spids were blocking themselves not other spids.

Thanks for the help.
Don

View 4 Replies View Related

Does Alter Index All Rebuild...

Nov 16, 2007

Does "Alter Index All Rebuild" rebuild just the tree level of the index? Or does it also rebuild the leaf level like reorganize?

Thanks and God Bless,
Thomas


ThomBeaux

View 1 Replies View Related

Alter Index FillFactor

Dec 5, 2007

If you perform an Alter Index All without the FillFactor (example below) and the previous fill factor for the table and it's index were (80%), will the fillfactor be set to the previous value (80) or the sql server default index fill factor value (0 or 100%)?



ALTER INDEX ALL ON [Sales Header] REBUILD
WITH (SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE=OFF)

Thanks,

Ian

View 3 Replies View Related

Alter Index On A Remote Database

Nov 5, 2007

I have created a stored procedure in msdb that will accept a database name and then rebuild non-clustered indexes in that database.

I placed the sp in msdb since msdb is where most job-related information is stored.

The sp identifies non-clustered indexes whose avg fragmentation exceeds a pre-determined threshold, and then runs the following statement for each index in the resulting subset:


SET @str_sql = 'ALTER INDEX ' + @str_index + ' ON ' + @str_object + ' REBUILD '

EXEC sp_executesql @str_sql

where @str_index is the name of the index and @str_object is the name of the object (view, table).

Clearly, I need to revise the statement so that it refers to the appropriate database.

Is there a way to execute 'alter index ... rebuild' so that it points to a database other than the current?

Thanks.

View 3 Replies View Related

ALTER INDEX &&<tablename&&> REBUILD

Aug 30, 2007

I have read through BOL but am still confused by the above sql. I think it rebuilds all indexes on a table. Am I correct? (If so, if would seem to be a good thing to run it nightly on all tables in all databases. Or maybe that is too extreme)


Barkingdog

View 1 Replies View Related

Can&#39;t Add Index, Too Many Statistical Indexes?

Oct 5, 2001

I was trying to add an index to a heavily used table which has one other index. When I did I recieve the following error message
Unable to create index 'IX_PATIENT__1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create more than 249 nonclustered indices or column statistics on one table.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

Do I need to wack the statistical

View 1 Replies View Related

Using The ONLINE Option The Alter Index Rebuild

Jul 9, 2007



I do a lot of index maintenance and started testing rebuilding my indexes with the ONLINE option, but noticed an increase in record size by 14 bytes. The issue I have is that for each index you build the ONLINE option, you add 14 bytes for row. After the index rebuild is done, normal transaction updates drop the additional 14 bytes.

The act of adding and dropping 14 bytes would cause fragmentation and uncompressed data. Is this correct or do I have something wrong?

View 1 Replies View Related

PK Still Shows Fragmentation After ALTER INDEX.. REBUILD

Sep 4, 2007

I am using sys.dm_db_index_physical_stats to identify indexes that need to be rebuilt based on a fragmentation limit. Once identified, I execute and ALTER INDEX... REBUILD on the index. If the index is clustered, only that index gets rebuilt for the table. After all the indexes are complete, I receive a report on the indexes that were rebuilt in the databases and what level of fragmentation the index was at before rebuilt. After checking these indexes, I still see that all the Primary Key indexes are still at the same fragmentation level. I run the process again and it does not change. I updated table usage and also ran update statistics after running the rebuild again, but the fragmentation does not change. Why can€™t these PK Clustered indexes be rebuilt as expected? Do I need to drop and recreate the PK before this fragmentation changes?

View 7 Replies View Related

Index Fragmentation LogicalFragmentation -how Bad Is Bad - Silly Question Alter

Mar 10, 2007

I thought I would delve into index fragmentation and I found somegreat sql from many posters (thanks Erland!).My question is how bad is bad? I know this is very subjective.Some scripts I found would reindex if the LogicalFragmenation is over30%.I have some tables that are 98% (I'm guessing really bad). I know itall depends..more as a learning point: I found a table that had over 30%logicalfragmentation, I dropped the indexes, created then ran thescript that used type code segment:'DBCC SHOWCONTIG(' + @TableName + ') WITH TABLERESULTS, ALL_INDEXES,NO_INFOMSGS')In one case, the indexes for the table dropped below 30%, in anothercase the index was still fragmented ever after I dropped and re-created index.SQL Server 2005 x64 SP2This is the script I am running (I found this in another thread thatErland posted):SET NOCOUNT ONUSE ds_v6_sourceDECLARE @TableName VARCHAR(100)-- Create a table to hold the results of DBCC SHOWCONTIGIF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULLDROP TABLE #ContigCREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT,[IndexName]VARCHAR(200),[IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,[MinimumRecordSize] INT,[MaximumRecordSize] INT , [AverageRecordSize] INT,[ForwardedRecords] INT ,[Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes]NUMERIC(6,2),[AveragePageDensity] NUMERIC(6,2), [ScanDensity]NUMERIC(6,2) ,[BestCount] INT ,[ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,[ExtentFragmentation] NUMERIC(6,2) )DECLARE curTables CURSOR STATIC LOCALFORSELECT Table_NameFROM Information_Schema.TablesWHERE Table_Type = 'BASE TABLE'OPEN curTablesFETCH NEXT FROM curTables INTO @TableNameSET @TableName = RTRIM(@TableName)WHILE @@FETCH_STATUS = 0BEGININSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @TableName + ') WITHTABLERESULTS, ALL_INDEXES, NO_INFOMSGS')FETCH NEXT FROM curTables INTO @TableNameENDCLOSE curTablesDEALLOCATE curTables

View 8 Replies View Related

Do You Include Clustered Index Column In Other Indexes?

Apr 3, 2002

I have a database where records are Inserted by an external process.
There is no updating or deleting of the data once inserted. The table in
question has a Clustered Index on the Machine_ID (integer) (data is from
manufacturing processes). Each record bears a start and end time. Most
queries involve the Machine, a time span (start time between to points in
time), the Downtime Cause, and the Running Mode.

I want to add an index on the Start Time, the Downtime Cause, and the
Runtime Mode.

My question is: should this new index also contain the Machine_id column
or does the existence of the Clustered Index already on that column negate
its need in the new index?

RC - Dedicated to only creating original mistakes!

View 2 Replies View Related

Index Usage - Find Unused Indexes

Sep 13, 2004

Can anyone tell me a good way to monitor which indexes are not being used? Over time, I'm sure there are extraneous indexes in our database, which I would like to get rid of.

Any ideas would be appreciated.

Thanks,
Rob

View 3 Replies View Related

Traditional Indexes Vs Clustered Column-store Index

Apr 3, 2015

I've been asked to look at using Clustered Columnstore indexes for one of my tables. The table contains about 5 million records with about 50 columns. The max field size is a NVarchar(MAX) with max field length currently of about 4k characters. It's only about a gigabyte's worth of data. The table is about 50% R/W operations. Currently, we have multiple indexes with no clustered index due to some performance issues that happened in the past. I've been attempting to determine if it's even really worth it to switch over. I feel that the table is still fairly small with minimal columns and don't believe there will be any noticeable improvement over traditional indexing.

View 3 Replies View Related

Optimize Indexes ...SQL Server Index Tuning Wizard

Jan 8, 2006

I saw this tool for SQL-Server 2000 :
http://www.sql-server-performance.com/index_tuning_wizard_tips.asp

Is there anything similar for SQL-Server 2005 Express ?

Thank you very much for any help!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

View 2 Replies View Related

T-SQL (SS2K8) :: MASTER And MSDB - Defrag Using Standard Alter Index Command?

Oct 10, 2014

On a 2008r2 server, I ran the frag utility against master and msdb and noticed they were severely fragmented.

Is it ok to defrag them using the standard Alter Index command?

View 7 Replies View Related

DB Design :: Re-indexing A Table Using Some Commands From ALTER INDEX REBUILD From Microsoft

May 20, 2015

I'm working on re-indexing a table using some commands from ALTER INDEX REBUILD from Microsoft. The indexes will be tested for threshold fragmentation. my plan is once the reindex is executed, a transaction backup will occur while controlling the size of the log file.  The query impose time limitations or stop reindexing after the specified amount of time has elapsed.

my question,
1. How can I integrate a query which checks if transaction log is getting full and which runs a Tlog backup if over 70%
2. How do I impose time limitation?

View 3 Replies View Related

SQL Server 2012 :: How To Generate Index Creation Scripts (many Indexes)

Jun 24, 2015

Script they use to generate indexes in SQL 2005.

I have 2 databases on a separate instance. I want to script out all indexes from database1 then execute it on database2.

How to accomplish this task efficiently.

View 5 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 12, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???





View 1 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 16, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???


thanks,

Ran Kizi

View 3 Replies View Related

Order Of Data Load And Index Creation / Move Indexes To Separate Filegroup?

Apr 15, 2015

We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).

1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.

2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?

CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO

I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)

Placing Indexes on Filegroups:

[URL]

By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:

• Create nonclustered indexes on a filegroup other than the filegroup of the base table.

Move an Existing Index to a Different Filegroup:

[URL]

Limitations and Restrictions

• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.

• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.

View 1 Replies View Related

Alter Tables

Apr 20, 2006

Dear friends

I would like to alter columns so that I have have empty string records instead of null. How do I query all tables and all columns so I can alter the column properties programmatically?

Thanks

Cm

View 19 Replies View Related

Alter All Tables Script

May 18, 2006

So I needed to add 2 columns to about 20 tables. Not much and I could have easily wrote a script for each table. But thats boring. So I started searching around.

I found up with this from this post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65351

Select 'Alter table '+table_name+' Add yourcol datatype' from information_schema.tables
where table_name<>'dtProperties'



Cool but I wanted it to run and execute at once, not just create the scripts. The only way I could think was is with a cursor. Something I didn't want to do. But did anyway. I came up with this.

DECLARE Alter_tables_cursor CURSOR
FOR
select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
PRINT 'Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime'
EXEC('Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime')
FETCH NEXT FROM Alter_tables_cursor INTO @tablename

END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor

I'm sure there are better ways. If so post them.

http://www.jiltedcitizen.com

View 5 Replies View Related

TABLES AND INDEXES

Jun 7, 2001

How do I move a table (test1) to a physical file group, and the table(test1) indexes to another file group. One clustered index and 3 nonclustered indexes. Thanks!!!!

View 4 Replies View Related

Indexes On Tables

Oct 9, 2001

Hello List,

I've a customer table and It has two indexes only. But when I view from the query analyser it list ten indexes with the following name:

"_WA_Sys_status_01EAB64E"

I do not know, what does the above index does and i remember, I did not created that. When trying to drop that, It says, Its not an index. I dont know, what to do. Would somebody please advice me on this.

Thanks,

View 3 Replies View Related

Indexes On Tables

Mar 17, 2006

Hi,

Can someone point me to where I can find out if an SQL server table has an index ? I was in enterprise manager and brought up properties for any given table but was unable to find the information.

Thanks,
Jeff

View 7 Replies View Related

Need To Alter Data Type On All Tables..

Jul 17, 2003

I need to alter datatypes from
numeric(18,0) to 'int' type across all the tables in the database of about 200 tables. Nearly 200 PK/FKs will be affected with this change..How can I do this with a script? Can anyone help witha script which drops all the constraints/indexs etc and then alters the datatype before re-creating the constraints/indexs..?

P.s. No rounding problem is expected as data is already in whole numbers.

Thanks,
Di.

View 1 Replies View Related

User That Can Alter And Modify Tables

Feb 7, 2008

What do I have to do in order to create a user that can alter and modify some/all tables?

Thank you.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved