{"id":121,"date":"2014-04-30T12:19:00","date_gmt":"2014-04-30T11:19:00","guid":{"rendered":"https:\/\/debuggersspace.com\/index.php\/2014\/04\/30\/different-types-of-sql-server-functions-2\/"},"modified":"2014-04-30T12:19:00","modified_gmt":"2014-04-30T11:19:00","slug":"different-types-of-sql-server-functions-2","status":"publish","type":"post","link":"https:\/\/debuggersspace.com\/index.php\/2014\/04\/30\/different-types-of-sql-server-functions-2\/","title":{"rendered":"Different Types of SQL Server Functions?"},"content":{"rendered":"
\n
\n \t<\/i> Read Time:<\/span>3 Minute, 46 Second <\/div>\n\n <\/div>
\n
\nFunction is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can\u2019t use function to Insert, Update, Delete records in the database table(s). <\/span><\/span><\/div>\n

\nTypes of Function<\/span><\/span><\/h2>\n
    \n
  1. \n

    \nSystem Defined Function<\/span><\/span><\/h2>\n
    \nThese functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server<\/span><\/span><\/div>\n
      \n
    1. \n

      \nScalar Function<\/span><\/span><\/h2>\n
      \nScalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.<\/span><\/span><\/div>\n
      \n<\/div>\n
      \n
      \nSystem Scalar Function<\/span><\/span><\/div>\n
      \n
      \nScalar Function<\/span><\/span><\/div>\n
      \nDescription<\/span><\/span><\/div>\n<\/div>\n
      \n
      \nabs(-10.67)<\/span><\/span><\/div>\n
      \nThis returns absolute number of the given number means 10.67.<\/span><\/span><\/div>\n<\/div>\n
      \n
      \nrand(10)<\/span><\/span><\/div>\n
      \nThis will generate random number of 10 characters.<\/span><\/span><\/div>\n<\/div>\n
      \n
      \nround(17.56719,3)<\/span><\/span><\/div>\n
      \nThis will round off the given number to 3 places of decimal means 17.567<\/span><\/span><\/div>\n<\/div>\n
      \n
      \nupper(‘dotnet’)<\/span><\/span><\/div>\n
      \nThis will returns upper case of given string means ‘DOTNET’<\/span><\/span><\/div>\n<\/div>\n
      \n
      \nlower(‘DOTNET’)<\/span><\/span><\/div>\n
      \nThis will returns lower case of given string means ‘dotnet’<\/span><\/span><\/div>\n<\/div>\n
      \n
      \nltrim(‘ dotnet’)<\/span><\/span><\/div>\n
      \nThis will remove the spaces from left hand side of ‘dotnet’ string.<\/span><\/span><\/div>\n<\/div>\n
      \n
      \nconvert(int, 15.56)<\/span><\/span><\/div>\n
      \nThis will convert the given float value to integer means 15.<\/span><\/span><\/div>\n<\/div>\n<\/div>\n
      \n<\/div>\n<\/li>\n
    2. \n

      \nAggregate Function<\/span><\/span><\/h2>\n
      \nAggregate functions operates on a collection of values and returns a single value. Below is the list of some useful Sql Server Aggregate functions.<\/span><\/span><\/div>\n
      \n<\/div>\n
      \n
      \nSystem Aggregate Function<\/span><\/span><\/div>\n
      \n
      \nAggregate Function<\/span><\/span><\/div>\n
      \nDescription<\/span><\/span><\/div>\n<\/div>\n
      \n
      \nmax()<\/span><\/span><\/div>\n
      \nThis returns maximum value from a collection of values.<\/span><\/span><\/div>\n<\/div>\n
      \n
      \nmin()<\/span><\/span><\/div>\n
      \nThis returns minimum value from a collection of values.<\/span><\/span><\/div>\n<\/div>\n
      \n
      \navg()<\/span><\/span><\/div>\n
      \nThis returns average of all values in a collection.<\/span><\/span><\/div>\n<\/div>\n
      \n
      \ncount()<\/span><\/span><\/div>\n
      \nThis returns no of counts from a collection of values.<\/span><\/span><\/div>\n<\/div>\n<\/div>\n
      \n<\/div>\n<\/li>\n<\/ol>\n<\/li>\n
    3. \n

      \nUser Defined Function<\/span><\/span><\/h2>\n
      \nThese functions are created by user in system database or in user defined database. We three types of user defined functions.<\/span><\/span><\/div>\n
        \n
      1. \n

        \nScalar Function<\/span><\/span><\/h2>\n
        \nUser defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.<\/span><\/span><\/div>\n
          \n
        1.  <\/span>--Create a table <\/span><\/span><\/span><\/li>\n
        2. CREATE<\/span> <\/span>TABLE<\/span> Employee<\/span><\/span><\/span><\/li>\n
        3. (<\/span><\/span><\/li>\n
        4.  EmpID int <\/span>PRIMARY<\/span> <\/span>KEY<\/span>,<\/span><\/span><\/span><\/li>\n
        5.  FirstName varchar<\/span>(<\/span>50<\/span>)<\/span> <\/span>NULL<\/span>,<\/span><\/span><\/span><\/li>\n
        6.  LastName varchar<\/span>(<\/span>50<\/span>)<\/span> <\/span>NULL<\/span>,<\/span><\/span><\/span><\/li>\n
        7.  Salary int <\/span>NULL<\/span>,<\/span><\/span><\/span><\/li>\n
        8.  Address varchar<\/span>(<\/span>100<\/span>)<\/span> <\/span>NULL<\/span>,<\/span><\/span><\/span><\/li>\n
        9. )<\/span><\/span><\/li>\n
        10. --Insert Data<\/span><\/span><\/li>\n
        11. Insert<\/span> <\/span>into<\/span> Employee<\/span>(<\/span>EmpID<\/span>,<\/span>FirstName<\/span>,<\/span>LastName<\/span>,<\/span>Salary<\/span>,<\/span>Address<\/span>)<\/span> <\/span>Values<\/span>(<\/span>1<\/span>,<\/span>'Mohan'<\/span>,<\/span>'Chauahn'<\/span>,<\/span>22000<\/span>,<\/span>'Delhi'<\/span>);<\/span><\/span><\/span><\/li>\n
        12. Insert<\/span> <\/span>into<\/span> Employee<\/span>(<\/span>EmpID<\/span>,<\/span>FirstName<\/span>,<\/span>LastName<\/span>,<\/span>Salary<\/span>,<\/span>Address<\/span>)<\/span> <\/span>Values<\/span>(<\/span>2<\/span>,<\/span>'Asif'<\/span>,<\/span>'Khan'<\/span>,<\/span>15000<\/span>,<\/span>'Delhi'<\/span>);<\/span><\/span><\/span><\/li>\n
        13. Insert<\/span> <\/span>into<\/span> Employee<\/span>(<\/span>EmpID<\/span>,<\/span>FirstName<\/span>,<\/span>LastName<\/span>,<\/span>Salary<\/span>,<\/span>Address<\/span>)<\/span> <\/span>Values<\/span>(<\/span>3<\/span>,<\/span>'Bhuvnesh'<\/span>,<\/span>'Shakya'<\/span>,<\/span>19000<\/span>,<\/span>'Noida'<\/span>);<\/span><\/span><\/span><\/li>\n
        14. Insert<\/span> <\/span>into<\/span> Employee<\/span>(<\/span>EmpID<\/span>,<\/span>FirstName<\/span>,<\/span>LastName<\/span>,<\/span>Salary<\/span>,<\/span>Address<\/span>)<\/span> <\/span>Values<\/span>(<\/span>4<\/span>,<\/span>'Deepak'<\/span>,<\/span>'Kumar'<\/span>,<\/span>19000<\/span>,<\/span>'Noida'<\/span>);<\/span><\/span><\/span><\/li>\n
        15. --See created table<\/span><\/span><\/li>\n
        16. Select<\/span> <\/span>*<\/span> <\/span>from<\/span> Employee <\/span><\/span><\/span><\/li>\n<\/ol>\n<\/pre>\n
          <\/span><\/span><\/p>\n
            \n
          1.  <\/span>--Create function to get emp full name <\/span><\/span><\/span><\/li>\n
          2. Create<\/span> <\/span>function<\/span> fnGetEmpFullName<\/span><\/span><\/span><\/li>\n
          3. (<\/span><\/span><\/li>\n
          4.  <\/span>@<\/span>FirstName varchar<\/span>(<\/span>50<\/span>),<\/span><\/span><\/span><\/li>\n
          5.  <\/span>@<\/span>LastName varchar<\/span>(<\/span>50<\/span>)<\/span><\/span><\/span><\/li>\n
          6. )<\/span><\/span><\/li>\n
          7. returns varchar<\/span>(<\/span>101<\/span>)<\/span><\/span><\/span><\/li>\n
          8. As<\/span><\/span><\/li>\n
          9. Begin<\/span> <\/span>return<\/span> <\/span>(<\/span>Select<\/span> <\/span>@<\/span>FirstName <\/span>+<\/span> <\/span>' '<\/span>+<\/span> <\/span>@<\/span>LastName<\/span>);<\/span><\/span><\/span><\/li>\n
          10. end<\/span> <\/span><\/span><\/span><\/li>\n<\/ol>\n<\/pre>\n
            <\/span><\/span><\/p>\n
              \n
            1.  <\/span>--Calling the above created function<\/span><\/span><\/span><\/li>\n
            2. Select<\/span> dbo<\/span>.<\/span>fnGetEmpFullName<\/span>(<\/span>FirstName<\/span>,<\/span>LastName<\/span>)<\/span> <\/span>as<\/span> Name<\/span>,<\/span> Salary <\/span>from<\/span> Employee <\/span><\/span><\/span><\/li>\n<\/ol>\n<\/pre>\n
              <\/span><\/span><\/li>\n
            3. \n
              \nInline Table-Valued Function<\/span><\/span><\/h2>\n
              \nUser defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.<\/span><\/span><\/div>\n
                \n
              1.  <\/span>--Create function to get employees<\/span><\/span><\/span><\/li>\n
              2. Create<\/span> <\/span>function<\/span> fnGetEmployee<\/span>()<\/span><\/span><\/span><\/li>\n
              3. returns <\/span>Table<\/span><\/span><\/span><\/li>\n
              4. As<\/span><\/span><\/li>\n
              5.  <\/span>return<\/span> <\/span>(<\/span>Select<\/span> <\/span>*<\/span> <\/span>from<\/span> Employee<\/span>)<\/span> <\/span><\/span><\/span><\/li>\n<\/ol>\n<\/pre>\n
                <\/span><\/span><\/p>\n
                  \n
                1.  <\/span>--Now call the above created function<\/span><\/span><\/span><\/li>\n
                2. Select<\/span> <\/span>*<\/span> <\/span>from<\/span> fnGetEmployee<\/span>()<\/span> <\/span><\/span><\/span><\/li>\n<\/ol>\n<\/pre>\n
                  <\/span><\/span><\/li>\n
                3. \n
                  \nMulti-Statement Table-Valued Function<\/span><\/span><\/h2>\n
                  \nUser defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.<\/span><\/span><\/div>\n
                    \n
                  1.  <\/span>--Create function for EmpID,FirstName and Salary of Employee<\/span><\/span><\/span><\/li>\n
                  2. Create<\/span> <\/span>function<\/span> fnGetMulEmployee<\/span>()<\/span><\/span><\/span><\/li>\n
                  3. returns <\/span>@<\/span>Emp <\/span>Table<\/span><\/span><\/span><\/li>\n
                  4. (<\/span><\/span><\/li>\n
                  5. EmpID int<\/span>,<\/span> <\/span><\/span><\/span><\/li>\n
                  6. FirstName varchar<\/span>(<\/span>50<\/span>),<\/span><\/span><\/span><\/li>\n
                  7. Salary int<\/span><\/span><\/li>\n
                  8. )<\/span><\/span><\/li>\n
                  9. As<\/span><\/span><\/li>\n
                  10. begin<\/span><\/span><\/li>\n
                  11.  <\/span>Insert<\/span> <\/span>into<\/span> <\/span>@<\/span>Emp <\/span>Select<\/span> e<\/span>.<\/span>EmpID<\/span>,<\/span>e<\/span>.<\/span>FirstName<\/span>,<\/span>e<\/span>.<\/span>Salary <\/span>from<\/span> Employee e<\/span>;<\/span><\/span><\/span><\/li>\n
                  12. --Now update salary of first employee<\/span><\/span><\/li>\n
                  13.  <\/span>update<\/span> <\/span>@<\/span>Emp <\/span>set<\/span> Salary<\/span>=<\/span>25000<\/span> <\/span>where<\/span> EmpID<\/span>=<\/span>1<\/span>;<\/span><\/span><\/span><\/li>\n
                  14. --It will update only in @Emp table not in Original Employee table<\/span><\/span><\/li>\n
                  15. return<\/span><\/span><\/li>\n
                  16. end<\/span> <\/span><\/span><\/span><\/li>\n<\/ol>\n<\/pre>\n
                    <\/span><\/span><\/p>\n
                      \n
                    1.  <\/span>--Now call the above created function<\/span><\/span><\/span><\/li>\n
                    2. Select<\/span> <\/span>*<\/span> <\/span>from<\/span> fnGetMulEmployee<\/span>()<\/span> <\/span><\/span><\/span><\/li>\n<\/ol>\n<\/pre>\n
                      <\/span><\/span><\/p>\n
                        \n
                      1.  <\/span>--Now see the original table. This is not affected by above function update command<\/span><\/span><\/span><\/li>\n
                      2. Select<\/span> <\/span>*<\/span> <\/span>from<\/span> Employee <\/span><\/span><\/span><\/li>\n<\/ol>\n<\/pre>\n
                        <\/span><\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
                        \nNote<\/span><\/span><\/h4>\n
                          \n
                        1. \n
                          \nUnlike Stored Procedure, Function returns only single value.<\/span><\/span><\/div>\n<\/li>\n
                        2. \n
                          \nUnlike Stored Procedure, Function accepts only input parameters.<\/span><\/span><\/div>\n<\/li>\n
                        3. \n
                          \nUnlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).<\/span><\/span><\/div>\n<\/li>\n
                        4. \n
                          \nLike Stored Procedure, Function can be nested up to 32 level.<\/span><\/span><\/div>\n<\/li>\n
                        5. \n
                          \nUser Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.<\/span><\/span><\/div>\n<\/li>\n
                        6. \n
                          \nUser Defined Function can’t returns XML Data Type.<\/span><\/span><\/div>\n<\/li>\n
                        7. \n
                          \nUser Defined Function doesn’t support Exception handling.<\/span><\/span><\/div>\n<\/li>\n
                        8. \n
                          \nUser Defined Function can call only Extended Stored Procedure.<\/span><\/span><\/div>\n<\/li>\n
                        9. \n
                          \nUser Defined Function doesn’t support set options like set ROWCOUNT etc.<\/span><\/span><\/div>\n<\/li>\n<\/ol>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
                          Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can\u2019t use function to Insert, Update, Delete records in the database table(s).  Types of Function System Defined Function […]<\/p>\n","protected":false},"author":43,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","footnotes":""},"categories":[27],"tags":[],"class_list":["post-121","post","type-post","status-publish","format-standard","hentry","category-sql"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Himanshu Namdeo","author_link":"https:\/\/debuggersspace.com\/author\/admin\/"},"uagb_comment_info":0,"uagb_excerpt":"Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can\u2019t use function to Insert, Update, Delete records in the database table(s).  Types of Function System Defined Function…","_links":{"self":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts\/121"}],"collection":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/users\/43"}],"replies":[{"embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/comments?post=121"}],"version-history":[{"count":0,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts\/121\/revisions"}],"wp:attachment":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/media?parent=121"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/categories?post=121"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/tags?post=121"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}