MSSQL - Kill all my processes (except current)

Template to kill all my processes (connections) except current

USE master
GO
 
DECLARE @sql VARCHAR(MAX) = '-- Kill all my processes (except current: '
                          + CAST(@@SPID AS VARCHAR(20)) + ')';
SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE hostname = HOST_NAME() AND spid != @@SPID;
 
PRINT @sql;
EXEC (@sql);

After execution we have something like this:

-- Kill all my processes (except current: 80)
kill 52;
kill 53;
kill 73;

MSSQL - Stored procedure to replace placeholders in texts (dynamic SQL)

It is often necessary to make the query text from constant pieces and variables.

For example we have a many tables with such structure:

(
    ID         INT IDENTITY(1,1) PRIMARY KEY,
    Number     INT,
    ChangeDate DATETIME
)

and we need dynamically, depending on variables with table name, [Number] min value and [ChangeDate] min value send queries to one or other table.
We can do it using Dynamic SQL method, for example so:

CREATE PROCEDURE MySP
    @ObjectName SYSNAME,
    @MinNumber  INT,
    @ValidFrom  DATETIME
AS BEGIN
    DECLARE @SQL NVarChar(MAX);
 
    SET @SQL = '
        SELECT
            *
        FROM
            ' + @ObjectName + '
        WHERE
            Number >= ' + CAST(@MinNumber AS VARCHAR(20)) + '
            AND
            ChangeDate >= CONVERT(DATETIME, '''
            + CONVERT(VARCHAR(30), @ValidFrom, 121) + ''', 121);';
 
    PRINT @SQL;
 
    EXEC sp_executesql @SQL;
END;
GO
 
EXEC MySP 'MyTableNr1', 123, '2015-01-01 12:00';

It works, but SQL-Text format is not readable.
Much more readable looks the text converted to a template with placeholders:

'
SELECT
    *
FROM
    {ObjectName}
WHERE
    Number >= {MinNumber}
    AND
    ChangeDate >= CONVERT(DATETIME, ''{ValidFrom}'';
'

In this case our example stored procedure will look like this:

CREATE PROCEDURE MySP
    @ObjectName SYSNAME,
    @MinNumber  INT,
    @ValidFrom  DATETIME
AS BEGIN
    DECLARE @SQL NVarChar(MAX);
 
    SET @SQL = '
        SELECT
            *
        FROM
            {ObjectName}
        WHERE
            Number >= {MinNumber}
            AND
            ChangeDate >= CONVERT(DATETIME, ''{ValidFrom}'';'
 
    SET @SQL = REPLACE(@SQL, '{ObjectName}', @ObjectName);
    SET @SQL = REPLACE(@SQL, '{MinNumber}',  CAST(@MinNumber AS VARCHAR(20)));
    SET @SQL = REPLACE(@SQL, '{ValidFrom}',  CONVERT(VARCHAR(30), @ValidFrom, 121));
 
    PRINT @SQL;
 
    EXEC sp_executesql @SQL;
END;

It is good for so simple example, but if we will have more dynamical parameters and inside one stored procedure (or plain query) more then one template, then this query will have too many redundant call like this:

SET @SQL = REPLACE(@SQL, ..., ...);

The solution is to create some help procedure which will take text template and dictionary with placeholder name:value pairs as parameters.
Stored procedure can not take table-value parameters directly.
Table-valued parameters have to be declared by using user-defined table types.
In our case:

CREATE TYPE PlaceholderMappingType AS TABLE (
    Placeholder NVARCHAR(100),
    Value NVARCHAR(MAX)
);

The complete script with usage example:

USE tools;
GO
IF OBJECT_ID('tools.dbo.sp_ReplacePlaceholders') IS NOT NULL BEGIN
    PRINT '-- DROP PROCEDURE dbo.sp_ReplacePlaceholders';
    DROP PROCEDURE dbo.sp_ReplacePlaceholders;
END;
GO
IF EXISTS (
    SELECT *
    FROM tools.INFORMATION_SCHEMA.DOMAINS
    WHERE DOMAIN_NAME = 'PlaceholderMappingType'
) BEGIN
    PRINT '-- DROP TYPE dbo.PlaceholderMappingType';
    DROP TYPE dbo.PlaceholderMappingType;
END;
GO
PRINT '-- CREATE TYPE dbo.PlaceholderMappingType';
-- TABLE-Domain (User Defined Type) for placeholders dictionary
CREATE TYPE PlaceholderMappingType AS TABLE (
    Placeholder NVARCHAR(100),
    Value NVARCHAR(MAX)
);
GO
CREATE PROCEDURE dbo.sp_ReplacePlaceholders (
    @Text                NVARCHAR(MAX) OUTPUT,
    -- TABLE-Domain (User Defined Type) for placeholders dictionary
    @PlaceholderMappings PlaceholderMappingType READONLY,
    -- Flag to replace in placeholder values each single apostrof with double apostrof
    @EscapeApostrofs     BIT = 1,
    -- Text to TrimLeft in each text line
    @IndentString        VARCHAR(MAX) = '',
    -- Flag to PRINT additional diagnostic information
    @Debug               BIT = 0
)
AS BEGIN
    SET NOCOUNT ON;
 
    IF (@Debug = 1)
        PRINT '-- EXEC dbo.sp_ReplacePlaceholders'
 
    DECLARE
        @CRLF             CHAR(2) = CHAR(13)+CHAR(10),
        @Indent_CharIndex INT,
        @I                INT = 0,
        @Char             CHAR(1),
        @Indent           VARCHAR(MAX) = '';
 
    -- Trim rows left
    IF (CHARINDEX(@IndentString, @Text, 0) = 1)
        SET @Text = STUFF(@Text, 1, LEN(@IndentString), '');
    SET @Text = REPLACE(@Text, @CRLF + @IndentString, @CRLF);
 
    -- Declare FOREACH(placeholderRow IN @PlaceholderMappings) cursor
    DECLARE
        @Placeholder NVARCHAR(102),
        @Value       NVARCHAR(MAX);
    DECLARE Placeholders_Cursor CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT
        Placeholder = '{' + REPLACE(REPLACE(Placeholder, '{', ''), '}', '') + '}',
        Value       = CASE @EscapeApostrofs
                          WHEN 1
                          THEN REPLACE(Value, '''', '''''')
                          ELSE Value
                      END
    FROM @PlaceholderMappings;
 
    OPEN Placeholders_Cursor;
 
    FETCH NEXT FROM Placeholders_Cursor INTO @Placeholder, @Value;
    WHILE @@FETCH_STATUS = 0 BEGIN
        
        IF (@Debug = 1) AND (CHARINDEX(@Placeholder, @Text) = 0)
            PRINT '--        ==> Could not find Placeholder ' + @Placeholder;
        SET @Text = REPLACE(@Text, @Placeholder, @Value);
 
        FETCH NEXT FROM Placeholders_Cursor INTO @Placeholder, @Value;
    END;
 
    CLOSE Placeholders_Cursor
    DEALLOCATE Placeholders_Cursor
 
    RETURN;
END;
GO
DECLARE
    @SQL NVARCHAR(MAX),
    @PlaceholderMappings PlaceholderMappingType;
 
SET @SQL = '
        SELECT
            F1=''{aaa}'',
            F2=''{bbb}'',
            F3=''{ddd}'';';
INSERT INTO @PlaceholderMappings
VALUES
    ('aaa',   'A''A''A'),
    ('{bbb}', 'BBB'),
    ('ccc',   'CCC');
 
DECLARE @IndentString VARCHAR(MAX) = SPACE(8);
EXEC dbo.sp_ReplacePlaceholders
    @SQL OUTPUT,
    @PlaceholderMappings,
    @IndentString=@IndentString,
    @Debug=1;
PRINT '>>>' + @SQL + '<<<';

To get all list of all script objects (VIEWs, SPs, UDFs) which are depending on some other script object we can use help of sys.sql_expression_dependencies system view:

SELECT
 object_name = OBJECT_NAME(D.referencing_id),
 O.type,
 O.type_desc
FROM
 sys.sql_expression_dependencies D
 INNER JOIN sys.objects O ON D.referencing_id = O.object_id
WHERE
 referenced_entity_name LIKE 'MyStoredProcedure';

Query result looks like this:



Now we can extend this query with navigation through all databases and save it as T-SQL Query Template:

DECLARE
    @referenced_entity SYSNAME = '<referenced_entity, sysname, ???>',
    @referencing_entity SYSNAME = '<referencing_entity, sysname, %>',
    @sql NVARCHAR(MAX) = '
SELECT
    server_name=NULL,
    database_name=NULL,
    object_name=NULL,
    object_type=NULL,
    object_type_desc=NULL
WHERE 1=0';
 
SELECT @sql = @sql + REPLACE(REPLACE(REPLACE(REPLACE('
UNION ALL
SELECT
    server_name = @@SERVERNAME,
    database_name = ''{database_name}'',
    object_name = OBJECT_NAME(D.referencing_id, {database_id}),
    object_type = O.type,
    object_type_desc = O.type_desc
FROM
    {database_name}.sys.sql_expression_dependencies D
    INNER JOIN {database_name}.sys.objects O ON D.referencing_id = O.object_id
WHERE
    D.referenced_entity_name LIKE ''{referenced_entity}''
    AND
    OBJECT_NAME(D.referencing_id, {database_id}) LIKE ''{referencing_entity}''',
    '{database_id}', database_id),
    '{database_name}', name),
    '{referenced_entity}', @referenced_entity),
    '{referencing_entity}', @referencing_entity)
FROM sys.databases
WHERE state=0 /* ONLINE */
ORDER BY name;
 
SET @sql = @sql + '
';
 
EXEC(@sql);

See also MSSQL - Query Templates

MSSQL - Very fast method to get statistic (rows and bytes) for all database tables

Typical way to count all rows in the table ist to send such query:

SELECT COUNT(*) FROM MyTable;

This method has some disadvantages:

1. We can only get a rows count, but not a physical size of table
2. This query works on one table, name of which we have to define explicitly
3. If table has no PRIMARY KEY, then this query can be very slow

There is some other method - to use a statistic from system tables.
This method uses for example this predefined SSMS report:


We can start Profiler (Menu =&rt; Tools =&rt; SQL Server Profiler) and extract for own usage a background query of this report.
I have already extracted this query and reformated as standalone VIEW:

CREATE VIEW dbo.disk_usage_by_table
AS
SELECT TOP(100) PERCENT
    Database_Name = DB_NAME(),
    Schema_Name   = CAST(a3.name AS SYSNAME),
    Table_Name    = CAST(a2.name AS SYSNAME),
    Records       = a1.records,
    Reserved_KB   = (a1.reserved + ISNULL(a4.reserved,0))* 8, 
    Data_KB       = a1.data * 8,
    Indexes_KB    = CASE
                        WHEN (a1.used + ISNULL(a4.used,0)) > a1.data
                        THEN (a1.used + ISNULL(a4.used,0)) - a1.data
                        ELSE 0
                    END * 8,
    Unused_KB     = CASE
                        WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used
                        THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used
                        ELSE 0
                    END * 8
FROM
    (
        SELECT 
            ps.object_id,
            records = SUM(
                        CASE
                            WHEN (ps.index_id < 2)
                            THEN row_count
                            ELSE 0
                        END
                    ),
            reserved = SUM(ps.reserved_page_count),
            data = SUM(
                        CASE
                            WHEN ps.index_id < 2
                            THEN ps.in_row_data_page_count
                               + ps.lob_used_page_count
                               + ps.row_overflow_used_page_count
                            ELSE ps.lob_used_page_count
                               + ps.row_overflow_used_page_count
                        END
                    ),
            used = SUM(ps.used_page_count)
        FROM sys.dm_db_partition_stats ps
        GROUP BY ps.object_id
    ) AS a1
    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id ) 
    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
    LEFT OUTER JOIN (
        SELECT 
            it.parent_id,
            SUM(ps.reserved_page_count) AS reserved,
            SUM(ps.used_page_count) AS used
        FROM sys.dm_db_partition_stats ps
        INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
        WHERE it.internal_type IN (202,204)
        GROUP BY it.parent_id
    ) AS a4 ON (a4.parent_id = a1.object_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY Table_Name;

If we query this VIEW:

SELECT * FROM dbo.disk_usage_by_table;

then we can see that [count rows], [reserved space], [space used for data], [space used for indexes] and [free space] for 106 tables takes less then 1 second:



We can also filter or join this view with other tables/views:

SELECT Records FROM dbo.disk_usage_by_table WHERE Table_Name = 'dim_date';

This method has folowing advantages:

1. Is very very fast
2. Is independent of table names
3. Shows
   • Count of records
   • Reserved space (in KBytes)
   • Space used by data (in KBytes)
   • Space used by indexes (in KBytes)
   • Unused space (in KBytes)

You can use this metod for example in ETL processes to count (outside of transformation) rows and space before and after load.

MSSQL - CHANGE TRACKING - How can I get DateTime of changes?

Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications.
You can get more information on the Microsoft Developer Network web site.

But on these pages you will not find an answer on this question:
How can I get DateTime of changes. You can find this information in the sys.dm_tran_commit_table DMV (Dinamic Management View).

There the complete T-SQL script:

-- Recreate test table
IF OBJECT_ID('dbo.TestCT') IS NOT NULL
    DROP TABLE dbo.TestCT;
GO
CREATE TABLE dbo.TestCT
(
    ID INT IDENTITY(1,1) NOT NULL,
        CONSTRAINT [dbo.TestCT.PK] PRIMARY KEY CLUSTERED (ID),
    Code VARCHAR(10) NOT NULL,
    Description NVARCHAR(100) NULL
);
 
GO
-- Enable CHANGE TRACKING for test table
ALTER TABLE dbo.TestCT ENABLE CHANGE_TRACKING 
 
GO
-- Fill test table with initial rows
INSERT INTO dbo.TestCT(Code, Description)
VALUES
    ('AAA', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.'),
    ('BBB', 'Cras sed tincidunt dolor, in interdum lorem.'),
    ('CCC', 'Vestibulum eget condimentum orci.');
 
GO
-- Wait for 30 seconds
WAITFOR DELAY '00:00:30'
 
GO
-- Change second row in the test table
UPDATE dbo.TestCT
SET Description = '!!! This line was changed !!!'
WHERE Code = 'BBB';
 
GO
-- Show CHANGE TRACKING information
-- directly after initial filling (VersionNumber=0)
SELECT
    CT.ID,
    CT.SYS_CHANGE_VERSION,
    CT.SYS_CHANGE_CREATION_VERSION,
    CT.SYS_CHANGE_OPERATION,
    COMM.commit_time
FROM
    CHANGETABLE(CHANGES dbo.TestCT, 0) CT
    INNER JOIN sys.dm_tran_commit_table COMM ON
        CT.sys_change_version = COMM.commit_ts
ORDER BY
    CT.ID, COMM.commit_time DESC;
 
-- Get initialisation version number
DECLARE @MaxCreationVersion INT = (
    SELECT TOP(1) SYS_CHANGE_CREATION_VERSION
    FROM CHANGETABLE(CHANGES dbo.TestCT, 0) CT
);
 
-- Show changes directly after initialisation
SELECT
    CT.ID, CT.SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,
    COMM.commit_time
FROM
    CHANGETABLE(CHANGES dbo.TestCT, @MaxCreationVersion) CT
    INNER JOIN sys.dm_tran_commit_table COMM ON
        CT.sys_change_version = COMM.commit_ts
ORDER BY
    CT.ID, COMM.commit_time DESC;

Execution result:

MSSQL - Performance tuning of Scalar-Value User Defined Functions (UDFs)

In most of cases the scalar-value User Defined Functions (Scalar UDF) are very slow.
It is enough to move the scalar expression in to UDF and query performance will be several times reduced.
Let us check ...

For testing, we need a test table with the dates from about a thousand years:

-- Recreate test table
IF OBJECT_ID('dbo.TestUDFs') IS NOT NULL
    DROP TABLE dbo.TestUDFs;
GO
CREATE TABLE dbo.TestUDFs(
    Datum Date
);
GO
-- SET NOCOUNT ON to prevent extra log messages returned as part of the result set:
-- (See: https://msdn.microsoft.com/en-us/library/ms189837%28v=sql.110%29.aspx)
SET NOCOUNT ON;
-- Fill test-table
DECLARE
    @Date Date = GetDate(),
       @i INT  = 0;
WHILE @i < 1000 * 365 BEGIN
    SET @Date = DATEADD(day, 1, @Date);
    INSERT INTO dbo.TestUDFs VALUES (@Date);
    SET @i = @i + 1;
END;
-- restore NOCOUNT
SET NOCOUNT OFF;
GO
SELECT CNT = COUNT(*) FROM dbo.TestUDFs;
GO

---

We will create four different UDFs:

FIRST:
Scalar-value UDF, which uses variables for intermediate calculations.
The body of function is placed into BEGIN-END container.

-- Recreate scalar-value UDF with variables in body
IF OBJECT_ID('dbo.fn_scalar_with_vars') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_with_vars;
GO
CREATE FUNCTION dbo.fn_scalar_with_vars (@date DATETIME)
RETURNS INT
AS BEGIN
    DECLARE @DateKey INT;
    SELECT @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @date, 112));
    RETURN @DateKey;
END;
GO
SELECT dbo.fn_scalar_with_vars(GetDate());
GO

SECOND:
Scalar-value UDF, which does not use intermediate calculations, and almost immediately returns the result of the calculation.
The body of function is placed into BEGIN-END container.

-- Recreate scalar-value UDF with direct expression (without variables) in body
IF OBJECT_ID('dbo.fn_scalar_direct_expression') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_direct_expression;
GO
CREATE FUNCTION dbo.fn_scalar_direct_expression (@date DATETIME)
RETURNS INT
AS BEGIN
    RETURN CONVERT(INT, CONVERT(VARCHAR(8), @date, 112));
END;
GO
SELECT DateKey = dbo.fn_scalar_direct_expression(GetDate());
GO

Scalar-value UDF which returns not scalar value, but a table-result.
For data storage uses UDF a table variable.
The body of function is placed into BEGIN-END container.

-- Recreate table-value UDF which returns table-variable
IF OBJECT_ID('dbo.fn_non_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_non_inline_table;
GO
CREATE FUNCTION dbo.fn_non_inline_table (@date DATETIME)
RETURNS @DateKey TABLE(DateKey INT)
AS BEGIN
    INSERT INTO @DateKey
    VALUES (CONVERT(INT, CONVERT(VARCHAR(8), @date, 112)));
RETURN;
END
GO
SELECT DateKey FROM dbo.fn_non_inline_table(GetDate());
GO

Finally the UDF, which returns a non-scalar value, but a table.
There is no any BEGIN-END container, and the expression is placed immediately after the AS expression.
Such UDFs are called "Inline Table-Value UDF".

-- Recreate inlide table-value UDF
IF OBJECT_ID('dbo.fn_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_inline_table;
GO
CREATE FUNCTION dbo.fn_inline_table (@date DATETIME)
RETURNS TABLE
AS
    RETURN SELECT DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @date, 112));
GO
SELECT DateKey FROM dbo.fn_inline_table(GetDate());
GO

---

Let us test the direct evaluation of the expression in the query, and calls of all four UDFs.
Tests were performed on a virtual machine running on my NoteBook-e.
On other computers the results may differ.

The test script is defined so:
- in the first step we will store the current date and time in the variable
- for each row of the test table we will performed a calculation
- the test request substitute this text: / * Put calculation there * /.
We will count the number of milliseconds between start and time.

-- Script to test calculations
DECLARE
    @DateKey INT,
    @StartedAt DATETIME2 = GETDATE(); 
 
/* Put calculation there */
 
PRINT 'Elapsed time (Direct calculation): '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';

Calculation directly in query:
300 ms.

SELECT @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), Datum, 112))
FROM dbo.TestUDFs;

Scalar-value UDF with variables and body:
1570 ms.

SELECT @DateKey = dbo.fn_scalar_with_vars(Datum)
FROM dbo.TestUDFs;

Scalar-value UDF with direct expression and body:
1274 ms.

SELECT @DateKey = dbo.fn_scalar_direct_expression(Datum)
FROM dbo.TestUDFs;

Non-inline table-value UDF with table-variable and body:
29623 ms.

SELECT @DateKey = DateKey
FROM dbo.TestUDFs CROSS APPLY dbo.fn_non_inline_table(Datum);

And now surprise - Inline table-value UDF:
210 ms!!! - at least the same result as in the first case, where a computation request occurs immediately.

SELECT @DateKey = DateKey
FROM dbo.TestUDFs CROSS APPLY dbo.fn_inline_table(Datum);

---

All-in-one Script (an execution takes about one minute):

-- SET NOCOUNT ON to prevent extra log messages:
-- See: https://msdn.microsoft.com/en-us/library/ms189837%28v=sql.110%29.aspx
SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.TestUDFs') IS NOT NULL
    DROP TABLE dbo.TestUDFs;
CREATE TABLE dbo.TestUDFs(
    [Date] Date
);
-- Fill test-table
DECLARE
    @Date Date = GetDate(),
       @i INT  = 0;
WHILE @i < 1000 * 365 BEGIN
    SET @Date = DATEADD(day, 1, @Date);
    INSERT INTO dbo.TestUDFs VALUES (@Date);
    SET @i = @i + 1;
END;
-- Check if table is filled
DECLARE @CNT INT = (SELECT COUNT(*) FROM dbo.TestUDFs);
PRINT 'Rows in TestUDFs table: ' + CAST(@CNT AS VARCHAR(20));
GO
IF OBJECT_ID('dbo.fn_scalar_with_vars') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_with_vars;
GO
CREATE FUNCTION dbo.fn_scalar_with_vars (@Date DATETIME)
RETURNS INT
AS BEGIN
    DECLARE @DateKey INT;
    SELECT @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112));
    RETURN @DateKey;
END;
GO
IF OBJECT_ID('dbo.fn_scalar_direct_expression') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_direct_expression;
GO
CREATE FUNCTION dbo.fn_scalar_direct_expression (@Date DATETIME)
RETURNS INT
AS BEGIN
    RETURN CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112));
END;
GO
IF OBJECT_ID('dbo.fn_non_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_non_inline_table;
GO
CREATE FUNCTION dbo.fn_non_inline_table (@Date DATETIME)
RETURNS @DateKey TABLE(DateKey INT)
AS BEGIN
    INSERT INTO @DateKey
    VALUES (CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112)));
RETURN;
END
GO
IF OBJECT_ID('dbo.fn_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_inline_table;
GO
-- Recreate scalar-value UDF with direct expression (without variables) in body
IF OBJECT_ID('dbo.fn_scalar_direct_expression') IS NOT NULL
    DROP FUNCTION dbo.fn_scalar_direct_expression;
GO
CREATE FUNCTION dbo.fn_scalar_direct_expression (@Date DATETIME)
RETURNS INT
AS BEGIN
    RETURN CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112));
END;
GO
-- Recreate table-value UDF which returns table-variable
IF OBJECT_ID('dbo.fn_non_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_non_inline_table;
GO
CREATE FUNCTION dbo.fn_non_inline_table (@Date DATETIME)
RETURNS @DateKey TABLE(DateKey INT)
AS BEGIN
    INSERT INTO @DateKey
    VALUES (CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112)));
RETURN;
END
GO
-- Recreate inlide table-value UDF
IF OBJECT_ID('dbo.fn_inline_table') IS NOT NULL
    DROP FUNCTION dbo.fn_inline_table;
GO
CREATE FUNCTION dbo.fn_inline_table (@Date DATETIME)
RETURNS TABLE
AS
    RETURN SELECT DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @Date, 112));
GO
/********** Test all methods **********/
DECLARE
    @DateKey INT,
    @StartedAt DATETIME2 = GETDATE();
 
PRINT '
Calculation directly in query:'
SET @StartedAt = GETDATE();
SELECT @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), [Date], 112))
FROM dbo.TestUDFs;
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';
 
PRINT '
Scalar-value UDF with variables and body:'
SET @StartedAt = GETDATE();
SELECT @DateKey = dbo.fn_scalar_with_vars([Date])
FROM dbo.TestUDFs;
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';
 
PRINT '
Scalar-value UDF with direct expression and body:'
SET @StartedAt = GETDATE();
SELECT @DateKey = dbo.fn_scalar_direct_expression([Date])
FROM dbo.TestUDFs;
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';
 
PRINT '
Non-inline table-value UDF with table-variable and body:'
SET @StartedAt = GETDATE();
SELECT @DateKey = DateKey
FROM dbo.TestUDFs CROSS APPLY dbo.fn_non_inline_table([Date]);
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';
 
PRINT '
Inline table-value UDF:'
SET @StartedAt = GETDATE();
SELECT @DateKey = DateKey
FROM dbo.TestUDFs CROSS APPLY dbo.fn_inline_table([Date]);
PRINT '    Elapsed time: '
    + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';
 
GO
-- Restore SET NOCOUNT OFF
SET NOCOUNT OFF;

An execution of this Script generates this result message:

Rows in TestUDFs table: 365000
 
Calculation directly in query:
    Elapsed time: 300 ms
 
Scalar-value UDF with variables and body:
    Elapsed time: 1570 ms
 
Scalar-value UDF with direct expression and body:
    Elapsed time: 1274 ms
 
Non-inline table-value UDF with table-variable and body:
    Elapsed time: 29623 ms
 
Inline table-value UDF:
    Elapsed time: 210 ms