Wednesday, July 25, 2012

What are the Types of SQL Server Functions




Different Types of SQL Server Functions


Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).
Types of Function
  1. System Defined Function

    These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server
    1. Scalar Function

      Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.
      System Scalar Function
      Scalar Function
      Description
      abs(-10.67)
      This returns absolute number of the given number means 10.67.
      rand(10)
      This will generate random number of 10 characters.
      round(17.56719,3)
      This will round off the given number to 3 places of decimal means 17.567
      upper('dotnet')
      This will returns upper case of given string means 'DOTNET'
      lower('DOTNET')
      This will returns lower case of given string means 'dotnet'
      ltrim(' dotnet')
      This will remove the spaces from left hand side of 'dotnet' string.
      convert(int, 15.56)
      This will convert the given float value to integer means 15.
    2. Aggregate Function

      Aggregate functions operates on a collection of values and returns a single value. Below is the list of some useful Sql Server Aggregate functions.
      System Aggregate Function
      Aggregate Function
      Description
      max()
      This returns maximum value from a collection of values.
      min()
      This returns minimum value from a collection of values.
      avg()
      This returns average of all values in a collection.
      count()
      This returns no of counts from a collection of values.
  2. User Defined Function

    These functions are created by user in system database or in user defined database. We three types of user defined functions.
    1. Scalar Function

      User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.
      1. --Create a table CREATE TABLE Employee
      2. (
      3. EmpID int PRIMARY KEY,
      4. FirstName varchar(50) NULL,
      5. LastName varchar(50) NULL,
      6. Salary int NULL,
      7. Address varchar(100) NULL,
      8. )
      9. --Insert Data
      10. Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
      11. Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
      12. Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
      13. Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
      14. --See created table
      15. Select * from Employee
      1. --Create function to get emp full name Create function fnGetEmpFullName
      2. (
      3. @FirstName varchar(50), @LastName varchar(50)
      4. )
      5. returns varchar(101)
      6. As
      7. Begin return (Select @FirstName + ' '+ @LastName);
      8. end
      1. --Now call the above created function
      2. Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee
    2. Inline Table-Valued Function

      User defined inline table-valued function 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.
      1. --Create function to get employees
      2. Create function fnGetEmployee()
      3. returns Table
      4. As
      5. return (Select * from Employee)
      1. --Now call the above created function
      2. Select * from fnGetEmployee()
    3. Multi-Statement Table-Valued Function

      User defined 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.
      1. --Create function to EmpID,FirstName and Salary of Employee
      2. Create function fnGetMulEmployee()
      3. returns @Emp Table
      4. (
      5. EmpID int, FirstName varchar(50),
      6. Salary int
      7. )
      8. As
      9. begin
      10. Insert @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
      11. --Now update salary of first employee
      12. update @Emp set Salary=25000 where EmpID=1;
      13. --It will update only in @Emp table not in Original Employee table
      14. return
      15. end
      1. --Now call the above created function
      2. Select * from fnGetMulEmployee()
      1. --Now see the original table. This is not affected by above function update command
      2. Select * from Employee




Difference between TRUNCATE and DELETE in SQL Server




Difference between TRUNCATE and DELETE statement in SQL Server

TRUNCATE V/s DELETE

TruncateDelete
TRUNCATE is a DDL commandDELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each rowDELETE statement is executed using a row lock,  each row in the table is locked for deletion
Cannot use Where ConditionWe can specify filters in where clause
It Removes all the dataIt deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.Delete activates a trigger because the operation                                are logged individually.
Faster in performance wise, because it is minimally logged in transaction log.Slower than truncate because, it maintain logs for every record




 Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the tablekeeps object’s statistics and all allocated space. After a                       DELETE statement is executed,the table can still contain empty pages.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction loThe DELETE statement removes rows one at a time                       and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the columnDELETE retain the identity