Hello. Android!!

1. word : 중복데이터 포함 필드

2. recno : 중복되지 않는 고유번호

3. tableName : 테이블명

4. 삭제 쿼리

DELETE FROM tableName

WHERE recno IN ( 

SELECT a.recno AS recno 

FROM tableName A INNER JOIN ( SELECT MAX(recno) AS recno, word, count(*) AS loginCount FROM tableName GROUP BY word HAVING count(*) >1) B

ON A.word = B.word and A.recno <> B.recno )

mssql 2005 이전 버전
exec sp_changeobjectowner '유저명.objects명(테이블 또는 프로시져)','dbo'

mssql 2005
alter schema dbo transfer 유저명.objects명(테이블 또는 프로시져)

예)
snjgame이 소유한 aaa테이블, bbb프로시져

mssql 2005이전

exec sp_changeobjectowner 'snjgame.aaa','dbo'
exec sp_changeobjectowner 'snjgame.bbb','dbo'

mssql 2005

alter schema dbo transfer SagoAdmin.UP_NT_SAGO_CHARGEINFO_CANCEL;

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

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

SET NOCOUNT ON

CREATE TABLE #TBLSize 

( 

Tblname varchar(80), 

TblRows int, 

TblReserved varchar(80), 

TblData varchar(80), 

TblIndex_Size varchar(80), 

TblUnused varchar(80) 

)

DECLARE @DBname varchar(80) 

DECLARE @tablename varchar(80)

SELECT @DBname = DB_NAME(DB_ID()) 

PRINT 'User Table size Report for (Server / Database): ' + @@ServerName + ' / ' + @DBName

PRINT '' 

PRINT 'By Size Descending'

DECLARE TblName_cursor CURSOR FOR 

SELECT NAME 

FROM sysobjects 

WHERE xType = 'U'

OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor 

INTO @tablename

WHILE @@FETCH_STATUS = 0 

BEGIN 

INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused) 

EXEC Sp_SpaceUsed @tablename

-- Get the next author. 

FETCH NEXT FROM TblName_cursor 

INTO @tablename 

END

CLOSE TblName_cursor 

DEALLOCATE TblName_cursor

SELECT CAST(Tblname as Varchar(30)) 'Table', 

CAST(TblRows as Varchar(14)) 'Row Count', 

CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)', 

CAST(TblData as Varchar(14)) 'Data Space', 

CAST(TblIndex_Size as Varchar(14)) 'Index Space', 

CAST(TblUnused as Varchar(14)) 'Unused Space' 

FROM #tblSize 

Order by 'Total Space (KB)' Desc

PRINT '' 

PRINT 'By Table Name Alphabetical'

SELECT CAST(Tblname as Varchar(30)) 'Table', 

CAST(TblRows as Varchar(14)) 'Row Count', 

CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)', 

CAST(TblData as Varchar(14)) 'Data Space', 

CAST(TblIndex_Size as Varchar(14)) 'Index Space', 

CAST(TblUnused as Varchar(14)) 'Unused Space' 

FROM #tblSize 

Order by 'Table'

DROP TABLE #TblSize

SET NOCOUNT OFF

declare cur cursor for  -- 커서선언

 select a.sayear, a.entsubno, d.saitmcd

   from TEST a

        -- 조회쿼리 

open cur

        -- 조회쿼리 항목들을 변수로 선언 (갯수가 동일해야 한다 )

 declare @year char(4)

  ,@entsubno char(6)

  ,@saitmcd char(11)

 fetch next from cur into @year, @entsubno, @saitmcd

 while @@fetch_status = 0

 begin 

  insert into TEST_subitm ( sayear, entsubno, saitmcd)

  values (@year,@entsubno,@saitmcd)

                --실행쿼리부분

 fetch next from cur into @year, @entsubno, @saitmcd

 end

close cur

deallocate cur

---------------------------------------------------

전체적인순서

1. 쿼리로 커서선언

2. 커서 OPEN

3. 데이터 선언

4. 첫번째 레코드의 값을 변수에 셋팅

5. 패치상태가 정상이면 while 반복문 실행

6. 반복문 시작 

7. 실행쿼리 실행

8. 다음 레코드의 값을 변수에 셋팅

9. 반복문 종료

10. 커서 CLOSE

11. 커서메모리 반환