Delete Vs Truncate Statement

* Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

* Truncate table also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.

* Truncate table is functionally identical to delete statement with no “where clause” both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.

* Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is.

* In truncate table the counter used by an identity column for new rows is reset to the seed for the column.

* If you want to retain the identity counter, use delete statement instead.

* You cannot use truncate table on a table referenced by a foreign key constraint; instead, use delete statement without a where clause. Because truncate table is not logged, it cannot activate a trigger.

* Truncate table may not be used on tables participating in an indexed view.

Normal Forms

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF).

First Normal Form (1NF)
First normal form (1NF) sets the very basic rules for an organized database:
* It contains two-dimensional tables with rows and columns.
* Eliminate duplicative columns from the same table.
* Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form (2NF)
Second normal form (2NF) further addresses the concept of removing duplicative data:
* Meet all the requirements of the first normal form.
* Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
* Create relationships between these new tables and their predecessors through the use of foreign keys.

Third Normal Form (3NF)
Third normal form (3NF) goes one large step further:
* Meet all the requirements of the second normal form.
* Remove columns that are not dependent upon the primary key.

Boyce-Codd Normal Form
A table is in Boyce-Codd normal form (BCNF) if and only if, for every one of its non-trivial functional dependencies X ? Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

Fourth Normal Form (4NF)
Finally, fourth normal form (4NF) has one additional requirement:
* Meet all the requirements of the third normal form.
* A relation is in 4NF if it has no multi-valued dependencies.
Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.

Fifth Normal Form (5NF)
The criteria for fifth normal form (5NF and also PJ/NF) are:
* The table must be in 4NF.
* There must be no non-trivial join dependencies that do not follow from the key constraints. A 4NF table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys.

Domain/key Normal Form (DKNF)
Domain/key normal form (or DKNF) requires that a table not be subject to any constraints other than domain constraints and key constraints.

Sixth Normal Form (6NF)
A table is in sixth normal form (6NF) if and only if it satisfies no non-trivial join dependencies at all. This obviously means that the fifth normal form is also satisfied. The sixth normal form was only defined when extending the relational model to take into account the temporal dimension.

Normalization

Normalization is a technique for designing relational database tables to minimize duplication of information and, to safeguard the database against certain types of logical or structural problems, namely data anomalies.
Normalization is typically a refinement process after the initial exercise of identifying the data objects that should be in the database, identifying their relationships, and defining the tables required and the columns within each table.
For example, when multiple instances of a given piece of information occur in a table, the possibility exists that these instances will not be kept consistent when the data within the table is updated, leading to a loss of data integrity. A table that is sufficiently normalized is less vulnerable to problems of this kind, because its structure reflects the basic assumptions for when multiple instances of the same information should be represented by a single instance only.
Higher degrees of normalization typically involve more tables and create the need for a larger number of joins, which can reduce performance.

There are two goals of the normalization process:
a. Eliminating redundant data (for example, storing the same data in more than one table) and
b. Ensuring data dependencies make sense (only storing related data in a table).

Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

TRUNCATE TABLE advantages over DELETE statement

* Less transaction log space is used - The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. 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 log.
* Fewer locks are typically used - When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
* Without exception, zero pages are left in the table - After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

SQL - Truncate and Delete

Truncate and Delete both are used to delete data from the table. Both the command will only delete the data of the specified table without modifying or deleting the structure of the table. Both the SQL statements are used to delete only the data from the table but they both differ from each other in many aspects like syntax, performance, resources uses etc.

TRUNCATE TABLE

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

Syntax:

TRUNCATE TABLE
[ { database_name.[ schema_name ]. | schema_name . } ]
table_name
[ ; ]

DELETE

Delete command in SQL also removes all rows from a table with logging the individual row deletion in the transaction log. We can use the Where Clause with this (Delete) statement.

Syntax:

DELETE FROM TABLE_NAME
[ { database_name.[ schema_name ]. | schema_name . } ]
table_name
[ ; ]

Table_name : Is the name of the table to truncate or from which all rows are removed.
Simple it looks like below query.

DELETE FROM authors

The above command will delete all data from the table author.

In case of delete statements you can limit your delete query using where clause to delete, only particular records that fulfills the condition of where clause will be deleted not the all records.

It looks like below query with where clause.

DELETE FROM authors Where AuthorId IN (1,2,3)

Benefits of Stored Procedures

* Pre-compiled execution - SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
* Reduced client/server traffic - If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
* Efficient reuse of code and programming abstraction - Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
* Enhanced security controls - You can grant users permission to execute a stored procedure independently of underlying table permissions.

Structure

Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you'll find that stored procedures are actually quite simple.

Example:
Let's take a look at a practical example. Assume we have the table shown at the bottom of this page, named Inventory. This information is updated in real-time and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following:

SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = 'FL'

This resulted in very inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.

We can simplify this process through the use of a stored procedure. Let's create a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. Here's the SQL code:

CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

Florida warehouse manager can then access inventory levels by issuing the command
EXECUTE sp_GetInventory 'FL'
The New York warehouse manager can use the same stored procedure to access that area's inventory.
EXECUTE sp_GetInventory 'NY'

Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure will work wonders. The SQL Sever creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.

Inventory Table

Stored Procedure

A Stored Procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a sproc or SP) are actually stored in the database.

A Stored Procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance. Additionally, stored procedures can be used to help ensure the integrity of the database.
Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.

CALL procedure(…)

Stored procedures can return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors by other stored procedures by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. The standard Structured Query Language provides IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared.

How to test Stored Procedure?

White Box Testing Techniques

* Basis Path Testing – Each independent path through the code is taken in a pre-determined order. This point will further be discussed in other section.

* Flow Graph Notation - The flow graph depicts logical control flow using a diagrammatic notation. Each structured construct has a corresponding flow graph symbol.

* Cyclomatic Complexity - Cyclomatic complexity has a foundation in graph theory and provides us with extremely useful software metric. Complexity is computed in one of the three ways:
1. The number of regions of the flow graph corresponds to the Cyclomatic complexity.
2. Cyclomatic complexity, V(G), for a flow graph, G is defined as
V (G) = E-N+2
Where E, is the number of flow graph edges, N is the number of flow graph nodes.
3. Cyclomatic complexity, V (G) for a flow graph, G is also defined as:
V (G) = P+1
Where P is the number of predicate nodes contained in the flow graph G.

* Graph Matrices - The procedure for deriving the flow graph and even determining a set of basis paths is amenable to mechanization. To develop a software tool that assists in basis path testing, a data structure, called a graph matrix can be quite useful.
A Graph Matrix is a square matrix whose size is equal to the number of nodes on the flow graph. Each row and column corresponds to an identified node, and matrix entries correspond to connections between nodes.

* Control Structure Testing - Described below are some of the variations of Control Structure Testing.

Condition Testing:
Condition testing is a test case design method that exercises the logical conditions contained in a program module.

Data Flow Testing:
The data flow testing method selects test paths of a program according to the locations of definitions and uses of variables in the program.

* Loop Testing - Loop Testing is a white box testing technique that focuses exclusively on the validity of loop constructs. Four classes of loops can be defined: Simple loops, Concatenated loops, nested loops, and unstructured loops.

Simple Loops:
The following sets of tests can be applied to simple loops, where ‘n’ is the maximum number of allowable passes through the loop.
1. Skip the loop entirely.
2. Only one pass through the loop.
3. Two passes through the loop.
4. ‘m’ passes through the loop where m5. n-1, n, n+1 passes through the loop.

Nested Loops:
If we extend the test approach from simple loops to nested loops, the number of possible tests would grow geometrically as the level of nesting increases.
1. Start at the innermost loop. Set all other loops to minimum values.
2. Conduct simple loop tests for the innermost loop while holding the outer loops at their minimum iteration parameter values. Add other tests for out-of-range or exclude values.
3. Work outward, conducting tests for the next loop, but keep all other outer loops at minimum values and other nested loops to “typical” values.
4. Continue until all loops have been tested.

Concatenated Loops:
Concatenated loops can be tested using the approach defined for simple loops, if each of the loops is independent of the other. However, if two loops are concatenated and the loop counter for loop 1 is used as the initial value for loop 2, then the loops are not independent.

Unstructured Loops:
Whenever possible, this class of loops should be redesigned to reflect the use of the structured programming constructs

Black Box Testing Techniques

* Graph Based Testing Methods - Software testing begins by creating a graph of important objects and their relationships and then devising a series of tests that will cover the graph so that each objects and their relationships and then devising a series of tests that will cover the graph so that each object and relationship is exercised and error is uncovered.

* Error Guessing - Error guessing is a skill that is well worth cultivating since it can make testing much more effective and efficient - two extremely important goals in the testing process. Typically, the skill of Error Guessing comes with experience with the technology and the project. Error Guessing is the art of guessing where errors can be hidden. There are no specific tools and techniques for this, but you can write test cases depending on the situation: Either when reading the functional documents or when you are testing and find an error that you have not documented.

* Boundary Value Analysis - In boundary value analysis, test cases are generated using the extremes of the input domaini, e.g. maximum, minimum, just inside/outside boundaries, typical values, and error values. BVA is similar to Equivalence Partitioning but focuses on "corner cases".

Advantages of Boundary Value Analysis
1. Robustness Testing - Boundary Value Analysis plus values that go beyond the limits
2. Min - 1, Min, Min +1, Nom, Max -1, Max, Max +1
3. Forces attention to exception handling
4. For strongly typed languages robust testing results in run-time errors that abort normal execution

Limitations of Boundary Value Analysis
BVA works best when the program is a function of several independent variables that represent bounded physical quantities
1. Independent Variables
o NextDate test cases derived from BVA would be inadequate: focusing on the boundary would not leave emphasis on February or leap years
o Dependencies exist with NextDate's Day, Month and Year
o Test cases derived without consideration of the function
2. Physical Quantities
o An example of physical variables being tested, telephone numbers - what faults might be revealed by numbers of 000-0000, 000-0001, 555-5555, 999-9998, 999-9999?

* Equivalence Partitioning - Equivalence partitioning is a black box testing method that divides the input domain of a program into classes of data from which test cases can be derived.
EP can be defined according to the following guidelines:
1. If an input condition specifies a range, one valid and one two invalid classes are defined.
2. If an input condition requires a specific value, one valid and two invalid equivalence classes are defined.
3. If an input condition specifies a member of a set, one valid and one invalid equivalence class is defined.
4. If an input condition is Boolean, one valid and one invalid class is defined.

* Comparison Testing - There are situations where independent versions of software be developed for critical applications, even when only a single version will be used in the delivered computer based system. It is these independent versions which form the basis of a black box testing technique called Comparison testing or back-to-back testing.

* Orthogonal Array Testing - The Orthogonal Array Testing Strategy (OATS) is a systematic, statistical way of testing pair-wise interactions by deriving a suitable small set of test cases (from a large number of possibilities).

Testing types and Testing techniques

Testing types

Testing types refer to different approaches towards testing a computer program, system or product. The two types of testing are Black box testing and White box testing, which would both be discussed in detail in this chapter. Another type, termed as Gray box testing or Hybrid testing is evolving presently and it combines the features of the two types.

Testing Techniques

Testing techniques refer to different methods of testing particular features a computer program, system or product. Each testing type has its own testing techniques while some techniques combine the feature of both types.

Black box testing techniques:

* Graph Based Testing Methods
* Error Guessing
* Boundary Value analysis
* Equivalence partitioning
* Comparison Testing
* Orthogonal Array Testing

White box testing techniques:

* Basis Path Testing
* Flow Graph Notation
* Cyclomatic Complexity
* Graph Matrices
* Control Structure Testing
* Loop Testing

Difference between Testing Types and Testing Techniques?

Testing types deal with what aspect of the computer software would be tested, while testing techniques deal with how a specific part of the software would be tested.

That is, testing types mean whether we are testing the function or the structure of the software. In other words, we may test each function of the software to see if it is operational or we may test the internal components of the software to check if its internal workings are according to specification.

On the other hand, ‘Testing technique’ means what methods or ways would be applied or calculations would be done to test a particular feature of a software (Sometimes we test the interfaces, sometimes we test the segments, sometimes loops etc.)