Thursday, August 2, 2007

Table variable vs Temporary Table

Table variable vs Temporary Table

Where to use Table Variable? and Where to use Temporary Table?

Most of the time these questions are arised when we handle a table of data inside the Stored Procedures.
When we go for Table variable...there is one more question..., Why they introduce Table variable when Temporary Table already exists?.......!!!

I googled these and got some answers...

Why were table variables introduced when temporary tables were already available?

Table variables have the following advantages over temporary tables:
  • As mentioned in the SQL Server Books Online "Tables" article, table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
  • Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
  • Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.

What are some of the drawbacks of table variables?

These are some of the drawbacks as compared to temporary tables:
  • Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.
  • Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
  • The table definition cannot be changed after the initial DECLARE statement.
  • Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
  • CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
  • You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure.

Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Do I have to use table variables instead of temporary tables?

The answer depends on these three factors:
  • The number of rows that are inserted to the table.
  • The number of recompilations the query is saved from.
  • The type of queries and their dependency on indexes and statistics for performance. In some situations, breaking a stored procedure with temporary tables into smaller stored procedures so that recompilation takes place on smaller units is helpful.
    In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure.

    http://support.microsoft.com/kb/305977/en-us
    http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html

Saturday, July 7, 2007

படித்ததில் பிடித்தது..........

காதல் என்பது பலவிதம்.


To avoid font problem read this post in Internet Explorer.

காதல் என்பது பலவிதம்.
விவரம் தெரியாப் பள்ளிப் பருவக் காதல் முதல்,
விவரத்துடன் வேலையையும் சம்பளத்தையும்
கணக்குப் போட்டு செய்யும் காதல் வரை
காதல் என்பது பலவிதம்.

நாம் இங்கு பார்க்கப் போவது,
ஒரு மாதிரியான ஒருதலைக் காதல்.
அந்த வாலிபனின் மனதில் ஒரே சந்தோஷம்.

சற்று முன்னர்தான், அவன் எதிரே,
அவள் ஒரு சிறு புன்னகையை
உதிர்த்து விட்டுச் சென்றாள்.
சிறிது நாட்களாகவே,
அவன் அவளை கவனித்து வருகிறான்.
அவள் அவனிடம் ஏதொ ஒன்றை
சொல்ல முயல்வதாக அவனுக்குள் எண்ணம்.
அவர்கள் இருவருக்கிடையே
ஏதோ ஒன்று இழைந்தோடுவதாய்
அவன் நம்ப ஆரம்பித்தான்.

அவன் இயல்பாகவே ஒரு மென்மையானவன்.
அதிலும் இந்த வாலிப வயதில்
அவள் பார்த்து சிரிக்கும் போது,
அவனது மென்மை, மென்மையிலும் மென்மையானது.
அவன் யோசிக்க ஆரம்பித்தான்.

அவள் ஏன் தன்னைப் பார்த்து சிரிக்க வேண்டும் ?
யோசிக்க ஆரம்பித்தான்.
ஒருவேளை காதலாக இருக்குமோ ?
யோசிக்க ஆரம்பித்தான்.
எதற்கும், தன் அறை நண்பனைக் கேட்டு விடலாம்
என முடிவு செய்தான்.

'மாப்ளே.. ஒரு விஷயம்.. '
அவன் மிக ஆவலாய் அறைக்குள் ஓடி வந்தான்.
படித்துக் கொண்டிருந்த புத்தகத்திலிருந்து

தலையைத் திருப்பி அவனது நண்பன்,
'என்னடா ஆச்சு ? '
'மாப்ளே.. இன்னிக்கு அவ என்னை

ஒரு மாதிரியா பாத்து சிரிச்சுட்டு போனாடா.. '
'எவடா ? '
'அவதான். ரேணு!! '
'ஓ... ரேணுவா ? இருக்கும் இருக்கும்.

நீதான் ஆளு பாக்குறதுக்கு சூப்பரா இருக்கே.
I think she is in love with you.
நான் கூட கொஞ்ச நாளா நோட் பண்ணேன்.
அவ ஒன்னை ஒரு மாதிரியாப்
பாத்துக்கிட்டே இருந்தா.
' எதுவுமே தெரியாவிட்டாலும், தெரிந்தது போல்
கூறினான் நண்பன்.
'ஓ... நீ கூட நோட் பண்ணியா ? '

அவன் முகத்தில் ஒரு சந்தோஷம் பிரதிபலித்தது.
'ஆமாமா. enjoy பண்ணு மச்சி ! '
'என்னடா செய்யலாம் அடுத்து ? '

அப்பாவியாய் கேட்டான் அவன்.
'ஒரு மாதிரியா கெத்தா maintain பண்ணுடா.

கொஞ்சம் விட்டுப் புடி, ஒனக்குத் தெரியாதா ?
அடுத்து என்ன செய்யனும்னு ! '
அவன் நாளொருமேனியும்,

பொழுதொரு வண்ணமுமாய் வலம் வர ஆரம்பித்தான்,
அவ்வப்போது, நண்பனிடம் ஆலோசனை கேட்டுக் கொண்டபடி.
கனவுலகில், காதல் தேசத்தில் அவளுடன் உலா வந்தபடி.


'மாப்ளே, நான் போயி பேசட்டுமாடா ? '
'Wait பண்ணு மச்சி. நீயா போயி பேசினா, உனக்கு value இருக்காது. '
'சரிடா. '

ஒவ்வொரு நாளும் அவளின் முன் தோன்றி,
அவளது உணர்வுகளை கணிக்கலானான்.
தன் உருவம் அவள் மனதை சந்தோஷமடையச்
செய்வதாய் நம்பத் தொடங்கினான்.
சிறிது நாட்களில், அவனுக்கே ஒரு எண்ணம்.

தான் போய் பேசாததால், அவள் வருத்தமாய் இருப்பதாய்.
அவன் அவளிடம் போய், 'ஏன் டல்லாயிருக்கே ? '
'ஒன்னுமில்லேயா. நான் நார்மலாதான் இருக்கேன். '
'இல்லே, நீ டல்லாதான் இருக்கே. என் கிட்டே

எதுவும் பேசனுமா ? வா கேண்டான் போகலாம் '
'I am alright. Nothing is wrong with me. '
அடுத்த நாள், மறுபடியும் 'என் கிட்டே

எதுவும் பேசனுமா ? வா கேண்டான் போகலாம் '
'ஒன்னுமில்லேயா ' அவள் திரும்பிச் சென்றாள்.
அவள் யோசிக்க ஆரம்பித்தாள்.

அவன் ஏன் தன்னைப் பார்த்து இப்படி கேட்க வேண்டும் ?
யோசிக்க ஆரம்பித்தாள்.
ஒருவேளை கிறுக்கனாய் இருப்பானோ ?
யோசிக்க ஆரம்பித்தாள்.
எதற்கும் தன் வகுப்பு
நண்பர்களைக் கேட்டு விடலாம் என முடிவு செய்தாள்.
வகுப்பிற்குச் சென்றவுடன், சக மாணவர்களிடம் கேட்டாள்

'ஏன்யா, அவன் சுந்தர் ஒரு மாதிரியா பேசிண்டுருக்கான் ? '
சிலர் உடனே, 'அவன் ஒரு மாதிரிம்மா.

சரியான ஊரு. girls யாரும் சிரிச்சா, பேசினா,
உடனே love பண்றதா நெனச்சிடுவான். '
'ஓ! அவன்கிட்டே சொல்லுங்கப்பா.

அந்த மாதிரியெல்லாம் ஒன்னும் கெடையாதுன்னு. '
நண்பர்கள் அவனிடம் விவரத்தைக் கூறியவுடன்,

அவன் சிறிது சோர்வடைந்தாலும்,
தன்னைத்தானே தேற்றிக்கொண்டு,
அடுத்த வேலையைப் பார்க்கலானான்.



படிப்பு முடிந்து, வேலையில் சேர்ந்து,
ஒரிரு வருடங்களுக்குப் பின் சந்தித்த போது
வேறிய விஷயங்கள் பேசியதற்கிடையே,
அவன் ஆவலாய் கூறினான்.

'மாப்ளே.. ஒரு விஷயம்.. '
'என்னடா ஆச்சு ? '
'என்னோட வீட்டுக்கு எதிர் வீட்டுலே

ஒரு பொன்னு இருக்காடா.
அவ என்னை ஒரு மாதிரியா பாத்து, சிரிச்சுகிட்டே இருக்காடா.. '
'இருக்கும் இருக்கும்.

நீதான் ஆளு பாக்குறதுக்கு சூப்பரா இருக்கே.
I think she is in love with you. '

To avoid font problem read this post in Internet Explorer.

*** -பாரி பூபாலன்
நன்றி : Thinnai.com
Thinnai 2000 March 5 ,Copyright:Thinnai.com 

http://www.thinnai.com/?module=displaystory&story_id=30003051&format=html

Friday, July 6, 2007

Global Warming

Global Warming” this is the word we hear again and again.

Yes all over the world people are talking about Global warming.

What’s this Global warming?

What it Cause?

Why we fear about that?

Is that really dangerous, How to believe?

How to avoid ?

What we must do? …….


More questions arise like this……

What’s this Global warming?

Global warming is the gradual increase in global temperatures caused by the emission of gases that trap the sun's heat in the Earth's atmosphere. Gases that contribute to global warming include carbon dioxide, methane, nitrous oxides, chlorofluorocarbons (CFCs), and halocarbons (the replacements for CFCs). The carbon dioxide emissions are primarily caused by the use of fossil fuels for energy.

www.airdfw.com/heating-glossary.htm


What it Cause?

In the case of warming by the addition of long-lived greenhouse gases such as CO2, the initial warming by these gases will cause more water to be evaporated into the atmosphere. Since water vapor itself acts as a greenhouse gas, this causes still more warming; the warming causes more water vapor to be evaporated, and so forth until a new dynamic equilibrium concentration of water vapor is reached with a much larger greenhouse effect than that due to CO2 alone.

When global temperatures increase, ice near the poles melt at an increasing rate. As the ice melts, land or open water takes its place. Both land and open water are on average less reflective than ice, and thus absorb more solar radiation. This causes more warming, which in turn causes more melting, and this cycle continues.
http://en.wikipedia.org/wiki/Global_warming#Causes

Why we fear about that?

Some effects on both the
natural environment and human life are,

sea level rise,

changes in rainfall patterns,

frequency of
hurricanes and extreme weather events

it is difficult to attribute specific events to global warming.

Other expected effects include

water scarcity in some regions and increased precipitation in others,

changes in mountain snowpack,

adverse health effects from warmer temperatures.

spread of diseases such as malaria and dengue fever

One study predicts 18% to 35% of a sample of 1,103 animal and plant species would be
extinct by 2050

Two populations of
Bay checkerspot butterfly are being threatened by changes in precipitation, though few mechanistic studies have documented extinctions due to recent climate change.

Is that really dangerous, How to believe?

Yes, Here are the Samples in various Regions,

Africa

Lake Chad
The surface area of the lake has decreased from 9,650 square miles (25,000 km2) in 1963 to 521 (1,350 km2) today.
Senegal
Sea level rise. Sea level rise is causing the loss of coastal land at Rufisque, on the South Coast of Senegal.
Kenya
2001 ? Worst drought in 60 years. Over four million people were affected by a severely reduced harvest, weakened livestock, and poor sanitary conditions.
Tanzania
Malaria expands in mountains. Higher annual temperatures in the Usamabara Mountains have been linked to expanding malaria transmission.

Antarctica


Antarctic Peninsula -- Warming 5 times global average. Since 1945, the Antarctic Peninsula has experienced a warming of about 4.5?F (2.5?C). The annual melt season has increased by 2 to 3 weeks in just the past 20 years.
Antarctica -- Ice shelf breakup. After 400 years of relative stability, nearly 1,150 square miles (2,978 km2) of the Larson B and Wilkins ice shelves collapsed between March 1998 and March 1999.
Antarctica - Decreasing Ice-thickness. The permanent ice cover of nine lakes on Signey Island has decreased by about 45% since the 1950s. Average summer air temperature has warmed by 1.8?F (1?C).
Antarctica -- Penguin population decline. Adelie Penguin populations have shrunk by 33% during the past 25 years in response to declines in their winter sea ice habitat.

Asia


Mt. Everest
The Khumbu Glacier, popular climbing route to the summit of Mt. Everest, has retreated over 3 miles (5 km) since 1953. The Himalayan region overall has warmed by about 1.8?F (1?C) since the 1970s.

Southern India
May 2002-In the state of Andhra Pradesh temperatures rose to 120?F (48.9?C), resulting in the highest one-week death toll on record. This heat wave came in the context of a long-term warming trend in Asia in general. India, including southern India, has experienced a warming trend at a rate of 1?F (0.6?C) per century.

Pakistan
1999-2001 ? Longest drought on record. The prolonged three-year drought, which covers much of South West Asia, has affected 2.2 million people and 16 million livestock in Pakistan.

China
2001 ? More than half of the 4,000 lakes in the Qinghai province are disappearing due to drought. The severity of the impact is exacerbated by overpumping of aquifers. Annual average temperature in China has increased during the past century, with pronounced warming since 1980. Most of the warming has been in northern areas, including Qinghai Province, and in the winter.

Indonesia
Burning rainforest, 1998. Fires burned up to 2 million acres (809,371 hectares) of land, including almost 250,000 acres of primary forest and parts of the already severely reduced habitat of the Kalimantan orangutan.

Korea
Heavy rains and flooding. Severe flooding struck during July and August, 1998, with daily rainfall totals exceeding 10 inches (25.4 cm).

America


Monteverde Cloud Forest, Costa Rica
Disappearing frogs and toads. A reduction in dry-season mists due to warmer Pacific ocean temperatures has been linked to disappearances of 20 species of frogs and toads, upward shifts in the ranges of mountain birds, and declines in lizard population.


Venezuela
December 1999 ? The heaviest rainfall in 100 years caused massive landslides and flooding that killed approximately 30,000 people. Total December rainfall in Maiquetia, near Caracas, was almost 4 feet (1.2 m), more than 5 times the previous December record. The high death toll was attributed to population growth in vulnerable areas and forest clearing on steep hill slopes.

Florida
Worst wildfires in 50 years, 1998. Fires burned 485,000 acres (196,272 hectares) and destroyed more than 300 homes and structures.

Bermuda
Dying mangroves. Rising sea level is leading to saltwater inundation of coastal mangrove forests.

Black Hills, South Dakota
Record snowfall, 1998. At the end of February, the Black Hills received 102.4 inches (260 cm) of snow in five days, almost twice as much snow as the previous single-storm record for the state.


Bering Sea
Reduced sea ice. Sea-ice extent has shrunk by about 5 percent over the past 40 years.

Europe


Central England -- Cold days declining, hot days increasing, 1772 to present. 1995 brought 26 days above 68?F (20?C) versus an average of 4 days per year since 1772.

Southeastern Norway
The year 2000 was the wettest year since records began in 1895. Precipitation in northern Europe has increased 10-40% in the last century.

United Kingdom
Birds shift northward. Over a 20-year period, many birds have extended the northern margins of their ranges by an average of about 12 miles (19 km).

Mediterranean
Intense drought and fires. Spain lost more than 1.2 million acres (485.622.8 hectares) of forest to wildfires in 1994, and 370,000 acres (149,734 hectares) burned in each of Greece and Italy in 1998.


Australia


New South Wales, Australia
Wettest August on record, 1998. On August 15-17, a storm dumped nearly 12 inches (30.5 cm) of rain on Sydney, over 8 inches (20.3 cm) more than what normally falls during that entire month.


New Zealand -- Retreating glaciers. The average elevation for glaciers in the Southern Alps has shifted upslope by more than 300 feet (91.4 m) over the past century.
Fiji -- Sea-level rise. Reports from local inhabitants at 16 sites indicate that the island's average shoreline has been receding half a foot (0.15 m) per year over at least the past 90 years.

How to avoid?


Using energy more efficiently and moving to renewable energy (wind, solar, geothermal, and bioenergy) would significantly reduce our emissions of heat-trapping gases.
Cars and trucks are another significant source (25 percent) of carbon dioxide emissions.
A serious effort to address global warming must therefore reduce emissions from cars and trucks.

By putting energy efficiency, renewable energy, and vehicle technology solutions in place at the federal level, we can reduce our contribution to global warming while creating a stronger, healthier, and more secure nation.

What we must do?


The car you drive: the most important personal climate decision.When you buy your next car, look for the one with the best fuel economy in its class. Each gallon of gas you use is responsible for 25 pounds of heat-trapping gases in the atmosphere. Better gas mileage not only reduces global warming, but will also save you thousands of dollars at the pump over the life of the vehicle. Compare the fuel economy of the cars you're considering and look for new technologies like hybrid engines.


Choose clean power.

More than half the electricity in the United States comes from polluting coal-fired power plants. And power plants are the single largest source of heat-trapping gas. None of us can live without electricity, but in some states, you can switch to electricity companies that provide 50 to 100 percent renewable energy.

Look for Energy Star.
When it comes time to replace appliances, look for the Energy Star label on new appliances (refrigerators, freezers, furnaces, air conditioners, and water heaters use the most energy). These items may cost a bit more initially, but the energy savings will pay back the extra investment within a couple of years. Household energy savings really can make a difference: If each household in the United States replaced its existing appliances with the most efficient models available, we would save $15 billion in energy costs and eliminate 175 million tons of heat-trapping gases.


Unplug a freezer.

One of the quickest ways to reduce your global warming impact is to unplug the extra refrigerator or freezer you rarely use (except when you need it for holidays and parties). This can reduce the typical family's carbon dioxide emissions by nearly 10 percent.
Get a home energy audit.Take advantage of the free home energy audits offered by many utilities. Simple measures, such as installing a programmable thermostat to replace your old dial unit or sealing and insulating heating and cooling ducts, can each reduce a typical family's carbon dioxide emissions by about 5 percent.


Light bulbs matter.

If every household in the United States replaced one regular light bulb with an energy-saving model, we could reduce global warming pollution by more than 90 billion pounds over the life of the bulbs; the same as taking 6.3 million cars off the road. So, replace your incandescent bulbs with more efficient compact fluorescents, which now come in all shapes and sizes. You'll be doing your share to cut back on heat-trapping pollution and you'll save money on your electric bills and light bulbs.


Think before you drive.

If you own more than one vehicle, use the less fuel-efficient one only when you can fill it with passengers. Driving a full minivan may be kinder to the environment than two midsize cars. Whenever possible, join a carpool or take mass transit.
Buy good wood.When buying wood products, check for labels that indicate the source of the timber. Supporting forests that are managed in a sustainable fashion makes sense for biodiversity, and it may make sense for the climate too. Forests that are well managed are more likely to store carbon effectively because more trees are left standing and carbon-storing soils are less disturbed.


Plant a tree.

You can also make a difference in your own backyard. Get a group in your neighborhood together and contact your local arborist or urban forester about planting trees on private property and public land. In addition to storing carbon, trees planted in and around urban areas and residences can provide much-needed shade in the summer, reducing energy bills and fossil fuel use.

http://www.ucsusa.org/global_warming/solutions/ten-personal-solutions.html

Wednesday, July 4, 2007

How to format float numbers in C#.NET and SQL

Hey

Here is the tip to Formatting Float numbers and strings in C#.NET and SQL, Its useful those who are all handling numbers


double Doublevalue = 1234.5667;
Response.Write("Sandard Format :" + Doublevalue);
Response.Write("Currency Format :" + Doublevalue.ToString("C"));
Response.Write("ExponentialFormat :" + Doublevalue.ToString("E") );
Response.Write("Fixed point Format :" + Doublevalue.ToString("F3"));
Response.Write("Number Format to 2 Decimal places :" + Doublevalue.ToString("N2") );
Response.Write("Number Format to 3 Decimal places :" + Doublevalue.ToString("N3") );
Response.Write("General Format :" + Doublevalue.ToString("G") );
Response.Write("Percentage Format :" + Doublevalue.ToString("P1") );



When you bind data use the following in aspx page.

<%#Eval("Doublevalue","{0:F2}")%>

# Eval("AMOUNT","{0:F2}")

to know more on numbers and string formats,


http://www.codeproject.com/books/0672325802.asp

In SQL,

Use the ROUND function,


SELECT ROUND(123.4545, 2) Result ------> 123.4500

SELECT ROUND(123.45, -2) Result -------> 100.00

SELECT ROUND(150.75, 0) Result --------> 151.00

SELECT ROUND(150.75, 0, 1) Result --------> 150.00


ROUND(748.58, -4) Result ------> 0


ROUND(748.58, -1) Result -------> 750.00


ROUND(748.58, -2) Result --------> 700.00


ROUND(748.58, -3) Result ---------> 1000.00

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3) Result ----> 123.9990 124.0000