WideWorldImporters で使用されている SQL Server 2016 の新機能等の確認クエリ

wwi-機能確認.sql

SET NOCOUNT ON
GO
/*
WideWorldImporters: The new SQL Server sample database
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/09/wideworldimporters-the-new-sql-server-sample-database/

https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
*/

-- Column store Index
SELECT
	'Column Store Index' AS Func, DB_NAME() AS dbname, OBJECT_SCHEMA_NAME(object_id) AS schemaname, OBJECT_NAME(object_id) AS objectname, name, type_desc 
FROM
	sys.indexes 
WHERE 
	type in (5,6)
ORDER BY
	schemaname,
	objectname,
	name

-- Data Compression
SELECT
	'Data Compression' AS Func, DB_NAME() AS dbname, OBJECT_SCHEMA_NAME(p.object_id) AS schemaname, OBJECT_NAME(p.object_id) AS objectname, i.name, p.data_compression_desc
FROM
	sys.partitions p
	INNER JOIN
	sys.indexes i
	ON
	p.object_id = i.object_id
	AND
	p.index_id = i.index_id
WHERE
	data_compression <> 0
	AND
	OBJECT_SCHEMA_NAME(p.object_id) <> 'sys'
ORDER BY
	schemaname, objectname, name

-- In-Memory OLTP
SELECT
	'In-Memory OLTP' AS Func, DB_NAME() AS dbname, OBJECT_SCHEMA_NAME(xtp.object_id) AS schemaname, OBJECT_NAME(xtp.object_id) AS objectname
FROM
	sys.dm_db_xtp_object_stats xtp
	INNER JOIN
	sys.objects o
	ON
	xtp.object_id = o.object_id
	AND
	o.is_ms_shipped = 0
ORDER BY
	schemaname,
	objectname

-- Native Compiled Stored Procedure
SELECT
	'Native Compiled Stored' AS Func, DB_NAME() AS dbname, OBJECT_SCHEMA_NAME(object_id) AS schemaname, OBJECT_NAME(object_id) AS objectname
FROM	 
	sys.all_sql_modules 
WHERE 
	uses_native_compilation = 1
ORDER BY
	schemaname,
	objectname

-- Partition
SELECT DISTINCT
	'Partition' AS Func, DB_NAME() AS dbname, OBJECT_SCHEMA_NAME(p.object_id) AS schemaname, OBJECT_NAME(p.object_id) AS objectname,i.name
FROM
	sys.partitions p
	INNER JOIN
	sys.indexes i
	ON
	p.object_id = i.object_id
	AND
	p.index_id = i.index_id
WHERE
	partition_number > 1
ORDER BY
	schemaname,
	objectname

-- Temporal
SELECT 
	'Temporal' AS Func, DB_NAME() AS dbname, OBJECT_SCHEMA_NAME(t.object_id) AS schemaname, t.name, h.name AS history_table
FROM 
	sys.tables t
	LEFT JOIN
	sys.tables h
	ON
	t.history_table_id = h.object_id
WHERE t.history_table_id IS NOT NULL
ORDER BY 
	schemaname,
	t.name

-- Row-Level Security
SELECT
	'Row-Level Security' AS Func, DB_NAME() AS dbname, OBJECT_SCHEMA_NAME(object_id) AS schemaname, name
FROM
	sys.security_policies
ORDER BY 
	schemaname,
	name

-- Security Predicates
SELECT
	'Security Predicates' AS Func, DB_NAME() AS dbname, OBJECT_SCHEMA_NAME(object_id) AS schemaname, OBJECT_NAME(object_id) AS objectname, OBJECT_NAME(target_object_id) AS target_object_name, predicate_type_desc
FROM
	sys.security_predicates
ORDER BY 
	schemaname,
	objectname

-- Dynamic Data Masking
SELECT
	'Dynamic Data Masking' AS Func, DB_NAME() AS dbname, OBJECT_SCHEMA_NAME(m.object_id) AS schemaname,object_name(m.object_id) as objectname, m.name, c.name AS columnname
FROM
	sys.masked_columns m
	INNER JOIN
	sys.columns c
	ON
	m.object_id = c.object_id
	AND
	m.column_id = c.column_id
ORDER BY 
	schemaname,
	objectname

-- Query Store
SELECT
	'Query Store' AS Func,
	actual_state_desc,
	query_capture_mode_desc,
	size_based_cleanup_mode_desc
FROM
	sys.database_query_store_options