first i created one table on user database
======================
create table asr (eno int,ename varchar(20),dep varchar(30))
then insert the values
insert into asr values(10,'asr','comp')
insert into asr values(12,'dsr','seci')
select * from asr
Then i created cluster index on table
=======================
create clustered index asr_cluster on asr(eno)
creating non cluster index
=================
create index asr_noncluster on asr(ename)
create index asr_noncluster1 on asr(dep)
Query for finding the what type indexes in a table
=====================
sp_helpindex 'asr(table_name)'
select object_name(object_id) object, * from sys.indexes where object_id = object_id ('asr')
finding the Fragmentation of Table in a database ..based on our Client Requirement....0-10% --no action take
ReplyDelete10-20-%----not bad..need to be reorganize the index.
30% above means ---bad status--we need to Rebuild the index on table..
SELECT * FROM sys.dm_db_index_physical_stats (db_id('dbname'),
OBJECT_ID('dbo.asr'), NULL, NULL, 'DETAILED')
SQL 2005 commands for Index rebuluild and Reorganize the index........
ReplyDeleteIndex Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO
Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO
http://sqlserverpedia.com/wiki/DM_Objects_-_Sys.dm_db_index_physical_stats
ReplyDelete