Serving Information Simply

Thursday 26 July 2012

Difference between char varchar and nvarchar in sql server




Char DataType

Char datatype which is used to store fixed length of characters. Suppose if we declared char(50) it will allocates memory for 50 characters. Once we declare char(50) and insert only 10 characters of word then only 10 characters of memory will be used and other 40 characters of memory will be wasted.

varchar DataType

Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.

nvarchar DataType

nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

So if we are not using other languages then it’s better to use varchar datatype instead of nvarchar

Tuesday 24 July 2012

Ajax updatepanel example with triggers in asp.net

In this Post I will explain what is Ajax updatepanel control, advantages of updatepanel control and how to use multiple updatepanels in asp.net that will prevent  refreshing the whole page and will allow only a single content to perform their task without affecting other content.

Let's Start now...........................


Ajax updatepanel will help us to avoid full postback of the page i.e., avoid refresh of the whole page content with postback and stop flickering of the page which is associated with a postback and allows only partial postbacks. By using Ajax updatepanel we can refresh only required part of page instead of refreshing whole page.
Ajax updatepanel contains property called UpdateMode this property is used to specify whether UpdatePanel is always refreshed during a partial render or if it refresh only when a particular trigger hit. By default updatepanel contains UpdateMode="Always" if we want to set it conditionally we need to change this property UpdateMode="Conditional"
Ajax updatepanel control contains two child tags those are ContentTemplate and Triggers.
ContentTemplate is used to hold the content of the page means suppose we designed page with some controls we will place controls inside of the ContentTemplate
Triggers we used in a situation like need to refresh updatepanel only whenever I click some button control in that situation I will define those controls with this Triggers child tag.
Our Sample update panel control will be like this
<asp:UpdatePanel ID="UpdatePanel2" runat="server"
 UpdateMode="Conditional">
 <ContentTemplate>
 <asp:Label ID="Label2" runat="server"
 ForeColor="red" />
……………………………………………………..
………………………………………………………
……………………………………………………….
 </ContentTemplate>
 <Triggers>
 <asp:AsyncPostBackTrigger ControlID="Button1"
 EventName="Click" />
</Triggers>
 </asp:UpdatePanel>
Now we will create one sample application with updatepanels for that first create application and design your aspx page will be likes this
<html xmlns="http://www.w3.org/1999/xhtml" >
 <head id="Head1" runat="server">
 <title>UpdatePanel Example in asp.net</title>
 </head>
 <body>
 <form id="form1" runat="server">
 <asp:ScriptManager ID="ScriptManager1" runat="server"/>
 <div>
 <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
 <ContentTemplate>
 <fieldset style="width:30%">
 <legend>Update Panel-1</legend>
 <asp:Label ID="lbl1" runat="server" ForeColor="green"/><br />
 <asp:Button ID="btnUpdate1" runat="server" Text="Update Both Panels" OnClick="btnUpdate1_Click" />
 <asp:Button ID="btnUpdate2" runat="server" Text="Update This Panel" OnClick="btnUpdate2_Click" />
 </fieldset>
 </ContentTemplate>
 </asp:UpdatePanel>
 <asp:UpdatePanel ID="UpdatePanel2" runat="server" UpdateMode="Conditional">
 <ContentTemplate>
 <fieldset style="width:30%">
 <legend>Update Panel-2</legend>
  <asp:Label ID="lbl2" runat="server" ForeColor="red" />
  </fieldset>
 </ContentTemplate>
 <Triggers>
 <asp:AsyncPostBackTrigger ControlID="btnUpdate1" EventName="Click" />
 </Triggers>
 </asp:UpdatePanel>
 </div>
 </form>
 </body>
</html>
If you observe above code in UpdatePanel2 I defined Triggers property with btnUpdate1. Here UpdatePanel2 content will update only whenever we click on btnUpdate1 because we defined UpdatePanel2 property UpdateMode="Conditional" and we set AsyncPostBackTrigger property with btnUpdate1
Write following code in code behind
C#.NET
protected void btnUpdate1_Click(object sender, EventArgs e)
{
lbl1.Text = DateTime.Now.ToLongTimeString();
lbl2.Text = DateTime.Now.ToLongTimeString();
}
protected void btnUpdate2_Click(object sender, EventArgs e)
{
lbl1.Text = DateTime.Now.ToLongTimeString();
lbl2.Text = DateTime.Now.ToLongTimeString();
}




Demo


If you observe above sample whenever I click on button “Update Both Panels” it’s updating data in both updatepanels but if click on button “Update This Panel” it’s updating data in first updatepanel because in both updatepanels we defined condition UpdateMode= "Conditional" and set Triggers conditions because of that here updatepanels will update conditionally and in UpdatePanel2 we written AsyncPostBackTrigger property to update panel only whenver we click on btnUpdate1

----
Thanks .

Monday 23 July 2012

Indexes in sqlserver full view




  *Here i am providing some basic Idea about Indexes that will help you in understanding the use of   Indexes .


Home
Indexes
 
Indexes Fundamentals
 
Introduction




If you take a look at the last pages of a non-fictional book (such as a book about history, economics, mathematics, sociology, or statistics, etc), you may find a series of pages that start in a section label Index. The words in that series allow you to locate a section of the book that mentions, explains, or describes the word and related topics. An index in a book makes it easy and fast to get to a section of a book that deals with a particular topic.

Like a book, a table or a view can use the mechanism provided by an index. In a table or a view, an index is a column (or many columns) that can be used to locate records and take a specific action based on some rule reinforced on that (those) column(s).

Practical LearningPractical Learning: Introducing Indexes

  1. Start the computer and log in with an account that has administrative rights
  2. Launch Microsoft sql server.
  3. In the Authentication combo box, select window authentication.
  4. Click Connect
  5. To create a new database, in the Object Explorer, right-click Databases and click New Database...
  6. Set the name to DepartmentStore2
  7. Click OK
  8. In the Object Explorer, right-click Databases and click Refresh
  9. Expand DepartmentStore2
  10. Right->click Tables and click New Table...
  11. Click the fields as follows:
     
    Column Name Data Type Allow Nulls
    EmployeeNumber int Unchecked
    FirstName nvarchar(20) Checked
    MI nchar(1) Checked
    LastName nvarchar(20) Unchecked
    Username nvarchar(20) Unchecked
    Password nvarchar(20) Checked
  12. Close the table
  13. When asked whether you want to save it, click Yes
  14. Set the name to Employees
  15. Click OK
  16. On the Standard toolbar, click the New Query button New Query
  17. To create a new database and a table, type the following:
    IF EXISTS(SELECT name FROM sys.databases
       WHERE name = N'HotelManagement1')
    DROP DATABASE HotelManagement1;
    GO
    CREATE DATABASE HotelManagement1;
    GO
    
    USE HotelManagement1;
    GO
    
    IF OBJECT_ID('Rooms', 'U') IS NOT NULL
      DROP TABLE Rooms
    GO
    
    CREATE TABLE Rooms
    (
        RoomID int identity(1, 1) primary key not null,
        RoomNumber nvarchar(10),
        LocationCode nchar(10) default N'Silver Spring',
        RoomType nvarchar(20) default N'Bedroom',
        BedType nvarchar(40) default N'Queen',
        Rate money default 75.85,
        Available bit default 0
    );
    GO
  18. Press F5 to execute
Visually Creating an Index
The database engine is already equipped with a default mechanism to automatically make up an index on a table depending on how the table is created. For example, if you create a primary key on a table, the database engine automatically creates an index for the column(s) used as the primary key. Otherwise, you can still explicitly create an index. You can create an index visually or using SQL code.
To visually create an index, you can use the Indexes/Keys dialog box. To display it, in the Object Explorer, expand the database that holds the table or view and expand the Tables node. Right-click the table or view for which you want to create the index and click Design. In the window, right-click Indexes/Keys... This would open the Indexes/Keys dialog box:
Indexes/Keys
To create an index, click the Add button. The first piece of information you should provide is the name. Normally, the database engine provides a default name. If you want to change it, click (Name) and type the desired name. The other very important piece of information you should provide is at least one column. By default, the database engine selects the first column of the table. If this is not the column you want to use, click Columns and click its ellipsis button Ellipsis. This would open the Index dialog box:
Index Columns
From the boxes under Column Name, you can select each column. Once you are ready, click OK.
Practical LearningPractical Learning: Visually Creating an Index
  1. In the Object Explorer, under DepartmentStore2, right-click Tables and click Refresh
  2. Expand the Tables node. Right-click dbo.Employees and click Design
  3. Right-click anywhere in the white area and click Indexes/Keys...
  4. Click Add
  5. Click Columns and click its ellipsis button
  6. Click the combo box of the default EmployeeNumber and select LastName
  7. Click the box under LastName
  8. Click the arrow of its combo box and select Username

    Index Columns
  9. Click OK
  10. Click (Name) and type IX_Credentials
  11. Click Close
  12. Close the table
  13. When asked whether you want to save, click Yes
Creating an Index With SQL
To create an index in SQL, the basic formula to follow is:
CREATE INDEX IndexName ON Table/View(Column(s))
Alternatively, open a Query window. Then, in the Templates Explorer, expand the Index node. Drag Create Index Basis (or another sub-node) and drop it in the window. Skeleton code would be generated for you:
-- =============================================
-- Create index basic template
-- =============================================
USE <database_name, sysname, AdventureWorks>
GO

CREATE INDEX <index_name, sysname, ind_test>
ON <schema_name, sysname, Person>.<table_name, sysname, Address> 
(
 <column_name1, sysname, PostalCode>
)
GO
The creation on an index starts with the CREATE INDEX expression, followed by a name for the index, followed by the ON keyword. In the Table/View placeholder, enter the name of the table or view for which you want to create the index, followed by parentheses in which you enter at least one column. Here is an example:
-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
 SELECT name 
  FROM sys.databases 
  WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO

CREATE DATABASE Exercise
GO

USE Exercise;
GO

-- =============================================
-- Database: Exercise
-- Table;    Employees
-- =============================================
CREATE TABLE Employees
(
 EmployeeNumber int NOT NULL,
 LastName nvarchar(20) NOT NULL,
 FirstName nvarchar(20),
 Username nchar(8) NOT NULL,
 DateHired date NULL,
 HourlySalary money
);
GO

INSERT INTO Employees
VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),
      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),
      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),
      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),
      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),
      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45);
GO

CREATE INDEX IX_Employees
ON Employees(EmployeeNumber);
GO
If the index will include more than one column, list them separated by commas. Here is an example:
CREATE INDEX IX_Employees
ON Employees(LastName, Username);
GO
Index Maintenance
 
Introduction
In microsoft sql Server (and most database system), an index is treated as an object. That is, an index can be checked or deleted at will.
Deleting an Index
If you don't need an index anymore, you can delete it. You can do this visually or manually.
To visually delete an index, open its table in design view. Right-click somewhere in the table window and click Indexes/Views. In the left frame, click the name of the index to select it, and click the Delete button. You will not receive a warning. Then click Close. If you want to change your mind and keep the index, don't save the table.
The basic syntax to delete an index in Transact-SQL is:
DROP INDEX IndexName ON TableName;
In this formula, replace the TableName with the name of the table that contains the index. Replace the IndexName with the name of the index you want to get rid of.
Here is an example:
USE Exercise;
GO
DROP INDEX IX_Employees ON Employees;
GO
Checking the Existence of an Index
Before performing an operation on an index, you may want to check first that it exists. For example, if you try creating an index and giving it a name that exists already, you would receive an error. The following code
USE Exercise;
GO

CREATE INDEX IX_Employees
ON Employees(EmployeeNumber);
GO
would produce:
Msg 1913, Level 16, State 1, Line 2
The operation failed because an index or statistics 
with name 'IX_Employees' already exists on table 'Employees'.
To visually check the existence of an index, open the table or view in design view, right-click the middle of the window and click Indexes/Keys. The list of indexes should appear on the left side. Here is an example:
To assist you with checking the existence of an index, Transact-SQL provides the following formula:
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = IndexName)
Do something here
The primary thing you need to provide in this formula is the name of the index. Once you have checked, you can take the necessary action. Here is an example:
USE Exercise;
GO

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_Employees')
DROP INDEX IX_Employees
ON Employees
GO
CREATE INDEX IX_Employees
ON Employees(EmployeeNumber);
GO
The Types of Indexes
 
Introduction
 microsoft sql server supports various types of indexes. The two broadest categories are clustered and non-clustered.
Clustered Indexes
In our introduction, we saw that an index is primarily created using one or more columns from a designated table. This means that, when it comes to using the index, we would use the values stored in the column(s) that was (were) selected for the index. Such an index is referred to as clustered. The columns that were made part of an index are referred to as keys.
To visually create a clustered index, display the Indexes/Keys dialog box. In the dialog box, when creating a new indexed or after clicking the name of an existing index, in the right list, Click create As Clustered and select Yes:
Clustered Index
Once you are ready, click Close.
To create a clustered index in SQL, use the following formula:
CREATE CLUSTERED INDEX IndexName ON Table/View(Column(s))
From the description we gave previously, the only new keyword here is CLUSTERED. Based on this, here is an example:
-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS(SELECT name 
    FROM sys.databases 
    WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO

CREATE DATABASE Exercise
GO

USE Exercise;
GO

-- =============================================
-- Database: Exercise
-- Table;    Employees
-- =============================================
CREATE TABLE Employees
(
 EmployeeNumber int NOT NULL,
 LastName nvarchar(20) NOT NULL,
 FirstName nvarchar(20),
 Username nchar(8) NOT NULL,
 DateHired date NULL,
 HourlySalary money
);
GO

INSERT INTO Employees
VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),
      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),
      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),
      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),
      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),
      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45);
GO

CREATE CLUSTERED INDEX IX_Employees
ON Employees(LastName);
GO
A table that contains a clustered index is called a clustered table.
There are various aspects to a clustered index:
  • To make it easy to search the records, they (the records) are sorted. This makes it possible for the database engine to proceed in a top-down approach and quickly get to the desired record
  • Without this being a requirement, each record should be unique (we have already seen how to take care of this, using check constraints; later on, we will see again how to create unique records)
  • There must be only one clustered index per table. This means that, if you (decide to) create a clustered index on a table, the table becomes equipped with one. If you create another clustered index, the previous one (clustered index) is deleted
Non-Clustered Indexes
While a clustered index uses a sorted list of records of a table or view, another type of index can use a mechanism not based on the sorted records but on a bookmark. This is called a non-clustered index. As opposed to a clustered table that can contain only one clustered index, you can create not only one, but as many as 249 non-clustered indexes.
To visually create a non-clustered index, display the Indexes/Keys dialog box. To create a new index, click the Add button. If an index was always created or set as clustered and you want to change it, you can change its Create As Clustered property from Yes to No.
To create a non-clustered index in SQL, use the following formula:
CREATE NONCLUSTERED INDEX IndexName ON Table/View(Column(s))
The new keyword in this formula is NONCLUSTERED. Everything is the same as previously described. Based on this, here is an example:
-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS(SELECT name 
    FROM sys.databases 
    WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO

CREATE DATABASE Exercise
GO

USE Exercise;
GO

-- =============================================
-- Database: Exercise
-- Table;    Employees
-- =============================================
CREATE TABLE Employees
(
 EmployeeNumber int NOT NULL,
 LastName nvarchar(20) NOT NULL,
 FirstName nvarchar(20),
 Username nchar(8) NOT NULL,
 DateHired date NULL,
 HourlySalary money
);
GO

INSERT INTO Employees
VALUES(62480, N'James', N'Haans', N'jhaans', N'1998-10-25', 28.02),
      (35844, N'Gertrude', N'Monay', N'gmonay', N'2006-06-22', 14.36),
      (24904, N'Philomène', N'Guillon', N'pguillon', N'2001-10-16', 18.05),
      (48049, N'Eddie', N'Monsoon', N'emonsoon', N'08/10/2009',   26.22),
      (25805, N'Peter', N'Mukoko', N'pmukoko', N'03-10-2004', 22.48),
      (58405, N'Chritian', N'Allen', N'callen', N'06/16/1995', 16.45);
GO

CREATE NONCLUSTERED INDEX IX_Employees
ON Employees(LastName, FirstName);
GO
If you create an index without specifying CLUSTERED or NONCLUSTERED, the database engine automatically makes it non-clustered.
Practical LearningPractical Learning: Creating a Non-Clustered Index With SQL
  1. Click in the top section of the Query window,  press Ctrl + A to select the whole text
  2. To create an index using Transact-SQL, type the following:
    USE HotelManagement1;
    GO
    
    IF EXISTS(SELECT name FROM sys.indexes
       WHERE name = N'IX_RoomsIdentities')
    DROP INDEX IX_RoomsIdentities
    ON Rooms
    CREATE NONCLUSTERED INDEX IX_RoomsIdentities
    ON Rooms(RoomNumber, LocationCode);
    GO
  3. Press F5 to execute




 




 
 


Indexes and Table Creation
We already know how to create a primary key on a table. Here is an example:
USE Exercise;
GO

CREATE TABLE Students
(
 StudentID int PRIMARY KEY,
 FirstName nvarchar(50) NOT NULL,
 LastName nvarchar(50));
GO
When you create a primary key, the database engine automatically creates an index on the table and chooses the primary key column as its key. You have the option of indicating the type of index you want created. To do this, on the right side of the name of the column, enter CLUSTERED or NONCLUSTERED. If you don't specify the type of index, the CLUSTERED option is applied.
Practical LearningPractical Learning: Creating a Clustered Tables
  1. Click the top section of the Query window and press Ctrl + A
  2. To complete the database, type the following:
    USE HotelManagement1;
    GO
    
    CREATE TABLE Customers (
        CustomerID int identity(1, 1) primary key CLUSTERED NOT NULL,
        AccountNumber nchar(10) UNIQUE,
        FullName nvarchar(50) NOT NULL,
        PhoneNumber nvarchar(20),
        EmailAddress nvarchar(50),
        CONSTRAINT CK_CustomerContact
     CHECK ((PhoneNumber IS NOT NULL) OR (EmailAddress IS NOT NULL))
    );
    GO
  3. Press F5 to execute
Data Entry and Analysis With Indexes
 
Introduction
In our introduction, we saw that an index can make it possible to take some action during data entry, such as making sure that a column have unique values for each record or making sure that the combination of values of a group of columns on the same record produces a unique value. Besides this characteristic of indexes, they are actually very valuable when it comes to data analysis.
As mentioned for a book, the primary goal of an index is to make it easy to locate the records of a table or view.
Practical LearningPractical Learning: Entering Data
  1. Click the top area of the Query window and press Ctrl + A
  2. Type the following:
    USE HotelManagement1;
    GO
    
    INSERT INTO Rooms(RoomNumber, LocationCode) VALUES(104, N'SLSP');
    GO
    
    INSERT INTO Rooms(RoomNumber, LocationCode,
     BedType, Rate, Available)
               VALUES(105, N'SLSP', N'King', 85.75, 1),
       (106, N'SLSP', N'King', 85.75, 1)
    GO
    
    INSERT INTO Rooms(RoomNumber, LocationCode, Available)
        VALUES(107, N'SLSP', 1)
    GO
    
    INSERT INTO Rooms(RoomNumber, LocationCode, BedType, Rate)
        VALUES(108, N'SLSP', N'King', 85.75)
    GO
    
    INSERT INTO Rooms(RoomNumber, LocationCode, Available)
        VALUES(109, N'SLSP', 1)
    GO
    
    INSERT INTO Rooms(RoomNumber, LocationCode, RoomType, Rate, Available)
        VALUES(110, N'SLSP', N'Conference', 450.00, 1)
    GO
  3. Press F5 to execute
Introduction to Index Uniqueness
An index is made valuable in two ways. On one hand, the records should be sorted. A clustered index itself takes care of this aspect because it automatically and internally sorts its records. What if the records are not unique? For example, in a bad data entry on a list of employees, you may have two or more employees with the same employee's records. If you create an index for such a table, the database engine would create duplicate records on the index. This is usually not good because when it comes time to select records, you may have too many records and take a wrong action.
When creating a table, you can create index for it and let the index apply a rule that states that each record would be unique. To take care of this, you can apply a uniqueness rule on the index.
If you are visually creating an index, in the Indexes/Keys dialog box, select the index on the left side. On the right list, set the Is Unique field to Yes. On the other hand, if you want to remove this rule, set the Is Unique field to No.
To create a uniqueness index in SQL, apply the UNIQUE keyword in the formula:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
       INDEX index_name ON Table/View(Column(s))
Start with the CREATE UNIQUE expression, then specify whether it would be clustered or not. The rest follows the descriptions we saw previously. Here is an example:
-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
 SELECT name 
  FROM sys.databases 
  WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO

CREATE DATABASE Exercise
GO

USE Exercise;
GO

-- =============================================
-- Database: Exercise
-- Table;    Employees
-- =============================================
CREATE TABLE Employees
(
 EmployeeNumber int NOT NULL,
 LastName nvarchar(20) NOT NULL,
 FirstName nvarchar(20),
 Username nchar(8) NOT NULL,
 DateHired date NULL,
 HourlySalary money
);
GO

CREATE UNIQUE CLUSTERED INDEX IX_Employees
ON Employees(EmployeeNumber);
GO
Practical LearningPractical Learning: Using Index Uniqueness
  1. Click the top area of the Query window and press Ctrl + A
  2. Type the following:
    USE HotelManagement1;
    GO
    
    INSERT INTO Customers(AccountNumber, FullName,
                          PhoneNumber, EmailAddress)
    VALUES(N'395805', N'Ann Zeke', N'301-128-3506', N'azeke@yahoo.jp'),
          (N'628475', N'Peter Dokta', N'(202) 050-1629', 
              N'pdorka1900@hotmail.com'),
          (N'860042', N'Joan Summs', N'410-114-6820', 
       N'jsummons@emailcity.net'),
          (N'228648', N'James Roberts',
          N'(301) 097-9374', N'jroberts13579@gmail.net')
    GO
  3. Press F5 to execute
Unique Indexes and Data Entry
Once you have specified the uniqueness of an index on a table, during data entry, if the user enters a value that exists in the table already, an error would be produced. Here is an example:
USE Exercise;
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(92935, N'Joan', N'Hamilton', 22.50)
GO

INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(22940, N'Peter', N'Malley', 14.25)
GO

INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(27495, N'Christine', N'Fink', 32.05)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(22940, N'Gertrude', N'Monay', 15.55)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(20285, N'Helene', N'Mukoko', 26.65)
GO
This would produce:
(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in 
object 'dbo.Employees' with unique index 'IX_Employees'.
The statement has been terminated.

(1 row(s) affected)

 
Lesson Summary Questions
  1. Consider the following table:
    CREATE TABLE Students
    (
     StudentNumber nchar(8),
     [Full Name] nvarchar(50),
    );
    GO
    Which one of the following codes will create an index?
    1. CREATE INDEX SomeIndex ON COLUMN StudentNumber FROM Students;
      GO
    2. CREATE INDEX SomeIndex FROM Students ON COLUMN StudentNumber;
      GO
    3. FROM Students CREATE INDEX SomeIndex ON StudentNumber;
      GO
    4. ON StudentNumber CREATE INDEX SomeIndex FROM Students;
      GO
    5. CREATE INDEX SomeIndex ON Students(StudentNumber);
      GO
  2. What's the basic formula to create an index?
    1. CREATE OBJECT::INDEX IndexName ON Table/View(Column(s))
    2. CREATE IndexName ON Table/View(Column(s)) AS INDEX
    3. CREATE INDEX IndexName ON Table/View(Column(s))
    4. WITH INDEX CREATE IndexName ON Table/View(Column(s))
    5. CREATE IndexName ON Table/View(Column(s)) = INDEX
  3. What is the syntax of creating a partition function?
    1. CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
      AS RANGE [ LEFT | RIGHT ] 
      FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
      [ ; ]
    2. CREATE OBJECT::partition_function_name ( input_parameter_type )
      AS RANGE [ LEFT | RIGHT ] 
      FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
      [ ; ] AS PARTITION FUNCTION
    3. CREATE FUNCTION partition_function_name ( input_parameter_type )
      AS RANGE [ LEFT | RIGHT ] 
      FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
      IN PARTITION PartitionName[ ; ]
    4. CREATE PARTITION partition_function_name ( input_parameter_type )
      WITH RANGE [ LEFT | RIGHT ] 
      FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
      [ ; ] AS FUNCTION
    5. CREATE PARTITION FUNCTION AS partition_function_name ( input_parameter_type )
      FOR RANGE [ LEFT | RIGHT ] 
      SET VALUES = ( [ boundary_value [ ,...n ] ] ) 
      [ ; ]
Answers
  1. Answers
    1. Wrong Answer: The COLUMN keyword is not used in the formula to create an index
    2. Wrong Answer: The FROM keyword is not used to specify the table of an index
    3. Wrong Answer: That whole formula is wrong
    4. Wrong Answer: The creation of an index starts with CREATE INDEX
    5. Right Answer: That code will create an index
  2. Answers
    1. Wrong Answer: You don't need OBJECT:: to create an index
    2. Wrong Answer: An index is created with the CREATE INDEX expression, not CREATE ... AS INDEX
    3. Right Answer
    4. Wrong Answer
    5. Wrong Answer
  3. Answers
    1. Right Answer
    2. Wrong Answer
    3. Wrong Answer
    4. Wrong Answer
    5. Wrong Answer

Example Database


Introduction
This is a simple Microsoft SQL Server database. The code has the ability to check the existence of the database. Besides creating the database, this code creates a table named Employees. The table contains a few columns, including a primary key.
After the table has been created, a few records are added to it.
-- =============================================
-- Database: Exercise
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
 SELECT name 
  FROM sys.databases 
  WHERE name = N'Exercise'
)
DROP DATABASE Exercise
GO
CREATE DATABASE Exercise
GO
-- =========================================
-- Table: Employees
-- =========================================
USE Exercise
GO

IF OBJECT_ID('Employees', 'U') IS NOT NULL
  DROP TABLE Employees
GO

CREATE TABLE dbo.Employees
(
    EmployeeID int Unique Identity(1,1) NOT NULL, 
    EmployeeNumber integer NOT NULL, 
    FirstName varchar(50) NULL,
    LastName varchar(50) NOT NULL,
    HourlySalary decimal(6, 2) 
    CONSTRAINT PKEmployees PRIMARY KEY (EmployeeID)
)
GO

INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(92935, 'Joan', 'Hamilton', 22.50)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(22940, 'Peter', 'Malley', 14.25)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(27495, 'Christine', 'Fink', 32.05)
GO
INSERT INTO Employees(EmployeeNumber, FirstName, 
        LastName, HourlySalary)
VALUES(50026, 'Leonie', 'Crants', 18.75)
GO

Friday 20 July 2012

How to implement twitter search in asp.net

In this post,I will show you how to implement twitter search in asp.net.
In this project,I am using following things




<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Twitter.aspx.cs" Inherits="Twitter" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        body
        {
            width: 600px;
            margin: auto;
        }
        .tweets
        {
            list-style-type: none;
        }
        img
        {
            float: left;
            padding-right: 1em;
        }
        a
        {
            text-decoration: none;
        }
    </style>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js" type="text/javascript"></script>
    <script src="Scripts/Handlebars.js" type="text/javascript"></script>
    <script type="text/javascript">
        var Twitter =
         {
             init: function () {
                 this.url = "http://search.twitter.com/search.json?q='" + $("#txtSearch").val() + "'&callback=?";
                 this.template = $("#template").html();
                 this.fetch();
             },
             fetch: function () {
                 var self = this;
                 $.getJSON(this.url, function (data) {
                     self.tweets = $.map(data.results, function (tweet) {
                         return {
                             author: tweet.from_user,
                             tweet: tweet.text,
                             thumb: tweet.profile_image_url,
                             url: 'http://twitter.com/' + tweet.from_user + '/status/' + tweet.id_str
                         };
                     });
                     self.parse();
                 });
             },

             parse: function () {
                 var template = Handlebars.compile(this.template);
                 var container = $(".tweets");
                 container.append(template(this.tweets));

             }

         };
        $(document).ready(function () {

            $("#btnSearch").click(function () {
                Twitter.init();
            });
        });
    
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <input type="text" id="txtSearch" /><br />
        <input type="button" id="btnSearch" value="Search" />
        <ul class="tweets">
            <script type="text/x-handlebars-template" id="template">
            {{#each this}}
            <li>
            <img src="{{thumb}}" alt="{{author}}"></img>
            <p><a href="{{url}}"> {{tweet}} </a></p>
            </li>
            {{/each}}
       
            </script>
        </ul>
    </div>
    </form>
</body>
</html>

Saturday 14 July 2012

sql server interview questions

1. Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.
2. What are the difference between clustered and a non-clustered index?
  1. 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.
  2. 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.
3. What are the different index configurations a table can have?
A table can have one of the following index configurations:
  1. No indexes
  2. A clustered index
  3. A clustered index and many nonclustered indexes
  4. A nonclustered index
  5. Many nonclustered indexes
4. What are different types of Collation Sensitivity?
  1. Case sensitivity - A and a, B and b, etc.
  2. Accent sensitivity
  3. Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
  4. Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.
5. What is OLTP (Online Transaction Processing)?
In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.
6. What's the difference between a primary key and a unique key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.