In this session, we learn about creating and calling multi-statement table-valued functions. And finally, we’ll also look at the differences between inline and multi-statement table-valued functions. Before continuing with the session, I strongly recommend reading these two articles.
How to create a multi-statement table valued function
Multis Statement Table-valued functions are very much similar to inline table-valued functions, with very few differences between them.
First, let’s look at an example of creating an inline and multi-statement table-valued function, and then we’ll find the differences between them. I have this TblCustomers table which is got Id, Email, PhoneNo, FirstName, LastName, and countryId columns.
CREATE TABLE [dbo].[TblCustomers]( [Id] [int] IDENTITY(1,1) NOT NULL, [Email] [nvarchar](max) NOT NULL, [PhoneNo] [nvarchar](14) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [CountryId] [int] NULL, ) GO SET IDENTITY_INSERT [dbo].[TblCustomers] ON GO INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (1, 'email@example.com', '2827528619', 'David', 'Kattah', 1) GO INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (17, 'firstname.lastname@example.org', '0557161755', 'Mame', 'Adjei', 90) GO INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (18, 'email@example.com', '0553474280', 'Dzifa', 'Adabla', 90) GO INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (19, 'firstname.lastname@example.org', '0244072176', 'Phyllis ', 'Adu', 90) GO INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (20, 'email@example.com', '0545196619', 'David', 'Nikoi', 90) GO INSERT [dbo].[TblCustomers] ([Id], [Email], [PhoneNo], [FirstName], [LastName], [CountryId]) VALUES (21, 'firstname.lastname@example.org', '98979495', 'Ankiish', 'Thapliyal', 90) GO SET IDENTITY_INSERT [dbo].[TblCustomers] OFF GO
Now what we want to do is we want to create inline and multi-statement table-valued functions that give us the same output that you can see on the below image. I just want the name and Email.
Create Function InlineFn_GetAllUsers() Returns Table as RETURN (Select FirstName, Email from TblCustomers)
we are creating an inline table-valued function and we have spoken about inline table-valued functions extensively in inline table-valued functions post.
so to create function we have used create function and then function name, returns keyword.we know that a function can take parameters and it should return a value.
so our function is not taking any parameters and it’s returning a table. And then we have ‘As’ you would return whatever is the select statement returning, here The select statement is selecting name and email from table TblCustomers table.
So whatever the select statement is retrieving from TblCustomers table that is being returned by the our function.
so this is an inline table-valued function.
- And if you look at the inline table-valued function after returns, you have only the table keyword. You’re not specifying the structure of the table that gets returned from this inline table-valued function.
- And another thing to note here is that this function doesn’t have a begin and end clause.
Create Multi-statement table valued function
Create Function MultiFn_GetAllUsers() Returns @Table Table (FirstName nvarchar(50), Email nvarchar(100)) as Begin Insert into @Table Select FirstName,Email From TblCustomers Return End
Let’s look at the multi-statement table value function. whether if it is inline table-valued function or scalar function or multi-statement table-valued function, You always use create function statement to create a function. so create function and then the name of the function here MultiFn_GetAllUsers.
And then I’m saying returns @table. So I’m creating a table variable . so @table is the name of the variable and its data type is a table. And obviously, if you’re creating a table variable, a table variable has to have, the column names and the data types.
So we are creating a table and specify the name of the column and its data type.
@Table Table (FirstName nvarchar(50), Email nvarchar(100))
You are saying, I am returning a table with this structure which has got FirstName and Email.
Multi-statement Table Valued Function vs Inline Table Valued Function
- In inline table-valued function. You don’t specify the structure of the table, but in multi-statement table-valued function, you do specify the structure of the table.
- And then ‘As’ and then you have this begin and end block. But if you look at the Inline table-valued function, you don’t have that block,And within that begin an end block, you have the body of the function.
- You can use an inline table-valued function as in updated, and deleted from – just like we can use a view with Similar limitations i.e can’t update functions using aggregates, can’t update calculated columns, etc. But you can use the Multi-statement Table function.
- Now if talk about the performance SQL Server uses an inline table-valued function as a view and a multi-statement table-valued function like a stored procedure.
Whenever we use an inline table-valued function in the outer query, the query processor read the User defined-function definition and creates an execution plan that uses the underlying objects table, using the indexes on the table.
In the case of a multi-statement table-valued function, an execution plan is generated for the function itself and stored in the execution plan cache when the function is executed the first time.
If we use multi-statement table-valued functions in larger SQL queries then the optimizer does not know what the multi-statement function will return, So it assumes that the function will return a single row and that the returns of the function will be accessed by using a table scan against a table with a single row instead of using indexes.
So multi-statement table-valued functions can perform worst is in case of a large number of rows.
what are you doing here? Here you are inserting data into this table variable. You’re saying insert into this table variable.
So what do you want to insert into this table variable. You want to insert the name and email from the TblCustomers table. So you are retrieving all these records from TblCustomers tables. You’re basically retrieving name and email columns and then inserting those rows into @table variable.
Insert into @Table
If you look at the table variable, it has got the name and email columns.so you are inserting that into this table and finally, you’re saying return
So when this function encounters this return Keywood, what’s going to happen? This function will return this table.
whatever you have inserted into this at table variable gets returned to the caller who was called to your function, he will get the data that is present in this table.
The post How to create a multi-statement table valued function appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Concatenate column values for rows with the same values (of different columns)
- Adding time that totals more than 24 hrs in SQL
- Dynamically generate the decimal points
- How can I debug a SQL Server trigger that is giving error "Conversion failed when converting date and/or time from character string"?
- Select 50 random files per day
- Parsing a JSON to meet minimum requirements inside a stored procedure
- Generate a result that contains multiple values from one column by WHERE
- Insert an object into a JSON array in SQL Server
- the multi part identifier yyy could not be bound (inner-join)
- Writing browser-based MMO server in Python
- Sql Server Displaying Items in specific order
- MS Access call SQL Server stored procedure
- Sql Server to Excel via a Web Service
- grant IIS APPPOOL wrecked my SQL Server windows authentication
- Use String parameter for RegEx in query
- Can SELECT expressions sometimes be evaluated for rows not matching WHERE clause?
- ServiceStack Ormlite transaction between services
- What can I do to improve performance of my pure User Defined Function in SQL Server?
- Simple functions in queries
- SQL Server interval numbers
- stored procedures with sqlAlchemy
- Conversion failed when converting the varchar value 'AL-DMK2_1' to data type int
- How to rollback if any update is not success?
- Is OK to enclosing all values in SQL statement with single quotes?
- Temp table over multiple ADOQueries
- nested WITH statement
- What are the drawbacks of foreign key constraints that reference non-primary-key columns?
- Have this problem with calling stored procedure unknown object type
- How to get machine name from ip address in SQL Server 2008?
- MSSQL datetime object won't echo out the correct date if i remove its var_dump
- SQL Server CASE returns NULL even when there are values
- SQL Server : view MUCH slower than same query by itself
- MS SQL 2012 - Divide by zero error encountered
- What are hypothetical indexes?
- Can you parameterize sort order in a sql server prepared statement?
- How can I fix CA2100 Review SQL queries for security vulnerabilities issue
- How can i add a table in my existing database without losing data in Entity-framework Code first 5.0 ?
- Representing SQL results in single row - ending up with stair-steps
- Can you call a WCF service from an SQL Server 2008 query?
- Query ELMAH's XML field
- SELECT query with CASE condition and SUM()
- MVC 5 unable to create controller with Linq to SQL class
- How to do the subtract function in sql script
- Is there metadata in SQL Server to determine the date/time of the last update?
- Count of Instances for One Attribute Based on Several Other Attributes
- Select rows that contain both English and non-English characters
- When Executing proc: Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server
- Check row existence in SQL Server with PowerShell
- SQL Server Count is slow
- Can I order the results of a concatenation in tsql?
- Difference between @@ and @ in SQL Server 2008 R2
- Is it possible to issue CREATE statements using sp_executesql with parameters?
- Multiple variables assignment using SET and SELECT
- execute SSIS or DTS package asynchronously from ASP.NET
- how to use if condition in cursor at time of declaration in sql server 2008?
- Grouping SSRS results by cell background color
- Difference between .NET, OLEDB, and Native Providers in SSIS
- The data type is DT_NTEXT, which is not supported with ANSI files
- Returning rows with same key when searching only one of them
- SQL TRY-CATCH and the missing columns
- Finding the number of concurrent days two events happen over the course of time using a calendar table
- How can I create a stored procedure for a log table
- For Each equivalent in SQL
- SQL Query: Grouping by an ntext field
- Double curly braces on SET operation sql
- Many to Many and Composite Primary Key and Foreign Key
- What is "MISCELLANEOUS" in sys.dm_os_wait_stats?
- Identity key on varchar column - T-SQL
- Unable to run an XMLQuery on a XML statement
- Change Auto-Increment, Primary Key field from SMALLINT to INT in SQL Server
- Cannot connect to SQL SERVER 2000 with php
- Mule 3.6 with JTDS insertion in to SQl Server
- What is the equivalent of varchar(max) in Oracle?
- SQL Server always encrypted database insert is working when logged on with user account but not when running through a proxy user
- .NET core support for SqlFileStream or any alternative way to get a filestream out of SQL Server using C#?
- Is there a way to remove '_' from column name while selecting * in sql statement?
- How to use split.item in SQL, spliting cols by commas
- adding a constraint to a column referenced to another table's column
- Do cubes use resources outside of the SQL Server Analysis Server?
- Get latest record for each day for last n days using MS Sql Server
- Row_num and Partition by DATE?
- SQL Server - Select daily values for each month
- How to read a complex json array with SQL Server
- What are the Steps to Upload a Document File from Windows Application to SQL Server DB?
- Where can I find friendly strings for SQL Server versions?
- SQL Server different select statement on same table and different result
- Oracle equivalent of T-SQL SET @var = @var + ' ';
- Incrementing partial PK
- Update all rows in a table based on a condition
- Using T-SQL is there a way to show the name of a database being backed up or restored?
- Stored procedure doesn't allow null parameters even the parameter represent accept nulls?
- Include default constraint names in generation of update script
- Query execution time in Entity Framework vs in SQL Server
- how to use a bit type variable as precondition for a criteria in where clause in SQL server
- SQL query to merge two aggregated subsets from the same table
- Sql Server Reporting Service (SSRS) 2005 Operation timeout issue
- Basic questions on setting up an access controlled intranet site - ASP .NET
- Swapping two rows in MS SQLServer retaining the original primary key and without manual update
- python and Django export mssql query to excel
- SQL Server : sort based on current date for last 6 months
- Running SQL Server on the Web Server
- SQL Server - Repeat Characters after nth row
- Delete part of a field in SQL
- Insert record into Multiple table from single excel sheet using SSIS package
- How to use case here
- find number of open connection on database
- In SQL Server Management Studio 2014, the New Trigger menu option is disabled
- Can a SQL "DELETE" statement followed by a "WHERE NOT IN" statement be in same transaction?
- Get more than million rows from SQL Server table
- Write queries in SQL server and Parallel.Invoke
- ADO.NET: What API I can use to get message when SET NOCOUNT OFF
- Creating SQL Server Database files on the fly with C#
- Data quality Services Internal behaviour
- row numbering based on the number of rows returned by query
- Calculate start date and end date when Fiscal year and Quarter numbers are given
- Need to sort out by tables name
- How to pass macro variable to PROC SQL on IN statement in WHERE clause on MS SQL Server
- SQL Server with clause versus temporary table
- Uploading a file into SQL Server
- Run a sql script from a bat file and divide the result set in two csv files based on a flag
- Calculating sum of time if continuous records are having same data based on user input
- Kerberos authentication issue : Site stops working automatically after some time
- SQL Server Full Text Search NOT Operator
- SQL Server 2008 Generate a Series of date times
- SQL Server 2019 Installation Failure
- Modifying SQL Results from Select Query
- Can I do a not in except for in my SQL query?
- SQL statement that calculates per-interval growth
- SQL Server Increment running total based on other column
- Can you do a FOREACH loop in T-Sql?
- Query ordering in SQL Server within sub number
- Combine two data fields(columns) values and display in Listbox-ASP.Net?
- How to select only records that have number in column (SQL)
- Default Buffer Max Size and Default Buffer Size in SSIS
- sql server remove trailing zeros in decimal
- SQL Server - index scan where index seek expected
- ORDER BY items must appear in the select list if SELECT DISTINCT is specified..... I spents hours but nothing found
- Indexing SQL database
- SQL join data row by level
- Pivot data in SQL Server
- Force primary key on a view
- How does SqlConnection manage IsolationLevel?
- Eof not triggering
- Azure Active Directory authentication with SQL Database
- How to have a newline in a string from a custom SQL query on an EA template fragment?
- Why SQL Server Management Studio 2005 showing Version 2012 on running 'select @@version'
- How to let the user to enter primary keys manually
- Querying Large Table in sql server 2008
- How to reorder rows using SQL (not swap)
- SQL Server using CASE WHEN THEN statement