안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 5-3. 테이블 생성을 진행 하겠습니다.

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

이번에 진행할 강좌는 테이블 생성입니다.

TL;DR

SSMS와 SQL 구문으로 테이블을 생성합니다, 테이블 생성 구문은 CREATE TABLE로 진행하며, 개체 명명 규칙, IDENTITY와 NULL 등 테이블 생성시 주의사항을 확인하세요.
 

이미 SSMS에서 SQL 쿼리를 이용해 생성해 보셨어요. 이번 강좌부터는 테이블을 생성할 때 다양한 제약(Constraint) 활용에 초점을 맞춰 진행합니다. 

SSMS에서 GUI로 생성하는 과정은 짧게 보여 드리고, 이후 SQL 쿼리로 테이블을 생성하는 예제를 주로 소개해 드리겠습니다. SSMS에서 테이블을 생성할 수는 있지만 권장하지는 않습니다. 항상 SQL 쿼리 코드로 만들고 코드를 잘 보관하는 습관을 기르시는 게 좋습니다.

SSMS에서 GUI로 테이블 생성

SSMS에서 생성하겠습니다.

이미지 - 테이블 생성 시작

샘플 데이터베이스인 AdventureWorks 데이터베이스에서 테이블을 생성합니다. 데이터베이스 선택 - 테이블 - 새로 만들기 - 테이블 수행합니다.

이미지 - 테이블 디자인

이런 화면에서 위와 같이 두 개의 컬럼을 추가합니다.

sqler_id 컬럼은 int(숫자) 형으로, contents라는 컬럼은 nvarchar(10) 컬럼으로 잡습니다. 

테이블 디자인 화면입니다. 개별 컬럼의 필수값인 이름과 데이터형을 선언하는 윗부분과 아래쪽 “열 속성”에서 추가 속성을 세팅할 수 있도록 구성됩니다. 

개체명 식별

컬럼명은 SQL Server가 사용하는 키워드(예약어-Reserved word)를 사용하지 않고 처리하시는 것이 좋습니다. 또한, 컬럼명을 잘 선택해 어떤 데이터인지 잘 표현하는 것이 좋습니다.

SQL Server의 예약어: Reserved Keywords (Transact-SQL) - SQL Server | Microsoft Learn 

예를 들어, 컬럼명을 “user”라고 잡았다면, SSMS에서는 잘 생성되지만, 실제 쿼리에서 SQL 예약어와 중복되기 때문에, 테이블명을 명시하는 다른 방식을 사용해야 합니다. “[  ]”(대괄호 - Square bracket)로 묶어 주면 됩니다.

-- 개체가 SQL Server 예약어 또는 공백이 포함될 경우 대괄호 사용 
SELECT * FROM [user];

이러한 식으로 묶어 주면 됩니다.

개체명인 컬럼명과 테이블명에 공백문자 또는 특수문자가 사용되었을 경우에도 [ ]로 묶으면 사용 가능합니다.

다른 DBMS로부터 데이터를 복사해 오거나, CSV 파일을 로드해 SQL Server에서 사용할 경우 예약어 이슈가 종종 발생합니다. CSV 등에서는 당연히 user와 같은 테이블명이나 컬럼명이 사용 가능합니다. 애플리케이션에서 잘 사용하다가, SQL Server로 마이그레이션 하고 특정 쿼리가 이상하다면, SQL Server의 예약어를 먼저 체크해 보세요.

☑️ 챗GPT 활용: CSV 파일에 대해서 알려줘

NULL 허용

NULL 허용으로 NULL값을 입력받을지 여부를 결정합니다. NULL / NOT NULL 설정은 제약으로, 체크가 되어 있으면 NULL값을 받습니다.

아래의 기본값(Default)을 이용해 가능한 NULL 값을 받지 않는 게 좋습니다. NULL 값 비교는 일반 데이터형과 다르게 비교합니다. WHERE 절에서 “IS NULL”로 비교하거나,  ISNULL() 함수, COALESCE() 함수를 따로 사용하여 복잡도가 높고, 동적 SQL 구문 적용이 어려워집니다. 테이블 디자인 시 항상 NOT NULL로 구성하고 특수한 케이스가 아니라면 가급적 NULL을 허용하지 마세요.

이미지 - 컬럼 속성

SSMS의 테이블 디자인에서 속성 부분을 살펴보겠습니다.

기본값(Default)

기본값 제약을 컬럼에 추가합니다. NULL로 들어올 경우, 기본값이 실행되며 값을 채우게 됩니다. 예를 들어 GETDATE() 같은 현재 시간을 가져오는 함수를 이용하면 NULL 값 대신 현재 시간이 기본값으로 입력됩니다.

ID 사양

3-1. 데이터 삽입, 삭제, 수정 - INSERT를 이용한 행 삽입 강좌에서 Identity에 대해서 소개해 드렸습니다. 이 Identity를 사용할 것인지 여부를 결정합니다. ID 시드는 Identity의 초기값이고, ID 증가값은 step 값으로 증가되는 수입니다.

RowGuid

NEWID() 함수로 중복되지 않는 16 Byte UUID를 생성합니다. SQL Server의 uniqueidentifier 데이터형과 호환됩니다.

계산열 사양 - 수식

컬럼 값을 계산된 값으로 채웁니다. 예를 들어, 다음과 같은 월급 데이터를 생각해 보세요.

일 경우입니다. 

받는 돈을 자동으로 계산해 입력 가능할까요? 가능합니다. 계산 열(Computed column)이라고 부르며, 아래처럼 설정합니다.

USE AdventureWorks;
GO

CREATE TABLE emp_pay(
    emp_name NVARCHAR(6), 
    salary INT, 
    tax INT,
    actual_wage AS (salary-tax)  -- 계산열 처리
);
GO

--데이터를 삽입한다.
INSERT emp_pay VALUES (N'김대우', 100, 10);
INSERT emp_pay VALUES (N'강하늘', 200, 20);
INSERT emp_pay VALUES (N'김우빈', 300, 50);

--데이터를 조회한다.
SELECT * FROM emp_pay;
GO

위의 예제는 간단하지만, 복잡한 수치연산 계산식도 컬럼에 추가할 수 있습니다.

데이터 정렬(Collation)

언어 정렬입니다. Collation을 SQL서버, 개별 데이터베이스, 테이블의 컬럼 또는 ORDER BY 구문에서 사용할 수 있습니다. 2-7. ORDER BY 결과 정렬 강좌에서 소개해 드린 내용이니 이곳을 참고하세요.

-- 영어 한국어 일본어 사전 - 테이블 생성 시 Collation 설정
CREATE TABLE DictBook (
Eng_word nvarchar(10),
Korean_word nvarchar(20) COLLATE Korean_Wansung_CS_AS, 
Japanese_word nvarchar(20) COLLATE Japanese_CI_AS
);
GO

--영어, 한글, 일어 데이터를 삽입한다.
INSERT INTO DictBook VALUES(N'Fool', N'바보', N'あほう');
INSERT INTO DictBook VALUES(N'Computer', N'컴퓨터', N'コンピュ-タ-');

--데이터를 조회해 보자. 
--이때 ORDER BY에서도 언어셋을 지정해 순서를 지정 가능하다.
SELECT * FROM DictBook
ORDER BY Korean_word ASC;
GO

SELECT * FROM DictBook
ORDER BY Japanese_word COLLATE Japanese_CI_AS DESC;
GO

맨 아래 부분의 ORDER BY를 일본어 컬럼에 대해서 진행하는 예제입니다. 정렬방식을 COLLATE Japanese_CI_AS DESC로 일본어에 대해 내림차순 정렬을 진행합니다. 지정하지 않으면 테이블의 컬럼, 없으면 데이터베이스 기본 정렬방식을 따르게 됩니다.

스파스(SPARSE)

스파스 컬럼으로 지정하면 NULL인 경우 값을 스토리지에 저장하지 않습니다. 하지만, 스파스 컬럼으로 설정하면 NULL이 아닌 값을 검색할 경우 성능 저하가 있습니다. 예를 들어, 주문 테이블의 “주문반품일”  컬럼은 99% NULL 값일 것입니다. 즉, NULL 값 비율이 매우 높은 컬럼으로 예상되고, 디스크 공간 비용이 아주 비싸다면, 스파스 설정을 고려할 수 있습니다.

데이터 압축

테이블의 로우나 페이지(Page - SQL Server의 데이터 저장 단위)를 압축할 수 있습니다. 압축해 저장하면 저장되는 데이터가 줄어 IO 성능이 향상될 수 있지만, 압축/해제에 CPU 사용량이 2배 가까이 증가할 수 있습니다.

전체텍스트(Full Text)

전체텍스트는 문자열 데이터에 대해 전체 텍스트 쿼리를 수행해 빠른 검색 기능을 제공합니다. 이후 강좌에서 진행 예정입니다.

Create Table 구문 정보

많이 길고 복잡해 보이지만 차근차근 예제로 진행합니다. 계속 강조하지만, 가장 중요한 건 맨 위의 한 블록입니다. 나머지는 제약(Constraint) 등을 정의하는 부분입니다. 

데이터베이스 개체 명명 규칙(naming convention)

뜬금없지만, 명명 규칙에 대해 짧게 진행합니다. 이런 얘기 들어 보셨을 겁니다. “개체명이나 변수명 짓기도 프로그래밍이다.” 개체명을 잘 지으면 동료들과 함께 개발하기도 편하고, 코드가 술술 읽힙니다. 그렇다면, SQL Server의 명명 규칙은 어떤 방식이 있을까요?

공식적인 가이드는 없으나, AdventureWorks 데이터베이스의 이름규칙 가이드가 많이 사용됩니다.
- sqlserver-kit/SQL Server Name Convention and T-SQL Programming Style.md at master

예를 들어, 아래와 같은 명명규칙을 고려할 수 있습니다. 

개발팀이 소속되어 있다면, 여러 개발 관련 문서가 있을 것이고, 네이밍 규칙도 있을 겁니다. 그런 규칙을 우선 따르시는 게 좋습니다. 그리고 제발!  테이블명을 TBL1, TBL2  이렇게 생성하지 마시길 바랍니다. 아울러 컬럼명도 테스트 용도가 아니면 C1, C2 이렇게 짓지 마시고요. 개체 명명 규칙은 이 정도로 정리하겠습니다.

다양한 테이블 생성

IDENTITY, 문자열 길이 제한, NOT NULL 테이블

-- 만약 테이블이 존재하면 삭제
IF OBJECT_ID(N'dbo.members_test3', N'U') IS NOT NULL  
   DROP TABLE dbo.members_test3;  
GO

-- IDENTITY, DEFAULT, NOT NULL 컬럼 테스트 테이블
CREATE TABLE members_test3 (
  member_num INT PRIMARY KEY IDENTITY(1, 1),
  member_name NVARCHAR(10),
  email NVARCHAR(15) NOT NULL,
  register_date datetime DEFAULT GETDATE()
);
GO

-- Identity 컬럼에 INSERT - 에러
INSERT INTO members_test3(member_num, member_name, email, register_date)
VALUES(1, N'김대우', N'kdw@example.com', '2023-07-09');
GO

메시지 544, 수준 16, 상태 1, 줄 457
IDENTITY_INSERT가 OFF로 설정되면 테이블 'members_test3'의 ID 열에 명시적 값을 삽입할 수 없습니다.

-- IDENTITY 컬럼을 제외하고 INSERT 수행 - 정상
INSERT INTO members_test3(member_name, email, register_date)
VALUES(N'김대우', N'kdw@example.com', '2023-07-09');
GO

-- 긴 문자열을 삽입 시도
INSERT INTO members_test3(member_name, email, register_date)
VALUES(N'아주아주긴이름을가진사람', N'big@example.com', '2023-07-17');
GO

메시지 2628, 수준 16, 상태 1, 줄 31
테이블 'AdventureWorks.dbo.members_test3'의 열 'member_name'에 있는 문자열 또는 이진 데이터는 잘립니다. 잘린 값: '아주아주긴이름을가진'.
문이 종료되었습니다.

-- NOT NULL로 설정된 email 컬럼에 NULL 입력 시도
INSERT INTO members_test3(member_name, email, register_date)
VALUES(N'김태리', NULL, '2023-07-17');
GO

메시지 515, 수준 16, 상태 2, 줄 40
테이블 'AdventureWorks.dbo.members_test3', 열 'email'에 NULL 값을 삽입할 수 없습니다. 열에는 NULL을 사용할 수 없습니다. INSERT이(가) 실패했습니다.
문이 종료되었습니다.

-- email에 빈 문자열 삽입 - 정상
INSERT INTO members_test3(member_name, email, register_date)
VALUES(N'김태리', '', '2023-07-17');
GO

SELECT * FROM members_test3;
GO

항상 오류 메시지를 잘 살펴보세요. 오류에 익숙해지면 개발할 때 많은 도움이 됩니다. 

IDENTITY 오류

IDENTITY는 명시적 입력을 기본적으로 허용하지 않습니다. IDENTITY_INSERT ON을 설정하면 INSERT가 가능하지만, Seed 숫자가 변경될 수 있기 때문에 권장하지 않습니다.

문자열 잘림

테이블에 설정된 문자열 컬럼의 길이보다 큰 데이터를 삽입할 때 이렇게 문자열 잘림 오류가 발생합니다. 테이블 디자인에서 컬럼의 길이를 늘리거나 문자열의 길이를 줄여야 합니다.

NULL 값 삽입 오류

NULL을 허용하지 않도록 디자인된 컬럼에 NULL을 입력하면, 이렇게 NULL 값 입력 오류가 발생합니다. 잊지 마세요, 빈 문자열과 NULL은 다릅니다.

SQL 구문으로 테이블에 대한 정보 확인

sp_help 구문을 이용합니다. 이렇게 실행하면 테이블에 대한 정보와 생성일, IDENTITY 컬럼, PRIMARY KEY, 색인(Index)이나 제약(Constraints) 정보를 한눈에 볼 수 있습니다.

sp_help members_test3;

이렇게 sp_help 뒤에 테이블명을 적으면 됩니다.

테이블 생성 스크립트 자동 작성

종종, 존재하는 테이블을 SQL 스크립트로 작성해야 할 경우가 있습니다. 다른 개발용 SQL Server에서 작업해야 하거나, 클라우드에서 테스트할 경우에도 같은 구조의 테이블을 생성하기 위해 필요할 수 있지요. CREATE TABLE 구문으로 작성해야 하는데 컬럼이나 제약 등이 많으면 쉽지 않은 작업입니다. 이때, 자동으로 생성 스크립트를 작성 가능합니다.

이미지 - 테이블 생성 스크립팅

만들어진 테이블 생성 스크립트를 참고하세요. 테이블을 DROP 명령으로 삭제하고 다시 실행해도 잘 생성됩니다.
 

SQL 강좌 책 구매

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

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