tag:blogger.com,1999:blog-8882112253129875972024-03-13T11:10:14.549-07:00Karthik's SQL BlogUnknownnoreply@blogger.comBlogger14125tag:blogger.com,1999:blog-888211225312987597.post-10175232368102531622010-10-06T10:02:00.000-07:002010-10-06T10:09:47.530-07:00First and Last day of a month.To find First and last days of the month..<br /><br />here I use the <strong>DATEDIFF </strong>function with <strong>m</strong> datepart and <strong>0 </strong>which is for <strong>'1900-01-01'</strong><br /><br /><em><strong>First day of the month</strong></em><br /><br /><strong>For previous month</strong><br />SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)<br /><br /><strong>For current month</strong><br />SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)<br /><br /><strong>For next month</strong><br />SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)<br /><br /><em><strong>Last day of the month</strong></em><br /> Here in this I have used the same code to compute First day of a month ,but after that I have added the result with by -1 second.. <br /> So when a date has 12.00 Am today then -1 second of it 11.59 Pm of the previous day.<br /> this is how it has been done..<br /><br /><strong>For previous month</strong><br />select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))<br /><br /><strong>For current month</strong><br />select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))<br /><br /><strong>For next month</strong><br />select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-888211225312987597.post-65084958793322910022010-09-12T23:58:00.000-07:002010-09-13T00:25:32.638-07:00Precision and scale values.<span class="Apple-style-span" style="font-family:verdana;">Sometimes I wonder why SQL server is not returning the exact number of decimals while multiplying or dividing two variables or columns….!<br />I hope everyone might have faced this problem.</span><div><span class="Apple-style-span" style="font-family:verdana;"><br /><b><i> For example:</i></b><br />declare @a numeric(18,2)<br />declare @b numeric(18,2)<br /><br />set @a = 10.35<br />set @b = 20.34<br /><br />In the above declared variables it is mentioned that precision = 18 and scale = 2, so I am expecting the result of same format. But here SQL server adds more scale while multiplying or dividing.<br /><br />select @a*@b<br /><br /><b>Ans: 210.5190</b><br /><br /><br />select @a/@b<br /><br /><b>Ans: 0.50884955</b></span><div><span class="Apple-style-span" style="font-family:verdana;"><br />But these were correct results and the scale value is decided based on the below formula.</span></div><div><span class="Apple-style-span" style="font-family:verdana;"><br />Here let’s consider @a has p1 as precision and s1 as scale and @b p2 as precision and s2 as scale</span></div><div><span class="Apple-style-span" style="font-family:verdana;"><br />Here p1 and p2 = 18 and both s1 and s2 = 2.</span></div><div><span class="Apple-style-span" style="font-family:verdana;"><br />So the precision value would be of<br /><b><i>p1 - s1 + s2 + max(6, s1 + p2 + 1) = 18 – 2 + 2 + max(6, 2 + 18 + 1) = 18 + 21 = 39</i></b></span></div><div><span class="Apple-style-span" style="font-family:verdana;"><br />and scale value would be of<br /><b><i>max(6, s1 + p2 + 1) = max(6, 2 + 18 + 1) = 21</i></b>.<br /><br />So to avoid more scale value we need to use cast or convert in the result </span></div><div><span class="Apple-style-span" style="font-family:verdana;"><br /><b><i>select cast(@a*@b as numeric(18,2))</i></b></span></div><div><span class="Apple-style-span" style="font-family:verdana;"><b><i><br />Ans: 210.52</i></b></span></div><div><span class="Apple-style-span" style="font-family:verdana;"><br /><b><i>select cast(@a/@b as numeric(18,2))<br /><br />Ans: 0.51</i></b></span><br /></div></div>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-888211225312987597.post-19609928737682679772010-05-22T08:39:00.000-07:002010-05-22T08:42:52.269-07:00Multiply values of a column.Multiplying with coalesce. its quite simple but I get values in a column multiplied...<br /><br />declare @MyTable table (col int) <br /><br />Insert into @MyTable values (1) <br />Insert into @MyTable values (2) <br />Insert into @MyTable values (3) <br />Insert into @MyTable values (4) <br />Insert into @MyTable values (5)<br /><br />declare @a int <br />select @a = col*coalesce(@a,col) from @MyTable <br />select @aUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-888211225312987597.post-89038504028334710902010-03-07T23:39:00.000-08:002010-05-12T02:11:11.637-07:00Top with TiesSpecifies that additional rows be returned from the base result set with the same value in the <br />ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. <br />TOP...WITH TIES can be specified only in SELECT statements, <br />and only if an ORDER BY clause is specified.<br /><br />The following example obtains the top 10 percent of all employees with the highest salary <br />and returns them in descending order according to salary base rate. <br />Specifying WITH TIES makes sure that any employees that have salaries equal to the <br />lowest salary returned are also included in the result set, <br />even if doing this exceeds 10 percent of employees.<br /><br /><em>Here We Go..!!!</em><br /><br />DECLARE @A TABLE (ID INT IDENTITY,NAM VARCHAR(12),SALARY INT)<br />INSERT INTO @A VALUES('A',1000)<br />INSERT INTO @A VALUES('B',5000)<br />INSERT INTO @A VALUES('C',3000)<br />INSERT INTO @A VALUES('D',1000)<br />INSERT INTO @A VALUES('E',2000)<br />INSERT INTO @A VALUES('F',1000)<br />SELECT * FROM @A ORDER BY SALARY<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEji0IJ7Jh7E98PRREVQa9eIyoO0ikKjJw-5BZGMUktFUrWlGoRMCz8ZeFEx7CK43FqIG1gNBYwK1-QqdQ4s28Tu_Jg3ZWw56J2Ih2G5oLDA1OXioGfOdorU5FL-lWtc8uj_19Pwi_t3ihl2/s1600-h/1.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 189px; height: 172px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEji0IJ7Jh7E98PRREVQa9eIyoO0ikKjJw-5BZGMUktFUrWlGoRMCz8ZeFEx7CK43FqIG1gNBYwK1-QqdQ4s28Tu_Jg3ZWw56J2Ih2G5oLDA1OXioGfOdorU5FL-lWtc8uj_19Pwi_t3ihl2/s400/1.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5446164927356997890" /></a><br /><br /><em><strong>TOP(n)PERCENT WITH TIES :</strong></em><br />SELECT TOP(10) PERCENT WITH TIES * FROM @A ORDER BY SALARY<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFOX9ASRpdFrvrzFIlfSClxPiLmENYg8H4pel5UcdNCUZu1IJ4saPQrZVVC6_t5X44IQDlT3r4aqbci0iTMZMGiNq8Dhl43d1AwoMToma_anGw9nbus5lTgoXS1dMOJeFgDU8TNoLhS5Io/s1600-h/2.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 178px; height: 114px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFOX9ASRpdFrvrzFIlfSClxPiLmENYg8H4pel5UcdNCUZu1IJ4saPQrZVVC6_t5X44IQDlT3r4aqbci0iTMZMGiNq8Dhl43d1AwoMToma_anGw9nbus5lTgoXS1dMOJeFgDU8TNoLhS5Io/s400/2.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5446165017486664690" /></a><br /><br /><em><strong>TOP(n)WITH TIES :</strong></em><br />SELECT TOP(1) WITH TIES * FROM @A ORDER BY SALARY<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmJ3L1wwjB-RixyHZPVc0sZMQezz78O3uuNJ76anjlnfn_sNVviYxtOK92FWXntTVB32iNfGB35E1XhgzQYWE0Tx8M8TOs1hUVmQufgJnrg1QVLix0HTr8gg3sfAViBq1rptTuFYuXNH9-/s1600-h/3.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 184px; height: 119px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmJ3L1wwjB-RixyHZPVc0sZMQezz78O3uuNJ76anjlnfn_sNVviYxtOK92FWXntTVB32iNfGB35E1XhgzQYWE0Tx8M8TOs1hUVmQufgJnrg1QVLix0HTr8gg3sfAViBq1rptTuFYuXNH9-/s400/3.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5446165115339273138" /></a><br /><br /><script type="text/javascript"><br />var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");<br />document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));<br /></script><br /><script type="text/javascript"><br />try {<br />var pageTracker = _gat._getTracker("UA-16365327-1");<br />pageTracker._trackPageview();<br />} catch(err) {}</script>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-888211225312987597.post-80793655481182273772010-02-15T09:20:00.000-08:002010-05-12T02:12:45.833-07:00RENAME objectsHere in this Blog I have mentioned how to rename a object in the database.<br /><br />while renaming a database name It should be changed to single user.<br /><br />Renaming a table,Stored procedure,function,view,trigger uses same the way sp_renamedb<br />how ever renaming stored procedure,function,view and trigger is not a good option.<br /><br />SQL server <span style="font-weight:bold;">will not update sys.syscomments</span> table while renaming objects.<br /><br />so when ever we use sp_helptext to retrieve a Stored procedure or a function. <br />you can clearly see that the Stored procedure or the function name is a old one.<br /><br />If you want to rename a object better drop and create again.<br /><br />see for samples below..<br /><br /><span style="font-style:italic;">Here we GO...!!!</span><br /><br /><span style="font-weight:bold;">Renaming a Database<span style="font-style:italic;"></span></span> <br /><br />use master<br />go<br />exec sp_dboption DBname_OLD ,'Single User',True<br />go<br />exec sp_renamedb 'DBname_OLD','DBname_NEW' <br />go<br />exec sp_dboption DBname_NEW,'Single User',False<br /><br /><br /><span style="font-weight:bold;">Renaming a Table <span style="font-style:italic;"></span></span><br />sp_rename 'Tablename_OLD','Tablename_NEW'<br /><br /><span style="font-weight:bold;">Renaming a column<span style="font-style:italic;"></span></span><br />sp_rename 'Tablename.column_OLD','column_NEW','column'<br /><br /><span style="font-weight:bold;">Renaming a Stored Procedure,Function,View,Trigger <span style="font-style:italic;"></span></span><br />sp_rename 'Old_Name','New_Name' <br /><br />In the below image I have created a procedure named SPTEST and renamed to SPTEST_NEW<br />But still when I use sp_helptext to retrieve the procedure it shows us the old Procedure name.<br />this is because <span style="font-weight:bold;">sys.syscomments table has not been updated<span style="font-style:italic;"></span></span>. <br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLe2tLtqbRpNMOW9iv3MJ_gK_M6mn1eaJFAoOX5YJ0q-WIAKGAQd07wFamw_Bb7Tv40JPqTe9jYGMPsAalSsNFz5lDHV-5LNYNB3LG0S2X4ncrOJEB9eiK_z5VPx3V8UbvgNW-IGF_K0ky/s1600-h/rename.jpg"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 361px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLe2tLtqbRpNMOW9iv3MJ_gK_M6mn1eaJFAoOX5YJ0q-WIAKGAQd07wFamw_Bb7Tv40JPqTe9jYGMPsAalSsNFz5lDHV-5LNYNB3LG0S2X4ncrOJEB9eiK_z5VPx3V8UbvgNW-IGF_K0ky/s400/rename.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5438521720970496242" /></a><br /><br />here either we can drop and create the procedure or we can alter the procedure from the SSMS window by right click on it.<br /><br /><script type="text/javascript"><br />var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");<br />document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));<br /></script><br /><script type="text/javascript"><br />try {<br />var pageTracker = _gat._getTracker("UA-16365327-1");<br />pageTracker._trackPageview();<br />} catch(err) {}</script>Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-888211225312987597.post-51706992554057578982010-02-09T08:43:00.000-08:002010-02-09T09:15:30.393-08:00Select TOP N RecordsMost times while retrieving records from database we like to fetch top N records<br />There are couple of ways to do it..<br />here in this blog I have tried with<br /><br /><span style="font-weight:bold;"><span style="font-style:italic;">1) TOP<br />2) ROWCOUNT<br />3) ROW_NUMBER()<br /></span></span><br /><div>out of all the three I could prefer to use TOP.<br />ROWCOUNT will take priority if its specified smaller than TOP.<br />ROWCOUNT cannot be used in Functions.<br />ROW_NUMBER() can be used to fetch top N records with partition.<br /><br /><span style="font-weight:bold;">Here We Go..!!<span style="font-style:italic;"></span></span><br /><br />USE AdventureWorks<br /><br /><span style="font-weight:bold;">Using TOP:<br /><span style="font-style:italic;"></span></span><br />SELECT TOP 10 * FROM Person.StateProvince<br /><br /><span style="font-style:italic;">with variable:</span><br /><br />DECLARE @N INT<br />SET @N = 10<br />SELECT TOP (@N) * FROM Person.StateProvince<br /><br /><span style="font-weight:bold;">Using ROWCOUNT:<span style="font-style:italic;"></span></span><br /><br />SET ROWCOUNT 10<br />SELECT * FROM Person.StateProvince<br /><br />--<span style="font-style:italic;">TO RESET THE ROWCOUNT</span><br />SET ROWCOUNT 0<br /><br /><span style="font-weight:bold;">Using ROWNUMBER:<span style="font-style:italic;"></span></span><br /><br />SELECT * FROM<br />( SELECT ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY NAME) AS ROW_NUM,* FROM<br />Person.StateProvince<br />) TAB<br />WHERE ROW_NUM <=10</div><div><br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-888211225312987597.post-10288837969519290432010-02-08T09:29:00.000-08:002010-02-08T09:32:46.352-08:00Ranking FunctionsRanking functions are used to partition the records based on the needs.<br />There are 4 types of ranking functions available in SQL server.<br /><br /><span style="font-weight:bold;">ROW_NUMBER()<span style="font-style:italic;"></span></span><br /> Used to generate identity numbers for each rows. <br /><span style="font-weight:bold;">RANK()<span style="font-style:italic;"></span></span><br /> Rank function will partition the record but skip the numbers. <br /><span style="font-weight:bold;">DENSE_RANK()<span style="font-style:italic;"></span></span><br /> This function will partition the record with out skipping the numbers. <br /><span style="font-weight:bold;">NTILE()<span style="font-style:italic;"></span></span><br /> This will equally divide the record set into number of parts based on the number which we specify.<br /><br /><br />Lets use all those things in a single query to see the different..<br /><br />In the below example we can partition the records by giving any of the column <br />Name (Multiple columns can also be given with comma seperator), I have given the integer value 1 to get the identity rownumbers <br /><br /><span style="font-weight:bold;">Here We GO..!!!<span style="font-style:italic;"></span></span><br /><br />USE ADVENTUREWORKS<br /><br />GO<br /><br />SELECT LOGINID,<br /> TITLE,<br /> ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY TITLE) AS ROWNUM,<br /> RANK()OVER(PARTITION BY 1 ORDER BY TITLE) AS RANK,<br /> DENSE_RANK()OVER(PARTITION BY 1 ORDER BY TITLE) AS DENSERANK,<br /> NTILE(4)OVER(PARTITION BY 1 ORDER BY TITLE) AS NTILE <br /> FROM HUMANRESOURCES.EMPLOYEE<br /><br /><span style="font-weight:bold;">Result window:<span style="font-style:italic;"></span></span><br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghY4LY2ci1Dnp8wXgyOOY12a8dMlGXpDgI8NryI_fRgjqu_5Jbvh2_PhkasyK3yMYNa9uSzp-NGYIwQPSWliIwwAx24km10dtHQVadeStsyZ3F4SezqdHb48Om58j9lGAw5fZStgdtMSdq/s1600-h/ranking.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 307px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghY4LY2ci1Dnp8wXgyOOY12a8dMlGXpDgI8NryI_fRgjqu_5Jbvh2_PhkasyK3yMYNa9uSzp-NGYIwQPSWliIwwAx24km10dtHQVadeStsyZ3F4SezqdHb48Om58j9lGAw5fZStgdtMSdq/s400/ranking.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5435926696739829026" /></a>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-888211225312987597.post-7533202229784840252010-02-07T10:37:00.000-08:002010-02-07T10:50:35.574-08:00Calculating Running totalHere in this Blog I have shown how to calculate <em><strong>Running totals</strong></em> and compared two ways of caculating it.<br /><br />I have tried with <em><strong>INNER JOIN</strong></em> and <em><strong>CTE</strong></em>. Both these methods brings the correct result, <br />But I could clearly see that<em><strong> CTE took only 27%</strong></em> of the total execution time compared to INNER JOIN, <em><strong>INNER JOIN took remaining 73%</strong></em> to bring the same result.<br /><br />I have provided the Example below....<br /><br /><em><strong>Here We Go...!!!!</strong></em><br /><br />sample table for testing.<br /><br />CREATE TABLE PRO (ID INT IDENTITY(1,1),PRO_NAME VARCHAR(100),PRO_QTY INT)<br /><br />INSERT PRO VALUES ('A',10)<br />INSERT PRO VALUES ('B',20)<br />INSERT PRO VALUES ('C',30)<br />INSERT PRO VALUES ('D',20)<br />INSERT PRO VALUES ('E',10)<br />INSERT PRO VALUES ('F',50)<br />INSERT PRO VALUES ('G',5)<br /><em><strong><br />METHOD 1:<br />using INNER JOIN</strong></em><br /><br />SELECT A.ID,A.PRO_NAME,A.PRO_QTY,SUM(B.PRO_QTY) as RUNNING_TOTAL<br />FROM PRO A<br />INNER JOIN PRO B<br />ON (B.ID <= A.ID) <br />GROUP BY A.ID,A.PRO_NAME,A.PRO_QTY<br /><br /><em><strong>METHOD 2: <br />CTE</strong></em><br /><br />;WITH CTE(ID,PRO_NAME,PRO_QTY,RUNNING_TOTAL)<br />AS<br />(<br />SELECT ID,PRO_NAME,PRO_QTY,PRO_QTY AS RUNNING_TOTAL FROM PRO <br />WHERE ID =1<br />UNION ALL<br />SELECT P.ID,P.PRO_NAME,P.PRO_QTY,P.PRO_QTY+C.RUNNING_TOTAL FROM PRO P<br />JOIN CTE C ON C.ID+1 = P.ID<br />)<br />SELECT * FROM CTE<br /><br />DROP TABLE PRO<br /><br /><em><strong>Results from both methods.</strong></em><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiN_FlhZTfhUdUwO5onfVPjFCh0ZX-OJt9oiduT8hycrjwq6dNXf7bpxEWT7PLqLy1zO_JztlQcauIpwCqpVnmFQUsNKC-OUKCxugwnF5_LMX0wc7FL8ovbj6fy_3I9JmBfbMWLsF4zYJ98/s1600-h/Run2.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 305px; height: 176px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiN_FlhZTfhUdUwO5onfVPjFCh0ZX-OJt9oiduT8hycrjwq6dNXf7bpxEWT7PLqLy1zO_JztlQcauIpwCqpVnmFQUsNKC-OUKCxugwnF5_LMX0wc7FL8ovbj6fy_3I9JmBfbMWLsF4zYJ98/s320/Run2.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5435573013512704258" /></a><br /><br /><em><strong>Execution Plan of both Methods .</strong></em> click on the Image to view <br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEir_iJQI3YuKYNcudyay64dtTHXociPI8u5dh4PyEF7seZ2Fw8NogucwDttAnDUQipnz7ZWF7ZwC0qeG7IlVpwtr9jQdTzXwb0xEOZD3R6CrONOMd8aLkVW2eya1vKiaZMWypDCeom7hj8V/s1600-h/Runningtotal.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 188px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEir_iJQI3YuKYNcudyay64dtTHXociPI8u5dh4PyEF7seZ2Fw8NogucwDttAnDUQipnz7ZWF7ZwC0qeG7IlVpwtr9jQdTzXwb0xEOZD3R6CrONOMd8aLkVW2eya1vKiaZMWypDCeom7hj8V/s400/Runningtotal.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5435574865129032082" /></a>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-888211225312987597.post-31840868565323563462010-02-06T07:29:00.000-08:002010-02-06T07:34:42.284-08:00Time alone in SQL serverMost the times we need to format the Datetime column while displaying the record,<br />although formatting date in Backend is not a good practice, because it changes the datatype of the Date to varchar<br /><br />We can split datetime in number of formats.<br />Microsoft has listed the possible format to get date alone in the below link..<br /><br /><a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx">http://msdn.microsoft.com/en-us/library/ms187928.aspx</a><br /><br />Let me go with time part alone,<br />Below I have provided 4 different time formats.<br /><br />This codeworks fine in SQL server 2005<br /><br />Here We GO...!!!<br /><br />To get the time part alone from given date<br /><br /><span style="font-weight:bold;">HH:MM:SS<span style="font-style:italic;"></span></span><br />SELECT CONVERT(VARCHAR, GETDATE(), 108)<br /><span style="font-weight:bold;">Result :<span style="font-style:italic;"></span></span> 20:43:28<br /><br /><span style="font-weight:bold;">HH:MM:SS.MS<span style="font-style:italic;"></span></span><br />SELECT RIGHT(CONVERT(VARCHAR, GETDATE(), 121) ,12)<br /><span style="font-weight:bold;">Result :<span style="font-style:italic;"></span></span> 20:43:28.327<br /><br /><span style="font-weight:bold;">HH:MM(AM/PM)<span style="font-style:italic;"></span></span><br />SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),6)<br /><span style="font-weight:bold;">Result :<span style="font-style:italic;"><span style="font-style:italic;"></span></span></span> 8:43PM<br /><br /><span style="font-weight:bold;">HH:MM:SS:MS(AM/PM)<span style="font-style:italic;"></span></span><br />SELECT RIGHT(CONVERT(VARCHAR(30), GETDATE(), 109),13)<br /><span style="font-weight:bold;">Result :<span style="font-style:italic;"></span></span> 8:43:28:327PMUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-888211225312987597.post-58881528076197625212010-02-03T03:25:00.000-08:002010-02-03T03:28:02.794-08:00Backp and Restore DatesSome times we need to know when the database had its last <strong>backup </strong>or <strong>restore</strong><br /><br /><strong>msdb </strong>Database holds the details of all the Backups and Restores <strong>happened on a server</strong>.<br /><br /><strong>backupset </strong>table holds the details of Backups<br /><strong>restorehistory </strong>table holds the details of Restores.<br /><br />Below scripted code will bring the list of backup and Restore for the given database.<br /><br /><em>Here we GO..!!</em><br /><br /><em>Code to list Backup.</em><br />select database_name,backup_finish_date ,user_name from msdb..backupset<br />where database_name='DatabaseName'<br />order by backup_finish_date desc<br /><br /><em>Code to list Restore.</em><br />select destination_database_name,restore_date,user_name from msdb..restorehistory<br />where destination_database_name='DatabaseName'<br />order by restore_date descUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-888211225312987597.post-68064149144644937672010-02-02T08:50:00.000-08:002010-02-02T09:18:04.204-08:00Table Definitions from a Database.<em>Some times we might need all the information about tables and columns in a Database.<br /></em><br /><em>Below scripted Code Defines the properties of all the columns from all tables, Foreign key specifications as well.<br /><br />One can export the result to an Excel for Database Design overview.<br /><br />This code works fine in SQL server 2005.</em><br /><br /><em>Here we GO...!!!!</em><br /><br /><em>/*****Lists all the Primary key and Indexs from all the Table*****/</em><br /><br />select so.object_id,<br />object_name(so.object_id)as tbl_name,<br />is_unique ,<br />is_primary_key,<br />sc.name,<br />colid ,<br />i.type_desc<br />into #primary<br />from sys.objects so<br />join sys.columns sc<br />on sc.object_id=so.object_id<br />join sysindexkeys ik<br />on ik.id=so.object_id and ik.colid=sc.column_id<br />join sys.indexes i<br />on i.object_id=ik.id and i.index_id=ik.indid<br />where so.type='U'<br />order by 1,4<br /><br /><em>/*****Table to Store basic info for all the tables*****/</em><br /><br />Create table #TableList<br />(<br />tablename nvarchar(100),<br />table_id int,<br />ColumnName nvarchar(100),<br />column_id int,<br />DataType nvarchar(100),<br />max_length int,<br />[precision] int,<br />[scale] int,<br />is_nullable int,<br />is_identity int,<br />is_computed int,<br />default_object_id int,<br />is_unique int,<br />is_primary_key int<br />)<br />insert into #TableList<br />select so.name TableName,<br />so.object_id,<br />sc.name as ColumnName,<br />sc.column_id,<br />sst.name as Datatype,<br />max_length,<br />precision,<br />sc.scale,<br />is_nullable,<br />is_identity,<br />is_computed,<br />default_object_id,<br />is_unique=(select is_unique from #primary where tbl_name=so.name and name=sc.name and is_unique=1 and is_primary_key=0),<br />is_primary_key=(select is_primary_key from #primary where tbl_name=so.name and name=sc.name and is_primary_key=1)<br />from sys.columns sc<br />join sys.objects so on sc.object_id=so.object_id<br />join sys.systypes sst on sc.user_type_id=sst.xusertype<br />where so.type='U'<br />ORDER BY object_name(sc.object_id),sc.name<br /><br /><em>/*****Table to store Foreign key informations.*****/</em><br /><br />Create table #ForeignKeys (<br />[Table_id] int,<br />TableName nvarchar(100),<br />Column_id int,<br />ColumnName nvarchar(100),<br />ForeignKey nvarchar(100),<br />ReferenceTableName nvarchar(100),<br />ReferenceColumnName nvarchar(100),<br />ReferenceObjectId int,<br />ReferenceColumnDatatype nvarchar(100),<br />ReferenceColumnMax_length int<br />)<br />Insert into #ForeignKeys<br />SELECT<br />f.parent_object_id,<br />OBJECT_NAME(f.parent_object_id) AS TableName,<br />fc.Parent_column_id,<br />COL_NAME(fc.parent_object_id,<br />fc.parent_column_id) AS ColumnName,<br />f.name AS ForeignKey,<br />OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,<br />COL_NAME(fc.referenced_object_id,<br />fc.referenced_column_id) AS ReferenceColumnName,<br />so.object_id,<br />sst.name as Datatype,<br />sc.max_length<br />FROM sys.foreign_keys AS f<br />INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id<br />inner join sys.objects so on f.referenced_object_id = so.object_id<br />inner join sys.columns sc on f.referenced_object_id = sc.object_id and fc.referenced_column_id = sc.column_id<br />inner join sys.systypes sst on sc.user_type_id=sst.xusertype<br />order by OBJECT_NAME(f.parent_object_id)<br /><br /><em>/*****Displays Details of all the Tables from a database.*****/</em><br /><br />Select a.tablename,<br />a.ColumnName,<br />a.DataType,<br />a.max_length,<br />a.[precision],<br />a.[scale],<br />a.is_nullable,<br />a.is_identity,<br />a.is_computed,<br />a.default_object_id,<br />a.is_unique,<br />a.is_primary_key,<br />b.ForeignKey,<br />b.ReferenceTableName,<br />b.ReferenceColumnName,<br />b.ReferenceColumnDatatype,<br />b.ReferenceColumnMax_length<br />from #TableList a<br />left outer join #ForeignKeys b on a.table_id = b.table_id and a.column_id = b.column_id<br />order by a.Tablename,a.ColumnName<br /><br />drop table #TableList,#ForeignKeys,#primary<br /><br />--<em>Thanks to Ram and Karthikeyan for Sharing this...</em>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-888211225312987597.post-29369886424662551832010-01-25T05:15:00.000-08:002010-01-25T05:19:45.846-08:00Recursive Queries a Replacement to connect by prior.Recursive Queries are applied to retrieve data which is presented in a <strong>hierarchical format</strong>.<br />Here in the below example we can see how to use <strong>Common Table Expression(CTE)</strong> in <strong>Recursive Queries<br />CTEs</strong> can also contain references to themselves. This allows the developer to write complex queries simpler.<br /><strong>CTEs</strong> can also be used as <strong>Views </strong><br /><br />Here we go..!!!<br /><br /><strong><em>Example for Recursive CTE,</em></strong><br />DECLARE @Employees TABLE(EmployeeID INT,<br />LastName VARCHAR(100),<br />Master INT)<br />INSERT INTO @Employees VALUES (1,'A',null)<br />INSERT INTO @Employees VALUES (2,'B',1)<br />INSERT INTO @Employees VALUES (3,'C',1)<br />INSERT INTO @Employees VALUES (4,'D',2)<br />INSERT INTO @Employees VALUES (5,'E',2)<br />INSERT INTO @Employees VALUES (6,'F',3)<br />INSERT INTO @Employees VALUES (7,'G',6)<br />INSERT INTO @Employees VALUES (8,'H',6)<br />INSERT INTO @Employees VALUES (9,'I',8)<br />INSERT INTO @Employees VALUES (10,'J',9)<br /><br /><strong>SELECT * FROM @Employees</strong><br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhF1ulyMI4hKHDsVsTwgMJHvmbNj9hsBXWjXTi33xaEs9xIl5e5y8E_hWLXzO4qRQ6dI2fWn1sWQTquHVcONYr_jI12dDuREvie4xrMMTd3pxp749Ht3ukVG6JgqcCi_MPqlsIotgEWu-Kk/s1600-h/1.JPG"><img id="BLOGGER_PHOTO_ID_5430665699353443666" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 218px; CURSOR: hand; HEIGHT: 235px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhF1ulyMI4hKHDsVsTwgMJHvmbNj9hsBXWjXTi33xaEs9xIl5e5y8E_hWLXzO4qRQ6dI2fWn1sWQTquHVcONYr_jI12dDuREvie4xrMMTd3pxp749Ht3ukVG6JgqcCi_MPqlsIotgEWu-Kk/s320/1.JPG" border="0" /></a><br /><br />;WITH Master<br />AS<br />(<br />--main Query<br />SELECT EmployeeID, LastName, Master<br />FROM @Employees<br />WHERE EmployeeID = 3<br />UNION ALL<br />--recursive part<br />SELECT e.employeeID,e.LastName, e.Master<br />FROM @Employees e INNER JOIN Master m<br />ON e.Master = m.employeeID<br />)<br /><strong>SELECT * FROM Master</strong><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWs09w4eGvCjdXzPk00jRwyXtfOe4OHdDRCWO24-qeSw86b83DYFdT4MtoVWevX9ULRPb9CC3tmmok6a8s4_HzSMjmW8R515FLTtFnon6Sv8iNRwqmVgQnklqH3nSkL-T-apUmx0ukFa43/s1600-h/2.JPG"><img id="BLOGGER_PHOTO_ID_5430665792696162594" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 225px; CURSOR: hand; HEIGHT: 185px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWs09w4eGvCjdXzPk00jRwyXtfOe4OHdDRCWO24-qeSw86b83DYFdT4MtoVWevX9ULRPb9CC3tmmok6a8s4_HzSMjmW8R515FLTtFnon6Sv8iNRwqmVgQnklqH3nSkL-T-apUmx0ukFa43/s320/2.JPG" border="0" /></a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-888211225312987597.post-28620222534480967102010-01-24T06:17:00.000-08:002010-01-25T05:24:03.237-08:00NULLIF,ISNULL,COALESCE<em><strong>NULLIF,ISNULL,COALESCE</strong></em><br /><br /><strong>NULLIF (expr1, expr2)</strong><br /><br />Returns null value when both expression are equal; if not it returns the first expression<br /><br /><strong>ISNULL (expr_check, expr_replace)</strong><br /><br />Returns a value that indicates whether the value of the specified column is null.<br /><br /><br /><strong>COALESCE (expr1...exprn)</strong><br /><br />Returns first non-null expression among its arguments<br />If all arguments are NULL, COALESCE returns NULL.<br />Rows in a table can be converted into columns using COALESCE.<br />Please see the below example.<br /><br /><em>Here we GO!!!!</em><br /><br />DECLARE @table TABLE (id INT IDENTITY(1,1),FirstName VARCHAR(50),<br />LastName VARCHAR(50),<br />Current_income NUMERIC(19,2),<br />Previous_income NUMERIC(19,2))<br /><br />INSERT INTO @table VALUES('Karthik','M',null,null)<br />INSERT INTO @table VALUES('Suman','PV',null,20.00)<br />INSERT INTO @table VALUES('First','Last',30.25,null)<br /><br /><em><strong>Example for NULLIF:</strong></em><br />SELECT id,FirstName+' '+LastName AS FullName,<br />NULLIF(current_income,Previous_income) AS Income<br />FROM @table<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLx_oLGvSYqpi1xxUUW2rKFW8yABqtX4bzSdcwiyHPoLg03h3-mhmy4WlTgvfNUt3XOiBzoTSgjt7anVuxRxskPB7LdZ1Wb3et_Xrcfc0jneE6lEftUrOqbMSOvu7s9eOwK7Grh_1kdfo7/s1600-h/1.JPG"><img id="BLOGGER_PHOTO_ID_5430311092310657714" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 218px; CURSOR: hand; HEIGHT: 108px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLx_oLGvSYqpi1xxUUW2rKFW8yABqtX4bzSdcwiyHPoLg03h3-mhmy4WlTgvfNUt3XOiBzoTSgjt7anVuxRxskPB7LdZ1Wb3et_Xrcfc0jneE6lEftUrOqbMSOvu7s9eOwK7Grh_1kdfo7/s320/1.JPG" border="0" /></a><br /><br /><em><strong>Example for ISNULL:</strong></em><br />SELECT id,FirstName+' '+LastName AS FullName,<br />ISNULL(current_income,Previous_income) AS Income<br />FROM @table<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGLISHUt90rVctb9wsm0iZHmUzFp6tWCgN1Imr-nrj78euozik42Be2hMg8f2WopN3QqtXB1oTYn92aJo-zxlfXDPFBlBn4_K8dn6LfpkteAdAHogdpai4Jp18ILDRTmEwnMt01msjoYuW/s1600-h/2.JPG"><img id="BLOGGER_PHOTO_ID_5430311598472204226" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 245px; CURSOR: hand; HEIGHT: 147px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGLISHUt90rVctb9wsm0iZHmUzFp6tWCgN1Imr-nrj78euozik42Be2hMg8f2WopN3QqtXB1oTYn92aJo-zxlfXDPFBlBn4_K8dn6LfpkteAdAHogdpai4Jp18ILDRTmEwnMt01msjoYuW/s320/2.JPG" border="0" /></a><br /><br /><em><strong>Example for COALESCE:</strong></em><br />SELECT id,FirstName+' '+LastName AS FullName,<br />COALESCE(current_income,Previous_income) AS Income<br />FROM @table<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFKPy13ax6akUiWHvkE4hplORSjL3qkjyV9eLQqCnFqlZqtxGHx2u1gVQPPz7fl_ua-d-KwhBPru8dbtermBkN5ao1csAfmn253tmydhZXe1c7vJ1jvvxKRwWs-uXReuelok0eRTuyyRkE/s1600-h/3.JPG"><img id="BLOGGER_PHOTO_ID_5430312013259779794" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 218px; CURSOR: hand; HEIGHT: 135px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFKPy13ax6akUiWHvkE4hplORSjL3qkjyV9eLQqCnFqlZqtxGHx2u1gVQPPz7fl_ua-d-KwhBPru8dbtermBkN5ao1csAfmn253tmydhZXe1c7vJ1jvvxKRwWs-uXReuelok0eRTuyyRkE/s320/3.JPG" border="0" /></a><br /><br /><em><strong>Example for ISNULL Combined with COALESCE:</strong></em><br />SELECT id,FirstName+' '+LastName AS FullName,<br />ISNULL(coalesce(current_income,Previous_income),0.00) AS Income<br />FROM @table<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEih5h-26UJ0rh6wuUmagueq73HavQitDFDuCARJdcdQh60xmgwoaUoiWGaklH6wPmqtqwZV-YS5PIUfnn9WNn8nWnqznntaMtXGs5WSp8xqG1sRik_9Vk62ZkecBX6gcowIMT9eyFpFezqD/s1600-h/4.JPG"><img id="BLOGGER_PHOTO_ID_5430312238858071666" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 254px; CURSOR: hand; HEIGHT: 136px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEih5h-26UJ0rh6wuUmagueq73HavQitDFDuCARJdcdQh60xmgwoaUoiWGaklH6wPmqtqwZV-YS5PIUfnn9WNn8nWnqznntaMtXGs5WSp8xqG1sRik_9Vk62ZkecBX6gcowIMT9eyFpFezqD/s320/4.JPG" border="0" /></a><br /><br /><em><strong>Example to convert rows into columns using COALESCE:</strong></em><br />DECLARE @concat varchar(300)<br />SELECT @concat = COALESCE(@concat+', ','')+FirstName FROM @table<br />WHERE current_income IS NULL<br />SELECT @concat as Names<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRMnAQE5kvlN4FIOGybyEZdozfS1Yh9GW9DYH8wYsIomMGUTwbeBMe_lPKaSoTk3g69jiICF6Ix_3manm2xbX6jLNwVTBN23K8htkr36JUJn8gLgTHN5UUXgpcIkq9OXeeREFNM30gMsVY/s1600-h/5..JPG"><img id="BLOGGER_PHOTO_ID_5430312730696473538" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 253px; CURSOR: hand; HEIGHT: 108px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRMnAQE5kvlN4FIOGybyEZdozfS1Yh9GW9DYH8wYsIomMGUTwbeBMe_lPKaSoTk3g69jiICF6Ix_3manm2xbX6jLNwVTBN23K8htkr36JUJn8gLgTHN5UUXgpcIkq9OXeeREFNM30gMsVY/s320/5..JPG" border="0" /></a><strong></strong><strong></strong>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-888211225312987597.post-76630881050032799562010-01-09T08:52:00.000-08:002010-01-09T09:07:36.458-08:00ON DELETE CASCADE<strong>ON DELETE CASCADE<br />ON UPDATE CASCADE</strong><br /><br />Cascading allows one to delete or update the child table once the master table gets affected.<br /><br />SQL Server has CASCADE options for delete and update.<br /><br /><strong>• [ ON DELETE { CASCADE | NO ACTION } ]<br />• [ ON UPDATE { CASCADE | NO ACTION } ]</strong><br /><br /><strong>• ON DELETE CASCADE</strong><br />Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.<br /><strong>• ON UPDATE CASCADE</strong><br />Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables<br /><br /><strong>Note: </strong><br />CASCADE cannot be applied to a timestamp column.<br /><br />Here is the sample for Cascading<br /><br />CREATE TABLE staff <br /> (staff_id INT NOT NULL CONSTRAINT p_staff_id PRIMARY KEY,<br /> staff_name VARCHAR(50) NOT NULL,<br /> staff_role VARCHAR(10) NULL )<br /><br />CREATE TABLE job <br /> (job_id INT NOT NULL CONSTRAINT p_job_id PRIMARY KEY,<br /> job_name VARCHAR(15) NOT NULL )<br /><br />CREATE TABLE staff_job<br /> (staff_id INT NOT NULL,<br /> job_id INT NOT NULL,<br /> comments VARCHAR(200) NULL,<br /> created_date DATETIME NULL,<br />CONSTRAINT p_staff_job PRIMARY KEY(staff_id, job_id),<br />CONSTRAINT f_staff_job1 FOREIGN KEY(staff_id) REFERENCES staff(staff_id)ON DELETE CASCADE,<br />CONSTRAINT f_staff_job2 FOREIGN KEY(job_id) REFERENCES job(job_id)ON UPDATE CASCADE<br />)<br /><br /><br />insert into staff values(1,'Staff1' ,'DBA')<br />insert into staff values(2,'Staff2' ,'DBA')<br />insert into staff values(3,'staff3','UI')<br /><br /><br />insert into job values(1,'Backup' )<br />insert into job values(2,'Restore' )<br /><br />insert into staff_job values(1,1,'created a backup',getdate())<br />insert into staff_job values(2,2,'Restored the Backup',getdate())<br />insert into staff_job values(3,2,'workdone',getdate())<br /><br />select * from staff<br />select * from job<br />select * from staff_job<br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjC-WUHTiR30P-ZlJk5D-CsQAirc57Ep6ZkYrNWD9pPU0tikgegc_9RYmR8hYxMX5gcDVHcL2SK_ljKeLTcWfRtRXguBgaKotAUy25Stw6ESUV4L3eUEPGfqopqUKV40KrQjxmMkIoP3N73/s1600-h/ondeletecascade1.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 203px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjC-WUHTiR30P-ZlJk5D-CsQAirc57Ep6ZkYrNWD9pPU0tikgegc_9RYmR8hYxMX5gcDVHcL2SK_ljKeLTcWfRtRXguBgaKotAUy25Stw6ESUV4L3eUEPGfqopqUKV40KrQjxmMkIoP3N73/s320/ondeletecascade1.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5424785387410518466" /></a><br /><br />here we are going to update and delete the master record.<br /><br /><strong>update job set job_id = 3 where job_name = 'Restore'<br /><br />delete from staff where staff_id = 1 </strong><br />GO<br /><br />select * from staff<br />select * from job<br />select * from staff_job<br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEGY_jDBHitUZqHimAT7BoJBy3BoEMs34fHN5OOM0D0ORwfXwmsWIpu9u8snBdxI4m6nhrRnZX7YtmbA4qpDYchq-zuxn6WxXskSZn9oPpR8YzbLQ4t4dahP5cPyAbof_BJsI_UnQl9Egx/s1600-h/ondeletecascade2.JPG"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 198px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEGY_jDBHitUZqHimAT7BoJBy3BoEMs34fHN5OOM0D0ORwfXwmsWIpu9u8snBdxI4m6nhrRnZX7YtmbA4qpDYchq-zuxn6WxXskSZn9oPpR8YzbLQ4t4dahP5cPyAbof_BJsI_UnQl9Egx/s320/ondeletecascade2.JPG" border="0" alt=""id="BLOGGER_PHOTO_ID_5424785753023464466" /></a><br /><br /><br />GO<br />drop table staff_job<br />drop table job<br />drop table staff<br />GOUnknownnoreply@blogger.com0