Programming

常見MSSQL語法

(參考MSSQL)

Menu

Type CRUD
— —
Index Read
— —
Schema Read
— —
Plan Read
— —
Rank Rank
— —

Read Index

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT i.name AS index_name
    ,i.type_desc
    ,is_unique
    ,ds.type_desc AS filegroup_or_partition_scheme
    ,ds.name AS filegroup_or_partition_scheme_name
    ,ignore_dup_key
    ,is_primary_key
    ,is_unique_constraint
    ,fill_factor
    ,is_padded
    ,is_disabled
    ,allow_row_locks
    ,allow_page_locks
FROM sys.indexes AS i  with (nolock)
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0
AND i.object_id = OBJECT_ID('tabel_name');

(Back to Menu)

Read Schema

1
2
3
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='table_name'

(Back to Menu)

Read Plan

1
2
3
4
5
6
7
8
SELECT
    q.text AS QueryText,
    cp.usecounts,
    p.query_plan
FROM sys.dm_exec_cached_plans AS cp with (nolock)
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS p
WHERE q.text LIKE '%stored_procedure_name%';

(Back to Menu)

Rank Schema

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH CurrencyTotal AS (
    SELECT
        c.CurrencyId,
        tl.CustomerId,
        SUM(tl.Amount) AS TotalAmount
    FROM
        [Customer] c WITH (nolock)
    JOIN
        [TransLog] tl
        ON tl.CustomerId = c.CustomerId
    GROUP BY
        c.CurrencyId, tl.CustomerId
)
SELECT
    CurrencyId,
    CustomerId,
    TotalAmount,
    RANK() OVER (PARTITION BY CurrencyId ORDER BY TotalAmount DESC) AS Ranking
FROM
    CurrencyTotal;

(Back to Menu)

ʕ •ᴥ•ʔ：記錄一些常見語法，需要時方便查詢。

Share