Basic SQL Commands Part 3:

HANDLING TRANSACTION

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program. A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors. Practically, you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.

Properties  of  Transactions:

Transactions have the following four standard properties, usually referred to by the acronym ACID:

• Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
• Consistency: ensures that the database properly changes states upon a successfully committed transaction.
• Isolation: enables transactions to operate independently of and transparent to each other.
• Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.
  There are following commands used to control transactions:

• COMMIT: to save the changes.
• ROLLBACK: to roll back the changes.
• SAVEPOINT: creates points within groups of transactions in which to ROLLBACK
• SET TRANSACTION: Places a name on a transaction.
  Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically commited in the database.
  /* Transactions */
  BEGIN TRAN
  Delete From Customers Where Age=51;
  Commit TRAN;
   
  
  BEGIN TRAN
  Delete From Customers Where Age=51;
     ROLLBACK TRAN;
  
  There are RDBMS, which support temporary tables. Temporary Tables are a great feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates. Temporary tables are available in MySQL version 3.23 onwards. If you use an older version of MySQL than 3.23, you can't use temporary tables, but you can use heap tables.
  As stated earlier, temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.
  /*Temprary Table*/
   
  CREATE TABLE #SalesSummary
  (product_name VARCHAR(50) NOT NULL,
   total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
   avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
   total_units_solds INT  NOT NULL DEFAULT 0);
   
  
   
  SELECT *FROM #SalesSummary;
  
   
  USING SEQUENCE
  A sequence is a set of integers 1, 2, 3, ... that are generated in order on demand. Sequences are frequently used in databases because many applications require each row in a table to contain a unique value, and sequences provide an easy way to generate them. The simplest way in MySQL to use sequences is to define a column as AUTO_INCREMENT and leave rest of the things to MySQL to take care.
   
  /* Using Sequences */
   
  CREATE TABLE INSECT
  (
   id INT NOT NULL,
   PRIMARY KEY (id),
   name VARCHAR (30) NOT NULL, /*type of insect*/
   date DATE NOT NULL , /* date collected */
   origin VARCHAR(30) NOT NULL /* where collected */
   );
   
   INSERT INTO INSECT (id,name,date,origin) VALUES
   ('3','grasshopper','2001-09-10','frontyard')
   
  SELECT *FROM INSECT ORDER BY id;
  
   
   
  SORTING IN ASENDING & DECENDING
   
  /*Sorting Results */
   SELECT *FROM Customers ORDER BY Name,Salary;
   SELECT *FROM Customers ORDER BY Name DESC;
   
   SELECT *FROM Customers ORDER BY (CASE Address
   WHEN 'Delhi'     THEN 1
   WHEN 'Bhopal'    THEN 2
   WHEN 'Kota'      THEN 3
   WHEN 'Ahmadabad' THEN 4
   WHEN 'MP'        THEN 5
   ELSE 100 END) ASC, Address DESC;
  
   

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column. Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value. Useful scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

SQL User Defined Functions

User defined functions provide an easy way to encapsulate business logic and custom actions and can be used by database developers to optimize their application behaviour. In the simplest terms, a user-defined function (UDF) in SQL Server is a programming construct that accepts parameters, does work that typically makes use of the accepted parameters, and returns a type of result. This article will cover two types of UDFs: table-valued and scalar-valued.

Benefits of User-defined Functions

Below are the most visible benefits of user-defined functions.

• Extend programming richness via user code - Your database can now do more than only process data. With UDFs, databases can now also validate business logic and do custom actions.
• Faster performance than T-SQL functions for computational logic - T-SQL functions are great at processing data. When it comes to business logic and custom algorithms, UDFs can do a much better job. UDFs work akin to stored procedures.
• Optimizes network traffic - UDFs allow data manipulation such that you don't need a lot of talking to the database and you can optimize your results based on your business logic within the database. This reduces network bandwidth utilization.
• Reusable code, which can be called akin to stored procedures with business logic.

Limitations of User-defined Functions

When called in SQL queries, UDFs are only executed in a single thread. They follow serial execution plan. Hence, if the business logic inside UDFs would work better in a multi-threaded environment, it would be better to not implement it as a user-defined function, since it would lead to over-all performance degradation. UDFs cannot change database state so there might be usage constraints if you are attempting to change the database information inside your business logic.

Where are User Defined Functions Used

The following is a list of most common usage area of UDFs.

• In T-SQL statements, such as select
• Inside the definition of another User-defined function
• As a replacement of stored procedures
• For defining automatic values for a column in a table

Structure of User-defined Function

UDFs are composed of two parts:

1. Header
2. Function body

The header contains the function name, input parameter info (name and type), and return parameter info (name and type). The function body contains the logic. It contains one or more T-SQL statements that contain the logic and it can also contain a reference to a .NET assembly (in case of a CLR UDF).

Types of UDFs

Scalar-valued functions

Scalar functions are UDFs, which return a single value.

There is no function body in the case of an inline scalar function, and the scalar value (the return value) is the result of a single statement.

 

A scalar-valued UDF accepts parameters and, ultimately, returns a single, atomic value. There are seven reasons why these types of functions are different than stored procedures in the database engine.

• You cannot modify data inside of a UDF.
• A scalar-valued UDF returns only one value, where a stored procedure can have numerous OUTPUT parameters.
• You can use scalar-valued UDFs as the default value for a column in a table.
• Scalar-valued UDFs are an easy way to define constant values to use in your database environment.
• You can pass field values as parameters into UDFs.
• You can nest scalar function calls. This means that you can pass a call to a scalar-valued function to another function or stored procedure.
• You can use the results from scalar-valued UDFs as criteria in a WHERE statement. Although you can do it, this is typically not a good idea. (Later in the article, I'll explain why I try to avoid this common practice.)
  There are two types of scalar-valued UDFs: deterministic and non-deterministic. Recognizing the determinism of the functions that are created is important. An example of the importance is the creation of indexed views. One of the many restrictions of creating an index on a view is that the view definition cannot use a non-deterministic function.
  Deterministic
  A deterministic UDF always returns the same result with the same set of input parameters. Some examples of deterministic functions are the system functions MONTH(), YEAR(), and ISNULL().
  Non-deterministic
  A non-deterministic UDF is can potentially return a different value each time it is called with the same set of input parameters. Some examples of non-deterministic functions are the system functions GETDATE(), NEWID(), and @@CONNECTIONS.
   
  /* Create Function (Scalar Valued) Example */
   
   CREATE FUNCTION whichContinent
  (@Country nvarchar(15))
  RETURNS varchar(30)
  AS
  BEGIN
  declare @Return varchar(30)
  select @return = case @Country
  when 'Argentina' then 'South America'
  when 'Belgium' then 'Europe'
  when 'Brazil' then 'South America'
  when 'Canada' then 'North America'
  when 'Denmark' then 'Europe'
  when 'Finland' then 'Europe'
  when 'France' then 'Europe'
  else 'Unknown'
  end
   
  return @return
  end
  /* Testing the userdefined Functions*/
  PRINT dbo.WhichContinent('USA')
  
   
  SELECT dbo.WhichContinent(Customers.Country), customers.*
  FROM customers
   
  
   
  CREATE TABLE test1(Country varchar(15),Continentas(dbo.WhichContinent(Country)));
   
  
  INSERT INTO test1 (country) VALUES ('USA');
  
   
  SELECT * FROM test1;
  
   

When to choose?

Scalar functions are preferred when you want a single value as the result. Examples might be the total cost of a business trip, total project cost in man-days or the birth date of a person.The return type can be any valid SQL type except text, ntext, image, cursor and timestamp.

  CREATE FUNCTION dbo.CalculateTotalDaysForProject(@DevelopmentDays int, @TestingDays int)

  RETURNS int

  AS

  BEGIN

  DECLARE @totalcost int;

  SELECT @ totalcost = @DevelopmentDays + @ TestingDays;

  RETURN @totalcost;

  END

 

 

 

/* Another Function (Scalar Valued) Example */

CREATE FUNCTION FetchTotalOrders

(

   @p_CustomerID NVARCHAR(10)

) RETURNS INT

BEGIN

RETURN (SELECT COUNT(OrderID) FROM Orders

WHERE CustomerID=@p_CustomerID)

END

 

 

 

 

Table-valued functions

Table value functions are UDFs that, as the name suggests, return a table.A table-valued UDF is a function that accepts parameters and returns the results in the form of a table. This type of function is special because it returns a table that you can query the results of and join with other tables.

It can be difficult to know when it is appropriate to use a VIEW vs. when it is appropriate to use a table-valued UDF. VIEWs are a great tool for data abstraction, combining data, and logically using subsets of data. I like to use table-valued UDFs when I need to use one or more values from different tables in a join operation where some type of calculation needs to be done and an aggregation returned.

 

CREATE FUNCTION [dbo].[customersbycountry] ( @Country varchar(15) )

RETURNS

      @CustomersbyCountryTab table (

            [CustomerID] [nchar] (5), [CompanyName] [nvarchar] (40),

            [ContactName] [nvarchar] (30), [ContactTitle] [nvarchar] (30),

            [Address] [nvarchar] (60), [City] [nvarchar] (15),

            [PostalCode] [nvarchar] (10), [Country] [nvarchar] (15),

            [Phone] [nvarchar] (24), [Fax] [nvarchar] (24)

      )

AS

BEGIN

      INSERT INTO @CustomersByCountryTab

      SELECT      [CustomerID],

                  [CompanyName],

                  [ContactName],

                  [ContactTitle],

                  [Address],

                  [City],

                  [PostalCode],

                  [Country],

                  [Phone],

                  [Fax]

      FROM [Northwind].[dbo].[Customers]

      WHERE country = @Country

     

      DECLARE @cnt INT

      SELECT @cnt = COUNT(*) FROM @customersbyCountryTab

     

      IF @cnt = 0

            INSERT INTO @CustomersByCountryTab (

                  [CustomerID],

                  [CompanyName],

                  [ContactName],

                  [ContactTitle],

                  [Address],

                  [City],

                  [PostalCode],

                  [Country],

                  [Phone],

                  [Fax]  )

            VALUES ('','No Companies Found','','','','','','','','')

     

      RETURN

END

 

When to choose?

Table value functions are used when one or more row's worth of data is expected. Examples would be all orders generated by the best performing salesperson, etc.

  CREATE FUNCTION GeneratedSales.SalesByPerson(@sales_ID int)

  RETURNS @SalesData TABLE

  (

  [CustomerID] int,

  [SalesAmount] int,

  [SalesDate] date

  )

  AS

  BEGIN

  INSERT INTO @SalesData

                    SELECT Sales.CustomerID, Sales.SalesAmount, Sales.SalesDate from Sales 

  Where Sales.Sales_ID = @sales_ID

  RETURN

  END

 

Creating User-defined Functions

User-defined functions are created by the "CREATE FUNCTION" statement. They can be edited by using the "ALTER FUNCTION" statement and deleted by the "DROP FUNCTION" statement. One key thing to keep in mind is that statements within the BEGIN..END block cannot make any changes like updates to the database. Only local changes (objects in the local scope, such as temporarily created tables and variables) are allowed. Valid T-SQL statements allowed in User-defined functions include DECLARE, SELECT, TRY…CATCH, EXECUTE, UPDATE (local only), INSERT(local only), DELETE (local only).Most deterministic T-SQL built-in functions can be used inside a UDF.

CLR user defined functions

Creating a CLR UDF is a multi-stage process. First, you define the desired function as a static method of a class in the .NET framework language.

For example, let us create a function in C#, which lists the count of unique salespersons that made sales in the last quarter.

  using Microsoft.SqlServer.Server;

  using System.Data.SqlClient;

  public class Sales

  {

      public static int GetCountOfUniqueSalesPersons()

      {

          using (SqlConnection myConnection

              = new SqlConnection("context connection=true"))

          {

              myConnection.Open();

              SqlCommand myCmd = new SqlCommand(

                  "SELECT COUNT(DISTINCT SalesPersonID) AS 'UniqueSalesPersons' FROM SalesOrders", myConnection);

              return (int)myCmd.ExecuteScalar();

          }

      }

  }

 - CLRfunction.cs

Now compile this as a library.

  C:\windows\microsoft.net\framework\v2.0.50727\csc.exe /t:library /out:CLRUDF.dll CLRFunction.cs

Next, we register this assembly with SQL using the CREATE ASSEMBLY statement.

  CREATE ASSEMBLY CLRUDF  FROM 'CLRUDF.dll';

Lastly, we create a function that references the newly registered assembly using the CREATE FUNCTION statement.

  CREATE FUNCTION GetCountOfUniqueSalesPersons() RETURNS INT 

  AS EXTERNAL NAME CLRUDF.Sales.GetCountOfUniqueSalesPersons; 

We can now use this newly available user-defined function.

T-SQL user defined functions

T-SQL UDFs are simpler to create as they only have SQL statements. See above for examples of the Scalar UDF and Table-Value UDFs.

Executing User Defined Functions

UDFs can be executed in a number of places as mentioned above. Let us look at how to invoke UDFs in a simple SELECT statement. The following will list all salespersons that made sales in the last quarter.

  SELECT dbo.GetCountOfUniqueSalesPersons()

  FROM SalesOrders where SalesOrders.QuarterStartDate = '2010-04-01' and Sales.QuarterEndDate = '2010-6-30';