How to create Indexes on Views: SQL Server

Similar to the tables, you can create indexes on views. By default, the views created on a table are no indexed. However, you can index the views when the volume of data in the underlying tables is large and not frequently updated. Indexing a view helps in improving the query performance.

Another benefit of creating an indexed view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. If the query contains references to columns that are also present in the indexed view, and the query optimizer estimates that using the indexed view offer the lowest cost access mechanism, the query optimizer selects the indexed view.

When indexing a view, you need to first create a unique clustered index on a view. After you have defined a unique clustered index on a view, you can create additional non-clustered indexes. When a view is indexed, the rows of the view are stored in the database in the same format as a table.

Guidelines for Creating an Indexed View

• You should consider the following guidelines while creating an indexed view:
• A unique clustered index must be the first index to be created on a view.
• The view must not reference any other views, it can reference only base tables.
• All base tables referenced by the view must be in the same database and have the same owner as the view.
• The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

Creating an Indexed View by Using the CREATE INDEX Statement

You can create indexes on views by using the CREATE INDEX statement. For example, you can use the following statements for creating a unique clustered index on the vwEmployeeDepDate view:

CREATE UNIQUE CLUSTERED INDEX idx_vwEmployeeDepData
ON HumanResources.vwEmployeeDepData (EmployeeID, DepartmentID)
The vwEmployeeDepData view was not bound to the schema at the time of creation. Therefore, before executing the preceding statement, you need to bind the vwEmployeDepData view to the schema using the following statement:

ALTER VIEW HumanResources.vwEmployeeDepData WITH SCHEMABINDING
AS
SELECT e.EmployeeID, MaritalStatus, DepartmentID
FROM HumanResources.Employee e JOIN
HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.EmployeeID

The preceding statement alters the existing view, vwEmployeeDepData, and binds it with the schema of the underlying tables. You can then create a unique clustered index on the view.

How to apply restrictions at time of Modifying Data using Views

Views do not maintain a separate copy of the data, but only display the data present in the base tables. Therefore, you can modify the base tables by modifying the data in the view, however, the following restrictions exist while inserting, updating, or deleting data through views:

• You cannot modify data in a view if the modification affects more than one underlying table. However, you can modify data in a view if the modification affects only one table at a time.
• You cannot change a column that is the result of a calculation, such as a computed column or an aggregate function.

For example, a view displaying the employee id, manger id, and rate of the employees has been defined using the following statement:

CREATE VIEW vwSal AS
SELECT i.EmployeeID, i.MangerID, j.Rate FROM HumanResources.Employee AS i
JOIN HumanResources.EmployeePayHistory AS j ON
i.EmployeeID = j.EmployeeID

After creating the view, if you try executing the following update statement, it generates an error. This is because the data is being modified in two tables through a single update statement.

UPDATE vwSal
SET ManagerID = 2, Rate = 12.45
WHERE EmployeeID = 1

Therefore, instead of a single UPDATE statement, you need to execute two UPDATE statement for each table.

The following statement would update the EmployeeID attribute in the Employee base table:

UPDATE vwSal
SET ManagerID = 2
WHERE EmployeeID = 1

The following statement would update the Rate attribute in the EmployeePayHistory table:

UPDATE vwSal
SET Rate = 12.45
WHERE EmployeeID = 1

Therefore, to modify the data in two or more underlying tables through a view, you need to execute separate UPDATE statements for each table.

Creating View and Guidelines in SQL Server

Database administrator might want to restrict access of data to different users. They might want some users to be able to access all the columns of a table whereas other users to be able to access only selected columns. The SQL Server allows you to create views to restrict user access to the data. Views also help in simplifying query execution when the query involves retrieving data from multiple tables by applying joins.

A view is a virtual table, which provides access to a subset of columns from one or more tables. It is a query stored as an object in the database, which does not have its own data. A view can derive its data from one or more tables, called the base tables or underlying tables. Depending on the volume of data, you can create a view with or without an index. As a database developer, it is important for you to learn to create and manage views.

Creating Views

A view is a database object that is used to view data from the tables in the database. A view has a structure similar to a table. It does not contain any data, but derives its data from the underlying tables.

Views ensure security of data by restricting access to:

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

Apart from restricting access, views can also be used to create and save queries based on multiple tables. To view data from multiple tables, you can create a query that includes various joins. If you need to frequently execute this query, you can create a view that executes this query. You can access data from this view every time you need to execute the query.

You can create a view by using the CREATE VIEW statement. The syntax of the CREATE VIEW statement is:

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

Where,

• View_name specifies the name of the view.
• Column_name specifies the name of the column(s) to be used in a view.
• WITH ENCRYPTION specifies that the text of the view will be encrypted in the syscomments view.
• SCHEMABINDING binds the view to the schema of the underlying table or tables.
• AS specifies the action to be performed by the view.
• Select_statement specifies the SELECT statement that defines a view. The view may use the data contained in other views and tables.
• WITH CHECK OPTION forces the data modification statements to meet the criteria given in the SELECT statement defining the view. The data is visible through the view after the modifications have been made permanent.

Guidelines for creating views

While creating views, you should consider the following guidelines:

• The name of a view must follow the rules for identifiers and must not be the same as that of the table on which it is based.
• A view can be created only if there is a SELECT permission on its base table.
• A view cannot derive its data from temporary tables.
• In a view, ORDER BY cannot be used in the SELECT statement.

For example, to provide access only to the employee ID, marital status, and department ID for all the employees you can create the following view:

CREATE VIEW HumanResources.vwEmployeeDepData
AS
SELECT e.EmployeeID, MaritalStatus, DepartmentID
FROM HumanResources.Employee e JOIN
HumanResources.EmployeeDepartmentHistory d
ON e.EmployeeID = d.EmployeeID

The preceding code crates the vwEmployeeDepData view containing selected columns from the Employee and EmployeeDepartmentHistory tables.

Returns Radio Button Input element using Html.RadioButton() handler: MVC

Html.RadioButton() handler, used to return radio button input element to be input true or false value by user. Checked radio button will return true and un-checked will return false, otherwise it returns null to store/use the value.

Html.RadioButton() handler method is used to present mutually exclusive option i.e. true of false. Using this radiobutton method makes it easy to bind to view data or model is so easy in compare to using simple input radio element. This handler provides mostly same features as Html.CheckBox() handler discussed earlier.

Parameters

• htmlHelper: specify html helper instance that this method extends.
• name: specify name of input element used to access the value in controller.
• value: specify value of radio button element. If none is assigned then this attribute is used to access the value.
• isChecked: to be set true of false for radio button. True to select the element, OW false.
• htmlAttributes: specify an object that contains html attributes to set for the element.
   e.g Html.RadioButton(“gender”)

Html.RadioButtonFor(…)

Having the same functionality as Html.RadioButton() but it returns radio button element for each property passed via the model from controller’s action. This handler have two types of type parameters i.e. TModel (specify type of model) and TProperty (specify type of value).

Parameters

• htmlHelper: specify html helper instance that this method extends.
• expression: used to identifies object containing the property to render.
• value: specify value of radio button element. If none is assigned then this attribute is used to access the value.
• htmlAttributes: specify an object that contains html attributes to set for the element.

e.g Html.RadioButtonFor(model=>model.Gender)

Render radio button input for the property gender passed via controller. Submitting form will assign the value of this radio button to model’s gender property to be accessed in the controller’s action.

Returns Password input element using Html.Password() Handler: MVC

Html.Password() handler, used to return password input element to be input password value by user. This article describes about to using password html helper that will make text unreadable by other users in MVC application.

Like all other input elements, using Html.Password() html handler users are not able to read input value. This handler is mostly used in login process of any ASP.Net MVC application because of some special features of this helper.

This helper commonly have following features:

• Text input in the control is un-readable.
• Does not allow user to copy the text input in the control.
• Does not repopulate its value from ModelState object.

Parameters

• htmlHelper: html helper instance that this method extends.
• name: used to specify the name of form field that may be used to look up the value.
• value: specify the value of this element.
• htmlAttributes: specify html attributes to be set for the element like placeholder, id, any class or other values.

Overall this will generate an input element having the type password on the page:
<input type=”password” runat=”server” id=”password” />

Html.PasswordFor(…)

Provide same functionality but used to render password input element for each property passed via model. Here the model is the class for which the view is to be created. This handler have two type parameter i.e. TModel (specify type of model) and TProperty (specify type of value).

Parameters

• htmlHelper: html helper instance that this method extends.
• expression: used to identifies object that contains property to render.

Html.PasswordFor(model=>model.Password)

this line will render an input element of type password having id "Password" passed by the model. After submitting form, the value of this element will assigned to the password field of the model and can be easily accessed for further use.

How to find no of days between two different date objects in asp.net

We have already discussed about that article , but today we add two months in current date time object , after that i will find how many days remaining from the current date and time. Lets take an simple example
<form id="form1"
     runat="server">
    <asp:Button ID="Button1"
    runat="server"
    onclick="Button1_Click"
    Text="Click"
    Width="71px" />
    <div>  
    <asp:Label ID="Label1"
    runat="server"
    BackColor="Yellow"
    Text="Label"></asp:Label>  
    </div>
    </form>
Code Behind
 protected void Button1_Click(object sender, EventArgs e)
        {

            DateTime today = DateTime.Today;
            Label1.Text = "today : " + today.ToLongDateString();
            DateTime twoMonthLater = today.AddMonths(2);
            TimeSpan TS = twoMonthLater - today;
            int days = TS.Days;
            Label1.Text += "<br ><br />after two month: ";
            Label1.Text += twoMonthLater.ToLongDateString();
            Label1.Text += "<br ><br />This is difference between to datetime object in days:";
            Label1.Text += days;
        }

  Code Generate the following output

C# - Arrays

Introduction

If we create a variable of type int also assign 10 as value in it. If we want to store more than one value in it then its not possible. That why array comes to the picture, If you want to store similar type and more then one value into it then should take array.

Array Definition 

"An array is a collection of similar data type or you can say a single variable hold multiple other variables, those variable known as label of array. Lets take an simple example, suppose you have two things, which are ball and bat and you want to store it, use a box. Put the both things in separate boxes, also with label. Here 'array' is a box and label is 'variable name'.

Basically array consists of two things first one is index and second one is value, If you want to access specific value in an array, use index. When we insert items into an array, default index started from 0. You can insert items randomly at any index position. Lets take an example for clarification that how to insert item into an array. Before learn this example, first should take array declaration.

Syntax of declaring array

datatype [] identifier;

where, you can use any data type to declare an array like int , float, double, string etc. This bracket [] known as rank of the array, its doesn't take decimal type value , always take integer. The value in the rank known as size of the array. Suppose i take 10 in the rank, then array index start from 0 to 9. Here 0 is the starting index and 9 is the last index. All arrays value consists of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

Lets more about an array that is how to initialize array variable. 

Array initialization means you can assign some values in to the array. 

Before initialization, should take new keyword for create instance of the array because array is a reference type. For example

int[] employee_age = new int[10];

In above mentioned example array hold 10 elements, means size of the array is 10. If you want to assign value in it, you can use index number. Like

employee_age[0] = 20;

employee_age[1] = 40;

Also You can assign values to the array at the time of declaration, like:

int [] employee_age = {20,21,22,23,24};
You can also create and initialize an array, like:
int [] employee_age = new int[5]  {20,21,22,23,24};
In the preceding example you can change the size of element, directly you can say re-sizing of an array. Also you can copy one array to another array in c#. In that case both array would locate to same memory location. like takes an simple example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {

            int[] employee_age = new int[5]{20,21,22,23,24};
            int[] second_array = employee_age;
            int i = 0;
            foreach (var item in second_array)
            {
                Console.WriteLine("arrays elements are[{0}]={1}",i,item);
                i++;
            }
            Console.ReadKey();
        }
    }
}

Code generate the following output

Above mentioned example covers declaring of an array, initialization of array and how to access array element in c#.

In c#, when we create a new array in the memory then compiler automatically initialize all index value with 0. Take a look.

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {

            int[] employee_age = new int[5];
            int[] second_array = employee_age;
            int i = 0;
            foreach (var item in second_array)
            {
                Console.WriteLine("arrays elements are[{0}]={1}",i,item);
                i++;
            }
            Console.ReadKey();
        }
    }
}

Code generate the following output

C# - Variables

A variable is everything in programming, in which we can store values also change it during program execution. Before declare a variable in c#, must use type of it, so you say Each variable in C# has a specific type. You can perform multiple operation on a single variable, which value stored in memory. Now in this topic we will cover

1. How to declare a variable

2. How to internalize a variable.

3. Types of a variable

1. How to declare a variable

 during the declaration of a variable, we use type of it. Through comma separation we declare multiple variable with single type. Lets take a simple syntax with example.

Syntax of single variable declaration 

<data_type> single_variable_declaration;

Example of single variable declaration

int Employee_age;

Syntax of list of variable declaration

<data_type> variable1, variable2, .....variableN;

Example of list of variable declaration

String Employee_Name, Employee_address, Employee_Country;

2. How to initialize a variable

The meaning of initialization is assigning some value into the variable. There are two types of initialization, first one is declaration time initialization, in which we can assign some value into the variable at declaration time. Lets take simple example

int employee_age=24;

In this example we are passing value(24) from the right side to left side. Also here we use assignment operator, In later session we will discuss about operators.

The last one is after declaration, in which we can pass the value into the variable after declaration of it. Like

int a,b,c; // Declaration over

a=10 ; // after initialization 

b=20 ; // after initialization 

c=a+b; // expression initialization

3. Types of a variable

In the c#, there are two types of variable, first one is local variable and second one is global variable. If we initialize a variable outside the block known as global variable you can consider public specifier as a example of it. If we initialize it inside the block known as local variable. lets take a simple example.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace ConsoleApplication4

{

    class Program

    {

        static void Main(string[] args)

        {

            int a = 10;

            {

                int b;

                a = 20;

                Console.WriteLine(a);

            }

            b = 30;  // b is a local variable , can't initialize here. 

            Console.WriteLine(a);

            Console.ReadKey();

        }

    }

}

In this program we get error because a local variable can't initialize outside the block.

C# - Type Conversion

In Type casting you can change types or convert one types of data into another type.In c#, Basically there are two types. First one implicit type casting and another one is explicit type casting. Both are very useful in c# application development like, in web application text box return by default text value and you want to take int type value then you must convert text to int.
Implicit type conversion - conversion is performed by c# compiler automatically with type-safe manner. Now lets to take an simple example here.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            Double d;
            int a=15;
            d = a;
            Console.Write(d.ToString());
            Console.ReadKey();
        }
    }
}

Code Generate the following output

Explicit type conversion - conversion is performed by programmer explicitly using the pre-defined functions. Suppose you want to change double type value into int type then must use pre-defined functions. This type of conversion is not safe. Lets take an simple example.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            Double d = 3.14;
            int a;
            a = (int)d;
            Console.Write(a.ToString());
            Console.ReadKey();
        }
    }
}

Code generate the following output

So there are various types available in c#, these are

ToBoolean : Convert one type to boolean type , if possible

ToByte : Convert one type to Byte type , if possible

etc.

How to add image and icon on button control in windows form c#

On a Button control, Text is not enough to making it beautiful. So If you want to make attractive windows application then you should go for images. In this article i will show you how to add image on button control also how to align with text. Now lets to start.
Step-1 : Add a new windows form project in visual studio, i have visual studio 2013, you can work in earlier version.
Step-2 : Add a button control in designer view of form
Step-3 : Select 'Image' property of Button control. Button control-->Property-->Image

Step-4 : Click on "ok" Button
Step-5 : After adding the image on button control, Set "ImageAlign" property of Button control to MiddleLeft.
Step-6 : Also set "TextAlign" property of Button control to MiddleRight.
Step-7 : Set Text Property of button control, decide you. In this article button with image is related to fruits so i giving you fruits title.
Text = " Fruits"
Now, your button control looking like

Change Text to Voice in windows form c#

Suppose your pronunciation is not right at this time and you want to use text to voice converter software. We are presenting to you  a very easy and simple converter, which convert text to voice. There are various steps to designing this types of converter, these are:

Step-1 : Create a new project in windows form.

Step-2 : Add a reference file (System.Speach) in the project.

Step-3 : Add one rich textBox and menu strip control on windows form.

Step-4 : Add items into menuStrip control looking like

Step-5 : Add two namespaces in code file, which are

using System.Speech;

using System.Speech.Synthesis;

// Both are used for reading text and doing operation on it.

Step-6 : Copy this code and paste into your menu strip item handlers.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Speech;

using System.Speech.Synthesis;

namespace text2spech

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        SpeechSynthesizer speech_reader = new SpeechSynthesizer();

        private void speakToolStripMenuItem_Click(object sender, EventArgs e)

        {

            if (richTextBox1.Text != "")

            {

                speech_reader.Dispose();

                speech_reader = new SpeechSynthesizer();

                speech_reader.SpeakAsync(richTextBox1.Text);

            }

            else

            {

                MessageBox.Show("Write some text into your text box");

            }

        }

        private void pauseToolStripMenuItem_Click(object sender, EventArgs e)

        {

            if (speech_reader !=null)

            {

                if (speech_reader .State == SynthesizerState.Speaking)

                {

                    speech_reader.Pause();

                }

            }

        }

        private void resumeToolStripMenuItem_Click(object sender, EventArgs e)

        {

            if (speech_reader != null)

            {

                if (speech_reader.State == SynthesizerState.Paused)

                {

                    speech_reader.Resume();

                }

            }

        }

        private void stopToolStripMenuItem_Click(object sender, EventArgs e)

        {

            speech_reader.Dispose();

        }

    }

}

Code Generate the following output

Returns CheckBox Input element using Html.CheckBox() Handler: MVC

Html.CheckBox() handler, used to return check box input element to be input true or false value by user. Selected checkbox will return true and un-selected will return false, otherwise it returns null for the programmer to store/use the value.

In compare to other input element, this handler is easy to bind to with the data source provided by the programmer for the page. This handler/method have its own parameters list as Html.Label() or Html.TextBox() handlers have.

Parameters

• Name: specifies the name of form field. It is string type of parameter.
• isChecked: programmer should pass true to select the checkbox, otherwise false. As the name implies it is Boolean type of parameter.
• htmlAttributes: specifies an object that contains all the html attributes to set for the element as discussed earlier.

@Html.CheckBox("isMarried", true);

This line will return an check box input element on the page with checked true value, as passed with the method. The name of form field will be isMarried, used to get the value of this element.

@Html.CheckBoxFor(…)

This handler works same as above but for a particular property in the object specified in the expression. This handler is used to return check box input for only the bind to property exists in the mode passed by an action to this view page.

Parameters are almost same except the expression which is a predicate to be bind this element with the specific property.

Let’s suppose this view page is bind to a model of type student having the properties like name, age, isMarried and etc. To bind this check box handler programmer have to write the following line of code:

@Html.CheckBoxFor(model => model.isMarried, false)

This handler now binds to isMarried property of the model and by default it will be un-selected on the page.

How to Execute Server-side code based on condition: Razor

Razor syntax helps developers to execute the code based on some condition specified like if-else or may be switch statement. Programmer can easily decide, which statement(s) need to be executed, based on the condition given within if statement.

IF-Else Statement:

To test a condition by using IF statement, write a condition in if parenthesis, start an IF block and at the last write the statements to be executed in the curly braces i.e. called if block. This block will be execute when the given condition is true, to execute some statements on the false of this condition, programmer have to write ELSE part.

@{

    var value = 5;

}

@if (value >4)

{

    <p style="font-size:20px; font-weight:bold">Value is greater than "4"</p>

}

else

{

    <p style="font-size:20px; font-weight:bold">Value is less than or equal to "4"</p>

}

In the above block variable value have initial value 5. According to the given condition the first block will execute. If we change the value as 4 then it will execute the second block i.e. ELSE part of the statement. What if we have some more condition to be check, either we have to use if-else if-else condition or switch statement.

Switch Statement

To execute statements based on number of individual conditions, switch statement is used by the developer. Switch statement specifies some cases to be check by compiler and execute the statements written in particular case on which condition matches. Just notice the following lines of code:

@switch (value)

{

    case 5: <p>Value is: 5</p>

        break;

    case 4: 

    case 3: 

    case 2: <p>Value is >= 2 and <=4</p>

        break;

    case 1: <p>Value is: 1</p>

        break;

    default: <p>Value is either <1 or >5</p>

        break;

}

Here, I have specified individual statement for case 1 and 5 but a single statement for case 2, 3 and 4. This feature of switch statement is used when a single statement is to be executing for one or more cases. Now for value = 2 /3/4 a single statement “Value is >= 2 and <=4” will execute.

Like in if condition the else part will execute when false, switch statement provides “default” case for all other case except specified. This value can also be changed or assigned at runtime to execute code dynamically.

How to Optimize Indexes in SQL Server

SQL Server automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. These modifications cause the information within the index to become scattered in the database. Fragmentation exists when indexes within the index to become scattered in the database.

Fragmentation exists when indexes have pages where the logical ordering does not match the physical ordering within the data file. Heavily fragmented indexes can degrade the query performance and cause your application to respond slowly.

Fragmentation normally occurs when a large number of insert and update operations are performed on the table. Fragmented data can cause the SQL Server to perform unnecessary data reads. This affects the performance of the query. Therefore, index defragmentation is necessary to speed up the query performance.

The first step in deciding which defragmentation method to use is to determine the degree of index fragmentation. You can detect index fragmentation by using the sys.dm_db_index_physical_stats system function.

The following statement displays a list of all the indexes on the HumanResources.Employee table with their fragmentation level:

SELCET a.index_id AS IndexID, name AS IndexName,
Avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID (N’AdventureWorks’),
OBJECT_ID (‘HumanREsources.Employee’), NULL, NULL, NULL ) AS a
JOIN sys.indexes AS b ON  a.object_id = b.object_id AND a.index_id = b.index_id ORDER BY Fragmentation desc

In the preceding output given by this query, you can notice that the AK_Employee_LoginID index shows a high level of fragmentation.

After the degree of fragmentation is known, the fragmentation needs to be corrected. The following table lists the actions to be taken at different fragmentation levels to defragment the index.

Fragmentation Level                         Action to be Taken
>5% and <=30%  ALTER INDEX REORGANIZE
>30%  ALTER INDEX REBUILD WITH (ONLINE – ON)

Method of Degragmentation

You can execute the following command to defragment the AK_Employee_LoginID index:

ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD
After executing the preceding command, the degree of fragmentation is reduced.

Managing indexes with pre-defined Query: SQL Server

In addition to creating indexes in sql server, database developer also need to maintain them to ensure their continued optimal performance. The common index maintenance tasks include disabling, enabling, renaming, and dropping an index. As a database developer, you need to regularly monitor the performance of the index and optimize it.

Disabling Indexes

When an index is disabled, the user is not able to access the index. If a clustered index is disabled then the table data is not accessible to the user. However, the data still remains in the table, but is unavailable for Data Modification Language (DML) operations until the index is dropped or rebuilt.

To rebuild and enable a disabled index, use the ALTER INDEX REBUILD statement or the CREATE INDEX WITH DROP_EXISTING statement.

The following query disables a non-clustered index, IX_EmployeeID, on the Employee table.

ALTER INDEX IX_EmployeeID
ON Employee DISABLE

Enabling Indexes

After an index is disabled, it remains in the disabled state until it is rebuilt or dropped. You can enable a disabled index by rebuilding it through one of the following methods:

• Using the ALTER INDEX statement with the REBUILD clause
• Using the CREATE INDEX statement with the DROP_EXISTING clause
• Using the DBCC DBREINDEX

By using one of the preceding statements, the index is rebuilt and the index status is set to enable. You can rebuild a disabled clustered index, when the ONLINE option is set to ON.

Renaming Indexes

You can rename the current index with the help of the sp_rename system stored procedure.
The following statement renames the IX_JobCandidate_EmployeeID index on the JobCandidate table to IX_EmployeeID.

EXEC sp_rename
‘HumanResources.JobCandidate.IX_JobCandidate_EmployeeID’,
‘IX_EmployeeID’, ‘index’

Dropping Indexes

When you no longer need an index, you can remove it from a database. You cannot drop an index used by either a PRIMARY KEY or UNIQUE constraint, except by dropping the constraint.

The following statement drops the IDX_Employee_ManagerID index on the Employee table:

DROP INDEX IDX_Employee_ManagerID
ON Employee

How to Create Partition Scheme and Clustered Index: SQL Server

After creating the partition function, programmer needs to create a partition scheme to associate it with the partition function. Based on the boundary values defined in the partition function, there will be five partitions. The data of each partition is stored in a filegroup. You should have the same number of filegroups as partitions. If there are five partitions, you need to create five filegroups: fg1, fg2, fg3, fg4, fg5.

The following statements create the PSOrderDate partition scheme, associating it with the PFOrderDate partition function:

CREATE PARTITION SCHEME PSOrderDate
AS PARTITION PFOrderDate
TO (fg1, fg2, fg3, fg4, fg5)

The partition scheme, PSOrderDate, created in the preceding statement directs each partition to a separate filegroup.

Creating a Clustered Index

After creating the partition scheme, you need to associate it with a clustered index. As the clustered index is created on the attribute having unique and non-null values, you can create the index on the SalesOrderID column of the SalesOrderHeader table. To create the partitioned index, you need to associate the clustered index with the partition scheme as follows:

CREATE CLUSTERED INDEX ix_SalesOrderID
ON Sales.MySalesOrderHeader (SalesOrderID)
ON PSOrderDate (OrderDate)

The preceding statement will distribute the table data into five filegroups based on the yearly data of orders stored in the OrderDate column.

How to Create Partitioned Indexes in SQL Server

In SQL Server, indexes can also be partitioned based on the value ranges. Similar to the partitioned tables, the partitioned indexes also improve query performance. Partitioning enables you to manage and access subsets of data quickly and efficiently. When indexes become very large, you can partition the data into smaller, more manageable sections as the data is spread across file groups in a database.

Consider an example. In the Adventure Works database, the SalesOrderHeader table contains the details about the order received by Adventure Works, Inc. As the data in this table is large, the query takes a long time to execute. To solve this problem, you can create a partitioned index on the table. Partitioning an index will distribute the data in the table into multiple filegroups, thereby partitioning the table. This will enable the database engine to read or write data quickly. This also helps in maintaining the data efficiently.

Partitioning is allowed only in the Enterprise Edition of SQL Server. To create a partitioned index, you need to perform the following tasks:

• Create a partition function
• Create a partition scheme.
• Create a clustered index.

Creating a Partition Function

Similar to creating a partitioned table, you need to create a partition function to create a partitioned index. The partition function will determine the boundary values for creating partitions.

Consider an example. The queries on the SalesOrderHeader table are mostly base on the OrderData column. The sales manager of Adventure Works requires the details of the orders received, on a yearly basis. The table contains the details of orders for the last five years beginning from 2001. Based on this information, you can create a partition function as follows:

CREATE PARTITION FUNCTION PFOrderDate (date time)
AS RANGE RIGHT FOR VALUES (‘2002-01-01’, ‘2003-01-01’, ‘2004-01-01’, ‘2005-01-01’)

The preceding statement creates a partition function, PFOrderDate, by using the date time data type. It specifies four boundary values. Therefore, there will be five partitions. As range right is specified for partitioning, the first partition will contain data less than the first boundary value, 2002-01-01. The second partition will contain data greater than or equal to 2002-01-01 and less than or equal to 2003-01-01. Similarly, other partitions will store data.

How to bind Gridview using strong model binding in asp.net

In our previous article, we have already learn how to bind GridView using ado.net, Entityframework, etc. Now, today we will learn strong model binding. Follow my steps
Step-1 :  Create a course.cs class, which is include in my previous article
Step-2 : Create a another class, which named as DataContext.cs class also include in my previous article.
Step-3 : After adding the two class in the project , add a new web form, which named as "getItem.aspx".
Step-4 : Add GridView control on web form
Step-5 :  Your source code looking like this

<form id="form1" runat="server">
    <div>
    
    </div>
        <asp:GridView ID="GridView1" runat="server" DataKeyNames ="ID" AutoGenerateColumns="false" ItemType="course" SelectMethod="getCourse">
            <Columns>
                
                <asp:BoundField DataField="ID" HeaderText="Course_Id" />
                  <asp:BoundField DataField="ccode" HeaderText="Course_code" />
                 <asp:BoundField DataField="cname" HeaderText="Course_Name" />


            </Columns>
        </asp:GridView>
    </form>

Here we add two extra attribute, which names as SelectMethod and ItemType. ItemType define the whole structure of your code. Suppose your course class inside in a namespace then your ItemType is
namespace.class name. Using the select method you can retrieve table data from database.

Step-6 : copy this code and paste into your code behind page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class getitem : System.Web.UI.Page
{
    private readonly DataContext dc = new DataContext();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    public IQueryable<course> getCourse()
    {
        return dc.courses;
    }

}
First to create DataContext object, which is used for retrieving data from database. Also create Select method which return  IQueryable type.

Code generate the following output

How to Bind Gridview using Entity Framework in ASP.NET

In our previous article, we have already learned that how to bind gridview using ado.net. Today we will learn simplest technique to bind gridview using entity framework. Lets start to bind.

Step-1 : Add a Course class with some attributes like

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

/// <summary>

/// Summary description for course

/// </summary>

public class course

{

    public int ID { get; set; }

    public string cname { get; set; }

    public string ccode { get; set; }

}

In this code ID is the primary key of the table. Learn How to make primary key in entity framework.

Step-2 : Add a DataContext class for creating database with some table like.

using System;

using System.Collections.Generic;

using System.Data.Entity;

using System.Linq;

using System.Web;

/// <summary>

/// Summary description for DataContext

/// </summary>

public class DataContext : DbContext

{

public DataContext():base ("connection1")

{

//

// TODO: Add constructor logic here

//

}

  

    public DbSet<course> courses { get; set; }

}

DataContext class inherit from DbContext class which is inside in System.Data.Entity namespace. Learn How to download it. 

Here we pass ConnectionString in base class constructor as a parameter. Also add Course table in DataContext using DbSet. 

Step-3 : Add a new webform, which named as "getItem.aspx". Also add a gridView control onit from toolBox.

Step-4 : Add this code in codebehind page.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class getitem : System.Web.UI.Page

{

    DataContext dc = new DataContext();

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!Page.IsPostBack)

        {

            bindgrid();

        }

    }

    private void bindgrid()

    {

        var item = from d in dc.courses

                   select d;

        GridView1.DataSource = item.ToList();

        GridView1.DataBind();

        

    }

}

Code Generate the following output

How to download EntityFramework.dll file, Add System.Data.Entity namespace

This namespace take some classes of entity framework for accessing data. In this article we will learn how to add System.Data.Entity namespace because Entity framework.dll missing by default. So first to add entityframework.dll file as a reference.
How to download
Step-1 : Select Package Manager console from Tool menu.

Step-2 : Write
PM> Install-package entityframework

Note : Internet connection must.
Now, Using this method you can download latest version of entityframework.

How to populate comboBox in windows form c#

ComboBox is a collection, in which we can take Text as well as value of the text. After array, developer designed comboBox or DropDownList because array contains one attribute of object, its does not take two value at a time. Suppose you want to store employee name and age into memory. Where we would not use array. Here we will bind the comboBox in different manner like
Method-1 : Bind ComboBox from dataset and datatable using ADO.NET
Method-2 : Bind ComboBox using Entity framework.

Method-1

I have a database table like Students, which take some value. The Data View of table, which is stored in database.

C# Code for binding ComboBox (Binding from DataReader)

using System;
using WindowsFormsApplication10;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Entity;
using System.Data.SqlClient;

namespace WindowsFormsApplication10
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=(LocalDB)\\v11.0; Initial Catalog=STUDENTer; Integrated Security=true";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Students";
            cmd.Connection = con;
            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read ())
            {
                string name = rd.GetString(1);
                comboBox1.Items.Add(name);

            }

        }
    }
}

Binding from DataTable

 private void Form1_Load(object sender, EventArgs e)
        {
           
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=(LocalDB)\\v11.0; Initial Catalog=STUDENTer; Integrated Security=true";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Students";
            cmd.Connection = con;
            SqlDataReader rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(rd);
            comboBox1.DataSource = dt;
            comboBox1.DisplayMember = "name";

        }

Binding from DataSet

 private void Form1_Load(object sender, EventArgs e)
        {
           
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=(LocalDB)\\v11.0; Initial Catalog=STUDENTer; Integrated Security=true";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from Students";
            cmd.Connection = con;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            comboBox1.DataSource = ds.Tables[0];
            comboBox1.DisplayMember = "name";

        }

Method-2

If you want to bind combobox using entity framework or you can say strong model binding, just follow my steps.
Step-1 : One class, which name as "Student.cs", which works as database table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WindowsFormsApplication10
{
   public class Student
    {
        public int Id { get; set; }
        public string name { get; set; }
    }
}
In this class we are taking two member first is Id and second is name.
Step-2 : Take another class, which name as "DataContext", which is used for creating database also used for inserting table in it. Look like

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;

namespace WindowsFormsApplication10
{
    class DataContext: DbContext
    {
        public DataContext() :
            base("connectionstring1")
        {
           

        }
        public DbSet<Student> Students { get; set; }

    }
}

In this code, DataContext class inherited from DbContext class. Base class constructor create database using conectionstring, which is defined in App.Config file. like

<connectionStrings>

    <add name="connectionstring1" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDB)\v11.0; Initial Catalog=STUDENTer; Integrated Security=true" />

  </connectionStrings>

Step-3 : Add ComboBox control on form
Step-4 : Copy this code and paste into your form load event
private void Form1_Load(object sender, EventArgs e)
        {
            DataContext dc=new DataContext();
            var stu = from student in dc.Students
                      select new
                      {
                         student.name
                      };
            comboBox1.DataSource = stu.ToList();
            comboBox1.DisplayMember = "name";

        }

Code Generate the following output