헝그리코더

대용량 데이터 행 삭제 작업 시 실행 조건 없이 삭제 할 경우 성능 감소 및 큰 문제를 몇가지 가지고 있다.

SQL 서버는 트랜잭션을 처리하기 위하여 트랜잭션 로그에 모든 변경 사항을 작성하고, 완전한 테이블 잠금을 수행할 것이다.

예를 들어 서비스중인 응용프로그램에서 해당 테이블에 액세스를 필요로 하는 경우, 응용프로그램은 테이블을 사용할 수 있을때까지 기다려야 하며. 응용프로그램에 타임아웃이 설정되어있는경우 응용프로그램을 사용하는 사용자는 정상적인 응답결과를 받지 못 할 것이다.

해결 방안으로 아래 테스트 코드를 참고하여 TOP LOOP 내에서 행을 반복적으로 삭제 처리해본다.

1. Log 테스트용 테이블 생성

CREATE TABLE [dbo].[Log](
    [id] [int] NOT NULL,
    [logDateTime] [datetime] NULL,
 CONSTRAINT [PK_LogTest] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2. While문을 이용한 테스트 데이터 생성
데이터 만건을 넣은후에 테스트.

declare @loopCnt int 
declare @logDateTime datetime

set @loopCnt = 1
set @logDateTime = '2001-01-01'

while (@loopCnt < 10000)
begin

    INSERT INTO [dbo].[Log] ([id],[logDateTime]) VALUES (@loopCnt,@logDateTime)
    set @loopCnt = @loopCnt + 1
    set @logDateTime = DATEADD(second,1,@logDateTime)

end

3. DELTE TOP문을 이용한삭제
일괄로 삭제시 시간이 오래 걸리므로 TOP문을 사용해서 데이터를 삭제.

이때 Loop문을 사용하여 DELETE문사이에 적절한 Delay를 주어 시스템 멈추는 부분을 최소화 하길 추천한다. 

삭제문의 경우는 복구가 어려우므로 충분한 테스트를 하신후에 사용하길. 

@@rowcount는 전역변수이므로 delete top문을 loop문 마지막에 위치.

delete top(1000)    from LogTest where logdatetime < '2021-12-01 10:00'

while (@@rowcount > 0)
BEGIN

    --delay 100m/s
    waitfor delay '0:0:0:100'

    delete top (1000)  from Log where logdatetime < '2021-12-01 10:00'

END

１. Database 관련 Naming Rule 

가.  Database Schema Name

1)   규칙

█    Database Profile 이름을 의미함

█    DB Alias 이름과 동일하게 함

█    영문 대문자로 작성함

█    Database Short Name의 길이는 최대 8자리를 넘을 수 없음

█    Database Short Name은 각 Site의 Unique한 Name을 사용함

2)   표기 방식

<Database Short Name>
예) TOURDB, ETKP, TKS…

나.  Table Name

1)   규칙

█    테이블임을 표시하기 위해 테이블 명 뒤에 ‘_TB’ 라는 구분을 사용함

█    테이블명은 대문자로 사용함

█    시스템 구분 코드와 모듈구분코드로 업무 영역을 구분함

█    의미있는 테이블명은 3단어까지 사용할 수 있음

█    단어와 단어 사이는 ‘_’로 구성함

█    각 단어는 최대 8자리까지 사용함

█    구분명은 Table의 특성을 나타냄

█    예로는 Master, Detail, Control, Summary, Trigger, History 등이 있음

2)   표기 방식

<시스템 구분> + ‘_’ + <의미있는 테이블명> + ‘_’ + TB

예) 사용자 테이블 : ACT_USERS_TB

다.  Column Name

1)   규칙

█    물리명은 영문 대문자를 이용함. 논리명을 사용자가 알 수 있는 정도에서 명사 및 명사형동사를 사용함

█    Column에 대한 자리수는 총 12자리로 하며, 제한은 없음

단, 사용하는 Database의 특성에 따라 제한될 수 있음

█    Word와 Word 사이에는 ‘_’로 구분함

█    한 Word는 8자리를 넘을 수 없음

█    모든 Column은 Dictionary List에 등록된 약어사전 및 자료사전을 기초로 작성함

█    Dictionary List에 등록되지 않은 약어는 책임자의 동의 하에 등록함

█    Column Name은 약어의 조합으로 구성

█    컬럼명에 컬럼을 대표하는 접미사를 사용하여 컬럼명의 성격을 나타냄.

2)   표기방식

<의미있는 컬럼명> 혹은 <의미있는 컬럼명> + ‘_’ + 접미사
종종 자주 사용하는 접미사는 다음과 같다.

3)   순서규칙

█    기본적으로 관계형 모델에서 열(Column)의 순서는 의미가 없음. 그러나, 물리적인 형태로 생성되어 관리될 때에는 보다 효율적인 저장공간의 관리를 위해 다음 순서에 따라 우선순위를 결정함

█    Primary Key가 우선함

█    Primary Key내에서는 Index 의미에 따라 순서를 결정함

█    Not Null Columns이 우선함

█    Not Null Columns 내에서는 Foreign Key, Attributes 순서로 함

█    Null Columns 내에서는 다음의 규칙에 따라 순서를 결정함

█    Fixed Length Columns이 우선함(Date,Number,Char순)

█    Smaller Length Column이 우선함

라.  Index Name

1)   규칙

█    해당하는 테이블명 뒤에 ‘_IX’를 붙여 index임을 명확히 함

█    대문자를 사용함

█    일련번호는 01 ~ 99까지 사용할 수 있음

█    MSSQL의 경우 클러스터드 인덱스와 넌 클러스터드 인덱스를 구분하여 작성함. 클러스터드 인덱스 _IXC를 사용하며, 넌 클러스터드 인덱스는 일반 인덱스 명 룰을 따름.

█    테이블에 인덱스가 하나만 존재할 경우 일련번호를 사용하지 않아도 됨.

2)   표기 방식

<시스템 구분> + ‘_’ + <의미있는 테이블명> + ‘_’ + IX{<일련번호>}

예) Table명 ‘I01_MASTER_TB’의 Index : I01_MASTER_IX01

마.  Primary Key Name

1)   규칙

█    영문 대문자로 작성함

█    해당하는 테이블명의 맨 뒤에 ‘_PK’라는 구분을 사용함

2)   표기방식

<시스템 구분> + ‘_’ + <의미있는 테이블명> + ‘_’ + PK

예) Table 명 ‘AC_USERS_TB’의 Primary Key : AC_USERS_PK

바.  Foreign Key Name

1)   규칙

█    영문 대문자로 작성함

█    해당하는 테이블명의 맨 뒤에 ‘_FK’라는 구분을 사용함

█    일반적으로 테이블명과 컬럼명까지 사용하나, OBJECT의 명칭이 길어져서 테이블명을 기준으로 작성함.

█    일련번호 : 1 ~ 9

2)   표기방식

<시스템 구분> + ‘_’ + <의미있는 테이블명> + ‘_’ + FK{<일련번호>}

예) Table 명 ‘I01_MASTER_TB’의 Foreign Key : I01_MASTER_FK1

사.  Stored Procedure Name

1)   규칙

█    길이는 큰 제한이 없으나 오라클의 OBJECT NAME 길이 제한은 있음.

█    해당하는 테이블명의 맨 뒤에 ‘_SP’라는 구분을 사용함

█    기능명은 복수개 사용이 가능하면 3개의 단어를 넘지 않도록 함

█    기능을 나타내는 명칭이 하나일 경우 일련번호를 생략해도 됨.

█    단어간에는 ‘_’로 구분함

█    업무룰에 해당되지 않는, 혹은 특정 테이블에 해당되지 않는 DBMS 전반적인 프로시저의 경우, 시스템 프로시저로 작성하는 경우에는 시스템구분 과 테이블명을 생략하고 간단히 작성할 수 있다. 예) 스키마 스크립트 GENERATION – GENERATE_SP

█    오라클의 경우 패키지 내부의 프로시저의 경우 패키지 명칭에 시스템구분을 사용하므로, 프로시저나 함수명에 시스템구분 코드를 넣지 않는다. 또한 기능에 따른 일련번호를 사용하지 않고 OOP의 기능인 Method Overloading 의 기능을 사용하여 작성한다. 또한 명칭은 Camel 표기법을 사용하여 작성한다. 예) 사용자를 가져오는 경우 getUsers()

2)   표기방식

<시스템 구분> + ‘_’ + <의미있는 테이블명> + ‘_’ + <기능명>{<일련번호>} + ‘_’ + SP

예) ‘I01_MASTER_TB’ 테이블에서 데이타 입력에 대한 Procedure

    : I01_MASTER_INS01_SP

아.  Function Name

1)   규칙

█    길이는 제한이 없으며 영문 대문자를 사용함

█    해당하는 테이블명의 맨 뒤에 ‘_FC’라는 구분을 사용함을 원칙으로 하나, 함수명이 길어서 사용상 불편할 경우, 특정 시스템에 국한하지 않고, 항상사용하는 라이브러리 같은 함수의 경우 구분가능한 Short Name을 사용해도 무방하다.

█    단어간에는 ‘_’로 구분함

█    시스템 함수로 작성한 경우에는 접미사를 사용하지 않고, 간략한 함수이름을 사용한다. 예) INSTR, LEASTR(@x bigint, @y bigint) 등

█    오라클의 경우 패키지 내부의 함수의 경우에는 프로시저의 해당 규칙에 따른다. 즉 시스템구분 코드와 접미사를 사용하지 않고, Camel 표기법으로 간략하게 작성한다.

2)   표기방식

<시스템 구분> + ‘_’ + <기능명> + ‘_’ + FC

예) ‘I01_MASTER_TB’ 테이블에서 주소명를 가져오기 위한 Function

    : I01_GET_ADDRESSNAME_FC(p_AddressCode IN Char) 내지는

: getAddressName(p_AddressCode IN Char)

자.  Table Trigger Name

1)   규칙

█    영문 대문자로 작성함

█    일련번호는 01 ~ 99까지 사용 가능함

2)   표기방식

<시스템 구분> + ‘_’ + <의미있는 테이블명> + ‘_’ + <Timing><Trigger Event><일련번호> + ‘_’ + TG

® Timing : B(Before), A(After)

® Trigger Event : I(Insert), D(Delete), U(Update)

예) ‘I01_MASTER_TB’ 테이블에서 데이타 입력 후에 실행되는 Trigger

: I01_MASTER_AU01_TG

차.  View Name

1)   규칙

█    길이는 제한이 없으며, 영문 대문자로 작성함

█    해당하는 테이블명의 맨 뒤에 ‘_VW’라는 구분을 사용함

█    일련번호는 01 ~ 99까지 사용할 수 있음

2)   표기방식

<시스템 구분> + ‘_’ + <의미있는 테이블명><일련번호> + ‘_’ + VW

예) AC_ADMINL_USER_VW

카.  Sequence Name <오라클의 경우에만 해당>

1)   규칙

█    길이는 제한이 없으며 영문 대문자를 사용함

█    해당하는 테이블명의 맨 뒤에 ‘_SQ’라는 구분을 사용함

2)   표기방식

<시스템 구분> + ‘_’ + <의미있는 테이블명> + ‘_’ + SQ

예) ‘I01_MASTER_TB’ 테이블의 Sequence : I01_MASTER_SQ

타.  Package Name<오라클의 경우에만 해당>

1)   규칙

█    길이는 제한이 없으며 영문 대문자를 사용함

█    해당하는 테이블명의 맨 뒤에 ‘_PKG’라는 구분을 사용함

2)   표기방식

<시스템 구분> + ‘_’ + <의미있는 패키지명> + ‘_’ + PKG

예) 검색엔진에서 사용하는 자원에 관련된 패키지 : SCH__PKG

파.  Check 제약조건

1)   규칙

█    길이는 제한이 없으며 영문 대문자를 사용함

█    기존의 명칭룰에 해당하는 접미사를 사용하지 않고, 예외적으로 접두어 CK_를 사용한다. 일반적으로 CHECK와 DEFAULT 제약조건은 특정 테이블에 한정시켜서 작성하기 보다는 시스템 전반에 걸쳐서 사용이 가능하므로 예외규정을 둔다.

2)   표기방식

CK + ‘_’ + <의미있는 CHECK명>

예) 이메일 체크 : CK_EMAIL

예) 성별 체크 : CK_SEX

하.  Default 제약조건

1)   규칙

█    길이는 제한이 없으며 영문 대문자를 사용함

█    기존의 명칭룰에 해당하는 접미사를 사용하지 않고, 예외적으로 접두어 DF_를 사용한다. 일반적으로 CHECK와 DEFAULT 제약조건은 특정 테이블에 한정시켜서 작성하기 보다는 시스템 전반에 걸쳐서 사용이 가능하므로 예외규정을 둔다

2)   표기방식

DF + ‘_’ + <의미있는 DEFAULT명>

예) Null String Default – DF_NULLSTR
예) 0(Zero) Default – DF_ZERO

1. 디비 서버 유저 조회 쿼리

select * from sys.server_principals;

2. 특정 유저 로그인 정보 조회 쿼리

sp_helplogins [loginID] ;

2–1. 특정 유저 로그인 정보 변경

alter login [loginID] with password=’password’ unlock
                     ,check_policy=off
                     ,check_expiration=off
                     ,default_database=[dbname]
                     ,name=[newloginID];

check_policy = sql server 윈도우 비밀번호 정책 사용여부
check_expiration = sql server 비밀번호 만료 정책 적용 여부
default_database = DB 로그인 시 할당할 기본 데이터베이스 지정
name = 로그인ID 변경
unlock = 잠금해체

3. DB 소유자 조회 쿼리

select 	  owner_sid, suser_sname(owner_sid) ‘owner name’, physical_database_name 
 from 	  sys.databases where name= [dbname];

3–1. DB 권한 수정- db 소유자 변경

sp_changedbowner ‘loginID’

4. DB 역할 조회

select * 
from sys.database_principals;

select name
      ,default_schema_name
      ,owning_principal_id
      ,create_date
      ,authentication_type
      ,authentication_type_desc 
from   sys.database_principals;

5. 접속한 데이터베이스 스키마 조회

select * from sys.schemas;

6. 특정유저 특정 스키마 권한 부여

grant select, excute, insert, update, view definition on schema::[schema] to [loginID];

7. 리눅스에서 sql 파일 실행 시 터미널 열어서 sql파일 위치 폴더 경로 이동 후

sqlcmd -S 192.168.0.0 -i data.sql -o log.text -U loginID -P password -d dbName

-S = 서버 ip
-i = 파일명
-o = 실행결과 로그파일
-U = 유저ID
-d = 데이터베이스명

DECLARE @SEARCH NVARCHAR(MAX) = '검색텍스트';

SELECT B.NAME AS NAME -- 이름
     , B.TYPE_DESC AS TYPE -- 타입 구분
     , A.DEFINITION AS CONTENTS -- 내용
FROM  SYS.SQL_MODULES A WITH (NOLOCK)
LEFT JOIN SYS.OBJECTS B WITH (NOLOCK) ON A.OBJECT_ID = B.OBJECT_ID
WHERE DEFINITION LIKE '%' + @SEARCH+ '%'
ORDER BY TYPE, NAME

* 분석 함수 (통계)

-- Pivot / UnPivot
-- 한 열에 포함된 여러 값을 출력. 컬럼으로 변환하여 테이블 반환 식을 회전하고 
-- 필요하면 집계까지 수행할 수 있다.

SELECT *
FROM MemberGame
Pivot( Sum(cash) -- 집계 함수(열)

    For userLevel -- 새로운 컬럼으로 변경할 컬럼명

    In ([1],[2],[3],[4],[5]) -- 컬럼 목록

) AS resultPivot; -- 피벗 이름


-- SQL Server 2012부터 제공
-- 다음 행과의 값의 차이 : LEAD(컬럼명, 다음 행 위치, 다음 행이 없을 경우 자기자신)

SELECT userXp, userXp - (LEAD(userXp, 1,0) OVER(Order By userXp Desc)) as [다음 회원과 userXp 차이], *
FROM MemberGame


-- 이전 행과의 값의 차이 : LEAD(컬럼명, 이전 행 위치, 이전 행이 없을 경우 자기자신)
SELECT userXp, userXp - (LAG(userXp, 1,0) OVER(Order By userXp Desc)) as [이전 회원과 userXp 차이], *
FROM MemberGame


-- 그룹의 가장 큰 값과의 차이
SELECT userLevel, userXp, userXp - (FIRST_VALUE(userXp) OVER(Partition By userLevel Order by userXp Desc)) as [다음 회원과 userXp 차이], *
FROM MemberGame


-- 누적 합계 (자신의 값과 같거나 큰 경우)
SELECT userLevel, userXp, (CUME_DIST() OVER(Partition By userLevel Order by userXp Desc)) * 100 as [누적인원 백분율%], *
FROM MemberGame




-- 그룹별 중앙값 계산
SELECT Distinct userLevel, PERCENTILE_CONT(0.5) Within Group(Order By userXp) Over(Partition By userLevel) as [userLevel 별 중앙값]
FROM MemberGame 

-- PERCENTILE_CONT 0.0~1.0 사이의 백분율 (0.5이면 정확히 중간값)
-- Error : 현재 호환성 모드에서는 PERCENTILE_CONT 함수를 사용할 수 없습니다. 110 모드 이상에서만 허용됩니다.


-- 그룹별 중앙위치 계산
SELECT Distinct userLevel, PERCENTILE_DISC(0.5) Within Group(Order By userXp) Over(Partition By userLevel) as [userLevel 별 중앙값]
FROM MemberGame 
-- PERCENTILE_CONT 0.0~1.0 사이의 백분율 (0.5이면 정확히 위치값)
-- Error : 현재 호환성 모드에서는 PERCENTILE_CONT 함수를 사용할 수 없습니다. 110 모드 이상에서만 허용됩니다.