SQL Server: Functions

For easy reference, we have provided a list of all SQL Server (Transact-SQL) functions. The list of SQL Server functions is sorted alphabetically based on the function name.

These functions can be used in SQL statements or queries in SQL Server. Or, they can be used within the programming environment provided by the SQL Server (Transact-SQL) database, such as stored procedures, functions, triggers, etc.

Below is the list of SQL Server (Transact-SQL) functions, sorted by alphabetically by function name.

• ABS
• ASCII
• AVG
• CASE
• CAST
• CEILING
• CHAR
• CHARINDEX
• COALESCE
• CONCAT
• Concat with +
• CONVERT
• COUNT
• CURRENT_TIMESTAMP
• CURRENT_USER
• DATALENGTH
• DATEADD
• DATEDIFF
• DATENAME
• DATEPART
• DAY
• FLOOR
• GETDATE
• GETUTCDATE
• ISDATE
• ISNULL
• ISNUMERIC
• LAG
• LEAD
• LEFT
• LEN
• LOWER
• LTRIM
• MAX
• MIN
• MONTH
• NCHAR
• NULLIF
• PATINDEX
• RAND
• REPLACE
• RIGHT
• ROUND
• RTRIM
• SESSION_USER
• SESSIONPROPERTY
• SIGN
• SPACE
• STR
• STUFF
• SUBSTRING
• SUM
• SYSTEM_USER
• UPPER
• USER_NAME
• YEAR

SQL Server - Joins

In Structured Query Language (SQL), a JOIN is a method of linking two data tables in a single query, allowing the database to return a set that contains data from both tables at once, or using data from one table to be used as a Filter on the second table. There are several types of JOINs defined within the ANSI SQL standard.

Here explains how to use JOINS, both INNER and OUTER JOINS, in SQL Server (Transact-SQL) with syntax, visual illustrations, and examples.

SQL Server - Restoring Databases

Restoring is the process of copying data from a backup and applying logged transactions to the data. Restore is what you do with backups. Take the backup file and turn it back into a database.

The Restore database option can be done using either of the following two methods.

Method 1 – T-SQL
Syntax
Restore database <Your database name> from disk = '<Backup file location + file name>'

SQL Server - Monitor Database

Monitoring refers to checking database status, settings which can be the owner’s name, file names, file sizes, backup schedules, etc.

SQL Server databases can be monitored mainly through SQL Server Management Studio or T-SQL, and also can be monitored through various methods like creating agent jobs and configuring database mail, third party tools, etc.

SQL Server - Assign Permissions

Permissions refer to the rules governing the levels of access that principals have to securables. You can grant, revoke and deny permissions in MS SQL Server.

To assign permissions either of the following two methods can be used.

SQL Server - Creating Backups

Backup is a copy of data/database, etc. Backing up MS SQL Server database is essential for protecting data. MS SQL Server backups are mainly three types − Full or Database, Differential or Incremental, and Transactional Log or Log.

Backup database can be done using either of the following two methods.

SQL Server - Drop Database

To remove your database from MS SQL Server, use drop database command. Following two methods can be used for this purpose.

Method 1 – Using T-SQL Script
Following is the basic syntax for removing database from MS SQL Server.

SQL Server - Summary - XV

Congratulations for reaching the end of this SQL Server tutorial!
SQL Server is a powerful application, and this tutorial has provided an overview of the main concepts involved in administering SQL Server 2008 Express.
To recap briefly, we've:

• Created databases
• Created database tables
• Inserted data into those tables
• Used the SSMS to run a query
• Created a login account
• Looked at server roles
• Learned about database schemas
• Looked at linked servers
• and more

What Next?

SQL Server Integration Services (SSIS) - XIV

SQL Server Integration Services (SSIS) allows you to integrate smoothly with other applications and data sources.
You can use Integration Services to copy or download files, send e-mail messages in response to events, update data warehouses, clean and mine data, and manage SQL Server objects and data.
In SQL Server 2005 and higher, Data Transformation Services (DTS) is no longer installed with SQL Server. All DTS related tasks are now performed with SSIS.

Where Has DTS Gone?

SQL Server - Linked Servers - XIII

If you've been navigating around SQL Server Management Studio (SSMS), you may have come across the Linked Servers option.
The Linked Servers option allows you to connect to another instance of SQL Server running on a different machine, perhaps remotely in a different city/country. This can be useful if you need to perform distributed queries (query a remote database). Setting up a linked server is quite straight forward in SSMS, all you need is details of the remote server, and the database that you need to query.

Creating a Linked Server

To create a linked server:

SQL Server - Database Schemas - XII

Ever since SQL Server 2005 was released, each object in a database has belonged to a database schema. SQL Server 2008 has continued with database schemas, and an explanation follows.

What is a Database Schema?

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.
Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

SQL Server - Server Roles - XI

When creating a new user login in SQL Server, you get the option of assigning the login one or more server roles.
Server roles (not to be confused with database roles) are available for various database administration tasks. Not everyone should be assigned to a server role. In fact, only advanced users such as database administrators should be assigned a server role.

Accessing the Server Roles

SQL Server - User Logins - X

SQL Server allows for the creation of user logins. Each individual who needs access to SQL Server can be given their own user account.
When the administrator configures these user logins, he/she can assign them to any number of roles and schemas, depending on the access that the individual is entitled to.
In this lesson, we will walk through the steps in creating a user login.

To Create a New User Login

SQL Server - Query Designer VII

In the previous lesson, we created a SQL script using SQL Server Management Studio (SSMS). In this lesson, we will look at how to write SQL scripts using the graphical query designer.

About The Query Designer

The graphical query designer is a graphical user interface that allows you to build queries to run against your SQL Server database. This can be particularly useful when building complex queries that involves many tables, views etc.

The query designer can also be beneficial for those who are learning how to write SQL. This is because you don't need to remember the SQL syntax in order to write queries against your database - the query designer generates the SQL for you.

SQL Server - Views VIII

In SQL Server, a view is a pre-written query that is stored on the database. A view consists of a SELECT statement, and when you run the view, you see the results of it like you would when opening a table. Some people like to think of a view as a virtual table. This is because a view can pull together data from multiple tables, as well as aggregate data, and present it as though it is a single table.

Benefits of Views

A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all of the data). Views can do the following:

SQL Server - Stored Procedures - IX

Stored procedures are a powerful part of SQL Server. They can assist programmers and administrators greatly in working with the database configuration and its data.

A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the "Stored Procedures" node). Programmers and administrators can execute stored procedures either from the SQL Server Management Studio or from within an application as required.

Transact-SQL, which is based on SQL (Structured Query Language), is the programming language used to interface between applications and their databases. Transact-SQL is a relatively easy language to learn and I highly recommend becoming familiar with it.

Benefits of Stored Procedures

Here are some key benefits in using stored procedures:

SQL Server - Adding Data - VI

In the previous lesson, we created a new table in our database. We now have a database table, columns and all, but with no data.

Editing Table Rows

We can use the "Edit Top 200 Rows" option to add data to our table.

SQL Server - Create a Table - V

This lesson demonstrates how to create a table in a SQL Server database using SQL Server Management Studio (SSMS).

SQL Server - Create a Database - IV

One of the first things we should look at with SQL Server/Management Studio is how to create a database. After all, most of the tasks you perform with SQL Server will evolve around one or more databases.

System Databases

If you've only just installed SQL Server, you might notice that some databases have already been created. These are system databases.

SQL Server Management Studio (SSMS) - III

SQL Server Management Studio (SSMS) is the main administration console for SQL Server.
SSMS enables you to create database objects (such as databases, tables, views etc), view the data within your database, you can configure user accounts, transfer data between databases, and more.
Here's what SQL Server Management Studio looks like when you first open it up: