SQL Tips
MS-SQL SERVER + Transact SQL Tip
IP 문자열에서 a.b.c 추출하기
declare @ip varchar(20)
SET @ip = '111.22.33.22'
SELECT LEFT(@ip,len(@ip)-patindex('%.%',reverse(@ip)))
리포팅 서비스
IIS + ASP + VisualStudio.NET 을 이용한 리포트 서비스 제작
FTP 파일 업로드
DTS 에서 제공하는 파일 전송 프로토콜 작업(FTPTask)의 경우, 다운로드만 가능하고 업로드는 지원하지 않는다. 가령, 로컬 백업한 .bak, .trn 파일을 리모트 서버로 옮기려면 업로드 스크립트를 SQL 이나 VB 또는 Python으로 직접 개발해야 한다.
복구 모델
당연히, 트랜잭션 로그를 남기려면 FULL 복구 모델로 데이터베이스를 설정해야 한다. 백업 정책을 세울 때 꼭 모델을 체크하기 바란다. (기본적으로 FULL 모델로 만들어지는 것 같지만, 필수적으로 체크할 것)
SQLServerAgent
유지 관리 계획 등 주기적인 작업을 실행하려면 기본적으로 꺼져있는 SqlServerAgent 를 켜야 한다.
테이블 스키마 복사
스크립트로 추출해서 다른 DB 에 설치할 경우 디폴트값이 지정되지 않는 경우가 있다. 이때 DEFAULT 등 제약조건 스크립팅에 꼭 체크를 해야 한다. 아니면 그냥 데이터 내보내기를 사용하길.
NULL 비교
NULL 에 대한 비교는 is 로 하라!
declare @a int
SET @a = NULL
IF @a = NULL
print '@a=NULL'
IF @a IS NULL
print '@a is NULL'
declare @b int
SET @b = 1
IF @b <> NULL
print '@b <> NULL'
IF @b IS NOT NULL
print '@b is not NULL'
-- 실행 결과
@a IS NULL
@b IS NOT NULL
constraint 관리
디폴트 constraint 를 삭제하고 다시 추가하는 법...
-- 삭제
ALTER TABLE [dbo].[테이블] DROP
CONSTRAINT [DF_테이블_컬럼]
-- 추가
ALTER TABLE [dbo].[테이블] WITH NOCHECK ADD
CONSTRAINT [DF_테이블_컬럼] DEFAULT (디폴트값) FOR [관련컬럼]
CASE WHEN...
다음은 승률을 나타내는 컬럼을 계산해오는 방식이다.
SELECT
....
CASE
WHEN (WinCount + LoseCount + DrawCount) = 0 THEN 0.00
ELSE round((WinCount * 1.0) / (WinCount + LoseCount + DrawCount) , 2)
END AS WinRate,
....
SELECT...INTO...
INSERT INTO...SELECT...가 존재하는 테이블에 데이터를 넣을 때 사용한다면, SELECT...INTO...는 새로운 테이블을 만들어 넣을 때 사용한다. 특히 통계 테이블에 데이터를 넣을 때, TRUNCATE TABLE xxx; INSERT INTO...SELECT를 하게 되면, 문제가 생길 수 있으므로 아예 DROP 한 다음 SELECT...INTO... 를 사용하도록 한다. (우리 DBA의 말씀~)
SELECT.. INTO는 MS-SQL의 기능! . 일반적인 ANSI-SQL에서는 CREATE TABLE ... SELECT 를 사용함.
대용량 데이터 빨리 넣기
- 관련 데이터베이스의 로그 타입 변경 : EM에서 데이터베이스 등록정보를 선택, 복구 모델을 단순 또는 대량 로그로 임시 변경해준다.
- ALTER TABLE ... 로 관련 테이블의 PK 제거하기. 그러나, FK 관계에 있을 경우에도 제거를 해야 하므로 EM 또는 VS 의 DB 다이어그램에서 삭제하는게 제일 간편하다. 단. 복구는 수동 -_-;
- DROP INDEX ...로 관련 테이블의 인덱스 제거하기. 그러나, 다시 복구해야 하므로... 수작업 필수
- BULK INSERT : DB 스트레스 테스트를 위해 백만건 이상의 데이터를 넣어야 할 때 사용하면 간편하다. 단 샘플 데이터 파일을 제공해줘야 한다.
- INSERT INTO xxx () SELECT ...로 자기 자신 혹은 관련 테이블을 활용할 것. while + insert 1 보다 훨씬 빠르다. 테스트해본 결과 한줄씩 insert 할 경우 10만건에 40초, 그러나 위의 방법을 쓰면 2초로 해결 가능. 단, 용량이 너무 커질 경우 실행이 안되므로, 범위를 나눠야 할 듯. 300만건에서는 대충 됨.
- PK/FK/Index/Trigger 복구하기. 800만건이 들어있는 테이블의 PK 를 복구하는데 Xeon Dual 0.8G 에서 무려 '''5분'''이나 걸렸다.
ALTER TABLE xxx ADD CONSTRAINT PK_xxx_yyy PRIMARY KEY(yyy)
ALTER TABLE xxx ADD CONSTRAINT FK_xxx_yyy FORENGN KEY REFERENCES zzz(yyy) ON DELETE CASCADE
CREATE [UNIQUE] INDEX IX_xxx_yyy ON xxx(yyy)
데드락 해결하기
sp_who2
sp_lock pid
KILL pid
like 와 인덱스
- col like 'userid%'
- col like '%userid%'
- col like '%userid'
오직 1번만이 인덱스를 타게 된다. Index는 Binary Tree로 구성되어 있기 때문에 처음부터 찾아서 들어가는 것임. 당연히 첫자를 알 수 없다면 index를 경유하지 못함!!!
Update Trigger
특정 컬럼이 변경된 경우 로그 테이블에 row 를 추가해야할 경우 Update Trigger 를 사용하면 간편하다. 가령, 플레이어 레벨이 바뀌었을 때 레벨 테이블에 insert 를 해야 한다고 가정하면 트리거는 대략 다음과 같다.
CREATE TRIGGER trigger_name ON src_tbl FOR UPDATE
AS
IF UPDATE(level)
begin
declare @old_level smallint, @new_level smallint
SELECT @new_level = [Level] FROM inserted
SELECT @old_level = [Level] FROM deleted
IF @old_level <> @new_level
begin
INSERT INTO log_tbl (....) VALUES (...,@old_level,@new_level,...)
end
RETURN
end
@@IDENTITY vs. SCOPE_IDENTITY()
전자의 경우, 현재 세션에서의 최종적인 IDENTITY 컬럼의 값을 리턴해준다. 단, 트리거에 의해서 다른 테이블에서 IDENTITY 가 바뀐 경우 그 값을 리턴해준다. 따라서, 최종적으로 직접 만진 테이블의 IDENTITY 값을 알아오려면 SCOPE_IDENTITY() 를 사용하는 것이 안전하다.
UPDLOCK
쿼리 분석기에서 좌측 쿼리를 먼저 실행한 후 우측 쿼리를 실행할 때, 첫번째 예제의 경우 그냥 실행이 되지만 두번째 예제는 block 된다. 오라클에는 select for update 라는 게 있다나...
| nonblocked query | |
| begin trans | select * from users |
| select * from users with (updlock) | |
| blocked query | |
| begin trans | select * from users with (updlock) |
| select * from users with (updlock) |
DB레벨에서 중복로그인을 처리할 경우, DB 컬럼에 LogOn flag 를 둬야 한다. LOGOFF - LOGON - GAME 이런 식의 상태 변화가 있다고 가정하면, 로그인시 LOGOFF -> LOGON 으로 바꿔주고 로그아웃시 LOGON -> LOGOFF 로 설정하게 될 것이다. 문제는 이걸 언제 어떻게 체크해서 바꾸느냐인데...
-- simplified login procedure
declare @logon
SELECT @logon=LogOn FROM users WHERE ...
IF ( @logon <> LOGOFF )
begin
handle_error...
end
UPDATE users SET @logon = LOGON WHERE ...
이런 식으로 처리할 경우, abuser 에 의해서 동일 유저에 대한 로그온 프로시저가 DB에서 동시에 일어난다고 가정하면 중복로그인이 충분히 발생할 수 있게 된다. (아주 낮은 확률이지만...) 이를 손쉽게 막으려면 select with updlock를 사용하면 된다. (단, 이때 (Page,IU), (KEY,U), (Table,IX) 의 3가지의 락이 걸린다)
웬지 불필요한 lock 을 피하기 위해서는, update 의 where 절에서 다시 한번 LogOn 필드를 체크(즉 select 해온 뒤 누가 바꿨는지 다시 확인) 해주면 될 거 같다.
-- simplified login procedure
declare @logon
SELECT @logon=LogOn FROM users WHERE ...
IF ( @logon <> LOGOFF )
begin
handle_error...
end
UPDATE users SET @logon = LOGON WHERE ... AND logon = LOGOFF
IF ( @@rowcount <> 1 )
begin
handle_error...
end
테스트 해 본 결과 양 쪽에서 select 한 후 한쪽에서 update 를 하면, 다른 쪽의 update 는 트랜잭션에 의해 block 되며 상대 트랜잭션이 끝나면 실패를 리턴하게 된다. (만약 where 절의 체크가 없다면 둘 다 로그인 성공으로 간주되므로 주의할 것)
이게 consistent non-blocking read을 지원하기 때문인데, 간혹 조금 이해하기 힘든 현상이 일어날 때도 있지. 하지만 Query 하나 들어갈때마다 table에 lock을 걸어버리는 방법은 너무 위험부담이 크기 때문에. consistent read에 대한 설명이 필요하다면 다음 기회에..
sp_lock, sp_who2
- sp_lock : 현재 시스템에 존재하는 lock 을 보여준다.
- sp_who2 : 현재 시스템에서 실행되는 프로세스, 소유주, 접속지, 실행내용 등을 보여준다.
이 내용은 Enterprise Manager 의 관리에서 찾아볼 수 있다.
GOTO
어릴 적(?)부터 항상 GOTO 는 쓰지 말라는 이야기를 들어 왔지만, 프로시저에서 또 쓰게 될 줄은... 그러나, 트랜잭션이 존재할 경우 GOTO 를 쓰지 않으면 자꾸 if-else-if-else 로 indent 해들어가는 바람에 읽기가 힘들어져서...
begin transaction
....
....
IF ( error_found )
begin
rollback transaction
SET @errorcode = -1
goto Label
end
....
....
commit transaction
....
....
Label:
log something...
....
....
SET IDENTITY_INSERT
identity 컬럼의 경우 DELETE FROM tbl 하더라도 시드값은 계속 증가하게 되어 있다. 또한, 특정 값을 명시적으로 insert 할 수 없다. 이를 가능하게 하기 위해서는 아래와 같이 하면 된다.
SET identity_insert tbl ON
INSERT INTO tbl (identity_col,...) VALUES (N,...)
SET identity_insert tbl off
@@ERROR / @@ROWCOUNT
프로시저 중간에 insert 나 update 할 경우 혹시 모를 실패에 대비해서 아래와 같이 체크해야 한다.
INSERT ...
IF @@error <> 0
begin
rollback transaction
RETURN
end
update 의 경우 1개의 업데이트만 기대했는데, 2개가 업데이트된 경우 @@ERROR는 정상이 된다. 이때에는 @@ROWCOUNT 를 체크해야 한다.
시간 필드의 디폴트값
insert 시점의 시간을 저장하는 필드의 경우, 테이블 선언시
CREATE TABLE xxx
...
yyy DATETIME DEFAULT GETDATE()
...
라고 해주면 insert 할때 굳이 명시할 필요가 없다.
현재 시간 + a
dateadd( datepart, N, getdate() )
스크립트 디버깅
exec sp_sdidebug 'legacy_on'
시간 변환
convert(varchar(10),getdate(),121)
프로시저 실행시 파라미터
out 을 명시해주지 않으면 받아낼 수 없다
execute proc @param1, @param2, @param3 out, @param4 out