Using and Using Alias

6. Using & Using Alias

 

The using directive has two uses:

  • To allow the use of types in a namespace so that you do not have to qualify the use of a type in that namespace:

Using System.Text; // Using Directives

All of the members defined within name of a Namespace are brought into view and can be used without qualification.

  • To create an alias for a namespace or a type. This is called a using alias directive.

Using Project = PC.MyCompany.Project; //Using Alias

The using keyword is also used to create using statements, which help ensure that IDisposable objects such as files and fonts are handled correctly.

As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called. Within the using block, the object is read-only and cannot be modified or reassigned.

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler.

Using has a second form that is called the using statement.

It has these general forms:

Using (obj) {//Using Statement

    // use obj

    }   

    Using (type obj = initializer) {

    // use obj

    }

 

Obj is an object that is being used inside the using block. In the first form, the object is declared outside the using statement. In the second form, the object is declared within the using statement. When the block concludes, the Dispose () method (defined by the System.IDisposable interface) will be called on obj. The using statement applies only to objects that implement the System.IDisposable interface.

Example

Font font2 = new Font (“Arial”, 10.0f);

Using (font2) // not recommended

{

    // use font2

}

// font2 is still in scope

// but the method call throws an exception

Float f = font2.GetHeight ();

Notes:

*    The scope of a using directive is limited to the file in which it appears.

*    Create a using alias to make it easier to qualify an identifier to a namespace or type.

*    Create a using directive to use the types in a namespace without having to specify the namespace. A using directive does not give you access to any namespaces that are nested in the namespace you specify.

*    Namespaces come in two categories: user-defined and system-defined. User-defined namespaces are namespaces defined in your code. For a list of the system-defined namespaces, see .NET Framework Class Library Reference.

C# Namespaces

C# .Net Namespaces

Namespace:

          Namespace is logically group’s classes. Namespace can span in multiple assemblies.

Namespaces and its usage:

     System is the basic namespace for .Net Framework. It is having the following Namespaces

  1. Collections: The .NET Framework provides specialized classes for data storage and retrieval.   These classes provide support for stacks, queues, lists, and hash tables.
  2. ComponentModel: The ComponentModel namespace provides classes that are used to implement the run-time and design-time behavior of components and controls. This namespace includes the base classes and interfaces for implementing attributes and type converters, binding to data sources, and licensing components.
  3. Configuration: The Configuration namespace contains the types that provide the programming model for handling configuration data.
  4. Data: The Data namespace provides access to classes that represent the ADO.NET architecture. ADO.NET lets you build components that efficiently manage data from multiple data sources.
  5. Diagnostics: It Contains classes that enable you to debug and follow the execution of your applications.
  6. DirectoryServices: It Provides access to Active Directory services. Drawing Contains classes that enable you to use basic, graphical display interface (GDI) capabilities.
  7. EnterpriseServices: This Namespace Contains objects that enable you to control how components behave on a server.
  8. Globalization: This Namespace Contains classes that define culture-related information.
  9. IO: This Namespace Contains classes that enable you to read and write to data streams and files.
  10. Media: The Media namespace contains classes for playing sound files and accessing sounds provided by the system.
  11. Net: This Namespace Provides classes to work with network protocols.
  12. Reflection: This Namespace Contains classes that enable you to view information about other types in the .NET Framework.
  13. Resources: This Namespace Contains classes that enable you to manage culture specific resources.
  14. Runtime: This Namespace contains advanced types that support diverse namespaces.
  15. Security: The Security namespace provides the underlying structure of the common language runtime security system, including base classes for permissions.
  16. Text: The Text namespace contains classes representing ASCII, Unicode, UTF-7, and UTF-8 character encodings; abstract base classes for converting blocks of characters to and from blocks of bytes; and a helper class that manipulates and formats String objects without creating intermediate instances of String.
  17. Threading: This Namespace Contains classes that enable multi-threaded programming.
  18. Timers: This Namespace Contains classes to raise events on specified time intervals.
  19. XML: This Namespace contains the classes to work with XML file.

Just in time compiler

3. Just In Time Compiler (JIT)

 

               During Run Time, the CLR invokes the JIT compiler to convert the MSIL into Native code (i.e. Executable Binary Code). When the Function is called, the IL of the function’s body is converted to Native code Just In Time. So the part of code that is not used by the particular run is never converted to native code. If some IL is converted to native code, the next time it’s needed, the CLR reuses the same copy without re-compiling.

Types of JIT:

·   PreJIT

·   EconoJIT

·   Standard JIT

(1) PRE JIT: It Compiles complete source code to native code in a single Compilation.
(2) ECONO JIT: It compiles only those methods that are called at Runtime.
(3) NORMALE JIT: It compiles only those methods that are called at Runtime and are stored in cache.

Sending e-mails with C# .NET 2.0

 

First step: Setting-up your SMTP (Simple Mail Transfer Protocol)

 

you must have installed IIS (internet information services), so you can setup your SMTP Virtual Server from there.

 

Control Panel->Administrative Tools->Internet Information Services , then you select the SMTP Virtual Server Properties and grant permission to local host (127.0.0.1) (Access->Relay).

 

Second Step: Writing the basic C# Code

 

Example: Windows Application

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Net;

using System.Net.Mail;

using System.Windows.Forms;

 

namespace Threading

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }       

    protected void Button1_Click(object sender, EventArgs e)

    {

        MailMessage objMM = new MailMessage();

        objMM.From = new MailAddress(textBox1.Text, “sender name”);

        objMM.To.Add(new MailAddress(textBox2.Text, “reciever name”));

        objMM.Subject = textBox3.Text;

        objMM.Body = richTextBox1.Text;

        SmtpClient objSC = new SmtpClient(“localhost”, 25);

        objSC.DeliveryMethod = SmtpDeliveryMethod.Network;

        objSC.Host = “smtp.gmail.com”; // for example gmail smtp server

        objSC.EnableSsl = true;

        objSC.Credentials = new System.Net.NetworkCredential(“mailid”, “password”);

        try

        {

            objSC.Send(objMM);

        }

        catch (Exception ex)

        {

            MessageBox.Show(ex.ToString());

        }

 

        MessageBox.Show(“Mail send successfully”);

    }       

    }

}

 

 

Be sure that the recipient checks his spam messages because many of the e-mails using this algorithm go there. Also don’t forget to include System.Net.Mail namespace (using System.Net.Mail ;).

 

Extra:

 

In .NET 1.1 you can use SmtpMail class (.NET1.1 does not support SmtpClient class) and be sure to include System.WEB.Mail.

Smtp Classes in .Net

MailMessage Class:

     The MailMessage class can be considered the foundation class of the System.Net.Mail namespace. It deals with creating and managing the email message. All other classes will somehow interact with this class. The MailMessage class exposes such properties as the

Property

 Description

Attachments

 Gets the attachment collection used to store data attached to this e-mail message.

Bcc

 Gets the address collection that contains the blind carbon copy (BCC) recipients for this e-mail message.

Body

 Gets or sets the message body.

CC

 Gets the address collection that contains the carbon copy (CC) recipients for this e-mail message.

From

 Gets or sets the from address for this e-mail message.

Subject

 Gets or sets the subject line for this e-mail message.

To

 Gets the address collection that contains the recipients of this e-mail message.

Below you will find an example of using the MailMessage class

Example:

//create the mail message
MailMessage mail = new MailMessage ();
//set the addresses
mail.From = new MailAddress ("me@mycompany.com");
mail.To.Add ("you@yourcompany.com");
//set the content
mail.Subject = "This is an email";
mail.Body = "this is the body content of the email”;

MailAddress Class:

            The MailAddress class is used for creating email addresses. This class is used for setting the MailMessage.From, MailMessage.To, MailMessage.CC and MailMessage.BCC properties. Of these properties the .From class is actually a MailAddress, while the To, CC and BCC properties are actually collections of MailAddresses. The two most common properties of the MailAddress class are the DisplayName and the Address properties. They are described below.

Name

Description

Address

Gets the e-mail address specified when this instance was created.

DisplayName

Gets the display name composed from the display name and address information specified when this instance was created.

Example:

//set the addresses

//to specify a friendly 'from' name, we use a different ctor
mail.From = new MailAddress ("me@mycompany.com", "Steve James");

Attachment Class:

            The Attachment class is used for creating and managing individual attachments of the MailMessage object. Attachments can be created from streams or file paths. The stream or file path must be set in the ctor of the Attachment.

Below is an example demonstrating the Attachment class

[C#]

 Static void AttachmentFromFile ()

{

//create the mail message

MailMessage mail = new MailMessage ();

//set the addresses

mail.From = new MailAddress (“me@mycompany.com”);

mail.To.Add (“you@yourcompany.com”);

//set the content

mail.Subject = “This is an email”;

mail.Body = “this content is in the body”;

 

//add an attachment from the file system

mail.Attachments.Add (new Attachment (“c:\\temp\\example.txt”));

//to add additional attachments, simply call .Add (…) again

mail.Attachments.Add (new Attachment (“c:\\temp\\example2.txt”));

mail.Attachments.Add (new Attachment (“c:\\temp\\example3.txt”));

//send the message

SmtpClient smtp = new SmtpClient(“127.0.0.1”);

smtp.Send(mail);

 

}

SmtpClient Class:

The SmtpClient class is responsible for sending or transporting the email. The SmtpClient can transport the email content over the network, or it can actually write them to the filesystem in the MS IIS Smtp Service Pickup Directory format, which resembles a RFC821 formatted message. Emails can be sent either synchronously or asynchronously. The SmtpClient also supports sending email via SSL for security purposes. The following list of properties are the most common used on the SmtpClient class.

Name

Description

Credentials

Gets or sets the credentials used to authenticate the sender.

DeliveryMethod

Specifies how outgoing email messages will be handled.

EnableSsl

Specify whether the SmtpClient uses Secure Sockets Layer (SSL) to encrypt the connection.

Host

Gets or sets the name or IP address of the host used for SMTP transactions.

Port

Gets or sets the port used for SMTP transactions.

 

AlternateView class:

The AlternateView class is used for providing alternate bodies and creating Multi-Part mime emails. If you want to create an email that will be rendered properly in both Html capable and Plain Text only mail clients, then you will create alternate views of the message. There are a few main properties and methods you will use with the AlternateView class. They are:

Name

Description

BaseUri

Gets or sets the Base URI to use for resolving relative URIs in the AlternateView

LinkedResources

Gets the set of embedded resources referred to by this attachment.

CreateAlternateViewFromString (static method)

Overloaded. Creates an AlternateView to view an email message using the specified format..

 

LinkedResource class:

The LinkedResource class is the last, and least used main class. It is mainly used for creating embedded images. To create an embedded image you will need to first create a Html formatted AlternateView. Within that alternate view you create an tag, that points to the ContentId (CID) of the LinkedResource. You then create a LinkedResource object and add it to the AlternateView’s LinkedResources collection.

SMTP Basics

Simple Mail Transfer Protocol:

Short for Simple Mail Transfer Protocol, a protocol for sending e-mail messages between servers. Most e-mail systems that send mail over the Internet use SMTP to send messages from one server to another; the messages can then be retrieved with an e-mail client using either POP or IMAP. In addition, SMTP is generally used to send messages from a mail client to a mail server.

Reasons why you might want to send email from your Web application:

*    create a “give us your feedback” Web page

*    implement a “forgotten password” script that sends a password to the user’s email account

*    send an automatic welcome email to your new newsletter subscriber

*    send automatic email update notifications

*    send automatic email notifications whenever an error occurs in your Web application

SMTP Server:

A relay is a service that allows you to send email. It is usually a full fledged mail server, or can be a specialized SMTP Service. Some examples of a mail server include Microsoft Exchange, IMail by IPSwitch, or Mail Enable by Mail Enable. An example of a SMTP service is the SMTP Service installed that can be installed with IIS. SNM sends email to a relay server, and the relay server is responsible for delivering the email to the final destination. When sending email to a relay server, you must have protocol permissions to use that server. Because of SPAM problems, relay servers are normally locked down, either by IPAddress or by some type of username/password authentication. Relaying errors are the most common problems when programmatically sending emails.

 

System. Net. Mail Namespace:

System.Net.Mail is the namespace used to send email if you are using the 2.0 (or higher) .NET Framework.

Unlike System.Web.Mail, which was introduced in the 1.0 Framework, it is not built upon the CDO/CDOSYS libraries. Although some functionality has been removed, the new System.Net.Mail namespace is much more versatile than the older CDO dependant System.Web.Mail.

In this Mail Namespace, the following classes are available

è    MailMessage

è    MailAddress

è    Attachment

è    SmtpClient

è    AlternateView

è    Linked Resource

 

Views in Sql Server

Definition:

A view is a virtual table created by a stored SQL statement that can span multiple tables. Views can be used as a method of security within your database, and they provide a simpler front end to a user querying the data.

A view is a virtual table that consists of columns from one or more tables. a view is an object that derives its data from one or more tables. These tables are referred to as base or underlying tables. Once you have defined a view, you can reference it like any other table in a database.

A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying tables. A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

Views ensure the security of data by restricting access to the following data:

  • Specific rows of the tables.
  • Specific columns of the tables.
  • Specific rows and columns of the tables.
  • Rows fetched by using joins.
  • Statistical summary of data in a given tables.
  • Subsets of another view or a subset of views and tables.

Some common examples of views are:

  • A subset of rows or columns of a base table.
  • A union of two or more tables.
  • A join of two or more tables.
  • A statistical summary of base tables.
  • A subset of another view, or some combination of views and base table.

i.Creating Views: 

A view can be created by using the CREATE VIEW statement.

Syntax

CREATE VIEW view_name
[(column_name [, column_name]….)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

Where:

View_name specifies the name of the view and must follow the rules for identifiers.

Column_name specifies the name of the column to be used in view. If the column_name option is not specified, then the view is created with the same columns as specified in the select_statement.

Example

CREATE VIEW vwCustomer
AS
SELECT
CustomerId, Company Name, Phone
FROM Customers

 

ii. Getting Information on a View:

SQL Server stores information on the view in the following system tables:

· SYSOBJECTS — stores the name of the view.

· SYSCOLUMNS — stores the names of the columns defined in the view.

· SYSDEPENDS — stores information on the view dependencies.

· SYSCOMMENTS — stores the text of the view definition.

There are also certain system-stored procedures that help retrieve information on views. The sp_help system-stored procedure displays view-related information. It displays the view definition, provided the name of the view is given as its parameter.

Example

Sp_helptext vwCustomer

Displays the definition of the vwCustomer view

Note

If a view is created with the WITH ENCRYPTION option, it cannot view the sp_helptext system-stored procedure.

iii. Altering Views:

You can modify a view without dropping it. This ensures that the permission on the view is also not lost. You can modify a view without affecting its dependent objects, such as triggers and stored procedures. You modify a view using the ALTER VIEW statement.

Example

ALTER VIEW vwCustomer
AS
SELECT CustomerId, Company Name, Phone, Fax
FROM Customers

iv. Dropping Views:

You can drop a view from a database by using the DROP VIEW statement. When a view is dropped, it has no effect on the underlying tables. Dropping a view removes its definition and all the permissions assigned to it. Furthermore, if you query any views that reference a dropped view, you receive an error message. However, dropping a table that references a view does not drop the view automatically. You must drop it explicitly.

Syntax

DROP VIEW view_name

Where:

View_name is the name of the view to be dropped.

You can drop multiple views with a single DROP VIEW statement. The names of the views that need to be dropped are separated by commas in the DROP VIEW statement.

v.. Renaming Views

You can rename a view without having to drop it. This ensures that the permissions on the view are not lost.

· A view can be renamed by using the sp_rename system stored procedure.

Syntax

Sp_rename old_viewname, new_viewname

Where:

Old_viewname is the view that needs to be renamed.

New_viewname is the new name of the view.

Example

Sp_rename vwCutomers vwCustomerDetails

Renames vwCutomers to vwCustomerDetails

vi. Manipulating Data Using Views:

You can modify data by using a view in only one of its base tables even though a view may be derived from multiple underlying tables. For example, a view vwNew that is derived from two tables, table and table2, can be used to modify either table or table2 in a single statement. A single data modification statement that affected both the underlying tables is not permitted.

vii. When to Use a View:

You need to have a goal in mind when creating a view. There are a number of scenarios where you will want to look for a view as a solution.

  • To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
  • To control access to rows and columns of data- a view can be a useful tool to provide both column and row level security for a database.
  • To aggregate data for performance- if you need to roll up or aggregate data from a set of tables.

viii. Modifying Data through a View:

You can modify data through a view in these ways:

  • Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.
  • Use updatable partitioned views that modify one or more member tables.

 

Advantages of views:

1. View the data without storing the data into the object.

2. Restricts the view of a table i.e. can hide some of columns in the tables.

3. Join two or more tables and show it as one object to user.

4.  restricts the access of a table so that nobody can insert the rows into the table.

Disadvantages:

1. Can not use DML operations on this.

2. When table is dropped view becomes inactive… it depends on the table objects.

3. It is an object, so it occupies space.


JOIN in Sql Server

Definition:

The SQL JOIN statement is used to combine the data contained in two relational database tables based upon a common attribute.

Types of Join:

  1. Inner Join
  2. Outer Join
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join
  3. Cross Join

1. Inner Join: Inner Join is a default type join of SQL Server. It uses logical operators such as =, <, > to match the records in two tables. Inner Join includes equi join and natural joins.

Self Join: Self join joins a single sql database table to itself.

Equi Join: Equi Join returns all the columns from both tables and filters the records satisfying the matching condition specified in Join “ON” statement of sql inner join query.

2. Outer Join: Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause.

o Left Outer Join: Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. If in the left table any row has no matching record in the right side table then that row returns null column values for that particular tuple.

o Right Outer Join: Right Outer Join is exactly the reverse method of Left Outer Join. It returns all the rows from right table and returns null values for the rows having no match in the left joined table.

o Full Outer Join: Full outer join returns all the rows from both left and right joined tables. If there is any match missing from the left table then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table.

2. Cross Join: Cross join works as a Cartesian product of rows for both left and right table. It combined each row of left table with all the rows of right table.

Triggers in Sql Server

Introduction:

The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed. The statements specified in the SQL trigger are executed each time an SQL insert, update, or delete operation is performed. An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed.

Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system, when the SQL table, that the trigger is defined on, is modified.

Definition:

A trigger is a Database object just like a stored procedure or we can say it is a special kind of Stored procedure which fires after (/before) a specified language event executes. More specifically, it is for the object which is attached to a Table or View or Database schemas for tracking the operations on them. The main difference between a trigger and a stored procedure is that the former is attached to a table or view and is fired only when an INSERT, UPDATE, and/or DELETE occurs, while a stored procedure executes at any time when it is called.

Example:

CREATE TRIGGER myTrigger ON Employee FOR INSERT, UPDATE AS

IF EXISTS (SELECT 'True' FROM Inserted i JOIN employee e

ON i.ID = e.ID)
BEGIN
RAISERROR ('Transaction Failed.', 16, 1)

ROLLBACK TRAN
END
GO

Types of Triggers

There are some added types in SQL Server 2005 for triggering actions:

1.    DML Triggers

AFTER Triggers

INSTEAD OF Triggers

2.    DDL Triggers

3.    CLR Triggers

DML Triggers:

These triggers are fired when a Data Manipulation Language (DML) event takes place. These are attached to a Table or View and are fired only when an INSERT, UPDATE and/or DELETE event occurs.

    1. INSTEAD OF Triggers:

INSTEAD-OF triggers are objects that will execute instead of data manipulation statements in the database engine. For example, attaching an INSTEAD-OF INSERT trigger to a table will tell the database engine to execute that trigger instead of executing the statement that would insert values into that table.

Why INSTEAD OF Trigger?

INSTEAD-OF triggers are very powerful objects in SQL Server. They allow the developer to divert the database engine to do something different than what the user is trying to do. An example of this would be to add an INSTEAD-OF trigger to any table in your database that rolls back transactions on tables that you do not want modified. You must be careful when using this method because the INSTEAD-OF trigger will need to be disabled before any specified modifications can occur to this table.

Perhaps a more functional reason to use an INSTEAD-OF trigger would be to add the trigger to a view. Adding an INSTEAD-OF trigger to a view essentially allows you to create updateable views. Updateable views allow you to totally abstract your database schema so you can potentially design a system in such a way that your database developers do not have to worry about the OLTP database schema and instead rely upon a standard set of views for data modifications.

Example:

CREATE VIEW order_info AS

SELECT c.customer_id, c.cust_last_name, c.cust_first_name,

o.order_id, o.order_date, o.order_status

FROM customers c, orders o

WHERE c.customer_id = o.customer_id;

Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view. The PL/SQL trigger implementation is shown in italics.

CREATE OR REPLACE TRIGGER order_info_insert

INSTEAD OF INSERT ON order_info

DECLARE

duplicate_info EXCEPTION;

PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

BEGIN

INSERT INTO customers

(customer_id, cust_last_name, cust_first_name)

VALUES (

: new.customer_id,

: new.cust_last_name,

: new.cust_first_name);

INSERT INTO orders (order_id, order_date, customer_id)

VALUES (

: new.order_id,

: new.order_date,

: new.customer_id);

EXCEPTION

WHEN duplicate_info THEN

RAISE_APPLICATION_ERROR (

num=> -20107,

msg=> ‘Duplicate customer or order ID’);

END order_info_insert;

/

You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):

INSERT INTO order_info VALUES

(999, ‘Smith’, ‘John’, 2500, ’13-MAR-2001′, 0);

    1. AFTER Triggers:

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. This is the only option available in earlier versions on Microsoft SQL Server and also AFTER triggers can be specified only on tables only. Here is a sample trigger creation statement on the Users table.

Example:

                       ------ Creating a DML trigger in T-SQL -------
SET NOCOUNT ON
CREATE TABLE UserTable (User_ID int IDENTITY, User_Name varchar (30), Type varchar (10))
go
CREATE TRIGGER tr_User_INSERT
ON UserTable
FOR INSERT
AS
PRINT GETDATE ()
Go
                                                             INSERT UserTable (User_Name, Type) VALUES ('James''ADMIN') 
------ Result ---------------
Apr 30 2007 7:04AM

DDL Triggers:

DDL triggers are new to SQL Server 2005. This type of triggers, like regular triggers, fire stored procedures in response to an event. They fire in response to a variety of Data Definition Language (DDL) events. These events are specified by the T-SQL statements that are start with the keywords CREATE, ALTER, and DROP. Certain stored procedures that perform DDL-like operations can also fire this. These are used for administrative tasks like auditing and regulating database operations.

CLR Triggers:

A CLR triggers can be any of the above, e.g. can be a DDL or DML one or can also be an AFTER or INSTEAD OF trigger. Here we need to execute one or more methods written in managed codes that are members of an assembly created in the ..Net framework. Again, that assembly must be deployed in SQL Server 2005 using CREATE assembly statement.

The Microsoft.SqlServer.Server Namespace contains the required classes and enumerations for this objective.

Steps for creating CLR Triggers:

The following are required steps for creating a CLR-Trigger of DML (After trigger) type for Insert action. This database Object is executed as the result of a user action against a table i.e. for an INSERT statement.

* Creating a .NET class of triggering action

* Making assembly(.DLL) from that Class

* Enabling CLR environment in that database.

* Registering the assembly in SQL Server

* Creating CLR Trigger using that assembly

Indexes in Sql Server

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.

Non-clustered Indexes

Unlike a clustered indexed, the leaf nodes of a non-clustered 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.

Non-clustered indexes cannot be sorted like clustered indexes; however, you can create more than one non-clustered index per table or view. SQL Server 2005 supports up to 249 non-clustered 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 non-clustered 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 non-clustered, 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 non-clustered 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, non-clustered index on the primary key.
    • Unique: When you define a unique constraint, SQL Server automatically creates a unique, non-clustered 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.

Microsoft SQL Server supports two types of indexes:

  • Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index. If a PRIMARY KEY constraint is created for a database table and no clustered index currently exists for that table, SQL Server automatically creates a clustered index on the primary key.
  • Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself. SQL Server 2000 supports a maximum of 249 non-clustered indexes per table. However, it’s important to keep in mind that non-clustered indexes slow down the data modification and insertion process, so indexes should be kept to a minimum

One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.

You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains.

Advantages:

Indexes allow you to speed query performance on commonly used columns and improve the overall processing speed of your database.

Disadvantages:

Although indexes can reduce access time significantly, they can also have adverse effects on performance. Before you create indexes, consider the effects of multiple indexes on disk space and processing time:

· Each index requires storage or disk space. The exact amount depends on the size of the table and the size and number of columns in the index.

· Each INSERT or DELETE operation performed on a table requires additional updating of each index on that table. This is also true for each UPDATE operation that changes the value of an index key.

· The LOAD utility rebuilds or appends to any existing indexes.

The index free space MODIFIED BY parameter can be specified on the LOAD command to override the index PCTFREE used when the index was created.

· Each index potentially adds an alternative access path for a query for the optimizer to consider, which increases the compilation time.

Choose indexes carefully to address the needs of the application program.

Difference B/W Clustered and Non-Clustered Index:

There are clustered and non-clustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Optimization tips

  • Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
  • Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.
  • Try to create indexes on columns that have integer values rather than character values.
  • If you create a composite (multi-column) index, the order of the columns in the key is very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
  • If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.
  • Create surrogate integer primary key (identity for example) if your table will not have many insert operations.
  • Clustered indexes are more preferable than non-clustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.
  • If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
  • You can use the SQL Server Profiler Create Trace Wizard with “Identify Scans of Large Tables” trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.
  • You can use the sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.

sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”