T-SQL이란? SQL Server를 위한 Transact-SQL 입문 가이드

T-SQL의 함수

T-SQL에는 데이터를 변환하는 데 쓰이는 내장 함수와 사용자 정의 함수(UDF)가 모두 포함되어 있습니다.

내장 함수

이 함수들은 SQL Server에 기본 제공되며 쿼리에서 바로 사용할 수 있습니다.

문자열 함수

문자열 데이터 유형을 변환하는 함수입니다. 예를 들어, 아래 쿼리는 Customers 테이블에서 모든 이름을 조회하여 대문자로 변환합니다.

-- Converts text to uppercase
SELECT UPPER(FirstName)  
FROM Customers;

날짜 함수

날짜 관련 값을 변환합니다. 아래 쿼리는 현재 시스템 날짜와 시간을 반환합니다.

-- Returns current date and time
SELECT GETDATE();  

집계 함수

테이블의 값을 집계하는 함수입니다. 예를 들어, 아래 쿼리는 Customers 테이블의 전체 행(고객 수)을 계산합니다.

-- Counts rows
SELECT COUNT(*) AS TotalCustomers
FROM Customers;  

사용자 정의 함수(UDF)

여러 쿼리에서 재사용할 로직을 직접 만들어 함수로 정의할 수도 있습니다. 

스칼라 함수

스칼라 함수는 하나의 값을 반환하며, 서식 지정이나 계산처럼 단일 계산 결과가 필요할 때 유용합니다. 아래 함수는 이름과 성을 입력으로 받아 하나의 전체 이름으로 반환합니다.

-- Create a function that combines the first and last name
CREATE FUNCTION GetFullName 
(@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName; -- Concatenates the two values
END;

테이블 반환 함수

테이블 반환 함수는 테이블을 반환합니다. 예를 들어, 아래 함수는 지정한 국가에 속한 모든 고객 테이블을 반환합니다.

-- Create a table-valued function named GetCustomersByCountry
CREATE FUNCTION GetCustomersByCountry (@Country VARCHAR(50))
RETURNS TABLE -- Specifies that the function returns a table
AS
RETURN
(
    -- Select all columns from the Customers table
    SELECT *
    FROM Customers
    WHERE Country = @Country -- Filter rows by the given country
);

저장 프로시저의 생성, 업데이트, 실행은 물론 집계 함수, 조인, 삽입, 삭제 등 폭넓은 기술을 익히려면 전체 SQL Server Developer 커리어 트랙을 추천합니다.

T-SQL의 오류 처리

T-SQL은 TRY...CATCH 블록을 사용해 전체 스크립트를 중단하지 않고 오류를 처리합니다. 이는 예기치 않은 중단을 방지하고, 디버깅이나 로깅을 위한 의미 있는 메시지를 반환할 수 있어 중요합니다.

예를 들어, 아래 쿼리는 오류를 일으키는 계산을 시도한 뒤 이를 포착하여, 중단 대신 읽기 쉬운 오류 메시지를 출력합니다.

BEGIN TRY
    -- Attempt to run this code
    SELECT 1 / 0;  -- This causes a divide-by-zero error
END TRY
BEGIN CATCH
    -- Runs if an error occurs in the TRY block
    PRINT 'An error occurred: ' + ERROR_MESSAGE(); -- Displays the error message
END CATCH;

T-SQL의 트랜잭션

트랜잭션은 여러 작업이 모두 성공하거나 모두 실패하도록 보장합니다. 이는 ACID 원칙을 따르며, 업데이트 도중 전원이 꺼지더라도 데이터베이스의 일관성을 보장합니다.

아래 예시에서는 하나의 계정에서 다른 계정으로 금액을 이체하며 두 변경 사항을 함께 저장합니다.

BEGIN TRANSACTION; -- Start the transaction

-- Deduct money from Account 1
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Add money to Account 2
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

COMMIT; -- Save all changes permanently

문제가 발생하면 ROLLBACK으로 트랜잭션을 취소하고 데이터를 이전 상태로 복원할 수 있습니다.

-- Undo all changes made in the current transaction
ROLLBACK; 

T-SQL의 트랜잭션은 특히 금융 애플리케이션처럼 부분 업데이트가 불일치를 초래할 수 있는 중요 시스템에서 데이터 무결성을 보장하는 데 중요합니다.

트랜잭션과 오류 처리를 다루는 Transactions and Error Handling in SQL Server 커리어 트랙을 수강해 보세요.

데이터 분석을 위한 주요 T-SQL 기능

T-SQL에는 데이터 전문가에게 특히 유용한 도구가 포함되어 있습니다. 몇 가지 예와 데이터 분석에서의 적용 방법을 살펴보겠습니다.

윈도 함수

윈도 함수는 행을 하나의 결과로 그룹화하지 않고도 행 전반에 걸쳐 계산을 수행합니다. 주로 순위 매기기, 누계, 고급 분석에 사용됩니다.

아래 예시에서 쿼리는 CustomerID를 기준으로 각 고객에게 고유한 행 번호를 부여합니다.

-- Assigns row numbers to customers
SELECT 
    FirstName,
    ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum -- Assigns row numbers
FROM Customers;

이 쿼리에서

• ROW_NUMBER(): 고유한 행 번호를 부여

• RANK(): 동률에 공백을 두고 순위 부여

• OVER(): 윈도 정의(정렬/분할)

다운로드 가능한 SQL Window Functions Cheat Sheet를 확인해 보세요. 다양한 윈도 함수 유형을 한눈에 참고할 수 있습니다. 또한 T-SQL을 데이터 분석에 활용하는 법과 윈도 함수를 통한 요약까지 다루는 Intermediate SQL Server 코스도 살펴보세요.

공통 테이블 식(CTE)

CTE(Common Table Expressions)는 쿼리 내에서 재사용할 수 있는 임시 결과 집합을 생성합니다. 아래 쿼리는 미국 고객의 임시 목록을 만든 후 이를 조회합니다.

-- Define a CTE
WITH CustomerCTE AS (
    SELECT FirstName, Country
    FROM Customers
    WHERE Country = 'USA' -- Filter only USA customers
)

-- Query the CTE
SELECT *
FROM CustomerCTE;

한편, 재귀 CTE는 정의 안에서 자기 자신을 참조하는 특별한 유형의 CTE로, 반복 작업을 수행할 수 있습니다. 조직도 같은 계층형 데이터를 다룰 때 이상적입니다.

CTE와 재귀 CTE를 포함해 SQL Server에서 고급 쿼리를 작성하는 방법을 배우려면 Hierarchical and Recursive Queries in SQL Server 코스를 추천합니다.

임시 테이블

임시 테이블은 세션 중 단기간 사용할 데이터를 저장합니다. 데이터 적재 단계, 다단계 변환 단순화, 복잡한 쿼리의 성능 개선에 유용합니다.

t-SQL에서 임시 테이블이 작동하는 방식을 보여주기 위해 아래 세 개의 쿼리 블록을 보세요. 첫 번째는 임시 테이블을 만들고, 그다음 고객 데이터를 채워 넣은 뒤, 같은 세션에서 이를 조회합니다.

-- Create a temporary table
CREATE TABLE #TempCustomers (
    FirstName VARCHAR(50),
    Country VARCHAR(50)
);

-- Insert data into the temporary table
INSERT INTO #TempCustomers
SELECT FirstName, Country
FROM Customers;

-- Retrieve data from the temporary table
SELECT * FROM #TempCustomers;

임시 테이블은 # 접두사가 붙으며, 세션이 종료되면 자동으로 삭제된다는 점을 유의하세요.

T-SQL과 다른 SQL 방언 비교

모든 SQL 방언은 같은 기반을 공유하지만, 각 데이터베이스 시스템은 자체 문법과 기능을 추가합니다. T-SQL은 Microsoft SQL Server에 맞게 설계되어 동작 방식과 최적 사용처에 영향을 줍니다.

T-SQL vs. PostgreSQL(PL/pgSQL)

PostgreSQL 은 T-SQL과 유사하지만 문법과 강점이 다른 PL/pgSQL을 사용합니다. 아래 표는 이러한 차이를 요약합니다.

데이터베이스 성능 향상을 위해 관계형 데이터베이스를 설계하고 쿼리 구조를 잡는 방법을 배우려면 SQL for Database Administrators 스킬 트랙을 확인해 보세요.

T-SQL vs. MySQL

MySQL 역시 절차적 SQL을 지원하지만, 보다 단순하고 제한적인 접근법을 사용합니다. 아래 표는 두 방언의 문법과 기능 차이를 비교합니다.

T-SQL의 실제 활용 사례

T-SQL은 단순한 데이터 조회를 넘어 폭넓게 사용됩니다. 데이터 전문가로서 특히 유용했던 사례는 다음과 같습니다.

리포팅 쿼리

T-SQL은 데이터베이스에서 직접 리포트를 생성할 때 흔히 사용됩니다. 예를 들어, 지역별 매출을 집계하거나 윈도 함수를 사용해 최고 실적 제품을 순위 매길 수 있습니다.

ETL 프로세스(Extract, Transform, Load)

데이터 엔지니어는 파이프라인에서 T-SQL을 사용해 데이터를 준비하고 이동시킵니다. 예를 들어, 스테이징 테이블에서 데이터를 추출하고, 형식을 변환하며, 불일치를 정제한 뒤, 최종 리포팅 테이블에 적재할 수 있습니다.

데이터 정제 스크립트

수백만 행의 주소 형식이 제각각인 데이터베이스가 있다면, WHILE 루프나 CTE를 활용한 T-SQL 스크립트로 중복을 식별하고 누락 값을 채우거나 형식을 표준화할 수 있습니다.

애플리케이션의 백엔드 로직

많은 애플리케이션이 데이터베이스 내부에서 비즈니스 로직을 처리하기 위해 T-SQL에 의존합니다. CRUD 작업에는 저장 프로시저를, 데이터 삽입 전 검증에는 조건 검사를, 중요 작업에는 트랜잭션 처리를 사용할 수 있습니다.

자주 하는 실수와 모범 사례

T-SQL은 고급 데이터베이스 쿼리에 유용하지만, 몇 가지 흔한 문제에 부딪히기 쉽습니다. 다음은 T-SQL을 사용할 때 자주 접하는 실수입니다.

• 루프 과사용(집합 기반 로직 대신): WHILE 등으로 행 단위 작업을 수행하면 성능이 저하될 수 있습니다. SQL은 한 번에 데이터 집합을 처리하도록 설계되었습니다.

• 인덱스에 대한 이해 부족: 대형 테이블에서 인덱스를 적절히 사용하지 않으면 쿼리가 느려집니다.

• 트랜잭션의 부적절한 사용: 관련된 여러 작업을 트랜잭션으로 묶지 않으면 중간에 실패했을 때 데이터가 불일치 상태로 남을 수 있습니다.

• 오류 처리 무시: TRY...CATCH 블록을 생략하면 디버깅이 어려워지고 운영 환경에서 스크립트가 조용히 실패하거나 예측 불가능하게 동작할 수 있습니다.

아래는 더 효율적이고 신뢰할 수 있는 쿼리를 작성하는 데 도움이 되는 모범 사례입니다.

• 집합 기반 쿼리를 우선시: 가능하면 루프 대신 JOIN, GROUP BY, 윈도 함수를 사용하세요. 이는 SQL Server의 효율적 데이터 처리 방식과 부합합니다.

• 가독성과 유지보수성 향상: 명확한 명명 규칙을 적용하고 쿼리 서식을 일관되게 유지하며 필요 시 주석을 추가하세요. 코드가 읽기 쉬워지고 디버깅과 협업이 수월해집니다.

• 프로시저 테스트: 저장 프로시저와 쿼리는 다양한 입력으로 테스트해 정상 동작과 엣지 케이스 처리를 확인하세요.

결론

T-SQL은 표준 SQL에 프로그래밍 기능을 더해 자동화, 데이터 처리, 분석에 유연하고 강력한 도구가 됩니다. 변수, 저장 프로시저, 오류 처리 같은 기능으로 더 동적이고 효율적인 데이터베이스 솔루션을 구축할 수 있습니다.

다음 단계로, 숙련된 데이터 분석가가 되기 위한 Associate Data Analyst in SQL 커리어 트랙을 추천합니다. 또한 Reporting in SQL 코스는 복잡한 리포트를 구축하는 역량을 높이는 데 도움이 됩니다. 마지막으로, SQL Associate Certification을 취득해 SQL로 비즈니스 문제를 해결하는 역량을 증명하고 전문가들 사이에서 돋보이세요.