tag:blogger.com,1999:blog-75028723125758908712024-03-21T10:54:08.513-07:00Slacker DBAWelcome to this blog. This blog NO LONGER discusses SQL Server topics. Please visit http://sqlserver2008tutorial.com/ for SQL Topics. Thanks!Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.comBlogger25125tag:blogger.com,1999:blog-7502872312575890871.post-49329229547696730822021-11-22T18:49:00.001-08:002021-11-22T18:49:58.131-08:00IronMan 70.3 Waco Texas – Recap of Half IronMan triathlon Waco 2021<iframe width="480" height="270" src="https://youtube.com/embed/QUH1aC6Zt9Q" frameborder="0"></iframe>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-85640607748459666202014-05-15T19:19:00.001-07:002014-05-15T19:20:52.550-07:00Primary types of Glaucoma - Overview and Symptoms of Glaucoma<!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:DontVertAlignCellWithSp/>
<w:DontBreakConstrainedForcedTables/>
<w:DontVertAlignInTxbx/>
<w:Word11KerningPairs/>
<w:CachedColBalance/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><br />
<!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style>
<![endif]-->
<br />
<div class="MsoNormal">
A lot of people have heard of glaucoma but really don’t know
what it is, how you get it and what some of the symptoms are.<span style="mso-spacerun: yes;"> </span>Most people think it is an “Old Person”
disease that has something to do with the eyes.<span style="mso-spacerun: yes;">
</span>Though it is true that Glaucoma strikes older Americans, it can also
strike children and carries symptoms that are far reaching from your eyes.</div>
<div class="MsoNormal">
To simplify it, Glaucoma is an eye disease that damages your
eyes optic nerve.<span style="mso-spacerun: yes;"> </span>An optic nerve <span class="st1"><span style="color: #444444; font-family: "Arial","sans-serif"; font-size: 10.0pt; line-height: 115%;">is located in the back of the eye</span></span>.<span style="mso-spacerun: yes;"> </span>It is also known as the cranial nerve.<span style="mso-spacerun: yes;"> </span>The optical nerve transmits visual
information from the retina to the brain.<span style="mso-spacerun: yes;"> </span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
There are different symptoms that people get when they get
glaucoma.<span style="mso-spacerun: yes;"> </span>Symptoms can vary depending on
what type of Glaucoma you have.<span style="mso-spacerun: yes;"> </span>There
are generally two primary types of Glaucoma:<span style="mso-spacerun: yes;">
</span>Open Angle and Angle Closure.<span style="mso-spacerun: yes;">
</span>Open Angle sneaks up without a lot of symptoms.<span style="mso-spacerun: yes;"> </span>For Open Angle glaucoma it is the loss of
side or peripheral vision.<span style="mso-spacerun: yes;"> </span>Since the
symptoms for this one are harder to recognize it is important to get checked
out every year.<span style="mso-spacerun: yes;"> </span>Just visit <a href="http://www.southcaleye.com/glaucoma/glaucoma-specialist-orange-county/" target="_blank">Orange county glaucoma specialist</a> today to get a checkup. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Open Angle Glaucoma is when the eye draining canals become
clogged over a long period of time.<span style="mso-spacerun: yes;"> </span>As
a result of this clog, a lot of pressure builds up to the optic nerve.<span style="mso-spacerun: yes;"> </span>It is very similar to when you have a clogged
pipe in your house.<span style="mso-spacerun: yes;"> </span>Ultimately there is
a lot of pressure that is pushing against the clog, but the drainage doesn’t
happen, causing a lot of pressure.<span style="mso-spacerun: yes;"> </span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
On the other hand, the symptoms of Angle Closure are very
apparent.<span style="mso-spacerun: yes;"> </span>Some of the symptoms include
the following, eye and headaches/pain, sight loss, nausea or vomiting, and hazy
or blurred vision.<span style="mso-spacerun: yes;"> </span>Similar to Open
Angle, Angle Closure is also caused by blocked drainage canals in the eye.<span style="mso-spacerun: yes;"> </span>If you are experiencing symptoms, you really
need to visit a doctor immediately.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Though keeping an eye out for symptoms is key for Glaucoma,
there are certain ethnic groups that have a higher propensity to get this
disease. Glaucoma occurs five more times more often in African Americans than
other races and strikes earlier. The doctors suggest getting checked for
Glaucoma every year after the age of 35.<span style="mso-spacerun: yes;"> </span>By getting checked up more regularly you will be able to identify
symptoms earlier.</div>
<div class="MsoNormal">
<br /></div>
Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com2tag:blogger.com,1999:blog-7502872312575890871.post-7659444121815397552011-12-05T12:21:00.000-08:002011-12-05T12:21:47.684-08:00Insurance Coverage – Liability, Comprehensive and Collision Coverage<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<b>Insurance Coverage – Liability, Comprehensive and Collision Coverage</b><br />
<br />
Car Insurance coverage is comprised of many different types like liability, comprehensive and collision. Some of these like liability is typically mandatory while others like collision is optional. What kind of full coverage do you need depends on your particular situation. If you have an older car, it may not make sense to have comprehensive and collision coverages. We have put together information on different types on<br />
<a href="http://compare-insurance-car.com/insurance_coverage.html" target="_blank">insurance coverage</a> so you can make an informed decision for your personal insurance needs.<br />
<br />
<h3>Liability coverage:</h3><br />
Liability Insurance coverage is typically required by all states in the United States. This will typically cover any bodily damage (physical harm to another person) resulting from a car accident if it is your fault. This will also cover property damages (like automobiles, road signs, houses etc.) if you are found to be the guilty party in an insurance claim.<br />
<br />
The liability insurance is typically written as three numbers like 25/50/25. If you carry such a policy, this means that you have maximum coverage in the amount of $25,000 for bodily injury damage per person, maximum coverage of $50,000 for bodily injury damage per accident and amount of $25,000 for property damage per accident. Different states in America set their own limits of liability insurance coverage. You can get that information from this website:<br />
<br />
<b>Minimum Liability insurance</b><br />
<br />
What do all these insurance coverage mean? That first number 25 means that the Insurance company will pay for things like medical bills, hospital fees and lost wages in the amount of $25,000 insurance coverage. If there are multiple people injured in an accident, this will limit coverage in the amount of the second number 50 ($50,000) that was mentioned above. The last number 25 is for property damage insurance coverage. The insurance company will pay upto $25,000 insurance coverage for things like other autos involved in the accident, any road signs or property items like fences and household items that were destroyed in the accident. This is again assuming that you were at fault in the accident. If we reverse the scenario and another person is at fault, then their insurance company will be responsible for not only damage to their vehicle but also to your auto, your medical bills and property damages.<br />
<br />
Let's say that you live in Nevada and have 15/30/10 liability coverage and you are involved in an accident where you were at fault. In addition, several people get hurt in that accident unfortunately. If an ambulance is called which takes one or more people to the hospital, you will be held liable for payment of any medical expenses. If your insurance limit is only $30,000 per accident, and the total bills happen to be around $50k, you will be responsible for this amount.<br />
<br />
$50k - $30k= $20,000!!<br />
<br />
As such it is recommended that typically you carry 100/300/100 coverage or at the very least 50/100/50. <br />
<br />
<br />
<h3>Comprehensive and Collision Coverage</h3><br />
Comprehensive and collision on the other hand are necessary to cover damages to your car. When you are involved in a car accident, the insurance company will pay for damages to your vehicle minus any deductible that are specified in your insurance policy.<br />
<br />
Collision insurance will help you pay for any damages to your car in the event of an accident, hence a collision! This insurance policy typically carries a deductible, an amount between $250 to an amount of $1000. This deductible portion is typically paid by you (if you are at fault in an accident) and the rest of the damages are covered by the insurance company. Let's say you are involved in an accident and the damage to your SUV is around $4000. If your deductible is $500, this means is that your insurance company will pay you the following for damages.<br />
<br />
$4000 - $500 = $3500<br />
<br />
Most often they will just cut you a check in that amount and you can take the car to any body shop that you like for repairs.<br />
<br />
Comprehensive is for damages that are not due to a traffic accident. It can include things like fire, damage, theft, vandalism, hit and run and natural disasters like tornadoes, hail damage or a big FAT deer in the road! A deductible still applies in this scenario, just like the collision policy. If you have an older car, lets say more than 10 years, it is recommended that you do not carry Comprehensive and Collision.<br />
<br />
<h3>Auto Medical payments and No-fault option</h3><br />
Auto medical payments will pay for medical expenses incurred by you or the passengers in an accident. It does not matter if you are at fault or not, this option will cover any medical expenses like ER visits, doctors' fees and hospital charges. Some states have No-fault or PIP (Personal Injury Protection) also. Just like the Auto Medical payments, this will pay for medical expenses in addition to lost wages (Earnings you had to forgo due to injury). PIP coverage will also pay for lost wages if the passengers are injured and unable to perform their regular work duties.<br />
<br />
<h3>Uninsured motorist and Underinsured motorist</h3><br />
Sometimes you may be involved in a hit and run accident or where the person responsible for the accident does not have any coverage. If you are not at fault, having Uninsured motorist will pick up the tab. In a similar fashion, Underinsured motorist kicks in when someone else is at fault, however they do not have enough insurance to cover all the expenses. In this scenario your Underinsured motorist policy will take care of the expenses.<br />
<br />
<h3>Related Links for Insurance Coverage</h3><br />
-<a href="http://www.carinsurance.com/CoverageDefinitions.aspx" target="_blank">Insurance Coverage definitions from CarInsurance.com</a><br />
<div class="MsoNormal"><o:p></o:p></div><div class="MsoNormal">-<a href="http://www.edmunds.com/auto-insurance/10-steps-to-buying-auto-insurance.html" target="_blank">Auto Insurance Advice from Edmunds.com</a><o:p></o:p></div><div class="MsoNormal">-<a href="http://www.mini-choppers-guide.com/cheap-motorcycle-insurance.htm" target="_blank">Cheap Motorcycle Insurance Buying Guide</a><o:p></o:p></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div></div>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com1tag:blogger.com,1999:blog-7502872312575890871.post-2769925994422638172011-09-01T11:45:00.000-07:002011-09-01T11:45:41.503-07:00Attention: Bloggers for IT Training topics FREE advertising<div dir="ltr" style="text-align: left;" trbidi="on"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
</style> <![endif]--> <br />
<div class="MsoNormal">Attention: Bloggers for IT Training topics</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">We are actively looking for bloggers who would like to write content for our site</div><div class="MsoNormal"><a href="http://learnincomputer.com/">Learnincomputer.com</a>. The subject matter has to do with topics around IT training, distance learning and computer training topics.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">In return you will get FREE advertising on a site that gets 1,000,000 hits a month! There is NO compensation for these articles. Please contact me at <a href="mailto:blog@Learnincomputer.com">blog@Learnincomputer.com</a> today to get started and get your name out there.</div></div>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com2tag:blogger.com,1999:blog-7502872312575890871.post-61109008947895796932011-08-27T15:59:00.000-07:002011-08-27T16:09:03.095-07:00How can you save energy with Ambit Energy?<div dir="ltr" style="text-align: left;" trbidi="on">How can you save energy with Ambit Energy? <br />
<br />
<div closure_uid_x2602c="131"><div closure_uid_x2602c="375">Ambit Energy is a retail energy provider that is making a difference for energy consumers in Texas, New York, Illinois, Pennsylvania and Maryland. In the above mentioned states, Ambit Energy provides energy savings to residential, small business, and commercial customers. In 2009, Ambit recorded more than 400, 000 customers who saved on energy cost. In fact Ambit Energy was named the fastest-growing privately held company in north Texas by SMU's Caruth Institute for Entrepreneurship in their annual Dallas 100 rankings.</div></div><br />
Ambit Energy customers enjoy significant energy savings and an attractive travel rewards program. Additionally, through the Free Energy Program, customers can save energy or even get free electricity each month by referring friends to Ambit Energy and save money on electricity.<br />
<br />
<div closure_uid_x2602c="371"><h3>Ambit Advantages:</h3></div><br />
-3 day/2 night stay just for enrolling<br />
<br />
<div closure_uid_x2602c="332">-Refer five friends and family who want to <a href="http://kash.joinambit.com/">save energy</a> and get a FREE travel package at the same time</div><div closure_uid_x2602c="333"><br />
</div>-Up to 30% off energy savings on your electric bill<br />
<br />
-No fee to switch to start your energy savings<br />
<br />
-Refer 15 who can save energy cost and you get FREE ELECTRIC month after month!<br />
<br />
1 Reward Point for every kWh of electricity you use<br />
<br />
-Partner with #1 private company in America (Inc magazine) <br />
<br />
<a href="http://www.inc.com/magazine/20100901/meet-americas-fastest-growing-company.html">http://www.inc.com/magazine/20100901/meet-americas-fastest-growing-company.html</a><br />
<br />
-Fixed and variable rate plans are available to help you save on energy cost<br />
<br />
-FREE personal website for referring customers who can save on electric bill<br />
<div closure_uid_hh18y="132" closure_uid_x2602c="141"><br />
</div><br />
<br />
Did you know that a typical U.S. family spends more than $1,600 a year on home utility bills? The good news is, there is a lot you can do to save energy and money at home. With Ambit Energy, there is no complicated paperwork to start saving money and energy cost. Simply complete a short online order form and Ambit Energy will take care of the rest. Nothing will change in the way your service is delivered; you will just save energy cost!<br />
<br />
<strong>Call Kash @ 214-518-5972 and start saving energy and money today.</strong><br />
<div closure_uid_x2602c="331"><strong><a href="http://kash.joinambit.com/">http://kash.joinambit.com/</a></strong></div><strong>"Get FREE ELECTRIC today!"</strong><br />
<br />
<div closure_uid_x2602c="297">Call or email Kash today and he can show you <a href="http://kash.energy526.com/">What is Ambit</a> all about </div><br />
<br />
<h3>Related Links for Saving Energy</h3>-<a href="http://www.ecomall.com/greenshopping/20things.htm">20 things you can do to save energy cost</a><br />
<div closure_uid_x2602c="177"><br />
</div>-<a href="http://www.wikihow.com/Save-Energy-in-Your-Home">How to Save Energy in Your Home</a><br />
<div closure_uid_x2602c="176"><br />
</div><div closure_uid_x2602c="212">-<a href="http://saveenergy.about.com/od/energyefficientappliances/tp/SaveNow.htm">Top 10 Tips for Saving Energy Right Now – About.com</a></div><br />
-<a href="http://tlc.howstuffworks.com/home/how-to-conserve-energy-at-home.htm">How-to-conserve-energy-at-home. – TLC.com</a><br />
<br />
<br />
<br />
<br />
<br />
</div>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com3tag:blogger.com,1999:blog-7502872312575890871.post-551143213974211432011-08-09T06:51:00.000-07:002011-08-09T06:51:32.164-07:00Ceiling Fan Remote Control - Lightingindoors.com<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<h1>Ceiling Fan Remote Control</h1><br />
<br />
<br />
<br />
<br />
Are you looking for a ceiling fan remote control replacement?<br />
<br />
<br />
You've come to the right page for your ceiling fan remote control replacement needs. <a href="http://lightingindoors.com/">Lightingindoors.com</a> has now grown to be one of the leading suppliers of ceiling fan remote control and wall control systems. Over the past decade, we have ventured into ceiling fan remote control business with products such as universal ceiling fan remote control conversion kit, thermostatic fan remote control system and wall control switch for ceiling fans. We provide ceiling fan remote control replacement solutions and surely you will find a replacement remote control for your fan right at this store.<br />
<br />
Listed below are our most popular <a href="http://www.lightingindoors.com/ceiling-fan-remote-control.html">ceiling fan remote control</a> products for replacement needs. <br />
<br />
-Ceiling Fan Remote Control: Part No: JY708R/E01BT<br />
-Ceiling Fan Remote Control: Part No: JY326B-1/JY622CF<br />
-Ceiling Fan Remote Control: Part No: JY200/E01BT<br />
<br />
Many ceiling fans are now equipped with the wireless remote control system. This feature offers full operation of the fan (and light attachment) from any location within a reasonable distance. This can be especially beneficial in adjusting the ceiling fan and lights without having to get up and operate a wall switch or pull-chain.<br />
<br />
Some ceiling fan remote control systems are not built into the fans. However there is a factory default option added and can be used for future installation at an additional cost. Usually these ceiling fans consist of a remote receiver wired to the fan inside the ceiling canopy and the fan may still have a pull-chain that is not used. If this is the case, then the receiver can be removed and replaced with our universal remote control /receiver conversion kit to turn your regular ceiling fan into a remote controllable one.<br />
<br />
If your ceiling fan remote is lost or broken regardless of your fan model, our company could offer you a universal remote control designed to work with the majority of different brands of ceiling fans. In addition, if you need detailed help in replacing/installing a remote control system for your ceiling fan, please refer to the installation manual that come with the kit. Here you will have find a step by step guide that you can follow for ceiling fan remote control replacement.<br />
<br />
<br />
<a href="http://lightingindoors.com/">Lightingindoors.com</a> link specializes in <a href="http://www.lightingindoors.com/index.html">ceiling fan capacitors</a>, ceiling fan remote control, lighting fixtures and ceiling fan parts and <a href="http://www.lightingindoors.com/LED-Lighting.html">LED lighting</a>.<br />
<br />
</div>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com1tag:blogger.com,1999:blog-7502872312575890871.post-60218300155722223502011-02-20T07:15:00.000-08:002011-02-20T07:15:53.917-08:00Select the right Plano printing partner Minuteman Press Plano TX<div dir="ltr" style="text-align: left;" trbidi="on"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:DoNotShowComments/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}
</style> <![endif]--> <br />
<div align="center" class="MsoNormal" style="text-align: center;"> <b>Select the right Plano printing partner Minuteman Press Plano TX</b></div><div align="center" class="MsoNormal" style="text-align: center;"><br />
</div><div class="MsoNormal">Do you know how to specify printing and get the most bang for your bucks?<span> </span>You will most likely at some point need printed marketing communication pieces. With many options available, consider maximizing your print projects with a superior Plano printing resource.<span> </span>At Minuteman Press Plano, we will deliver top quality print job.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Many factors determine the right type of printing for your job. Basically, <a href="http://www.plano.minutemanpress.com/">Plano printing</a> offers offset printing or digital printing in Plano and Frisco Texas. Offset printing is best for high quality reproduction in a quantity of 500 or more. Offset printing uses ink, which typically yields a much higher quality than digital printing, which uses toner. However, Plano printing digital technology makes digital printing a good choice for great short print runs and quick, economical projects. Select Minuteman Press conveniently located by Frisco and Plano areas as the right type of print partner for your business.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Paper will either enhance or destroy the printed piece. Besides the weight of paper, we will help you choose a coated (gloss) or uncoated (flat) finish. Texture also plays a big part in the overall feel of the piece and guidance from Plano printing makes all the difference.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">The number of colors will help determine what kind of printing. In offset, you may choose to print using spot colors such as PMS (Pantone Matching Systems®) or 4-color process (CMYK) depending on the number of colors and/or effects you want to achieve. Spot colors such as PMS will give you a solid ink coverage rather than dots combined to create a color using cyan, magenta, yellow and black. You can see these small dots in 4-color process if you look at a magazine under magnification. The advantage of 4-color process is that you can print as many colors as you like and still only pay for a 4-color print job. Plano printing press comes in even number of colors. <span> </span>The more colors do not necessarily make a piece look better.<span> </span>The message and design of the entire piece is what makes it work.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">At <a href="http://www.plano.minutemanpress.com/">Minuteman Press Plano</a>, we provide proofs at the beginning of the print job. With so much efforts spent on creating the piece, we want your print projects in North Texas to turn out right. Before the job prints, Minuteman Press Plano will provide a proof for your review. <span> </span>Our printing expertise at the Plano Office will review your printing project that includes folding, scoring, finishing specification to assist you with the best possible outcome every time.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Site optimized by <a href="http://www.datatechnologyllc.com/">SEO Dallas</a> company.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div></div>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-28119480876098221322011-02-20T07:09:00.001-08:002011-02-20T07:09:52.065-08:00Blog is moving!!!!<div dir="ltr" style="text-align: left;" trbidi="on">We will be posting all the new content related to SQL Server topics at this blog<br />
<a href="http://sqlazuretutorials.com/wordpress/">http://sqlazuretutorials.com/wordpress/</a><br />
<br />
Thank you and have a good day!<br />
<br />
</div>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-29356468518026536802010-12-11T12:45:00.000-08:002010-12-11T12:45:06.543-08:00SQL Server 2005 Managment Studio Express<iframe src="http://www.youtube.com/embed/KHHmRhFRM20?fs=1" width="425" frameborder="0" height="344"></iframe>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-4293611647550788332010-11-11T09:03:00.000-08:002010-11-11T09:03:13.393-08:00How to find and remove duplicate rows in SQL Server<h1>How to find and remove duplicate rows in SQL Server:</h1><br />
Relational databases by design are supposed to eliminate redundancy. The whole idea of Redundancy is that<br />
you should store information in one place in order to maintain data integrity. Reality however is far from this truth as there are many Production databases out there with redundant data. As such the duplicate records are alive and well unfortunately!<br />
<br />
In today’s discussion we are going to look at some sample code that will let you find duplicate data across one or many fields. Next we will look at some different methods on how to remove and delete that duplicate data. For today’s exercises, we are going to be working with sample Microsoft Northwind database which has been around for a few years to say the least. All this SQL code has been tested on SQL server 2008 Enterprise Edition.<br />
<br />
<b>-How do we create duplicate data for testing purposes?</b><br />
<br />
First we are going to make a copy of the CUSTOMERS table using the following <a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/sql-find-duplicate-records1.sql">sql query</a>.<br />
Next we are going to reinsert the data for Country=UK twice into the same table. We can do this by using the following SQL <a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/how-to-create-duplicate-rows2.sql">query to populate duplicate data</a>. <br />
<br />
Before we get started with finding and deleting duplicate records, we are going to do a simple <a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/count-rows-duplicate-table3.sql">record count</a>. This will let us know how many rows are present in the CUSTOMERS_BAK table. Now that we have our baseline data we need to start looking at different methods of finding duplicates in CUSTOMERS_BAK table.<br />
<br />
We are going to go through a set of examples to show you how to accomplish this task.<br />
<br />
<b>-How do you find duplicate records in a SQL table?</b><br />
<br />
There are many ways of finding duplicate data in a SQL Server table, however the simplest and the most efficient method is to use the <b>GROUP BY</b> clause. Further information on this SQL expression can be found on this <a href="http://databases.about.com/od/sql/a/Group_By_SQL.htm">website</a>. In our case we are going to be using GROUP BY with a HAVING clause to find the repeating (duplicate) rows. The first query will let us find any rows with repeating Company Names in our table. We are going to use the following <a href="http://www.blogger.com/">TSQL query in SQL Server</a> for this.<br />
<br />
<i>SELECT COMPANYNAME<br />
FROM CUSTOMERS_BAK<br />
GROUP BY COMPANYNAME<br />
HAVING COUNT(*)>1</i><br />
<br />
<br />
<br />
This query is looking for those rows in the CUSTOMERS_BAK table where the record count is greater than 1 when you group them by COMPANYNAME field. Notice you will get the 7 rows from UK customers that we had added in an earlier step. Here is a screen capture of this duplicate query for you.<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/query-to-find-duplicate-rows.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="357" src="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/query-to-find-duplicate-rows.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Duplicate Company Name</td></tr>
</tbody></table><br />
What if we wanted to find out, how often these records are repeated within the table. We can easily do that by running <a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/duplicate-sql-query-5.sql">sql command with COUNT</a> function in it.<br />
<br />
You will have noticed that so far we have only looked at one field, COMPANYNAME the above case. In a similar fashion we can add multiple fields to the SQL query. Let us see if he wanted to find any duplicate rows with fields CITY and COUNTRY that are being repeated in our table. How would we find the duplicates for two fields instead of one? Let us try this <a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/find-duplicate-city-country-6.sql">sql server command</a> first.<br />
<br />
<i>SELECT CITY, COUNTRY, COUNT(*) AS [# OF TIMES]<br />
FROM CUSTOMERS_BAK<br />
GROUP BY CITY<br />
HAVING COUNT(*)>1</i> <br />
<br />
<br />
<br />
When we ran the above SQL statement ro find duplicate records, we got the following error:<br />
<br />
<i><b>Msg 8120, Level 16, State 1, Line 1</b><br />
Column 'CUSTOMERS_BAK.Country' is invalid in the select list because it is not contained <br />
in either an aggregate function or the GROUP BY clause.</i><br />
<br />
The reason for this error is that although we are selecting CITY, COUNTRY fields, in our GROUP BY clause, we only have the CITY field in the SELECT portion. The number of fields in the SELECT clause has to match the GROUP BY clause. It is ok to have the COUNT(*) as it is an aggregate function. This is how GROUP BY clause works because you are trying to find the CITY and the COUNTRY fields that are also grouped by the same fields. <br />
<br />
You can simply run the following query that will give you the end result. You will notice that there are 11 rows in our table that match the duplicate criteria. <br />
<br />
<i>SELECT <b>CITY, COUNTRY</b>, COUNT(*) AS [# OF TIMES]<br />
FROM CUSTOMERS_BAK<br />
GROUP BY <b>CITY, COUNTRY</b><br />
HAVING COUNT(*)>1</i><br />
<i><br />
</i><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/find-duplicate-records-sql-server6.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="357" src="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/find-duplicate-records-sql-server6.jpg" width="400" /></a></div><br />
<br />
<br />
Let us say that we actually have duplicate rows across all the fields in a MS SQL table. This is probably the most common type of situation that you will run into working with redundant data. In this one you simply need to add all the fields in the table and also make sure that all those fields are also added in the GROUP BY clause. Using our CUSTOMERS_BAK table we can use the following <a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/sql-duplicates-accross-all-fields7.sql">tsql script</a> to do just that.<br />
<br />
You will notice that there are seven rows in the customer table that are repeated.<br />
<br />
<b>How do you remove duplicate rows that?</b><br />
<br />
So far we have covered how to find duplicate data, the next question obviously is: <u>How to delete duplicate rows?</u> There are many options and ways on how you can eliminate duplicate rows, we are going to look at two of these options. Both these options involve inserting non-duplicate data into another table first and then renaming the new table to the original table.<br />
<br />
<b>Remove duplicate rows - Option 1</b><br />
<br />
This is the one I prefer as it is more efficient and uses similar code to what we have already seen. We are going to use a <b>GROUP BY</b> without the having clause. Here’s the <a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/how-to-remove-duplicates8.sql">query to remove duplicates</a>. We have also included a screen capture from part of the sql statement that removes the duplicates.<br />
<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/delete-duplicate-rows-sqlserver8.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="357" src="http://www.sqlserver2008tutorial.com/blog/duplicate-rows/delete-duplicate-rows-sqlserver8.jpg" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Remove duplicate data</td></tr>
</tbody></table><br />
<br />
<b>Remove duplicate rows - Option 2</b><br />
<br />
Another method is to use the <b>UNION </b>expression to filter out the duplicate rows and leave distinct values behind. Here’s the sql command in order to do that.<br />
<br />
<br />
<h3>Related articles to Finding and Deleting Duplicate rows:</h3><br />
-<a href="http://support.microsoft.com/kb/139444">Remove Duplicate Rows in SQL Server </a><br />
-<a href="http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx">Delete duplicate rows in a table </a><br />
-<a href="http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/">Using common table expression to remove duplicate data</a><br />
-<a href="http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X?ie=UTF8&tag=sl09d-20&link_code=btl&camp=213689&creative=392969" target="_blank">Microsoft SQL Server 2008 Management and Administration</a><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=sl09d-20&l=btl&camp=213689&creative=392969&o=1&a=067233044X" style="border: medium none ! important; margin: 0px ! important; padding: 0px ! important;" width="1" /><br />
-<a href="http://www.amazon.com/Microsoft-SQL-Server-2008-Bible/dp/0470257040?ie=UTF8&tag=sl09d-20&link_code=btl&camp=213689&creative=392969" target="_blank">Microsoft SQL Server 2008 Bible</a><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=sl09d-20&l=btl&camp=213689&creative=392969&o=1&a=0470257040" style="border: medium none ! important; margin: 0px ! important; padding: 0px ! important;" width="1" /><br />
-<a href="http://sqlserver2008tutorial.com/">SQL Server Tutorials</a><br />
<br />
<a href="http://www.amazon.com/Microsoft-SQL-Server-2008-Step/dp/0735626049?ie=UTF8&tag=sl09d-20&link_code=bil&camp=213689&creative=392969" imageanchor="1" target="_blank"><img alt="Microsoft SQL Server 2008 Step by Step (Step by Step (Microsoft))" src="http://ws.amazon.com/widgets/q?MarketPlace=US&ServiceVersion=20070822&ID=AsinImage&WS=1&Format=_SL160_&ASIN=0735626049&tag=sl09d-20" /></a><img alt="" border="0" height="1" src="http://www.assoc-amazon.com/e/ir?t=sl09d-20&l=bil&camp=213689&creative=392969&o=1&a=0735626049" style="border: medium none ! important; margin: 0px ! important; padding: 0px ! important;" width="1" />Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com1tag:blogger.com,1999:blog-7502872312575890871.post-73158640935380733702010-10-06T12:06:00.000-07:002010-11-01T13:55:09.967-07:00How to import data in SQL Azure from SQL 2008 R2 using SSIS?<h2>How to import data in SQL Azure from SQL 2008 R2 using SSIS </h2><br />
When working with SQL databases one of the most common tasks is to import or export data from a database. In experimenting with <b>SQL Azure</b>, I realized that it is not possible to migrate data using the familiar Import and Export wizard that is available in SQL Server Management Studio (SSMS). What you have to do it is create a <b>SSIS</b> (SQL Server Integration services) package in Business Intelligence Development Studio (<b>BIDS</b>) and then execute it. <br />
<br />
In this article we are going to show you how to copy data from <b>on-site SQL 2008</b> to <b>off-site SQL Azure</b>. For this example we are going to export data from Products table in a local database to a database in the cloud using SQL Integration Services.<br />
<br />
Before we jump into SSIS, we found another thing that is unique to SQL Azure version. We noticed that we need to have a clustered index on the destination table before SSIS will let us import data into it. So we have to do two things on the destination SQL Azure database first<br />
<br />
-Create the Products table<br />
-Define a clustered index on the table <br />
<br />
We have included the <a href="http://sqlserver2008tutorial.com/blog/sql-azure/import-data/create_products.sql">SQL script</a> for the above two steps for your convenience.<br />
<br />
<br />
--This script creates the Products table on SQL Azure.<br />
--Next we create a clustered index on ProductID<br />
<br />
<br />
<i>CREATE TABLE [dbo].[Products](<br />
[ProductID] [int] NOT NULL,<br />
[ProductName] [nvarchar](40) NOT NULL,<br />
[SupplierID] [int] NULL,<br />
[CategoryID] [int] NULL,<br />
[QuantityPerUnit] [nvarchar](20) NULL,<br />
[UnitPrice] [money] NULL,<br />
[UnitsInStock] [smallint] NULL,<br />
[UnitsOnOrder] [smallint] NULL,<br />
[ReorderLevel] [smallint] NULL,<br />
[Discontinued] [bit] NOT NULL<br />
<br />
)<br />
---ON [PRIMARY]</i><br />
<br />
<i>CREATE CLUSTERED INDEX IX_Product ON dbo.Products<br />
(<br />
ProductID<br />
)<br />
<br />
GO</i> <br />
<br />
<br />
<br />
<br />
<br />
Before getting started please realize that we are working with Enterprise edition of <b>SQL Server 2008R2</b> and cloud version on SQL Server. <br />
First off launch the Business Intelligence Development Studio or BIDS from the start menu by doing the following:<br />
<br />
<b>Start – All Programs – Microsoft Visual Studio 2008 - Microsoft Visual Studio 2008</b><br />
<br />
This will launch the Business Intelligence Development Studio. Next from the File menu and do the following:<br />
<br />
<b>File - New - Project</b><br />
<br />
Next go ahead and select Integration Services Project. This is shown in the dialog box below:<br />
<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="330" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-integration-services-project1.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Integration Services Project</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-integration-services-project1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
<br />
You can add a name for the project and then click OK. This action will go ahead and create the necessary components under <b>Solution Explorer</b>. In addition it will also launch the Package Design window in the middle. The next thing you need to do is to add a data source that will be used to export data. Within the Solution Explorer, right click on Data Sources and then select New Data Source. This will open up the Data Source wizard screen, go ahead and click on Next. If you do not have an existing data connection, you can create one by clicking New.<br />
<br />
In this example we are going to use sample <b>Northwind</b> database on a local instance of SQL server 2008 R2 to export data. This SSIS data source is for server KASHMONEY-PC\SQL08R2 as shown in the following computer screenshot.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="400" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-onpremise-database2.jpg" style="margin-left: auto; margin-right: auto;" width="391" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">On-site SQL 2008 R2</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-onpremise-database2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql_azure_data_source-wizard3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br />
</a></div><br />
<br />
The next thing we need to do is to define the data destination where data will be imported into. In this case this we will use a SQL Azure database in the cloud. In order to create this connection, we need to define another data source so we are going to follow the earlier step.<br />
<br />
<br />
We will invoke the <b>Data Source Wizard </b>one more time as shown below.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql_azure_data_source-wizard3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="362" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql_azure_data_source-wizard3.jpg" width="400" /></a></div><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-connection-manager4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br />
</a></div><br />
Here we had some issues as we tried different data providers in order to connect successfully to a SQL Azure server and the database. Bottom line is you need to use the <b>.Net Providers\SqlClient data provider</b> as shown below in the Connection Manager screen.<br />
<br />
In addition you will need to know the server address, SQL username and the SQL password. <br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="400" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-connection-manager4.jpg" style="margin-left: auto; margin-right: auto;" width="391" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">SQL Azure Connection</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-connection-manager4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
Next click OK which will finish the wizard. At this point, we have our data source and are data destination all squared away. We have included a screen capture that shows the end of the data wizard with the connection string information.<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="362" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-data-import5.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Destination Database</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-data-import5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
<br />
<br />
Click Finish to exit out of this.<br />
<br />
Now we are going to configure the <b>SSIS package</b> details, remember an Integration package is the building block for data movement in SQL Server. Make sure that you are on the <b>Control Flow</b> tab in design pane. From the Toolbox on the left, drag the Data Flow task under the Control Flow Items onto the design surface.<br />
<br />
This step has been displayed for you in the following figure.<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="332" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-ssis-data-flow-task6.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">SSIS Package</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-ssis-data-flow-task6.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
Next switch to the <b>Data Flow</b> tab on the SSIS design pane. Next we will be adding a data source from our local on premise database. We drag the <b>OLE DB Source</b> from the Data Flow Sources.<br />
<br />
Here is what it looks like on our puter monitor.<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="310" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/integration-services-oledb-data-source7.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">OLE DB Source</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/integration-services-oledb-data-source7.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
<br />
We need to configure the SSIS data flow item. Right click on <b>OLE DB Source</b> and select Edit. On the next dialog OLE DB Source Editor, go ahead and select New. This will open up a new screen as shown below.<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="282" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/ssis-config-oledb-connection8.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">On Premise SQL Server</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/ssis-config-oledb-connection8.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
<br />
From the data connections pane, select LocalHost.Northwind and select Ok.<br />
<br />
So far we have just made the connection to the source database, we still need to get the table information. Here you will select Table or view under Data access mode. Next choose Products under Name of the table or the view. After you click Ok, you have successfully created SSIS data flow from the source. This step is shown below:<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-ssis-oledb-provider9.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="293" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-ssis-oledb-provider9.jpg" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/ssis-sql-data-flow-tab10.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br />
</a></div><br />
<br />
<br />
The next step is to create the <b>ADO Net Destination</b>, this Is where the data will be imported into. From the Toolbox on the left, go ahead and select the Ado Net destination SSIS Data Flow item and then move it over to the design pane in Data Flow tab. This is shown in the screen capture right below, notice that the connection has not been configured yet and is displayed in red.<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="321" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/ssis-sql-data-flow-tab10.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Data Flow controls</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/ssis-sql-data-flow-tab10.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
<br />
Before you can configure the ADO NET destination, you have to drag the output (green arrow) from the OLE DB Source to the ADO NET Destination connection. Next you can right click on ADO NET destination and select Edit. This will launch a dialog box titled <b>ADO NET Destination Editor</b>.<br />
<br />
Go ahead and click on New. You will get the Connection Manager screen shown below.<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/ado-net-connection-manager11.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="307" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/ado-net-connection-manager11.jpg" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-import-data-table12.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br />
</a></div><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-import-data-table12.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br />
</a></div><br />
<br />
<br />
<br />
Go ahead and select the SQL Azure connection that was defined in earlier step. Also you will need to point to the table we created using SQL Script. For <b>Use a table or view</b> select Products. Here is what is looks like on our Microsoft Visual Studio 2008 application.<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-import-data-table12.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="383" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-import-data-table12.jpg" width="400" /></a></div><br />
<br />
<br />
<br />
One final thing we need to do before we can import data using Integration Services is to do map the columns between the data source and the data destination. If you are using the same table schema structure on the source and the destination tables, this should be pretty straight forward.<br />
<br />
<br />
Click on <b>Mappings</b> in the left pane and make sure the Input and Output columns match. We have included a screen shot for your review.<br />
<br />
<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="330" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-data-mapping13.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Column Mappings</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-data-mapping13.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
<br />
<br />
<br />
<br />
Let us take a look at our Data Flow designer before we kick things off. We have done this so far<br />
<br />
1- Defined a local on premise server connection to Northwind database<br />
2- Defined a cloud SQL Azure server connection to an off site database<br />
3- Defined a OLE DB source that maps to local on-site database connection<br />
4- Defined a ADO NET destination that maps to an off-site cloud SQL Azure database connection<br />
<br />
Here is what we have so far, we are almost done.<br />
<br />
<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="363" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-ssis-package14.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Integration Services Package</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-ssis-package14.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-execute-ssis-package15.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br />
</a></div><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-execute-ssis-package15.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br />
</a></div><br />
<br />
Now we are going to kick off the SSIS package, all you need to do is click on Execute (green arrow) or <b>Debug – Start Debugging</b>. In our case, all the items are configured properly and our SSIS package executed with success. we have the following end result and were able to move 77 records from <b>SQL 2008 to SQL Azure</b>.<br />
<br />
<br />
<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="363" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-execute-ssis-package15.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Execute SSIS package</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/sql-azure-execute-ssis-package15.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
<br />
<br />
We can verify the data on the target SQL Azure location with this SQL query.<br />
<br />
<br />
<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><img border="0" height="330" src="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/migrate-data-sql-azure16.jpg" style="margin-left: auto; margin-right: auto;" width="400" /></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Check data at Target</td></tr>
</tbody></table><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-azure/import-data/migrate-data-sql-azure16.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<br />
<br />
<br />
<br />
<br />
We were successful in migrating data from an on premise local database to a SQL Azure cloud database using SQL Server Integration Services.<br />
<br />
<br />
More tips on SQL Azure can be found at our site:<br />
<br />
<a href="http://sqlazuretutorials.com/">http://sqlazuretutorials.com/</a><br />
<br />
<br />
<br />
<br />
<h2>Tags: Import data, SQL Azure, SQL 2008 R2, SSIS </h2>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-7116039902691506152010-08-30T11:41:00.000-07:002010-11-01T13:55:09.967-07:00SQL Aggregate functions like SQL MAX, SQL MIN, SQL COUNT And SQL AVERAGE function<h1>SQL Aggregate functions like SQL MAX, SQL MIN, SQL COUNT And SQL AVERAGE function</h1><br />
SQL Aggregate functions are necessary to summarize numerical data in a SQL Server database. We have already looked at other transact SQL statements like <a href="http://slackerdba.blogspot.com/2010/07/sql-statements-including-sql-insert-sql.html">SELECT, INSERT, DELETE and UPDATE</a> SQL statements. Those typically are used to return rows of data that matches the criteria in the WHERE clause. SQL statements using aggregate functions like MIN or MAX however return just one value after SQL server has already applied the aggregation and summarized the data. In this manner SQL Server aggregate functions perform their operation across multiple rows of data. <br />
<br />
For today’s discussion we are going to look at AdventureWorks2008 sample database in SQL Server 2008. You can download the associated data from this location:<br />
<br />
Products: <br />
<br />
<br />
<a href="http://www.sqlserver2008tutorial.com/blog/sql-aggregate-functions/production_products.xls">http://www.sqlserver2008tutorial.com/blog/sql-aggregate-functions/production_products.xls</a><br />
<br />
Orders:<br />
<br />
<a href="http://www.sqlserver2008tutorial.com/blog/sql-aggregate-functions/sales_order_header.xls">http://www.sqlserver2008tutorial.com/blog/sql-aggregate-functions/sales_order_header.xls</a><br />
<br />
<br />
<br />
There are a number of SQL aggregate functions; however we are only going to look at the important ones so here we go:<br />
<br />
<h3>SQL MIN Aggregate function:</h3><br />
The SQL function is used to return the minimum value in the select list, ignoring any Null value. This would typically be used let’s say in a PRODUCTS table to find your cheapest product. <br />
<br />
<span style="color: #0b5394;">SELECT </span><br />
<span style="color: #0b5394;">MIN(LISTPRICE) AS [MINIMUM PRICED ITEM]</span><br />
<span style="color: #0b5394;">FROM PRODUCTION.PRODUCT</span><br />
<span style="color: #0b5394;">WHERE SIZE IS NOT NULL</span><br />
<br />
--8.99<br />
<br />
<br />
<h3>SQL MAX Aggregate function:</h3><br />
This Aggregate function is used to return the maximum value in the select list, ignoring th any Nulls. Similar to the SQL MIN function, the SQL Max function can be used to find the highest priced item in the above products table. Here is the SQL syntax for MIN function:<br />
<br />
<span style="color: #0b5394;">SELECT </span><br />
<span style="color: #0b5394;">MAX(LISTPRICE) AS [MAXIMUM PRICED ITEM]</span><br />
<span style="color: #0b5394;">FROM PRODUCTION.PRODUCT</span><br />
<span style="color: #0b5394;">WHERE SIZE IS NOT NULL</span><br />
<br />
--3578.27<br />
<br />
<h3>SQL SUM Aggregate function:</h3><br />
This SQL function returns the total sum of all items in the select list, ignoring any Nulls. The sum function will add up all the values for a column in a certain table. For example we are going to use the SALESORDERHEADER table and summarize the TOTALDUE field<br />
<br />
<span style="color: #0b5394;">SELECT SUM(TOTALDUE) AS SUMMATION</span><br />
<span style="color: #0b5394;">FROM SALES.SALESORDERHEADER</span><br />
<span style="color: #0b5394;">WHERE CUSTOMERID='29898'</span><br />
<br />
--197634.248<br />
<br />
<h3>SQL AVG Aggregate function:</h3><br />
SQL provides the AVG function to return the average of values in the select list, ignoring any Nulls. This is an important function which will help you find average value of let’s say product prices in your product table. We are going to use the same SALESORDERHEADER table from the prior example to show you SQL average function in action<br />
<br />
<br />
<span style="color: #0b5394;">SELECT AVG(TOTALDUE) AS AVERAGE</span><br />
<span style="color: #0b5394;">FROM SALES.SALESORDERHEADER</span><br />
<span style="color: #0b5394;">WHERE CUSTOMERID='29898'</span><br />
<br />
--49408.562<br />
<br />
<br />
<h3>SQL COUNT Aggregate function:</h3><br />
A lot of times all you’re trying to figure out his how many rows or records apply to a certain condition. This is where the sql COUNT function comes in handy as it will simply count the number of rows that meet your criteria. The COUNT aggregate function returns the number of items in the select list. This ignores the NULL values unless you use COUNT(*). This is by far the most important aggregate function that you will be using. Here is an example from the SALESORDERHEADER table.<br />
<br />
<span style="color: #0b5394;">SELECT COUNT(TOTALDUE) AS [ITEM COUNT]</span><br />
<span style="color: #0b5394;">FROM SALES.SALESORDERHEADER</span><br />
<span style="color: #0b5394;">WHERE CUSTOMERID='29898'</span><br />
<br />
--4 RECORDS<br />
<br />
Sample video on Aggregate Functions here:<br />
<br />
<a href="http://www.learningsqlserver2008.com/products.htm">http://www.learningsqlserver2008.com/products.htm</a><br />
<br />
<h2>TAGS: SQL Server AGGREGATE FUNCTIONS, SQL COUNT, SQL AVG, SQL MAX, SQL MIN, SQL SUM<br />
</h2>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-51912596682319595702010-07-31T17:16:00.000-07:002010-11-01T13:55:09.967-07:00SQL STATEMENTS INCLUDING SQL INSERT, SQL UPDATE And SQL DELETE<h1>SQL STATEMENTS INCLUDING SQL INSERT, SQL UPDATE And SQL DELETE:<br />
</h1><br />
<h3>SQL INSERT STATEMENT:</h3><br />
The <b>SQL INSERT</b> statement adds one or more new rows to a table. In a simplified version, INSERT has this sql syntax:<br />
<i><br />
</i><br />
<i><b>INSERT </b>[INTO] table_or_view </i><br />
<i>[(column_list)] </i><br />
<i>data_values</i><br />
<br />
Using this syntax you can typically insert data in a single row shown as follows. Here we are using Northwind sample database from Microsoft.<br />
<i><br />
</i><br />
<i><b>INSERT</b> INTO REGION VALUES ('5','MIDWEST')</i><br />
<br />
If you want to insert data in a table with multiple records, INSERT statement can use a sub-select (sub query) to do just that. <br />
In the next sql insert command example we are trying to insert contact information into CUSTOMER_ CONTACT table from CUSTOMER table. Here’s the transact SQL syntax for insert statement with a sub query:<br />
<i><br />
</i><br />
<i><b>INSERT</b> CUSTOMER_CONTACT (CUSTOMERID, CONTACTNAME, PHONE)</i><br />
<i>SELECT CUSTOMERID, CONTACTNAME, PHONE</i><br />
<i>FROM CUSTOMERS</i><br />
<br />
This will insert only the specific fields, however all the records from the customers table.<br />
Sql insert comman. Here is a screen shot of what this sql command looks like in SQL Server Management Studio (SSMS)<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlserver2008tutorial.com/blog/tsql-commands/sql-insert-command.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="http://sqlserver2008tutorial.com/blog/tsql-commands/sql-insert-command.jpg" width="400" /></a></div><br />
<br />
<br />
What if you wanted to make a copy of a table and insert data at the same time? No problem. You can do that by using SELECT * INTO. Here is an example of a copy of CUSTOMERS table with a new table is CUSTOMER_CONTACT_BAK. When you use WHERE 1=2, it just creates the table and does not insert any data in the table.<br />
<br />
<i><b>SELECT * INTO </b>CUSTOMER_CONTACT_BAK<br />
FROM CUSTOMERS WHERE 1=2</i><br />
<br />
<br />
<h3>SQL UPDATE STATEMENT:</h3><br />
The <b>Update SQL </b>command is used to update data in tables. Here is the basic syntax of this important transact sql (tsql) command.<br />
<i><br />
</i><br />
<i><b>UPDATE </b>TABLE </i><br />
<i>SET COLUMN=NEW VALUE </i><br />
<i>WHERE CONDITION</i><br />
<br />
If you use the update statement without the WHERE clause, you will update all the rows in the table with the same value Ooops! Be careful with this sql command for sure. <br />
<br />
For the next scenario let us say there is a shortage of seafood in the market, so we need to raise the price by 5% for these items. Go ahead and try this update command on Northwind database.<br />
<i><br />
</i><br />
<i><b>UPDATE </b>PRODUCTS</i><br />
<i>SET UNITPRICE=UNITPRICE * 1.05</i><br />
<i>WHERE CATEGORYID=8</i><br />
<br />
Here is a screen capture of the above tsql command from SSMS,<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlserver2008tutorial.com/blog/tsql-commands/sql-update-command.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="http://sqlserver2008tutorial.com/blog/tsql-commands/sql-update-command.jpg" width="400" /></a></div><br />
<br />
Just like the SQL INSERT command, you can also use a sub-select command with an SQL UPDATE statement also. The concept is say you are trying to update one table with data from another table. This is commonly used when you are trying to fix the transactional table with values from some lookup table. Let us take a look at this next update sql example now.<br />
<br />
<i><b>UPDATE </b>DBO.CUSTOMER_CONTACT</i><br />
<i>SET REGION=REGIONDESCRIPTION</i><br />
<i>FROM DBO.REGION_STATE</i><br />
<i>WHERE CUSTOMER_CONTACT.STATE=REGION_STATE.STATE</i><br />
<br />
In the above sql query we are updating CUSTOMER_CONTACT table, field REGION with values from <a href="http://sqlserver2008tutorial.com/blog/tsql-commands/Region_State.xls">REGION_STATE</a> table, field REGIONDESCRIPTION. The catch is that it will only update those rows where there is a match between two tables on the state field.<br />
<br />
Here are the copies of the CUSTOMER_CONTACT table before and after update <br />
<br />
<a href="http://sqlserver2008tutorial.com/blog/tsql-commands/before_update_CUSTOMER_CONTACT.xls">Before the sql update command</a><br />
<a href="http://sqlserver2008tutorial.com/blog/tsql-commands/after_update_CUSTOMER_CONTACT.xls">After the update sql command</a><br />
<br />
<br />
<h3>SQL DELETE STATEMENT:</h3><br />
In five words <b>SQL Delete</b> statement “Removes rows from a table!” Be very careful with this command. Here is the basic syntax of a sql delete statement<br />
<i><br />
</i><br />
<i><b>DELETE </b>FROM TABLE </i><br />
<i>WHERE CONDITION</i><br />
<br />
Notice that absence of where clause deletes all rows. Here is an example of a simple delete query command. <br />
<br />
<i><b>DELETE </b>FROM NORTHWIND..REGION</i><br />
<i>WHERE REGIONDESCRIPTION='MIDWEST' </i><br />
<br />
This delete statement will affect the row that we had added earlier in the INSERT statement. Lets us say that we stopped carrying ay TOFU products, you could get rid of those rows by using the following sql command.<br />
<i><br />
</i><br />
<i><b>DELETE </b>FROM Products</i><br />
<i>WHERE ProductName LIKE '%TOFU%'</i><br />
<br />
When we ran this we got the following error:<br />
<i><br />
</i><br />
<i>The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'.</i><br />
<i>The statement has been terminated.</i><br />
<br />
We have capture the above sql command and the error in the following image<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlserver2008tutorial.com/blog/tsql-commands/sql-delete-statement.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="http://sqlserver2008tutorial.com/blog/tsql-commands/sql-delete-statement.jpg" width="400" /></a></div><br />
<br />
<br />
This SQL error has to do with the Primary key and Foreign key relationships. Basically what it is saying is that you have existing customer orders with this product. You would first need to delete the specific rows in ORDER DETAILS table and then delete the “TOFU” rows in PRODUCTS table. <br />
<br />
Just like the INSERT And UPDATE statements, DELETE sql statement can also use a sub-select (sub query). Also please note <b>DELETE * FROM TABLE</b> will not work and you have to use <b>DELETE FROM TABLE</b> format.<br />
<br><br />
<h2>TAGS: SQL INSERT, SQL DELETE, SQL UPDATE, NORTHWIND</H2>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-55457127800412793482010-06-30T16:45:00.000-07:002010-11-01T13:55:09.968-07:00Transfer logins in SQL Server 2005 and SQL Server 2008/*------------------------------------------------------------------------------------------------------------<br />
<br />
<h3>Transfer logins in SQL Server 2005 and SQL Server 2008</h3><br />
In this script we cover sp_help_revlogin stored procedure which can help you copy sql server logins from one server to another server running SQL Server 2008.<br />
<br />
This can be useful when you are trying to copy a database from one server (maybe production) to another (development or stage). Remember when you copy a database it does not move the SQL Server Logins which are present at the server level. You have to manually transfer the login accounts from the source ms sql server to target ms sql server. <br />
<br />
Using the information here you can easily do this task.<br />
<br />
------------------------------------------------------------------------------------------------------------*/<br />
<br />
/* <br />
<br />
<h3>Transfer logins SQL Server 2005 - Step 1 </h3><br />
Create sp_help_revlogin procedure<br />
<br />
Sources:<br />
<br />
<a href="http://support.microsoft.com/kb/246133/">http://support.microsoft.com/kb/246133/</a><br />
<br />
<a href="http://blog.netnerds.net/2009/01/migratetransfer-sql-server-2008200520007-logins-to-sql-server-2008/">http://blog.netnerds.net/2009/01/migratetransfer-sql-server-2008200520007-logins-to-sql-server-2008/</a><br />
<br />
This needs to be run on the source sql server, the one you are copying the logins from <br />
<br />
<br />
*/<br />
<br />
----- Begin Script, Create sp_help_revlogin procedure -----<br />
<br />
<br />
<br />
USE master<br />
GO<br />
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL<br />
DROP PROCEDURE sp_hexadecimal<br />
GO<br />
CREATE PROCEDURE sp_hexadecimal<br />
@binvalue varbinary(256),<br />
@hexvalue varchar (514) OUTPUT<br />
AS<br />
DECLARE @charvalue varchar (514)<br />
DECLARE @i int<br />
DECLARE @length int<br />
DECLARE @hexstring char(16)<br />
SELECT @charvalue = '0x'<br />
SELECT @i = 1<br />
SELECT @length = DATALENGTH (@binvalue)<br />
SELECT @hexstring = '0123456789ABCDEF'<br />
WHILE (@i <= @length)<br />
BEGIN<br />
DECLARE @tempint int<br />
DECLARE @firstint int<br />
DECLARE @secondint int<br />
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))<br />
SELECT @firstint = FLOOR(@tempint/16)<br />
SELECT @secondint = @tempint - (@firstint*16)<br />
SELECT @charvalue = @charvalue +<br />
SUBSTRING(@hexstring, @firstint+1, 1) +<br />
SUBSTRING(@hexstring, @secondint+1, 1)<br />
SELECT @i = @i + 1<br />
END<br />
<br />
SELECT @hexvalue = @charvalue<br />
GO<br />
<br />
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL<br />
DROP PROCEDURE sp_help_revlogin<br />
GO<br />
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS<br />
DECLARE @name sysname<br />
DECLARE @type varchar (1)<br />
DECLARE @hasaccess int<br />
DECLARE @denylogin int<br />
DECLARE @is_disabled int<br />
DECLARE @PWD_varbinary varbinary (256)<br />
DECLARE @PWD_string varchar (514)<br />
DECLARE @SID_varbinary varbinary (85)<br />
DECLARE @SID_string varchar (514)<br />
DECLARE @tmpstr varchar (1024)<br />
DECLARE @is_policy_checked varchar (3)<br />
DECLARE @is_expiration_checked varchar (3)<br />
<br />
DECLARE @defaultdb sysname<br />
<br />
IF (@login_name IS NULL)<br />
DECLARE login_curs CURSOR FOR<br />
<br />
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM<br />
sys.server_principals p LEFT JOIN sys.syslogins l<br />
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'<br />
ELSE<br />
DECLARE login_curs CURSOR FOR<br />
<br />
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM<br />
sys.server_principals p LEFT JOIN sys.syslogins l<br />
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name<br />
OPEN login_curs<br />
<br />
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin<br />
IF (@@fetch_status = -1)<br />
BEGIN<br />
PRINT 'No login(s) found.'<br />
CLOSE login_curs<br />
DEALLOCATE login_curs<br />
RETURN -1<br />
END<br />
SET @tmpstr = '/* sp_help_revlogin script '<br />
PRINT @tmpstr<br />
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'<br />
PRINT @tmpstr<br />
PRINT ''<br />
WHILE (@@fetch_status <> -1)<br />
BEGIN<br />
IF (@@fetch_status <> -2)<br />
BEGIN<br />
PRINT ''<br />
SET @tmpstr = '-- Login: ' + @name<br />
PRINT @tmpstr<br />
IF (@type IN ( 'G', 'U'))<br />
BEGIN -- NT authenticated account/group<br />
<br />
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'<br />
END<br />
ELSE BEGIN -- SQL Server authentication<br />
-- obtain password and sid<br />
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )<br />
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT<br />
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT<br />
<br />
-- obtain password policy state<br />
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name<br />
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name<br />
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'<br />
<br />
IF ( @is_policy_checked IS NOT NULL )<br />
BEGIN<br />
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked<br />
END<br />
IF ( @is_expiration_checked IS NOT NULL )<br />
BEGIN<br />
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked<br />
END<br />
END<br />
IF (@denylogin = 1)<br />
BEGIN -- login is denied access<br />
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )<br />
END<br />
ELSE IF (@hasaccess = 0)<br />
BEGIN -- login exists but does not have access<br />
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )<br />
END<br />
IF (@is_disabled = 1)<br />
BEGIN -- login is disabled<br />
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'<br />
END<br />
PRINT @tmpstr<br />
END<br />
<br />
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin<br />
END<br />
CLOSE login_curs<br />
DEALLOCATE login_curs<br />
RETURN 0<br />
GO<br />
<br />
<br />
----- End Script, Create sp_help_revlogin procedure -----<br />
<br />
<br />
/* <br />
<br />
<h3>Transfer logins in SQL Server 2005 - Step 2 </h3><br />
Run the following script on the source SQL Server. After you execute the SQL, this stored procedure will go ahead and generate the SQL code for your Logins. You can then copy this SQL code and execute it on the target server.<br />
<br />
*/<br />
<br />
<br />
--USE [Enter your database]<br />
<br />
EXEC master..sp_help_revlogin<br />
<br />
<br />
<br />
For our case I am using Northwind sample database. I have included a screen shot of what this looks like on my machine.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlserver2008tutorial.com/blog/transfer-logins-sql-server-2008/transfer-logins-sql-server-2005.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="http://sqlserver2008tutorial.com/blog/transfer-logins-sql-server-2008/transfer-logins-sql-server-2005.jpg" width="400" /></a></div><br />
<br />
<br />
<h3>Transfer logins SQL Server 2005 - Step 3</h3><br />
--Execute the sql code generated by the previous step on your target (destination) SQL Server, This will copy all the login information to the destination server.<br />
<br />
<br />
Here is part of the SQL script.<br />
<br />
<br />
/* sp_help_revlogin script <br />
** Generated Jun 30 2010 4:08PM on KASHMONEY-PC */<br />
<br />
<br />
-- Login: ##MS_PolicyEventProcessingLogin##<br />
CREATE LOGIN [##MS_PolicyEventProcessingLogin##] WITH PASSWORD = 0x01003869D680ADF63DB291C6737F1EFB8E4A481B02284215913F HASHED, SID = 0x0A6983CDF023464B9E86E4EEAB92C5DA, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyEventProcessingLogin##] DISABLE<br />
<br />
-- Login: ##MS_PolicyTsqlExecutionLogin##<br />
CREATE LOGIN [##MS_PolicyTsqlExecutionLogin##] WITH PASSWORD = 0x01008D22A249DF5EF3B79ED321563A1DCCDC9CFC5FF954DD2D0F HASHED, SID = 0x8F651FE8547A4644A0C06CA83723A876, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyTsqlExecutionLogin##] DISABLE<br />
<br />
-- Login: NT AUTHORITY\SYSTEM<br />
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE = [master]<br />
<br />
-- Login: NT SERVICE\MSSQLSERVER<br />
CREATE LOGIN [NT SERVICE\MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE = [master]<br />
<br />
<br />
Also we have included a screen shot right below of what it looks like:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlserver2008tutorial.com/blog/transfer-logins-sql-server-2008/transfer-logins-sql-server-2008.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="http://sqlserver2008tutorial.com/blog/transfer-logins-sql-server-2008/transfer-logins-sql-server-2008.jpg" width="400" /></a></div><br />
<br />
<h3>TAGS: SQL Server 2005, Copy SQL Logins, sp_help_revlogin, SQL Server 2005</h3>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-80891347707175685742010-06-17T07:53:00.000-07:002010-11-01T13:55:09.968-07:00Why Table Variables are better than Temporary Tables<h1>Why Table Variables are better than Temporary Tables</h1>If you are working with SQL Server tables that have millions of rows, one common technique to speed up your SQL queries and stored procedures is to either use <b>temporary tables</b> or <b>table variables</b>. There is quite a bit of debate on which is the better option, Table Variables or Temporary Tables. We will take a closer look at the two objects with SQL Profiler to decide the winner.<br />
<br />
<h2>Temporary tables</h2><br />
Temporary tables are similar to static tables, however they are not permanent database objects. Temporary tables are extremely useful as work tables for storing intermediate results and complex queries. In addition, SQL server creates and maintains them in <b>Tempdb</b> database and drops them when they are no longer needed. You can virtually do everything with a temporary table that you can do with a standard SQL Server table. You can create indexes, create defaults, modify the table and basically use the temporary table anywhere you need to use a regular persistent database table. Temporary tables can also be used in stored procedures to improve performance tuning and query optimization. There are two types of temporary tables:<br />
<br />
<h3>Local Temporary table:</h3>This is by far the most common type of a temporary table. When using this one, the scope of the temporary table is limited to the database connection that creates the temp table. The name of the local temporary table must start with a # (Hash symbol) e.g. <b>#local_temp_table</b>. The local temporary table can either be dropped explicitly (best practice), or when the database connection closes. If you are using a store procedure, the local temporary table will be dropped when the stored procedure ends execution.<br />
<br />
<h3>Global Temporary table:</h3>Temporary tables can also be shared across many connections in SQL Server. This is possible by using a global temporary table which is visible to any and all connection in SQL server. This table is only deleted when the last connection explicit drops the temporary tables. Global temporary table must begin with 2 ## signs, e.g.<b>##global_temp_table.<br />
</b>Let us take a look at some of these examples next, we are going to be using Northwind database which is a sample database for SQL server. If you would like to download this database, please visit the <a href="http://www.codeproject.com/KB/database/InstallingNorthwindAndPub.aspx">Northwind download</a> page.<br />
<br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">--Create Table #CUSTOMER_DATA</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CREATE</span></span><span style="color: black; font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TABLE</span></span><span style="font-size: x-small;"><span style="color: black;"> #CUSTOMER_DATA</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"> (</span></span><span style="font-size: x-small;"> </span><span style="font-size: x-small;">[CONTACTNAME] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">30</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[ADDRESS] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">60</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[CITY] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">15</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[COUNTRY] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">15</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<br />
<br />
<br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: 12pt;"><span lang="EN" style="font-family: "Times New Roman","serif"; font-size: 12pt;">We will compare the performance of temporary table with a table variable using SQL Server 2008 at the end of this article<br />
<br />
</span><br />
<h3><span lang="EN" style="font-family: "Times New Roman","serif"; font-size: 12pt;">Advantages of using temporary tables</span></h3><span lang="EN" style="font-family: "Times New Roman","serif"; font-size: 12pt;"> Here are some of the advantages of using temporary tables:<br />
<br />
-It is possible to create an index on a temporary table<br />
-You can create constraints on a temporary table including Primary Key, unique, NULL and check constraints<br />
-The scope of a temporary table is not only limited to the current session, instead you can extend it to all connections using global temporary table<br />
-Using Statistics is possible on a temporary table<br />
-Temporary table are best suited for big datasets which involves large amount of processing<br />
<br />
</span><br />
<h3><span lang="EN" style="font-family: "Times New Roman","serif"; font-size: 12pt;">Disadvantages of using temporary tables</span></h3><span lang="EN" style="font-family: "Times New Roman","serif"; font-size: 12pt;"> Some of the disadvantages of using temporary tables are:<br />
<br />
-For smaller data sets, temporary table are outperformed by table variables<br />
-Generally there is more overhead with temporary table as you have to create the object, populate temporary table and also log read/write operations<br />
-When using temporary table in a stored procedure, there may be incidence of recompilation <br />
<br />
</span><br />
<h3>Performance Testing between Temporary Tables and Table Variables </h3>Using Northwind sample database, we are going to create a test table called <span style="font-size: x-small;">CUSTOMER_DATA_BAK</span> which contains repeated data from <span style="font-size: x-small;">CUSTOMER_DATA</span> table. This test table has 2 million rows so we can compare temporary table with table variable for a large data set. When we run these sql queries we will run a trace in SQL Profiler to capture different parameters and statistics. We will repeat the test for these scenarios:<br />
<br />
<b>1. Temporary table without an index<br />
2. Temporary table with an index<br />
3. Table Variable</b><br />
<br />
Here we have included a transact sql script for testing the first case. It does the following things:<br />
<br />
-Clear the data and procedure cache to get a clean baseline<br />
-Create the temporary table<br />
-Populate the temporary table<br />
-Run a select query from the temporary table with formatted customer mailing list<br />
<br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">USE</span></span><span style="font-size: x-small;"> Northwind</span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--TEMP TABLE WITH NO INDEX<br />
--The first command empties the data cache and the second one empties the procedure cache</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DBCC</span></span><span style="font-size: x-small;"> DROPCLEANBUFFERS</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DBCC</span></span><span style="font-size: x-small;"> FREEPROCCACHE</span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--This displays disk activity and time of TSQL execution</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">STATISTICS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IO</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">STATISTICS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TIME</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--Create Table #CUSTOMER_DATA</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CREATE</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TABLE</span></span><span style="font-size: x-small;"> #CUSTOMER_DATA</span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"> (</span></span><span style="font-size: x-small;"> </span><span style="font-size: x-small;">[CONTACTNAME] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">30</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[ADDRESS] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">60</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[CITY] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">15</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[COUNTRY] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">15</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--Populate Temp Table #CUSTOMER_DATA</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> #CUSTOMER_DATA</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">CONTACTNAME</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ADDRESS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> CITY</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> COUNTRY</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> DBO</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">CUSTOMER_DATA_BAK</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> CITY </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">IN</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'SAN FRANCISCO'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'SEATLLE'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'ELGIN'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'LONDON'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--Select formatted data from Temp Table #CUSTOMER_DATA</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">CONTACTNAME </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">13</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ADDRESS</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">13</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">CITY </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> COUNTRY </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">13</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">13</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> PRINT_ADDRESS</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> #CUSTOMER_DATA</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> CITY </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'LONDON'</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--Explicitly drop the Temp Table #CUSTOMER_DATA</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DROP</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TABLE</span></span><span style="font-size: x-small;"> #CUSTOMER_DATA</span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"><br />
/* SQL PROFILER RESULTS<br />
<br />
CPU= 3274<br />
READS= 72194<br />
WRITES= 2297<br />
DURATION= 162519<br />
<br />
*/<br />
<br />
</span></span></div><br />
<br />
You can download this <a href="http://www.sqlserver2008tutorial.com/blog/temporary-tables/sql-temp-table.sql">sql script on temp</a> table from this location:<br />
When we look at the trace output from the SQL Profiler on temp table, the numbers are as follows:<br />
<br />
CPU=3274<br />
READS=72194<br />
WRITES=2297<br />
DURATION=162519<br />
<br />
Here is a screen shot from SQL server Profiler trace<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/temporary-tables/temp-table-profiler.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="335" src="http://www.sqlserver2008tutorial.com/blog/temporary-tables/temp-table-profiler.jpg" width="400" /></a></div><br />
<br />
http://www.sqlserver2008tutorial.com/blog/temporary-tables/temp-table-profiler.jpg<br />
<br />
<br />
<h3>Performance Testing on Temp Table with an Index</h3><br />
Next we are going to test the same process, however this time we are going to create an index on the temporary table in addition to the existing code. You can <a href="http://www.sqlserver2008tutorial.com/blog/temporary-tables/sql-temp-table-index.sql">download this sql script on temp table with index</a> from this location:<br />
<br />
We have included a partial screen shot of this transact sql code when we tested this in Management Studion in SQL Server 2008:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/temporary-tables/create-temp-table-index.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="335" src="http://www.sqlserver2008tutorial.com/blog/temporary-tables/create-temp-table-index.jpg" width="400" /></a></div><br />
<span id="goog_1767934606"></span><span id="goog_1767934607"></span>http://www.sqlserver2008tutorial.com/blog/temporary-tables/create-temp-table-index.jpg<br />
<br />
When we look at the trace on temp table with an index from the SQL Profiler, here are the numbers:<br />
<br />
CPU=6114<br />
READS=757553<br />
WRITES=3035<br />
DURATION=66602<br />
<br />
Here is a screen shot from SQL server Profiler trace<br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/temporary-tables/temp-table-index-profiler.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="336" src="http://www.sqlserver2008tutorial.com/blog/temporary-tables/temp-table-index-profiler.jpg" width="400" /></a></div><br />
<br />
http://www.sqlserver2008tutorial.com/blog/temporary-tables/temp-table-index-profiler.jpg<br />
<br />
<h2>Table variables:</h2>Table variable is simply a data variable of type table. As such Table variables is created using a declare statement. It offers an alternative approach to using temporary table and is typically faster and efficient for smaller record sets. A table variable can store results for later processing similar in concept to using a temporary table. Table variable act like a local variables and their scope ends with the batch that is currently using them.<br />
<br />
Here is an example of sql code for a Table variable<br />
<br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">--Declare Table Variable @CUSTOMER_DATA</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DECLARE</span></span><span style="font-size: x-small;"> @CUSTOMER_DATA </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TABLE</span></span><span style="font-size: x-small;"> </span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"> </span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;"> [CONTACTNAME] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">30</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[ADDRESS] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">60</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[CITY] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">15</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[COUNTRY] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">15</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<br />
<br />
<br />
<h3>Advantages of a Table variable</h3><h3><br />
</h3>Here are some of the advantages of using table variables: <br />
<br />
-In general for smaller record set, table variables outperform the temporary tables<br />
-When using a function with temporary storage, you have to use the table variable as temporary tables will not work<br />
-Table variables do not cause recompilation issues like temporary tables do<br />
-Table variables require fewer system resources causing than them to be less of a performance hit compare to temp tables<br />
<br />
<h3>Disadvantages of a table variable</h3><br />
<br />
Here are some of disadvantages of using table variables:<br />
<br />
-The table definition cannot be changed once table variable has been declared<br />
-You cannot use <b>Select Into</b> statements with table variables<br />
-The only way you can create an index in a table variable is to use a Primary Key constraint at the time of table variable declaration<br />
-New Indexes cannot be created after declaration of the table variable<br />
-Performance is slow with table variables when working with large data sets<br />
-<b>Rollback Tran and Truncate table</b> are not allowed with table variables<br />
<br />
<h3>Performance Testing on Table Variable</h3>Next we are going to repeat the process this time with a table variable. Here is the script than we are going to use:<br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">USE</span></span><span style="font-size: x-small;"> Northwind</span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--SQL TABLE VARIABLE<br />
--The first command empties the data cache and the second one empties the procedure cache</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DBCC</span></span><span style="font-size: x-small;"> DROPCLEANBUFFERS</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DBCC</span></span><span style="font-size: x-small;"> FREEPROCCACHE</span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--This displays disk activity and time of TSQL execution</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">STATISTICS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IO</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">STATISTICS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TIME</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--Declare Table Variable @CUSTOMER_DATA</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DECLARE</span></span><span style="font-size: x-small;"> @CUSTOMER_DATA </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TABLE</span></span><span style="font-size: x-small;"> </span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"> </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;"> [CONTACTNAME] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">30</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[ADDRESS] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">60</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[CITY] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">15</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL,</span></span><br />
<span style="font-size: x-small;"> </span><span style="font-size: x-small;">[COUNTRY] [NVARCHAR]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">15</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--Insert data into Table Variable @CUSTOMER_DATA</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @CUSTOMER_DATA</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">CONTACTNAME</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ADDRESS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> CITY</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> COUNTRY</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> DBO</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">CUSTOMER_DATA_BAK</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> CITY </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">IN</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'SAN FRANCISCO'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'SEATLLE'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'ELGIN'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'LONDON'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
--Select data from Table Variable @CUSTOMER_DATA</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">CONTACTNAME </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">13</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ADDRESS</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">13</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">CITY </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> COUNTRY </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">13</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">13</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> PRINT_ADDRESS</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> @CUSTOMER_DATA</span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> CITY </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'LONDON'</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> <br />
/* SQL PROFILER RESULTS<br />
<br />
CPU= 2730<br />
READS= 71000<br />
WRITES= 2289<br />
DURATION= 46184<br />
<br />
*/<br />
<br />
</span></span>Next we are going to repeat the process this time with a table variable. Here is the script than we are going to<br />
<br />
You can also download the <a href="http://www.sqlserver2008tutorial.com/blog/temporary-tables/sql-server-table-variable.sql">temp variable sql script</a> from our site<br />
We have included a screen shot of the SQL profiler results on table variables<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/temporary-tables/table-variable-profiler.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="336" src="http://www.sqlserver2008tutorial.com/blog/temporary-tables/table-variable-profiler.jpg" width="400" /></a></div><br />
<br />
<br />
<br />
http://www.sqlserver2008tutorial.com/blog/temporary-tables/table-variable-profiler.jpg<br />
<br />
When we look at the trace on temp table from the SQL Profiler output, here are the numbers:<br />
<br />
CPU=2730<br />
READS=71000<br />
WRITES=2289<br />
DURATION=46184<br />
<br />
<h3>Performance Testing Results between Temp Table and Table Variable</h3><br />
<span style="color: black; font-family: Arial; font-size: x-small;"> </span><br />
<div><table bgcolor="#ffffff" border="1" cellpadding="2" cellspacing="2"><tbody>
<tr valign="top"> <td><b></b><br />
<b></b><br />
<b>Parameter</b></td> <td><b></b><br />
<b>Temp Table (No Index)</b></td> <td><b></b><br />
<b>Temp Table (With Index)</b></td> <td><b></b><br />
<b>Table Variable</b></td></tr>
<tr valign="top"> <td>CPU</td> <td>3274</td> <td>6114</td> <td>2730</td></tr>
<tr valign="top"> <td>DURATION </td> <td>162519</td> <td>66602</td> <td>46184</td></tr>
</tbody></table></div><span style="color: black; font-family: Arial; font-size: x-small;"></span><br />
<br />
As you can see using Table Variable is a much better option as it used less CPU and took only 1/3 of time to process the same data!!<br />
<br />
For futher query optimization tricks and tips, please visit our site on <a href="http://sqlserver2008tutorial.com/member.htm">SQL Server Tutorials</a><br />
<br />
<h3>Related Links on the Topic</h3>-<a href="http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx">Temporary Tables vs. Table Variables </a><br />
-<a href="http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html">Should I use a #temp table or a @table variable?</a><br />
<br />
<br />
<br />
<br />
<br />
<h1>TAGS: Table Variables, Temporary Tables</h1>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-47531028740084191552010-05-28T19:16:00.000-07:002010-11-01T13:55:09.968-07:00Change Recovery Model of all your SQL Server databases in one shot<h2>Change Recovery Model of all your SQL Server databases in one shot</h2><br />
/*--------------------------------------------------------------------------------<br />
<br />
Sometimes it is necessary to change properties of all your databases in one shot<br />
<br />
In this script, we use a system table to get all the database names on our server. <br />
<br />
Next we use a cursor to loop through all the records and then change the recovery model using <b>Alter Database command</b><br />
<br />
--------------------------------------------------------------------------------*/<br />
<br />
--Declaration of variables<br />
<br />
declare <br />
@dbnm sysname,<br />
@sql varchar(100)<br />
<br />
<br />
-- Declare begin cursor to get the database names and get info from sys.databases catalog<br />
<br />
declare cursor_db cursor <br />
for select name from sys.databases where name != 'tempdb'<br />
<br />
-- Using a cursor to loop through database names and change recovery model<br />
<br />
open cursor_db<br />
fetch next from cursor_db into @dbnm<br />
<br />
--While Loop with Alter database command<br />
<br />
while @@fetch_status = 0<br />
<br />
begin <br />
<br />
--print 'database is ' + @dbnm<br />
<br />
set @sql='alter database ' + @dbnm + ' set recovery simple'<br />
print 'sql is ' + @sql<br />
exec (@sql)<br />
<br />
<br />
fetch next from cursor_db into @dbnm<br />
end<br />
<br />
--clean up objects<br />
<br />
close cursor_db<br />
deallocate cursor_db<br />
<br />
<br />
<br />
<h3>Tags: SQL Server 2008, Recovery Model, While loop, Cursor</h3><br />
Source:<br />
<br />
<a href="http://sqlserver2008tutorial.com/member.htm">http://sqlserver2008tutorial.com/member.htm</a>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-22845048409869328392010-05-01T12:33:00.000-07:002010-11-01T13:55:09.969-07:00SQL Joins Explained – Inner Joins and Outer Joins using SQL Server 2008<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-6444202-3']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script><br />
<br />
<h2>SQL Joins Explained – Inner Joins and Outer Joins using SQL Server 2008</h2><br />
<h4>Why do I need SQL Joins again?</h4><br />
Relational databases like SQL Server focus on the concept of “<b>Normalization</b>” which reduces data redundancy. What this really means is that each subject or group of data in a sql database should really be stored in only one table. In other words if you have customer data, you need to keep that data in CUSTOMERS table whereas if you have order detail information, this needs to be stored in an ORDERS table. Following this routine eliminates data redundancy in sql tables and makes relational databases like SQL Server efficient in processing data. At the same time it creates a challenge in pulling data together from different tables into a unified sql view. This is where SQL Joins come into play. The Joins facilitate in bringing data from various SQL tables by using T-SQL (Transact SQL) queries. In our case of CUSTOMERS and ORDERS example, a Join in SQL2008 could combine information together from both these tables in our database. We will show you how to do just that here shortly using SQL Server 2008.<br />
<br />
<b>SQL Joins</b> are able to pull information by using <b>Primary Key</b> (PK) and <b>Foreign Key</b> (FK) relationships. Primary Key is a column in a SQL table that uniquely identifies all the rows. In order to understand these concepts, we are going to use Northwind sample database from Microsoft SQL Server. We will be using a few tables from this database to walk you through the Joins in SQL. More information on this database can be found online at this location, <a href="http://www.codeproject.com/KB/database/InstallingNorthwindAndPub.aspx">Northwind database</a><br />
<br />
Coming back to SQL joins, in order to understand Primary and Foreign keys, let us take a look at this figure:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/primary_foreign_key_relationship.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="http://www.sqlserver2008tutorial.com/blog/sql-joins/primary_foreign_key_relationship.jpg" width="400" /></a></div><br />
<br />
In CUSTOMERS, the Primary (aka Parent) table, a column CustomerID with unique values can be used to find a specific customer. This column or field serves as the Primary Key for CUSTOMERS SQL table and is highlighted in blue. Similarly in the Secondary table, ORDERS, we also have a Primary Key column OrderID which is highlighted in blue. This column is the unique identifier for records in the ORDERS table. We have the data and inner join from CUSTOMERS SQL table and ORDERS SQL table in this <a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/Customers_Orders_Inner_Outer_Joins.xls">Excel Spreadsheet</a><br />
<br />
<br />
One customer in this SQL Server database can have many orders, as such the database relationship between CUSTOMERS and ORDERS table is one to many. In order to relate CUSTOMERS table to the ORDERS table using Joins in SQL 2008 Server, you would need to add another column CustomerID in the ORDERS table. This column then becomes the Foreign Key for ORDERS table and is highlighted in red. If a customer has placed any orders, this CustomerID column in ORDERS table will contain the same value as CustomerID from CUSTOMERS table. This mechanism is exactly how SQL joins help in bringing related information together from many SQL tables in a unified view. <br />
<br />
<h3>Types of SQL Joins:</h3><br />
There are two types of SQL Joins, SQL INNER Join and SQL OUTER Join. A SQL OUTER Join can be subdivided into a Left OUTER Join, Right OUTER Join and a Full OUTER join.<br />
<br />
<h4>SQL INNER Join:</h4><br />
An SQL INNER join is used to pull matching data from two tables. This is the type of join that is commonly used in databases like SQL Server. In order to understand inner joins in SQL, we are going to use <a href="http://en.wikipedia.org/wiki/Venn_diagram">Venn Diagrams</a> which are helpful in understanding the concept. Take a look at the following figure:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/inner_join_example.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="236" src="http://www.sqlserver2008tutorial.com/blog/sql-joins/inner_join_example.jpg" width="320" /></a></div><br />
<br />
<br />
Here we are representing a table by a circle. On the left side we have the CUSTOMERS circle and on the right side we have the ORDERS circle. The portion where the two circles intersect is common to both and represents Customers that have placed an Order. As such this common region represents an INNER join in SQL.<br />
<br />
We are going to show you the syntax for an INNER join query next:<br />
<br />
SELECT <i>columns</i><br />
FROM <i>table_1</i> JOIN <i>table_2</i><br />
ON <i>table_1. primarykey</i> = <i>table_2.foreignkey</i><br />
<br />
Notice the words in bold are SQL keywords and we use the ON clause to match rows from primary and secondary tables. In our database the query for inner join will look like this: <br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;"> </span><br />
SELECT<br />
CUSTOMERS.CUSTOMERID,<br />
CUSTOMERS.CompanyName,<br />
Orders.CustomerID,<br />
Orders.OrderDate<br />
FROM CUSTOMERS<br />
<b>INNER JOIN</b> ORDERS<br />
ON CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID<br />
ORDER BY CUSTOMERS.CUSTOMERID<br />
<br />
<br />
We have included an output of this data in the following screen capture.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/sql_inner_join_example.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="305" src="http://www.sqlserver2008tutorial.com/blog/sql-joins/sql_inner_join_example.jpg" width="400" /></a></div><br />
<br />
<br />
Notice that the two matching columns are highlighted in blue. We have the complete data from Customers SQL table, Orders SQL table and Inner Join in this <a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/Customers_Orders_Inner_Outer_Joins.xls">Excel Spreadsheet</a>. <br />
<br />
<h4>SQL OUTER Join:</h4><br />
A SQL OUTER join is used to bring matching and non matching data from two tables. There are two types of outer joins in SQL, Left Outer Join and Right Outer Join. <br />
<br />
<h4>Left OUTER Join: </h4><br />
In order to understand Left Outer Joins, let us study this possible scenario:<br />
<br />
-What if we wanted to find out CUSTOMERS in our database that has not placed any ORDERS so far? In other words who is not buying our great products yet?<br />
<br />
We could use a Left OUTER join to get this information. In a Left OUTER join all the data from the main table, CUSTOMERS in our case and any matching data from the ORDERS is returned. We can further explain a Left OUTER join with the following Venn diagram.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/left_outer_join_example.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="254" src="http://www.sqlserver2008tutorial.com/blog/sql-joins/left_outer_join_example.jpg" width="320" /></a></div><br />
<br />
As you can see from the blue line with arrows, a Left OUTER join will not only include the intersection of the two circles (matching data) but that also the portion from Customers circle that is not matching with any Orders. <br />
<br />
Here’s the join query for Left Outer Join in SQL Server database.<br />
<br />
SELECT<br />
CUSTOMERS.CUSTOMERID,<br />
CUSTOMERS.CompanyName,<br />
Orders.CustomerID,<br />
Orders.OrderDate<br />
FROM CUSTOMERS<br />
<b>LEFT OUTER JOIN</b> ORDERS<br />
ON CUSTOMERS.CUSTOMERID=ORDERS.CUSTOMERID<br />
WHERE Orders.CustomerID IS NULL<br />
<br />
The above sql query will return 3 customers that do not have any orders yet. The screen shot from SQL Server Management is shown below<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/sql-left-outer-join.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="305" src="http://www.sqlserver2008tutorial.com/blog/sql-joins/sql-left-outer-join.jpg" width="400" /></a></div><br />
<br />
<br />
One thing we wanted to point out is the last optional line (using IS NULL). This condition forces SQL Server to return Customer rows with no Orders. We have the complete data from Customers SQL table, Orders SQL table and Left Outer Join in this <a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/Customers_Orders_Inner_Outer_Joins.xls">Excel Spreadsheet</a>. <br />
<br />
<h4>Right OUTER Join:</h4>Next we are going to look at Right Outer Join which is the logical opposite of a Left Outer Join. Here’s the scenario:<br />
<br />
-What if we were trying to figure out which CATEGORIES in our SQL database have been added recently that do not have any corresponding PRODUCTS yet. How can we solve this problem using a Right Outer Join?<br />
<br />
Let’s look at the Venn diagram shown below:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/right_outer_join_example.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="270" src="http://www.sqlserver2008tutorial.com/blog/sql-joins/right_outer_join_example.jpg" width="320" /></a></div><br />
<br />
Here you will see that the Right Outer Join is highlighted in dark orange pointed by the blue line. This area includes all the records from CATEGORIES table and matching rows from PRODUCTS table. If we were to write the transact SQL for this Right Outer Join, it would be as follows:<br />
<br />
SELECT <br />
PRODUCTS.PRODUCTID, <br />
PRODUCTS.PRODUCTNAME, <br />
CATEGORIES.CATEGORYID, <br />
CATEGORIES.CATEGORYNAME<br />
FROM PRODUCTS <br />
<b>RIGHT OUTER JOIN</b><br />
CATEGORIES <br />
ON PRODUCTS.CATEGORYID = CATEGORIES.CATEGORYID<br />
WHERE PRODUCTS.CATEGORYID IS NULL<br />
<br />
The above sql query will return 2 categories that do not have any products yet. Once again the optional IS NULL condition on the last line limits only Categories without any products. If you omit the WHERE clause, you will get a true right join in line with the Venn diagram. A screen capture of this result is as follows.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/sql-right-outer-join.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="sql right outer join" border="0" height="305" src="http://www.sqlserver2008tutorial.com/blog/sql-joins/sql-right-outer-join.jpg" width="400" /></a></div><br />
<br />
We have the complete data from CATEGORIES SQL table, PRODUCTS SQL table, Left Outer Join and Right Outer Join in this <a href="http://www.sqlserver2008tutorial.com/blog/sql-joins/Products_Categories_Right_Outer_Joins.xls">Excel Spreadsheet</a>.<br />
<br />
<br />
<h3>Related Links on Inner and Outer Joins</h3><br />
-<a href="http://www.sql-tutorial.com/sql-join-sql-tutorial/">SQL JOIN</a><br />
<br />
-<a href="http://www.w3schools.com/sql/sql_join.asp">Examples of Inner and Outer Joins</a><br />
-<a href="http://sqlserver2008tutorial.com/member.htm">Free SQL Server Tutorials</a><br />
<br />
-<a href="http://www.orafaq.com/wiki/Equi_join">Inner joins in Oracle</a><br />
<br />
<br />
<br />
<h4>TAGS: SQL, SQL JOIN, INNER JOIN, OUTER JOIN, SQL Server, Primary Key, Foreign Key </h4><br />
<h4><br />
</h4><br />
Kash<br />
<br />
<br />
<a href="http://www.learningsqlserver2008.com/">Learning SQL server 2008.com</a>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-30754903105075625702010-04-28T09:38:00.000-07:002010-11-01T13:55:09.969-07:00How to check SQL Server Error logs<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><br />
<h1>How to check SQL Server Error logs</h1><br />
When you are trying to troubleshoot problems in SQL Server, one of the best places to check is SQL Server Error logs. These will have basic information on the issue, what was the source of the problem, what time did it occur so on and so forth. In order to get to the SQL Server Error log, you would need to do connect to the instance and the following:<br />
<br />
Server - Management - SQL Server Logs - Current<br />
<br />
I have included a screen shot of this right below:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlserver2008tutorial.com/sql_server/sql_server_error_log.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="http://sqlserver2008tutorial.com/sql_server/sql_server_error_log.jpg" width="400" /></a></div><br />
<br />
<br />
<br />
<br />
When you go ahead and double click on one of the SQL Server Error logs, it will open up a new Log File Viewer. This will not only contain information on SQL Server Logs, but also SQL Server Agent Logs, Database Mail and Windows Logs like Application, Security and System Logs. Here’s a screen capture of what I’m talking about:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://sqlserver2008tutorial.com/sql_server/sql_log_file_viewer.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="http://sqlserver2008tutorial.com/sql_server/sql_log_file_viewer.jpg" width="640" /></a></div><br />
<br />
You will notice that on the left pane, you can choose which particular log you want to work with. Within this log, you will see a Current Log and a set of Archive Logs. Every time SQL Server or the SQL Server Agent is restarted, SQL Server goes ahead and recycles the log and creates a new one. In this manner you can go back in time to see what issues happened with SQL Server.<br />
<br />
On the right side you will have the details pane with information on particular SQL Server related event, when it happened? what was the source? which SQL Server process generated it ETC. You can further filter these results if you like, you can search for a particular string and even export this SQL Server log to a text file if you need to save it or email it to somebody.<br />
<br />
Sometimes the SQL Server error log does not load properly using the SQL Server Management Studio. For these occasions, SQL Server has an undocumented feature that will let you let you load up SQL Server logs and SQL Server Agent logs using an extended stored procedure. The procedure is this one xp_readerrorlog and I have included an example of how to use this.<br />
<br />
<br />
<br />
/*------------------------------------------------------------------------------------------------------------<br />
<br />
In this script we are looking at two important stored procedures<br />
<br />
-master..xp_readerrorlog<br />
-master..xp_fixeddrives<br />
<br />
we cover xp_cmdshell in this script using_xp_cmdshell_0011.sql<br />
<br />
------------------------------------------------------------------------------------------------------------*/<br />
<br />
<br />
<br />
--reads the current sql server log<br />
<br />
exec master..xp_readerrorlog<br />
exec master..xp_readerrorlog 0, 1<br />
<br />
<br />
--reads the previous sql server log<br />
<br />
exec master..xp_readerrorlog 1, 1<br />
<br />
<br />
<br />
<br />
--reads the current sql server agent log<br />
<br />
exec master..xp_readerrorlog 0, 2<br />
<br />
<br />
--reads the current sql server agent log<br />
<br />
exec master..xp_readerrorlog 1, 2<br />
<br />
<br />
<br />
--get information on disk drives from within sql server<br />
<br />
<br />
exec master..xp_fixeddrives<br />
<br />
/* --OUTPUT<br />
<br />
C 30371<br />
D 25803<br />
Q 1011<br />
R 189<br />
S 80834<br />
T 19013<br />
<br />
*/<br />
<br />
<br />
For more information on SQL Server tips and tricks, please visit our site on SQL Server 2008<br />
<a href="http://sqlserver2008tutorial.com/">http://sqlserver2008tutorial.com/</a><br />
<br />
<br />
<h2>TAGS include SQL Server Error logs, SQL Server Agent logs, Windows NT Log</h2><br />
PS. Happy Birthday baby Sofia, she turns 6 today!!Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-73161449474795104732010-04-13T13:32:00.000-07:002010-11-01T13:55:09.969-07:00Timeout expired. The timeout period elapsed prior to completion of the operation<h2>Timeout expired. The timeout period elapsed prior to completion of the operation </h2><br />
This is by far my favorite error in Microsoft SQL Server 2008 to date!!!<br />
Let us say that you are trying to modify an existing table using SQL server management studio. Using SSMS Object Explorer, you browse down to the Database, Tables down to the specific table. Right click on the table and select Modify. Next you find the field that needs to be renamed or maybe the size needs to be changed or worse the field needs to be a new data type (yes this does happen in REAL life all the time). Regardless you go ahead and make the change and then try to save your work and then BAMM!! You get this nice wonderful error. In my case I was trying to add a Primary key to a table when this happened. I have included screen shots and some description here.<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9xK1pQwOu4zPd8l9NoA1SfNMXIFrutCS6MALFv_t6Qbne-VKKW4rZRllcbL59-9NmsvMTLYfE5m74LYwMYBshtVztaDJ9AzPUo5Rg1UKYGQaCa5WgRYZurj7ydfxLQlMb2l8b814YH1U/s1600/SSMS_Timeout_error.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9xK1pQwOu4zPd8l9NoA1SfNMXIFrutCS6MALFv_t6Qbne-VKKW4rZRllcbL59-9NmsvMTLYfE5m74LYwMYBshtVztaDJ9AzPUo5Rg1UKYGQaCa5WgRYZurj7ydfxLQlMb2l8b814YH1U/s320/SSMS_Timeout_error.jpg" wt="true" /></a></div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><em>'stock_price_historical2' table</em><br />
<em></em><br />
<em><br />
</em><br />
<em>- Unable to create index 'PK_stock_price_historical'. </em><br />
<em><br />
</em><br />
<em>Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</em><br />
<em><br />
</em><br />
<em>Could not create constraint. See previous errors.</em><br />
<em>--Error 2</em><br />
<br />
<em>===================================</em><br />
<br />
Next I get this fine dialog box show below with error description after it;<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0GLeErfl_QbYIAlhCEmfMoCbpKJ_hsYEslVL8xr8hHRhyphenhypheneDkU0RAUzORbE9WapieCFls_HJHufRvW-6UknLbWLZ0SRU-hUkc8hSBEb_Gr1v6een4kVq-n9HB94-eUBJQOH94nVKOMWTk/s1600/SSMS_error_2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0GLeErfl_QbYIAlhCEmfMoCbpKJ_hsYEslVL8xr8hHRhyphenhypheneDkU0RAUzORbE9WapieCFls_HJHufRvW-6UknLbWLZ0SRU-hUkc8hSBEb_Gr1v6een4kVq-n9HB94-eUBJQOH94nVKOMWTk/s320/SSMS_error_2.jpg" wt="true" /></a></div><br />
<br />
<em>User canceled out of save dialog</em><br />
<em></em><br />
<em><br />
</em><br />
<em>(MS Visual Database Tools)</em><br />
<br />
<em>------------------------------</em><br />
<em><br />
</em><br />
<em>Program Location:</em><br />
<em><br />
</em><br />
<em>at Microsoft.SqlServer.Management.DataTools.Interop.IDTDocTool.Save(Object dsRef, String path, Boolean okToOverwrite)</em><br />
<em><br />
</em><br />
<em>at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.DatabaseDesignerNode.Save(VSSAVEFLAGS dwSave, String strSilentSaveAsName, IVsUIShell pIVsUIShell, IntPtr punkDocDataIntPtr, String& strMkDocumentNew, Int32& pfCanceled)</em><br />
<br />
So how do you get around this issue. One option is to change the timeout limit under Tools - Options. In my case it is already set to 0 (infinite!) <br />
<br />
This is what I have discovered when wrestling with the issue in SQL Server Management Studio. If you go ahead and make the change using the graphic user interface, then right before saving the change, you can go ahead and generate a SQL script that will encompass the modification that you are trying to make through SQL 2008 Management Studio. I know the real DBA'S as are saying, why even bother with this and use the TRANSACT SQL (TSQL) from the getgo. Good point and I am in agreement here 100%. However if you are new to these DBA tasks, this may be your only option. <br />
<br />
Getting back to the point, I’m going to show you an example where I was trying to add a primary key to my table, a simple task that you would think, however I’m getting my favorite error so this is what I’m going to do next. I’m going to go to modify the table, right click and choose <strong>Set Primary Key</strong> as shown below.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRbx9CrF4DBzT7SQKs6liuzF5S4xectKsGk-ja7yZ6RCloZuS-2J5v4b0Z4e3kCVamaDh06KbrCjNK-x4ghghfmV1sUwokJV4zQGcNQjxZFHLm9dGS-pxiu35gGKf1GN4Xnlu3OgsIeQA/s1600/SSMS_SET_PRIMARY_KEY.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRbx9CrF4DBzT7SQKs6liuzF5S4xectKsGk-ja7yZ6RCloZuS-2J5v4b0Z4e3kCVamaDh06KbrCjNK-x4ghghfmV1sUwokJV4zQGcNQjxZFHLm9dGS-pxiu35gGKf1GN4Xnlu3OgsIeQA/s320/SSMS_SET_PRIMARY_KEY.jpg" wt="true" /></a></div>I <br />
<br />
Next instead of saving this change, I’m going to right click in the empty space and select <strong>Generate Change Script</strong>. This is also shown below so you can see this in action.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMVILKVZvKv0vZZSJvDjdhyiIIWg9VidnQgVbJreJX8mh09RdL9jA1QvZ7oQzm7f7medkXq64aTGNJSHxGswbljF1BeNGPUFaDWL-leLH5Bv2uWFD3Y6LI26JDeOHmYrgiJNGWVsKg9Mg/s1600/SSMS_CHANGE_SCRIPT.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMVILKVZvKv0vZZSJvDjdhyiIIWg9VidnQgVbJreJX8mh09RdL9jA1QvZ7oQzm7f7medkXq64aTGNJSHxGswbljF1BeNGPUFaDWL-leLH5Bv2uWFD3Y6LI26JDeOHmYrgiJNGWVsKg9Mg/s320/SSMS_CHANGE_SCRIPT.jpg" wt="true" /></a></div><br />
SQL server will then go ahead and generate the change script including all the necessary sql code that you need. You can either select the portion that you like or you can save the whole SQL code to a SQL script. In our case I have just highlighted the portion (ALTER TABLE) that I need.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9IXfFGPF4JL58Ur74WtF6dptlINqEzXv6L9QaspKKOPgNF97Y4LQ1kHPYPq3bQRyk0PDb__2OO5bUH1XeLyZBiDMuFVX9bEPjdMUKdRZKzciiw1Ry3Uv29RAqDcft5UwysMYIMKQokA8/s1600/SSMS_ALTER_TABLE.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9IXfFGPF4JL58Ur74WtF6dptlINqEzXv6L9QaspKKOPgNF97Y4LQ1kHPYPq3bQRyk0PDb__2OO5bUH1XeLyZBiDMuFVX9bEPjdMUKdRZKzciiw1Ry3Uv29RAqDcft5UwysMYIMKQokA8/s320/SSMS_ALTER_TABLE.jpg" wt="true" /></a></div><br />
<br />
Finally you can open up a new SQL window, paste the code as shown above and execute the bad boy!!Pretty cool to get around the Annoying Timeout error in my opinion. Hope that helps. <br />
<br />
Tags:Timeout expired. The timeout period elapsed prior to completion of the operationKashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com18tag:blogger.com,1999:blog-7502872312575890871.post-61131763679759755032010-04-05T19:04:00.000-07:002010-11-01T13:55:09.970-07:00How do you do loop through records one at a time in SQL server?<h2>How do you do loop through records one at a time in SQL server?</h2><br />
<p>This task is quite elementary when it comes to regular programming, however this can be challenging when we are talking about programming in SQL server. The reason for this is that Transact SQL is best at fetching recordsets altogether and not one record at a time. It is optimized for performance and hence is the happiest when working with a bunch of records.<br />
<br />
<p>Nevertheless we need to be able to loop through records one at a time. You may want to get customer id from a CUSTOMER table, then use this primary key to pull related data from the Orders table. One way you can do this is using CURSORS which will offer you the ability to loop through records one at a time. Cursors should never be your first choice as they can add a performance hit when it comes to database tuning. Instead you could use while loop or temp tables to do the same action.<br />
Regardless for this blog post I am including an example of a CURSOR that will loop through all the databases and then switch RECOVERY mode to SIMPLE, one by one. <br />
<br />
<hr><br />
--declaration of variables<br />
<br />
declare <br />
@dbnm sysname,<br />
@sql varchar(100)<br />
<br />
<br />
-- begin cursor to get the database names<br />
<br />
declare cursor_db cursor <br />
for select name from sys.databases where name != 'tempdb'<br />
<br />
-- using a cursor to loop through database names and change recovery model<br />
<br />
open cursor_db<br />
fetch next from cursor_db into @dbnm<br />
<br />
while @@fetch_status = 0<br />
<br />
begin <br />
<br />
--print 'database is ' + @dbnm<br />
<br />
set @sql='alter database ' + @dbnm + ' set recovery simple'<br />
print 'sql is ' + @sql<br />
exec (@sql)<br />
<br />
<br />
fetch next from cursor_db into @dbnm<br />
end<br />
<br />
close cursor_db<br />
deallocate cursor_dbKashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-73407015001106669382010-03-11T14:43:00.000-08:002010-11-01T13:55:09.970-07:00Using SQL SELECT statement with Transact SQL or TSQL<h1>Using SQL SELECT statement with Transact SQL or TSQL</h1><br />
SELECT sql statement returns the data from SQL tables. Does not make any changes to the underlying data. It is the most commonly used SQL statement. This statement can use a sub-select (sub query). Here is the syntax<br />
<br />
<i>SELECT fields FROM tables WHERE condition ORDERBY fields</i><br />
<br />
Examples from the Northwind database<br />
If you would like to download a copy of this Microsoft sample database here is the link:<br />
<br />
<a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en">Download Northwind sample database </a><br />
<br />
<br />
<h3>Returning all data from a table</h3><br />
• SELECT * FROM CUSTOMERS returns all the customer records<br />
<br />
<br />
<br />
<h3>Using a SQL WHERE clause</h3><br />
• SELECT * FROM CUSTOMERS WHERE COUNTRY='USA' return customers in USA<br />
<br />
<br />
<h3>Using SQL ORDER BY clause</h3><br />
• SELECT * FROM CUSTOMERS ORDER BY COMPANYNAME sorts by Company Name in Ascending Order<br />
<br />
<br />
<h3>Using a DISTINCT clause to eliminate duplicates</h3><br />
• SELECT DISTINCT(COUNTRY) FROM CUSTOMERS will return all the unique values for Country field<br />
<br />
<br />
<h3>Using OR in WHERE clause</h3><br />
• SELECT * FROM CUSTOMERS WHERE COUNTRY='USA' OR COUNTRY='UK' returns customers in USA or UK<br />
<br />
<br />
<br />
<h3>Using the SQL IN clause (similar to OR clause)</h3><br />
• SELECT * FROM CUSTOMERS WHERE COUNTRY IN ('USA','UK') same as above<br />
<br />
<br />
<br />
<h3>Using LIKE clause to look for patterns</h3><br />
• SELECT * FROM CUSTOMERS WHERE CONTACTNAME LIKE 'JO%' returns Customers with name beginning with the pattern ‘JO___’ e.g. JOHN would be returned<br />
<br />
<br />
<br />
<h3>Using my fav IS NULL clause</h3><br />
• SELECT * FROM CUSTOMERS WHERE REGION IS NULL will give all the customer where the Region is missing or not known<br />
<br />
<br />
<br />
<h3>Searching for numeric value </h3><br />
• SELECT CUSTOMERID FROM ORDERS WHERE EMPLOYEEID=5 returns the Customer ID from the Orders table where EmployeeID is 5<br />
<br />
<br />
<br />
<h3>Working with Dates in Transact SQL</h3><br />
• SELECT * FROM ORDERS WHERE ORDERDATE=’ 1996-07-04’ checks for the all the orders for 7/4/1996<br />
<br />
<br />
<br />
<h3>Using SQL COUNT clause</h3><br />
• SELECT COUNT(*) FROM CUSTOMERS returns the number of records in Customer table. will return number<br />
<br />
<br />
<br />
<h3>Using SQL MIN function</h3><br />
• SELECT MIN(UNITPRICE) FROM PRODUCTS; will return the lowest priced product (a number)<br />
<br />
<br />
<h3>Using SQL MAX function</h3><br />
• SELECT MAX(UNITPRICE) FROM PRODUCTS; will return the highest priced product (a number)<br />
<br />
<br />
<h3>Using SQL AVG clause to compute Average</h3>• SELECT AVG(UNITPRICE) FROM PRODUCTS; will return the average Unit Price (a number)<br />
<br />
<br />
<br />
<h3>Using SQL SUM function</h3><br />
• SELECT SUM(Quantity) FROM [ORDER DETAILS] WHERE ORDERID=10258 will sum all the items for this order<br />
<br />
<br />
<h3>Using GROUP BY HAVING clause, mainly used for grouping data</h3><br />
• SELECT ProductID, SUM(Quantity) AS Total_Quantity FROM [ORDER DETAILS] GROUP BY ProductID HAVING SUM(Quantity) >1000 <br />
<br />
will return two things: one the product id and second the sum of quantity gouped by Product ID where number of products ordered is greater than 1000Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-76743668438859954452010-03-05T12:58:00.001-08:002010-11-01T13:55:09.970-07:00How do I change owner for SQL Server jobs to SA account--How do I change owner for SQL Server jobs to SA account<br /><br />/*<br /><br />At work, I am in the process of migrating a sql server machine with all the objects to a new SQL server.<br />As it turns out we have more than 200 jobs that are created by different DBAs and developers!! One of my task<br />list is to change all the job owners to SA account. How am I going to do that? I’m going to use the stored<br />procedure sp_update_job to do this. Also I’m going to use a combination of temporary table and while loop,<br />so let’s go ahead and take a look of this next.<br /><br />Syntax from MSDN<br /><br />sp_update_job [ @job_id =] job_id | [@job_name =] 'job_name'<br /> [, [@new_name =] 'new_name' ]<br /> [, [@enabled =] enabled ]<br /> [, [@description =] 'description' ]<br /> [, [@start_step_id =] step_id ]<br /> [, [@category_name =] 'category' ]<br /> [, [@owner_login_name =] 'login' ]<br /> [, [@notify_level_eventlog =] eventlog_level ]<br /> [, [@notify_level_email =] email_level ]<br /> [, [@notify_level_netsend =] netsend_level ]<br /> [, [@notify_level_page =] page_level ]<br /> [, [@notify_email_operator_name =] 'email_name' ]<br /> [, [@notify_netsend_operator_name =] 'netsend_operator' ]<br /> [, [@notify_page_operator_name =] 'page_operator' ]<br /> [, [@delete_level =] delete_level ]<br /> [, [@automatic_post =] automatic_post ]<br /> <br /><br />*/<br /><br /><br />use master<br />go<br /><br />set nocount on<br /><br /><br />--Declaration of variables<br /><br /><br />declare<br /><br />@jobid uniqueidentifier,<br />@rec_count int,<br />@loop_counter int<br /><br /><br />--create temp table to hold the information on job id. We will pull this information from sysjobs table in<br />--the msdb database<br /><br />create table<br />#t_sqljobs<br />(<br /><br />id int identity(1,1),<br />job_id uniqueidentifier,<br />owner_sid varbinary(100)<br />)<br /><br />--insert the data into a temporary table from sysjobs table<br /><br />insert #t_sqljobs(job_id, owner_sid)<br />select job_id, owner_sid<br />from msdb..sysjobs<br /><br />--Set up the loop counters so we can loop through all the jobs in the temporary table<br /><br />set @loop_counter=1<br />select @rec_count=COUNT(*) from #t_sqljobs<br /><br /><br />--Using a while loop to go through all the records. We first get the job id and then using sp_update_job procedure,<br />--will change the job owner to SA<br /><br /><br />while @loop_counter<=@rec_count<br /><br />begin<br /><br /> select @jobid=job_id from #t_sqljobs where id=@loop_counter<br /> --print 'job id is ' + cast (@jobid as varchar(100))<br /> exec msdb.dbo.sp_update_job @job_id = @jobid, @owner_login_name ='sa'<br /><br /> set @loop_counter=@loop_counter+1<br /> <br />end<br /><br />--Cleanup and drop the temporary table<br /><br /> <br />drop table #t_sqljobsKashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-50696275979849323662010-03-04T18:02:00.000-08:002010-11-01T13:55:09.971-07:00Ok so I was on vacation!!Not really, just bz with work, school (getting a MIS degree), family, ..............<br />I am back and will try to add stuff here regularly.<br /><br />KashKashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-3529474905030859242008-07-22T13:01:00.000-07:002010-11-01T13:54:54.043-07:00Useful common sql commands for daily dba grind<span style="color:#009900;"></span><br />
<span style="color:#009900;">--get the name of database files</span><br />
<br />
select * from sysfiles<br />
<br />
<span style="color:#009900;">--C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\nwind_report.mdf<br />
--C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\nwind_report_log.ldf<br />
</span><br />
<span style="color:#009900;">--get version and name of SQL Server you are running</span><br />
<span style="color:#009900;"></span><br />
select @@version<br />
select @@servername<br />
select serverproperty('INSTANCENAME')<br />
select '\\' + @@servername<br />
select '\\' + cast(serverproperty('servername')as varchar)<br />
<br />
<span style="color:#009900;">--Find active processes and related commands</span><br />
<br />
sp_who2 active<br />
kill 60<br />
dbcc inputbuffer(61)<br />
<br />
<span style="color:#009900;">--one of favorites commands. used when log file runs out of space</span><br />
<span style="color:#009900;"></span><br />
backup log database with no_log<br />
<br />
<span style="color:#009900;">--some useful extended stored procedures</span><br />
<br />
master..xp_fixeddrives<br />
master..xp_readerrorlog<br />
<br />
<span style="color:#009900;">--old school system objects</span><br />
<br />
select * from sysobjects<br />
where type='u'<br />
select * from sysdatabases<br />
where name not in ('northwind', 'pubs', 'tempdb')<br />
<br />
<span style="color:#009900;">--text for a stored procedure</span><br />
<br />
sp_helptext cs_Process_t_IPContact<br />
<br />
<span style="color:#009900;">--xp_cmdshell at work</span><br />
<br />
master..xp_cmdshell 'dir c:\' --12GB<br />
master..xp_cmdshell 'dir d:\' --12GB<br />
master..xp_cmdshell 'dir data\dba'<br />
<br />
<script src="http://www.assoc-amazon.com/s/link-enhancer?tag=sl09d-20&o=1" type="text/javascript"></script>Kashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0tag:blogger.com,1999:blog-7502872312575890871.post-21918616450552454702008-07-22T12:52:00.000-07:002010-11-01T13:55:09.971-07:00Welcome to my Slacker DBA blogHope all is well with you and the world.<br /><br />I am sitting here working on this blog even though I could be working on important and vital DBA related stuff. Oh well, it can wait. I was aimlessly surfing the net (Does anyone use that term anymore), when I came across this blogger site. Figured I could do this on the side. It may add a little excitement to my <br />mundane life.<br /><br />So here we go. I am a Senior DBA at an IT company. Most days I work with SQL Server, although I have indulged into Oracle and Sybase as well. I would also consider myself a lazy dba as I hate to do stuff that has already been done by someone more intelligent and aspiring than me.<br /><br />With that introduction, I am officially open for business. Feel free to send in comments and database related stuff.<br /><br />KashKashhttp://www.blogger.com/profile/07129693765695387017noreply@blogger.com0