MSSQL 테이블 생성 Query

1. 테이블

(1) 테이블 생성

CREATE TABLE [스키마명].[테이블명] ( [컬럼명1] [bigint] NOT NULL IDENTITY(1, 1) PRIMARY KEY, -- 선언할 때 같이 추가(기본키, 제약조건명 시스템이 자동 생성) [컬럼명2] [varchar](32) NOT NULL, [컬럼명3] [varchar](32) NOT NULL UNIQUE, -- 선언할 때 같이 추가(유니크, 제약조건명 시스템이 자동 생성) [컬럼명4] [int] NOT NULL DEFAULT 0, -- DEFAULT 값 설정(제약조건명 시스템이 자동 생성) [컬럼명5] [varchar](500) NOT NULL, [컬럼명6] [bigint] NOT NULL FOREIGN KEY REFERENCES [테이블명] ([컬럼명1]), -- 선언할 때 같이 추가(외래키, 제약조건명 시스템이 자동 생성) [컬럼명7] [int] NOT NULL FOREIGN KEY REFERENCES [부모테이블명] ([PK컬럼명]), -- 선언할 때 같이 추가(외래키, 제약조건명 시스템이 자동 생성) [컬럼명8] TINYINT NOT NULL CHECK ([컬럼명7] >= 1 AND [컬럼명7] <= 10), -- 선언할 때 같이 추가(체크제약조건, 제약조건명 시스템이 자동 생성) [컬럼명9] DATETIME NOT NULL CONSTRAINT [제약조건명] DEFAULT getdate(), -- DEFAULT 값 설정(제약조건명 설정 가능) CONSTRAINT [제약조건명] PRIMARY KEY ([컬럼명1]), -- 뒤에 따로 추가(기본키, 제약조건명 설정 가능) CONSTRAINT [제약조건명] UNIQUE ([컬럼명3]), -- 뒤에 따로 추가(유니크, 제약조건명 설정 가능) INDEX [제약조건명] NONCLUSTERED COLUMNSTORE ([컬럼명5]), -- 뒤에 추가(Columnstore 인덱스, 제약조건명 설정 가능) INDEX [제약조건명] NONCLUSTERED ([컬럼명6]), -- 뒤에 추가(인덱스, 제약조건명 설정 가능) INDEX [제약조건명] NONCLUSTERED ([컬럼명7]), -- 뒤에 추가(인덱스, 제약조건명 설정 가능) CONSTRAINT [제약조건명] CHECK ([컬럼명8] >= 1 AND [컬럼명8] <= 10), -- 뒤에 따로 추가(체크제약조건, 제약조건명 설정 가능) CONSTRAINT [제약조건명] FOREIGN KEY ([컬럼명6]) REFERENCES [테이블명] ([컬럼명1]), -- 뒤에 추가(자기 자신 외래키 참조) CONSTRAINT [제약조건명] FOREIGN KEY ([컬럼명7]) REFERENCES [부모테이블명] ([PK컬럼명]) -- 뒤에 추가(다른테이블 외래키 참조) )

① PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY 선언 위치는 대상 컬럼 선언하는 곳에 같이 추가할 수도 있고, 모든 컬럼 정의 마지막에 따로 추가할 수도 있음

- 대상 컬럼 선언하는 곳에 같이 추가할 경우 제약 조건명을 명시하지 않고 추가했기 때문에 시스템이 제약 조건명을 자동 할당

- 모든 컬럼 정의 마지막에 따로 추가할 경우 제약 조건명을 명시했기 때문에 명시한 해당 제약 조건명으로 설정할 수 있음

- 만약 대상 컬럼이 1개가 아니라, 2개 이상인 경우 모든 컬럼 정의 마지막에 따로 정의해서 추가해야 함

​

② INDEX(CLUSTERED, NONCLUSTERED)는 모든 컬럼 정의 마지막에 따로 추가해야 함

- 대상 컬럼이 1개인 경우에도 무조건 모든 컬럼 정의 마지막에 따로 추가해야 함

​

③ 제약 조건명을 명시하지 않고 추가할 경우 시스템이 제약 조건명을 자동 할당하게 되는데, 이 경우보다는 관리에 효율성을 위해 제약 조건명을 명시하는 편이 좋음.

​

④ 시스템이 제약 조건명을 자동 할당했는지 아니면 생성시 설정한 제약 조건명으로 설정했는지 아래 쿼리로 확인할 수 있음

- is_system_named 컬럼 값이 1이면 시스템 자동 할당, 0이면 설정한 제약 조건명

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, OBJECT_NAME(parent_object_id) AS table_name, [name], is_system_named FROM sys.key_constraints WHERE [name] = '제약조건명'

(2) 테이블 제약 조건

• NOT NULL : 해당 컬럼은 NULL 값을 저장할 수 없게 됩니다.
• UNIQUE : 해당 컬럼은 서로 다른 값을 가져야만 합니다.
• PRIMARY KEY : 해당 컬럼은 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됩니다.
• FOREIGN KEY : 하나의 테이블을 다른 테이블에 의존하게 만듭니다.
• DEFAULT : 해당 컬럼의 기본값을 설정합니다.
• CHECK : 컬럼 또는 컬럼들에 들어갈 수 있는 값을 제한합니다.

​

자세한 사항은 '[MSSQL] 제약조건 관련 쿼리' 포스트 참조

# Bbs DB 생성 CREATE DATABASE [Bbs] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Bbs', FILENAME = N'E:\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA\Bbs.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'Bbs_log', FILENAME = N'E:\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA\Bbs_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) WITH LEDGER = OFF GO

CREATE TABLE [dbo].[AuthGroup] ( [No] [int] NOT NULL IDENTITY(1, 1), [AgName] [varchar](30) NOT NULL, [AgDesc] [varchar](100) DEFAULT NULL, [InsertDate] [datetime] NOT NULL DEFAULT getdate(), [UpdateDate] [datetime] NOT NULL DEFAULT getdate(), CONSTRAINT [pk_AuthGroup_No] PRIMARY KEY CLUSTERED ( [No] ASC ) )

CREATE TABLE [dbo].[Member] ( [MbIdx] [bigint] NOT NULL IDENTITY(1, 1), [Name] [varchar](32) NOT NULL, [NickName] [varchar](32) NOT NULL, [Age] [int] NOT NULL CONSTRAINT [df_Member_Age] DEFAULT 0, [Gender] [bit] NOT NULL CONSTRAINT [df_Member_Gender] DEFAULT 0, [Introduction] [varchar](500) NULL, [RefMbIdx] [bigint] NOT NULL, [AgNo] [int] NOT NULL, [State] [tinyint] NOT NULL CONSTRAINT [df_Member_State] DEFAULT 1, [RegDate] [datetime] NOT NULL CONSTRAINT [df_Member_RegDate] DEFAULT getdate(), CONSTRAINT [pk_Member_MbIdx] PRIMARY KEY CLUSTERED ( [MbIdx] ASC ), CONSTRAINT [uq_Member_NickName] UNIQUE NONCLUSTERED ( [NickName] ASC ), INDEX [ix_Member_RefMbIdx] NONCLUSTERED ( [RefMbIdx] ASC ), INDEX [ix_Member_AgNo] NONCLUSTERED ( [AgNo] ASC ), INDEX [ix_Member_Introduction] NONCLUSTERED COLUMNSTORE ( [Introduction] ), CONSTRAINT [ck_Member_State] CHECK ([State] >= 1 AND [State] <= 10), CONSTRAINT [fk_Member_RefMbIdx] FOREIGN KEY ([RefMbIdx]) REFERENCES [dbo].[Member] ([MbIdx]), CONSTRAINT [fk_Member_AgNo] FOREIGN KEY ([AgNo]) REFERENCES [dbo].[AuthGroup] ([No]) )

CREATE TABLE [dbo].[Bbs] ( [No] [int] IDENTITY(1,1) NOT NULL, [Subject] [varchar](100) NOT NULL, [Content] [varchar](max) NOT NULL, [MbIdx] [bigint] NOT NULL, [Name] [varchar](20) NOT NULL, [IP] [varchar](20) NULL, [HtmlFlag] [bit] NOT NULL CONSTRAINT [df_Bbs_HtmlFlag] DEFAULT 0, [Hit] [int] NOT NULL CONSTRAINT [df_Bbs_Hit] DEFAULT 0, [Recom] [int] NOT NULL CONSTRAINT [df_Bbs_Recom] DEFAULT 0, [UnRecom] [int] NOT NULL CONSTRAINT [df_Bbs_UnRecom] DEFAULT 0, [CommentCnt] [int] NOT NULL CONSTRAINT [df_Bbs_CommentCnt] DEFAULT 0, [RegDate] [datetime] NOT NULL CONSTRAINT [df_Bbs_RegDate] DEFAULT getdate(), CONSTRAINT [pk_Bbs_No] PRIMARY KEY CLUSTERED ( [No] DESC ), INDEX [ix_Bbs_Subject] NONCLUSTERED ( [RegDate] DESC, [Subject] ASC ), INDEX [ix_Bbs_Name] NONCLUSTERED ( [RegDate] DESC, [Name] ASC ) )

CREATE TABLE [dbo].[Bbs_AttgenFile] ( [No] [int] IDENTITY(1,1) NOT NULL, [BbsNo] [int] NOT NULL, [FilePath] [varchar](260) DEFAULT NULL, [FileName] [varchar](260) DEFAULT NULL, [FileSize] [int] NOT NULL CONSTRAINT [df_Bbs_AttgenFile_FileSize] DEFAULT 0, [FileExt] [varchar](10) DEFAULT NULL, [RegDate] [datetime] NOT NULL CONSTRAINT [df_Bbs_AttgenFile_RegDate] DEFAULT getdate(), CONSTRAINT [pk_Bbs_AttgenFile_No] PRIMARY KEY CLUSTERED ( [No] DESC ), INDEX [ix_Bbs_AttgenFile_BbsNo] NONCLUSTERED ( [BbsNo] ASC ), CONSTRAINT [fk_Bbs_AttgenFile_BbsNo] FOREIGN KEY ([BbsNo]) REFERENCES [Bbs] ([No]) )

(3) 테이블 조회

SELECT DB_NAME() AS db_name, t.object_id AS object_id, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, ISNULL((SELECT CAST(ISNULL(last_value, 0) AS BIGINT) FROM sys.identity_columns WHERE object_id = t.object_id AND last_value > 0), 0) AS auto_increment, CAST(ep.[value] AS NVARCHAR(4000)) AS table_comment, CONVERT(VARCHAR(23), create_date, 121) AS create_date, CONVERT(VARCHAR(23), modify_date, 121) AS modify_date FROM sys.tables AS t LEFT OUTER JOIN sys.extended_properties AS ep ON t.object_id = ep.major_id AND ep.minor_id = 0 AND ep.name = 'MS_Description' WHERE t.type = 'U' ORDER BY t.name ASC;

(4) 특정 테이블 컬럼 조회

SELECT DB_NAME() AS db_name, OBJECT_SCHEMA_NAME(col.object_id) AS schema_name, col.table_name AS table_name, col.column_id AS seq, col.name AS column_name, (UPPER(TYPE_NAME(col.user_type_id)) + (CASE WHEN TYPE_NAME(col.user_type_id) = 'varchar' OR TYPE_NAME(col.user_type_id) = 'char' THEN '(' + (CASE WHEN col.max_length = -1 THEN 'MAX' ELSE CAST(col.max_length AS VARCHAR) END) + ')' WHEN TYPE_NAME(col.user_type_id) = 'nvarchar' OR TYPE_NAME(col.user_type_id) = 'nchar' THEN '(' + (CASE WHEN col.max_length = -1 THEN 'MAX' ELSE CAST(col.max_length / 2 AS VARCHAR) END) + ')' WHEN TYPE_NAME(col.user_type_id) = 'decimal' THEN '(' + CAST(col.precision AS VARCHAR) + ',' + CAST(col.scale AS VARCHAR) + ')' ELSE '' END)) AS datatype_desc, col.is_nullable, col.is_identity, CAST(ISNULL(ic.seed_value, 0) AS BIGINT) AS seed_value, CAST(ISNULL(ic.increment_value, 0) AS BIGINT) AS inc_value, dc.name AS default_constraintname, dc.definition AS default_definition, col.collation_name AS collation, CAST(ep.[value] AS NVARCHAR(4000)) AS column_comment FROM ( SELECT a.*, b.name AS table_name FROM sys.columns AS a INNER JOIN sys.tables AS b ON a.object_id = b.object_id WHERE b.type = 'U' AND b.name = '테이블명' ) AS col LEFT OUTER JOIN sys.identity_columns AS ic ON col.object_id = ic.object_id AND col.column_id = ic.column_id LEFT OUTER JOIN sys.default_constraints AS dc ON col.default_object_id = dc.object_id LEFT OUTER JOIN sys.extended_properties AS ep ON col.object_id = ep.major_id AND col.column_id = ep.minor_id AND ep.class = 1 AND ep.name = 'MS_Description' ORDER BY col.table_name ASC, col.column_id ASC;

(5) 테이블명 변경

EXEC sp_rename [테이블명], [변경할 테이블명]

(6) 테이블 삭제

DROP TABLE [IF EXISTS] [테이블명]

2. 테이블 컬럼

Info

<컬럼 속성>

- COLLATE, <NULL|NOT NULL>, IDENTITY(시드값, 증분값) 설정이 포함됨

- 만약 컬럼 속성에 포함된 설정 값을 변경할 경우 아래와 같은 순서로 나열해서 변경하면 됨

- 만약 데이터정렬 값이 해당 데이터베이스에 설정된 데이터정렬 값과 동일한 경우 따로 명시하지 않아도 됨​

COLLATE 데이터정렬 <NULL|NOT NULL> IDENTITY(시드값, 증분값)

(1) 컬럼 추가

① 새로운 컬럼을 추가하고, 그 컬럼에 identity 추가

- 기존 컬럼을 IDENTITY열로 수정하는 것은 안됨(테이블을 삭제하고, 다시 만들어야 함)

​

② NULL 허용.비허용

ALTER TABLE [테이블명] ADD [컬럼명] 데이터타입 <NULL|NOT NULL>

③ DEFAULT 설정

- 신규로 컬럼을 추가할 때 DEFAULT 값을 함께 설정할 수 있지만, 이 방법으로 추가된 DEFAULT 제약조건명은 시스템에서 임의로 생성하므로 관리가 되지 않음.

- 신규 컬럼을 추가한 후에 DEFAULT 제약조건명을 직접 지정하여 추가하는 것이 관리 차원에서 좋음

-- 컬럼 추가 ALTER TABLE [테이블명] ADD [컬럼명] 데이터타입 <NULL|NOT NULL> -- DEFAULT 설정 ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] DEFAULT 값 FOR [컬럼명]

④ 데이터정렬 설정

ALTER TABLE [테이블명] ADD [컬럼명] 데이터타입 COLLATE 데이터정렬 <NULL|NOT NULL>

(2) 컬럼 변경

① 컬럼명 변경

EXEC sp_rename [테이블명.컬럼명], [변경할 컬럼명], 'COLUMN'

② 컬럼 데이터타입 변경

ALTER TABLE [테이블명] ALTER COLUMN [컬럼명] 변경할데이터타입

③ NOT NULL 설정 또는 제거

ALTER TABLE [테이블명] ALTER COLUMN [컬럼명] 데이터타입 <NULL|NOT NULL>

④ 컬럼 Collation(데이터정렬) 변경

- Collation을 변경할 컬럼이 인덱스가 설정되어 있으면, 해당 컬럼은 Collation을 변경할 수 없으므로, Collation을 변경하기 위해서는 인덱스 제거 -> Collation을 변경 -> 인덱스 재설정의 순서로 Collation을 변경해야 함

ALTER TABLE [테이블명] ALTER COLUMN [컬럼명] 데이터타입 COLLATE 변경할데이터정렬 <NULL|NOT NULL>

⑤ 컬럼 DEFAULT 값 변경

- 기존 default 값을 변경하고자 한다면, 기존 default 제약 조건을 삭제하고, 새로운 제약 조건을 추가

-- DEFAULT 삭제 ALTER TABLE [테이블명] DROP CONSTRAINT [제약조건명] -- DEFAULT 설정 ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] DEFAULT 값 FOR [컬럼명]

(3) 컬럼 삭제

ALTER TABLE [테이블명] DROP COLUMN [컬럼명]

3. 인덱스

(1) 추가

① PRIMARY KEY, UNIQUE

-- PRIMARY KEY, UNIQUE ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] <PRIMARY KEY|UNIQUE> <CLUSTERED|NONCLUSTERED> ([컬럼명] [정렬방식])

② 클러스터 인덱스(중복 허용)

CREATE CLUSTERED INDEX [인덱스명] ON [테이블명] ([컬럼명] [정렬방식])

③ 클러스터 인덱스(중복 불가)

CREATE UNIQUE CLUSTERED INDEX [인덱스명] ON [테이블명] ([컬럼명] [정렬방식])

④ 넌 클러스터 인덱스(중복 허용)

CREATE NONCLUSTERED INDEX [인덱스명] ON [테이블명] ([컬럼명] [정렬방식])

⑤ 넌 클러스터 인덱스(중복 불가)

CREATE UNIQUE NONCLUSTERED INDEX [인덱스명] ON [테이블명] ([컬럼명] [정렬방식])

(2) 삭제

① PRIMARY KEY, UNIQUE

ALTER TABLE [테이블명] DROP CONSTRAINT [제약조건명]

② 인덱스

DROP INDEX [인덱스명] ON [테이블명]

(3) 수정

- 기존 인덱스를 변경하고자 한다면, 기존 인덱스를 삭제하고, 새로운 인덱스를 추가

① PRIMARY KEY, UNIQUE

-- 인덱스 삭제 ALTER TABLE [테이블명] DROP CONSTRAINT [제약조건명] -- 인덱스 추가 ALTER TABLE [테이블명] ADD CONSTRAINT [제약조건명] <PRIMARY KEY|UNIQUE> <CLUSTERED|NONCLUSTERED> ([컬럼명] [정렬방식])

② 인덱스

-- 인덱스 삭제 DROP INDEX [인덱스명] ON [테이블명] -- 인덱스 추가 CREATE [UNIQUE] <CLUSTERED|NONCLUSTERED> INDEX [인덱스명] ON [테이블명] ([컬럼명] [정렬방식])

(4) 확인

SELECT DB_NAME() AS db_name, i.object_id AS object_id, OBJECT_SCHEMA_NAME(i.object_id) AS schema_name, i.table_name AS table_name, i.name AS index_name, i.index_id, i.type AS index_kind, 0 AS index_type, i.is_primary_key, i.is_unique, kc.is_system_named, ic.index_column_id AS column_seq, COL_NAME(ic.object_id, ic.column_id) AS column_name, (CASE is_descending_key WHEN 1 THEN 2 ELSE 1 END) AS column_sort FROM ( SELECT a.*, b.name AS table_name FROM sys.indexes AS a INNER JOIN sys.tables AS b ON a.object_id = b.object_id WHERE b.type = 'U' AND b.name = '테이블명' ) AS i INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id LEFT OUTER JOIN sys.key_constraints AS kc ON i.object_id = kc.parent_object_id AND i.name = kc.name WHERE i.type > 0 ORDER BY i.table_name ASC, i.index_id ASC, ic.index_column_id ASC