tag:blogger.com,1999:blog-91993665465215337112024-02-08T06:56:34.973-08:00Having fun with databaseAnonymoushttp://www.blogger.com/profile/05605031396191143816noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-9199366546521533711.post-86696950264033030212012-12-07T07:02:00.001-08:002012-12-14T07:07:19.387-08:00Returning row count per page<span style="font-family: Verdana, sans-serif;">Well hi everyone, this is my first blog ever thus my first post ever. I work in IT since 2006 and have always been interested in databases since the beginning, but can also code various other languages. Enough about me...</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">Here is a little trick that can save you some loading time in your application. Various methods are used to have a combination of current rows on max rows shown (0-100 of 200000). By using this code, you can simplify the returns of the procedures and get all the information in one single database call. We will mainly use the </span><span style="background-color: white; color: blue; font-family: 'Courier New';">OVER</span><span style="font-family: Verdana, sans-serif;"> clause here to achieve our goal. I will update later to include the Oracle version.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><u>TSQL</u></span><br />
<span style="font-family: Verdana, sans-serif;"><u><br /></u></span>
<br />
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">DECLARE </span><span lang="EN-CA" style="font-family: "Courier New";">@ProductNumber <span style="color: blue;">INT<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">DECLARE </span><span lang="EN-CA" style="font-family: "Courier New";">@MaxProducts <span style="color: blue;">INT<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">DECLARE </span><span lang="EN-CA" style="font-family: "Courier New";">@SearchString <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>100<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">DECLARE </span><span lang="EN-CA" style="font-family: "Courier New";">@MinRsltNum <span style="color: blue;">INT<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">DECLARE </span><span lang="EN-CA" style="font-family: "Courier New";">@MaxRsltNum <span style="color: blue;">INT<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">SET </span><span lang="EN-CA" style="font-family: "Courier New";">@ProductNumber <span style="color: grey;">=</span> 1<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">SET </span><span lang="EN-CA" style="font-family: "Courier New";">@MaxProducts <span style="color: grey;">=</span> 50<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">SET </span><span lang="EN-CA" style="font-family: "Courier New";">@SearchString <span style="color: grey;">=</span> <span style="color: red;">'2'<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">SET </span><span lang="EN-CA" style="font-family: "Courier New";">@SearchString <span style="color: grey;">=</span> <span style="color: red;">'%'</span>
<span style="color: grey;">+</span> @SearchString <span style="color: grey;">+</span>
<span style="color: red;">'%'<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">SET </span><span lang="EN-CA" style="font-family: "Courier New";">@MinRsltNum <span style="color: grey;">=</span> 1<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">SET </span><span lang="EN-CA" style="font-family: "Courier New";">@MaxRsltNum <span style="color: grey;">=</span> 5<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">CREATE</span><span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: blue;">TABLE</span> #Products<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: grey; font-family: "Courier New";">(<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> Id <span style="color: blue;">INT</span> <span style="color: blue;">IDENTITY</span><span style="color: grey;">(</span>1<span style="color: grey;">,</span>1<span style="color: grey;">),<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: blue;">Name</span> <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>100<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: grey; font-family: "Courier New";">)<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">WHILE</span><span lang="EN-CA" style="font-family: "Courier New";">
@ProductNumber <span style="color: grey;"><=</span> @MaxProducts<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: blue;">BEGIN<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: blue;">INSERT</span> <span style="color: blue;">INTO</span> #Products
<span style="color: blue;">VALUES</span> <span style="color: grey;">(</span><span style="color: red;">'Product '</span> <span style="color: grey;">+</span> <span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">,</span>
@ProductNumber<span style="color: grey;">))<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: blue;">SET</span> @ProductNumber <span style="color: grey;">=</span>
@ProductNumber <span style="color: grey;">+</span> 1<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: blue;">END<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: green; font-family: "Courier New";">-- Start by reading the subquery. It will be where you
do all the work.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">SELECT</span><span lang="EN-CA" style="font-family: "Courier New";"> rslt<span style="color: grey;">.</span>Name<span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> rslt<span style="color: grey;">.</span>RsltNum<span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> rslt<span style="color: grey;">.</span>RsltTotalCount<span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: green;">-- And to play a little with data!<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: red;">'Viewing '</span> <span style="color: grey;">+</span> <span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">,</span> <span style="color: magenta;">MIN</span><span style="color: grey;">(</span>rslt<span style="color: grey;">.</span>RsltNum<span style="color: grey;">)</span> <span style="color: blue;">OVER</span> <span style="color: grey;">(</span><span style="color: blue;">PARTITION</span> <span style="color: blue;">BY</span> 1<span style="color: grey;">))<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: grey;">+</span> <span style="color: red;">' - '</span> <span style="color: grey;">+</span> <span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">,</span> <span style="color: magenta;">MAX</span><span style="color: grey;">(</span>rslt<span style="color: grey;">.</span>RsltNum<span style="color: grey;">)</span> <span style="color: blue;">OVER</span> <span style="color: grey;">(</span><span style="color: blue;">PARTITION</span> <span style="color: blue;">BY</span> 1<span style="color: grey;">))<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: grey;">+</span> <span style="color: red;">' of '</span> <span style="color: grey;">+</span> <span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">,</span> rslt<span style="color: grey;">.</span>RsltTotalCount<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">FROM</span><span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: grey;">(</span> <span style="color: blue;">SELECT</span> p<span style="color: grey;">.</span>Name<span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: green;">-- This gets the row number. The ORDER BY is important to
ensure a stable result set.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: magenta;">ROW_NUMBER</span><span style="color: grey;">()</span> <span style="color: blue;">OVER</span> <span style="color: grey;">(</span><span style="color: blue;">ORDER</span> <span style="color: blue;">BY</span> <span style="color: blue;">Name</span><span style="color: grey;">)</span> <span style="color: blue;">AS</span> RsltNum<span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: green;">-- This will return the total number of rows found before
filtering the wanted row set.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: magenta;">COUNT</span><span style="color: grey;">(*)</span> <span style="color: blue;">OVER</span> <span style="color: grey;">(</span><span style="color: blue;">PARTITION</span> <span style="color: blue;">BY</span> 1<span style="color: grey;">)</span> <span style="color: blue;">AS</span> RsltTotalCount<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: blue;">FROM</span> #Products
p<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: blue;">WHERE</span> p<span style="color: grey;">.</span>Name <span style="color: grey;">LIKE</span>
@SearchString<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="font-family: "Courier New";"> <span style="color: grey;">)</span> rslt<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span lang="EN-CA" style="color: blue; font-family: "Courier New";">WHERE</span><span lang="EN-CA" style="font-family: "Courier New";"> rslt<span style="color: grey;">.</span>RsltNum <span style="color: grey;">BETWEEN</span> @MinRsltNum <span style="color: grey;">AND</span>
@MaxRsltNum <span style="color: green;">-- This filters the wanted row set.<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; line-height: 115%;">DROP</span><span style="font-family: "Courier New"; line-height: 115%;"> <span style="color: blue;">TABLE</span> #Products</span><o:p></o:p></div>
Anonymoushttp://www.blogger.com/profile/05605031396191143816noreply@blogger.com0Boucherville, QC, Canada45.5913698 -73.436409745.5024783 -73.5943382 45.680261300000005 -73.2784812