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