Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Categories of Functions Based on Return Modes

By: FYIcenter.com

(Continued from previous topic...)

How Many Categories of Functions based Their Return Modes?

SQL Server supports 2 categories of user defined functions based on their return modes:

1. Scalar-valued Functions - A function that returns a single value. Scalar-valued functions can be used in scalar expressions. Below are some scalar-valued functions:

PRINT GETDATE();
GO
May 19 2007  1:26PM

PRINT 'URL reversed: '+REVERSE('dba.fyicenter.com');
GO
URL reversed: moc.retneciyf.abd

2. Table-valued Functions - A function that returns data in rows and columns like a table. Table-valued functions can be used in table expressions like the FROM clause of SELECT statements Below are some scalar-valued functions:

SELECT * FROM fn_helpcollations() WHERE name LIKE 'French_CI%'
GO
name               
-------------------
French_CI_AI       
French_CI_AI_WS    
French_CI_AI_KS    
French_CI_AI_KS_WS 
French_CI_AS       
French_CI_AS_WS    
French_CI_AS_KS    
French_CI_AS_KS_WS 
(8 row(s) affected)

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
   FROM sys.dm_db_index_physical_stats (
      DB_ID(N'FyiCenterData'), 
      OBJECT_ID(N'fyi_links_indexed'),
      DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
   WHERE s.object_id = i.object_id
      AND s.index_id = i.index_id;
GO
index_id name             avg_fragmentation_in_percent
0        NULL             0.574712643678161
2        fyi_links_url    0
3        fyi_links_counts 0

(Continued on next topic...)

1. What Are User Defined Functions?
2. What Are the Differences between User Defined Functions and Stored Procedures?
3. How To Create a Simple User Defined Function?
4. How To Use User Defined Functions in Expressions?
5. How To List All User Defined Functions in the Current Database?
6. How To Drop an Existing User Defined Function?
7. How To Generate CREATE FUNCTION Script on an Existing Function?
8. How To Get the Definition of a User Defined Function Back?
9. How To Modify an Existing User Defined Function?
10. How To Create User Defined Functions with Parameters?
11. How To Provide Values to User Defined Function Parameters?
12. Can You Pass Expressions to Function Parameters?
13. How To Provide Default Values to Function Parameters?
14. How Many Categories of Functions based Their Return Modes?
15. How Many Ways to Create Table-Valued Functions?
16. How To Create an Inline Table-Valued Function?
17. How To Create an Multi-Statement Table-Valued Function?