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.

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

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

[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

Comparing table structure in SQL Server

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)

When statistics were last updated in SQL Server?

T-SQL showing when statistics were last updated using STATS_DATE function in SQL Server:

SELECT
        OBJECT_NAME(S.object_id) as object_name,  S.name as stat_name,
        S.auto_created, S.user_created, S.no_recompute,
        STATS_DATE(S.object_id, stats_id) as last_updated
FROM
        sys.stats S
        INNER JOIN sys.objects O ON O.object_id = S.object_id AND O.is_ms_shipped = 0
ORDER BY object_name

How to (manually) update the statistics?

  • Force stats to update for a specific table (including clause WITH FULLSCAN):
    UPDATE STATISTICS <tablename>;
  • Full sampling:
    UPDATE STATISTICS <tableName> (<columnName>) WITH FULLSCAN;
  • Force stats to update for the entire database:
    EXEC sp_updatestats;

Identify blocking in SQL Server

Run this T-SQL statement to identify current blockings in the current database:

SELECT *
FROM sys.sysprocesses
WHERE
        dbid = DB_ID()
        AND blocked > 0

Too see T-SQL that blocks, just take the value of spid column returned above and run the following command:

DBCC INPUTBUFFER(<spid>);

In order to see the blocked T-SQL command, take the value of column returned above and run:

DBCC INPUTBUFFER(<blocked>);

Find missing index warnings in cached query plans

Find missing index warnings for cached plans in the current database:

SELECT TOP 20
        OBJECT_NAME(objectid) as object_name,
        CP.objtype, CP.usecounts,
        QP.query_plan  
FROM
        sys.dm_exec_cached_plans CP
        CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
        CAST(QP.query_plan as NVARCHAR(MAX)) LIKE N'%MissingIndex%'
        AND dbid = DB_ID()
ORDER BY CP.usecounts DESC

Find locks in SQL Server

dm_tran_locks contains info about all current locks, granted or pending.

select *
from sys.dm_tran_locks

Pages

Subscribe to RSS - sql-server-2016