Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How Many type of Function in SQL Server ? Expand / Collapse
Author
Message
Posted Thursday, July 19, 2012 9:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 10:04 AM
Points: 8, Visits: 33
function in sql server?

Thanks
Alok
Post #1332276
Posted Thursday, July 19, 2012 9:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 12,905, Visits: 32,168
four i would say, built in functions, plus three kinds of user defined functions: scalar, multi statement table functions and inline table functions.

-edit]
from my notes of SQL server object types, i didn't include agragate functions or CLR types in the offhand list above:
OBJECT TYPES
AF = Aggregate function (CLR) AGGREGATE_FUNCTION
C = CHECK constraint CHECK_CONSTRAINT
D = DEFAULT (constraint or stand-alone) DEFAULT_CONSTRAINT
F = FOREIGN KEY constraint FOREIGN_KEY_CONSTRAINT
FN = SQL scalar function SQL_SCALAR_FUNCTION
FS = Assembly (CLR) scalar-function CLR_SCALAR_FUNCTION
FT = Assembly (CLR) table-valued function CLR_TABLE_VALUED_FUNCTION
IF = SQL inline table-valued function SQL_INLINE_TABLE_VALUED_FUNCTION
IT = Internal table INTERNAL_TABLE
P = SQL Stored Procedure SQL_STORED_PROCEDURE
PC = Assembly (CLR) stored-procedure CLR_STORED_PROCEDURE
PG = Plan guide PLAN_GUIDE
PK = PRIMARY KEY constraint PRIMARY_KEY_CONSTRAINT
R = Rule (old-style, stand-alone) RULE
RF = Replication-filter-procedure REPLICATION_FILTER_PROCEDURE
S = System base table SYSTEM_TABLE
SN = Synonym SYNONYM
SQ = Service queue SERVICE_QUEUE
TA = Assembly (CLR) DML trigger CLR_TRIGGER
TF = SQL table-valued-function SQL_TABLE_VALUED_FUNCTION
TR = SQL DML trigger SQL_TRIGGER
TT = Table type TABLE_TYPE
U = Table (user-defined) USER_TABLE
UQ = UNIQUE constraint UNIQUE_CONSTRAINT
V = View VIEW
X = Extended stored procedure EXTENDED_STORED_PROCEDURE



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1332283
Posted Thursday, July 19, 2012 11:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
Function Categorization:

1) System Functions

2) User Defined Functions
2.1) Scalar Functions
2.1) In-Line table valued Functions
2.1) Multi-statement table valued Function



~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1332387
Posted Thursday, July 19, 2012 11:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
Two types. Deterministic and non-deterministic.

In other words, what do you mean by "types" of functions?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1332389
Posted Thursday, July 19, 2012 11:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372, Visits: 1,566
Further to add on; scalar functions are always deterministic.

- Lokesh


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1332399
Posted Thursday, July 19, 2012 11:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
lokeshvij (7/19/2012)
Further to add on; scalar functions are always deterministic.


Sure about that?
All scalar udfs will always return the same value if called multiple times with the same parameter?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1332404
Posted Thursday, July 19, 2012 11:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
lokeshvij (7/19/2012)
Further to add on; scalar functions are always deterministic.

- Lokesh


No they're not. It's easy to make nondeterministic scalar UDFs.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1332409
Posted Thursday, July 19, 2012 11:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 12,905, Visits: 32,168
ok, so my answer is D. Seven function types. Final Answer.

(...waiting for the failure buzzer.....)


my thinking is there are the Built in functions plus these six others.
AF = Aggregate function (CLR) AGGREGATE_FUNCTION
FS = Assembly (CLR) scalar-function CLR_SCALAR_FUNCTION
FT = Assembly (CLR) table-valued function CLR_TABLE_VALUED_FUNCTION

IF = SQL inline table-valued function SQL_INLINE_TABLE_VALUED_FUNCTION
FN = SQL scalar function SQL_SCALAR_FUNCTION
TF = SQL table-valued-function SQL_TABLE_VALUED_FUNCTION


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1332432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse