Scalar UDF Inlining (SQL 2019)- What is it and how do we know if we can use it?

I have been pretty excited about the new SQL 2019 feature “Scalar UDF inlining”. I want to share what I learned about Scalar UDF inlining because it's awesome. It makes functions easier to implement without becoming a plague to performance, thus functions will be used more often (finally).

Scalar UDF inlining in SQL 2019 simply makes most (non-inlined) UDF’s execute faster. The SQL 2019 query engine interprets the scalar UDF’s logic and makes it set-based (inlined), ready to take advantage of the performance improvements that come with set based processing.

So how do we know if an existing function would be able to use this new feature?

In my opinion, the easiest way to know if your function is able to take advantage of this is to simply query the sys.sql_modules view (SQL 2019). The sys.sql_modules catalog view includes a property called “is_inlinable”, which indicates whether a UDF is inlinable or not.
·         A value of 1 indicates that it is inlinable, and 0 indicates otherwise
·         This property will also have a value of 1 for inline table-valued functions, since they are inlinable by definition.

In my next blog post I will cover how to manually change scalar UDFs into an inlinable (for those that won’t be going to 2019 any time soon).

Reference:
Microsoft has a large list of requirements that let you know if a Scalar UDF can be inlined:

(Source: Microsoft)

A scalar T-SQL UDF can be inline if all of the following conditions are true:

·         The UDF is written using the following constructs:

o    DECLARE, SET: Variable declaration and assignments.

o    SELECT: SQL query with single/multiple variable assignments¹.

o    IF/ELSE: Branching with arbitrary levels of nesting.

o    RETURN: Single or multiple return statements.

o    UDF: Nested/recursive function calls².

o    Others: Relational operations such as EXISTS, ISNULL.

·         The UDF does not invoke any intrinsic function that is either time-dependent (such as GETDATE()) or has side effects³ (such as NEWSEQUENTIALID()).

·         The UDF uses the EXECUTE AS CALLER clause (the default behavior if the EXECUTE AS clause is not specified).

·         The UDF does not reference table variables or table-valued parameters.

·         The query invoking a scalar UDF does not reference a scalar UDF call in its GROUP BY clause.

·         The query invoking a scalar UDF in its select list with DISTINCT clause does not have ORDER BY clause.

·         The UDF is not used in ORDER BY clause.

·         The UDF is not natively compiled (interop is supported).

·         The UDF is not used in a computed column or a check constraint definition.

·         The UDF does not reference user-defined types.

·         There are no signatures added to the UDF.

·         The UDF is not a partition function.

¹ SELECT with variable accumulation/aggregation (for example, SELECT @val += col1 FROM table1) is not supported for inlining.

² Recursive UDFs will be inlined to a certain depth only.

³ Intrinsic functions whose results depend upon the current system time are time-dependent. An intrinsic function that may update some internal global state is an example of a function with side effects. Such functions return different results each time they are called, based on the internal state.

SSIS Script Task, using C#, to Download Files w/ variable Security Protocols

I have a SSIS package that I use to download files from various websites. I began by using a C# script published by Tom Archer - MSFT on https://www.codeguru.com/columns/dotnettips/article.php/c7005/Downloading-Files-with-the-WebRequest-and-WebResponse-Classes.htm. I had to modify this script because certain security protocols were required for some sites but not others. I've included my modifications below so that Security Protocols can be controlled based on variables I pass to the Script Task. My resulting code is as follows:

public static int DownloadFile(String remoteFilename,
                                String localFilename,
                                String securityProtocolType)
{
    //Make sure to use the following namespaces:
    //using System.Net;
    //using System.IO;
    
    // Function will return the number of bytes processed
    // to the caller. Initialize to 0 here.
    int bytesProcessed = 0;

    // Assign values to these objects here so that they can
    // be referenced in the finally block
    Stream remoteStream = null;
    Stream localStream = null;
    WebResponse response = null;

        if (securityProtocolType == "Tls12") //Added if statement since some downloads will fail with Tls12 selected
        { ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072; } // 3072 = TLS 1.2 Security Protocol

    // Use a try/catch/finally block as both the WebRequest and Stream
    // classes throw exceptions upon error
    try
    {
        // Create a request for the specified remote file name
        WebRequest request = WebRequest.Create(remoteFilename);
        if (request != null)
        {
            // Send the request to the server and retrieve the
            // WebResponse object 
            response = request.GetResponse();
            if (response != null)
            {
                // Once the WebResponse object has been retrieved,
                // get the stream object associated with the response's data
                remoteStream = response.GetResponseStream();

                // Create the local file
                localStream = File.Create(localFilename);

                // Allocate a 1k buffer
                byte[] buffer = new byte[1024];
                int bytesRead;

                // Simple do/while loop to read from stream until
                // no bytes are returned
                do
                {
                    // Read data (up to 1k) from the stream
                    bytesRead = remoteStream.Read(buffer, 0, buffer.Length);

                    // Write the data to the local file
                    localStream.Write(buffer, 0, bytesRead);

                    // Increment total bytes processed
                    bytesProcessed += bytesRead;
                } while (bytesRead > 0);
            }
        }
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
    }
    finally
    {
        // Close the response and streams objects here 
        // to make sure they're closed even if an exception
        // is thrown at some point
        if (response != null) response.Close();
        if (remoteStream != null) remoteStream.Close();
        if (localStream != null) localStream.Close();
    }

    // Return total bytes processed to caller.
    return bytesProcessed;
}

/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
    int read110 = DownloadFile(Dts.Variables["User::rSourceFullName"].Value.ToString(), Dts.Variables["User::rDestinationFullName"].Value.ToString(), Dts.Variables["User::rSecurityProtocolType"].Value.ToString());

    //if no bytes are processed return error
    if (read110 > 0) { Dts.TaskResult = (int)ScriptResults.Success; }
    else { Dts.TaskResult = (int)ScriptResults.Failure; }
   
}

PASS Summit Tips for First Timers

I volunteered for the pass buddy program and shared a list of tips with them for attending PASS Summit 2017.


Tip #1- if your staying near the event I would not suggest a rental car. You can catch the shuttle to/from the airport & Uber to other places you might want to go.

Tip #2- Sign up for after parties before you head down. This is a good time to meet other people.

Tip #3- Have a flexible schedule. Try to skip one time slot (maybe a slot where nothing seems overly interesting to you) to visit the vendor area (it’ll be less crowded). Also a session you start to attend may not be exactly what you expected, have a back up session you can go to.

Tip #4- Skip another time slot and talk to the Microsoft team that is on sight, they can give you great job specific advice on a project you may be working on or planning to implement.

Tip #5- Don’t bring a laptop, unless needed. Do you really want to lug it around all day? All sessions are recorded so you can reference them (as long as you purchased the recordings) *note it takes a bit of time (1-2 months) to receive the recordings*

Tip #6- Make sure your luggage has room for all the swag you end up picking up.

How to turn off IntelliSense in SQL Server Management Studio

IntelliSense in SQL Server Management Studio is beneficial to some, but irritating to others. For me, when I type out a line of simple code, IntelliSense will spam me with suggestions and actually cause my script to become gibberish if I ignore the suggestions. The suggestions will automatically be selected if I type too fast and don’t hit the escape key every time a seemingly meaningless IntelliSense option is selected. I will note that in SQL 2014 IntelliSense has become better, and I do use it now; however, while working in 2008R2 it was a major PIA and I always disabled it. Here is the way to turn it off, for good, without having to hit the IntelliSense Button in the SQL Editor Toolbar every time you load SQL.

IntelliSense Button in the SQL Editor Toolbar

Turn off IntelliSense

·         From SSMS go to the Tools Menu, select Options

·         Under Text Editor, Transact-SQL, select IntelliSense

·         If Enable IntelliSense is selected, clear the checkbox and hit OK.

Using INTERSECT and EXCEPT to compare records

I was asked for a simple way to compare two tables that should be identical and have the same schema. I provided the following scripts to show records that are identical (Intersect) and different (Except):


IF OBJECT_ID(N'dbo.test1') IS NOT NULL
    DROP TABLE test1
CREATE TABLE dbo.test1 (name VARCHAR(20), dob DATETIME)

IF OBJECT_ID(N'dbo.test2') IS NOT NULL
    DROP TABLE test2
CREATE TABLE dbo.test2 (name VARCHAR(20), dob DATETIME)

INSERT INTO test1 (name, dob)
        VALUES ('Fred','20010102')
INSERT INTO test1 (name, dob)
        VALUES ('Fred1','20010103')
INSERT INTO test1 (name, dob)
        VALUES ('Fred2','20010104')
INSERT INTO test1 (name, dob)
        VALUES ('Fred3','20010105')
INSERT INTO test1 (name, dob)
        VALUES ('Fred4','20010106')
INSERT INTO test1 (name, dob)
        VALUES ('Fred5','20010107')
INSERT INTO test2 (name, dob)
        VALUES ('Fred','20010102')
INSERT INTO test2 (name, dob)
        VALUES ('Fred2','20010104')
INSERT INTO test2 (name, dob)
        VALUES ('Fred3','20010105')
INSERT INTO test2 (name, dob)
        VALUES ('Fred7','20010106')
INSERT INTO test2 (name, dob)
        VALUES ('Fred5','20010107')
    
--exist and are the same in both tables test 1, not in test 2
SELECT name, dob
FROM dbo.test1
INTERSECT
SELECT name, dob
FROM dbo.test2

--exists in test 1, does not exist in test 2
SELECT name, dob
FROM dbo.test1
EXCEPT
SELECT name, dob
FROM dbo.test2

--exists in test 2, does not exist in test 1
SELECT name, dob
FROM dbo.test2
EXCEPT
SELECT name, dob
FROM dbo.test1


As you can see by running the script above, intersect shows values from the first query where there are identical record matches in the second query. Except shows values in the first query where there are No identical record matches in the second query. Additional benefit is that it also includes null values in the comparison, you do not get the same functionality with null values on joins (when you just enter the column names) from 2 tables (without adding specific null handling).
 

CheckSum is pretty damn accurate

I am at a SSIS Masters course with Jorge Segarra and Brian Knight (Pragmatic Works). An attendee brought up an issue about checksum not being 100% accurate, which we all acknowledge; however, another attendee said it is only 99% accurate when checking for differences. I know it's not even close to 1% inaccurate so I wrote a quick script to attempt find an inaccurate checksum value via brute force. I ran the script below for 15 minutes, checking over a quarter of a billion random values in which I found 0 invalid matches. If someone wants to run this longer and change the bigint to a numeric, let me know how many attempts until you find a record.

Summary: checksum is pretty damn accurate (not 100%, but pretty close).


DECLARE @value1a VARCHAR(36)
DECLARE @value2a VARCHAR(36)
DECLARE @value1b VARCHAR(36)
DECLARE @value2b VARCHAR(36)

DECLARE @attempt bigint
SET @attempt = 0


   SET @value1a = NEWID()
   SET @value2a = NEWID()
   SET @value1b = NEWID()
   SET @value2b = NEWID()

WHILE checksum(@value1a, @value2a) <> checksum(@value1b, @value2b)
BEGIN
   SET @attempt = @attempt + 1
   SET @value1a = NEWID()
   SET @value2a = NEWID()
   SET @value1b = NEWID()
   SET @value2b = NEWID()
   IF @attempt%100000 = 0 PRINT @attempt
END

SELECT @attempt
SELECT @value1a value1a, @value2a value2a, checksum(@value1a, @value2a) chk_a
SELECT @value1b value2a, @value2b value2b, checksum(@value1b, @value2b) chk_b



Make a SQL Server Shortcut to Change Database Connections

In SSMS, to change databases most people do one of the following:
1. Click the Change Connection toolbar button
2. Right click anywhere in the query pane -> Connection -> Change Connection
3. Go to the query menu -> Connection -> Change Connection (Alt+Q, C, H)

The change connection menu button actually does have a shortcut key predefined (Alt+H); however this action has a lower priority than the (Alt+H) shortcut for the Help Menu. Thanks Microsoft! I'll show you how to enable this the Change Connection action using (Alt+G).

The work around is actually quite simple, but it is not very intuitive.

• Right click anywhere on the toolbar and select Customize
• Ignore (but don't close this yet) the Customize window that pops up
• Right Click on the Change Connection toolbar button and select "Image & Text"
• This enables the hotkey to be use inside the query window but you can not activate it yet because Help Menu shortcut (Alt + H) still has precedence.
• Right Click on the Change Connection toolbar button again
• Change the Name field from "C&hange Connection..." to "Chan&ge Connection..."
• Close the Customize window that popped up earlier.

Now you can use the Alt + G keyboard shortcut to change connections.