| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | ||||||
| 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| 9 | 10 | 11 | 12 | 13 | 14 | 15 |
| 16 | 17 | 18 | 19 | 20 | 21 | 22 |
| 23 | 24 | 25 | 26 | 27 | 28 |
- VUE
- jwt
- java
- AOP
- docker
- 테스트 코드
- stream
- DDL
- 조합
- MSA
- cache
- select_type
- 재정의
- equals
- 생성자 주입
- 바이너리 카운팅
- 열 속성
- Test
- SQL
- DI
- 인덱스
- StringBuilder
- lambda
- jpa
- KEVISS
- hashcode
- static
- 필드 주입
- Spring
- redis
- Today
- Total
백엔드 개발자 블로그
MS-SQL 본문
1. 테이블 만들기
1-1. UI로 만들기
1) 테이블을 만들고자 하는 데이터베이스를 선택 후 우클릭 > 새로만들기 > 테이블 을 클릭한다.
2) 아래와 같은 데이터를 넣어준다.
3) 그 후 상단의 'X' 버튼이나 저장(Ctrl+S) 단축키를 누르면 테이블 이름을 지정할 수 있는 알람창이 뜨는데
여기서 테이블 이름을 작성한다.
4) 테이블 우클릭 후 새로고침 버튼을 눌러주면 아래에 새로 생성된 테이블을 확인할 수 있다.
1-2. CREATE문으로 만들기
|
1
2
3
4
5
6
7
8
|
CREATE TABLE dbo.직원(
사원번호 char(5) NOT NULL,
이름 ncahr(10) NOT NULL,
성별 char(1) NOT NULL,,
입사일 date NOT NULL,
전자우편 varchar(60) NOT NULL,,
부서코드 char(3) NOT NULL
)
|
1-3. ERD로 테이블 설계하기
데이터베이스 설계시 ERD작성이 필요한데 ER-Win 이나 ER-Studio와 같은 프로그램을 사용합니다. MSSQL의 경우 SQL Management에서 데이터베이스 다이어그램을 제공하고 있습니다. ERD작성 전문툴보다는 많은 기능을 제공하지는 않지만 엔티티간의 관계나 테이블구조등을 살펴보는데에는 개인적으로 편리하게 사용하고 있습니다. MSSQL에서 제공하는 다이어 그램에 대해서 알아보도록 하겠습니다.
먼저 데이터베이스-> Database Diagrames -> New Database Diagram을 선택합니다.
아래와 같이 보안 주체 "보안 주체 "dbo"이(가) 없거나 이 유형의 보안 주체를 가장할 수 없거나 사용 권한이 없기 때문에 데이터베이스 보안 주체로 실행할 수 없습니다. (microsoft sql server, error: 15517) 라는 경고메세지가 나오면 권한이 부여되지 않아서 실행할수 없는 문제입니다.
권한을 부여해보겠습니다. 스크립트창에서 아래와 같이 입력합니다.
|
1
|
ALTER AUTHORIZATION on DATABASE::AdventureWorksLT2019 TO [sa]
|
|
다시 데이터베이스 다이어그램을 다시 실행합니다(데이터베이스-> Database Diagrames -> New Database Diagram
전체테이블을 선택후 Add버튼을 클릭합니다.
이제 테이블 스키마 정보 및 테이블간의 관계를 확인할수 있습니다. 아래와같이 테이블리스트, 컬럼명, PK/FK 및 테이블 관계에 대하여 기본적으로 표시해줍니다.
표시되는 항목을 보다 다양하게 하고 싶을경우에는 Modify Custom(사용자지정)부분에서 표시될 항목을 추가하면됩니다. 테이블설명에 대한 부분을 추가해보도록 하겠습니다.
사용자지정 항목에 설명을 추가한 후 테이블뷰(Table View)부분에 항목을 Custom으로 선택합니다. 이제 해당테이블항목에 설명이 추가된 부분을 확인할수 있습니다.
MSSQL에서 기본적으로 제공하는 데이터베이스 다이어그램을 이용하면 프로그램개발시 테이블정보를 확인하고 DB작업하는데 용이합니다.
2. Stored Procedure 개발
2-1 Stored Procedure 개념
저장 프로시저는 쿼리문들의 집합으로, 어떤 동작을 여러쿼리를 거쳐서 일괄적으로 처리할 때 사용한다.
먼저, SQL Server의 성능을 향상 시킬 수 있다.
저장 프로시저를 처음에 실행하면 최적화, 컴파일 단계를 거쳐 그 결과가 캐시(메모리)에 저장되게 되는데, 이 후에 해당 SP를 실행하게 되면 캐시(메모리)에 있는 것을 가져와서 사용하므로 실행속도가 빨라지게 된다.
그렇기 때문에 일반 쿼리를 반복해서 실행하는 것보다 SP 를 사용하는게 성능적인 측면에서 좋다.
두번째, 유지보수 및 재활용 측면에서 좋다.
C#, Java등으로 만들어진 응용프로그램에서 직접 SQL문을 호출하지 않고 저장 프로시저의 이름을 호출하도록 설정하여 사용하는 경우가 많은데, 이때 개발자는 수정요건이 발생할때 코드 내 SQL문을 건드리는게 아니라 SP 파일만 수정하면 되기 때문에 유지보수 측면에서 유리해진다.
또한 한번 저장 프로시저를 생성해 놓으면, 언제든 실행이 가능하기 때문에 재활용 측면에서 매우 좋다.
셋째, 보안을 강화할 수 있다.
사용자별로 테이블에 권한을 주는게 아닌 저장 프로시저에만 접근 권한을 주는 방식으로 보안을 강화할 수 있다.
실제 테이블에 접근하여 다양한 조작을 하는 것은 위험하기 때문에 실무에서는 실제로 개발자에게는 sp권한만 주는 방식을 많이 사용한다.
마지막으로, 네트워크의 부하를 줄일 수 있다.
클라이언트에서 서버로 쿼리의 모든 텍스트가 전송될 경우 네트워크에는 큰 부하가 발생하게 된다. 하지만 저장 프로시저를 이용한다면 저장프로시저의 이름, 매개변수 등 몇글자만 전송하면 되기 때문에 부하를 크게 줄일 수 있다.
일반적인 SQL 문과 저장 프로시저 동작 방식 비교
저장 프로시저를 사용하면보통 일반적인 T-SQL 사용보다 시스템의 성능이 향상 된다고 한다.
그 이유는 뭇엇일까? 내부에서 처리되는 방식을 비교해서 한번 확인해보자.
일반 T-SQL 동작 방식
위의 그림은 일반적인 T-SQL 문을 처음으로 실행하면 위의 프로세스로 동작한다.
만약에 아래 쿼리를 실행한다고 가정하자.
SELECT name FROM userTbl;그러면 먼저 구문 분석단계에서 구문 자체에 오류가 없는지 분석을 할 것이다. 만약 오타가 잇으면 여기서 오류가 발생되어 에러메시지를 띄울 것이다.
다음은 개체 이름 확인 단계에서 userTbl 이라는 테이블이 현재 데이터베이스에 있는지 확인을 한다. 만약에 userTbl이 있으면 그안에 name이라는 열이 있는지를 확인할 것이다.
그다음 사용권한 확인 단계에서 userTbl을 현재 접근중인 사용자가 권한이 있는지를 확인한다.
다음으로 최적화 단계에서 해당 쿼리문이 가장 좋은 성능을 낼 수 있는 경로를 결정한다. 인덱스 사용여부에 따라 경로가 결정된다고 보면된다. 위의 쿼리의 경우 전체 데이터를 가져오기 때문에 아마도 테이블 스캔이나 클러스터 인덱스 스캔이 될 것이다.
다음은 최적화된 결과를 바탕으로 컴파일 및 실행 계획 등록 단계에서 해당 실행계획 결과를 메모리(캐시)에 등록한다.
그리고 컴파일된 결과를 실행한다.
단 한 줄의 쿼리지만 이렇게 많은 절차를 거친다...! 한번쯤은 제대로 알아두면 좋으니 잘 보고 공부해두자.
만약에 동일한 SQL문을 실행하면 아래와 같이 단순하게 동작하게 된다.
여러 과정이 생략되다 보니 시간이 단축되게 될 것이다.
만약에 메모리(캐시)에 동일한 쿼리가 없다면 위의 전체 과정을 다시 반복할 것이다.
여기서 주의해야할 점은 쿼리 전체가 한글자도 틀리자 않고 같아야 한다는 것이다.
다음은 저장 프로시저의 동작 방식이다.
먼저 저장 프로시저를 정의 했을때 작동방식을 알아보자.
일단 먼저 해당 저장 프로시저에서 구문 오류가 있는 지를 파악하는 과정을 거친다.
다음은 지연된 이름 확인(deferred name resolution) 과정을 거치게 되는데 이는 저장 프로시저의 특징중 하나이니 잘 기억해두자.
저장 프로시저의 경우에는 프로시저를 정의하는 시점에 테이블과 같은 해당 개체의 존재 여부와 상관없이 정의가 가능한데, 그 이유는 해당 테이블의 존재 여부를 프로시저의 실행 시점에 확인하기 때문이다. 그렇기에 해당 테이블의 존재 여부와 상관없이 프로시저는 정의할 수 있다. 그런데 테이블의 열이름이 틀리면 오류가 발생된다.
실무에서 없는 테이블을 프로시저 정의에 사용하는 등의 실수를 할 수 있으니 주의하자.
다음은 생성권한은 확인하는 단계인데 사용자가 저장 프로시저를 생성할 권한이 있는지를 확인하는 과정이다.
마지막으로 시스템 테이블 등록을 진행한다. 저장 프로시저의 이름과 코드가 관련 시스템 테이블에 등록되는 과정이다. 만약 관련 내용을 확인하고자 하면 카탈로그 뷰 sys.objects 및 sys.sql_modules 등을 확인하자.
다음은 첫번째로 저장 프로시저를 실행했을때 벌어지는 일이다.
일반적인 쿼리를 수행하는 것과 비슷하다. 일단 정의 단계에서 구문분석은 끝났기때문에 따로 구문분석을 하지 않는다.
앞서 정의시에 지연된 이름 확인이란게 있었는데, 실제로 해당 개체가 유효한지를 개체이름 확인 단계에서 진행하게 된다. 다시 말해서 저장 프로시저의 실행 시에만 해당 개체가 존재하면 실행이 된다.
그리고 그 이후에 앞서 쿼리문을 돌렸을때와 같이 사용권한 확인, 최적화, 컴파일 및 실행계획 등록 단계를 거치 실행 되게 된다.
이후에 두번째 실행 부터는 메모리(캐시)에 있는 것을 그대로 가져와 재사용하게 되어 수행시간이 많이 단축되게 된다.
이렇게만 보면 일반 sql문과 거의 차이가 없어보인다.
하지만 아래 상황을 보자.
SELECT * FROM userTbl WHERE name ='이승기';
SELECT * FROM userTbl WHERE name ='성시경';
SELECT * FROM userTbl WHERE name ='은지원';해당 쿼리는 where 조건의 값만 다르다. 그런데 앞서 말한 것처럼 일반 쿼리는 글자 하나라도 다르면 다른 쿼리라 인식하기 때문에 세 쿼리 모두 다 다른 것으로 인식해버린다.
그렇기 때문에 매번 최적화와 컴파일을 다시 수행해야한다.
이걸 그냥 아래와 같은 저장 프로시저로 만들면?
CREATE PROC select_by_name
@Name NVARCHAR(3)
AS
SELECT * FROM userTbl WHERE name =@name;
EXEC select_by_name '이승기';
EXEC select_by_name '성시경';
EXEC select_by_name '은지원';이렇게 하면 첫번째 이승기를 검색하는 과정에서만 최적화 및 컴파일을 수행하고 나머지는 메모리(캐시)에 있는것을 사용하게 된다. 실제로 다른 것들의 경과시간이 0ms 인지는 직접 확인해보길 바란다.
결국 자주 쓰는 쿼리라면 일반 쿼리를 여러 개 날리는 것보다 저정 프로시저를 쓰는게 성능적인 측면에서 효과적인 것을 확인 할 수 있다.
저장 프로시저의 문제점
그렇다면 저장 프로시저는 과연 만능의 성능을 자랑할까?
대부분의 경우에는 성능이 향상되나 항상 그렇지는 않다.
앞에서 저장 프로시저를 실행할 때 최적화 단계를 수행한다고 말씀드렸다. 최적화 단계에서 인덱스를 사용할지 안할지를 결정하게 되는데, 다들 아시는 것처럼 인덱스를 사용한다고 항상 수행결과가 빨라지지 않는다.
만약에 가져올 데이터가 다량인데 인덱스를 사용하면 오히려 성능이 바빠지게 될 것이다.
저장 프로시저는 첫번째 수행 시에 최적화가 이루어져서 인덱스 사용여부가 결정되어 버린다.
만약에 첫번째 수행때 데이터를 몇건만 가져오도록 파라미터가 설정되어 있다면, 인덱스를 사용하도록 최적화되어 컴파일 됐을 것이다.
그런데 두번째 수행에서 많은 건수의 데이터를 가져오도록 파라미터가 들어가면..? 일반 쿼리문이었다면 파라미터가 달라졌으니 다시 최적화되어 컴파일 되겠지만... 안타깝게도 저장 프로시저는 그냥 인덱스를 사용하는 프로시저를 실행시켜 버릴 것이다.
이렇게 되어 버리면 성능에 크게 문제가 될 것이다. 이를 방지 하기 위해서는 저장 프로시저를 다시 컴파일 해줘야한다.
다시 컴파일 하는 방법은 여러개가 있는데,
실무에서는 보통 인덱스 사용여부가 불분명하다면 저장 프로시저를 생성한느 시점에서 아예 실행시마다 다시 컴파일 되도록 설정해버리기도 한다.
DROP PROC sp_recompile_test
GO
CREATE PROC sp_recompile_test
[매개변수]
WITH RECOMPILE
AS
[사용 될 쿼리문]
GO2-2 저장 프로시저의 예
저 형식을 바탕으로 간단하게 저장 프로시저를 실습해보자.
먼저 입력 매개변수가 하나일때의 예이다.
CREATE PROCEDURE select_user_by_name
@userName NVARCHAR(10)
AS
SELECT * FROM userTbl WHERE name = @userName;
GOEXEC select_user_by_name '이승기';
다음은 입력 매개변수가 2개 이상일 때, 저장 프로시저의 예이다.
CREATE PROCEDURE select_user_by_first_name_and_height
@userFirstName NVARCHAR(2),
@userHeight INT
AS
SELECT * FROM userTbl WHERE name LIKE @userFirstName+'%' AND height > @userHeight;
GOEXEC select_user_by_first_name_and_height '김',170;
파라미터를 정의한 순서대로 값을 입력해주면되는데,
순서가 바껴도 상관은 없으나 다만 순서가 바뀔시에는 파라미터 명을 함께 넣어서 실행해줘야한다.
EXEC select_user_by_first_name_and_height @userHeight= 170, @userFirstName ='김';
파라미터에 디폴트 값을 넣을 수도 있다.
CREATE PROCEDURE default_select_user_by_first_name_and_height
@userFirstName NVARCHAR(2) ='김',
@userHeight INT = 170
AS
SELECT * FROM userTbl WHERE name LIKE @userFirstName+'%' AND height > @userHeight;
GOEXEC default_select_user_by_first_name_and_height;
출력 파라미터를 설정하는 방법도 알아보자.
CREATE PROCEDURE output_table_identity
@txtValue NCHAR(10),
@outputValue INT OUTPUT
AS
INSERT INTO outputTestTbl Values(@txtValue);
SELECT @outputValue = IDENT_CURRENT('outputTestTbl'); -- 테이블의 현재 identity 값
GO
CREATE TABLE outputTestTbl ( id INT IDENTITY, txt NCHAR(10));
GO위의 내용은 입력된 파라미터에 의해 출력 테이블에 데이터가 들어가는 걸 확인해 볼수 있는 예이다.
outputTestTbl 같은 경우에는 저장 프로시저의 지연된 이름 확인에 의해 생성되기전부터 정의가 가능하다.
DECLARE @myValue INT;
EXEC output_table_identity '텍스트', @myValue OUTPUT;
PRINT 'Current input Id value : ' + CAST(@myValue AS CHAR(5));실행될때마다 id 값이 증가되는 것을 확인할 수 있다.
저장 프로시저 안의 SQL 프로그래밍
저장 프로시저는 프로그래밍하듯 이용이 가능하다.
IF~ELSE
CREATE PROCEDURE if_else_test
@userName NVARCHAR(10)
AS
DECLARE @birthYear INT
DECLARE @tempName NVARCHAR(10) = @userName
SELECT @birthYear = birthYear FROM userTbl WHERE name = @userName;
IF (@birthYear >= 1980 AND @birthYear < 1995)
BEGIN
PRINT @tempName + '님은 M 세대입니다.';
END
ELSE IF (@birthYear >= 1995 )
BEGIN
PRINT @tempName + '님은 Z 세대입니다.';
END
ELSE
BEGIN
PRINT @tempName + '님은 MZ 세대가 아닙니다.';
END
GO
EXEC if_else_test '이승기';
CASE
CREATE procedure case_test
@userName NVARCHAR(10)
AS
DECLARE @birthYear INT
DECLARE @animal NCHAR(3)
SELECT @birthYear = birthYear FROM userTbl WHERE name = @userName;
SET @animal =
CASE
WHEN(@birthYear%12 =0) THEN '원숭이'
WHEN(@birthYear%12 =1) THEN '닭'
WHEN(@birthYear%12 =2) THEN '개'
WHEN(@birthYear%12 =3) THEN '돼지'
WHEN(@birthYear%12 =4) THEN '쥐'
WHEN(@birthYear%12 =5) THEN '소'
WHEN(@birthYear%12 =6) THEN '호랑이'
WHEN(@birthYear%12 =7) THEN '토끼'
WHEN(@birthYear%12 =8) THEN '용'
WHEN(@birthYear%12 =9) THEN '뱀'
WHEN(@birthYear%12 =10) THEN '말'
ELSE '양'
END;
PRINT @userName + '(은)는 ' + @animal+'띠 입니다.';
GO
EXEC case_test '이승기';
WHILE
GO
ALTER TABLE userTbl
ADD userLevel NVARCHAR(5);
GO
CREATE PROCEDURE while_test
AS
DECLARE userCur CURSOR FOR -- 커서 선언
SELECT U.userId, sum(price* amount)
FROM buyTbl B
RIGHT OUTER JOIN userTbl U
ON B.userId = U.userId
GROUP BY U.userId, U.name
OPEN userCur -- 커서 오픈
DECLARE @userId NVARCHAR(10) -- 사용자 아이디
DECLARE @sum BIGINT -- 구매 합
DECLARE @userLevel NCHAR(5) -- 유저별 등급
FETCH NEXT FROM userCur INTO @userId, @sum -- 첫 행 값을 대입
WHILE (@@FETCH_STATUS=0) -- 행이 없을 때까지 반복
BEGIN
SET @userLevel =
CASE
WHEN (@sum >=1500) THEN 'VIP'
WHEN (@sum >= 1000) THEN 'GOLD'
WHEN (@sum >= 50) THEN 'SILVER'
ELSE 'BRONZE'
END
UPDATE userTbl SET userLevel = @userLevel WHERE userId = @userId
FETCH NEXT FROM userCur INTO @userId, @sum -- 다음행 값 대입
END
CLOSE userCur -- 커서 닫기
DEALLOCATE userCur -- 커서 해제
GO
EXEC while_test;
SELECT * FROM userTbl;
RETURN
CREATE PROC return_test
@userName NVARCHAR(10)
AS
DECLARE @userId char(8);
SELECT @userId = userId FROM userTbl WHERE name = @userName;
IF (@userId <>'')
RETURN 0;
ELSE
RETURN -1;
GO
DECLARE @retVal1 INT;
EXEC @retVal1 = return_test '이승기';
SELECT @retVal1;
DECLARE @retVal2 INT;
EXEC @retVal2 = return_test '김연아';
SELECT @retVal2;
ERROR 처리 (로직에서 처리)
CREATE PROC error_test
@userId char(8),
@name NVARCHAR(10),
@birthYear INT = 2021,
@addr NCHAR(2) = '성남',
@mobile1 char(3) = NULL,
@mobile2 char(8) = NULL,
@height smallInt = 170,
@mDate date = '2021-02-05'
AS
DECLARE @err INT;
INSERT INTO userTbl(userId, name, birthYear, addr, mobile1, mobile2, height, mDate) VALUES (@userId, @name, @birthYear, @addr, @mobile1, @mobile2, @height, @mDate);
SELECT @err = @@ERROR;
IF @err != 0
BEGIN
PRINT '###' + @NAME + '은(는) INSERT할 수 없습니다. ###'
END;
RETURN @err;
GO
DECLARE @errNum INT;
EXEC @errNum = error_test 'KBA', '강뽀야';
if (@errNum != 0)
SELECT @errNum;한번 더 수행하면...
DECLARE @errNum INT;
EXEC @errNum = error_test 'KBA', '강뽀야';
if (@errNum != 0)
SELECT @errNum;
ERROR 처리 ( TRY ~ CATCH)
CREATE PROC try_catch_test
@userId char(8),
@name NVARCHAR(10),
@birthYear INT = 2021,
@addr NCHAR(2) = '성남',
@mobile1 char(3) = NULL,
@mobile2 char(8) = NULL,
@height smallInt = 170,
@mDate date = '2021-02-05'
AS
DECLARE @err INT;
BEGIN TRY
INSERT INTO userTbl(userId, name, birthYear, addr, mobile1, mobile2, height, mDate) VALUES (@userId, @name, @birthYear, @addr, @mobile1, @mobile2, @height, @mDate);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()
SELECT ERROR_MESSAGE()
END CATCH
GO
EXEC try_catch_test 'KJS', '강정성';한번 더 수행하면...
EXEC try_catch_test 'KJS', '강정성';
그외 자주 사용되는 것
저장 프로시저 이름 및 내용 조회
SELECT o.name, m.definition
FROM sys.sql_modules m
JOIN sys.objects o
ON m.object_id = o.object_id AND o.TYPE = 'P';
저장 프로시저의 소스코드 확인
EXECUTE sp_helptext if_else_test;
소스코드 암호화
CREATE PROC encrypt_test WITH ENCRYPTION
AS
SELECT * FROM userTbl;
GO
EXECUTE encrypt_test;EXECUTE sp_helptext encrypt_test;
사용자 정의 데이터 형식의 파라미터 사용
파라미터에 READONLY를 꼭 붙여줘야한다.
CREATE TYPE userTblType AS TABLE
(
userId char(8),
name NVARCHAR(10),
birthYear int,
addr NCHAR(2)
)
GO
CREATE PROC test_custom_data
@customTblParam userTblType READONLY
AS
BEGIN
SELECT * FROM @customTblParam WHERE birthYear < 1970;
END
GO
DECLARE @tempVar userTblType;
INSERT INTO @tempVar
SELECT userId, name, birthYear, addr FROM userTbl;
EXEC test_custom_data @tempVar;
임시 저장 프로시저 만들기
프로시저 명 앞에 #, ## 붙여서 생성한다.
'ETC' 카테고리의 다른 글
| ASP.NET Core 웹 개발 (0) | 2023.08.24 |
|---|---|
| MS-SQL (0) | 2023.08.23 |
| C# 기초 문법 (0) | 2023.08.23 |
| ASP.NET Core (0) | 2023.08.23 |