What is View in SQL ?

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

A View is a "Virtual Table". It is not like a simple table, but is a virtual table which contains columns and data from different tables (may be one or more tables). A View does not contain any data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View.

Use of a View

Views are used as security mechanisms in databases. Because it restricts the user from viewing certain column and rows. Views display only those data which are mentioned in the query, so it shows only data which is returned by the query that is defined at the time of creation of the View. The rest of the data is totally abstract from the end user.

Along with security, another advantage of Views is data abstraction because the end user is not aware of all the data in a table.

Views are used for several different reasons:

1. To hide data complexity. Instead of forcing your users to learn the T-SQL JOIN syntax you might wish to provide a view that runs a commonly requested SQL statement.
    
2. To protect the data. If you have a table containing sensitive data in certain columns, you might wish to hide those columns from certain groups of users. For instance, customer names, addresses and their social security numbers might all be stored in the same table; however, for lower level employees like shipping clerks, you can create a view that only displays customer name and address. You can grant permissions to a view without allowing users to query the underlying tables. There are a couple of ways you might want to secure your data:
    
   1. Create a view to allow reading of only certain columns from a table. A common example of this would be the salary column in the employee table. You might not want all personnel to be able to read manager's or each other's salary. This is referred to as partitioning a table vertically and is accomplished by specifying only the appropriate columns in the CREATE VIEW statement.
       
   2. Create a view to allow reading only certain rows from a table. For instance, you might have a view for department managers. This way, each manager can provide raises only to the employees of his or her department. This is referred to as horizontal partitioning and is accomplished by providing a WHERE clause in the SELECT statement that creates a view.
       
3. Enforcing some simple business rules. For example, if you wish to generate a list of customers that need to receive the fall catalog, you can create a view of customers that have previously bought your shirts during the fall.
    
4. Data exports with BCP. If you are using BCP to export your SQL Server data into text files, you can format the data through views since BCP's formatting ability is quite limited.
    
5. Customizing data. If you wish to display some computed values or column names formatted differently than the base table columns, you can do so by creating views.

Disadvantages

Even though views can be a great tool for securing and customizing data, they can be slow. Indeed, they are not any faster than the query that defines them. With SQL Server 2000, indexed views (also referred to as "materialized" views) are supported to overcome this limitation. 

What is index in SQL Server ?

Index in sql is created on existing tables to retrieve the rows quickly.

When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

Syntax to create Index

CREATE INDEX index_name
ON table_name (column_name)

One of the most important routes to high performance in a SQL Server database is the index. Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. In this article, I provide an overview of SQL Server indexes and explain how they’re defined within a database and how they can make the querying process faster. Most of this information applies to indexes in both SQL Server 2005 and 2008; the basic structure has changed little from one version to the next. In fact, much of the information also applies to SQL Server 2000. This does not mean there haven’t been changes. New functionality has been added with each successive version; however, the underlying structures have remained relatively the same. So for the sake of brevity, I stick with 2005 and 2008 and point out where there are differences in those two versions.

Index Structures

Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

You can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar(max). You can also create indexes on XML columns, but those indexes are slightly different from the basic index and are beyond the scope of this article. Instead, I'll focus on those indexes that are implemented most commonly in a SQL Server database.

An index is made up of a set of pages (index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom

When a query is issued against an indexed column, the query engine starts at the root node and navigates down through the intermediate nodes, with each layer of the intermediate level more granular than the one above. The query engine continues down through the index nodes until it reaches the leaf node. For example, if you’re searching for the value 123 in an indexed column, the query engine would first look in the root level to determine which page to reference in the top intermediate level. In this example, the first page points the values 1-100, and the second page, the values 101-200, so the query engine would go to the second page on that level. The query engine would then determine that it must go to the third page at the next intermediate level. From there, the query engine would navigate to the leaf node for value 123. The leaf node will contain either the entire row of data or a pointer to that row, depending on whether the index is clustered or nonclustered.

Clustered Indexes

A clustered index stores the actual data rows at the leaf level of the index. Returning to the example above, that would mean that the entire row of data associated with the primary key value of 123 would be stored in that leaf node. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table.

Note: A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

Nonclustered Indexes

Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

A row locator’s structure depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.

Nonclustered indexes cannot be sorted like clustered indexes; however, you can create more than one nonclustered index per table or view. SQL Server 2005 supports up to 249 nonclustered indexes, and SQL Server 2008 support up to 999. This certainly doesn’t mean you should create that many indexes. Indexes can both help and hinder performance, as I explain later in the article.

In addition to being able to create multiple nonclustered indexes on a table or view, you can also add included columns to your index. This means that you can store at the leaf level not only the values from the indexed column, but also the values from non-indexed columns. This strategy allows you to get around some of the limitations on indexes. For example, you can include non-indexed columns in order to exceed the size limit of indexed columns (900 bytes in most cases).

Index Types

In addition to an index being clustered or nonclustered, it can be configured in other ways:

• Composite index: An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes.
• Unique Index: An index that ensures the uniqueness of each value in the indexed column. If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. For example, if you were to create an index on the FirstName and LastName columns in a table, the names together must be unique, but the individual names can be duplicated.

A unique index is automatically created when you define a primary key or unique constraint:

• Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, nonclustered index on the primary key.
• Unique: When you define a unique constraint, SQL Server automatically creates a unique, nonclustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table.

• Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns

To see demo please see Index demo

Also see very good explanation of the clustered and non- clustered index here

Binding in WCF Services ?

Windows Communication Foundation (WCF) separates how the software for an application is written from how it communicates with other software. Bindings are used to specify the transport, encoding, and protocol details required for clients and services to communicate with each other. WCF uses bindings to generate the underlying wire representation of the endpoint, so most of the binding details must be agreed upon by the parties that are communicating. The easiest way to achieve this is for clients of a service to use the same binding that the endpoint for the service uses. For more information about how to do this, see Using Bindings to Configure Windows Communication Foundation Services and Clients.

A binding is made up of a collection of binding elements. Each element describes some aspect of how the endpoint communicates with clients. A binding must include at least one transport binding element, at least one message-encoding binding element (which the transport binding element can provide by default), and any number of other protocol binding elements. The process that builds a runtime out of this description allows each binding element to contribute code to that runtime.

WCF provides bindings that contain common selections of binding elements. These can be used with their default settings or you can modify those default values according to user requirements. These system-provided bindings have properties that allow direct control over the binding elements and their settings. You can also easily work side-by-side with multiple versions of a binding by giving each version of the binding its own name. For details, see Configuring System-Provided Bindings.

If you need a collection of binding elements not provided by one of these system-provided bindings, you can create a custom binding that consists of the collection of binding elements required. These custom bindings are easy to create and do not require a new class, but they do not provide properties for controlling the binding elements or their settings. You can access the binding elements and modify their settings through the collection that contains them. For details, see Custom Bindings.

To specify in code to use the BasicHttpBinding for the service :

1. Define a service contract for the type of service.

[ServiceContract]
public interface ICalculator
{
   [OperationContract]
   double Add(double n1, double n2);
   [OperationContract]
   double Subtract(double n1, double n2);
   [OperationContract]
   double Multiply(double n1, double n2);
   [OperationContract]
   double Divide(double n1, double n2);
}

2. Implement the service contract in a service class.

public class CalculatorService : ICalculator
{
   public double Add(double n1, double n2)
   {
      return n1 + n2;
   }
   public double Subtract(double n1, double n2)
   {
      return n1 - n2;
   }
   public double Multiply(double n1, double n2)
   {
      return n1 * n2;
   }
   public double Divide(double n1, double n2)
   {
      return n1 / n2;
   }
} 

3. In the hosting application, create the base address for the service and the binding to use with the service.

// Specify a base address for the service

String baseAddress = "http://localhost/CalculatorService";
// Create the binding to be used by the service.

BasicHttpBinding binding1 = new BasicHttpBinding();

4. Create the host for the service, add the endpoint, and then open the host.

using(ServiceHost host = new ServiceHost(typeof(CalculatorService)))
{
    host.AddServiceEndpoint(typeof(ICalculator),binding1, baseAddress);



    host.Open();
}    



To modify the default values of the binding properties ;


To modify one of the default property values of the BasicHttpBinding class, set the property value on the binding to the new value before creating the host. For example, to change the default open and close timeout values of 1 minute to 2 minutes, use the following.





TimeSpan modifiedCloseTimeout = new TimeSpan(00, 02, 00);
binding1.CloseTimeout = modifiedCloseTimeout;







If you dont want to add the service endpoint using code behind, then you can usethe Configuration method of defining service binding in web.config file as below

Create a Web.config file to configure an endpoint for the CalculatorService that uses the WSHttpBinding.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.serviceModel>
    <services>
      <service name=" CalculatorService" >
        <endpoint 
        <-- Leave the address blank to be populated by default-->
        <--from the hosting environment,in this case IIS, so -->
        <-- the address will just be that of the IIS Virtual -->
        <--Directory.-->
            address="" 
        <--Specify the binding type -->
            binding="wsHttpBinding"
        <--Specify the binding configuration name for that -->
        <--binding type. This is optional but useful if you  -->
        <--want to modify the properties of the binding. -->
        <--The bindingConfiguration name Binding1 is defined  -->
        <--below in the bindings element.  -->
            bindingConfiguration="Binding1"
            contract="ICalculator" />
      </service>
    </services>
    <bindings>
      <wsHttpBinding>
        <binding name="Binding1">
          <-- Binding property values can be modified here. -->
          <--See the next procedure. -->
        </binding>
      </wsHttpBinding>
   </bindings>
  </system.serviceModel>
</configuration>

Then Create a Service.svc file that contains the following line and place it in your Internet Information Services (IIS) virtual directory.

<%@ServiceHost language=c# Service="CalculatorService" %>



To modify the default values of the binding properties


To modify one of the default property values of the WSHttpBinding, create a new binding configuration name - <binding name="Binding1"> - within the<wsHttpBinding> element and set the new values for the attributes of the binding in this binding element. For example, to change the default open and close timeout values of 1 minute to 2 minutes, add the following to the configuration file.






 <wsHttpBinding>
        <binding name="Binding1"
                 closeTimeout="00:02:00"
                 openTimeout="00:02:00">
        </binding>
      </wsHttpBinding>







Thanks For Reading..!!
Default Programmer

What is Having Clause in SQL Server ?

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function. (Reference :BOL)

Example of HAVING and WHERE in one query:

SELECT BloggerName,AVG(total) AS ‘Average’ FROM blogcount

WHERE Topic=‘SQL’

GROUP BY BloggerName

HAVING AVG(total)>25

Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

Thanks For Reading..!!

Default Programmer

What is a Dirty Read ?

Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value.

While you can easily command a database to disallow dirty reads, this usually degrades the performance of your application due to the increased locking overhead. Disallowing dirty reads also leads to decreased system concurrency. 

What is Connection Pooling in SQL Server ?

Connecting to a database server typically consists of several time-consuming steps. A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

In practice, most applications use only one or a few different configurations for connections. This means that during application execution, many identical connections will be repeatedly opened and closed. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling.

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application callsClose on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration. Connections are separated into pools by connection string, and by Windows identity when integrated security is used. Connections are also pooled based on whether they are enlisted in a transaction.

Pooling connections can significantly enhance the performance and scalability of your application. By default, connection pooling is enabled in ADO.NET. Unless you explicitly disable it, the pooler optimizes the connections as they are opened and closed in your application. You can also supply several connection string modifiers to control connection pooling behavior. 

View demo at : http://www.mssqltips.com/sqlservertip/1830/script-to-check-sql-server-connection-pooling/

and at http://consultingblogs.emc.com/jamesrowlandjones/archive/2009/07/25/how-to-tell-if-connections-to-sql-server-are-pooled-or-not.aspx 

Thanks For Reading..!!

Default Programmer

Difference Between Delete and Truncate Command in SQL Server.

Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.

• TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
• You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .
• You cann't rollback data in TRUNCATE( unless it is used in Trasaction)  but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.
• A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.
• If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.
• TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.
• Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.
• TRUNCATE is faster than DELETE.

Thanks For Reading..!!

Default Programmer

What is Collation in SQL Server ?

Collation refers to  set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.

You can check you database collation using below query :

SELECT DATABASEPROPERTYEX('YourDatabaseName', 'Collation') SQLCollation;

if it reutrns Latin1_General_CI_AS it means :

Arguments

SortRules

A string identifying the alphabet or language whose sorting rules are applied when dictionary sorting is specified. Examples are Latin1_General or Polish.

Pref

Specifies uppercase preference.

Codepage

Specifies a one to four digit number identifying the code page used by the collation. CP1 specifies code page 1252, for all other code pages the complete code page number is specified. For example, CP1251 specifies code page 1251 and CP850 specifies code page 850.

CaseSensitivity

CI specifies case-insensitive, CS specifies case-sensitive.

AccentSensitivity

AI specifies accent-insensitive, AS specifies accent-sensitive.

BIN

Specifies the binary sort order is to be used.

Detail can be found at :http://msdn.microsoft.com/en-us/library/aa258233(v=sql.80).aspx

Thanks For Reading..!!

Default programmer

How to send email from an asp.net web application using gmail account

Here is the code which will help you to send email from your gmail account using asp.net application. Copy and paste the below code in click event of the button if you are sending mail using button click.


string from = "demo@gmail.com"; //Replace this with your own correct Gmail Address

string to = "demo123@gmail.com"; //Replace this with the Email Address to whom you want to send the mail

System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
mail.To.Add(to);
 mail.From = new MailAddress(from,"demo" , System.Text.Encoding.UTF8);
mail.Subject = "This is a test mail";
mail.SubjectEncoding = System.Text.Encoding.UTF8;
mail.Body = "This is Email Body Text";
mail.BodyEncoding = System.Text.Encoding.UTF8;
mail.IsBodyHtml = true ;
mail.Priority = MailPriority.High;
SmtpClient client = new SmtpClient();
//Add the Creddentials- use your own email id and password

 client.Credentials = new System.Net.NetworkCredential(from, "password");

client.Port = 587; // Gmail works on this port
client.Host = "smtp.gmail.com";
client.EnableSsl = true; //Gmail works on Server Secured Layer
       try
        {
            client.Send(mail);
        }      
       catch (Exception ex)
        {
            Exception ex2 = ex;
            string errorMessage = string.Empty;
            while (ex2 != null)
            {
                errorMessage += ex2.ToString();
                ex2 = ex2.InnerException;
            }
 HttpContext.Current.Response.Write(errorMessage );
        } // end try

Thanks for reading..!!

Default Programmer

Define Address in WCF Services

Windows Communication Foundation provides a flexible model for exposing service endpoints and communicating with them through a variety of different communication protocols. There are  various addressing details that surround endpoint communication, many of which enable more advanced messaging scenarios.

Endpoints and Addresses

The WCF architecture cleanly separates physical communication details from the underlying service implementation. Developers spend most of their time working with familiar Microsoft.NET Framework code to implement service contracts and to define service behavior. After the implementation is complete, they can decide how to host the service and expose it to consumers.

In order to host a particular service, you create a ServiceHost instance and define a collection of service endpoints. A service endpoint specifies an address, a binding, and a contract to use for communication. Windows Communication Foundation needs this information to build the necessary messaging runtime, also known as the channel stack, and to provide metadata in the form of Web Services Description Language (WSDL) to external consumers upon request.

You can supply service endpoints to a ServiceHost instance through code or through entries in the <system.serviceModel> configuration section. Anything you can accomplish in code, you can also accomplish in configuration and vice-versa. However, supplying endpoints via configuration usually provides greater flexibility for making post-development changes.

The ServiceHost must contain at least one service endpoint before you attempt to open it or the runtime will throw an exception; a service without at least one endpoint wouldn’t be very useful since you’d have no way to interact with it. A service can, however, expose more than one endpoint in order to accommodate multiple contracts or different types of consumers that have a wide range of capabilities or constraints.

When discussing endpoints, most folks tend to focus on the binding and contract while overlooking the endpoint address and the various issues surrounding it.

 Defining Endpoints

To use Your Service, you need to host it in a .NET-based application. The ServiceHost class gives you direct control over the WCF hosting infrastructure. You instantiate ServiceHost based on a particular service type. The following code shows how to do this in a console application:

using System;
using System.ServiceModel;
using ServiceLibrary;

class Program
{
    static void Main(string[] args)
    {
        using (ServiceHost host = new ServiceHost(
            typeof(YourServiceName), new Uri("http://localhost:8080/YourServiceName")))
        {
            ...

In addition to specifying the service type, you also specify the base addresses for the different transports you plan to use. In this example, I've specified a base address of http://localhost:8080/YourServiceName. This will be used as the base for any relative HTTP addresses I might specify when adding endpoints. The base HTTP address is also used as the default for retrieving the service description.

You then add the service endpoints. Again, a service may have one or more endpoints and each endpoint consists of an address, a binding, and a contract. You provide this information to your ServiceHost by calling AddServiceEndpoint. This is where you specify the service contract you defined earlier (IServiceInterface). For the binding, you typically choose from one of the many predefined bindings that ship with WCF and an appropriate address given the binding's transport. Here's an example:




host.AddServiceEndpoint(typeof(IServiceInterface), 
    new BasicHttpBinding(), "svc");
host.AddServiceEndpoint(typeof(IServiceInterface), 
    new NetTcpBinding(), "net.tcp://localhost:8081/echo/svc");


In this particular example, I've specified IServiceInterface as the contract for both endpoints, but with each endpoint using a different binding and address. The first endpoint uses the BasicHttpBinding and a relative HTTP address of svc (which would make its absolute address http://localhost:8080/YourServiceName/svc). The second endpoint uses the NetTcpBinding and an address of net.tcp://localhost:8081/YourServiceName/svc. Therefore, this service allows consumers to communicate with it over either HTTP or TCP using different WS-* protocols, as defined by each binding.

What is Difference between Update and Exclusive Locks in SQL SERVER ?

When exclusive lock is on process, no other lock can be placed on the row or table. Every other process has to wait until the exclusive lock completes its tasks.
An update lock is a type of exclusive lock, except that it can be placed on the row which already has shared lock on it. Update lock can read the data of the row which has the shared lock. As soon as the update lock is ready to change the data it converts itself to the exclusive lock.

What is Optimistic and Pessimistic Locking in SQL Server ?

Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic. (i.e. hasn't been updated between when you check the version and write the record to the disk) and update the version in one hit.

If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

There are 4 levels of locking in the pessimistic isolation levels from lowest to highest. They are :

1. Read Uncommitted 2. Read Committed 3. Repeatable read 4. Serializable 

Types Of Locks :

1. Shared Locks -> Used for operation that do not change or update data. 

2. Update Locks -> Used on resources that can be updated. 

3.Exclusive locks -> Used for data modelling operations such as INSERT ,UPDATE, DELETE .

4. Intent Locks -> Used To establish a lock hierarchy.

5. Schema Locks -> Used when an operation dependent on the schema of table .

6. Bulk Update Locks -> Used when bulk copying data into a table and the TABLOCK hint is specified.