indexe issues

[nathanharcup]

Hi All,
I hope someone can help me with this. I have a strange issue in that if i re-build the index the fragmentation does not change (internal fragmentation)

I have both unique clustered indexes and non unique non clustered indexes on each table.

Runing;
ALTER INDEX ALL ON 'tablename' REBUILD'

does re-build the non clustered indexes but seems to ignor the clustered ones.


running;
CREATE UNIQUE CLUSTERED INDEX [indexname] ON [dbo].[tablename] ([columns] ASC)
WITH (FILLFACTOR = 1,DROP_EXISTING = ON)

affects the clustered indexes (not a great deal) but then really fragments the non clustered indexes.

I am trying to build a statment that will for the clustered indexes use the create statment and for the non clustered indexes use the alter statment.

Having problems (i think with sub cursors) but a a novice really

This is what I have so far, any help would be very much appreciated;


USE DB
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
declare @indexname varchar(2000);
declare @type varchar(2000);
declare @UNIQUE int;
declare @UNIQUEVALUE varchar(2000);
declare @OBJECTNAME varchar(2000);
DECLARE @SQL VARCHAR (2000);
DECLARE @object_id1 int;
DECLARE @INDEXID INT;
DECLARE @INDEXIDSYSINDEXES INT;
DECLARE @COLID INT;

SET @db_id = DB_ID(N'DB');
SET @object_id = OBJECT_ID(N'');

declare curCursor cursor for

select [name], [type_desc],[is_unique],[object_id], [INDEX_ID]
from sys.indexes
where object_id in (select id from sysobjects where xtype = 'u')

open curCursor

fetch next from curCursor into @indexname, @type, @UNIQUE, @object_id1,@INDEXID
while (@@FETCH_STATUS = 0)
begin
set @OBJECTNAME = (select [name] from sysobjects where xtype = 'u' and id = @object_id1)
declare curCursor1 cursor for

SELECT [COLID] FROM sysindexkeys WHERE [INDID]=@INDEXID AND ID = @object_id1

open curCursor1

fetch next from curCursor1 into @COLID
while (@@FETCH_STATUS = 0)
begin
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
IF @UNIQUE =1 SET @UNIQUEVALUE = 'UNIQUE'
IF @UNIQUE =0 SET @UNIQUEVALUE = ''


BEGIN;
if @UNIQUEVALUE = 'Unique' SELECT @SQL = 'CREATE '+ @UNIQUEVALUE + ' INDEX ' + @TYPE+ ' INDEX ' + ' ' + '['+@INDEXNAME +']'+ ' ON '+@OBJECTNAME+' WITH (FILLFACTOR = 1,DROP_EXISTING = ON)'
if @UNIQUEVALUE = '' SELECT @SQL = 'ALTER INDEX '+ @INDEXNAME + ' ON '+@OBJECTNAME+' REBUILD'

PRINT @SQL


END;
fetch next from curCursor1 into @COLID
fetch next from curCursor into @indexname,@type, @UNIQUE,@object_id1,@INDEXID
end

close curCursor
deallocate curCursor
close curCursor1
deallocate curCursor1
END;

 

[nathanharcup]

Seem to be getting further with this. It is now putting the correct column name but the asscoisation between table name and Index name is wrong;

USE itbm
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
declare @indexname varchar(2000);
declare @type varchar(2000);
declare @UNIQUE int;
declare @UNIQUEVALUE varchar(2000);
declare @OBJECTNAME varchar(2000);
DECLARE @SQL VARCHAR (2000);
DECLARE @object_id1 int;
DECLARE @INDEXID INT;
DECLARE @INDEXIDSYSINDEXES INT;
DECLARE @COLID VARCHAR (2000);

SET @db_id = DB_ID(N'itbm');
SET @object_id = OBJECT_ID(N'');

declare curCursor cursor for

select [name], [type_desc],[is_unique],[object_id], [INDEX_ID]
from sys.indexes
where object_id in (select id from sysobjects where xtype = 'u')

open curCursor

fetch next from curCursor into @indexname, @type, @UNIQUE, @object_id1,@INDEXID
while (@@FETCH_STATUS = 0)
begin
set @OBJECTNAME = (select [name] from sysobjects where xtype = 'u' and id = @object_id1)
declare curCursor1 cursor for

--SELECT [COLID] FROM sysindexkeys WHERE [INDID]=@INDEXID AND ID = @object_id1
SELECT [name] FROM SYSCOLUMNS where ID = @object_id1 and [colid] in (select [COLID] FROM sysindexkeys WHERE [INDID]=@INDEXID AND ID = @object_id1)
open curCursor1

fetch next from curCursor1 into @COLID
while (@@FETCH_STATUS = 0)
begin
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
IF @UNIQUE =1 SET @UNIQUEVALUE = 'UNIQUE'
IF @UNIQUE =0 SET @UNIQUEVALUE = ''


BEGIN;
if @UNIQUEVALUE = 'Unique' SELECT @SQL = 'CREATE '+ @UNIQUEVALUE + ' ' + @TYPE+ ' INDEX ' + ' ' + '['+@INDEXNAME +']'+ ' ON '+@OBJECTNAME+ ' (['+ @colid + '] ASC)'+' WITH (FILLFACTOR = 1,DROP_EXISTING = ON)'
if @UNIQUEVALUE = '' SELECT @SQL = 'ALTER INDEX '+ @INDEXNAME + ' ON '+@OBJECTNAME+' REBUILD'

PRINT @SQL


END;
fetch next from curCursor1 into @COLID
fetch next from curCursor into @indexname,@type, @UNIQUE,@object_id1,@INDEXID
end

close curCursor
deallocate curCursor
close curCursor1
deallocate curCursor1
END;

 

[nathanharcup]

I changed;
fetch next from curCursor1 into @COLID
fetch next from curCursor into @indexname,@type, @UNIQUE,@object_id1,@INDEXID

to

fetch next from curCursor into @indexname,@type, @UNIQUE,@object_id1,@INDEXID

fetch next from curCursor1 into @COLID

and it now returns the correct table name with the correct index name but only returns one row :-(

 

[nathanharcup]

now getting the below error message;
CREATE UNIQUE CLUSTERED INDEX [index] ON table ([column] ASC) WITH (FILLFACTOR = 1,DROP_EXISTING = ON)
then.....
Msg 16916, Level 16, State 1, Line 50
A cursor with the name 'curCursor1' does not exist.
then....
CREATE UNIQUE CLUSTERED INDEX [index] ON table ([column] ASC) WITH (FILLFACTOR = 1,DROP_EXISTING = ON)

etc..etc..

i think it's because each index can have more than one column just not sure how to (if more than one value) change the @sql variable;

SELECT @SQL = 'CREATE '+ @UNIQUEVALUE + ' ' + @TYPE+ ' INDEX ' + ' ' + '['+@INDEXNAME +']'+ ' ON '+@OBJECTNAME+ ' (['+ @colid + '] ASC)'+' WITH (FILLFACTOR = 1,DROP_EXISTING = ON)'

 

[nathanharcup]

its now working except that its creating a new row for each column i.e;

Output;
CREATE UNIQUE CLUSTERED INDEX [index] ON table1 ([column1] ASC) WITH (FILLFACTOR = 1,DROP_EXISTING = ON)
CREATE UNIQUE CLUSTERED INDEX [index] ON table1 ([column2] ASC) WITH (FILLFACTOR = 1,DROP_EXISTING = ON)

Is there a way I can have both column1 & column2 in the same statement?

maybee an array (don't even know where to start with this really)

Kindest Regards

Nathan

 

[nathanharcup]

i think i can do this using a temp table, not sure of the syntax;

temp table columns

column1 column2 column3
column name table1 blank
column name table1 blank

if i can concatanate the two values in column1 and insert into column3 where column2 is the same I can then select column3 where it's not null for each column2?????

 

[nathanharcup]

getting really close to resolving this issue now;

My stored proc is working for most indexes but the @indexkeys value is duplicated for differant indexes on the same object?;

SET NOCOUNT ON

USE db

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
declare @indexname varchar(2000);
declare @type varchar(2000);
declare @UNIQUE int;
declare @UNIQUEVALUE varchar(2000);
declare @OBJECTNAME varchar(2000);
DECLARE @SQL VARCHAR (2000);
DECLARE @object_id1 int;
DECLARE @INDEXID INT;
DECLARE @indexkeys VARCHAR (2000);
declare @tablename varchar (2000);


SET @db_id = DB_ID(N'db');
SET @object_id = OBJECT_ID(N'');


declare curCursor cursor for
select [name], [type_desc],[is_unique],[object_id], [INDEX_ID]
from sys.indexes
where object_id in (select id from sysobjects where xtype = 'u')
open curCursor
fetch next from curCursor into @indexname, @type, @UNIQUE, @object_id1,@INDEXID
while (@@FETCH_STATUS = 0)
begin

set @tablename = (select [name] from sysobjects where id = @object_id1)
PRINT ' '


execute dbo.sp_helpindex_custom @tablename,@indexname, @INDEXKEYS OUTPUT
print @tablename +' - '+ @indexname+' - ' + @INDEXKEYS


set @OBJECTNAME = (select [name] from sysobjects where xtype = 'u' and id = @object_id1)
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
IF @INDEXKEYS IS NULL
BEGIN;
PRINT N'No Indexes';
END;
IF @UNIQUE =1 SET @UNIQUEVALUE = 'UNIQUE'
IF @UNIQUE =0 SET @UNIQUEVALUE = ''
if @UNIQUEVALUE = 'Unique' SELECT @SQL = 'CREATE '+ @UNIQUEVALUE + ' ' + @TYPE+ ' INDEX ' + ' ' + '['+@INDEXNAME +']'+ ' ON '+@OBJECTNAME+ ' ('+ @indexkeys + ' ASC)'+' WITH (FILLFACTOR = 1,DROP_EXISTING = ON);'
if @UNIQUEVALUE = '' SELECT @SQL = 'ALTER INDEX '+ @INDEXNAME + ' ON '+@OBJECTNAME+' REBUILD;'

PRINT @SQL


fetch next from curCursor into @indexname,@type, @UNIQUE,@object_id1,@INDEXID

END
CLOSE curCursor
DEALLOCATE curCursor

 

[nathanharcup]

Got in working by copying the values into a temp table

Now...Can anyone explain why PK indexes do not defragment if re-built or re-organised?

Cheers