[SQL SERVER] Table & View

- Primary key 지정시 이름 지정 가능

userID char(8) NOT NULL CONSTRAINT PK_userTbl_userID PRIMARY KEY

혹은 맨 뒤에

CONSTRAINT PK_userTbl_userID PRIMARY KEY (userID)

ALTER TABLE userTbl

ADD CONSTRAINT PK_userTbl_userID 

PRIMARY KEY(userID);

: Primary Key로 변경

- Foreign Key

userID char(8) NOT NULL

CONSTRAINT FK_userTbl_buyTbl

FOREIGN KEY PREFERENCES userTbl(userID)

ON DELETE CASCADE;

ON UPDATE CASCADE;

UNIQUE

: 중복되지 않은 유일한 값

email char(30) NULL

CONSTRAINT AK_email UNIQUE

CHECK

: 입력되는 데이터를 점검하는 기능

ALTER TABLE userTbl

ADD CONSTRAINT CK_birthYear

CHECK (birthYear>=1900 AND birthYear<=YEAR(GETDATE()));

CHECL (mobile in ('010','011','016'));

CHECK (height>=0);

CHECK CONSTRAINT Fk_userTbl_buyTbl;

ALTER TABLE userTbl

WITH NOCHECK -- 기존의 데이터에서 CHECK의 범위를 넘은 것은 그냥 넘어가자 (앞으로 들어갈 데이터만 CHECK하자)

ADD CONSTRAINT CK_mobile1

CHECK (mobile in ('010','011'));

ALTER TABLE userTbl

ADD CONTRAINT CD_birthYear

DEFAULT YEAR(GETDATE()) FOR birthYear;

스파스열

: null값에 대해 최적화된 저장소가 있는 일반 열

NULL값이 많이 들어갈 것으로 예상되는 열을 스파스 열로 지정해놓으면 공간 절약의 효과가 있다

스파스열을 지정하면 속도가 느려짐

따라서 적어도 공간이 40%는 절약이 되야 스파스열로 지정하는 의미가 있음

CREATE TABLE sparseTbl (id int identity, data char(100) SPARSE NULL);

임시테이블

:'#' - 지역임시테이블 이나 '##'-전역임시테이블 을 붙이면 임시 테이블로 tempdb에 저장이 된다.

SQL Server를 다시 시작하면 tempdb의 데이터도 모두 삭제된다

로컬 임시 테이블 : 생성한 사용자의 연결이 끊기면 삭제 (쿼리창이 닫히면 삭제)

전역 임시 테이블 : 생성한 사용자의 연결이 끊기고 이 테이블을 사용중인 사용자가 없을 때

ALTER TABLE table 

DROP COLUMN mobile;

ALTER COLUMN name NVARCHAR(20) NULL;

DROP CONSTRAINT 제약 조건 이름;

EXEC sp_rename 'userTbl.name','username', 'COLUNM';

메모리 테이블

:

메모리 테이블은 디스크가 아닌 메모리(RAM)에 테이블이 존재

테이블에 읽기/쓰기 속도가 향상

메모리는 전원이 꺼지면 그 내용이 모두 사라지기 때문에 이를 방지하기 위해서 메모리의 테이블의 보조 복사본이 디스크에서도 유지 관리

조건

1. 64bit SQL SERVER 2014 이후 버전의 Enterprise, Developer Evaluation Edition 사용

2. 충분한 RAM이 장착 (32GB 이상)

3. 메모리 테이블 크기의 2배에 해당하는 디스크 여유 공간

4. 테이블에 기본키 및 비클러스터형 인덱스 필요

<메모리 테이블 실습해보기>

1) Database 생성 : CREATE DATABASE memoryDB;

2) 파일 그룹 생성 : memoryDB -> 속성 -> 파일 그룹 -> 파일 그룹 지정

3) 파일 생성 : 파일 -> 데이터 파일 (FileStream Data로 생성)

4) Table을 생성

CREATE TABLE diskTable (a INT PRIMARY KEY NONCLUSTERED, b NCHAR(100)); 

//disk 용 테이블(일반 테이블)

CREATE TABLE diskTable (a INT PRIMARY KEY NONCLUSTERED, b NCHAR(100)) WITH (MEMORY_OPTIMIZED=ON); 

//memory 용 테이블

5) 저장 Procedure를 생성

5-1) disk Table

CREATE PROCEDURE usp_diskInsert

@data NCHAR(100)

AS

DECLARE @i INT = 1;

WHILE @i <= 500

BEGIN

INSERT INTO dbo.diskTable(a,b) VALUES (@i,@data);

SET @i+=1;

END

GO

5-2) memory Table

CREATE PROCEDURE usp_memoryInsert

@data NCHAR(100)

WITH NATIVE_COMPILATION, SCHEMABINDING

AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'Korean')

DECLARE @i INT = 1;

WHILE @i <= 500

BEGIN

INSERT INTO dbo.memory Table(a,b) VALUES (@i,@data);

SET @i+=1;

END

GO

6) 데이터를 넣어보자

DECLARE @sendData NCHAR(100) = REPLICATE(N'가',100);

// REPLICATE : 가를 100번 쓴다

EXECUTE usp_diskInsert @sendData;

DECLARE @sendData NCHAR(100) = REPLICATE(N'가',100);

EXECUTE usp_memoryInsert @sendData;

시간이 위보다 아래가 훨씬 짧게 나온다

스키마

: 기본 스키마는 dbo이다

하지만 스키마 생성이 가능!

CREATE DATABASE schemaDB;

USE schemaDB;

CREATE SCHEMA userSchema;

CREATE SCHEMA buySchema;

CREATE TABLE userSchema.userTBL (id int);

CREATE TABLE buySchema.buyTBL (pid int);

VIEW

:

CREATE VIEW v_userTbl

AS

SELECT userid AS [바꿀 열이름], name, addr FROM userTbl;

// 뷰를 테이블이라고 생각해도 무방

뷰테이블을 확인하고 싶을 때

SELECT * FROM sys.sql_modules;

SELECT OBJECT_NAME(object_id) AS [뷰 이름].definition FROM sys.sql_modules;

ALTER VIEW v_userTbl

WITH DNCRYPTION

AS

SELECT userid, name, addr FROM userTbl;

: View의 내용이 보안이 됨 (뷰 테이블의 내용을 확인하더라도 definition은 확인이 되지 않음)

View에 UPDATE 가능

UPDATE v_userTbl SET addr = N'부산' WHERE userid='JKW';

ALTER VIEW v_userTbl

AS

SELECT * FROM table WHERE height>=177

WITH CHECK OPTION;

// height가 177 이상인 것만 입력 가능

EXEC sp_depends userTbl;

: 어떤 테이블을 어떤 뷰가 참조하고 있는지 확인

+) 인덱싱된 뷰 : 실제 데이터가 뷰에 들어있음 -> 인덱싱을 하면 속도가 빨라짐

   시스템 뷰 : SQL Server의 상태 및 내부 정보를 관리하기 위한 시스템 테이블을 가지고 있음