ttmind

Main Navigation

ttmind
  • jim-jams
  • Tech
  • Positive
Login

Login

Facebook Google

OR

Remember me Forgot password?

Don't have account? Signup here.

Sort by Categorys

.Net

PHP

Java

JavaScript

Database

Server

Client Side

Tools

Artificial Intelligence

Cloud

Hybrid Development

Event

Smart City

Education

Security

Scrum

Digital Marketing

APP Development

Business

Internet

Simulation

Art

Network

Microservices

Architecture

Technology

Leadership

    Top Articles

  • How Does Social Media Bring People Together?
    TTMind Author
  • How to read appSettings JSON from Class Library in ASP.NET Core
    Anil Shrestha
  • Printing Support In Asp.Net Core
    TTMind Author
  • HOW TO EXTRACT TEXT FROM IMAGE USING JAVASCRIPT (OCR with Tesseract.js)?
    Prakash Pokhrel
  • Images Upload REST API using ASP.NET Core
    Prakash Pokhrel
  • Related Topic

  • Importance of database Constraints
  • Real life example of functional dependency, multivalued dependency, trivial functional dependency and non-trivial functional dependency.
  • MongoDb With Asp.Net Core Web API
  • DBMS and most popular DBMS Software in 2019
  • Creating .NET Core Console Application with NoSQL- MongoDB At Back-end
  • Tech
  • About Us
  • Contact Us
  • TechHelp
  • PositiveHelp
  • Jim-Jams Help
  • Terms & Conditions

© Copyright ttmind.com

Main Content

SQL Server 2019 performence improvement in Scaler User Defined Functions

Database MS SQL about 6 years ago || 6/16/2019 || 4.0 K View

  • Hide

As a developer when we have to perform complex work in stored procedure many of us use user defined functions to centralize complex logic and get a proper result, but we all know when we use user-defined functions we get stuck with a performance problem. With MS SQL 2019 preview now we got better performance with UDF inlining. A developer I am happy that MS SQL now has this great improvement in MS SQL 2019.

So in this post, I am trying to guide who is using UDF as well as who is starting work on MS SQL as developer or DBA too. So starting with the basic definition what is User Defined Functions.

MS SQL 2019

What is User Defined Functions?

A UDF is a SQL statement (T-SQL) which returns a single data value is termed as Scalar UDF. UDF helps in building a complex logic without writing complex SQL queries.

Why Performance of Scalar UDF is poor some times,

  • Iterative invocation
  • LAck of costing
  • Interpreted execution
  • Serial execution

To improve this now we have automatic inlining of UDF

This new feature of auto inlining, our UDFs are automatically get transformed into a scalar expression or scalar subqueries and substituted in the calling query in place of the UDF operator.

But to have any auto inlining our UDF must fallow some requirement as per docs.microsoft.com

  • A scalar T-SQL UDF can be inline if all of the following conditions are true:
    • The UDF is written using the following constructs:
    • DECLARE, SET: Variable declaration and assignments.
    • SELECT: SQL query with single/multiple variable assignments1.
    • IF/ELSE: Branching with arbitrary levels of nesting.
    • RETURN: Single or multiple return statements.
    • UDF: Nested/recursive function calls2.
    • 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 effects3 (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 reference a scalar UDF call in its 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.

Even we can check whether our UDF can be inlined or not By using sys.sql_modules

The result shows is it can be inlined or not. A value of 1 indicates that it is inalienable, and 0 indicates for not.

How to Enable scalar UDF inlining

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

After running this query we do not have to make any changes in our works, which is great.

How to Disable Scalar UDF inlining without changing the compatibility level

 

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

MS SQL 2019 seems well improved for performance as well as well supported for the docker and Linux, which is great if we want to use ms SQL 2019 in Linux and DevOps during development. This post is just for showing interesting features of MS SQL 2019, I am doing some tests on a few features will post them soon too. Also please visit What's new in SQL Server 2019 preview to know more about.

  • 0
  • 0
  • 0
    • Facebook
    • Twitter
    • Google +
    • LinkedIn

About author

Alok Pandey

Alok Pandey

Love to share what I know.

Reset Your Password
Enter your email address that you used to register. We'll send you an email with your username and a link to reset your password.

Quick Survey