How Many type of Function in SQL Server ?

Question

How Many type of Function in SQL Server ?

Alok Kumar Sharma

SSC Veteran

Points: 297
More actions
July 19, 2012 at 9:15 am

#259668

function in sql server?
Thanks
Alok

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 constraintCHECK_CONSTRAINT

D = DEFAULT (constraint or stand-alone)DEFAULT_CONSTRAINT

F = FOREIGN KEY constraintFOREIGN_KEY_CONSTRAINT

FN = SQL scalar functionSQL_SCALAR_FUNCTION

FS = Assembly (CLR) scalar-functionCLR_SCALAR_FUNCTION

FT = Assembly (CLR) table-valued functionCLR_TABLE_VALUED_FUNCTION

IF = SQL inline table-valued functionSQL_INLINE_TABLE_VALUED_FUNCTION

IT = Internal tableINTERNAL_TABLE

P = SQL Stored ProcedureSQL_STORED_PROCEDURE

PC = Assembly (CLR) stored-procedureCLR_STORED_PROCEDURE

PG = Plan guidePLAN_GUIDE

PK = PRIMARY KEY constraintPRIMARY_KEY_CONSTRAINT

R = Rule (old-style, stand-alone)RULE

RF = Replication-filter-procedureREPLICATION_FILTER_PROCEDURE

S = System base tableSYSTEM_TABLE

SN = SynonymSYNONYM

SQ = Service queueSERVICE_QUEUE

TA = Assembly (CLR) DML triggerCLR_TRIGGER

TF = SQL table-valued-functionSQL_TABLE_VALUED_FUNCTION

TR = SQL DML triggerSQL_TRIGGER

TT = Table typeTABLE_TYPE

U = Table (user-defined)USER_TABLE

UQ = UNIQUE constraintUNIQUE_CONSTRAINT

V = ViewVIEW

X = Extended stored procedureEXTENDED_STORED_PROCEDURE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

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[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

Further to add on; scalar functions are always deterministic.

- Lokesh

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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, MVP, M.Sc (Comp Sci)
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

GSquared SSC Guru Points: 260824 More actions · Answer 6

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

Lowell SSC Guru Points: 323518 More actions · Answer 7

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!