What is Transaction in sql server?

Hi  guys,

 This article we will discuss about transaction and properties of transaction in sql server.

Transaction:-

 A transaction is units of work in which all statements are processed successfully or none of the statements are processed.

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.

Transaction Control:

There are following commands used to control transactions:

·         COMMIT: to save the changes.

·         ROLLBACK: to rollback 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 committed in the database.

What is constructor and types of constructor:-

Hi all,

 This article we discuss what is constructor and how many types of constructor available in c sharp.

What is Constructor?

A constructor is the special method of a class that is called automatically when an object of that class is created. The constructor initializes class parameters and has the same name as the class. It has no return type.

Types of constructors:-

Basically 5 types of constructors available.

These are:-

1:-Default Constructor.

2:-Parameterized constructor.

3:-Copy    constructor(c sharp doesn’t support it)

4:-Static constructor.

5:-Private constructor.

Default Constructor: - A constructor without any parameters is called as default constructor. Drawback of default constructor is every instance of the class will be initialized to same values and it is not possible to initialize each instance of the class to different values.

Parameterized Constructor: - A constructor with at least one parameter is called as parameterized constructor. Advantage of parameterized constructor is you can initialize each instance of the class to different values.

Copy Constructor: - A parameterized constructor that contains a parameter of same class type is called as copy constructor. Main purpose of copy constructor is to initialize new instance to the values of an existing instance.

Static Constructor: - You can create a constructor as static and when a constructor is created as static, it will be invoked only once for any number of instances of the class and it is during the creation of first instance of the class or the first reference to a static member in the class. Static constructor is used to initialize static fields of the class and to write the code that needs to be executed only once.

Private Constructor : -You can also create a constructor as private. When a class contains at least one private constructor, then it is not possible to create an instance for the class. Private constructor is used to restrict the class from being instantiated when it contains every member as static. 

Temporary Table vs. Table Variable:-

This article we will discuss about temporary table and table variable. What is the difference between both.

Temporary Table	Table Variable
1- Syntax-  create  table  #temp	1-Syntax- Declare @tablename Table
2-It support DDL operation :-like drop, create, alter etc.	2- It doesn’t support DDL operation.
3-It participate the explicitly transactions defined by the user.	3- It doesn’t participate in the explicit transactions defined by the user.
4- It doesn’t allowed in  UDF	4- It can be used in user defined functions
5- It support adding indexes explicitly after table creation.	5-It doesn’t adding indexes explicitly.

Var vs. Dynamic:-

This article we will discuss what  is the difference between var  and Dynamic in c sharp 4.0

Var	Dynamic
1- It is a keyword that is used for implicit variable declaration at compile time.	1 It is a keyword used for variable declaration at runtime.
2- errors caught at compile times	2- Errors caught at runtimes.
3- Var cannot be used as the return type of methods and can’t used to declare variables at class level.	3- Dynamic can also be used as return type of the methods.
4-It is type safe i.e. Compiler has all information about the stored value, so that it doesn't cause any issue at run-time.	4-It is not type safe i.e. Compiler doesn't have any information about the type of variable.

Remove Duplicate record from a table using CTE :-

This article we will discuss how to remove duplicate record from a table using CTE. Without using TOP, MAX.   There is a  table name Employee and three columns like EmpId, EmpName, Salary.

Before writing query using CTE first we learn what is CTE:

  Common Table Expression are temporary result sets which is defined within the execution scope of a single select , insert ,update, delete or createview statement.

Adv of CTE:-

1:- Can be used to create a recursive query.

2:- Can be substituted for a view.

3:- Can reference itself multiple times..

4:-Improve readability.

5:- Easy maintenance of complex query.

Query for remove record:-

With delrecord as

(

Select * , Row_Number() over(partition by EmpId, EmpName, Salary order by Name desc) as mydel

From Emplyee)

Delete from delrecord where mydel > 1

Difference between Primary key and Foreign key

Hi Dear,

 This article we will discuss what is the difference between primary key and

 Foreign key. It is also a common interview question. Most of time at interview you will face this question.

Primary key	Foreign Key
1- Primary key uniquely identify a record in the table	1- Foreign key is a field in the table that is primary key in another table
2-  Primary Key can't accept null values	2- Foreign key can accept multiple null values.
3- We can have only one Primary key in a table.	3- We can have more than one foreign key in a table

Difference between Server.Transfer and Response.Redirect

Dear  All,

 This article i am explaining differences between Abstract classes and Interfaces.

  

Response. Redirect   vs   Server. Transfer:-

Response. Redirect	Server. Transfer
1-It involues a round trips to server	1-It doesn’t involves a round trips
2- It redirect to an external server.	2-It redirect to only same server.
3- It used for both .aspx and html pages	3- It used for only .aspx pages.
4-  We can’t preserve our information	4- We can preserve our information.