How Many Categories of Functions based Their Return Modes in SQL Server Transact-SQL?

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

⇒ Syntaxes of Creating Table-Valued Functions in SQL Server

⇐ DEFAULT - Providing Default Values to Function Parameters in SQL Server

⇑ Using User Defined Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials