안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 2-4. 데이터형(Data type)과 함수(Function)를 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 데이터형(Data type)과 함수(Function)입니다.

TL;DR

SQL Server의 기본 데이터형과 함수를 살펴봅니다. 특히, 자주 사용되는 함수를 예제 코드와 함께 직접 사용해보겠습니다.
 

데이터형(Data type)

테이블에 데이터를 저장합니다. 문자열형(String data type)에 문자열이 들어가고, 숫자 데이터형(Numeric data type)에 숫자가 들어갑니다. 이렇게 SQL Server의 데이터형은 문자, 숫자 및 여러 데이터 형이 사용됩니다. 

함수(function)

함수란, 재사용 가능한 코드 블록으로 다른 코드에서 호출 가능한 모듈입니다. SQL Server도 다양한 기본 함수와 사용자 정의 함수를 제공합니다. 그럼 데이터형과 함수에 대해서 자세히 살펴보겠습니다.

데이터형

먼저 데이터 형입니다. 실제 테이블을 생성하는 강좌에서 상세하게 다루게 됩니다. 이번 강좌에서는 기본적인 부분만 소개해 드립니다.

정수 데이터형 - int, bigint, smallint 및 tinyint

정수 데이터형으로 테이블에 저장되는 데이터 공간에 맞춰 설정합니다. 예를 들어, 나이는 255를 넘기 힘들기 때문에 tinyint면 충분하겠죠.

int, bigint, smallint 및 tinyint(Transact-SQL) - SQL Server | Microsoft Learn

bit 데이터형

1, 0 또는 NULL 값을 가질 수 있는 정수 데이터 형식으로 참 또는 거짓 값으로 사용됩니다.　

decimal 및 numeric 데이터형

decimal

 -10^38 -1  10^38 -1까지의 수에 대해 정밀도와 크기를 직접 지정 가능 합니다. numeric 데이터형도 같습니다. 소수점 이하 특정 자릿수를 지정할 수 있습니다.

money 및 smallmoney
통화(돈)와 관계되는 데이터 타입입니다.

float 및 real
부동(근사 숫자) 소수 데이터 형입니다.

날짜 및 시간 데이터형

날짜 데이터를 처리할 경우 주로 사용하게 됩니다. 프로그래밍 언어처럼 다양한 데이터형과 처리 함수를 제공합니다.

datetime
1월  1일, 1753년,부터 12월 31일, 9999년, 까지의 데이터를 1000분의 일초 단위로 지정 가능 합니다.

-- datetime 예제
DECLARE @date date = '2023-7-6';     --변수를 선언
DECLARE @datetime datetime = @date;  
  
SELECT @datetime AS '@datetime', @date AS '@date';  

결과
@datetime               @date
----------------------- ----------
2023-07-06 00:00:00.000 2023-07-06

(1개 행 적용됨)

smalldatetime
1월 1일, 1900년, through 6월 6일, 2079년, 까지 1분 단위 정확도로 지정 가능 합니다.
- 날짜 및 시간 형식 - SQL Server

문자열(string) 및 이진(binary) 데이터형

char 및 varchar

고정 크기(char) 또는 가변 크기(varchar)인 문자 데이터 형식입니다. SQL Server 2019부터 UTF-8 “데이터 정렬(collation)”을 사용할 때 유니코드 문자 데이터로 저장되고 UTF-8 인코딩을 사용합니다. 처음 접하면 어려운 용어입니다. UTF-8이나 유니코드는 검색 등을 통해 조금 더 확인해 보세요.

☑️ 챗GPT 활용: 유니코드와 UTF-8 인코딩에 대해서 설명해 줘

nchar 및 nvarchar

고정 크기(nchar) 또는 가변 크기(nvarchar)인 유니코드 문자 데이터 형식입니다.

binary 및 varbinary
고정 길이 또는 가변 길이의 이진 데이터 형식입니다. 

ntext, text 및 image
차기 SQL Server에서 제거될 예정이니 사용하지 마시길 바랍니다. 대신 nvarchar(max), varchar(max),  varbinary(max)를 사용하세요. 대용량의 유니코드 및 비유니코드 문자와 이진 데이터를 저장하기 위한 고정 및 가변 길이 데이터 형식입니다. 

“var”가 붙어있는 데이터형

많이 질문받는 내용으로 var형과 var가 안 붙은 데이터 형 차이입니다. varchar과 char의 차이를 조금 더 말씀드리면, 1번 컬럼은 char(10)  2번 컬럼은 varchar(10)으로 컬럼을 잡습니다. 9글자 문자열 ‘kimdaewoo’를 입력하고 출력합니다.

그럼 1번 컬럼에는 'kimdaewoo '가 저장됩니다. 즉 9자의 알파벳과 공백 하나가 들어갑니다. 항상 지정한 10개의 문자를 채워 저장됩니다.

varchar는 'kimdaewoo'를 입력하면 'kimdaewoo'로 딱 9자만 들어갑니다. 'daewoo'를 넣으면 'daewoo' 6자만 저장됩니다.

DECLARE @name char(10) = 'kimdaewoo';
DECLARE @vname varchar(10) = 'kimdaewoo';

SELECT @name, @vname;  --출력하면 @name은 뒤에 공백이 있습니다.

var가 붙으면 저장 시 뒷부분의 공백이 잘려 저장되어 저장 공간이(디스크) 낭비되지 않습니다. 하지만, varchar는 내부적으로 데이터를 처리할 때 데이터 블록 위치를 계산하는 루틴이 있어 아주 미세한 성능 차이가 있을 수 있습니다.

기타 데이터 형식

uniqueidentifier 데이터형
16 바이트 GUID(UUID)입니다. NEWID() 함수로 생성할 수 있습니다.

DECLARE @myid uniqueidentifier = NEWID();  
SELECT @myid;

결과
------------------------------------
C67D8EEC-4B59-4AE1-8A33-9A073236CC5B

☑️ 챗GPT 활용: GUID와 UUID에 대해서 알려줘

geography 데이터형

공간 데이터형(Spatial Types)으로 둥근 표면 좌표 시스템의 데이터를 나타내며 일반적으로 GPS 위도와 경도 데이터를 저장할 수 있습니다.

DECLARE @GeogVal1 geography = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656 )', 4326);
DECLARE @GeogVal2 geography = geography::STGeomFromText('POLYGON((-122.358 47.653 , -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);

SELECT @GeogVal1, @GeogVal2;
SELECT @GeogVal1.STIntersection(@GeogVal2).STAsText();  -- 교차점 출력

결과
각 geography 출력                   
----------------------------------------------------------------
0xE610000001148716D9CEF7D34740D7A3703D0A975EC08…
0xE6100000010405000000DD24068195D34740F4FDD478E996…

STIntersection으로 교차점 출력
----------------------------------------------------------------
LINESTRING (-122.35800000000017 47.656000130337446, -122.34800000000006 47.656000260674908)

table 데이터형
table 데이터형은 결과집합을 임시 저장하는 데이터형입니다. 하나의 value 값이 아닌 테이블형을 저장할 수 있습니다. 

DECLARE @T TABLE (id int, name varchar(8));
INSERT @T values(10,'Joe');
select * from @T;

결과
id          name
----------- --------
10          Joe

이 외에도 XML, cursor, rowversion 등이 있습니다.

함수(function)

함수란, 재사용 가능한 코드 블록으로 다른 코드에서 호출 가능한 모듈을 말합니다. SQL Server도 다양한 기본 함수와 사용자 정의 함수를 제공합니다.

SQL서버의 함수의 종류

집계함수(Aggregate Functions)

테이블 값 집합에 대해 하나의 값을 리턴합니다. COUNT(), SUM() 등이 집계함수이며 자주 사용됩니다.

분석함수(Analytic functions)
로우들의 그룹에 대한 계산된 집계 결과를 출력합니다. PERCENTILE_CONT() 등이 있습니다.

순위함수(Ranking functions)
개별 로우의 랭크를 출력합니다. RANK() 함수 등이 있습니다.

스칼라함수(Scalar functions)
단일 값에 대해 변환된 값을 리턴합니다.

함수들이 많아 보입니다. 걱정 마시고, 많이 사용하는 함수들을 살펴보고 필요하면 언제든지 검색해 사용할 수 있습니다.

- 함수 소개 링크: Microsoft SQL 데이터베이스 함수란? - SQL Server

집계함수(Aggregate Functions)

일반적으로 테이블 집계 작업에 사용합니다. 주로 테이블 로우에 적용해 결과 또는 결과 집합을 생성합니다. AVG, COUNT, MAX, MIN, SUM 등이 있습니다.

AVG 함수
수치형으로 된 컬럼 로우에 대해 평균을 리턴합니다.

USE AdventureWorks;
GO

SELECT AVG(ListPrice) FROM Production.Product;

SUM 함수
수치형 컬럼의 로우에 대해 수치데이터의 총합값을 계산합니다.

SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail;

COUNT 함수
그룹의 항목 수를 리턴합니다. COUNT 함수 파라미터는 *로 잡으셔도 되며 컬럼 1개를 잡으셔도 같은 결과입니다. 

SELECT COUNT(*) FROM Production.Product;
SELECT COUNT(ProductID) FROM Production.Product;

MAX 함수
표현식(expression) 또는 숫자형 그룹 컬럼의 최댓값을 리턴합니다. MIN 함수 - 최솟값을 리턴합니다. 

SELECT MIN(ListPrice), MAX(ListPrice) FROM Production.Product;

날짜 및 시간 함수

날짜 및 시간데이터 형식을 다루는 함수입니다. DATEADD, DATEDIFF, DATEPART, GETDATE 등이 있습니다.

GETDATE 함수
현재 날짜와 시간을 출력합니다.

GETUTCDATE 함수
현재 날짜와 시간을 UTC(Coordinated Universal Time)로 출력합니다.

SELECT GETDATE();
SELECT GETUTCDATE();

☑️ 챗GPT 활용: UTC(Coordinated Universal Time)에 대해서 알려줘

DATEADD 함수
기존 날짜 데이터에 대해 날짜, 월, 년 등의 날짜 데이터를 더하는 함수입니다.

SELECT DATEADD(month, 1, '2023-07-06');  --1개월 추가
SELECT DATEADD(day, 3, '2023-07-06');  --3일 추가
SELECT DATEADD(year, -1, '2023-07-06');  --1년 빼기

결과
-----------------------
2023-08-06 00:00:00.000
2023-07-09 00:00:00.000
2022-07-06 00:00:00.000

SELECT SellStartDate, DATEADD(year, 1, SellStartDate) as AddedDate
FROM Production.Product;

결과
SellStartDate           AddedDate
----------------------- -----------------------
2008-04-30 00:00:00.000 2009-04-30 00:00:00.000
2008-04-30 00:00:00.000 2009-04-30 00:00:00.000
2008-04-30 00:00:00.000 2009-04-30 00:00:00.000
...

DATEDIFF 함수
지정일부터 지정일까지의 datepart(연월일시분초 등)를 정수 값으로 출력

SELECT DATEDIFF(year, '2023-07-06', '2025-01-01');
SELECT DATEDIFF(quarter, '2023-07-06', '2025-01-01');
SELECT DATEDIFF(month, '2023-07-06', '2025-01-01');

SELECT SellStartDate, DATEDIFF(day, SellStartDate, GETDATE()) as CountDays FROM Production.Product;

DATEPART 함수
지정한 날짜의 datepart(연월일시분초 등)를 출력합니다.

SELECT DATEPART(weekday, '2023-07-06 12:15:32.1234567');
SELECT DATEPART(hour, '2023-07-06 12:15:32.1234567');
SELECT DATEPART(second, '2023-07-06 12:15:32.1234567');

SELECT SellStartDate, DATEPART(day, SellStartDate) as DayPart FROM Production.Product;

참고로, datepart는 연월부터 나노초(nanosecond)까지 설정 가능합니다. 
- datepart 참조: DATEDIFF(Transact-SQL) - SQL Server | Microsoft Learn

수치 연산 함수(Mathematical Function)

다음은 수치 연산 함수입니다.

ABS 함수
절댓값을 리턴합니다.

SELECT ABS(-1.0), ABS(0.0), ABS(1.0);

RAND 함수
0~1 사이의 소수로된 난수를 생성합니다. 파라미터는 seed 값으로 초기 설정값이며 없을 경우 임의값이 설정됩니다.

SELECT RAND(100), RAND(), RAND();

결과
0.715436657367485    0.28463380767982    0.0131039082850364

ROUND 함수
지정위치에서 반올림합니다.

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3);  
GO

결과
123.9990    124.0000

자주 사용되기 때문에 몇 개 예제를 추가합니다.

SELECT ROUND(748.58, -1);  --750.00
SELECT ROUND(748.58, -2);  --700.00
SELECT ROUND(748.58, -3);  -- Arithmetic overflow 오류 발생. 1000.00은 decimal(5,2)로 설정 불가
SELECT ROUND(123.4545, 2);  --123.4500
SELECT ROUND(123.45, -2);  --100.00
SELECT ROUND(150.75, 0);  --151.00
SELECT ROUND(150.75, 0, 1);  --150.00
SELECT ROUND(748.58, -4);  --0.00

SQUARE 함수
제곱값을 리턴합니다.

SELECT SQUARE(5);

결과
25
 

문자열 처리 함수(String Function)

문자열 처리 함수도 실무에서 다양하게 사용됩니다. 다음은 자주 사용하는 함수 예제입니다.

LEFT 함수
문자열 왼쪽부터 지정 숫자만큼 문자를 리턴

SELECT LEFT('abcdefg', 2);

결과
ab

SELECT Name, LEFT(Name, 5) as First5Char FROM Production.Product;
GO

결과
Name                                               First5Char 
-------------------------------------------------- -----
Adjustable Race                                    Adjus
All-Purpose Bike Stand                             All-P
...

LEN 함수
문자열의 캐릭터 수를 리턴합니다.

SELECT LEN('abcdefg');

결과
-----------
7

SELECT Name, LEN(Name) as NameLength FROM Production.Product;
GO

결과
Name                                               NameLength
------------------------------------------------- -----------
Adjustable Race                                    15
...

LOWER 함수
문자열(영문)을 모두 소문자로 변경합니다.

SELECT LOWER('Hello World and SQL Server');

결과
--------------------------
hello world and sql server

SELECT Name, LOWER(Name) as LowerName FROM Production.Product;

결과
Name                        LowerName 
---------------------------------------- 
Adjustable Race             adjustable race
All-Purpose Bike Stand      all-purpose bike stand

TRIM 함수
문자열 양쪽의 공백을 제거합니다.

SELECT TRIM( '     test    ') AS Result;

결과
-------------
test

PATINDEX 함수
문자열 중 지정한 패턴이 처음 발생한 위치를 리턴합니다. 못 찾으면 0을 리턴합니다.

SELECT PATINDEX('%ter%', 'interesting data') as PatternPos;

결과
3

SELECT Name, PATINDEX('%ball%', Name) as PatternPos FROM Production.Product;

결과
Name                                               PatternPos
------------------------------------------------- -----------
Adjustable Race                                    0
All-Purpose Bike Stand                             0
AWC Logo Cap                                       0
BB Ball Bearing                                    4
Bearing Ball                                       9
...

REPLACE 함수
문자열 중 일정 문자열 또는 문자를 다른 문자열로 변환합니다. 

SELECT REPLACE('abcdefghicde','cde','xyz');

결과
abxyzfghixyz
　
SELECT Name, REPLACE(Name, ' ', '-') as ReplacedName FROM Production.Product;

결과
Name                        ReplacedName
------------------------------------------------------
Adjustable Race             Adjustable-Race
All-Purpose Bike Stand      All-Purpose-Bike-Stand
...

RIGHT 함수
문자열의 오른쪽부터 지정한 수만큼 문자열을 가져옵니다.

SELECT RIGHT('abcdefg', 3); 

결과
----
efg
　
SELECT Name, RIGHT(Name, 5) as RightString FROM Production.Product;

결과
Name                                               RightString
-------------------------------------------------- ----------
Adjustable Race                                    Race
All-Purpose Bike Stand                             Stand

STUFF 함수
문자열의 지정한 위치부터 원하는 길이까지 특정 문자열로 변환합니다.

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

결과
---------
aijklmnef

SUBSTRING 함수
문자열중 원하는 위치부터 원하는 길이만큼 문자열을 가져옵니다.

SELECT SUBSTRING('abcdef', 2, 3);

결과
----
bcd

SELECT Name, SUBSTRING(Name, 2,5) as NamePart FROM Production.Product;

결과
Name                                               NamePart
-------------------------------------------------- --------
Adjustable Race                                    djust
All-Purpose Bike Stand                             ll-Pu

자 이렇게 해서 많이 사용되는 함수들에 대해서 살펴보았습니다.

Aggregate Functions, Date and Time Functions, Mathematical Functions, String Functions 이 함수들은 개발 업무를 하면서 여러 번 사용하게 되실 거에요. 꼭 한 번씩은 수행해 보세요. 강좌의 함수 말고도 많은 함수들이 있으니 이런 함수는 없나? 싶을 때는 꼭 검색해 보세요.

예를 들어, 문자열처리가 필요한데 - 문자열 함수에서, 날짜와 관계된 함수가 필요한데 - 날짜 시간 함수에서 찾아보시면 됩니다. 찾고 있는 함수가 없어도 차근차근 강좌를 보면서 공부하면 다양한 함수를 직접 만드는 것도 가능합니다. 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022