C# ????????????? ??????????: SQL Views ?? Indexes. ??????? #46

?? ????????

Views (??????) - ?? ???? ???

???????????? ?????? ????????? ????????? ?????. ??????? ????? ?????? ???????? ???? ???????, ?????? ?? ?????? ?????????, ?????????????? ??????????????? ?? ???? ???????? ????????. SQL-?? Views ?????? ??? ??????? - ?? ???? "?????????? ??????", ??????? ?????????? ?????????? ???????? ????????? ??????.

Indexes (?????????) - ????? ??????????

???????????? ????? ????????? ??????. ?? ?????? ?????? ?????????? ????, ???????? ????? ?????? ??????????????. ????????? (???????) ?????????? ??????? ??????? ?????? ??????. SQL-?? ????????? ?????? ??? ??????? - ???????? ????? ??????? ?????? ?????? ??????????.

?? Views - ?????????

1. ??????? Views

???????????? ?????? Instagram-?? - ????? ????? ????? ?? ????? ??????. ???? ??????, ??????? ?????????? ????.

-- ??????? View-? ????????
CREATE VIEW ???????_?????????? AS
SELECT ??????, ?????, ?????????
FROM ??????????
WHERE ??????? = '???????';

-- ??????????
SELECT * FROM ???????_??????????;        

2. ????? Views

?? ????? Excel-?? ????????? ????????? ?????????:

CREATE VIEW ???????????_?????_???? AS
SELECT 
    ?.??????,
    ?.?????,
    ?.?????????? AS ?????????,
    AVG(?.????) AS ???????_????
FROM 
    ?????????? ?
    JOIN ??????????? ? ON ?.??????????_ID = ?.ID
    LEFT JOIN ?????????? ? ON ?.ID = ?.?????????_ID
GROUP BY 
    ?.??????, ?.?????, ?.??????????;        

3. ????????? Views

????? ???? SQL Server-?? "????????? ??????":

-- ????????? ??????????? ?????
SELECT * FROM sys.databases;  -- ????? ????? ???
SELECT * FROM sys.tables;     -- ????? ??????? ???        

?? Indexes - ?????????

1. Clustered Index (?????????? ???????)

???????????? ????????? - ???????? ??????????? ??????? ????????. ????? ???? ??? "?"-?? ????????? ???????? ???? ??????????, "?"-?? ????????? - ??????.

-- ????????? ???????? ??????????? ????? ????????? ???????
CREATE TABLE ?????????? (
    ?????????_ID INT PRIMARY KEY,  -- ?? ???? ?????????? ???????
    ?????? NVARCHAR(50),
    ????? NVARCHAR(50)
);

-- ?? ????????? ????? ????????
CREATE CLUSTERED INDEX IX_??????????_?????
ON ??????????(?????);        

2. Non-Clustered Index (????????????? ???????)

???????????? ?????? ????? ???????? ?????????? ????????? - ?? ?????????? ??????? ??????, ????? ????????? ??????? ?????????? ????.

-- ????????????? ???????? ??????
CREATE NONCLUSTERED INDEX IX_??????????_??????
ON ??????????(??????);

-- ????????? ??????
CREATE NONCLUSTERED INDEX IX_??????????_??????_?????
ON ??????????(??????, ?????);        

?? ?????????? ????????

????????????? ?????? ???????? ???????:

-- ????????? ??????
CREATE TABLE ?????????? (
    ?????????_ID INT PRIMARY KEY,
    ?????????? NVARCHAR(100),
    ???? DECIMAL(10,2),
    ????????? NVARCHAR(50)
);

CREATE TABLE ????????? (
    ????????_ID INT PRIMARY KEY,
    ?????????_ID INT,
    ????????? INT,
    ?????? DATE
);

-- View ?????? ?????????? ????????????
CREATE VIEW ??????????_???????? AS
SELECT 
    ?.??????????,
    ?.?????????,
    SUM(?.?????????) AS ??????_?????????,
    SUM(?.????????? * ?.????) AS ??????_?????
FROM 
    ?????????? ?
    JOIN ????????? ? ON ?.?????????_ID = ?.?????????_ID
GROUP BY 
    ?.??????????, ?.?????????;

-- ?????????? ???????? ??????????????
CREATE NONCLUSTERED INDEX IX_?????????_??????
ON ?????????(??????);

CREATE NONCLUSTERED INDEX IX_??????????_?????????
ON ??????????(?????????);        

?? ?????????? ???????

???????? 1: ???????? View

???????? View ??????? ???_??????????, ??????? ???????? ????????? 10 ???????? ??????? ????? ????????.

???????? 2: ?????????? ???????????

??????????? ???????? ?????????? ?? ???????? ?????????? ????????? ?????????? ????????????????.

?? ???????

Views-?? ?????????????:

1. ??????????? ??????????? - ???????? ?????? ?????? ???????????
2. ????? ??????????? - ????? ??????????? ???????????
3. ??????????? ?????????? - ????????? ????????? ???????

Indexes-?? ?????????????:

1. ?????? ????? - ??????????? ?????? ?????
2. ?????????? ???????????? - ???????? ???????????
3. ??????????? ?????????? - ??????? ?????????

?? ??????? ????????

1. ???????? ?????????? ????? Views ?????? ??????
2. ??????????? ????? ???????? ????????? ?????????
3. ????????? Views ?? Indexes-?? ??????? ???????????
4. ?????????? ????????? Views ????????????????

?? ??????? ?????????????

• ??????? ??????? Views-??
• ?? ??????? ??????? ?????????
• ?????????? ????????? Views ????????????????
• ????????? ??????? ???????????