Hello. Android!!

Declare @TblName sysname, @ObjId int, @IndexName sysname, @IndexId int, @GroupId int

, @ignore_dup_key bit, @is_unique bit, @is_hypothetical bit, @is_primary_key bit, @is_unique_key bit

, @keys nvarchar(2126), @groupname sysname

declare TblObjects cursor local static for

Select objects.object_Id, objects.Name, Indexes.Name, index_id, data_space_id

, ignore_dup_key, is_unique, is_hypothetical, is_primary_key, is_unique_constraint

From sys.objects As objects

Inner Join sys.indexes As Indexes On objects.object_Id=Indexes.object_Id

Where objects.Type='U'

open TblObjects

fetch TblObjects into @ObjId, @TblName, @IndexName, @IndexId, @GroupId, 

@ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key

Create Table #Index

(

tblName sysname

, indexName sysname null

, IndexId int

, ignore_dup_key bit

, is_unique bit

, is_hypothetical bit

, is_primary_key bit

, is_unique_key bit

, groupname sysname collate database_default NULL

, indexKeys nvarchar(2126)

)

while @@fetch_status >= 0

begin

declare @i int, @thiskey nvarchar(131) 

select @keys = index_col(@TblName, @IndexId, 1), @i = 2

if (indexkey_property(@objid, @IndexId, 1, 'isdescending') = 1)

select @keys = @keys  + '(-)'

select @thiskey = index_col(@TblName, @IndexId, @i)

if ((@thiskey is not null) and (indexkey_property(@objid, @IndexId, @i, 'isdescending') = 1))

select @thiskey = @thiskey + '(-)'

while (@thiskey is not null )

begin

select @keys = @keys + ', ' + @thiskey, @i = @i + 1

select @thiskey = index_col(@TblName, @IndexId, @i)

if ((@thiskey is not null) and (indexkey_property(@objid, @IndexId, @i, 'isdescending') = 1))

select @thiskey = @thiskey + '(-)'

end

select @groupname = null

select @groupname = name from sys.data_spaces where data_space_id = @groupid

-- INSERT ROW FOR INDEX

insert into #Index values (@TblName, @IndexName, @IndexId

, @ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key

, @groupname, @keys)

-- Next index

fetch TblObjects into @ObjId, @TblName, @IndexName, @IndexId, @GroupId, 

@ignore_dup_key, @is_unique, @is_hypothetical, @is_primary_key, @is_unique_key

end

deallocate TblObjects

select

tblName,

indexName,

indexKeys,

PK=case when is_primary_key <>0 then 'PK' else 'IX' end,

UniqueYN=case when is_unique <>0 then 'Y' else 'N' end,

'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group

case when IndexId = 1 then 'clustered' else 'nonclustered' end

+ case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end

+ case when is_unique <>0 then ', unique' else '' end

+ case when is_hypothetical <>0 then ', hypothetical' else '' end

+ case when is_primary_key <>0 then ', primary key' else '' end

+ case when is_unique_key <>0 then ', unique key' else '' end

+ ' located on ' + groupname)

from #Index

order by tblName, IndexId, indexName

Drop Table #Index

데이터를 입력,수정,삭제를 하다보면 인덱스에 조각화가 일어난다.

조각화가 많아지면 성능이 느려진다.

1. 인덱스를 다시 작성하는 방법에는 

① DBCC DBREINDEX

② DBCC INDEXDEFRAG

③ ALTER INDEX ALL ON 테이블명 REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90)

여기에서 속도도 가장 빠르고 효율적인 것은 MS-SQL 2005 이상에서 지원하는 아래의 쿼리다.

ALTER INDEX ALL ON 테이블명 REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90)

2. 해당 데이터베이스의 모든 테이블의 모든 인덱스를 재정리하는 쿼리

=========================================================================================

DECLARE @i int, @sql varchar(1000)

DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR 

 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME

OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor

INTO @ownerName, @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '

 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'

 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor

 INTO @ownerName, @tablename

END

CLOSE DB_Cursor

DEALLOCATE DB_Cursor

=========================================================================================

$(document).ready(function(){

/* data-role="content" 높이 100%로 채우기 */

scroll(0, 0);

var header = $("div[data-role='header']:visible");

var footer = $("div[data-role='footer']:visible");

var content = $("div[data-role='content']:visible");

var viewport_height = $(window).height();

var content_height = viewport_height - header.outerHeight() - footer.outerHeight();

content_height -= (content.outerHeight() - content.height());

$("div[data-role='content']").css('min-height',content_height);

});