Saturday, February 21, 2009

ASP.Net 3.5 Performance Boost with Pipeline Optimization

Hi Friends,

Now a days we all are building web sites and forums for increase our popularity or revenue through the advertise.

Now what is in our site that cause a user to visit our site and what is the factor that users are ignoring our site to visit often?

The main factor is functionality and user experience. But even if your site have the best user experience but not the speed, user will slowly dislike your site due to the speed.

So here is a suggestion to optimize your performance of ASP.Net sites with optimizing your pipeline of the execution.

There are several ASP.NET default HttpModules which sit in the request pipeline and intercept each and every request.
for Example Following are default Modules.

<httpModules>
  <add name="OutputCache" type="System.Web.Caching.OutputCacheModule" />
  <add name="Session" type="System.Web.SessionState.SessionStateModule" />
  <add name="WindowsAuthentication" 
        type="System.Web.Security.WindowsAuthenticationModule" />
  <add name="FormsAuthentication" 
        type="System.Web.Security.FormsAuthenticationModule" />
  <add name="PassportAuthentication" 
        type="System.Web.Security.PassportAuthenticationModule" />
  <add name="UrlAuthorization" type="System.Web.Security.UrlAuthorizationModule" />
  <add name="FileAuthorization" type="System.Web.Security.FileAuthorizationModule" />
  <add name="ErrorHandlerModule" type="System.Web.Mobile.ErrorHandlerModule, 
                             System.Web.Mobile, Version=1.0.5000.0, 
                             Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</httpModules>


Now if you want To bust site performance you can Remove Unnecessary modules from pipeline in Web.Config.
For Exam
If we are Using only FormAuthentication we can remove  following Modules.

<httpModules>
         <!-- Remove unnecessary Http Modules for faster pipeline -->
         <remove name="WindowsAuthentication" />
         <remove name="PassportAuthentication" />
         <remove name="AnonymousIdentification" />
         <remove name="UrlAuthorization" />
         <remove name="FileAuthorization" />
</httpModules>

Try this and let me know your review on the performance change. Also please suggest your suggestions which can optimize the performance of the ASP.Net site.

Saturday, November 15, 2008

Tracking User Activity in Web site with ASP.Net C#.Net

Reason to Track User Activity:

Now a days every small business is listed over the Internet. Every business wants to expand and get more business from the World Market. Also some users want to generate revenue from the public ads like Google Adsense.

To obtain higher business a web site owner needs to understand visitor behavior. Now to understand the visitor behavior a developer need to log all the activity of a visitor. From which source a user has reached to web site. If the user has came from a search engine then on which keyword he has searched. How user navigate to other page? How much time a user spend on the web page? How many page a user visit in a single visit?

After analyzing above all data a developer can decide how to keep user visiting web site? Which section of the web site have most visit? Where to improve in the user interested content?

Tools for Tracking User Activity:

To track the user activity there are many Online tools are available like Google Analytics or MyBlogLog. Google Analytics is a free user tracking tool provided by Google. With Google Analytics you can analyze your visit and monitor visit from different country, visit from different sources, visit on different keywords. You can also monitor entering and exiting page. MyBlogLog is providing free service but it is limited. If you want a full service you need to upgrade to MyBlogLog Pro which is paid.

Building Interactive User Interfaces with Microsoft ASP.NET AJAX: Enabling Bookmarking and the Browser's Back Button

Introduction
AJAX applications offer a more interactive user experience by replacing traditional full page postbacks with leaner and more efficient partial page postbacks. These partial page postbacks are executed asynchronously using JavaScript code in the browser. When a web surfer clicks on a link or submits a form (via a full page postback) the browser automatically adds the page being left to the browser's history. This allows the web surfer to use his Back and Forward buttons to navigate through this history. However, the partial page postbacks performed by AJAX applications do not cause the browser to register anything in their history. As a consequence, if a user visits an AJAX-enabled web page, performs a number of partial page postbacks, and then clicks the Back button, she is not returned to the state of the page prior to the last partial page postback. Instead, she is taken back to the page she was at before arriving at the AJAX-enabled web page.

The good news is that starting with ASP.NET 3.5 SP 1, the ScriptManager control in the ASP.NET AJAX Framework includes functionality for creating history points in an AJAX-enabled web page. Adding a history point creates an entry in the browser's history for a particular page state. What's more, this page state is encoded in the querystring of the browser, meaning that visitors can bookmark a particular state of an AJAX application.

This article shows how to add history points using the ScriptManager control. In particular, it shows how to record history points whenever the user pages or sorts a GridView. Read on to learn more!

A Quick Overview of the ASP.NET AJAX History Feature
As noted in the Introduction, the history feature became part of the ASP.NET AJAX Framework in ASP.NET version 3.5, SP 1. (For a link to download SP1, and for more information on the features included in SP1, see: ASP.NET in .NET 3.5 Service Pack 1.) It was originally part of the Microsoft ASP.NET Futures, although at that point in time this functionality was implemented using a History Web control. In the ASP.NET 3.5 SP1 release, this functionality was moved to the ScriptManager control.

The history feature allows a page developer to create "states" in the lifecycle of an AJAX-enabled web page. Such a "state" is created by adding a history point. Once defined, these "states" are accessible from the browser's Back button; moreover, they can be bookmarked. If you use GMail as your online email account then you've already encountered an AJAX application that uses such history "states." Whenever you read an email in GMail, the email message is loaded asynchronously via JavaScript calls back to Google's servers. Your browser's querystring is also updated to include the ID of the message. If you hit the Back button you return to your GMail Inbox (and not the page you were visiting before going to GMail). Likewise, you can bookmark the page you are on while reading a particular email message. If you later visit that bookmark the GMail interface will automatically load the same mail message.

The ASP.NET AJAX history feature works in the following manner:

  • You, the page developer, decide what actions are cause for a history point to be added. These actions can be client-side actions (clicking on an HTML element) or server-side actions triggered by a partial page postback. The set of actions that should cause a history point depend on the web page and its user interface. The download available at the end of this article includes two pages that display a sortable and page-able GridView. In one page, each sort action causes the insertion of a history point; in the other, both sorting and paging actions cause the insertion of a history point.
  • When an action that should cause the insertion of a history point fires, you need to add a history point. This is done by creating some string that models the state and registering that history state with the ScriptManager control. You can also update the page's Title based on the state so that the browser's title bar and history information includes an apt description for each state.
  • Finally, you need to create an event handler for the ScriptManager's Navigate event. This event fires whenever the user reaches the page by clicking the Back or Forward buttons or when coming in from a bookmark. The Navigate event handler is responsible for restoring the history state.
In short, the history feature allows you to say, in response to certain user actions, "I want to save the application's current state." Doing so injects the current page information in the browser's history and encodes the state that you specify in the querystring. Later, if a user returns to this "state" by hitting the Back or Forward buttons or via a bookmark, you must restore that state.

The remainder of this article looks at how to save a history point whenever a GridView is sorted, and how to restore that state when needed. Let's get started!

Paging and Sorting Through Products in an AJAX-Enabled Web Page
Before we concern ourselves with ASP.NET AJAX's history feature, let's start by creating and demonstrating the standard AJAX behavior with regard to the Back button and bookmarks. The demo available for download at the end of this article includes an ASP.NET page named StandardBehavior.aspx that contains a sortable and page-able GridView that lists the contents of the Products table in the Northwind database. The GridView is placed within an UpdatePanel and therefore any postbacks triggered by the GridView are handled as partial page postbacks.

<asp:ScriptManager ID="MyScriptManager" runat="server"> 
</asp:ScriptManager> 
<asp:UpdatePanel ID="UpdatePanel1" runat="server"> 
   <ContentTemplate> 
      <asp:GridView ID="gvProducts" runat="server" AllowPaging="True" 
      AllowSorting="True" AutoGenerateColumns="False" ...> 
         ... 
      </asp:GridView> 
   </ContentTemplate> 
</asp:UpdatePanel> 

Visit the StandardBehavior.aspx and sort and page through the grid. Each time a sort or paging link is clicked, a partial page postback ensues and the grid's display is updated. Because partial page postbacks are used, the browser does not maintain any sort of history when sorting and paging. Therefore, if you click the Back button you will not be taken back to the previous sort order or the previous page. Instead, you'll be returned to the page you were visiting before you reached the StandardBehavior.aspx page. The following screen shots illustrate this behavior.

Start by Visiting Default.aspx...
Start by visiting Default.aspx...

Click on the "Behavior With History on Sorting" Link. Now Sort and Page through the Grid Several Times. Here is the Grid After I've Sorted by Price and Moved to Page 4...
Sort and page through the grid...

Click Your Browser's Back Button. You are Returned to Default.aspx...
Clicking the Back button returns you to Default.aspx...

Adding a History Point Whenever the User Sorts the Grid
Let's extend our example to save a history point whenever the user sorts the grid. By doing so, each time the user sorts the grid the browser will record a new history state and the browser's URL will have its querystring updated to include the state information (this allows the page to be bookmarked). With recording history points whenever the grid is sorted, the user could sort the grid by Price, then by Name, and then by ProductID. If the user, while viewing the grid sorted by ProductID, clicked his browser's Back button, he would be returned to seeing the grid sorted by Name (rather than being returned to the page visited prior to reaching this page). The demo available for download at the end of this article includes an ASP.NET page named BehaviorWithHistory.aspx that implements this functionality.

Recall earlier that I said there are three things we need to do when using the ASP.NET AJAX history feature:

  1. Decide what actions cause a history point to be inserted.
  2. Add the history point whenever one of the actions from task #1 is performed.
  3. Create an event handler for the ScriptManager's Navigate event that restores the state when the user clicks the Back or Forward buttons, or when he reaches a page via a bookmark.
We've already knocked out task #1 - we've decided to record the history whenever the GridView is sorted. Whenever a GridView is sorted it raises its Sorting event. We need to create an event handler for the Sorting event to add a history point.

A history point is added via a call to the ScriptManager class's AddHistoryPoint method. This method requires that we supply a string that describes the current state. This string will be used later in the ScriptManager's Navigate event handler to reconstruct the state of the application when a page is reached via the Back or Forward buttons, or from a bookmark. Because we are basing the history point on the sort criteria, the GridView's SortExpression and SortDirection properties define the state. Therefore, we need to pass the values of these properties into the AddHistoryPoint method.

The following code shows how I encode the GridView's state and add it as a history point.

protected void gvProducts_Sorting(object sender, GridViewSortEventArgs e) 
{ 
   // Create the history state 
   MyScriptManager.AddHistoryPoint("SortExpression", e.SortExpression); 
   MyScriptManager.AddHistoryPoint("SortDirection", e.SortDirection.ToString()); 
   Page.Title = GetPageTitle(e.SortExpression, e.SortDirection); 
} 

Note that for each item I want to store in the state I call the AddHistoryPoint method, passing in a "name" for the state item and its value. In the above code I register two items in the state, named "SortExpression" and "SortDirection" and storing the values of e.SortExpression and e.SortDirection, respectively.

After saving the state, I update the page's title. The GetPageTitle method generates and returns a suitable title given the passed-in SortExpression and SortDirection values. For instance, when sorting by the UnitPrice column in ascending order the GetPageTitle method returns the string "Viewing Products Sorted by Price (Ascending)". This value is assigned to the Page object's Title property.

If we visit the page with this code in place, each time we sort the grid the URL in the browser's Address bar is updated to include a querystring that specifies the state information. Note the Address bar in the following screen shots. When the BehaviorWithHistory.aspx page is first visited the URL is, as we'd expect, just BehaviorWithHistory.aspx.

When first visiting BehaviorWithHistory.aspx there is no content in the querystring.

But as soon as the grid is sorted the querystring is updated to include the encoded state information. Also the Back button is now available. In the screen shot below the grid is sorted by Price in ascending order and the URL in the Address bar now reads: BehaviorWithHistory.aspx#&&+2toRdN1TcuXKFN4BhNSqrMXMtnAEvRpZEk2MUzyrUhoPiJPO4mIypwAP9J+s7VtZ+uMZ0ub2iUXY5KDMgwufg==.

The grid is now sorted by the Price column; the URL has been updated to include the history state in the querystring.

While the Back button is now click-able, going back does not return us to the previous state of the grid before it was sorted by Price. Rather, we remain on the BehaviorWithHistory.aspx page, but the data is still sorted by Price. This is because we have yet to complete the final step - task #3 - which is to create an event handler for the ScriptManager control's Navigate event. The Navigate event handler fires whenever the user visits a page by clicking the Back or Forward buttons or via a bookmark. The Navigate event handler is responsible for rendering the state as specified through the querystring.

protected void MyScriptManager_Navigate(object sender, HistoryEventArgs e) 
{ 
   if (!string.IsNullOrEmpty(e.State["SortExpression"])) 
   { 
      string sortExpressionFromState = e.State["SortExpression"]; 
      SortDirection sortDirectionFromState = (SortDirection)Enum.Parse(typeof(SortDirection), e.State["SortDirection"]); 
      // Sort the grid according to the sort information in the history state 
      gvProducts.Sort(sortExpressionFromState, sortDirectionFromState); 
      Page.Title = GetPageTitle(sortExpressionFromState, sortDirectionFromState); 
   } 
   else 
   { 
      // Sort the grid by ProductName in ascending order 
      gvProducts.Sort("ProductName", SortDirection.Ascending); 
      Page.Title = GetPageTitle("ProductName", SortDirection.Ascending); 
   } 
} 

The Navigate event handler starts by checking to see if the expected state was passed in. If so, it grabs the values from the history state (via e.State), sorts the GridView, and updates the page's title. If the state information was not passed in, it defaults to the default state - the grid sorted by the ProductName column in ascending order.

That's all there is to it! With this code in place, the Back and Forward buttons work as expected. What's more, a user can bookmark the web page after sorting by a particular column. When the user returns to that bookmarked page, the GridView will load and automatically sort by the appropriate column.

What About Paging?
The BehaviorWithHistory.aspx page adds a history point whenever the user sorts the grid. It does not, however, add a history point when paging. If a user sorts the grid by, say, Price, the querystring is updated to reflect the state information. If they then go to page 2 in the grid the the querystring is not updated and no entry is added to the browser's history. If at this point they hit the Back button, they are not returned to page 1, sorted by Price. Instead, they are returned to the grid prior to sorting it by Price. Likewise, if a user sorts the grid by Price, navigates to page 5, and adds a bookmark to their browser, if they later visit that bookmark the screen will load the grid sorted by Price, but will show page 1 of the data.

If you want to add a history point whenever the grid is sorted or paged, then you'll need to record the GridView's PageIndex property as an additional history state item in the Sorting event handler. You'll also need to create an event handler for the GridView's PageIndexChanging event, which fires whenever the user navigates to a new page. In this event handler you'll need to add a history point, recording the sort- and paging-related properties. Finally, you'll need to update the ScriptManager's Navigate event handler to set the GridView's PageIndex property to the value stored in the history state.

The download includes a complete, working example of a GridView that records history points whenever it is sorted or pages. See the BehaviorWithHistory2.aspx ASP.NET page.

Conclusion
Because AJAX-enabled web pages circumvent the standard browser request/form submission model, partial page postbacks do not register as new entries in the browser's history. This behavior neuters the usefulness of the browser's Back and Forward buttons, as well as bookmarks. The good news is that the ASP.NET AJAX framework, starting with ASP.NET 3.5 SP1, includes functionality to record history points. Recording a history point saves the state that describes the history in the querystring and adds an entry to the browser's history. This enables visitors to use their browsers' Back and Forward buttons to navigate through different states in an AJAX-enabled web page. It also allows for bookmarking of different states in an AJAX-enabled application.

Thursday, November 6, 2008

Customizing and Personalizing a Web Application with ASP.Net C#

ASP.Net 2.0 provides the ability to customize and personalize Web-based applications by using four techniques. These techniques include master pages, themes, user profiles, and Web parts. The following describes briefly about each of these four techniques:
 
  1. Master Page: A master page is a technique that customizes and personalizes a Web application. It defines the look, feel and the standard behavior that should be for each Web page in the application. it creates a consistent layout for all Web pages in the application. A master page consists of two parts: a master page and one or more content pages, which have been described in the following manner:
    • Master Page: A master page is an ASP.Net file with a .master extension. It contains the layout that includes text, HTML, and server controls. It is identified by the @Master directive that replaces the @Page directive. The @Master directive contains all HTML elements such as <html>, <head>, and <form> for a Web page. The @master directive syntax is as follows:

      <%@ Master Language="C# %>

      It is inherited from the MasterPage class and contains one or more ContentPlaceHolder controls. The following code snippet displays a master page:

      <%@ Master Language="C#" %> 
      <html> 
          <head runat="server"> 
               <title>Master Page</title> 
          </head> 
          <body> 
              <form id="MyWebForm1" runat="server"> 
              <asp:ContentPlaceHolder ID="FrontPage" runat="server" /> 
              <asp:ContentPlaceHolder ID="FootNote" runat="server" /> 
          </body> 
      </html>
    • Content Page: A content page is a standard Web page with an .aspx file extension. It defines the ContentPlaceHolder controls in a master page and is bound to it. It is used as the MasterPageFile attribute in the @Page directive. For example:

      <%@ Page Language="C#" MasterPageFile="MyMasterPage.master" %>

      The following code snippet displays a content page:

      <%@ Page Language="C#" MasterPageFile="MyMasterPage.master" Title="FirstContentPage" %> 
      <asp:Content ID="WebContent1" ContentPlaceHolderID="FrontPage" runat="server"> 
           FrontPage Content. </asp:Content> 
      <asp:Content ID="WebContent2" ContentPlaceHolderID="FootNote" runat="server"> 
           FootNote Content. </asp:Content>


      Note: When a user requests content pages, ASP.Net merges the contents of the content pages with the master page being created for the specified Web application.
  2. Themes: A theme is another technique of customizing and personalizing a Web application. It is a collection of ASP.Net property settings such as background color, font size and style, and foreground color. These property settings define the look and appearance of Web pages and controls. These looks are applied consistently across all the pages in a Web application. A theme consists of a set of elements that include skins, cascading style sheets (CSS), images, and other resources. It is defined in special directories in a user's Web site or on a Web server. The following describes briefly about each element of a theme:

    • Skins: A skin is an element of the theme and is stored in the Theme folder with a .skin file extension. It contains property settings for server controls such as Button, Label, TextBox and Calendar controls. These property settings are defined as part of the theme applied to an application. A skin file contains more than one control skins are resembles as control markups. Skins can be defined in a separate file for each server control. Therefore, all the skins for a particular theme are defined in one file. There are two types of control skins, default skins and named skins.

      When a theme is applied on a Web application a default skin is automatically applied to all controls of the same type. However, a default skin does not have a SkinID attribute. On the other hand, a named skin has to be explicitly applied to a control by setting the control's SkinID property. One advantage of the named skin is that a user can set different skins for different instances of the same control type.
    • Cascading Style Sheets (CSS): A cascading style sheet (CSS) is an element of a theme applied to a Web application. It stores all formatting style information for the application in a single file with .css extension in the Theme folder. It adds different styles such as fonts, colors, and spacing between text lines to contents on a Web page. By default, it automatically applies to all Web pages of an application. By attaching CSS enhances the presentation of all the Web pages.
    • Images and Resources: A theme also includes images and other resources such as script files or sound files as an element. For example, a specific image as part of a theme can represent the expand button and the collapse button in a Web page. A resource file can be either stored in a subfolder of the theme or outside the theme folder. If a resource file is stored outside the theme folder, a tilde '~' syntax is used to find the images automatically by the application.
  3. User Profiles: A user profile is another technique that can customize and personalize a Web application. ASP.Net provides a user's experience with a Web site by defining and using profile properties on the Web site. When profile properties are defined, it becomes easy to track user information, as the individual instances of the profile properties are automatically associated with each user. Each profile data value is stored in a data store from where the profile property for a user can be retrieved. By default, a profile information is stored in a SQL Server database by using the SqlProfileProvider class.

    Profiles can be used with either authenticated or non-authenticated users. Profiles are automatically enabled for authenticated users when a Web application required user authentication. For non-authenticated users, ASP.Net profiles have to be explicitly enabled in the Web.config file of the application. The <anonymousIdentification> element to the <system.web> section is added to the Web.config file and the enabled attributes are set to true. When the anonymous profiles are enabled, a unique identification is created for each user and the information is tracked with a cookie. A user profile can also work without any cookie by storing unique identifiers of each user in the URL of the Web page request. But, the drawback is that the profile is lost when a user's session is expired.
  4. Web Parts: A Web part is a technique of customizing and personalizing a Web application. It is an integrated set of controls to enable a user to modify the contents, appearances, and behavior of all Web pages directly from a Web browser. the modified Web pages and controls as well as the settings can be easily saved across Web browser sessions for the future user requests. This feature can be called as the personalization.

    A Web part can be used for a list of news articles related to a specific organization, a blog site, a calendar, and search box. It is also used for a list of Web site links, graphs showing stock market reports, local weather reports, and images taken from picture galleries. A standard or a custom control can be a Web part, which does not require writing code when using it as a Web part. A Web Part consists of three main sections. They include personalization, user interface (UI) structural components, and actual Web parts user interface controls.

Friday, October 31, 2008

Creating and Managing Database Objects using T-SQL Statements in SQL Server

Database: A database comprises information in structured and organized form that can easily be accessed and manipulated. According to the needs of a Web site, the database can be large or small in size. A database can be stored on a single PC or on a collection of high-powered database servers depending upon the size. When a database is stored on a collection of servers, it is known as a distributed database. It is a collection of relevant data that is interconnected and organized systematically so that its contents can easily be accessed, managed, and updated electronically.

The syntax for creating a database is as follows.

CREATE DATABASE database_name
Two files whose extensions are .mdf and .ldf are responsible for creating a database. The .mdf file is a data file, where .ldf is log file.
Database Objects: A database consists of various types of objects that are used to manage a database. The database objects are used to store data. Various properties of these database objects are used to sort, index and search data. The database objects are as follows:
  • Table
  • View
  • Index
  • Constraint
Table: A table is the basic unit of data storage in a SQL Server database. It is composed of columns and rows. A column is vertical space in a database table, which represents a particular domain of data. A row is a collection of data in columns corresponding to a single record. A table can be created in a database at any time, even while users are using the database. While creating a table, its size need not be specified. It is ultimately determined by the amount of space allocated to the database as a whole.
The syntax for creating a table is as follows:
CREATE TABLE table_name
(
     column1 datatype NULL | NOT NULL
     column2 datatype NULL | NOT NULL
     column3 datatype NULL | NOT NULL
     column4 datatype NULL | NOT NULL
     column5 datatype NULL | NOT NULL
)
Modifications can be made to an existing table by using the ALTER Table statement. In order to alter the column settings of a table, the following syntax is used:
ALTER TABLE table_name
{
    ALTER COLUMN column_name
    {
        datatype
        CONSTRAINT constraint_name
    }
}

In order to modify collation settings, the following syntax is used:

ALTER TABLE table_name
{
    COLLATE collation_name
}

The DELETE statement is used to delete records from a table or a view. The syntax for using the DELETE statement  is as follows:

DELETE * FROM table_name

The above syntax will delete all the records from a table.

DELETE FROM table_name WHERE column_name=value

The above syntax will delete records based on the search criteria.

In order to delete a table, the DROP TABLE statement is used. The syntax for deleting a table is as follows:

DROP TABLE table_name

View: A view can be thought of as a virtual table. Data accessible through a view is not stored in the database as a distinct object. A View is created by defining a SELECT statement. The result set of the SLECT statement from the virtual table. A user can use this virtual table by referencing the view name in SQL statements in the same way a table is referenced.

The syntax for creating a view is as follows:

CREATE VIEW view_name AS
SELECT columns
FROM table_name
[WITH CHECK OPTION
ENCRYPTION
SCHEMABINDING
VIEW_METADATA]

 

The WITH CHECK OPTION is used to force all data modifications to follow the criteria set within a SELECT statement given in the CREATE VIEW statement.

The WITH ENCRYPTION clause is used with a view or stored procedure definition. Using the WITH ENCRYPTION clause changes the view or stored procedure definition to an encrypted form. This prevents anyone from viewing the statement used to create the view or stored procedure.

The WITH SCHEMABINDING clause safeguards a view definition against any structural modification of the underlying table. If a view is created with the WITH SCHEMABINDING clause, the underlying tables cannot be deleted or altered in a way that affects the view definition.

The view specified with VIEW_METADATA can return the DBLIB, ODBC, and the OLEDB APIs. When metadata information about a view is to be viewed, it is created with the VIEW_METADATA option.

Modification can be made in an existing view by using the ALTER VIEW statement.

ALTER VIEW view_name AS
SELECT columns
FROM table_name
[WITH CHECK OPTION
ENCRYPTION
SCHEMABINDING
VIEW_METADATA]

In order to delete a view, the DROP VIEW statement is used. The syntax for the DROP VIEW statement is as follows:

DROP VIEW view_name

Index: An index is a data structure that improves the performance of queries issued against a table. An index can be created for one or more columns of a table. Once created, an index is automatically updated and used by the database. It is logically and physically independent of data. The table or other indexes remain unaffected when an index is created or deleted.

The syntax for creating an index is as follows:

CREATE [CLUSTERED] [NONCLUSTERED][UNIQUE] INDEX index_name object_name(column_name)

In order to create an AML index, the following syntax is used:

CREATE [PRIMARY] CML INDEX index_name ON object_name(xml_column_name)

Modifications can be made to an existing index by using the ALTER INDEX statement.

In order to specify rebuild option for an index or to enable an index, the following syntax is used:

ALTER INDEX index_name ON object_name
{
    REBUILD WITH rebuild options
}

 

In order to disable an index, the following syntax is used:

ALTER INDEX index_name
{
    DISABLE
}

 

In order to drop an index, the DROP INDEX statement is used.

DROP INDEX index_name

Constraints: Constraints are implemented on a table or a view to ensure that only valid data is inserted. Constraints prevent users from inserting invalid data or updating a column of a table or view with wrong data. These constraints are as follows:

  • PRIMARY KEY constraint
  • FOREIGN KEY constraint
  • UNIQUE constraint
  • CHECK constraint
  • DEFAULT constraint
  • NULL Values

PRIMARY KEY constraint: A PRIMARY KEY constraint is implemented on a column or on a group of columns of a table. These columns identify a row of a table uniquely. A table can contain only one primary key. the column on which a primary key constraint has implemented cannot contain NULL values.

FOREIGN KEY constraint: A FOREIGN KEY constraint is implemented on a column of a table to establish referential integrity between two table. A combination of a PRIMARI KEY and a FOREIGN KEY is used in the JOIN clause to retrieve data from two or more tables.

UNIQUE constraint: UNIQUE constraint ensures that no two rows in specified column or set of columns have duplicate values. Multiple UNIQUE constraints can be defined for a table. The UNIQUE constraint can be created by using the CREATE TABLE statement while creating a table. It can also be created by using the ALTER TABLE statement while modifying the table. The UNIQUE constraint allows NULL values unless NOT NULL constraint is specified for the same column.

CHECK constraint: A CHECK constraint enforces domain integrity by limiting the values that are accepted by a column. Multiple CHECK constraints can be applied to a column. A CHECK constraint can be created by using a logical expression to validate the value to be accepted by a column. It is also possible to use multiple-columns in a CHECK constraint. For example, a CHECK constraint can be used to confirm that value in the OrderDate column bust be less then the value in the InvoiceDate column. However, a CHECK constraint cannot be created to enforce a rule that requires column value from another row in the table or column value from another table.

DEFAULT constraint: The DEFAULT constraint is a constraint in which a default value is given to the column if the value for that column in unknown. If no value is provided for that column, the default value is automatically inserted. If a default value is not provided, then NULL is inserted. If a column does not allow NULL value and default value is also not assigned for that column, an error is sent by the database engine.

In order to implement a constraint on a column while creating a table, the following syntax is used:

CREATE TABLE table_name
(
    CONSTRAINT constraint_name(column_name)
)

 

In order to implement a constraint on a column of an existing table, the following syntax is used:

ALTER TABLE table_name
(
    CONSTRAINT constraint_name(column_name)
)