An Idea can change your life.....

Wednesday, October 28, 2009

What are the difference between clustered and a non‐clustered index?


SQL Server data structures


Pages

In SQL Server, data is organized in pages. A page has a fixed size (8 KB). Each page contains records. The number of records that can be stored in a page depends on the size of the records. The operation of reading data from a page is called a logical IO. The smaller the size of a record is, the more records can be read with the same number of logical IOs.
A page can be identified by a 6 bytes page pointer: 2 bytes for the file ID and 4 bytes for the page number. A row in a page can be identified by an 8 bytes row ID: 2 bytes for the file ID, 4 bytes for the page number and 2 bytes for the row number.
There are two types of non system pages: data pages, composed of data rows, and index pages, composed of index rows.

Structure of a table: heaps and clustered tables

There are only two types of tables in SQL Server: heaps and clustered tables.

Heaps

Heaps are tables that have no clustered index. They are entirely composed of data pages. The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.
Heap
A heap.

Clustered tables

A clustered table is a table whose rows are kept in sorted order. The clustered index is the structure that stores and maintains the rows of a clustered table in sorted order. The rows are ordered using the clustering key, which is defined by one or more columns of the table. Like in a heap, the data rows are stored in data pages, and the clustered index additionally uses index pages to navigate in the data.
SQL Server organizes indexes as trees, with one page at the root level, multiple pages at the leaf level, and zero or more levels in between.

  • Leaf level:
    The leaf level is the data of the table. It is composed of data pages. They store all the columns of the table for every row of the table.


  • Non-leaf levels:
    The non-leaf levels are composed of index pages.
    At every non-leaf level, each index row corresponds to a page in the next level. Each index row in every index page contains two things: the index key value, which is the first key value of the corresponding page in the next level of the index, and a 6 bytes page pointer to that corresponding page.

At each level (including leaf level), the pages have a pointer to the next and the previous page in the same level, so that each level forms a doubly-linked list. At each level, the pages and the rows within a page are ordered using the clustering key.

Heap
A clustered index.
Blue pages are index pages and green pages are data pages.

Nonclustered indexes

Nonclustered indexes are always associated with a table (it can be either a heap of a clustered table), but they have a structure completely separate from the data rows. Nonclustered indexes only contain a partial copy of the data of the associated table, sorted in a different order than the actual data. The rows are ordered using the nonclustered key, which is defined by one or more columns of the associated table. There can be multiple nonclustered indexes per table.
Nonclustered indexes have the same B-tree structure as clustered indexes, however the leaf level is different.

  • Leaf level:
    In a nonclustered index, the leaf level is composed of index pages instead of data pages. Each index row in the leaf level of a nonclustered index contains a nonclustered key value along with a row locator that points to the corresponding data row in the actual table structure (heap or clustered table).
    If the associated table is a heap, the row locator is an 8 bytes row ID. If the table is a clustered table, the row locator is the clustering key identifying the row.


  • Non-leaf levels:
    The non-leaf levels of a nonclustered index are used for navigation identically as in a clustered index.

At each level of the nonclustered index, the pages and the rows within a page are ordered using the nonclustered key.

Heap
A nonclustered index for the clustered table above.
Heap
A nonclustered index for the heap above.

Covered columns

The columns stored at the leaf of an index are called the columns covered by this index.
A clustered index always covers all the columns.
A nonclustered index covers all the columns of its non clustering key. If it is a nonclustered index on a clustered table, it also covers the columns of the clustering key of the table, since they constitute the row locators.

Clustered indexes are created by default to match the primary key, which is defined on tables in SQL Server. However, you can create a clustered index on any column and then define a primary key on a separate column or columns. At this point, the primary key would be created as a unique non-clustered index. Typically, a clustered index will match the primary key, but not necessarily, so be careful. Given the variety of situations that can arise, I'll be discussing the clustered indexes themselves, and for now ignore whether you choose to make them primary keys.


 different index configurations a table can have?
  • No indexes
  • A clustered index 
  • A clustered index and many nonclustered indexes
  • A nonclustered index
  • Many nonclustered indexes

CREATE [UNIQUE] [ CLUSTERED | NONCLUSTERED ]
INDEX index_name

ON table (column [ASC
DESC] [ ,...n ] )

[WITH {IGNORE_DUP_KEY
DROP_EXISTING
SORT_IN_TEMPDB}]

[ON filegroup ]

 


CREATE TABLE dummytable1 (

  empid   INT,

  empname VARCHAR(700))


INSERT INTO dummytable1

VALUES     (4,

            Replicate('d',700))


GO


INSERT INTO dummytable1

VALUES     (6,

            Replicate('f',700))


GO


INSERT INTO dummytable1

VALUES     (1,

            Replicate('a',700))


GO


INSERT INTO dummytable1

VALUES     (3,

            Replicate('c',700))


GO


SELECT empid

FROM   dummytable1


GO


CREATE CLUSTERED INDEX ix_dummytable ON dbo.dummytable1 (

      empid)


SELECT empid

FROM   dummytable1


GO


CREATE NONCLUSTERED INDEX ix_dummytable1 ON dbo.dummytable1 (

      empname)


GO


SELECT *

FROM   dummytable1

WHERE  empname LIKE '%a%'

       AND empid = 1


GO
The Clustered/Non Clustered index can be enabled disabled


primary key creates a clustered index on the column, where as unique key creates a nonclustered index by default.

Friday, October 02, 2009

WordAhead Vocabulary Builder


 





Trace Indian Mobile Location & Operator

IndianMobile

Searches across Indian mobile number details...
Updated Regularly

This is a good websit. you can know some phone no. Location & Operator

Self-Massage for Athletes

selfmassageforathletes
Massage away muscle pain and soreness
Turn your hands into powerful pain relievers. Are you aching in the places where you used to play? Join the revolution and feel better fast

Friday, September 11, 2009

Watch Vocabular Videos Studying vocabulary or just want to improve your vocabulary? This ‘video dictionary’ is indispensable!

WordAhead Study Room provides a distraction free environment with over 787 Vocabulary Videos and Flash Cards. More vocab videos continuously added. Join us, its FREE!Studying vocabulary or just want to improve your vocabulary? This ‘video dictionary’ is indispensable!
http://www.wordahead.com/WordList/tabid/71/Default.aspx

Tuesday, September 01, 2009

OpenDns Stay Secured

Make your network safer, faster, smarter, and more reliable.

start
OpenDNS Server Ip Address

208.67.222.222 and 208.67.220.220.

Saturday, August 08, 2009

Effective Communication

Dealing with any clients in services is an art.

In the case of IT, we primarily deal with American and UK clients. It is useful to know how English language works with them.

Some of us may hesitate to speak to the client, because we are not confident. ‘Practice makes you confident’

“Practice these tips…….”

PowerPoint

Money Kumar The Monetary Policy

Money Kumar

The Monetary Policy

PDF

Managing Anger PPT

Managing Anger PPT


Thought it might help each of us know how to manage anger.

PowerPoint

Bell the Cat.... Click on the link

Try this out...very funny..............

http://www.members.shaw.ca/gf3/circle-the-cat.html

Incredible!!!New initiative of Govt of India ............ Very Informative - read, save & pass on

Government of India has an online Grievance forum at http://www.pgportal.gov.in

Can you imagine this is happening in INDIA ?

The govt. wants people to use this tool to highlight the problems they faced while dealing with Government officials or departments like
(all hyperlinks given below links to respective dept/office site)

1) Railways

2) Posts

3) Telecom (incl. Bharat Sanchar Nigam Limited (BSNL) & Mahanagar Telephone Nigam Limited (MTNL)

4) Urban Development (Delhi Development Authority (DDA), Land & Development Office (L&DO), Central Public Works Department (CPWD), etc)

5) Petroleum & Natural Gas

6) Civil Aviation (Air India, Airports Authority of India, etc)

7) Shipping, Road Transport & Highways

8) Tourism

9) Public Sector Banks
Allahabad Bank
Andhra Bank Bank of Baroda Bank of India Bank of Maharashtra Canara Bank Central Bank of India Corporation Bank Dena Bank Indian Bank Indian Overseas Bank Industrial Development Bank of India Ltd National Bank for Agriculture and Rural Development Oriental Bank of Commerce Punjab & Sind Bank Punjab National Bank Small Industries Development Bank of India State Bank of Bikaner & Jaipur State Bank of Hyderabad State Bank of India State Bank of Indore State Bank of Mysore State Bank of Patiala State Bank of Travancore Syndicate Bank UCO Bank Union Bank of India United Bank of India Vijaya Bank

10) Public Sector Insurance Companies
GIC of India
Life Insurance Corporation of India National Insurance Company Ltd. The New India Assurance Company Ltd. The Oriental Insurance Company Ltd. United India Insurance Company Ltd.

11) National Saving Scheme of Ministry of Finance

12) Employees' Provident Fund Organization

13) Regional Passport Authorities
Regional Passport Office, Ahemadabad
Regional Passport Office, Amritsar Regional Passport Office, Bangalore Regional Passport Office, Bareilly Regional Passport Office, Bhopal Regional Passport Office, Bhubaneswar Regional Passport Office, Chandigarh Regional Passport Office, Chennai Regional Passport Office, Cochin Regional Passport Office, Coimbatore Regional Passport Office, Dehradun Regional Passport Office, Delhi Regional Passport Office, Ghaziabad Regional Passport Office, Goa Regional Passport Office, Guwahati Regional Passport Office, Hyderabad Regional Passport Office, Jaipur Regional Passport Office, Jalandhar Regional Passport Office, Jammu Regional Passport Office, Kolkata Regional Passport Office, Kozhikode Regional Passport Office, Lucknow Regional Passport Office, Madurai Regional Passport Office, Malappuram Regional Passport Office, Mumbai Regional Passport Office, Nagpur Regional Passport Office, Patna Regional Passport Office, Pune Regional Passport Office, Raipur Regional Passport Office, Ranchi Regional Passport Office, Shimla Regional Passport Office, Srinagar Regional Passport Office, Surat Regional Passport Office, Thane Regional Passport Office, Trichy Regional Passport Office, Trivandrum Regional Passport Office, Visakhapatnam

14) Central Government Health Scheme

15) Central Board of Secondary Education

16) Kendriya Vidyalaya Sangathan

17) National Institute of Open Schooling

18) Navodaya Vidyalaya Samiti

19) Central Universities

20) ESI Hospitals and Dispensaries directly controlled by ESI Corporation under Ministry of Labour


Many of us say that these things don't work in India .
Couple of months back, the Faridabad Municipal Corporation laid new roads in the area and the residents were very happy about it. But two weeks later, BSNL dug up the newly laid roads to install new cables which annoyed all the residents. A resident used the above listed grievance forum to highlight his concern. To his surprise, BSNL and Municipal Corporation of Faridabad were served a show cause notice and the guy received a copy of the notice in one week. Government has asked the FMC and BSNL about the goof up as it's clear that both the government departments were not in sync at all.

So use this grievance forum and educate others who don't know about this facility.


This way we can at least raise our concerns instead of just talking about the 'System' in India .

Invite your friends to contribute for many such happenings.


PLEASE SPREAD THIS MESSAGE IF U WANT OUR INDIA TO HAVE A BETTER TOMORROW

Surendrapuri - A Mythological Museum Near Hyderabad - Yadagirigutta...

Enjoy the scenes of Kunda Satyanarayana kaladhamam - Surendrapuri


A Mythological Museum ~~~ Near Hyderabad - Yadagirigutta...


PowerPoint

VERY IMP..!!! What is ICE??

We all carry our mobile phones with names & numbers stored in its memory but nobody, other than ourselves, knows which of these numbers belong to our closest family or friends.

If we were to be involved in an accident or were taken ill, the people attending us would have our mobile phone but wouldn't know who to call. Yes, there are hundreds of numbers stored but which one is the contact person in case of an emergency? Hence this "ICE" (In Case of Emergency) Campaign

The concept of "ICE" is catching on quickly. It is a method of contact during emergency situations. As cell phones are carried by the majority of the population, all you need to do is store the number of a contact person or persons who should be contacted during emergency under the name "ICE" (In Case Of Emergency).

The idea was thought up by a paramedic who found that when he went to the scenes of accidents, there were always mobile phones with patients, but they didn't know which number to call. He therefore thought that it would be a good idea if there was a nationally recognized name for this purpose. In an emergency situation, Emergency Service personnel and hospital Staff would be able to quickly contact the right person by simply dialing the number you have stored as "ICE."

For more than one contact name simply enter ICE1, ICE2 and ICE3 etc. A great idea that will make a difference!

Let's spread the concept of ICE by storing an ICE number in our Mobile phones today!

Remember:-
ICE will speak for you when you are not able to

Thursday, June 04, 2009

27 ways to fall in love with a girl !!

01.Tell her she is beautiful (not hot, fine, or sexy)
02.Hold her hand at any moment . . . even if its just for a second.
03.Kiss her on the forehead.
04.Leave her voice messages to wake up to.
05.Always tell her you love her every second of the day.
06.When she is upset, hold her tight and tell her how much she means to you.
07.Recognize the small things . . . they usually mean the most.
08.Sing to her no matter how horrible your voice is.
09.Pick her over all the other girls you hang out with.
10.Write her notes. (she loves them)
11.Introduce her to family and friends . . . as your girlfriend.
12.Play with her hair.
13.Pick her up, tickle her, and play-wrestle with her.
14.Sit in the park and just talk to her.
15.Tell her funny jokes, tell her stupid jokes, or just tell her jokes.
16.Throw pebbles at her window in the middle of the night.
17.Let her fall asleep in your arms.
18.Carve your names into a tree.
19.If she's mad at you, kiss her.
20.Give her piggyback rides.
21.Bring her flowers
22.Treat her the same around your friends as you do when your alone.
23.Look her in the eyes and smile.
24.Let her take as many pictures of you as she wants.
25.Slow dance with her, even if there isn't any music playing.
26.Kiss her in the rain.
27.If you are in love with her . . . tell her.

Friday, May 22, 2009

Software Downloads

An Ultimate solution to download the software you need

Check out the link below

http://ftp.freenet.de/pub/filepilot/windows/

Tuesday, April 14, 2009

Recycling - Nice






Buddha, one day, was in deep thought about the worldly activities and the ways of instilling goodness in human beings. One of his disciples approached him and said humbly "Oh my teacher! While you are so much concerned about the world and others, why don't you look in to the welfare and needs of your own disciples also?"

Buddha : "OK.. Tell me how I can help you"

Disciple : "Master! My attire is worn out and is beyond the decency to wear the same. Can I get a new one, please?"

Buddha found the robe indeed was in a bad condition and needed replacement. He asked the store keeper to give the disciple a new robe to wear on. The disciple thanked Buddha and retired to his room. A while later; he went to his disciple's place and asked him "Is your new attire comfortable? Do you need anything more?”

Disciple : "Thank you my Master. The attire is indeed very comfortable. I need nothing more"

Buddha : "Having got the new one, what did you do with your old attire?"

Disciple : "I am using it as my bed spread"

Buddha : "Then.. hope you have disposed off your old bed spread"

Disciple : " No.. no.. master. I am using my old bedspread as my window curtain"

Buddha : " What about your old Curtain?"

Disciple : "Being used to handle hot utensils in the kitchen"

Buddha : "Oh.. I see.. Can you tell me what did they do with the old cloth they used in Kitchen"

Disciple : "They are being used to wash the floor."

Buddha : " Then, the old rug being used to wash the floor...?"



Disciple: "Master, since they were torn off so much, we could not find any better use, but to use as a twig in the oil lamp, which is right now lit in your study room."

Buddha smiled in contentment and left for his room.

If not to this degree of utilization, can we at least attempt to find the best use of all our resources at home and in office? We need to handle wisely, all the resources earth has bestowed us with ….both natural and material so that they can be saved for the generations to come. Reserve Consumption !!!

Monday, April 13, 2009

DILBERT BY SCOTT ADAMS

THE FERN AND THE BAMBOO




ONE DAY I DECIDED TO QUIT...

I QUIT MY JOB, MY RELATIONSHIP, MY SPIRITUALITY....

I WANTED TO QUIT MY LIFE.

I WENT TO THE WOODS TO HAVE ONE LAST TALK WITH GOD.


"God", I said. "Can you give me one good reason not to quit?"


His answer surprised me.



"Look around", He said. "Do you see the fern and the bamboo?"


"Yes", I replied.


When I planted the fern and the bamboo seeds, I took very good care of them. I gave them light. I gave them water. The fern quickly grew from the earth. Its brilliant green covered the
floor. Yet nothing came from the bamboo seed. But I did not quit on the bamboo."


In the second year the fern grew more vibrant and plentiful. And again, nothing came from the bamboo seed. But I did not quit on the bamboo."


In year three there was still nothing from the bamboo seed. But I would not quit. The same in year four.

Then in the fifth year, a tiny sprout emerged from the earth. Compared to the fern, it was seemingly small and insignificant.


But just six months later, the bamboo rose to over 100 feet tall.


It had spent the five years growing roots. Those roots made it strong and gave it what it needed to survive. I would not give any of my creations a challenge it could not handle.


Did you know, my child, that all this time you have been struggling, you have actually been growing roots? I would not quit on the bamboo.. I will never quit on you.


"Don't compare yourself to others." He said. "The bamboo had a different purpose than the fern. Yet they both make the forest beautiful.


"Your time will come", God said to me.. "You will rise high"


"How high should I rise?" I asked.


"How high will the bamboo rise?" He asked in return.


"As high as it can?" I questioned


"Yes." He said,

"Give me glory by rising as high as you can."


I left the forest, realizing that God will never give up on me.


And He will never give up on you.


NEVER REGRET A DAY IN YOUR LIFE.

GOOD DAYS GIVE YOU HAPPINESS; BAD DAYS GIVE YOU EXPERIENCES.

BOTH ARE ESSENTIAL TO LIFE.


SO ENJOY EVERY MOMENT OF LIFE……………


Continuous efforts, not strength or intelligence is the key to unlocking our potential.

Wednesday, April 08, 2009

THE MAYONNAISE JAR

PLEASE READ TO FIND THE MORAL OF THE STORY


The Mayonnaise Jar

When things in your life seem, Almost too much to handle,When 24 Hours in a day is not enough,Remember the mayonnaise jar and 2 cups of coffee.

A professor stood before his philosophy class And had some items in front of him.When the class began, wordlessly,He picked up a very large and empty mayonnaise jarAnd proceeded to fill it with golf balls.

He then asked the students, If the jar was full. They agreed that it was.

The professor then picked up a box of pebbles and pouredthem into the jar. He shook the jar lightly.The pebbles rolled into the open Areas between the golf balls.

He then asked The students againIf the jar was full.. They agreed it was.

The professor next picked up a box of sand And poured it into the jar. Of course, the sand filled up everything else.He asked once more if the jar was full. The students respondedWith an unanimous 'yes.'

The professor then produced Two cups of coffee from under the tableAnd poured the entire contents Into the jar, effectivelyFilling the Empty space between the sand.The students laughed.

'Now,' said the professor, As the laughter subsided,'I want you to recognize that This jar represents your life.The golf balls are the important things - God, family,children, health, Friends, and Favorite passions – Things that if everything else was lost And only they remained, Your life would still be full.

The pebbles are the other things that matter Like your job, house, and car.

The sand is everything else --The small stuff.

'If you put the sand into the jar first,' He continued,'there is no room for The pebbles or the golf balls.The same goes for life.

If you spend all your time And energy on the small stuff,You will never have room for The things that areImportant to you.

So...

Pay attention to the things That are critical to your happiness.Play With your children.Take time to get medical checkups.Take your partner out to dinner.

There will always be time To clean the house and fix the disposal.

'Take care of the golf balls first --The things that really matter.Set your priorities. The rest is just sand.'

One of the students raised her handAnd inquired what the coffee represented.

The professor smiled.'I'm glad you asked'.

It just goes to show you that no matter how full your life may seem,there's always room for A couple of cups of coffee with a friend.'

If it's true that we are here to help others, then what exactly are the others here for ?