MSSQL
Microsoft SQL Server는 .NET Framework와 연동하여 데이터베이스와의 상호작용이 용이하다. 또한 ACID(원자성, 일관성, 격리성, 지속성) 트랜잭션 처리와 복구 기능이 강력하여, 안정적인 데이터베이스 운영을 보장한다.
MSSQL은 다양한 개발 도구와 언어를 지원한다. Transact-SQL(T-SQL)을 사용하여 DB에 대한 작업을 수행할 수 있으며, Visual Studio와 연동하여 개발을 지원한다.
또한, .NET Framwork, C#등과의 완전한 통합을 제공하여 개발자가 유연하고 효율적으로 애플리케이션을 개발할 수 있다.
USE
USE DATABASE_NAME;변수 선언
-- 지역변수 선언
DECLARE @Variable_Name VARCHAR(6);
-- 변수에 값 할당
SET @Variable_Name '0000';UPDATE
UPDATE 테이블명 AS A
SET A.컬럼명 = 값DELETE
DELETE 테이블명
WHERE TOP
-- MySQL의 LIMIT과 같은 기능을 한다. 상위 100개의 데이터만 출력됨
SELECT TOP 100
FROM DATABASE.(dto 생략).TABLEWHEN, THEN
조건에 따라서 값을 지정해 주는 CASE문
USE
GO
SELECT Category =
CASE ProductLine
WHEN 'R' THEN 'ROAD'
WHEN 'M' THEN 'Mountain'
ELSE 'Not For Sale'
END
FROM Production.Product;
GOswitch(ProductLine) case: 와 같다
Category라는 속성이 결과로 출려된다.
R, M이 아닌 경우에는 "Not For Sale"이 들어간다.
GO
MSSQL에서 GO 명령은 배치단위를 구분하는 명령으로 사용된다.
쿼리들을 블록으로 나누는 느낌이다.
GO는 T-SQL 명령어가 아닌, SQL Server의 Client 툴(SSMS)에서 해석하여, 쿼리들을 실행 가능한 배치 단위로 분리하는 역할을 한다.
DECLARE @num int
GO
SET @num = 10위 코드는 에러가 발생한다.
그 이유는 GO 위에서 지역변수 num을 선언 했는데 GO 아래에서 num을 수정하려고 하기 때문이다.
STORED PROCEDURE
줄여서 SP는, 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.
프로시저 정의
CREATE PROCEDURE select_user_by_name
@userName VARCHAR(10) = '김', -- 입력 매개변수
@age INT, -- 입력 매개변수
@outputResult NVARCHAR(50) OUTPUT -- 출력 매개변수
AS
SELECT * FROM userTbl WHERE name = @userName AND age = @age;
SET @outputResult = '조회 완료: name=' + @userName + ', age=' + CAST(@age AS NVARCHAR);
GO프로시저 실행
-- OUTPUT을 받을 변수 선언
DECLARE @resultMessage NVARCHAR(50);
EXEC select_user_by_name
@userName = 'diense_kk',
@age = 26,
-- OUTPUT 변수 전달
@outputResult = @resultMessage OUTPUT;
-- 결과 출력
PRINT @resultMessage;
GO결과
-- SELECT 결과
id name age
1 diense_kk 26
-- PRINT 결과
조회 완료: name=diense_kk, age=26
AS
프로시저를 정의하는 부분에서 AS는 SQL Server에서 SP의 본문을 시작하는 키워드이다.
즉, AS 이후부터는 해당 저장 프로시저가 실행될 때 수행할 작업을 정의하는 부분이 나온다.
OUTPUT
프로시저를 활용해서 N개의 변수 결과를 OUTPUT 받는 기능이다.
SELECT 절을 통한 DataTable 형태의 수신도 가능하지만, OUTPUT을 사용하면 단순 카운트 혹은 연산 문자열을 수신 받을때 유용하다.
EXEC
SP를 실행하기 위한 명령어로, "EXEC SP명" 으로 실행한다.
만약 SP에서 파라미터를 받거나 OUPUT이 있는 경우에는 다음과 같이 실행하면 된다.
DECLARE resultMessage VARCHAR(50);
EXEC SP명
@SP의 파라미터명 = 전달값,
@SP의 파라미터명 = @결과를 받을 변수명 OUTPUT;
GOSET ANSI_NULLS ON/OFF
SET ANSI_NULLS는 SQL Server에서 NULL값을 비교하는 방식을 제어하는 설정이다.
ON 또는 OFF로 설정할 수 있으며, ON 상태에서는 ANSI SQL 표준에 따라 NULL 값을 처리한다.
SET ANSI_NULLS 옵션이 ON으로 설정하지 않는다면 계산 열이나 인덱싱된 뷰가 있는 테이블의 CREATE, UPDATE, INSERT 및 DELETE 문이 실패한다.
SET ANSI_NULLS ON인 경우, NULL과의 =, != 는 항상 FALSE 또는 UNKNOWN이 된다. IS NULL 또는 IS NOT NULL을 사용해야 올바르게 비교 가능하다.
WHERE column_name = NULL을 사용하는 SELECT 문은 column_name에 Null 값이 있을 때도 0행을 반환한다.
WHERE coulumn_name <> NULL을 사용하는 SELECT 문은 column_name에 Null이 아닌 값이 있을 때도 0행을 반환한다.
- <>는 !=(Not Equal) 와 같음
SET ANSI_NULLS 옵션이 OFF인 경우,ANSI SQL 표준을 따르지 않는다. NULL과의 동등 비교가 가능하며, NULL은 다른 값처럼 비교된다.
WHERE column_name = NULL을 사용하는 경우 column_name이 NULL인 테이블을 반환한다.
WHERE column_name <> NULL을 사용하는 경우에는 column_name이 NULL이 아닌 행을 반환한다.
SQL Server의 기본값은 SET ANSI_NULLS ON이다. 이 설정을 끄는 것은 권장되지 않는다.
저장 프로시저나 함수 생성 시, SET ANSI_NULLS ON이어야만 작성이 가능하다.
SET QUOTED_IDENTIFIER ON/OFF
SET QUOTED_IDENTIFIER는 SQL Server에서 따옴표(")의 동작 방식을 제어하는 설정이다. 이 설정은 SQL 표준 준수 여부를 결정하며, 식별자와 문자열 리터럴을 구분하는 데 영향을 준다.
SET QUOTED_IDENTIFIER ON이 기본값이다. ANSI SQL 표준을 따르며, 이중 따옴표(")는 식별자(테이블 명, 컬럼명 등)를 감싸는 데 사용된다. 문자열 리터럴에는 항상 작은 따옴표(')를 사용해야 된다.
SELECT ColumnName FROM tableName WHERE ColumnName = 'value';SET QUOTED_IDENTIFIER OFF인 경우, 비표준 방식으로 작동한다. 이중 따옴표(")는 문자열 리터럴로 사용되고, 식별자는 기본 규칙에 따라 따옴표 없이 사용해야 된다.
SELECT ColumnName FROM tableName WHERE ColumnName = "Value";SET NOCOUNT ON/OFF
SET NOCOUNT는 Transact-SQL문 또는 SP의 영향을 받은 행의 수를 나타내는 메시지가 결과 집합의 일부로 반환되지 않도록 하는 것이다.
SET NOCOUNT 옵션을 ON으로 설정하면 처리 개수가 반환되지 않는다.
SET NOCOUNT 옵션을 OFF로 설정하면 처리 개수가 반환된다.
SET NOCOUNT OFF
(1개 행 적용됨)
(1개 행 적용됨)
(1개 행 적용됨)
완료시간 : ~
SET NOCOUNT ON
완료시간 : ~NOCOUNT ON을 사용하면 프로시저 성능 향상
SET TRANSACTION ISOLATION LEVEL
트랜잭션 격리 수준(TRANSACTION ISOLATION LEVEL)이란?
트랜잭션 격리 수준은 동시성 제어를 위해 데이터의 일관성과 잠금을 어떻게 처리할지를 정의한다.
격리 수준이 높아질수록 데이터 정확성은 향상되지만, 동시성이 낮아지고 성능이 저하될 수 있다.
트랜잭션 격리 수준을 설정한 이후에 트랜잭션을 실행시키자.
트랜잭션 격리 수준의 필요성
트랜잭션이 DB를 다루는 동안 다른 트랜잭션이 관여하지 못하게 막는데, 무조건적인 Locking으로 동시에 수행되는 많은 트랜잭션들을 순서대로 처리하는 방식으로 구현되면 DB의 성능이 떨어지게 된다.
반대로 응답성을 높이기 위해 Locking 범위를 줄인다면 잘못된 값이 처리 될 여지가 있다.
그래서 최대한 효율적인 Locking 방법이 필요하다.
트랜잭션 격리 수준의 종류
1) READ UNCOMMITTED
트랜잭션에서 잠금을 걸지않고 데이터를 읽는다. 커밋되지 않은 다른 트랜잭션의 데이터를 읽을 수 있다. 가장 낮은 격리 수준으로, Dirty Read가 가능하다. 또한, 데이터의 불일치 가능성이 높다.
2) READ COMMITTED (기본값)
다른 사용자의 작업이 완료된 데이터만 조회하거나 변경할 수 있다.
다른 사용자가 변경 중이라면 SELECT를 포함한 어떤 작업도 할 수 없고, 대기 하다가 다른 사용자가 비로소 작업이 완료 되면 변경된 결과로 데이터를 조회 하거나 변경 작업이 진행된다.
또한, 데이터를 읽는 동안 다른 트랜잭션이 해당 데이터에 대한 변경 작업을 수행하지 못하게 공유 잠금(Shared Lock)을 설정한다.
Non-Repeatable Read 가능 (같은 데이터를 두 번 읽었을 때 값이 다를 수 있다.)
3) REPEATABLE READ
트랜잭션이 데이터를 읽는 동안 다른 트랜잭션이 해당 데이터를 수정하지 못하게 읽기 잠금을 건다.
Dirty Read 방지, Non-Reapeatable Read 방지, Phantom Read 가능 (새로운 행이 추가되는 경우는 허용된다.)
4) SNAPSHOT
트랜잭션이 시작될 때 버전 기반 데이터 복사본을 읽는다. 다른 트랜잭션의 변경 사항과 관계없이 트랜잭션 시작 시점의 데이터를 유지한다.
Dirty Read 방지, Non-Repeatable Read 방지, Phantom Read 방지, 별도의 버전 관리 비용이 발생한다.
읽기 작업이 많고, 동시에 업데이트 작업도 자주 발생하는 시스템에서 효율적이다.
SNAPSHOT 격리 수준은 DB에서 별도로 활성화해야 사용할 수 있다.
5) SERIALIZABLE
가장 높은 격리 수준으로, 트랜잭션 동안 다른 트랜잭션이 데이터를 읽거나 수정하거나 추가하지 못하게 완전한 테이블 잠금을 건다.
Dirty Read 방지, Non-Repeatable Read 방지, Phantom Read 방지, 동시성은 낮아지고 성능에 부담이 크다.
가장 높은 데이터 정확성이 필요할 때 사용한다.
| 격리 수준 | Dirty Read | Non-Repeatable Read | Phantom Read | 잠금 강도 |
|---|---|---|---|---|
| READ UNCOMMITTED | O | O | O | 없음 |
| READ COMMITTED | X | O | O | 공유 잠금 |
| REPEATABLE READ | X | X | O | 읽기 잠금 |
| SERIALIZABLE | X | X | X | 범위 잠금 |
| SNAPSHOT | X | X | X | 버전 관리 기반 |
Dirty Read -> 커밋되지 않은 데이터 읽기 (메모리에는 변경이 되었지만 아직 디스크에는 변경되지 않은 데이터를 읽는 것)
아직 커밋되지 않은 데이터를 읽어오기 때문에, 더티 페이지를 읽은 후에 더티 페이지의 데이터가 Rollback 된다면 이미 읽어온 데이터는 잘못된 데이터가 된다.
Non-Repeatable Read -> 반복되지 않은 읽기 (트랜잭션 내에서 한 번 읽은 데이터가 트랜잭션이 끝나기 전에 다른 트랜잭션에 의해 변경되었다면, 다시 읽었을 때 새로운 값이 읽히는 것)
Phantom Read -> Repeatable Read 격리 수준에서는 트랜잭션이 진행 중인(엄밀히 말하면 공유 잠금이 설정된) 데이터에 대해서 변경 작업을 할 수 없지만, 새로운 데이터의 입력 작업(Insert)은 가능한데 이를 Phantom Read라고 한다.
BEGIN ... END
SQL문의 그룹을 실행 할 수 있도록 일련의 그룹화를 한 것이다.
BEGIN
-- 쿼리문 혹은 쿼리 블럭
END
-- 트랜잭션 시작
-- 트랜잭션은 COMMIT 또는 ROLLBACK으로 종료된다.
BEGIN TRAN
-- 쿼리문 혹은 쿼리 블럭
COMMIT TRAN;
-- 예외 감지 블록
-- TRY 블록 내에서 오류가 발생하면 CATCH 블록으로 제어를 넘기고 그렇지 않다면 CATCH 블록은 실행되지 않는다.
BEGIN TRY
-- 쿼리문 혹은 쿼리 블럭
END
-- TRY문에서 제어가 넘어오면 실행되는 예외 처리 블록이다.
BEGIN CATCH
-- 예외 발생시 트랜잭션을 롤백시킨다.
ROLLBACK TRAN
END CATCHBEGIN ... END 구문은 중첩 될 수 있다.
@@ERROR, @@ROWCOUNT
@@로 시작하는 것은 시스템 변수이다.
@@ERROR는 현재 시스템의 최근 실행된 SQL문의 에러코드를 반환한다.
사용자 프로시져나 함수 등에서 함수의 종료나, SQL 구문의 유효성 검사 등에 이용한다.
IF @@ERROR <> 0 BEGIN
-- 이전 구문에서 에러가 발생
PRINT 'ERROR'
END
ELSE BEGIN
-- 정상 완료
PRINT 'OK'
END@@ROWCOUNT는 최근에 실행된 SQL문에 의해 영향받은 행의 수를 반환한다.
SELECT, INSERT, UPDATE 구문이 실행된 행 수를 반환하게 되며, SET, USE 등은 1을 반환하게 되어 이전에 실행한 구문 바로 아래에서 @@ROWCOUNT를 확인해야 된다.
SELECT @p_row_count = @@ROWCOUNT IF NOT EXISTS
IF NOT EXISTS는 우선 데이터를 조회 한 이후에 데이터의 존재 여부에 따라 어떤 쿼리를 실행할지를 결정하는 것이다.
IF NOT EXISTS(SELECT 데이터 FROM TABLE WHERE 데이터='데이터')
BEGIN
INSERT INTO TABLE (데이터,입력) VALUES('데이터','1');
END
ELSE
BEGIN
UPDATE TABLE
SET 데이터 = '변경값입력'
WHERE 데이터='데이터'
END위의 쿼리를 예를 들면, SELECT 데이터 FROM TABLE WHERE 데이터 = '데이터' 쿼리를 실행한 이후에 쿼리의 결과 ROW가 0인 경우 INSERT INTO TABLE(데이터, 입력) VALUES('데이터','1'); 을 실행하고, 데이터가 존재하는 경우네는 UDPATE TABLE SET 데이터 = '변경값입력' WHERE 데이터 = '데이터' 를 실행하는 것이다.
UNION
UNION은 2개 이상의 SELECT 쿼리 결과를 하나로 연합시켜주는 집합 연산자이다.
UNION과 UNION ALL 연산자 사용 시 필요한 기본 규칙은 아래와 같다.
1) 열의 개수와 순서가 모든 쿼리에서 동일해야 된다.
2) 데이터 형식이 호환되어야 된다.
UNION과 UNION ALL의 차이
두 쿼리문을 하나로 합쳐준다는 것에 공통점이 있지만, UNION은 두 쿼리의 결과의 중복값을 제거해서 보여주고, UNION ALL은 중복값도 전부 다 보여준다는 차이점이 있다.
UNION은 중복값 제거를 위해 연산을 한 번 더 해야되지만, UNION ALL은 그런 연산이 없기 때문에 속도는 UNION ALL이 더 빠르다.
-- UNION
SELECT [컬럼1], [컬럼2] ... FROM [테이블명]
UNION
SELECT [컬럼1], [컬럼2] ... FROM [테이블명]-- UNION ALL
SELECT [컬럼1], [컬럼2] ... FROM [테이블명]
UNION ALL
SELECT [컬럼1], [컬럼2] ... FROM [테이블명]CURSOR
CURSOR란 행 단위 작업을 효율적으로 하기 위한 방식으로 테이블에서 여러 행을 조회한 후, 쿼리의 결과를 한 행씩 처리하는 방식이다.
SELECT한 결과(행 집합)를 한 행씩 반복 작업해줘야 할 경우 유용하게 사용 가능한 방식이다.
CURSOR의 특징
1) 커서는 내장 SQL 문의 수행 결과로 반환될 수 있는 복수의 튜플들을 액세스 할 수 있도록 해주는 개념이다.
2) 커서는 질의 수행 결과로 반환되는 첫 번째 튜플에 대한 포인터로 생각 할 수 있다.
3) 커서를 사용하여 질의 결과로 반환될 수 있는 튜플들을 한 번에 하나씩 차례대로 처리할 수 있다.
CURSOR 관련 명령어
DECLARE - 커서를 정의하는 등 커서에 관련된 선언을 하는 명령이다.
OPEN - 커서가 질의 결과의 첫 번째 튜플을 포인트 하도록 설정하는 명령이다.
FETCH - 질의 결과의 튜플들 중 현재의 다음 튜플로 커서를 이동시키는 명령이다.
CLOSE - 질의 수행 결과에 대한 처리 종료 시 커서를 닫기 위해 사용하는 명령이다.
CURSOR를 사용함에 따라 SQL의 성능을 떨어트리는 요인이 된다. 적은 데이터를 처리할 경우 그렇게 큰 차이가 나지 않겠지만 많은 데이터를 처리할 경우에는 커서 사용을 피하는 것이 좋다.
-- 변수 선언
DECLARE
@INDEX INT,
@NAME VARCHAR(100),
@AGE INT,
@FETCH_STATUS INT
SET @INDEX = 0; -- INDEX 초기화
-- 커서를 선언하고, MY_FRIEND 테이블에서 NAME과 AGE 데이터를 조회하여 사용한다.
DECLARE CUR CURSOR FOR
SELECT NAME, AGE FROM MY_FRIEND
-- 커서 오픈
OPEN CUR
-- 첫 번째 ROW 가져오기
FETCH NEXT FROM CUR INTO @NAME, @AGE
SET @FETCH_STATUS = @@FETCH_STATUS -- FETCH 상태 저장
-- 커서를 이용해 한 ROW씩 읽음
WHILE @FETCH_STATUS = 0
BEGIN
-- 인덱스 증가
SET @INDEX = @INDEX + 1;
-- SELECT 한 데이터의 행집합을 가지고 수행할 작업
UPDATE MY_FRIEND
SET AGE = @AGE + 1 -- 나이 + 1
WHERE NAME = @NAME;
-- 다음 ROW로 이동
FETCH NEXT FROM CUR INTO @NAME, @AGE
SET @FETCH_STATUS = @@FETCH_STATUS -- FETCH 상태 업데이트
END
-- 커서 닫고 초기화
CLOSE CUR
DEALLOCATE CUR