Serving Information Simply
Friday, 27 July 2012
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...........................
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
Practical
Learning: Introducing Indexes
.
This would open the Index dialog box:
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.
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.
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
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)
|
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?
- A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
- A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
3. What are the different index configurations a table can have?
A table can have one of the following index configurations:
- No indexes
- A clustered index
- A clustered index and many nonclustered indexes
- A nonclustered index
- Many nonclustered indexes
4. What are different types of Collation Sensitivity?
- Case sensitivity - A and a, B and b, etc.
- Accent sensitivity
- Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
- 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.
Subscribe to:
Posts (Atom)