tag:blogger.com,1999:blog-11446965345626018332024-03-05T23:36:57.458+05:30MS SQL EDGEALL THINGS SQL …… ( AND SOME NOT SO MUCH ! ) Unknownnoreply@blogger.comBlogger7125tag:blogger.com,1999:blog-1144696534562601833.post-72437158572237468112000-01-01T00:06:00.000+05:302014-09-29T21:09:39.071+05:30POST MIGRATION AND UPGRADE STEPS<span style="font-family: Arial, Helvetica, sans-serif;">I recently performed a migration exercise for one of my clients wherein they upgraded their SQL Server versions. This has taught me a lot and in this post i shall list some of the post migration steps that need to be performed. I'll also give a list of a few links along the way where these points have been discussed in detail and hence i need not re-invent the wheel by writing them here again. </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">So here goes my list (since i have provided the links, i have refrained from describing the points in detail). The intent is to enable readers to use this as a quick checklist whenever they perform a migration or upgrade SQL Server instances. </span><br />
<span style="font-family: Arial;"></span><br />
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"> - Transfer Logins</span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><div style="text-align: left;">
- Service Packs, upgrades and hotfixes </div>
<div style="text-align: left;">
- Run DBCC UPDATEUSAGE on all databases to correct any incorrect row or page counts. </div>
<div style="text-align: left;">
- Change authentication mode if required </div>
<div style="text-align: left;">
- Change database compatibility level if required </div>
<div style="text-align: left;">
- Connection string change in applications </div>
<div style="text-align: left;">
- Transfer Jobs - Remove @uid parameter </div>
<div style="text-align: left;">
- Update Statistics - See point 6 in link below (QueryPlan needs to have latest data). Done<br />
by default anyways.</div>
<div style="text-align: left;">
<a href="http://thomaslarock.com/2013/03/upgrading-to-sql-2012-ten-things-you-dont-want-to-miss/">http://thomaslarock.com/2013/03/upgrading-to-sql-2012-ten-things-you-dont-want-to-miss/</a></div>
<div style="text-align: left;">
- DBCC reindex etc<br />
- Configure HA<br />
- Run DBCC CheckDB on all databases to check consistency</div>
<div style="text-align: left;">
<a href="http://msdn.microsoft.com/en-in/library/ms143532(v=sql.110).aspx">http://msdn.microsoft.com/en-in/library/ms143532(v=sql.110).aspx</a></div>
<div style="text-align: left;">
- Enable full text indexing (disabled after upgrade)</div>
<div style="text-align: left;">
<a href="http://technet.microsoft.com/en-us/library/ms142536(v=sql.105).aspx">http://technet.microsoft.com/en-us/library/ms142536(v=sql.105).aspx</a></div>
</span><br />
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<br /></div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-1144696534562601833.post-89921237210355666792000-01-01T00:05:00.000+05:302014-09-29T20:44:10.277+05:30CREATE TEMP TABLES ON THE FLY IN STORED PROCEDURES<span style="font-family: Arial, Helvetica, sans-serif;">There are instances where we need to create temp tables in a stored procedure. For example lets say i have a table Person which looks somewhat like this</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEill4ipa0F5TnIIJhicrMdPN2P5i_5pp54SJczfT8yw7EhJT7sQp59Do3Wwi6Zns-_av8f2Rz5jah-I_h5uNy70aZLphyphenhyphenQNapckTNbKXRvYQ6BOzYPXG5y6ur__WkNnnI-wal-XHxvdDms/s1600/Person1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEill4ipa0F5TnIIJhicrMdPN2P5i_5pp54SJczfT8yw7EhJT7sQp59Do3Wwi6Zns-_av8f2Rz5jah-I_h5uNy70aZLphyphenhyphenQNapckTNbKXRvYQ6BOzYPXG5y6ur__WkNnnI-wal-XHxvdDms/s1600/Person1.JPG" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Now if i were to create a temp table #Person in a stored proc to store the contents of the above table temporarily for some reason, i will need to create the table with the same structure as the table whose contents i want to store before i can actually use it in my stored proc, that is somewhat like below:</span><br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">CREATE TABLE #Person<br />(<br /> Name varchar(50),<br /> Surname varchar(50), <br /> State varchar(50)<br />)</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">An easier way will be to use the following code</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Courier New", Courier, monospace;">IF OBJECT_ID('tempdb..#Person') IS NULL<br />select * into #Person from [dbo].[Person]<br />else<br />truncate table #Person<br />insert into #Person select * from [dbo].[Person]</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">The above code checks for the presence of the temp table first (from an earlier execution in the same session let's say). If it doesn't find one, it automatically creates it with the same structure as the destin ation table and copies the data. It it is present, the temp table is truncated to clear existing values and the insert happens subsequently.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />Unknownnoreply@blogger.com9tag:blogger.com,1999:blog-1144696534562601833.post-71809477784770952662000-01-01T00:04:00.000+05:302014-09-29T20:14:29.826+05:30MIRRORING BREAKAGE SCENARIOS<span style="font-family: Arial, Helvetica, sans-serif;">In this post i give a summary of what to do when mirrorring breaks down due to some reason. Here lets assume that the Principal is A and the Mirror is B.</span><br />
<strong><span style="font-family: Arial;"></span></strong><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><strong><u>Principal Lost</u></strong> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /><u>Safety Full With Witness</u><br />
<br />
B is the new Principal now and the mirroring state is DISCONNECTED. When A becomes online, it resumes role as a Mirror automatically (ie we dont have to set up mirroring all over again) except that the session remains suspended until a resume command is issued in B (the new Principal). To do this run the following command in B (the new Principal). <br />
<br />
<span style="font-family: "Courier New", Courier, monospace;">ALTER DB SET PARTNER RESUME</span> - no re-establishing required <br />
<br />
<u>Safety Full Without Witness</u><br />
<br />On mirror, execute the following <br />
<br /><span style="font-family: "Courier New", Courier, monospace;">ALTER DB SET PARTNER OFF <br />RESTORE DB WITH RECOVERY</span> <br />
<br />Then when A becomes online, re-establish mirroring <br />
<br />OR <br />
<br /><span style="font-family: "Courier New", Courier, monospace;">ALTER DB SET PARTNER FORCE service_allow_data_loss</span> ###<br /><span style="font-family: "Courier New", Courier, monospace;">ALTER DB SET PARTNER RESUME</span> - (no re-establishing required)<br />
<br /><u>Safety Off</u> <br />
<br />
Same as ###<br /><br /><strong><u>Mirror Lost</u></strong> <br />
<br /><span style="font-family: "Courier New", Courier, monospace;">ALTER DB SET PARTNER OFF</span> <br />
<br />Then re-establish the mirroring session.</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1144696534562601833.post-45044366109982481802000-01-01T00:03:00.000+05:302014-09-29T21:07:28.022+05:30JOIN VS EXISTS - WHAT TO USE WHEN<span style="font-family: Arial, Helvetica, sans-serif;">Let's say i have a table 'Person' that holds three basic fields and looks somewhat like the following: </span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;">Person</span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_HZ6G3MldN0zjQ1SQsSj8f_qe81286K6-zKrC0dcRS3gQ2E33NMPPCGJKDFh7gyPH_D_bPFsYTlN_erHJguElXk65dL_5ikrm07f_UX8SEshyphenhyphenun1aWiR2UIy3-nTkcaXcjLT9rwFYgTk/s1600/Person1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_HZ6G3MldN0zjQ1SQsSj8f_qe81286K6-zKrC0dcRS3gQ2E33NMPPCGJKDFh7gyPH_D_bPFsYTlN_erHJguElXk65dL_5ikrm07f_UX8SEshyphenhyphenun1aWiR2UIy3-nTkcaXcjLT9rwFYgTk/s1600/Person1.JPG" /></span></a></div>
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;">There is another table called 'Pets' that holds the names of all presidential pets and what kind of animal it is.</span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;">Pets</span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsez10BOE7YlR-Fg8XMuDEVaP-sbjcB9bEJnlfSL1q-k9ZnRga1wcXyW0uBpxDC965AnaNy4araQx342jE50iv6M3cnKmo1LpIXNhEX5pwchlkztmfRn5v_aId16jl9Pxglq5axpYHuGw/s1600/Pet.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsez10BOE7YlR-Fg8XMuDEVaP-sbjcB9bEJnlfSL1q-k9ZnRga1wcXyW0uBpxDC965AnaNy4araQx342jE50iv6M3cnKmo1LpIXNhEX5pwchlkztmfRn5v_aId16jl9Pxglq5axpYHuGw/s1600/Pet.JPG" /></span></a></div>
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><u></u></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><u>Requirement:</u></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Let's say that we want to find the details of all persons who share their name with that of a pet. There are two options here:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">We can join both the above tables to get the details. The query will look somewhat like this:</span><br />
<span style="font-size: x-small;"></span><br />
<span style="color: black; font-family: "Courier New", Courier, monospace;">select <br />ps.Name,</span><br />
<span style="color: black; font-family: "Courier New", Courier, monospace;">ps.Surname,<br />ps.State from <br />Person ps, <br />Pets pt where <br />ps.Name = pt.PetName</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The result of the above query will look somewhat like this:</span><br />
<span style="font-family: Arial;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwje8IRI_NMOU7nPG6tC_5rEYtfzQpDwC4_b828QDGO2bjTR88QnyKjtCToRc1DHhpQLZqpuVZuh4lrb2vbEBNWLQ65hjw_vta3X9Q6jd934PigKtsMXanaD0_mTAZpioPYdscroBt6dg/s1600/Join+Result.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwje8IRI_NMOU7nPG6tC_5rEYtfzQpDwC4_b828QDGO2bjTR88QnyKjtCToRc1DHhpQLZqpuVZuh4lrb2vbEBNWLQ65hjw_vta3X9Q6jd934PigKtsMXanaD0_mTAZpioPYdscroBt6dg/s1600/Join+Result.JPG" /></a></div>
<br />
<br />
<br />
<br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">But is this what we want? Of course not ! The question then arises that why am i viewing four rows instead of two? This is because join creates sort of a cross product of the matching result set (hence all possible combinations of the two tables for the matching Name/Petname (Sunny) is shown. So what is the alternative ? Use EXISTS. The following code snippet illustrates this:</span><br />
<br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="font-family: "Helvetica Neue", Arial, Helvetica, sans-serif;"><span style="color: black; font-family: "Courier New", Courier, monospace;">select <br />ps.Name, <br />ps.Surname, <br />ps.State from <br />Person ps where exists <br />(select 1 from Pets pt where pt.PetName = ps.Name)</span><br />
<span style="color: black;"></span><br />
</span><span style="color: black; font-family: Arial, Helvetica, sans-serif;">The result set is shown below which is what we want to see.</span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6DMZc-GxPk4rTvlgMRsQ50JBANrKy8Su_HZfuewsJfGeqobZQ5h5xhvWK6zWVCrtVlOsIOsPlzRzhWJAXeu0IxV3xXClv7-_dbcvM6Cv5Wex6w90t9PVSd_Oavaw0DKt0IoOJ2TWNui4/s1600/Exists+Result.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6DMZc-GxPk4rTvlgMRsQ50JBANrKy8Su_HZfuewsJfGeqobZQ5h5xhvWK6zWVCrtVlOsIOsPlzRzhWJAXeu0IxV3xXClv7-_dbcvM6Cv5Wex6w90t9PVSd_Oavaw0DKt0IoOJ2TWNui4/s1600/Exists+Result.JPG" /></a></div>
</span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"></span></span></span><br />
<span style="color: blue;"></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: black; font-family: Arial, Helvetica, sans-serif;"><u></u></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: black; font-family: Arial, Helvetica, sans-serif;"><u></u></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: black; font-family: Arial, Helvetica, sans-serif;"><u>Lesson:</u></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: black;"></span></span></span></span><br />
<span style="color: blue;"><span style="color: blue;"><span style="color: blue;"><span style="color: black;"><div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US" style="font-family: "Arial","sans-serif";"><span style="color: black; font-family: Arial, Helvetica, sans-serif;">Use EXISTS<span style="mso-spacerun: yes;"> </span>instead of JOIN when: </span></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US" style="font-family: "Arial","sans-serif";"><span style="color: black;"></span></span><span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US" style="font-family: "Arial","sans-serif";"><span style="color: black; font-family: Arial, Helvetica, sans-serif;">•You don't need to return data from the related table </span></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US" style="font-family: "Arial","sans-serif";"><span style="color: black; font-family: Arial, Helvetica, sans-serif;">•You have dupes in the related table (JOIN can cause duplicate rows if values are repeated) </span></span></div>
<div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<span lang="EN-US" style="font-family: "Arial","sans-serif";"><span style="color: black;"><span style="font-family: Arial, Helvetica, sans-serif;">•You want to check existence</span></span></span></div>
</span><div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<br /></div>
</span><div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<br /></div>
</span><div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<br /></div>
</span><div class="MsoNoSpacing" style="margin: 0cm 0cm 0pt;">
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-1144696534562601833.post-44236791509000088292000-01-01T00:02:00.000+05:302014-09-29T20:00:53.150+05:30SIZE CALCULATION OF SQL DATABASES<span style="font-family: Arial, Helvetica, sans-serif;">In this post i write about some methods of calculating the size of databases in a SQL Server instance</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">To get the size of the mdf+ldf files taken together, use the following</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Courier New", Courier, monospace;">exec sp_helpdb</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-2z9xtcRUrwbrMjiBzNmMCw2gQzHUSln9xZ2hj5WTQWTTF_yS0t-JQFmvh-ucUe6QX9bnzMI23mxOjNv59HR5-rv7w76bIyXXCaHgQ7RFpxYYip4L736QqEXu1ZnqEtsnzEFOLmla4Dc/s1600/helpdb.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-2z9xtcRUrwbrMjiBzNmMCw2gQzHUSln9xZ2hj5WTQWTTF_yS0t-JQFmvh-ucUe6QX9bnzMI23mxOjNv59HR5-rv7w76bIyXXCaHgQ7RFpxYYip4L736QqEXu1ZnqEtsnzEFOLmla4Dc/s1600/helpdb.JPG" /></span></a></div>
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-family: Arial, Helvetica, sans-serif;"></span></span><br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-family: Arial, Helvetica, sans-serif;"></span></span><br />
<span style="font-family: "Courier New", Courier, monospace;"><span style="font-family: Arial, Helvetica, sans-serif;">There is another method that gives the size of the data and the log files separately. The size column is not the actual size but the number of 8KB extents (a measure of size in SQL Server). Therefore to get the actual size, we need to multiply the third column with eight to get the actual size in KBs.</span><br />
</span><br />
<span style="font-family: "Courier New", Courier, monospace;">use master</span><br />
<span style="font-family: "Courier New", Courier, monospace;">select name, type_desc, size, size*8 as KB, size*8/1024 as MB from sys.master_files</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTm5SkAgMTIWhRZZZTBGFtO6_A9kuRJmTyCQH9hjKvvTpXUMauCpSVQAjVNr6vtOYi7Caa2YB_4A9sLmNkYRY8pXehZcvAtwLT6JF5Nn3jGHPX6oH42xdYg8qHud4JjDyZFSwRZafD47o/s1600/sys.masterfiles.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTm5SkAgMTIWhRZZZTBGFtO6_A9kuRJmTyCQH9hjKvvTpXUMauCpSVQAjVNr6vtOYi7Caa2YB_4A9sLmNkYRY8pXehZcvAtwLT6JF5Nn3jGHPX6oH42xdYg8qHud4JjDyZFSwRZafD47o/s1600/sys.masterfiles.JPG" height="243" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br /></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">The same can also be achieved if we want to view the details of any one single database. For example to find the details of only the master database, use the following</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: "Courier New", Courier, monospace;">use master</span><br />
<span style="font-family: "Courier New", Courier, monospace;">select name, type_desc, size, size*8 as KB, size*8/1024 as MB from sys.database_files</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">All the above methods are consistent and give the correct database size.</span>Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-1144696534562601833.post-6582310348776684092000-01-01T00:01:00.000+05:302014-09-29T19:58:34.415+05:30DIFFERENCE BETWEEN CENTRAL MANAGEMENT SERVERS AND LOCAL SERVER GROUPIf you have used either of them, they look somewhat like this<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZTVHO0xGoMzvEaJ9yM-EifsQJWJocBtHnQaT-74UbFicGgls94LsH7KwMpX3gozyy-wSDjrHTCT6j7wLTuuDD-wOD1CGP6WfHgOHZi4evPcSWQjHw4oWKbZ_UpsKbw-N2d73tQ244Ku8/s1600/CMS.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZTVHO0xGoMzvEaJ9yM-EifsQJWJocBtHnQaT-74UbFicGgls94LsH7KwMpX3gozyy-wSDjrHTCT6j7wLTuuDD-wOD1CGP6WfHgOHZi4evPcSWQjHw4oWKbZ_UpsKbw-N2d73tQ244Ku8/s1600/CMS.JPG" /></a></div>
So what is the difference? <br />
<br />
In the case of LSG, the servers are visible only to the user who created them. This is because, the settings are configured as a xml file which is located in the following (the location may vary somewhat across environments)<br />
<br />
C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\120\Tools\Shell\RegSvr.xml<br />
<br />
On the other hand CMS configurations are stored in the msdb database and hence are visible to all users provided they have the requisite priveleges. <br />
<br />
So when to use what?<br />
<br />
Use LSG when you need to create shortcuts to servers/instances for yourself instead of cluttering the CMS view which might not be relevant to others.<br />
<br />
On the other hand, lets say you are part of a team that works with pretty much a fixed set of servers, then use CMS as the'll be accessible to all.<br />
<br />
Now why are the above tow used ? Well, it's for the following:<br />
<br />
<ul>
<li>Policy implementation across multiple instances. This eliminates the need to do them on individial instances one at a time.</li>
<li>Code implementation across multiple instances. This is pretty much an extension of the above point.</li>
<li>Connection shortcut. Simply double/right click and you are in the instance. This is because the passwords are stored beforehand during creation.</li>
</ul>
<br />
Unknownnoreply@blogger.com7tag:blogger.com,1999:blog-1144696534562601833.post-39926570578721839272000-01-01T00:00:00.000+05:302014-09-29T18:24:47.063+05:30DIFFERENCE BETWEEN RANK() AND DENSE_RANK()<span style="font-family: Arial, Helvetica, sans-serif;">I have the following table</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Employees</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgS3HapI9d-6ItSMJQTeauhRsDGfvNyqikTXh3trVE8sX0AckqdK0Y67aQBG6udEZibZxIHjlL0lxrU0VBhUJbY7Y9plV0RXc61zJ6YIIb_WlW6543eZxVM71f62NcqhQbE8FrKUVZ4b0I/s1600/Employees.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgS3HapI9d-6ItSMJQTeauhRsDGfvNyqikTXh3trVE8sX0AckqdK0Y67aQBG6udEZibZxIHjlL0lxrU0VBhUJbY7Y9plV0RXc61zJ6YIIb_WlW6543eZxVM71f62NcqhQbE8FrKUVZ4b0I/s1600/Employees.JPG" /></span></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">I need to arrange them in order of their seniority. Their are two ways to do this. In the first case we use the RANK() function</span></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<span style="font-family: "Courier New", Courier, monospace;">select empid, empname, age, RANK() over (order by age desc) as Rank <br />from [dbo].[Employee]</span><span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOIgfJhO9BMPKadq3PPr3ckgUfB1bK4QEFi1JN_ZChYE0jPJr9ORPkJRjWElYpubrDF2hEFnHE_m9Z_I8N8IwzLSorxKikJem8hq6wtPvtI-VXqxMkxJpgqfNdKbZz_wvwcz60ukXxTb8/s1600/Rank.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOIgfJhO9BMPKadq3PPr3ckgUfB1bK4QEFi1JN_ZChYE0jPJr9ORPkJRjWElYpubrDF2hEFnHE_m9Z_I8N8IwzLSorxKikJem8hq6wtPvtI-VXqxMkxJpgqfNdKbZz_wvwcz60ukXxTb8/s1600/Rank.JPG" /></span></a></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: "Courier New", Courier, monospace;">select empid, empname, age, DENSE_RANK() over (order by age desc) as DenseRank <br />from [dbo].[Employee]</span> </div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;">
<span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtCbLtCxuP-WvajalVz6l8LjTt7oh8k0pCt4NDPvYLMoY9h07a710E3tBLUGtoecA0jAovbRaWcI-ZLpKwofSkkhFWBRbLcUt94O2z-eUpSxf27p2KdtZ7CVQaAP91TRZC81wPQHh0U6M/s1600/Dense+Rank.JPG" /></span></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">If we notice the difference, the first result set doesn't have rank 4. The reason if to people have the same rank, DENSE_RANK() gives the next immediate rank whereas RANK() doesn't. Hence Jack has a rank of 4 via DENSE_RANK() but 5 via RANK().</span>Unknownnoreply@blogger.com6