How Many type of Function in SQL Server ?

  • function in sql server?

    Thanks

    Alok

  • 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!

  • 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

  • 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

  • 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

  • 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
  • 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

  • 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!

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

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