DataBase/MSSQL
-
MSSQL에서 프로시저에 대해 사용자에게 권한을 부여할 때 아래의 스크립트만 적용할 경우 오류가 발생하게 됩니다. GRANT CREATE PROCEDURE TO [UserName]; 왜 생성이 불가할까? CREATE 권한은 객체를 생성 할 수 있는 기능만을 제공 새로 생성되는 객체를 배치할 수 있는 공간이 필요한데 이 공간이 바로 스키마라는 컨테이너 따라서 CREATE 권한을 부여하기 위해서는 스키마를 변경할 수 있는 ALTER 권한이 필요합니다. --프로시저를 생성할 수 있는 권한 부여 GRANT CREATE PROCEDURE TO [UserName]; --스키마를 변경할 수 있는 권한 부여 GRANT ALTER ON SCHEMA::[dbo] TO [UserName]; 데이터베이스 수준 역할 부여 db..
[MSSQL] 프로시저 생성 권한 부여하기MSSQL에서 프로시저에 대해 사용자에게 권한을 부여할 때 아래의 스크립트만 적용할 경우 오류가 발생하게 됩니다. GRANT CREATE PROCEDURE TO [UserName]; 왜 생성이 불가할까? CREATE 권한은 객체를 생성 할 수 있는 기능만을 제공 새로 생성되는 객체를 배치할 수 있는 공간이 필요한데 이 공간이 바로 스키마라는 컨테이너 따라서 CREATE 권한을 부여하기 위해서는 스키마를 변경할 수 있는 ALTER 권한이 필요합니다. --프로시저를 생성할 수 있는 권한 부여 GRANT CREATE PROCEDURE TO [UserName]; --스키마를 변경할 수 있는 권한 부여 GRANT ALTER ON SCHEMA::[dbo] TO [UserName]; 데이터베이스 수준 역할 부여 db..
2021.04.06 -
MSSQL에는 CHOOSE라는 논리함수가 존재합니다. CHOOSE ( index, val_1, val_2 [, val_n ] ) 형태로 작성되며 간단한 예시로 아래의 쿼리문을 보시게되면 SELECT CHOOSE(1,'가','나','다','라') 아래의 쿼리를 실행하게 되면 인덱스 1에 위치한 문자열 '가' 가 출력이 됩니다. 요일 추출 방법 --CASE문 사용 SELECT CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN '일' WHEN 2 THEN '월' WHEN 3 THEN '화' WHEN 4 THEN '수' WHEN 5 THEN '목' WHEN 6 THEN '금' WHEN 7 THEN '토' END --CHOOSE문 사용 SELECT CHOOSE(DATEPART(WEE..
[MSSQL] CHOOSE문을 사용한 초간단 요일 추출 방법MSSQL에는 CHOOSE라는 논리함수가 존재합니다. CHOOSE ( index, val_1, val_2 [, val_n ] ) 형태로 작성되며 간단한 예시로 아래의 쿼리문을 보시게되면 SELECT CHOOSE(1,'가','나','다','라') 아래의 쿼리를 실행하게 되면 인덱스 1에 위치한 문자열 '가' 가 출력이 됩니다. 요일 추출 방법 --CASE문 사용 SELECT CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN '일' WHEN 2 THEN '월' WHEN 3 THEN '화' WHEN 4 THEN '수' WHEN 5 THEN '목' WHEN 6 THEN '금' WHEN 7 THEN '토' END --CHOOSE문 사용 SELECT CHOOSE(DATEPART(WEE..
2020.04.24 -
MSSQL에서 단순 조건을 비교하는 경우 CASE 문을 쓰지 않고 IIF라는 함수를 통해 조건문을 작성할 수 있습니다. IIF ( boolean_expression, true_value, false_value ) 형태로 작성하며 간단한 예로 아래의 쿼리문을 보면 --CASE문을 사용 SELECT CASE WHEN @X = 'TRUE' THEN 1 ELSE 0 END --IIF함수를 사용 SELECT IIF(@X = 'TRUE', 1, 0) CASE 문을 사용할 때보다 훨씬 명확하고 가독성있게 표시가 가능합니다. 만약 여러개의 조건이 있다면 CASE 문이 필수 불가결하게 사용되지만 단순 비교문이라면 IIF를 사용하는 것이 훨씬 편리합니다. 또한 실행 계획도 CASE문을 쓸때와 동일하게 생성된다고 하니 성능..
[MSSQL] IIF 함수를 사용한 간단한 조건문 작성하기MSSQL에서 단순 조건을 비교하는 경우 CASE 문을 쓰지 않고 IIF라는 함수를 통해 조건문을 작성할 수 있습니다. IIF ( boolean_expression, true_value, false_value ) 형태로 작성하며 간단한 예로 아래의 쿼리문을 보면 --CASE문을 사용 SELECT CASE WHEN @X = 'TRUE' THEN 1 ELSE 0 END --IIF함수를 사용 SELECT IIF(@X = 'TRUE', 1, 0) CASE 문을 사용할 때보다 훨씬 명확하고 가독성있게 표시가 가능합니다. 만약 여러개의 조건이 있다면 CASE 문이 필수 불가결하게 사용되지만 단순 비교문이라면 IIF를 사용하는 것이 훨씬 편리합니다. 또한 실행 계획도 CASE문을 쓸때와 동일하게 생성된다고 하니 성능..
2020.04.24 -
SQL 작성시 4자리수 순번을 생성해야하는 경우가 있습니다 (0001, 0002...) 이럴때 특정 문자로 자리수를 채우는 간단한 방법이 있습니다. 바로 MSSQL에서 제공하는 함수인 Replicate 입니다. SELECT REPLICATE('채울문자', 고정길이 - LEN('데이터')) + '데이터' 예를 들어 0001, 0002, 0003처럼 표시를 하고 싶은 경우에는 아래의 쿼리문 처럼 사용하시면 됩니다. DECLARE @LENGTH INT SELECT @LENGTH = 4 SELECT REPLICATE('0', @LENGTH - LEN(CAST(1 AS NVARCHAR))) + '1'
[MSSQL] 특정 문자로 자릿수 채우는 간단한 방법SQL 작성시 4자리수 순번을 생성해야하는 경우가 있습니다 (0001, 0002...) 이럴때 특정 문자로 자리수를 채우는 간단한 방법이 있습니다. 바로 MSSQL에서 제공하는 함수인 Replicate 입니다. SELECT REPLICATE('채울문자', 고정길이 - LEN('데이터')) + '데이터' 예를 들어 0001, 0002, 0003처럼 표시를 하고 싶은 경우에는 아래의 쿼리문 처럼 사용하시면 됩니다. DECLARE @LENGTH INT SELECT @LENGTH = 4 SELECT REPLICATE('0', @LENGTH - LEN(CAST(1 AS NVARCHAR))) + '1'
2020.04.24 -
SQL SERVER 운영시 프로시저가 동작을 안하고 뻗어버리는 경우가 종종 발생합니다. 일반적으로 Lock이 발생하였을 때 경우는 1. 대용량 데이터 처리로 인해 쿼리문의 실행시간이 길어져 오랜시간 Lock을 잡고 있는 경우 2. 트랜잭션을 걸었지만 Commit 또는 Rollback되지 않은 경우 3. 리소스 경합으로 인한 교착상태(DeadLock) 발생 Lock을 해제하는 방법은 다음의 글 참고 부탁드립니다. https://omty.tistory.com/29 [MSSQL] Lock 걸린 쿼리 확인하고 해제 및 방지하기 쿼리를 실행하다 보면 간단한 쿼리인데도 실행되지 않는 경우가 종종 발생하곤 합니다. 이럴때 가장먼저 확인해야 하는것이 해당 쿼리에 대해 Lock의 발생 여부입니다. Lock이 걸린 spi..
[MSSQL] 데드락(DeadLock) 발생 로그 확인 및 원인 찾기SQL SERVER 운영시 프로시저가 동작을 안하고 뻗어버리는 경우가 종종 발생합니다. 일반적으로 Lock이 발생하였을 때 경우는 1. 대용량 데이터 처리로 인해 쿼리문의 실행시간이 길어져 오랜시간 Lock을 잡고 있는 경우 2. 트랜잭션을 걸었지만 Commit 또는 Rollback되지 않은 경우 3. 리소스 경합으로 인한 교착상태(DeadLock) 발생 Lock을 해제하는 방법은 다음의 글 참고 부탁드립니다. https://omty.tistory.com/29 [MSSQL] Lock 걸린 쿼리 확인하고 해제 및 방지하기 쿼리를 실행하다 보면 간단한 쿼리인데도 실행되지 않는 경우가 종종 발생하곤 합니다. 이럴때 가장먼저 확인해야 하는것이 해당 쿼리에 대해 Lock의 발생 여부입니다. Lock이 걸린 spi..
2020.04.23 -
프로시저 및 DB 작업시 현재는 잘 작동하는 프로그램이 칼럼의 추가, 데이터 타입의 변화, 그 밖의 많은 상황에서 오류가 발생하는 경우가 존재합니다. 이때 트랜잭션이 걸려있지 않은 경우 일부 데이터만 처리가 되는 매우 심각한 오류가 발생할 수 있습니다. MSSQL SERVER는 이러한 상황에 대해 SQL 실행시 오류를 컨트롤 할 수 있도록 Try Catch 문을 제공하고 있습니다. 그리고 Try Catch와 Transaction처리를 통해 데이터 정합성을 보장할 수 있습니다. 사용법은 간단합니다. BEGIN TRY //실행할 SQL 문입력 END TRY BEGIN CATCH //예외 상황 발생시 처리 END CATCH 간단하게 테스트를 한번 해보겠습니다. 우선 아래의 테이블을 생성해 줍니다. CREATE..
[MSSQL] Try Catch 문을 사용한 트랜잭션(taransaction) 컨트롤 방법프로시저 및 DB 작업시 현재는 잘 작동하는 프로그램이 칼럼의 추가, 데이터 타입의 변화, 그 밖의 많은 상황에서 오류가 발생하는 경우가 존재합니다. 이때 트랜잭션이 걸려있지 않은 경우 일부 데이터만 처리가 되는 매우 심각한 오류가 발생할 수 있습니다. MSSQL SERVER는 이러한 상황에 대해 SQL 실행시 오류를 컨트롤 할 수 있도록 Try Catch 문을 제공하고 있습니다. 그리고 Try Catch와 Transaction처리를 통해 데이터 정합성을 보장할 수 있습니다. 사용법은 간단합니다. BEGIN TRY //실행할 SQL 문입력 END TRY BEGIN CATCH //예외 상황 발생시 처리 END CATCH 간단하게 테스트를 한번 해보겠습니다. 우선 아래의 테이블을 생성해 줍니다. CREATE..
2020.04.07 -
프로젝트 진행시 DB상의 쿼리를 자동으로 처리해주어야 하는 경우(배치 작업)가 발생합니다. 이때 MSSQL SERVER에는 작업 스케줄러를 등록하고 실행하는 에이전트가 존재합니다. 먼저 개체탐색기 > SQL Server 에이전트 > 작업 에서 오른쪽 마우스를 클릭하신 후 새 작업을 눌러줍니다. 해당 스케줄러 작업명을 기입합니다. 단계> 새로만들기를 클릭합니다. 해당 작업 단계 이름 / 데이터베이스/ 명령을 실행할 쿼리문을 입력한뒤 확인을 누릅니다. 아래 그림과 같이 작업 단계 목록이 생성이 되었다면 정상적으로 처리가 된 것 입니다. 만약 아래와 같은 창이 발생시 단계가 하나만 존재할 경우는 예/ 추가 단계를 입력할 경우 아니오 일정 > 새로만들기를 클릭합니다. 이제 작업 일정을 설정하시면 됩니다. 빈도 ..
[MSSQL] 스케줄러(scheduler)를 통해 쿼리문 자동 반복하기프로젝트 진행시 DB상의 쿼리를 자동으로 처리해주어야 하는 경우(배치 작업)가 발생합니다. 이때 MSSQL SERVER에는 작업 스케줄러를 등록하고 실행하는 에이전트가 존재합니다. 먼저 개체탐색기 > SQL Server 에이전트 > 작업 에서 오른쪽 마우스를 클릭하신 후 새 작업을 눌러줍니다. 해당 스케줄러 작업명을 기입합니다. 단계> 새로만들기를 클릭합니다. 해당 작업 단계 이름 / 데이터베이스/ 명령을 실행할 쿼리문을 입력한뒤 확인을 누릅니다. 아래 그림과 같이 작업 단계 목록이 생성이 되었다면 정상적으로 처리가 된 것 입니다. 만약 아래와 같은 창이 발생시 단계가 하나만 존재할 경우는 예/ 추가 단계를 입력할 경우 아니오 일정 > 새로만들기를 클릭합니다. 이제 작업 일정을 설정하시면 됩니다. 빈도 ..
2020.04.07 -
쿼리를 실행하다 보면 간단한 쿼리인데도 실행되지 않는 경우가 종종 발생하곤 합니다. 이럴때 가장먼저 확인해야 하는것이 해당 쿼리에 대해 Lock의 발생 여부입니다. Lock이 걸린 spid를 확인 EXEC SP_LOCK Mode가 X로 되어있는 spid를 확인합니다. Lock이 걸린 쿼리내용 확인 DBCC inputbeffer(spid) 위에서 식별된 spid를 넣고 Lock이 걸린 쿼리 내용을 확인합니다. Lock이 발생한 host 찾기 EXEC SP_WHO spid spid를 넣고 어떤 host에서 Lock이 발생한지 확인합니다. Lock 제거 EXEC KILL spid Lock이 발생한 경우 일부 테이블에 대한 액세스가 불가하기 때문에 꼭 제거하도록 합시다. 트랜잭션이 필요없는 대량의 데이터를 조회..
[MSSQL] Lock 걸린 쿼리 확인하고 해제 및 방지하기쿼리를 실행하다 보면 간단한 쿼리인데도 실행되지 않는 경우가 종종 발생하곤 합니다. 이럴때 가장먼저 확인해야 하는것이 해당 쿼리에 대해 Lock의 발생 여부입니다. Lock이 걸린 spid를 확인 EXEC SP_LOCK Mode가 X로 되어있는 spid를 확인합니다. Lock이 걸린 쿼리내용 확인 DBCC inputbeffer(spid) 위에서 식별된 spid를 넣고 Lock이 걸린 쿼리 내용을 확인합니다. Lock이 발생한 host 찾기 EXEC SP_WHO spid spid를 넣고 어떤 host에서 Lock이 발생한지 확인합니다. Lock 제거 EXEC KILL spid Lock이 발생한 경우 일부 테이블에 대한 액세스가 불가하기 때문에 꼭 제거하도록 합시다. 트랜잭션이 필요없는 대량의 데이터를 조회..
2020.04.07