Webinar Alert : Mastering  Manual and Automation Testing! - Reserve Your Free Seat Now

- SQL Server Blogs -

Different Types of SQL Server & SQL Database Functions

A function in SQL is a subroutine or a small program that can be used again and again throughout the database apps for data manipulation. Each database has a few in-built functions for the basic programming and you can define your own that are named as the user-defined functions. Customized functions in SQL are generally used to perform complex calculations and return the result as a value. The return value can be a single value or a result set. The objective of this SQL blog is to make you familiar with different types of SQL functions with examples and the basic syntax.

SQL Server Functions:

  • It is a database object in sql server.
  • By using functions, we can do complex arithmetical operations and programing.
  • Supports only SELECT or read data.
  • We cannot use function to Insert/Update/Delete records in the database table.
  • We can use variables and cursors.
  • It accepts only input parameters, do not have output parameters.
  • We can call User Defined Functions as part of Select/Insert/Update/Delete.
  • It can be nested up to 32 level.
  • UDF can have up to 1023 input parameters.
  • It can’t return XML data type.
  • It doesn’t support Exception handling.

Types of SQL Server Functions:

  • System Defined Functions

And in system defined function we have 2 types of functions.

  • System Scalar Function
  • System Aggregate Function

Scalar Functions:

Which operators on single value and returns single value, below is the list of some scale functions used in sql server.

Read: What is the Difference Between Control Flow & Data Flow in SSIS?
Scalar Function Description
round (9.56785) This will round the give number to 3 places of decimal, 9.567
rand (10) This will generate random numbers of 10 characters
upper (‘sql’) This will return upper case of given string, ‘SQL’
lower (‘SQL’) This will return lower case of given string, ‘sql’
abs (-20.75) This will return absolute number of a given number, 20.25
Convert (int, 20.56) This will convert given float value to integer, 20
ltrim ('  sql') This will remove the spaces from left hand side, ‘sql’
rtrim (‘sql  ‘) This will remove the spaces from right hand side, ‘sql’
Substr () This will extract characters from a text field
ASCII (char_Exp) This will return ASCII code of the given character expression

Read More: Different Type of SQL Joins

Aggregate Functions:

Aggregates the values and return a single value, below is the list of some aggregate values in sql server.

Aggregate Function Description
Min () This will return Minimum value
Max() This will return Maximum value
Avg () This will return Average value
Count () This will return number of counts
Sum () This will return total sum of numeric value
  • User Defined Functions

These functions are created by user in the system database, and we have 3 types of user define functions:

  • Scalar Function
  • Inline Table-Valued Function
  • Multi Statement Table-Valued Function

Read More: Different Types of SQL Injection

Scalar Function:

Read: SAS Tutorial Guide for Beginners

It is a function that takes one or more values but returns a single value.

Example:  Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

Inline table-Valued function:

It 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. Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions

Read: Most Frequently Asked RDBMS Interview Questions And Answers

Read More: SQL Server Interview Questions

Multi-Statement Table-Valued Function

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. Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions Types of SQL Server & SQL Database Functions

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

What’s Next?

The blog gives you a sound idea of functions and how they can be used within a database. Using functions is a good programming practice and if you want to know how to implement them for complex calculations then sign up for the SQL training now and start learning practical aspects of the SQL database programming in 2020 right away.

If you have the basic skills, then you can learn SQL by availing our self-learning courses with the best video content designed by our certified mentors.

Read: What is Data Mining SQL? Data Mining SQL Tutorial Guide for Beginner


SQL Tutorial Overview

fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

17 days 02 Nov 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

10 days 26 Oct 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

2 days 18 Oct 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

2 days 18 Oct 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

2 days 18 Oct 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

9 days 25 Oct 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

2 days 18 Oct 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

9 days 25 Oct 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

3 days 19 Oct 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

17 days 02 Nov 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

30 days 15 Nov 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

9 days 25 Oct 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews