Transaction

What is Transaction.

A transaction is a set of operations performed so all operations are guaranteed to succeed or fail as one unit.


When to Use Transactions

You should use transactions when several operations must succeed or fail as a unit.

The following are some frequent scenarios where use of transactions is recommended:

In batch processing, where multiple rows must be inserted, updated, or deleted as a single unit

Whenever a change to one table requires that other tables be kept consistent

When modifying data in two or more databases concurrently

In distributed transactions, where data is manipulated in databases on various servers.


Understanding ACID Properties.

1. Atomicity:

2. Consistency

3. Isolation

4. Durability




Example:

1. 1. create procedure sp_Trans_Test  
   2. @newpersonid nvarchar(5),  
   3. @newfirstname nvarchar(10)  
   4. @newcompanyname nvarchar(15),  
   5. @oldpersonid nvarchar(5)  
   6. as  
   7. declare @inserr int  
   8. declare @delerr int  
   9. declare @maxerr int  
   10. set @maxerr = 0  
   11. BEGIN TRANSACTION  
   12.   
   13. -- Add a person  
   14. insert into person (personid, firstname, company)  
   15. values(@newpersonid, @newfirstname, @newcompanyname)  
   16.   
   17. -- Save error number returned from Insert statement  
   18. set @inserr = @@error  
   19. if @inserr > @maxerr  
   20. set @maxerr = @inserr  
   21.   
   22. -- Delete a person  
   23. delete from person  
   24. where personid = @oldpersonid  
   25.   
   26. -- Save error number returned from Delete statement  
   27. set @delerr = @@error  
   28. if @delerr > @maxerr  
   29. set @maxerr = @delerr  
   30.   
   31. -- If an error occurred, roll back  
   32. if @maxerr <> 0  
   33. begin  
   34. ROLLBACK  
   35. print 'Transaction rolled back'  
   36. end  
   37. else  
   38. begin  
   39. COMMIT  
   40. print 'Transaction committed'  
   41. end  
   42. print 'INSERT error number:'+ cast(@inserras nvarchar(8))  
   43. print 'DELETE error number:'+ cast(@delerras nvarchar(8))  
   44. return @maxerr

Types Of Keys In Database

Database supports the following types of keys.

• Super Key
• Minimal Super Key
• Candidate Key
• Primary Key
• Unique Key
• Alternate Key
• Composite Key
• Foreign Key
• Natural Key
• Surrogate Key

Key In Sql:

Composite Key:

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

Columns that make up the composite key can be of different data types.

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, 

but when it taken individually it does not guarantee uniqueness.

A primary key that is made by the combination of more than one attribute is known as a composite key.

CREATE TABLE SAMPLE_TABLE  

(COL1 integer,  

COL2 nvarchar(30),  

COL3 nvarchar(50),  

PRIMARY KEY (COL1, COL2)); (Composite Key)

Candidate Key:

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In the below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

AlterNate key:

Alternate key is a secondary key it can be simple to understand by an example:

Let's take an example of student it can contain NAME, ROLL NO., ID and CLASS.

Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. 

So when we talk about alternate key, 

the column may not be primary key but still it is a unique key in the column.

Super Key:

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. 

Example: Primary key, Unique key, Alternate key are a subset of Super Keys

TUPLE

A tuple is a data structure that contains a sequence of elements of different data types. 

It can be used where you want to have a data structure to hold an object with properties, but you don't want to create a separate type for it.

Tuple<T1, T2, T3, T4, T5, T6, T7, TRest>

Tuple<int, string, string> person = 
                        new Tuple <int, string, string>(1, "Steve", "Jobs");

Example: Accessing Tuple Elements

var person = Tuple.Create(1, "Steve", "Jobs");
person.Item1; // returns 1
person.Item2; // returns "Steve"
person.Item3; // returns "Jobs"


var numbers = Tuple.Create("One", 2, 3, "Four", 5, "Six", 7, 8);
numbers.Item1; // returns "One"
numbers.Item2; // returns 2
numbers.Item3; // returns 3
numbers.Item4; // returns "Four"
numbers.Item5; // returns 5
numbers.Item6; // returns "Six"
numbers.Item7; // returns 7
numbers.Rest; // returns (8)
numbers.Rest.Item1; // returns 8





Usage of Tuple

Tuples can be used in the following scenarios:

When you want to return multiple values from a method without using ref or outparameters.
When you want to pass multiple values to a method through a single parameter.
When you want to hold a database record or some values temporarily without creating a separate class.


Tuple Limitations:

Tuple is a reference type and not a value type. It allocates on heap and could result in CPU intensive operations.
Tuple is limited to include 8 elements. You need to use nested tuples if you need to store more elements. However, this may result in ambiguity.
Tuple elements can be accessed using properties with a name pattern Item<elementNumber> which does not make sense.

IsNull and COALESCE

IsNull:

Return the specified value IF the expression is NULL, otherwise return the expression:

SELECT ISNULL(NULL, 'W3Schools.com');


In SQL Server, the ISNULL( ) function is used to replace NULL value with another value.

Store_Name	Sales
Store A	300
Store B	NULL

SELECT SUM (ISNULL(Sales,100)) FROM Sales_Data;

returns the following result:

SUM (ISNULL(Sales,100))

400

COALESCE:

Return the first non-null value in a list:

SELECT COALESCE(NULL, NULL, NULL, 'W3Schools.com', NULL, 'Example.com');

Output:W3Schools.com

What is char, var, nchar , nvarchar

CHAR vs VARCHAR

CHAR:

• It is a fixed length data type
• Used to store non-Unicode characters
• Occupiers 1 byte of space for each character

1. DECLARE @string CHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

VARCHAR:

• It is a variable length data type
• Used to store non-Unicode characters
• Occupies 1 byte of space for each character

1. DECLARE @string VARCHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

NCHAR:

• Is a fixed length data type
• Used to store Unicode characters (for example the languages Arabic, German and so on)
• Occupies 2 bytes of space for each character

1. DECLARE @string NCHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

 

NVARCHAR:

• It is a variable-length data type
• Used to store Unicode characters
• Occupies 2 bytes of space for each character

1. DECLARE @string NVARCHAR(20)  
2. SET @string = 'Robin'  
3. SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len' 

When to use what?

If your column will store a fixed-length Unicode characters like French, Arabic and so on characters then go for NCHAR. If the data stored in a column is Unicode and can vary in length, then go for NVARCHAR. 

 

Querying to NCHAR or NVARCHAR is a bit slower then CHAR or VARCHAR. So don't go for NCHAR or NVARCHAR to store non-Unicode characters even though this data type supports that. 

Identity in SQL

What is Identity in Sql:



An identity column of a table is a column whose value increases automatically. The value in an identity column is crated by the server. A user generally cannot insert a value into an identity column.


Example:

1. CREATE TABLE EMPLOYEE  
2. (  
3.    IID INT IDENTITY(1,1),  
4.    NAME [varchar](MAX) NOT NULL,  
5.    AGE INT NOT NULL  
6. )  

IDENTITY_INSERT ON allows a user to insert data into an Identity column and IDENTITY_INSERT OFF restricts a user from inserting data into an Identity column.