Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

Different Types of SQL Server & SQL Database Functions

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

SQL Server Curriculum

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 a CASE Statement in the SQL?
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

Read: How To Become Expert In Sql Server Developer?

Scalar Function:

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

Read More: Different Types of SQL Keys

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: Top 30 Data Modeling Interview Questions with 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 Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

-1 day 14 Nov 2019

DevOps

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

Upcoming Class

0 day 15 Nov 2019

Data Science

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

Upcoming Class

0 day 15 Nov 2019

Hadoop

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

Upcoming Class

1 day 16 Nov 2019

Salesforce

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

Upcoming Class

-1 day 14 Nov 2019

QA

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

Upcoming Class

18 days 03 Dec 2019

Business Analyst

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

Upcoming Class

0 day 15 Nov 2019

SQL Server

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

Upcoming Class

4 days 19 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews