Restoring a SQL Server database backup file (.bak)

Introduction On regular basis we need to restore the database, either will only schema of the database, or many times both data as well as schema.  Most of the applications (either Web application e.g. ASP.NET based apps, or Windows applications e.g. WPF based apps) requires 'reference data' such as drop-down data, types of users, etc. So what is the most optimum, reliable way of moving database either on separate database server, or on the same database server but with different database name? There are several ways to achieve this objective but my personal preference is to first create the database backup file i.e. 'my_backup.bak' using Microsoft SQL Server Management Studio , and then restoring that backup file to create new database.  This article focuses on restoring the backup file to create a new database. How to create the backup file will be covered in another article.   This article addresses below points: 1.   How to check the name and path of th...
0

User Defined Functions in T-SQL - MS SQL Server

Introduction

This post will explain different types of UDFs with simple examples.  

User Defined Function / UDF

  • Unlike SP (Stored Procedure) when you are expecting an input from a simple set of query then you will use UDF, or simply called as Function. 
  • Another unique feature of UDF is, it can be called from/within a SQL statement, which is not possible with SP.

Types of UDFs

Below are the three types of Functions/UDFs:

1.  Scalar Function 

  • Scalar UDFs return a single value, similar to built-in functions such as OBJECT_NAME(), or GETDATE(), which returns a single string, integer, or date. The output of these functions are based on the input parameters.
  • You cannot return values with a user-defined data type from scalar UDFs, but only  system-supplied data type.
  • The scalar UDFs can return any scalar data type, with the exception of TIMESTAMP. 
  • Below example is returning a single INT.
    CREATE FUNCTION FunctionName (inputParams)
    RETURNS INT
    AS
    RETURN
    (
<Your single SELECT statement>
    );

2.  Table-Value / In-line Function

  • In-line UDFs return a single row or multiple rows and can contain a single SELECT statement.
  • UDFs are limited to a single SELECT. 
  • But select statement can return multiple values.
  • Below example is returning a Table. 
    CREATE FUNCTION FunctionName (inputParam)
    RETURNS TABLE
    AS
    RETURN
    (
<Your single SELECT statement>
    );

3.  Multi-statement Table-value Function

  • As the name suggests, multi-statement UDFs can contain any number of statements that can populate the table variable prior to returning it. 
  • Multi-statement UDFs are useful in scenarios where in you need to return a set of rows but the logic is too complex to be contained in a single SELECT statement.
  • Note that although you can use INSERT, UPDATE, and DELETE statements against the table variable (which is being returned), a function cannot modify data in a permanent table. 
  • Example:
    CREATE FUNCTION FunctionName (inputParams)
    RETURNS @TableName TABLE (Columns)
    AS
    BEGIN;
        <Multiple statements to populate table variable>
        RETURN;
    END;

Summary

We have described the three types of UDFs in simple words, along with a single example of each.


Your feedback

Your feedback is of paramount importance to us, provide your valuable inputs; what else you would like to read about, or if you would like the author to elaborate on any specific topic from this article, or any other article on this blog.

Disclaimer

The material presented in this blog is to help educate the developers, and the author(s) & owner(s) of this blog are not responsible of any damage arising out of use of the code snippets or any other contents of this blog.

Comments

Post a Comment