모델링 및 쿼리작성 - 신규개발(SI) rule

사이트별로 Rule도 상이하고 문서 양식도 다를 수 있으니 참고바란다.

DBMS는 Azure SQL server를 기준으로 했으나

실상 비용과 관련된 부분을 제외하면 MS-SQL server와 거의 동일하다.

 

 

1. 작명규칙  
 ㄴ 공통 : MS-SQL server에서 기본 제공되는 function, method, property 사용금지(ex :USER_ID)
 ㄴ 테이블 : 업무관련 이니셜로 대문자(필요시 언더바로 연결)
 ㄴ 컬럼 : 가독성 좋게 최대한 줄인 fullName(ex : ShipName) 으로 대소문자 혼용과 특수문자("_") 사용금지
 ㄴ PRIMARY KEY(CLUSETERED) : PK_스키마명_테이블명
 ㄴ FOREIGN KEY : FK_스키마명_테이블명
 ㄴ DEFAULT : DF_스키마명_테이블명_컬럼명
 ㄴ CHECK : CK_스키마명_테이블명_컬럼명
 ㄴ NONCLUSTERED INDEX : IX_스키마명_테이블명_넘버링(2자리)
 ㄴ CLUSTERED INDEX : CIX_스키마명_테이블명 - CLUSTERED PK가 없는 경우에 한해서
 ㄴ FILTERED INDEX : FIX_스키마명_테이블명_넘버링(2자리)
 ㄴ UNIQUE : UQ_스키마명_테이블명_컬럼명
 ㄴ VIEW : V_스키마명_테이블명_넘버링(2자리)
 ㄴ INDEXED VIEW : IV_스키마명_테이블명_넘버링(2자리)
 ㄴ TRIGGER : TR_스키마명_테이블명_넘버링(2자리)
 ㄴ PROCEDURE : SP_스키마명_주요업무명(대문자 언더바로 연결)
 ㄴ FUNCTION(SCALAR) : SF_스키마명_주요업무명(대문자 언더바로 연결)
 ㄴ FUNCTION(TABLE) : TF_스키마명_주요업무명(대문자 언더바로 연결)
 ㄴ 변수 : 외부 파라미터 - @P, 내부 변수 - @V_대응컬럼명
 
 
 
 
2. 모델링
 ㄴ 한글 사용 금지
 ㄴ 약속된(제공된) 양식에 맞는 테이블 정의서를 이용 
 ㄴ 컬럼의 확장(증가) 여부에 따라 EAV(역정규화) 적용여부 실무자와 신중하게 결정
   : 하나의 테이블에 컬럼수가 30개가 넘어가는 경우 사전 협의
 ㄴ 대량 테이블인 경우 헤더정보와 상세정보 테이블로 분할시 PK외 별도 컬럼 중복은 기본적으로 불허
 ㄴ 최초작성자(CrUser) 최초작성일시(CrDt), 최종수정자(UpUser), 최종수정일시(UpDt)
   : 최종수정일시(UpDt)의 경우 DEFAULT GETDATE()로 이용
 ㄴ 코드값과 같이 확정된 자리수가 아닌 경우 고정 문자열 사용금지
 ㄴ 개발자들이 쿼리 특성상 숫자타입 컬럼은 isnull함수를 사용하지 않는 경향이 있으므로 가급적 NOT NULL로 지정 후 기본값 설정
 ㄴ 조회 조건이 DATETIME인 경우가 거의 존재하지 않으므로 가급적 일자인 경우 CHAR(8) 로 지정
 ㄴ 일시와 일자가 동시에 필요한 경우 DEFAULT 혹은 COMPUTED COLUMN을 이용, 조회시 CASTING 으로 인한 퍼포먼스 저하 방지
 ㄴ 별도의 히스토리 테이블 생성보다는 파티션 설정 진행 하는 것이 현재 정책(AZURE 비용상승) 상 유리해 보임
 ㄴ [논의필요] PIPELINE과의 연결로 사용과 제거되었어야 하는 구분이 없다면 UPDATE 혹은 DELETE를 방지를 위해
   무조건 INSERT로만 진행하고 사용여부 UseYn과 같은 컬럼으로 처리하는 것이 좋아보임
   현재 AUDIT 정책(데이터 훼손방지 엄수)에도 부합하며 별도로 거대한 히스토리 테이블도 필요없음
 ㄴ COMMENT 추가 필수
 ㄴ SEQ의 경우 타 테이블과 RELATION이 없는 경우 IDENTITY 를 이용
 ㄴ IDENTITY의 경우 예상 row count를 담당자와 협의하여 INT 혹은 BIGINT 로 구분하여 사용
 ㄴ collate - 문자형 컬럼에는 정의시 무조건 COLLATE Korean_Wansung_CS_AS 함께 표기 
 ㄴ ONLINE 옵션의 사용 : NOCLUSTERED INDEX는 무조건 ONLINE 옵션 적용 
                    데이터의 무결성(수정/삭제)과 무관하게 BLOCK을 피할 경우 사용
 ㄴ PK 작성시 가급적 필수 조건절에 여부와 선택도가 낮은 순으로(가령 SEX라는 두가지 결과값이 존재하는 컬럼이 있다면 최우선) : SKIP INDEX SCAN유도 가능
   : 참고 사이트 https://cafe.naver.com/xlsvba/28494
 ㄴ PK를 포함한 INDEX 작성시 각 컬럼의 정렬(ASC, DESC) 구분도 반드시 검토(REVERSE 가 지원되나 종종 Top-N 알고리즘 STOP KEY로 간단하게 처리되는 경우가 많음)  
    : 참고 사이트 https://cafe.naver.com/xlsvba/28485 
 ㄴ 테이블 생성 예제
CREATE TABLE DBO.TMP_C 
(
      IDT        INT      NOT NULL
    , PARENT_IDT INT      NOT NULL
    , USE_YN     CHAR(1)  COLLATE Korean_Wansung_CS_AS NOT NULL CONSTRAINT DF_DBO_TMP_C_USE_YN DEFAULT 'Y'
    , CR_DATE    DATETIME NOT NULL CONSTRAINT DF_DBO_TMP_C_CR_DATE DEFAULT GETDATE()
    , CONSTRAINT PK_DBO_TMP_C PRIMARY KEY CLUSTERED(IDT)
    , CONSTRAINT CK_DBO_TMP_C_USE_YN CHECK (USE_YN IN('Y', 'N'))
    , CONSTRAINT FK_DBO_TMP_C_PARENT_IDT FOREIGN KEY (PARENT_IDT) REFERENCES DBO.TEST_P(IDT)

);


 
3. 조회
 ㄴ 특별한 경우가 아닌 경우 쿼리를 분할하여 내부 커서를 이용하지 말고(JAVA내 연산 지양) 부득이한 경우 SP 혹은 UDF로 관리하는 게 기본
 ㄴ SELECT절에 subauery 혹은 scalar UDF는 결과값이 20건 이하이거나 조인 컬럼 혹은 전달 인수의 DISTINCT COUNT가 10개 이하인 경우에 한하여 DBA와 상의 후 사용
   특별한 경우 사용불가
 ㄴ WHERE 절에 scalar UDF, CASE문 등의 사용시 협의(기본 불허)
 ㄴ AS 필수 사용
 ㄴ 조인시 필수 Driving table이 아닌 경우 결과값이 작은 순서데로 작성
   : 참조 https://cafe.naver.com/xlsvba/28556
 ㄴ 1라인 1컬럼, 1라인 1조인, 1라인 1컬럼 표기를 원칙으로
 ㄴ ANSI 쿼리를 원칙으로 하고 의미없는 CROSS JOIN 사용불가
 ㄴ 장문의 sql문의 경우 INLINE VIEW(SUBQUERY)보다 CTE(WITH) 사용 지향 - 가독성 측면과 튜닝시 용이
 ㄴ TEMP 테이블 사용시 DBA와 상의 - 테이블 변수로 대체가능할 수 있슴
 ㄴ 컴마(,)의 경우 문장 앞(왼)쪽으로
 ㄴ 동적 쿼리작성시에는 필수 조건절을 우선으로 하고 동적 조건절을 뒤쪽으로 하고 주석 삽입
 ㄴ 파라미터 타입 정확하게 엄수
 ㄴ 조건 대상 컬럼에 캐스팅 특별한 경우를 제외하고 사용금지(TRIM, CAST, SUBSTRING....) - 선 모델링시 필수 체크 
 ㄴ LIKE 문 사용시 좌우 모두 % 사용은 반드시 필요한 경우만 
   : 참고사이트 https://cafe.naver.com/xlsvba/28474
 ㄴ 시점에서 조금은 변경된 데이터가 수용 가능한 경우 NOLOCK 사용
 ㄴ VIEW MERGE 필요한 경우 명시적 APPLY절(LATEAL과 같은 기능) 사용
 ㄴ MAXDOP 힌트 사용금지(ETL을 포함한 배치가 아닌 이상 기본 불가)
 ㄴ INDEX지정 힌트 사용금지(DBA 협의 후 가능)
 ㄴ CURSOR 혹은 순환문 의 사용은 사전 협의 없이 사용불가
 ㄴ 전역 임시테이블(##) 사전 협의없이 사용불가
 ㄴ SP 사용시 SP내에서 또 다른 UDF 혹은 SP 호출 사전 협의 없이 사용불가(모니터링 툴의 활용성 떨어짐)
 ㄴ SP 혹은 UDF 사용시 TRY 분기 사용(에러시 ERROR_LINE() 필수) 및 로그 테이블 필요검증 후 필요시 로그 테이블 생성 혹은 기존 로그 테이블 활용
   : 참조 https://cafe.naver.com/xlsvba/28622
 
4. 삽입/수정/삭제
 ㄴ INSERT 시 DEFAULT 값을 최대한 활용하여 쿼리 다이어트하고 컬럼명을 개별적으로 하나하나 지정해야 함
 ㄴ UPDATE, DELETE는 정책상 어느 사이트나 민감하며 가급적 사용을 지양하고(모델링시 사용불가 지향)
   대량으로 변경(삭제시) 경합여부 확인 후 TABLOCK 힌트 사용 지향 
 ㄴ 테이블 전체 초기화(삭제)시에는 TRUNCATE TABLE 사용
 ㄴ 주기적으로 테이블 전체 데이터를 대부분 REFRESH하는 경우 테이블 재생성이 REINDEX 비용이나 조회 성능저하보다 유리할 수 있슴 
 ㄴ AUTO COMMIT이 기본 설정값이나 상황에 따라 TRANSACTION 메뉴얼 지정이 필요한 경우 반드시 설정
 
 
   
5. 기타
 ㄴ WBS 작성 필수(히스토리 포함)
 ㄴ 화면 정의서 필수(히스토리 포함)
 ㄴ 테이블 정의서 필수(EXCEL)
 ㄴ FK는 1차 안정화(모듈테스트) 후 생성하는 것으로 하고 이후 ERD 작성
 ㄴ 이슈 명세서 작성
 

 

이 글은 이비니어스( www.evinious.co.kr )의 소유이며 불펌은 허락하지 않습니다.

 

↓ ↓ ↓  개발문의는 아래 클릭 ↓ ↓ ↓