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
- Flatten table rows into columns in SQL Server
- Convert DATE_FORMAT to SQL Server function
- How to connect easypaisa/jazzcash payment gateway in flutter?
- I need top row of same user RegistrationId Using SQL Query(I need SQL select query)
- Pivot Table with Dynamic
- How can I get a SQL report with a group by that substitutes the group by value with a string?
- Retrieve the Median from a decimal column using PERCENTILE_CONT SQL
- Parsing and uploading >1GB of data in C#
- Align tables next to each other based on 1-N relationship
- SQL Server - OPTION CLAUSE
- Entity Framework, full-text search and temporary tables
- SQL Server : Nvarchar to Varchar
- Can't connect to local database using C# Visual Studio
- SQL SELECT multiple criterias IF not null
- T/F: Using IF statements in a procedure produces multiple plans
- SQL query to find records with specific prefix
- Is there any way to convert 'all' strings to uppercase when they are saved
- How to iterate this SQL Query?
- Print an error message when there is the input parameter is null and terminate the procedure
- The identifier that starts with ...... is too long. Maximum length is 128
- sql query to bring last letter in a string to first letter position
- Average from a same row - SQL server
- Combine ASP.NET MVC and Web API projects using identity
- choosing row (from group) with max value in a SQL Server Database
- SQL Server: IF EXISTS massively slowing down a query
- Delete using inner join between tables in separate servers
- move azure SQL database from resource group to another one
- SQL Server Table Fails to Update
- Converting an integer to a 0-padded string
- SQL Server Recursive Cartesian
- Populating droplist in ASP.NET with SQL Server takes a long time. Any optimizations?
- Detecting cells in column that cause error in SQL
- Executing stored procedure returns syntax error but running query works
- Get substring between second and fourth slash
- temporary table vs temporary table variable indexing
- SQL Server split string and access different parts of it
- SQL Server - How to shrink the allocated space for a staging table used by SSIS?
- EF6 The operation cannot be completed because the DbContext has been disposed
- Is there a SQL Server Profiler for SQL Server Express?
- Connect SQL Server 2008 to Visual C++ Win32 Console Application
- SQL Server: Detect Non-Parameterized Queries
- How can I create a Docker container whose timezone matches that of my local machine?
- TSQL If Statement
- How to extract ids of the rows with minimum value in sql
- How to insert data into temp table from a string
- How to modify JSON data in XML attribute in SQL server, if XML is having attribute which contains JSON like data having special characters
- SQL CLR Geography Type
- How to create a foreign key referencing one column in a composite primary key
- Better way to INSERT/UPDATE a Customer table from a Key/Value style Property table?
- Generating Database Scripts in SQL Server Express
- Sql Update record from another table
- SQL Server 2012 CLR Assembly - Parameter Counts Don't Match
- Temporary enabling of ON DELETE CASCADE
- Pass result of a query into stored procedure
- Transform and group row data into columns
- numeric to minutes in sql
- SSRS Extract substring from string before the second string
- Setting values to 2 decimal places in SQL Server
- A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible
- SQL Server: replace by NULL
- C# - SQL Query Rounding Problem while adding DataGridView
- Only return employees who are over 25 from the latest pay period
- SQL server uft8 connection
- Are there any issues and/or gotchas going from SQL Server Express 2008 to SQL Azure?
- Insert a doc file into a column in SQL Server
- SQL comparing date in column against system date
- Which algorithm is used for Checksum(), Binary_Checksum() and CHECKSUM_AVG() function in SQL Server?
- pivot table to make each date as column with out any aggregate column
- compare strings of uneven length in TSQL
- SQL: count of replica entries in a column
- Convert some database data (SQL Table) to .csv format
- SQL Server Agent stop, start, pause, restart buttons are all disabled
- SQL STUFF function roll up multiple rows
- How group by data every 15 days
- How to search multiple strings or chars in a field in sql
- How to connect Python's pyodbc to MS SQL using AD Universal Authentication?
- How do I make values from a SQL table be the headers of a View?
- How to replace multiple Line Breaks with single Line break from nvarchar string in SQL Server
- Performing action on gridview cell click in asp.net C#
- SqlServer2016, Hibernate schema-validation: Missing table, but table exists in db
- Multiple statements in case MDX
- Inner join based on comma separated field
- How to migrate SQL Server database to MySQL?
- How to optimize JOIN ON LIKE '%' + name + '%'
- Updating specific rows to values based on the count of rows in another table
- How to sum cumulative days an account is active from a table with multiple date ranges per account?
- How to restore the right date if it was inserted lately
- How to get exact two columns of table(s) from a entair database in SQL server
- Get total count using a window function
- How to Handle SQLServer Archive
- How can I make a stored procedure commit immediately?
- How to run sql query for each row value using set based approach
- Exclude specific column from result in SQL Server
- How do you convert the number you get from datepart to the name of the day?
- Return percent-population of number of duplicate members
- Sql query for stored procedure
- Complex SQL Update on 2 interdependent tables
- How to view an mdf file Visual Studio 2012 creates to store ASP.Net MVC 4 project data?
- SQL Server interview questions
- Read stored procedure select results into pandas dataframe
- How to avoid having too many similar columns?
- SQL Server query with intersect except or union relational Algebra
- How do I get the creation date of a MySQL table?
- Cannot get my Query to execute in my DAO. Always returns null
- Multiple random values in SQL Server 2005
- How to prevent SQL Server transactions to get stuck?
- SELECT DISTINCT with Duplication check
- How to find first positive value and third consecutive positive values in SQL?
- SQL Server Average Query
- User-defined CLR data types in Windows Azure SQL Database
- Is file_get_contents multi line?
- Using a complex query results to update a table
- SQL Count, and distinct count(*) by grouping two columns
- How to write a query for the following output?
- On delete cascade error - How can I solve it with trigger?
- Azure sql query is slow when an indexed column used in where clause has a particular value
- SQL Server between statement
- Execute procedure each n minutes in Oracle and SQL Server
- Fastest way to sort in Ms sql
- Update table value from another table
- SQL Server: get records that their date is either begining or end of the month
- Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
- Pass a table name to INSERT INTO tablename from outside or a variable?
- Find list of procedures using a specific column
- SQL calculate growth percent from datatable
- How to add temp table to another temp table with extra column
- SQL Server update statement with join
- When a sql result contains multiple rows, how can I fetch each row individually from it?
- Creating non-clustered indexes with ServiceStack OrmLite on SQL Server 2012
- Joining Multiple MySQL Queries Together
- To NULL particular fields in retrieval time in sql
- How to Convert a Nullable DateTime variable's null value to DbNull.Value
- SQL Server unused, but allocated table space
- Am I protected against SQL injections?
- SQL Server Unit Testing Stored Procedures - generating testdata
- How to escape symbols in SQL query?
- How to estimate theoretical size of a single row inserted in the SQL Server table
- insert into using Values
- Why does my week of the year's query show "2022.52" when it should show "2021.52"
- How to get COUNT from stored procedure return?
- SQL Max returns duplicates if values are equal
- SQL query takes too long
- SQL Server case insensitive
- How do you create a yes/no boolean field in SQL server?
- Run stored procedure for each value in a comma separated string
- Good example of using ScalaQuery in the context of Lift?
- Convert between System.Data.SqlDbType and Microsoft.SqlServer.Management.Smo.SqlDataType
- Ensuring correct data in MS SQL Server, DB Level
- how to subtract records coming from one query from the records that is coming from another query?
- Sql - how to calculate 2 separate sub queries?