What are the implications of index fragmentation?

Slower index scans.

Increased disk space usage.The density of rows on the pages is lower => store fewer rows per page => wasting space

Increased buffer pool usage.

Causes increased transaction log generation.

Tags:

Solving the N+1 selects problem

This problems occurs when you have a parent object loading its child data by issuing a separate SQL statement for each child objects that needs to be loaded. So n+1 queries are executed against the database.

Records are being loaded individually because they are being lazy loaded.

What we can do instead if to use eager loading.

Tags:

Adhoc queries vs Parameterized queries vs Stored procedures

Parameterized queries vs adhoc (dynamic) queries:
- Protects against SQL injection attacks.
- Improves application performance and scalability as the same cached execution plan is reused for each query execution, compared with adhoc queries where an execution plan is generated every time.
- Plan Cache memory is kept low.

Parameterized queries vs stored procedures:
- Similar performance.
- Stored procedures offers advantages when we talk about security or restriction to data.

Tags:

SQL Server Operations in Query Execution Plan

Clustered Index Scan: Reads all the rows stored in a table stored as a clustered index.
Table Scan: Reads all the rows in table that is stored as a heap structure.
Clustered Index Seek: Traverses the tree structure of table stored as a clustered index to find the needed row(s).
Index Scan: Reads all of the key values of an index to find the matching data.
Index Seek: Traverses the tree structure of an index to find the matching index keys.

Nested Loops Join: For each value in the first data set, SQL Server loops through the second data set looking for matches.
Merge Join: Used to join two data sets that are already sorted using the same key. A row from each source is obtained. If the rows match, they are joined. If the rows don't match, the lower value row is discarded and a new row is obtained from that source.
Hash Match: A hashtable of the smaller data set is created, then SQL Server loops through the larger data set probing the hashtable for matching values. Used when two large datasets must be joined.

Tags:

Temporal tables - New feature in SQL Server 2016

- 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

Tags:

Built-in JSON support in SQL Server 2016

- 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.

Tags:

Row-level security in SQL Server 2016

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)

Tags:

Dynamic data masking in SQL Server 2016

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.

Tags:

SESSION_CONTEXT - Retain state for the lifetime of the connection in SQL Server

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')

Tags:

Drop if exists in SQL Server 2016

Before:

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

When writing T-SQL code, we often write code to check if the database object exists first and then take some action. There is an easier way to do this in SQL Server 2016:

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

Tags:

Pages