T-SQL

When to use IN operator

Using WHERE IN and WHERE NOT IN clauses in T-SQL code can produce an execution plan involving one or more nested loops. This increases the number of comparisons SQL Server must perform exponentially. Use the WHERE IN clause only if you have a short list of values you need to evaluate:

SELECT *
FROM Customers
WHERE CustomerID NOT IN
   (SELECT CustomerID FROM Orders)

Replace the WHERE IN clause with OUTER JOIN if you're using a subquery to generate a potentially large list. Doing so you can improve performance significantly:

SELECT c.*
FROM Customers c
LEFT OUTER JOIN Orders o
ON o.CustomerID = c.CustomerID
WHERE o.CustomerID IS NULL

In this case, the second query uses LEFT OUTER JOIN, producing an execution plan that lets it run about three times faster than the first query.

Generate row number column in SQL Server

Sometimes we need our SELECT statement to return current row number. In the following script it is shown two ways you can perform this task.

-- Sample 1: Using SELF JOIN
SELECT
        (SELECT COUNT(TerritoryID)
        FROM dbo.Territories T2
        WHERE T2.TerritoryID <= T1.TerritoryID) AS RowNumber
        , T1.*
FROM dbo.Territories T1

-- Sample 2: Using IDENTITY
SELECT
    IDENTITY(int,1,1) as RowNumber, *
    INTO #TempTerritories
FROM dbo.Territories

SELECT * FROM #TempTerritories
DROP TABLE #TempTerritories

Anyway, most of the times you can avoid this practice, and build row number in the GUI part. By example, if you have a report and you want to add a row count column, you can insert a special field called “Record Number”: select Insert menu/Special Field/RecordNumber.

Your suggestions are welcomed.

Empty all tables in SQL Server

In day-by-day programmer life you need a magic script to clean up data you’ve inserted in the application you work on. Of course, you can do this by running ‘CREATE TABLE’ statements and recreate your database from scratch. This is perfect valid, but most of the times you want to keep data in some tables like: configuration tables, catalogues tables, so recreating database is not appropriate in this case.

The following script, interrogates sysobjects table to get all user tables. Then it tries to delete rows from tables. Some of the tables’ rows could be deleted most probably because of reference integrity will be broken. If so, I reiterate all tables and retrying to delete rows. This operation continues until no error is raised while deleting rows from all tables. I must admit this solution uses brute- force, but I found it very simple. I’ve tested it on medium size databases and the results were very good.

If you need to keep data in some tables, you just need to add a clause to the SQL statement getting all user tables in current database:
AND name NOT IN ('author', 'titles', ‘table X’)

SET NOCOUNT ON
DECLARE @TABLE_NAME NVARCHAR(255), @HAS_IDENTITY NUMERIC(15), @HAS_ERROR BIT

-- Create a cursor with all table names
DECLARE TABLES_CURSOR CURSOR SCROLL FOR
SELECT name
FROM sysobjects
WHERE xtype = 'U' AND name <> 'dtproperties'
--AND name NOT IN ('author', 'titles', ...)

SET @HAS_ERROR = 1
OPEN TABLES_CURSOR
WHILE @HAS_ERROR <> 0
BEGIN
       SET @HAS_ERROR = 0
       FETCH FIRST FROM TABLES_CURSOR INTO @TABLE_NAME
       WHILE @@FETCH_STATUS = 0
       BEGIN        
             EXECUTE ('DELETE ' + @TABLE_NAME)
             IF @@ERROR<>0
                    -- Table rows can't be deleted.
                    SET @HAS_ERROR = 1
             ELSE
                    BEGIN
                           --Reset identity for emptied table
                           SET @HAS_IDENTITY = (SELECT IDENT_CURRENT(@TABLE_NAME))
                           IF @HAS_IDENTITY IS NOT NULL
                           BEGIN
                                 DBCC CHECKIDENT (@TABLE_NAME, reseed, 0) WITH NO_INFOMSGS
                           END
                    END

             FETCH NEXT FROM TABLES_CURSOR INTO @TABLE_NAME
       END
END

CLOSE TABLES_CURSOR
DEALLOCATE TABLES_CURSOR

Your suggestions are welcomed.

Using computed columns in SQL Server

In a database we need some values that were often calculated while generating several reports.

Assuming we have the following table:

CREATE TABLE InvoiceLine
AS
(
       InvoiceLineID int
       , InvoiceID int
       , NumberOfItems int
       , Amount money
)

And we want to find the total amount of an invoice line. We can achieve this by addding a new computed column:

CREATE TABLE InvoiceLine
AS
(
       InvoiceLineID int
       , InvoiceID int
       , NumberOfItems int
       , Amount money
       , TotalAmount AS (Amount*NumberOfItems) PERSISTED
)

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)

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.

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

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

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

[SQL Server] Number of pages in a table

The query below helps you to find data file pages for a table based on sys.allocation_units 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

Pages

Subscribe to RSS - T-SQL