sql-server-2016

Articles about SQL Server 2016, In-Memory OLTP Database Engine, Windows Azure integration, AlwaysOn Availability Groups, business intellligence, data visualization tools, and more.

Temporal tables

- System version tables
- Point-in-time data access
- Behind the hood: a history table is created with the same schema, but without constraints
- ALTER TABLE automatically updates the history table

DECLARE @TenDaysAgo datetime2 = DATEADD(d, -10, SYSDATETIME())

SELECT *
FROM dbo.MyTable
FOR SYSTEM_TIME AS OF @TenDaysAgo

Built-in JSON support

- New XML is JSON
- Conceptually similar to XML support
- FOR JSON clause

SELECT *
FROM dbo.MyTable
FOR JSON AUTO

- New function ISJSON(): validates for well-formed JSON string. Can be used as CHECK CONSTRAINT.
- OPENJSON: transforms JSON text to table
SELECT
    PersonId = JSON_VALUE(@personJsonAsString, '$.Id'),
    PersonName = JSON_VALUE(c.value, '$.Name')
FROM
    OPENJSON(@personJsonAsString, '$.Contacts') as c

- JSON_QUERY: queries by path expression and returns a nested array
- JSON_VALUE: queries by path expression and returns a scalar value
SELECT *
FROM dbo.MyTable
WHERE JSON_VALUE(MyColumn, '$.myProperty') = 'MyValue'

Limitations
- No native "json" data type => use nvarchar(max)
- No custom JSON index => create computed columns over desired properties, and then index the computed columns
- No JSON "DML": cannot directly modify JSON content. Use string functions for manipulation.

Row-level security

RLS: Row-level security = restrict access to individual rows in a table
- Allows you to write custom logic to control user access to every row
- Security predicate function = ordinary inline schema-bound table-valued function whose parameters are mapped to column values

CREATE SECURITY POLICY sec.MyTablePolicyFilter
    ADD FILTER PREDICATE sec.fn_mySecurityPredicate(MyColumnName)
    ON dbo.MyTable
    WITH (STATE = ON)

Dynamic data masking

DDM: Dynamic Data Masking = Limit exposure to sensitive data by masking
- Full masking: entire column is marked
- Partial masking: show starting and/or ending characters of the column data
- Random masking: entire column is replaced by random values
- Data is the database is not changed

CREATE TABLE dbo.MyTable
(
    MyColumn VARCHAR(20) MASKED WITH (FUNCTION = 'partial(1, "...", 0)')
)

Permissions
- ALTER ANY MASK: add, replace or remove a column mask
- UNMASK: view unmasked data in masked columns

SESSION_CONTEXT()

Session context
- It is a stateful dictionary retaining state for the lifetime of the connection.
- Vast improvement of CONTEXT_INFO
- Used to share data accross stored procedures
- It is stored in memory and it stays there as long the SQL Server connection is being alive
- The value can be declared as "read-only" => the value cannot be changed along the current connection

Store a value in the session context:

EXEC sp_set_session_context @key = N'MyKey' , @value=N'MyValue'

Read a value from the session context:

SESSION_CONTEXT(N'MyKey')

Drop if exists

Before:

IF OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.MyTable
END
GO

Now:

DROP TABLE IF EXISTS dbo.MyTable
DROP TRIGGER IF EXISTS trMyTableInsert

SSMS vs SSDT

SSMS:SQL Server Management Studio
- Object Explorer
- Query Window (F5)

SSDT:SQL Server Data Tools
- Object Explorer
- Query Window (Ctrl + Shift + E)
- Source control, database projects, integrated debugger

How to find space used by each column in a table

SELECT 'SELECT SUM(DATALENGTH(' + name + '))/1024.0/1024.0 As ' + name + '_MB FROM dbo.MyTable'
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.MyTable')

Number of pages in a table

How to find data file pages for a table:

SELECT
        T.NAME AS table_name,
        P.rows AS no_rows,
        SUM(AU.total_pages) AS total_pages,
        SUM(AU.used_pages) AS used_pages,
        (SUM(AU.total_pages) - SUM(AU.used_pages)) AS unused_pages
FROM
        sys.tables T
        INNER JOIN sys.indexes I ON T.OBJECT_ID = I.object_id
        INNER JOIN sys.partitions P ON I.object_id = P.OBJECT_ID AND I.index_id = P.index_id
        INNER JOIN sys.allocation_units AU ON P.partition_id = AU.container_id
GROUP BY
        T.name, P.rows
ORDER BY total_pages DESC

Comparing table structure with TSQL

Compare table structures using sys.columns:

DECLARE
        @table1 as NVARCHAR(255) = N'Reports',
        @table2 as NVARCHAR(255) = N'Reports_Archives'

;WITH cteColumns
AS
(
        SELECT C.object_id, C.name as column_name, T.name as type_name, C.max_length, C.precision
        FROM
                sys.columns C
                INNER JOIN sys.types T ON T.system_type_id = C.system_type_id
)
SELECT column_name, type_name, max_length, precision, 'In ' + @table1
FROM cteColumns
WHERE object_id = OBJECT_ID(@table1)
EXCEPT
SELECT column_name, type_name, max_length, precision, 'In ' + @table1
FROM cteColumns
WHERE object_id = OBJECT_ID(@table2)
 
UNION ALL
 
SELECT column_name, type_name, max_length, precision, 'In ' + @table2
FROM cteColumns
WHERE object_id = OBJECT_ID(@table2)
EXCEPT
SELECT column_name, type_name, max_length, precision, 'In ' + @table2
FROM cteColumns
WHERE object_id = OBJECT_ID(@table1)

Pages

Subscribe to RSS - sql-server-2016