Click here to Skip to main content
15,867,594 members
Articles / Database Development / SQL Server

Built-In Functions (SQL Server)

Rate me:
Please Sign up or sign in to vote.
4.89/5 (6 votes)
11 Jan 2016MIT4 min read 11.7K   3  
Basic concepts of built-In functions in SQL Server
Built-In functions are used in SQL SELECT expressions to calculate values and manipulate data. These functions can be used anywhere expressions are allowed. Common uses of functions include to change a name to all upper case. In this article, we’ll introduce you to the basic concepts.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.

What are Built-In Functions?

In SQL, a built-in function is a piece for programming that takes zero or more inputs and returns a value. An example of a built-in function is ABS(), which when given a value calculates the absolute (non-negative) value of the number.

Some functions, such as ABS() are used to perform calculations, others such as GETDATE() are used to obtain a system value, such as the current data, or others, like LEFT(), are used to manipulate textual data.

Here is a simple query using the absolute value function.

SQL
SELECT Name,
       ABS(500 - ReorderPoint) ReorderPointDeviation
FROM   production.Product
WHERE  ABS(500 - ReorderPoint) > 200

In this query, we first calculate the difference between 500 and a product’s reorder point. The ABS function is then used to return the result as a positive number.

There are several things to note regarding functions.

  1. The inputs to a function are called parameters. Not all function have parameters, and some functions have more than one.
  2. Parameters are enclosed in parenthesis.
  3. We use functions in the SELECT clause as well as WHERE filter condition. A function can be used anywhere in a SELECT statement that you can use an expression.
  4. Function is a reserved word. I would avoid using them as column or table names. If you do, then expect to qualify your names with brackets [].

As you learn more about functions, you soon find they are vital in being able to calculate and manipulate your query results. We’ll dig deeper into specific functions and their uses in future posts, but before we do so, let’s talk about the type of data a function can return.

What Can Functions Return?

When most of us think about functions, we think fondly of math class – ah those memories… :)

built-in functions in SQL Server are different than those we learn in math.

Learning Math

Most folks' first impression is that functions are used to return numeric values.

Sure, they are used for these, but functions can return many other data types as well.

As you’ll see, functions are used to manipulate DATETIME, VARCHAR, and REAL types.

In SQL Server, the built-in functions return one value. These are called scalar functions, and are used wherever expressions are allowed.

Scalar is just a fancy work for “single value.” You will also learn about functions that can return table rows, these functions are called table value functions. You’ll use these later on when you create your own user defined functions.

How Do Functions Behave?

Some functions return the same value each time you call them. These are said to be deterministic functions. For a given input, these functions return the same value each time they are called..

SQRT(), which is used to return the square root of a number, is deterministic. No matter how many times you run:

SQL
SELECT SQRT(9)

It will return 3.

Non-deterministic functions’ return value may change from execution to execution. GETDATE(), which returns the current date and time, is a good example.

SQL
SELECT GETDATE()

Returns a different value second by second.

This is important to keep in mind when writing and troubleshooting queries. If your query contains non-deterministic functions, then you should expect the results to change. If you didn’t think of this and you were comparing query results, you may think the inconsistent results indicated a major problem, when it was only a non-deterministic function behaving as it should.

Later, as you start to program SQL, and write your own UDFs (User Defined Functions), and work with indexes, you find that non deterministic functions limit where you can use incorporate your UDFs into the database.

Function Categories

There are over a hundred built-in functions in SQL server. To understand their breadth of application, I would recommend visiting the Built-In Functions (Transact SQL) page on the MDSN site. You find the functions are categorized into major categories. The categories we’ll cover next are:

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --