<SQL Server> 04. Transact SQL 고급 - 1

Transact SQL 고급 - 1

SQL Server의 데이터 형식

Data Type은 데이터 형식, 데이터형, 자료형, 데이터 타입 등 다양하게 불릴 수 있지만, SQL Server의 도움말에서 ‘데이터 형식’으로 표기하고 있음

SELECT 문을 더욱 잘 활용하고 테이블을 효율적으로 생성하려면 데이터 형식을 이해해야 한다.

SQL Server에서 지원하는 데이터 형식의 종류

SQL Server에서 데이터 형식의 종류는 30개 가까이 된다. 이를 모두 외우는 것은 무리이며, 거의 쓰이지 않는 것도 있으니 그럴 필요도 없다. 또한, 각각의 바이트 수나 숫자의 범위를 외우는 것도 당장 SQL Server를 학습하는 데 큰 도움이 되는 것은 아니다 (필요 없다는 얘기가 아니라, 당장 모두 외울 필요가 없다는 의미다).

숫자 데이터 형식

숫자형 데이터 형식은 정수, 실수 등의 숫자를 표현한다. (★중요)

문자 데이터 형식

CHAR 형식은 고정길이 문자형으로 자릿수가 고정되어 있다. 예를 들어, CHAR(100)에 ‘ABC’ 3바이트만 저장해도, 100바이트를 모두 확보한 후에 앞에 3바이트를 사용하고 뒤의 97바이트는 낭비하게 되는 결과가 나온다. VARCHAR 형식은 가변길이 문자형으로 VARCHAR(100)에 ‘ABC’ 3바이트를 저장할 경우 3바이트만 사용한다. 그래서 공간을 효율적으로 운영할 수 있다. 하지만, 대용량 데이터베이스르를 사용할 때, 4글자 미만을 저장하게 될 경우는 CHAR 형식으로 설정하는 것이 INSERT/UPDATE 시에 더 좋은 성능을 발휘한다.

또, 한글(유니코드)을 저장하기 위해서 CHAR나 VARCHAR 형식으로 정의할 경우에는 2배의 자릿수를 준비해야 한다. 즉, 최대 3글자의 한글을 저장하고자 한다면, CHAR(6) 또는 VARCHAR(6)로 데이터 형식을 지정해야 한다. 한글은 2바이트의 자리를 차지하기 때문이다. 이러한 문제를 간단히 내려면 NCHAR이나 NVARCHAR 형식을 사용하면 된다. 데이터 형식은 한글뿐 아니라 다른 나라 언어의 문제까지 해결해 준다. 예를 들어 NCHAR(3)으로 지정하면 한글, 중국어, 일본어, 프랑스어, 영어 등 모든 언어를 무조건 3글자 저장할 수 있어서 더 단순하고 명확하게 문자 데이터를 입력할 수 있다.

날짜와 시간 데이터 형식

기타 데이터 형식

ISO 표준 데이터 형식

사용자 정의 데이터 형식

사용자 정의 데이터 형식(또는 사용자 정의 형식)은 기존의 데이터 형식에 별칭을 붙이는 것으로, 사용의 편의성 때문에 주로 사용한다. 예를 들어, 이름을 저장하는 데이터 형식이 NCHAR(10)이라면 편리하게 myTypeName 등으로 붙일 수 있다.

사용자 정의 데이터 형식은 SSMS의 개체 탐색기에서 [데이터베이스] >> [해당 데이터베이스] >> [프로그래밍 기능] >> [유형] >> [사용자 정의 데이터]에서 정의할 수 있다.

사용자 정의 데이터 형식은 CREATE TYPE 문을 사용해 T-SQL로 지정한다.

•

형식

CREATE TYPE 사용자정의데이터형식_이름 FROM 기존데이터형식 NULL 또는 NOT NULL;

SQL

•

위의 SSMS에서 사용한 예를 CREATE TYPE 문으로 정의하면 다음과 같다.

USE sqlDB; CREATE TYPE myTypeName FROM NCHAR(10) NOT NULL;

SQL

MAX 데이터 형식

VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)

SQL Server는 대량의 데이터를 저장하기 위해서 VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) 데이터 형식을 지원한다.

지원되는 데이터 크기는 $2^{23}-1Byte$까지 저장할 수 있다. 이는 약 2GB 크기의 파일을 하나의 데이터로 저장할 수 있다는 의미다. 예를 들어 장편 소설과 같은 큰 텍스트 파일이라면, 그 내용을 전부 NVARCHAR(MAX) 형식으로 지정된 하나의 컬럼에 넣고, 동영상 파일과 같은 큰 바이너리 파일이라면, 그 내용을 전부 VARBINARY(MAX) 형식으로 지정된 하나의 컬럼에 넣을 수 있다.

다음과 같은 구성이 가능하다.

위의 예를 보면 영화 대본 열에는 영화 대본 전체가 들어가고, 영화 동영상 열에는 실제 영화 파일 전체가 들어갈 수 있다. 또한 VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)는 일반적인 VARCHAR(N) 형식과 동일하게 사용이 가능하다.

LOB 데이터 중에서 이미지, 동영상, 음악 등의 이진데이터를 BLOB $BLOB^{Binary Large Object}$라고 부른다. BLOB 데이터를 저장하기 위해서는 varbinary(max) 형식을 사용하면 되는데, varbinary(max)는 최대 크기가 2GB까지의 파일만 저장할 수 있다. 그래서 2GB 크기 이상의 파일을 저장하려면 FILESTREAM을 사용해야 된다. FILESTREAM은 Windows 운영체제의 파일시스템인 NTFS를 사용하기에 파일 크기에 제한이 없으며, 저장, 추출 성능 또한 상당히 뛰어나다. 참고로 FILESTREAM은 SQL Server 2008부터 지원되었으며, FILESTREAM을 기반으로 FileTable이라는 테이블은 SQL Server 2012부터 지원한다. SQL Server 2014부터는 ‘메모리 액세스에 최적화된 테이블’이 제공된다.

유니코드 데이터

특징 : N자가 들어감

문자 데이터를 저장하고 관리할 경우, 각 국가별 코드 페이지가 달라서 서로 호환되지 않는 문제점이 있다. 즉, 데이터베이스에 한국어만 사용할 경우에는 별 문제가 없지만, 한국어/중국어/일본어를 동시에 사용할 경우에는 코드 페이지가 서로 달라서 많은 문제점을 일으키게 된다.

이러한 문제점을 갖고 있는 데이터 형식이 CHAR, VARCHAR, VARCHAR(MAX)다. 방금 얘기했듯이 한 국가의 언어만을 처리하는 데는 이 데이터 형식이 전혀 문제되진 않지만, 여러 언어를 처리한다면 NCHAR, NVARCHAR, NVARCHAR(MAX) 형식을 사용해야 한다. 여기서 n은 National의 약자로 유니코드를 의미한다. 데이터 형식을 사용하게 되면, 전 세계의 어느 언어를 저장하든지 서로 충돌하는 상황이 발생하지 않는 장점이 있다. 유니코드 데이터 형식인 NCHAR, NVARCHAR, NVARCHAR(max)는 CHAR, VARCHAR, VARCHAR(max)와 동일하게 사용하면 되지만, 몇 가지 차이점이 있다.

•

유니코드 문자를 저장 시에는 내부적으로 더 넓은 공간이 필요하다. 즉, CHAR(4)는 4바이트지만 NCHAR(4)는 8바이트를 내부적으로 차지한다.

•

NCHAR의 열의 최대 크기는 8000이 아닌 그의 절반인 4000이다. 유니코드는 2바이트의 공간을 요구하기 떄문이다.

•

유니코드의 상수를 지정하기 위해서는 ‘N문자열’과 같은 형식을 사용한다.

•

유니코드는 문자에만 관련된 얘기이며, 숫자/날짜 등에서는 전혀 신경 쓸 필요가 없다.

데이터베이스를 다른 국가의 언어와 호환할 예정이 아니라면 유니코드와 관련된 내용은 무시해도 된다. 간단히 얘기하면 유니코드는 다양한 국가의 문자를 동시에 저장할 때 사용된다. 그래서 NCHAR 또는 NVARCHAR 형식에 문자를 입력할 때 N’문자열’ 형식(꼭 대문자 N이어야 함)을 취하는 것을 권장하는 것이며, N을 생략해도 한글 운영체제에서 한글/영문만 입력하는 데 별 문제가 없다.

USE tempdb; CREATE TABLE uniTest( korName NVARCHAR(10) ); GO INSERT INTO uniTest VALUES ( N'박신혜'); -- 맞음 INSERT INTO uniTest VALUES ( '박신혜'); -- 맞음 INSERT INTO uniTest VALUES ( n'박신혜'); -- 틀림

SQL

변수의 사용

T-SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다. $변수^{Variable}$의 선언과 값의 대입은 다음의 형식을 따른다.

변수의 선언: DECLARE @변수이름 데이터 형식 ; 변수의 값 대입: SET @변수이름 = 변수의 값 ; 변수의 값 출력: SELECT @변수이름 ;

SQL

변수는 일시적으로 사용되는 것이므로 재사용되지 않는다. 즉, 한 번 실행되면 바로 소멸된다.

sqlDB 초기화

USE tempdb; RESTORE DATABASE sqlDB FROM DISK = 'C:\SQL\sqlDB2016.bak' WITH REPLACE;

SQL

변수 선언 예시

USE sqlDB; DECLARE @myVar1 INT; DECLARE @myVar2 SMALLINT, @myVar3 DECIMAL(5,2); DECLARE @myVar4 NCHAR(20); SET @myVar1 = 5 ; SET @myVar2 = 3 ; SET @myVar3 = 4.25 ; SET @myVar4 = N'가수 이름==> ' ; SELECT @myVar1 ; SELECT @myVar2 + @myVar3 ; SELECT @myVar4 , name FROM userTbl where height > 180 ;

SQL

변수 선언, SELECT TOP 구문 사용

DECLARE @myVar1 INT ; SET @myVar1 = 3 ; SELECT TOP(@myVar1), name FROM userTbl ORDER BY height ;

SQL

데이터 형식과 관련된 시스템 함수

데이터 형식과 관련된 함수는 자주 사용되므로 잘 기억하자.

데이터 형식 변환 함수

가장 일반적으로 사용되는 데이터 형식 변환과 관련해서는 CAST(), CONVERT(), TRY_CONVERT(), PARSE(), TRY_PARSE() 함수를 사용한다. CAST(), CONVERT()는 형식만 다를 뿐 거의 비슷한 기능을 한다.

TRY_CONVERT()는 CONVERT()와 동일하지만 변환에 실패할 경우에 Null 값을 반환한다. PARSE()와 TRY_PARSE() 함수는 문자열에서 날짜/시간 및 숫자 형식으로 변환하는 경우에 사용된다. TRY_PARSE()도 PARSE()와 같지만 변환에 실패할 경우에 Null 값을 반환한다.

형식

CAST ( expression AS 데이터형식 [ (길이) ]) CONVERT ( 데이터형식[(길이)] , expression [ , 스타일 ] ) TRY_CONVERT ( 데이터형식[(길이)] , expression [ , 스타일 ] ) PARSE ( 문자열 AS 데이터형식) TRY_PARSE ( 문자열 AS 데이터형식)

SQL

사용 예를 보면 좀 더 쉽게 이해될 것이다. 아래는 sqlDB의 구매 테이블(buyTbl)에서 평균 구매 개수를 구하는 쿼리문이다.

USE sqlDB; SELECT AVG(amount) AS [평균 구매 개수] FROM buyTbl;

SQL

결과는 2가 나왔다. 그 이유는 수량의 데이터 형식이 정수형(INT)이므로, 정수형과 정수형의 계산 결과는 정수형이 되기 때문이다.

이것을 아래와 같이 CAST() 함수나 CONVERT(), TRY_CONVERT() 함수를 사용할 수 있다.

SELECT AVG( CAST(amount AS FLOAT) ) AS [평균구매개수] FROM buyTbl ;

SQL

또는

SELECT AVG( CONVERT(amount, FLOAT) ) AS [평균구매개수] FROM buyTbl ; -- 또는 SELECT AVG( TRY_CONVERT(amount, FLOAT) ) AS [평균구매개수] FROM buyTbl ;

SQL

이번에는 단가/수량의 결과를 살펴보자. 둘 다 int 형이므로 결과도 int 형이다.

SELECT price, amount, price/amount AS [단가/수량] FROM buyTbl ;

SQL

결과 중 두 개(5행, 8행)은 정수이기 때문에 정확한 값이 아니다. 이를 CAST() 함수나 CONVERT() 또는 TRY_CONVERT() 함수를 사용해서 실수로 변환하자. 그리고 출력되는 값을 소수점 아래 2자리까지만 출력하자.

SELECT price, amount, CAST(CAST(price AS FLOAT)/amount AS DECIMAL(10,2)) AS [단가/수량] FROM buyTbl ;

SQL

문자열을 변환할 경우에는 PARSE()나 TRY_PARSE()를 사용할 수 있다.

SELECT PARSE('2019년 9월 9일' AS DATE) ;

SQL

PARSE()가 실패할 경우에는 오류가 발생되지만, TRY_PARSE()는 정상적으로 실행되고 Null값을 반환한다.

SELECT PARSE('123.45' AS INT) ; SELECT TRY_PARSE('123.45' AS INT);

SQL

위 첫 문장은 쿼리에서 오류가 발생되지만, 두 번째 문장은 오류가 발생하지 않고 Null을 반환한다. TRY_CONVERT() 및 TRY_PARSE()는 프로그래밍에서 오류의 경우에도 계속 진행하고자 할 경우에 유용하게 사용될 수 있다.

암시적인 형변환

형 변환 방식에는 명시적인 변환과 암시적인 변환 두 가지가 있다. 명시적인 변환이란 위에서 한 CAST() 또는 CONVERT() 함수를 이용해서 데이터 형식을 변환하는 것을 말한다. 암시적인 변환이란 CAST()나 CONVERT() 함수를 사용하지 않고 형이 변환되는 것을 말한다. 다음 예를 보자.

DECLARE @myVar1 CHAR(3) ; SET @myVar1 = '100' ; SELECT @myVar1 + '200' ; -- 문자와 문자를 더함(정상) SELECT @myVar1 + 200 ; -- 문자와 정수를 더함(정상) SELECT @myVar1 + 200.0 ; -- 문자와 실수를 더함(정상)

SQL

첫번째 결과인 ‘문자 + 문자 = 문자’의 정상적인 결과가 나왔다. 그런데 두 번째와 세 번째는 ‘문자 + 숫자’라서 서로 더할 수가 없지만, 암시적인 변환이 일어나서 ‘문자 + 정수 = 정수’, ‘문자 + 실수 = 실수’라는 결과가 나온 것이다. 모두 정상적인 결과다. 이곳을 직접 명시적인 변환으로 고치면 다음과 같다.

DECLARE @myVar1 CHAR(3) ; SET @myVar1 = '100' ; SELECT @myVar1 + '200' ; -- 문자와 문자를 더함(정상) SELECT CAST(@myVar1 AS INT) + 200 ; -- 정수로 변환후 연산 SELECT CAST(@myVar1 AS DECIMAL(5,1)) + 200.0 ; -- 실수로 변환후 연산

SQL

이처럼 암시적인 변환과 명시적인 변환 중에서 어느 것이 더 편리한가? 마치, 암시적인 변환이 코딩량을 줄여줄 수 있는 것처럼 보일 수도 있지만, 조금 불편하더라도 명시적인 변환을 사용하는 것을 권장한다. 암시적인 변환을 믿고 그냥 사용하게 될 경우에는, 약간의 착오로 결과가 엉뚱하게 나오는 사고(?)가 발생할 소지가 항상 있다.

그 외에 형 변환을 할 때 주의할 사항은 숫자에서 문자로 변환할 때 문자의 자릿수를 잘 고려해야 한다는 점이다.

DECLARE @myVar2 DECIMAL(10,5) ; SET @myVar2 = 10.12345 ; SELECT CAST(@myvar2 AS NCHAR(5)) ;

SQL

이 오류는 10.12345를 문자로 변환하게 되면 8글자로 변환되므로, 문자형 8자리 이상이 필요해서 나오는 오류다. NCHAR(5)를 NCHAR(8) 이상으로 수정한 후에 사용해야 한다.

또, 한 가지는 실수를 정수로 변환 할 때 자릿수가 잘릴 수 있다는 점을 고려해야 한다.

DECLARE @myVar3 DECIMAL(10,5) ; SET @myVar3 = 10.12345 ; SELECT CAST(@myVar3 AS INT) ; DECLARE @myVar4 DECIMAL(10,5) ; SET @myVar4 = 10.12345 ; SELECT CAST(@myVar4 AS DECIMAL(10,2)) ;

SQL

두 결과는 모두 오류는 아니지만 첫 번째는 결과가 10으로, 두 번째는 결과가 10.12로 자릿수가 잘리는 점에 유의해야 한다.

Made with