What is transaction ?

                               A transaction is one or more actions that are defined as a single unit of work. In the Relational Database Management System (RDBMS) world they also comply with ACID properties:

Atomic(ity) - The principle that each transaction is 'all-or-nothing', i.e. it either succeeds or it fails, regardless of external factors such as power loss or corruption. On failure or success, the database is left in either the state in which it was in prior to the transaction or a new valid state. The transaction becomes an indivisible unit.


Consistency - The principle that the database executes transactions in a consistent manner, obeying all rules (constraints). For example, consider the following table:



CREATE TABLE dbo.MyTestTable (
 ColA SMALLINT,
 CONSTRAINT uq_ColA UNIQUE )



Now consider the following valid transactions that will leave the database in a consistent state:
• INSERT INTO dbo.MyTestTable VALUES (1)
• INSERT INTO dbo.MyTestTable VALUES (9)
But the following statements, if executed and allowed to modify data, will leave the database in an inconsistent state, since they violate some constraint (or allowed datatype) of the defined table. Hence an error is returned:
• INSERT INTO dbo.MyTestTable VALUES ('Hello')
• INSERT INTO dbo.MyTestTable VALUES (3),(3),(3)


Isolation - This property means that each transaction is executed in isolation from others, and that concurrent transactions do not affect the transaction. This property level is variable, and as this article will discuss, SQL Server has five levels of transaction isolation depending on the requirements of the database.


Durability - This property means that the data written to the database is durable, i.e. it is guaranteed to be in storage and will not arbitrarily be lost, changed or overwritten unless specifically requested. More formally, it means that once a transaction is committed, no event can 'un-commit' the transaction - it is written and cannot be changed retrospectively unless by another transaction.

What is the difference between a clustered and a nonclustered index?

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.
A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.
You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book’s index.

What is a CTE?

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH TestCTE (id)
AS
(
SELECT id  FROM table
)
SELECT * FROM TestCTE 

A CTE can be used in place of a view in some instances.

How are transactions used?

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

What is Lock,Blocking and Deadlock?

                                            Let's say you have a ball and two children. Only one child can have the ball at any one time. However, if one of the children gets the ball and doesn't let go of it because he's distracted (watching TV, for example), then the other child will not get to play with the ball.

The other child is blocked from that resource.

If we compare this to the TV, for example, several children can watch TV at any one point.

Locks

                             If we move over to the database world, we see that there are different ways to use resources (just like our two examples above). We can perform "reads" or we can perform "writes".

When we want to read the data, there's no reason that other's can't read the data as well--just like two people watching TV. However, if we want to write the data, then we need to make sure that no one else is looking at it. If they are reading it while we're writing it, they will get "dirty" reads. (Meaning, they'll see the data partially written out, which will be invalid.)

In order to insure that these dirty reads never occur, we have two primary types of locks, Read Locks and Exclusive Locks.

Read Lock

                              You can have several different connections reading from the same datasource at any given time. But to insure that no one changes that data while they're reading it, they take out a Read Lock.

Once a connection has a read lock on a piece of data, all other connections must wait until the Read Lock is released before they can write the data. Others can, however, take out Read Locks of their own on that same piece of data.

Exclusive Lock

                            If a connection wants to update/insert/delete a piece of data, they have to take out an exclusive lock. This prevents any other connection from also taking out a lock on the data (making the lock exclusive to that connection).

When a connection has an exclusive lock on the data, no other connections may read from the data. This helps prevent dirty reads by insuring that no one can read the data while its being written.

Blocking

                          "Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read or write to it. It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.

Compare this to the case with a child holding the ball. The child holding the ball is blocking all other children from holding the ball.

Deadlock

    Deadlocks can happen when you have two connections that each have a lock, but they want each others resource. In this scenario, it's like two children that each has a ball, but wants the other's ball.

Like children, these connections are not willing to share at all. Each connection needs access to both of the resources in order to continue. However, they are in a state of permanent blocking. In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connections) can have access to both of the resources.

Once that "winning" connection is done, it releases the resources and then the other ("losing") connection can try again to get to both resources.

So, the concept of a deadlock is where you have two resources that are blocking each other.

---

What are defaults? Is there a column to which a default cannot be bound?

                        A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them.

Can I call trigger in procedure ?

   No, Trigger is special type of event , which is implicitly called .
We can not call it explicitly  .
 
So We can not call trigger in procedure .

What is trigger?

                                              Trigger is block of sql statement.  In simple word it is event which is raised when there is any DML or DDL operation performed.

Question related Trigger :

1.Can I call trigger ?

Ans - No,You can not call trigger explicitely.It will be called implecitely.

2.How many type of triggr are in sql server?

Ans - Mainly two type of trigger are in sql server.

1.DDL trigger : which will be raised when DDL command will be performaned like DROP ,CREAT,Truncate at database level

2.DML trigger : which will be raised when DML command will be performed like insert,update and delete

   Also DML tigger is divided into two type

1) INSTEAD OF TRIRRGER
2) AFTER TRIGGER

How to delete all view of database .

 I am explaining this with concept of  Dynamic sql .

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+'DROP VIEW '+name +';' FROM sys.views;
EXEC(@sql);

It will delete all view of your database.

Difference between Primary key and unique key

Hi,
   Primary key and unique key both are constrain .Which are used for saving unique data.
So difference must be there -

  Basic diference is -
 Primary key does not support NULL values But Unique key support  NULL value.

note : Unique key support  only one NULL value.

What is Collation in SQL Server?

Hi,
 Collation refers to a set of rules that determine how data is sorted and compared.
In sql server we have four type of Collation .

Case Sensitivity

If A and a, B and b, etc. are treated in the same way, then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent Sensitivity
If a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.

Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width Sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

On which port sql server run on?can I change It?

Hi

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

Why database devloper do not like to use cursor in sql server?

   Hi,
       Sql server is  set based relational database rather than row based.Cursor work on row based computing.It took time  So it may lead performance issue in realtime.
you may have doubt on what is set base and row based.

Set Based  :
    e.g consider following query

 select * from tblTemp_Sample where sample_id in( '1','2','3','4' )
or
 select * from tblTemp_Sample where sample_id in(  select sample_id from sample1 )
 now above query will  compair result on set basis .


Row Based :
 If we will do programming with cursor then it will do row by row comparison.

Difference between PL SQL and TSQL

  Hi,
     Many beginner  have doubt those who know oracle . In oracle we  write procedural code called as PL /SQL . e.g  trigger ,cursor or  procedure  .

 In sql server we can write same thing e.g   trigger ,cursor or  procedure .But here name is TSQL called  as transact SQL.

 In simple word PL /SQL is used in oracle database and TSQL is used while  dealing  with SQL Server database.

What is Sql server?

      It is relational database management System.It is developed by Microsoft .Primary function of it is to store  and retrive data.We can use in single system or  in network environment.

e.g. if You are beginer so you will use on your local system.
       When you will be working as devloper your database may be thousand mile away on other system so you will access it through internet.

  In sql server we can  write query or TSQL (Transact Sql) code for accessing data.

First release of Sql server was in 1989 v1.0. Current sql server release is  Sql Server 2012 called as (DENALI) .