뷰

CREATE VIEW Board.V_POST_LIST AS
SELECT p.num, b.name AS board_name, c.name AS category_name, u.nickname,
       p.title, p.hit_count, p.reg_date, p.depth
FROM Board.POSTS p
    JOIN Board.BOARDS b ON b.num = p.board_num
    LEFT JOIN Board.CATEGORIES c ON c.num = p.category_num
    JOIN Member.USERS u ON u.num = p.user_num;
GO

-- 이제 표처럼 조회합니다.
SELECT TOP (3) num, board_name, category_name, nickname, title
FROM Board.V_POST_LIST ORDER BY num;

SET STATISTICS IO ON;
-- 글 자체의 열만 가져옵니다.
SELECT num, title FROM Board.V_POST_LIST WHERE num = 250;

-- 게시판 이름과 작성자까지 가져옵니다.
SELECT num, title, board_name, nickname FROM Board.V_POST_LIST WHERE num = 250;
SET STATISTICS IO OFF;

SET SHOWPLAN_TEXT ON;
GO
SELECT num, title FROM Board.V_POST_LIST WHERE num = 250;
GO
SET SHOWPLAN_TEXT OFF;

CREATE VIEW Board.V_NOTICE AS
SELECT num, board_num, title, hit_count FROM Board.POSTS WHERE board_num = 1;
GO

UPDATE Board.V_NOTICE SET hit_count = hit_count + 1 WHERE num = 250;
SELECT num, hit_count FROM Board.POSTS WHERE num = 250;

UPDATE Board.V_NOTICE SET board_num = 2 WHERE num = 250;

SELECT COUNT(*) AS 뷰에남았나 FROM Board.V_NOTICE WHERE num = 250;

-- 되돌립니다. 앞의 조회수도 함께 되돌립니다.
UPDATE Board.POSTS SET board_num = 1, hit_count = hit_count - 1 WHERE num = 250;

ALTER VIEW Board.V_NOTICE AS
SELECT num, board_num, title, hit_count FROM Board.POSTS WHERE board_num = 1
WITH CHECK OPTION;
GO

UPDATE Board.V_NOTICE SET board_num = 2 WHERE num = 250;

-- POSTS 의 열만 고칩니다. 됩니다.
UPDATE Board.V_POST_LIST SET title = N'바꿔봅니다' WHERE num = 250;

-- POSTS 의 title 과 USERS 의 nickname 을 함께 고칩니다.
UPDATE Board.V_POST_LIST SET title = N'x', nickname = N'y' WHERE num = 250;

-- 지우는 것도 마찬가지입니다. 어느 표에서 지울지 정할 수 없습니다.
DELETE FROM Board.V_POST_LIST WHERE num = 250;

-- 첫 문장이 성공했으므로 제목을 되돌립니다.
UPDATE Board.POSTS SET title = N'제약 조건 이렇게 쓰는 게 맞나요' WHERE num = 250;

CREATE VIEW Board.V_STAR AS SELECT * FROM Board.BOARDS;
GO

-- 원본에 열을 하나 더합니다.
ALTER TABLE Board.BOARDS ADD memo nvarchar(100) NULL;

SELECT name AS 열 FROM sys.columns
WHERE object_id = OBJECT_ID('Board.V_STAR') ORDER BY column_id;

EXEC sp_refreshview 'Board.V_STAR';   -- 이제 memo 가 나옵니다

CREATE TABLE Board.T_V (a int, b nvarchar(20), c int);
INSERT INTO Board.T_V VALUES (1, N'비', 100), (2, N'비2', 200);
GO
CREATE VIEW Board.V_T AS SELECT * FROM Board.T_V;
GO
-- 가운데 열을 지우고 뒤에 다른 열을 더합니다.
ALTER TABLE Board.T_V DROP COLUMN b;
GO
ALTER TABLE Board.T_V ADD d nvarchar(20) NULL;
GO
UPDATE Board.T_V SET d = N'디' WHERE a = 1;
GO
SELECT * FROM Board.T_V;
SELECT * FROM Board.V_T;

CREATE VIEW Board.V_SB WITH SCHEMABINDING AS
SELECT num, title, hit_count FROM Board.POSTS;
GO
-- 뷰가 보고 있는 열의 형식을 바꿔 봅니다.
ALTER TABLE Board.POSTS ALTER COLUMN hit_count bigint NOT NULL;

-- 지우는 것도 막힙니다.
ALTER TABLE Board.POSTS DROP COLUMN hit_count;

SET STATISTICS IO ON;
SELECT board_num, COUNT_BIG(*) AS post_count, SUM(hit_count) AS hit_sum
FROM Board.POSTS GROUP BY board_num;
SET STATISTICS IO OFF;

-- SCHEMABINDING 을 빠뜨리면
CREATE VIEW Board.V_BOARD_STAT_NS AS
SELECT board_num, COUNT_BIG(*) AS post_count, SUM(hit_count) AS hit_sum
FROM Board.POSTS GROUP BY board_num;
GO
CREATE UNIQUE CLUSTERED INDEX CX_NS ON Board.V_BOARD_STAT_NS (board_num);
GO

-- COUNT_BIG(*) 을 빠뜨리면
CREATE VIEW Board.V_BOARD_STAT_NC WITH SCHEMABINDING AS
SELECT board_num, SUM(hit_count) AS hit_sum
FROM Board.POSTS GROUP BY board_num;
GO
CREATE UNIQUE CLUSTERED INDEX CX_NC ON Board.V_BOARD_STAT_NC (board_num);

CREATE VIEW Board.V_BOARD_STAT WITH SCHEMABINDING AS
SELECT board_num, COUNT_BIG(*) AS post_count, SUM(hit_count) AS hit_sum
FROM Board.POSTS GROUP BY board_num;
GO
CREATE UNIQUE CLUSTERED INDEX CX_V_BOARD_STAT ON Board.V_BOARD_STAT (board_num);

-- 뷰가 자리를 차지하고 있는지 봅니다.
SELECT i.name AS 인덱스, ps.page_count AS 페이지, ps.record_count AS 행
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Board.V_BOARD_STAT'), NULL, NULL, 'DETAILED') ps
    JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id;

SET STATISTICS IO ON;
SELECT board_num, post_count, hit_sum FROM Board.V_BOARD_STAT;
SELECT board_num, post_count, hit_sum FROM Board.V_BOARD_STAT WITH (NOEXPAND);
SET STATISTICS IO OFF;

BEGIN TRAN;
UPDATE Board.POSTS SET hit_count = hit_count + 1 WHERE num BETWEEN 1 AND 100;
SELECT database_transaction_log_record_count AS 로그레코드,
       database_transaction_log_bytes_used AS 로그바이트
FROM sys.dm_tran_database_transactions WHERE database_id = DB_ID();
ROLLBACK;