tag:blogger.com,1999:blog-6675833085048146332024-03-09T05:31:05.759+05:30Learn SQL<small><i>Learn. Practice. Share.</i></small>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-667583308504814633.post-2867009190475811012017-11-04T22:32:00.000+05:302017-11-25T20:39:39.406+05:30How to check if the input is numeric<p><font size="2"><font color="#ff00ff">ISNUMERIC()</font> – This is a system function which can be used to evaluate the given input, if the input is valid numeric it returns 1 otherwise 0.  </font></p> <font size="2"></font> <p><font size="2">There are times where we need to check if the input value contains any non numeric chars. the <font color="#ff00ff">ISNUMERIC()</font> can not be used for this purpose as it evaluates numbers, money and decimal to true. </font></p> <p><font size="2">Example: <br /></font>1)  Input value contains only numbers </p> <font size="2"></font> <blockquote class="code"> <code> DECLARE @input VARCHAR(10)='102030' <br> SELECT ISNUMERIC(@input) IsNumber </code> </blockquote> <h5>2)  Input value is decimal </h5> <blockquote class="code"> <code> DECLARE @input VARCHAR(10)='102030.40' <br> SELECT ISNUMERIC(@input) IsNumber </code> </blockquote> <h5>3)  Input value is money</h5> <blockquote class="code"> <code> DECLARE @input VARCHAR(10)='$102030' <br> SELECT ISNUMERIC(@input) IsNumber </code> </blockquote> <h5><font color="#000000">PATINDEX</font> to check existence of non numeric chars</h5> <font size="2"></font> <p><font size="2">The <font color="#ff00ff">PATINDEX</font> function can be used to check if any non numeric char exists in the input.</font></p> <blockquote class="code"> <code> DECLARE @input VARCHAR(10)='102030.40' <br> SELECT PATINDEX('%[^0-9]%',RTRIM(LTRIM(@input))) AS IsNumber </code> </blockquote> <p><font size="2">This statement returns the position of any chars other than numbers. if the statement returns greater than 0 then the input is not a valid number.</font></p>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0tag:blogger.com,1999:blog-667583308504814633.post-72448537189287707122015-11-22T19:40:00.001+05:302017-11-25T20:52:06.528+05:30How to find when was SQL Server restarted?<p>There are few ways to find out when the SQL Server was restarted, here I am going to use DMV (sys.dm_os_sys_info) to find the SQL Server restarted time. </p> <blockquote class="code"><code>SELECT sqlserver_start_time FROM sys.dm_os_sys_info</code></blockquote> <p><a href="http://lh3.googleusercontent.com/-2KsqaTidnFQ/VlHM7bUbUZI/AAAAAAAAAPU/inD2-nztV2I/s1600-h/image%25255B4%25255D.png"><img title="image" style="border: 0px currentcolor; padding-top: 0px; padding-right: 0px; padding-left: 0px; display: inline; background-image: none;" border="0" alt="image" src="http://lh3.googleusercontent.com/-sCDu68RlTG0/VlHM8fV-FMI/AAAAAAAAAPY/nyThWVFsjzg/image_thumb%25255B2%25255D.png?imgmax=800" width="334" height="157" /></a></p>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0tag:blogger.com,1999:blog-667583308504814633.post-44096115527998346562013-04-07T11:35:00.001+05:302017-11-25T20:55:18.687+05:30How to get error description in SSIS dataflow?<p>In SSIS, there is no direct method to get the error description when error occurred during data Load and transformation. </p> <p>We can only redirect the failed records along with Error Code to log file (or any destination component). Since the Error Code is not very useful, we may need to store the Error Description along with the failed row. Even though there is no direct mechanism there is a workaround to achieve. The workaround is to add Script Component. </p> <p>Let’s explore with an example.</p> <p>Below is the sample package, designed to load Sales Order details from flat file to Sales database. Note the Script Component “SC-GetErrorDesc” placed in between Sales Database and Log Error tasks.</p> <p><a href="http://lh6.ggpht.com/-wVpm0LjR7Gc/UWEMp6dliGI/AAAAAAAAAH0/klaTxFiVsIA/s1600-h/clip_image001%25255B7%25255D.jpg"><img title="clip_image001" style="border-width: 0px; padding-top: 0px; padding-right: 0px; padding-left: 0px; display: inline; background-image: none;" border="0" alt="clip_image001" src="http://lh4.ggpht.com/-Rpo-skT5PtE/UWEMqdClPMI/AAAAAAAAAH8/oKdskeYi1ik/clip_image001_thumb%25255B4%25255D.jpg?imgmax=800" width="336" height="222" /></a></p> <p>The next screen shows the configuration of Script Component to get the Error Description.</p> <p>1. Choose the ErrorCode from the available Input columns. </p> <p><a href="http://lh6.ggpht.com/-mGDx4pcjASo/UWEMq3vtlJI/AAAAAAAAAIE/Gm1D7eMdD5s/s1600-h/clip_image003%25255B7%25255D.jpg"><img title="clip_image003" style="border-width: 0px; padding-top: 0px; padding-right: 0px; padding-left: 0px; display: inline; background-image: none;" border="0" alt="clip_image003" src="http://lh5.ggpht.com/-HGvPI5fj4sc/UWEMrejerhI/AAAAAAAAAIM/HgVO9QChcj4/clip_image003_thumb%25255B4%25255D.jpg?imgmax=800" width="362" height="199" /></a></p> <p>2. Add new output column (ErrDesc) in the Inputs and Outputs section </p> <p><a href="http://lh5.ggpht.com/--lUD0W5FbbM/UWEMsMUfmVI/AAAAAAAAAIU/PePYs6_jGpI/s1600-h/clip_image005%25255B9%25255D.jpg"><img title="clip_image005" style="border-width: 0px; padding-top: 0px; padding-right: 0px; padding-left: 0px; display: inline; background-image: none;" border="0" alt="clip_image005" src="http://lh4.ggpht.com/-igXqfbfs7II/UWEMsndovJI/AAAAAAAAAIc/eeoT-KamMpY/clip_image005_thumb%25255B6%25255D.jpg?imgmax=800" width="360" height="219" /></a></p> <p>3. Edit the script </p> <blockquote class="code"><code>public override void Input0_ProcessInputRow(Input0Buffer Row) <br />{ <br />/* <br />Add your code here <br />*/ <br />Row.ErrDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode); <br />}</code></blockquote> <p>4. Finally the result from Script component is stored in the flat file. When you map the column you will notice a new columns named ErrDesc in the Available Input Column side, this new column is created and populated within the script component task.</p> <p><a href="http://lh4.ggpht.com/-g5AFB6UxGg0/UWEMtKhWTpI/AAAAAAAAAIk/8Z7PZ3YeTvY/s1600-h/clip_image006%25255B5%25255D.jpg"><img title="clip_image006" style="border-width: 0px; padding-top: 0px; padding-right: 0px; padding-left: 0px; display: inline; background-image: none;" border="0" alt="clip_image006" src="http://lh4.ggpht.com/-OPjd_mDIT7I/UWEMuKqmYXI/AAAAAAAAAIs/7uzV8eHs4R8/clip_image006_thumb%25255B2%25255D.jpg?imgmax=800" width="308" height="180" /></a></p>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com1tag:blogger.com,1999:blog-667583308504814633.post-38148991123269168942013-04-06T00:10:00.001+05:302017-11-25T20:56:35.740+05:30NULL value for empty field when importing flat file in SSIS<p>When importing data from text file you may need to apply NULL to the field where no values present (blank). Not doing so the package may fail. </p> <p>Consider a scenario; you received a text file contains sales order details, it need to be loaded in to database for further reporting or analysis. </p> <p>The format of CSV file: </p> <p>--------------------------------------------------------------- <br />OrderId, OrderDate, CustomerId, Qty, UnitPrice <br />---------------------------------------------------------------</p> <p>The table definition is:</p> <blockquote class="code"><code>CREATE TABLE SalesOrderDetails <br />( <br />OrderId INT NOT NULL, <br />OrderDate DATE NOT NULL, <br />CustomerId INT NOT NULL, <br />Qty INT NULL, <br />UnitPrice DECIMAL(10,4) NULL <br />)</code></blockquote> <p>The below image shows how the data in text file, if you look at the Qty field of 3rd row, there is no value present. The corresponding column in the destination table is defined as INT. So, you can’t insert non integer value into Qty column. If you do so, you may end up with the following error message.</p> <p>Error code: 0x80004005. <br />Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".</p> <p><a href="http://lh6.ggpht.com/-L6dYkJ0Uhe8/UV8aiL8OKmI/AAAAAAAAAHM/kMeEzXNc58o/s1600-h/clip_image002%25255B4%25255D%25255B8%25255D.jpg"><img title="clip_image002[4]" style="border-width: 0px; padding-top: 0px; padding-right: 0px; padding-left: 0px; display: inline; background-image: none;" border="0" alt="clip_image002[4]" src="http://lh5.ggpht.com/-SWWZf169d30/UV8aijCfqHI/AAAAAAAAAHU/QGygAxsyVB4/clip_image002%25255B4%25255D_thumb%25255B6%25255D.jpg?imgmax=800" width="420" height="105" /></a></p> <p>So the blank field needs to be converted to NULL before inserting into table. You can do this by enabling “retain null values from the source as null values in the data flow” setting available in the Flat File Source Editor.</p> <p><a href="http://lh4.ggpht.com/-ZPhViiSRVPw/UV8ajOMw5-I/AAAAAAAAAHc/1Fdu9UY8dCg/s1600-h/clip_image004%25255B4%25255D%25255B4%25255D.jpg"><img title="clip_image004[4]" style="border-width: 0px; padding-top: 0px; padding-right: 0px; padding-left: 0px; display: inline; background-image: none;" border="0" alt="clip_image004[4]" src="http://lh4.ggpht.com/-FbSmu3hCE4I/UV8ajyIZH7I/AAAAAAAAAHk/WuN0wIRS6hg/clip_image004%25255B4%25255D_thumb%25255B2%25255D.jpg?imgmax=800" width="395" height="157" /></a></p>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com1tag:blogger.com,1999:blog-667583308504814633.post-2373797663384319512012-12-17T21:27:00.001+05:302017-11-25T21:00:48.301+05:30Usage of @@Error within transaction<div style="text-align: left;" dir="ltr" trbidi="on">@@ERROR returns error number of the last statement executed. When the last executed statement completed successfully, this returns 0. In case of any error, the error number will be return. <br /> <br />Example: <br /><blockquote class="code"><code>Declare @n int, @d int <br />Set @n=10 <br />Set @d=0 <br />Select @n/@d <br />PRINT @@ERROR <br /></code></blockquote> <br />I am going to show you a common mistake most of the developer does when using @@ERROR and PRINT, In fact I did so, that’s why I am able write this blog. <br /> <br />Let’s take a look at an example <br /> <blockquote class="code"><code> <br />Create table tblPrintErr(rowId tinyint) <br />Begin Tran <br />Insert into tblPrintErr(rowId) values (1) <br />PRINT @@ERROR <br />Insert into tblPrintErr(rowId) values (300) // This statement will generate an error as 300 is not a tinyint <br />PRINT @@ERROR <br />IF @@ERROR = 0 <br /> BEGIN <br />    COMMIT TRAN <br /> END <br /> ELSE <br /> BEGIN <br />    ROLLBACK TRAN <br /> END <br /> </code></blockquote> <br /> I am forcefully creating an error in order to roll back the transaction, but what really happened was the transaction got committed. <br /> Let’s see what caused for the transaction to commit instead of roll back. <br /> <br /> PRINT @@ERROR statement next to the insert query is what caused the transaction to commit. When IF @@ERROR = 0 statement executed, the last executed statement was PRINT @@ERROR, since this statement executed without error, @@ERROR holds value of 0 and transaction went into commit. <br /> <br /> You need to be careful, when you use @@ERROR. In order to avoid the above discussed behaviour. Use local variable to hold the value of @@ERROR. <br /> <br /> Example: <blockquote class="code"><code> <br /> Declare @Errno int <br /> Insert into tblPrintErr(rowId) values (300) <br /> Set @Errno = @@ERROR <br /> . <br /> . <br /> . <br /> . <br /> IF @Errno =0 <br /> BEGIN <br />    … … … …  <br />END <br /> ELSE <br /> BEGIN <br />    … … … …  <br />END <br /></code></blockquote> <div style="display: none;"><a href="http://www.codeproject.com/" rel="tag">CodeProject</a></div> </div>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0tag:blogger.com,1999:blog-667583308504814633.post-83278242486682517672012-04-27T21:30:00.001+05:302017-11-25T21:05:39.663+05:30SELECT * Vs. SELECT COLUMNS – SQL Server Optimization Techniques<h3>“Don’t use * in SELECT query, instead use only required column”. </h3> <p><font size="2">This is one of the tips to optimize SELECT query. </font><font size="2">However, does this really give better performance in SQL Server query processing? </font></p> <p><font size="2">Selecting only relevant columns doesn’t improve the performance of query processing; however there is an overall performance improvement to the system. For example, when you select only relevant column(s) from a table, the amount of data transferred from database server to frontend server will be less and data get transferred quickly. </font></p> <p><font size="2">In some cases, there will be improvement in query processing too. For example, you are extracting records from a column which is the key column of non-clustered index; in such case, records will be retrieved from non-clustered index page, thus less number of physical database page reads issued. The less physical page read is always good sign for better performance. </font></p> <h3><font size="2">Is there any difference in physical page reads when we use SELECT * and SELECT COLUMNS?</font></h3> <p><font size="2">“SQL Server by default copy the complete table to buffer pool irrespective of columns selection” – So no matter whether you use * or few columns. </font></p> <p><font size="2">I will elaborate this with an example. Let’s create a table and insert some records.</font> </p> <blockquote class="code"><code>create table tblBuffer <br />( <br />r1 int identity(1,1), <br />c1 varchar(900) default replicate('a',900), <br />c2 varchar(900) default replicate('b',900), <br />c3 varchar(900) default replicate('c',900), <br />c4 varchar(900) default replicate('d',900), <br />c5 varchar(900) default replicate('e',900), <br />c6 varchar(900) default replicate('f',900), <br />c7 varchar(900) default replicate('g',900), <br />c8 varchar(900) default replicate('h',900) <br />) declare @count int <br />set @count = 1 <br />while @count <= 1000 <br />begin <br />    insert into tblBuffer default values <br />    set @count = @count +1 <br />end </code></blockquote> <p><font size="2">Once the table is ready, verify space used by the table. Executing “<em>sp_spaceused tblBuffer</em>” I got below result. Data size is 8000 KB approximately 1000 pages (1 page = 8KB).</font></p> <table class="MsoTableGrid" style="border: currentcolor; border-image: none; line-height: normal; letter-spacing: normal; margin-left: 5.4pt; border-collapse: collapse; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt;" cellspacing="0" cellpadding="0" width="564" border="1"><tbody> <tr style="mso-yfti-irow: 0; mso-yfti-firstrow: yes;"> <td style="background: rgb(223, 223, 223); padding: 0cm 5.4pt; border: 1pt solid windowtext; border-image: none; mso-border-alt: solid windowtext .5pt; mso-shading: windowtext; mso-pattern: gray-125 auto;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="center"><span lang="EN-US"><font size="2">Name</font></span></p> </td> <td style="background: rgb(223, 223, 223); border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-shading: windowtext; mso-pattern: gray-125 auto; mso-border-left-alt: solid windowtext .5pt;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="center"><span lang="EN-US"><font size="2">Rows</font></span></p> </td> <td style="background: rgb(223, 223, 223); border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-shading: windowtext; mso-pattern: gray-125 auto; mso-border-left-alt: solid windowtext .5pt;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="center"><span lang="EN-US"><font size="2">Reserved</font></span></p> </td> <td style="background: rgb(223, 223, 223); border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-shading: windowtext; mso-pattern: gray-125 auto; mso-border-left-alt: solid windowtext .5pt;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="center"><span lang="EN-US"><font size="2">Data</font></span></p> </td> <td style="background: rgb(223, 223, 223); border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-shading: windowtext; mso-pattern: gray-125 auto; mso-border-left-alt: solid windowtext .5pt;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="center"><span lang="EN-US"><font size="2">Index_size</font></span></p> </td> <td style="background: rgb(223, 223, 223); border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-shading: windowtext; mso-pattern: gray-125 auto; mso-border-left-alt: solid windowtext .5pt;" valign="top" width="72"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="center"><span lang="EN-US"><font size="2">Unused</font></span></p> </td> </tr> <tr style="mso-yfti-irow: 1; mso-yfti-lastrow: yes;"> <td style="border-width: medium 1pt 1pt; border-style: none solid solid; border-color: currentcolor windowtext windowtext; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span lang="EN-US"><font size="2">tblBuffer</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="right"><span lang="EN-US"><font size="2">1000</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="right"><span lang="EN-US"><font size="2">8008 KB</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="right"><span lang="EN-US"><font size="2">8000 KB</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="98"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="right"><span lang="EN-US"><font size="2">8 KB</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="72"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt;" align="right"><span lang="EN-US"><font size="2">0 KB</font></span></p> </td> </tr> </tbody></table> <p><font size="2">In order to capture stats of <em>page read operation</em>, I prefer to use the performance monitor (<em>perfmon</em>) with following counters.</font> </p> <p><font size="2">SQL Server: Buffer Manager </font> <br /><font size="2">Database pages - Number of pages in the buffer pool with database content.</font> <br /><font size="2">Free Pages - Total number of pages on all free lists.</font> <br /><font size="2">Total Pages - Number of pages in the buffer pool (includes database, free, and stolen).</font> </p> <p><font size="2">By now, I have everything setup and ready to do some experiment. </font></p> <p><font size="2">To verify the page read operation, we need to run the SELECT statements, but before that it is good to clean buffer. DBCC DROPCLEANBUFFERS command can be used to clean buffers from the pool. </font></p> <p><font size="2">Let’s execute DBCC DROPCLEANBUFFERS and capture the performance counters value.</font> </p> <p><a href="http://lh3.ggpht.com/-GpMOcS8IwvQ/T5rCmMtws-I/AAAAAAAAAEk/vJavNU_0v44/s1600-h/clip_image002%25255B3%25255D.jpg"><font size="2"><img title="clip_image002" style="border-width: 0px; padding-top: 0px; padding-right: 0px; padding-left: 0px; display: inline; background-image: none;" border="0" hspace="12" alt="clip_image002" src="http://lh4.ggpht.com/-USDOuogGvRA/T5rCnt33IKI/AAAAAAAAAEs/xhCe3ngwh2o/clip_image002_thumb.jpg?imgmax=800" width="244" height="93" /></font></a><em><font color="#0000ff"><font size="2">(This screen scrap taken from perfmon after execution of DBCC DROPCLEANBUFFERS command)</font> </font></em></p> <p><font size="2">Now, execute the query “select c1 from tblBuffer” and capture the performance counters values.</font> </p> <p><a href="http://lh3.ggpht.com/-E3tNzJwho-g/T5rCpPysy5I/AAAAAAAAAE0/8bv3SaPXHro/s1600-h/clip_image004%25255B3%25255D.jpg"><font size="2"><img title="clip_image004" style="border-width: 0px; padding-top: 0px; padding-right: 0px; padding-left: 0px; display: inline; background-image: none;" border="0" hspace="12" alt="clip_image004" src="http://lh6.ggpht.com/-6iGdQ-ro8OI/T5rCqTUejNI/AAAAAAAAAE8/EPROuqh4_v4/clip_image004_thumb.jpg?imgmax=800" width="244" height="82" /></font></a><em><font color="#0000ff"><font size="2">(This screen scrap taken from perfmon after execution of select c1 from tblBuffer)</font> </font></em></p> <p><font size="2">I have taken the performance counter values into below table for clarity. </font></p> <table class="MsoTableGrid" style="border: currentcolor; border-image: none; line-height: normal; letter-spacing: normal; margin-right: 6.75pt; margin-left: 6.75pt; border-collapse: collapse; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-table-lspace: 9.0pt; mso-table-rspace: 9.0pt; mso-table-anchor-vertical: paragraph; mso-table-anchor-horizontal: margin; mso-table-left: 5.35pt; mso-table-top: 7.9pt;" cellspacing="0" cellpadding="0" width="570" border="1"><tbody> <tr style="mso-yfti-irow: 0; mso-yfti-firstrow: yes;"> <td style="padding: 0cm 5.4pt; border: 1pt solid windowtext; border-image: none; mso-border-alt: solid windowtext .5pt;" width="116"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2"> </font></span></p> </td> <td style="border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt;" width="111"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">After </font></span></p> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">Buffer Cleaned</font></span></p> </td> <td style="border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt;" width="190"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">After query exaction :</font></span></p> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">select c1 from tblBuffer</font></span></p> </td> <td style="border-width: 1pt 1pt 1pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt;" width="151"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">Difference</font></span></p> </td> </tr> <tr style="mso-yfti-irow: 1;"> <td style="border-width: medium 1pt 1pt; border-style: none solid solid; border-color: currentcolor windowtext windowtext; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="116"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">Database Pages</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="111"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">700</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="190"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">1719</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="151"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">1019</font></span></p> </td> </tr> <tr style="mso-yfti-irow: 2; mso-yfti-lastrow: yes;"> <td style="border-width: medium 1pt 1pt; border-style: none solid solid; border-color: currentcolor windowtext windowtext; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="116"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">Free Pages </font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="111"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">2866</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="190"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">1839</font></span></p> </td> <td style="border-width: medium 1pt 1pt medium; border-style: none solid solid none; border-color: currentcolor windowtext windowtext currentcolor; padding: 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;" valign="top" width="151"> <p class="MsoNormal" style="margin: 0cm 0cm 0pt; mso-element: frame; mso-element-frame-hspace: 9.0pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: margin; mso-element-left: 5.4pt; mso-element-top: 7.9pt; mso-height-rule: exactly;"><span lang="EN-US"><font size="2">1024</font></span></p> </td> </tr> </tbody></table> <p><font size="2">After execution of query “<em>select c1 from tblBuffer</em>”, the free page has reduced to 1839 from 2866 similarly number of database pages got increased from 700 to 1719. And the difference shown in the above is closely matching with the number of pages of the table. </font></p> <p><font size="2">It clearly shows that, though I select single column, the complete table has been brought into buffer pool. </font></p> <p>Next, I will show you an example for table with non-clustered index briefly. </p> <p><font size="2">Let’s create a non clustered index on column “c2”</font> </p> <p><font size="2">create nonclustered index idx_tblBuffer_c2 on tblBuffer(c2)</font> </p> <p><font size="2">The number of pages copied to buffer pool will be approximately 110 when you run this query “select c2 from tblBuffer”... It is because, this time data are copied from non-clustered index “idx_tblBuffer_c2”. </font></p> <p><font size="2">You can verify it in perfmon as we did earlier or use the “<em>set statistics io on</em>” settings to verify the same.</font> </p> <blockquote class="summary"> <h1><span class="summary">Summary</span></h1> <p>1. SQL Sever by default copies all the columns to buffer pool, irrespective of column used in the SELECT query. 2. Though you don’t find much performance impact in the SQL Server query processing, it is good to use only the columns you needed as this will increase the overall system performance.</p></blockquote>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0tag:blogger.com,1999:blog-667583308504814633.post-83581643738599489592012-04-25T16:22:00.001+05:302017-11-25T20:29:39.873+05:30JOIN – Inner Join and Outer Join<blockquote class="summary"> <p>JOIN is one of the fundamental units of relational database system. Generally JOIN is used to combine related data from many tables to produce result. </p> </blockquote> <p>There are different types of JOINs available, </p> <p>1. Inner Join <br />2. Outer Join <br />3. Cross Join <br />4. Full Join </p> <p>In this article, I have used few examples to demonstrate inner and outer joins. before dive into the examples, lets see the definition of inner and outer joins.</p> <p>Inner Join, The default join in SQL Server is inner join. Inner join returns all matching records from the given datasets. The match is identified using predicates. </p> <p>Outer join can be classified into Left outer join and Right outer join.</p> <p>Left outer join returns all records from left table, at same time, it brings all matching rows from right table for the given predicate and NULL appears in right side when no matching row exists.</p> <p>Left outer join can also be defined as “result set of inner join + missed records from left table with NULL in the right hand side”</p> <p>Right outer join returns all records from right table, at the same time, it brings all matching rows from left table for the given predicate, and NULL appears in left side when no matching row exists.</p> <p>Right outer join can also defined as “result set of inner join + missed records from right table with NULL in the left hand side” </p> <h5>Below example used to illustrate the “join”</h5> <p>Let’s assume we have two datasets, one contains the student’s details of class “A” and other contains exam results details. This has been illustrated using Venn diagram, however Venn diagram shouldn’t be used to represent table since a table can have many group of related data. Just to give a clarity and visual representation, I have used Venn diagram. </p> <p>There are six students in class “A” which represented in the blue circle. But only two students from class “A” passed in exam. The green circle contains students who passed the exam, in this case, two students from Class “A” and one student from other class. </p> <p><a href="http://lh5.ggpht.com/-uwPjva4IWSc/T5fXSHkt9TI/AAAAAAAAADg/l5dlcWIyCyM/s1600-h/clip_image002%25255B4%25255D%25255B18%25255D.jpg"><img title="clip_image002[4]" style="border-width: 0px; margin: 0px auto; float: none; display: block;" border="0" alt="clip_image002[4]" src="http://lh3.ggpht.com/--yPj1aCO_84/T5fXTLZ3qNI/AAAAAAAAADo/SZI_YKpJWm0/clip_image002%25255B4%25255D_thumb%25255B16%25255D.jpg?imgmax=800" width="401" height="183" /></a></p> <p>From here, let’s go by some questions. </p> <h4>Q. Find out the students from class “A” who passed in exam?</h4> <p>In order to answer this question, the best suitable join is inner join. Because, we need to extract only the matching records from both datasets. As shown in the figure, each row in the CLASS table is matched with all rows of the EXAM table using student_id predicate. When the match is found the record will be copied down.</p> <p><a href="http://lh6.ggpht.com/-zF9DGHWLokU/T5fXT8tDNUI/AAAAAAAAADw/2bW_mOh2e0k/s1600-h/clip_image004%25255B11%25255D%25255B12%25255D.jpg"><img title="inner join" style="border-width: 0px; margin: 0px auto; float: none; display: block;" border="0" alt="inner join" src="http://lh4.ggpht.com/-ytsjK4kFtHo/T5fXVaQjohI/AAAAAAAAAD4/IcQlvDvPpGM/clip_image004%25255B11%25255D_thumb%25255B10%25255D.jpg?imgmax=800" width="434" height="239" /></a></p> <p>The corresponding transact sql code is :</p> <blockquote class="code"><code>“Select * from <br />Class a join Exam b <br />On a.student_id = b.student_id” </code></blockquote> <h4>Q. Find out the students from class “A” who failed in exam?</h4> <p>This can be answered using the “Left Outer Join”. Though there are different ways to get the result, I will be using left outer join since this article about join.</p> <p>When joining CLASS and EXAM table using left outer join, we get all the records from CLASS table and matching records from EXAM table and NULL for the unmatched rows.</p> <p>In order to answer the question, We need to extract all records where student_id is null in right side table. Referring to the below picture, you will get better understating of exactly how left outer join works.</p> <p><a href="http://lh6.ggpht.com/-O5fFx_CVY64/T5fXWOHCXHI/AAAAAAAAAEA/8sDhmWSutR4/s1600-h/clip_image006%25255B11%25255D.jpg"><img title="left outer join" style="border-width: 0px; margin: 0px auto; float: none; display: block;" border="0" alt="left outer join" src="http://lh5.ggpht.com/-2u2AuePgoLE/T5fXXLfZASI/AAAAAAAAAEI/XvGtjntJMLI/clip_image006_thumb%25255B8%25255D.jpg?imgmax=800" width="532" height="420" /></a></p> <p>The corresponding transact sql code is :</p> <blockquote class="code"><code>“Select * from Class a left outer join Exam b <br />On a.student_id = b.student_id <br />where b.student_id is null” </code></blockquote> <h4>Q. Find out the students who passed the exam but not studied in class “A” ?</h4> <p>This can be answered using the “Right Outer Join”. </p> <p>When joining EXAM and CLASS table using right outer join, we get all the records from EXAM table and matching records from CLASS “A” table and NULL for the unmatched rows.</p> <p>In order to answer the question, We need to extract all records where student_id is null in left side table</p> <p><a href="http://lh5.ggpht.com/-6r-YibQt2oM/T5fXYLRVtMI/AAAAAAAAAEQ/j7ll4BfqKNs/s1600-h/clip_image008%25255B9%25255D.jpg"><img title="right outer join" style="border-width: 0px; margin: 0px auto; float: none; display: block;" border="0" alt="right outer join" src="http://lh6.ggpht.com/-W-qCqvCM8H0/T5fXZaMxDDI/AAAAAAAAAEY/Ph2HBs6nFJk/clip_image008_thumb%25255B6%25255D.jpg?imgmax=800" width="509" height="354" /></a></p> <p>The corresponding transact sql code is :</p> <blockquote class="code"> <code>“Select * from <br />Class a right outer join Exam b <br />On a.student_id = b.student_id <br />where a.student_id is null”</code> </blockquote> <p>I hope, you had a good time in reading and with this I am ending this article. The next article will be about Internals of Join in SQL Server. </p>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com1tag:blogger.com,1999:blog-667583308504814633.post-85995943027613924852012-04-15T23:15:00.001+05:302017-11-25T21:18:18.015+05:30Table variable and Table type in transaction<p>The myth is to roll back the work done inside the transaction, in case of any error during the transaction. However this is not the case with the SQL Server table variable, table type and variables. That is, the changes made on these types are not rolled back even when there is an error. </p> Let me explore this with an example. <p>In order to distinguish variable from table, I have created one temporary table and local variable. Inside the transaction, value 1 is assigned to the variable then 256. Assigning of value 256 will cause error as the variable data type is tinyint. The same applied for the temporary table. Due to the error, the transaction enter into the roll back section and the entire transaction rolled back successfully. However by running the select statement “select @num as num” value 1 is return whilst “select * from #tbl” return nothing as expected. This clearly shows that there is no effect for local variable even though the transaction rolled back. </p> <blockquote class="code"><code> use tempdb <br/>GO <br/>create table #tbl (c1 tinyint) <br/> declare @num tinyint <br/>begin tran <br/>set @num = 1 <br/>set @num = 256 <br/>insert into #tbl (c1) values(1) <br/>insert into #tbl (c1) values(256) <br/>if @@ERROR =0 <br/>commit tran <br/>else <br/>rollback tran <br/>select @num as num <br/>select * from #tbl </code></blockquote> <p>The same is applicable for table variable and table type. Sample using table variable: </p> <blockquote class="code"><code> declare @tbl as table (c1 int) <br/>create table #tbl (c1 int) <br/>begin tran <br/>insert into @tbl (c1) values (1) <br/>insert into @tbl (c1) values (1/0) <br/>insert into #tbl (c1) values(1) <br/>insert into #tbl (c1) values(1/0) <br/>if @@ERROR =0 <br/>commit tran <br/>else <br/>rollback tran <br/>select * from @tbl <br/>select * from #tbl </code></blockquote> <p>Sample using table type: </p> <blockquote class="code"><code> create type mytab as table(c1 tinyint) <br/>GO <br/>declare @mytab as mytab <br/>begin tran <br/>insert into @mytab(c1) values (1) <br/>insert into @mytab(c1) values (256) <br/>if @@ERROR = 0 <br/>commit tran <br/>else <br/>rollback tran <br/>select * from @mytab </code></blockquote>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0tag:blogger.com,1999:blog-667583308504814633.post-90251213757422605822012-04-13T21:13:00.001+05:302012-04-13T21:38:58.778+05:30Database Modelling – Many to Many Relationship<p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Let’s start with an example to understand what is many to many relationship and how this can be represented in database.</font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">If you have shopping experience with any online shopping websites like amazon.com, you could have noticed that the way they organized their products to display.</font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">The online store may sell same type of products of different manufacturer and different type of products from same manufacturer. Example Mobile can be manufactured by Nokia, Apple and Samsung. Similarly Apple manufactures Mobile and Computer. I have represented the same scenario in the below diagram.</font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="mso-ignore: vglayout"> <table cellspacing="0" cellpadding="0" align="left"> <tbody> <tr> <td height="8" width="11"> </td></tr> <tr> <td> </td> <td><a href="http://lh3.ggpht.com/-IlsAPTAK0BQ/T4hPjoFg4CI/AAAAAAAAADM/F487xMaRNiA/s1600-h/clip_image001%25255B6%25255D%25255B2%25255D.gif"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="clip_image001[6]" border="0" alt="clip_image001[6]" src="http://lh3.ggpht.com/-Z9kh9c_Ah9o/T4hPlEouRNI/AAAAAAAAADU/RLoaTDhD_P0/clip_image001%25255B6%25255D_thumb.gif?imgmax=800" width="534" height="156"></a></td></tr></tbody></table></span><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p><br style="mso-ignore: vglayout" clear="all"> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">I hope, by now we know what is many to many relationships. Having this knowledge let’s design database table for this use case.</font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <table style="border-bottom: medium none; border-left: medium none; line-height: normal; border-collapse: collapse; margin-left: 4.65pt; border-top: medium none; border-right: medium none; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext" class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" width="259"> <tbody> <tr style="height: 16.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-row-margin-right: 125.45pt"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: navy; border-top: windowtext 1pt solid; border-right: medium none; padding-top: 0cm; mso-border-bottom-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="92" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: ; color: "><font face="Segoe UI"><font style="font-size: 10pt" color="#ffffff">Department</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; border-top: medium none; border-right: medium none; mso-cell-special: placeholder" width="167" colspan="2"> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><font face="Times New Roman"><font style="font-size: 12pt" color="#000000"> </font></font></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 1"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" height="22" valign="bottom" width="115" colspan="2" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">department_id</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: medium none; padding-top: 0cm; mso-border-bottom-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="144" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">department_name</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 2"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" height="22" valign="bottom" width="115" colspan="2" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">1</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: medium none; padding-top: 0cm; mso-border-bottom-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="144" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Mobile Phone</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" height="22" valign="bottom" width="115" colspan="2" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">2</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: medium none; padding-top: 0cm; mso-border-bottom-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="144" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Computer</font></font></span></p></td></tr> <tr> <td style="border-bottom: medium none; border-left: medium none; border-top: medium none; border-right: medium none" width="92"> </td> <td style="border-bottom: medium none; border-left: medium none; border-top: medium none; border-right: medium none" width="23"> </td> <td style="border-bottom: medium none; border-left: medium none; border-top: medium none; border-right: medium none" width="144"> </td></tr></tbody></table> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <table style="border-bottom: medium none; border-left: medium none; line-height: normal; border-collapse: collapse; margin-left: 4.65pt; border-top: medium none; border-right: medium none; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-alt: solid windowtext .5pt; mso-border-insideh: .75pt solid windowtext; mso-border-insidev: .75pt solid windowtext" class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" width="264"> <tbody> <tr style="height: 16.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-row-margin-right: 107.3pt"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: navy; border-top: windowtext 1pt solid; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .75pt" height="22" valign="bottom" width="121" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: ; color: "><font face="Segoe UI"><font style="font-size: 10pt" color="#ffffff">Manufacturer</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; border-top: medium none; border-right: medium none; mso-cell-special: placeholder" width="143"> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><font face="Times New Roman"><font style="font-size: 12pt" color="#000000"> </font></font></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 1"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="121" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">manufacturer _id</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .75pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" height="22" valign="bottom" width="143" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">manufacturer _name</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 2"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="121" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">1</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .75pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" height="22" valign="bottom" width="143" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Apple</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 3"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .75pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="121" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">2</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .75pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .75pt; mso-border-left-alt: solid windowtext .75pt" height="22" valign="bottom" width="143" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Dell</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: .75pt; mso-border-bottom-alt: .5pt; mso-border-top-alt: .75pt; mso-border-left-alt: .5pt; mso-border-color-alt: windowtext; mso-border-style-alt: solid" height="22" valign="bottom" width="121" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">3</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: .5pt; mso-border-bottom-alt: .5pt; mso-border-top-alt: .75pt; mso-border-left-alt: .75pt; mso-border-color-alt: windowtext; mso-border-style-alt: solid" height="22" valign="bottom" width="143" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Nokia</font></font></span></p></td></tr></tbody></table> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">The “Department” table can be used to store all the department of the online store and “Manufacturer” table can be used to store all the manufacturer information. So far everything looks good, but the store wants to display the menu in a way where user can browser product by manufacturers. In our example, computer by manufacturers.</font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">The below given menu is what the online store need. </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <table style="line-height: normal; border-collapse: collapse; margin-left: 4.65pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="592"> <tbody> <tr style="height: 16.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #333333; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: ; color: "><font face="Segoe UI"><font style="font-size: 10pt" color="#ffffff">Browse By Department </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #333333; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: ; color: "><font face="Segoe UI"><font style="font-size: 10pt" color="#ffffff">Browse By Department </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 1"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Mobile Phone ›</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Apple</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #969696; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Mobile Phone </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 2"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #969696; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Computer</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Samsung</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Computer ›</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Apple</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 3"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Dell</font></font></span></p></td></tr> <tr style="height: 12.75pt; mso-yfti-irow: 4"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="17" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="17" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="17" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="17" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="17" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 5"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #333333; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: ; color: "><font face="Segoe UI"><font style="font-size: 10pt" color="#ffffff">Browse By Manufacturer </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #333333; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: ; color: "><font face="Segoe UI"><font style="font-size: 10pt" color="#ffffff">Browse By Manufacturer </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 6"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Apple</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Mobile Phone</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #969696; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Apple</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 7"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #969696; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Dell</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Computer</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Dell</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: silver; padding-top: 0cm" height="22" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Computer</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 8"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #969696; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Samsung</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: #969696; padding-top: 0cm" height="22" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Samsung</font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="22" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td></tr> <tr style="height: 13.5pt; mso-yfti-irow: 9; mso-yfti-lastrow: yes"> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="18" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="18" valign="bottom" width="138" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="18" valign="bottom" width="25" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="18" valign="bottom" width="174" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td> <td style="padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; padding-top: 0cm" height="18" valign="bottom" width="79" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p></td></tr></tbody></table> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font color="#000000"><font style="font-size: 10pt">To achieve this, we create a new table to hold the department and manufacturer relation, this table is called <i style="mso-bidi-font-style: normal">junction table</i>. <span style="mso-spacerun: yes"> </span></font><span style="mso-spacerun: yes"><font style="font-size: 10pt"> </font></span></font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <table style="line-height: normal; border-collapse: collapse; margin-left: 4.65pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="236"> <tbody> <tr style="height: 16.5pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: navy; border-top: windowtext 1pt solid; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-alt: solid windowtext .5pt" height="22" valign="bottom" width="236" colspan="2" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: ; color: "><font face="Segoe UI"><font style="font-size: 10pt" color="#ffffff">Department_Manufacturer</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 1"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="111" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">department_id</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" height="22" valign="bottom" width="124" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">manufacturer_id</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 2"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="111" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">1</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" height="22" valign="bottom" width="124" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">1</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 3"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="111" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">1</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" height="22" valign="bottom" width="124" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">3</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 4"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="111" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">2</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" height="22" valign="bottom" width="124" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">1</font></font></span></p></td></tr> <tr style="height: 16.5pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"> <td style="border-bottom: windowtext 1pt solid; border-left: windowtext 1pt solid; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" height="22" valign="bottom" width="111" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">2</font></font></span></p></td> <td style="border-bottom: windowtext 1pt solid; border-left: medium none; padding-bottom: 0cm; padding-left: 5.4pt; padding-right: 5.4pt; background: white; border-top: medium none; border-right: windowtext 1pt solid; padding-top: 0cm; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" height="22" valign="bottom" width="124" nowrap> <p style="margin: 0cm 0cm 0pt" class="MsoNormal" align="center"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">2</font></font></span></p></td></tr></tbody></table> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000"> </font></font></span></p> <p style="line-height: normal; margin: 0cm 0cm 0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Using this table, we will be able to answer the below questions </font></font></span></p> <ol style="line-height: normal; margin-top: 0cm; margin-bottom: 0cm" type="1"> <li style="text-indent: 0pt; margin: 0cm 0cm 0pt; mso-list: l0 level1 lfo1; tab-stops: list 36.0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">Which departments belong to manufacturer “Apple”</font></font></span></li> <li style="text-indent: 0pt; margin: 0cm 0cm 0pt; mso-list: l0 level1 lfo1; tab-stops: list 36.0pt" class="MsoNormal"><span style="font-family: "><font face="Segoe UI"><font style="font-size: 10pt" color="#000000">List of manufacturers who belong to department “Mobile” </font></font></span></li></ol> Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com1tag:blogger.com,1999:blog-667583308504814633.post-89717793739412561662011-03-22T18:03:00.001+05:302011-03-22T21:15:09.073+05:30Data compression in SQL Server 2008<p><strong><font face="Segoe UI">Data compression – SQL Server 2008</font> </strong> <p><font face="Segoe UI">I found the Data compression feature in SQL Server 2008 is very handy. This definitely helps in optimizing query performance in terms of I/O cost and additionally it helps in saving storage space.</font> <p><font face="Segoe UI">There are two types of compression in SQL Server 2008 </font> <p><font face="Segoe UI"><strong>Row Level Compression </strong></font> <p><font face="Segoe UI">When we enable the Row level compression, the data stored in the fixed length data type will be stored in variable length format.</font> <p><font face="Segoe UI">Before I show you the example, it is worth explaining the difference between CHAR and VARCHAR data type</font> </p> <div class="code-content"><font face="Segoe UI">“CHAR is a fixed-length data type; the storage size of the char value is equal to the maximum size for this column. Whereas VARCHAR is a variable-length data type, the storage size of the VARCHAR value is the actual length of the data entered, not the maximum size for this column.”</font> </div> <p><font face="Segoe UI">Okay, now let’s look at the example,</font> <p><font face="Segoe UI">Let’s assume, we have a table with CHAR (15) column. It is not always true that you will store the data in this column which has 15 characters in length; it may vary, but still depends on your application. For example if you are storing “SQL Server 2008” and “SQL” in the CHAR(15) column each value will take 15 characters space to store the data irrespective of the actual number of characters. When the same value stored in variable length column then the storage space will depend on the length of the character stored.</font></p> <p><font face="Segoe UI">You may ask yourself, why I need a feature “Row level Compression” if I change the CHAR data type to VARCHAR of my table? Yes, absolutely no sense of having such a feature but it is applicable for any fixed length data type and more importantly another advantage of this feature is, it doesn’t take space to store zero and NULL values.</font></p> <p><font face="Segoe UI"><strong>Page Level Compression </strong></font> <p><font face="Segoe UI">Page level compression is very useful functionally which I was expecting for a long time. I was working in analytical processing application, which uses SQL Server 2000,and of course SQL Server 2000 doesn’t have such compression feature. However I have managed to reduce or compress the data size by doing some workaround. I can explain in detail in another post.</font></p> <p><font face="Segoe UI">Let’s see how we can get benefit by using the Page level compression; Page level compression is nothing but removing the duplicate values within the page (i.e.) irrespective of rows and columns.</font></p> <p><font face="Segoe UI">For example, when you insert value “SQL Server 2000”, “SQL Server 2008” into a table the SQL Server storage engine will store “SQL Server 200” only once in a page and it will refer this value for all other occurrences.</font></p> <p><font face="Segoe UI">It is advisable to estimate the benefit before you implement the compression, you can make use of the system stored procedure <em>sp_estimate_data_compression_savings</em> or data compression wizard to estimate how much space you will save by implementing.</font></p> Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0tag:blogger.com,1999:blog-667583308504814633.post-84444431419635170182010-12-22T18:02:00.000+05:302017-11-25T21:25:13.349+05:30Be aware when you use date to filter the records<p> <font face="Segoe UI">When you execute the below query, as you expect, the result will be “September 26<sup>th</sup> 2049” </font></p> <blockquote class="code"><code>DECLARE @DATE AS DATETIME <br />SET @DATE = '09/26/49' <br />SELECT @DATE </code></blockquote> <p><font face="Segoe UI">Now, Lets see what happen when we pass 50 instead of 49 </font></p> <blockquote class="code"><code>DECLARE @DATE AS DATETIME <br />SET @DATE = '09/26/50' <br />SELECT @DATE</code></blockquote> <p><font face="Segoe UI">You will get the result as “September 26<sup>th</sup> 1950”; this is because by default, SQL Server interprets two-digit years based on a cutoff year of 2049. </font></p> <blockquote class="code"><code>DECLARE @DATE AS DATETIME <br />SET @DATE = '09/26/2050' <br />SELECT @DATE</code></blockquote> <p><font face="Segoe UI">Now, as you expect you will get the result as “September 26<sup>th</sup> 2050”; so it is always good to specify the four-digit year. </font></p> <blockquote class="summary"><h1>Notes from BOL</h1> <p>By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.”</blockquote>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0tag:blogger.com,1999:blog-667583308504814633.post-89297228803420065252010-12-15T15:39:00.000+05:302017-11-26T12:58:20.024+05:30How to insert values into an Identity column in SQL Server<p> Identity is a property that can be set on a column. When an identity is enabled the column will be populated automatically by system and it will not allow user to enter value explicitly however there is an option in SQL server SET IDENTITY_INSERT. </p> Let’s create table to explain in details. <blockquote class="code"><code> CREATE TABLE dbo.ErrorLog (LogId INT IDENTITY(1,1), LogMsg VARCHAR(MAX))<br/> INSERT INTO ErrorLog (LogMsg) VALUES('a') GO 10 SELECT * FROM dbo.ErrorLog </code> </blockquote> <p> The test table is created and populated with some sample records. Let’s insert a record into the table and specify value for the LogId column explicitly. </p> <blockquote class="code"><code> INSERT INTO dbo.ErrorLog(LogId,LogMsg) VALUES(0,'b') </code> </blockquote> <p> As expected we get the error message <span class="error">Cannot insert explicit value for identity column in table 'ErrorLog' when IDENTITY_INSERT is set to OFF.</span> There are situations where we need to enter value into the Identity enabled column. To do that we must turn on the IDENTITY_INSERT property as in below example. The identity_insert property must be turned off after the insert otherwise it always expect you to specify value for the identity enabled column with in the session. </p> <blockquote class="code"><code> SET IDENTITY_INSERT dbo.ErrorLog ON <br/> INSERT INTO dbo.ErrorLog(LogId,LogMsg) VALUES(20,'b')<br/> SET IDENTITY_INSERT dbo.ErrorLog OFF </code></blockquote>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com2tag:blogger.com,1999:blog-667583308504814633.post-53799636002096856452010-11-29T17:49:00.000+05:302017-11-25T21:37:04.957+05:30Concatenate column values from multiple rows into a single column in SQL Server<p> I have created a simple table to demonstrate how to concatenate the values from multiple rows in to one column. </p> <blockquote class="code"> <code> CREATE TABLE #tblProduct (product_Id INT IDENTITY(1,1), product_name VARCHAR(10)) </code> </blockquote> <p> Insert some sample records in to this temporary table </p> <blockquote class="code"> <code> INSERT INTO #tblProduct (product_name) VALUES('AAA')<br/> INSERT INTO #tblProduct (product_name) VALUES('BBB')<br/> INSERT INTO #tblProduct (product_name) VALUES('CCC')<br/> INSERT INTO #tblProduct (product_name) VALUES('DDD')<br/> INSERT INTO #tblProduct (product_name) VALUES('EEE')<br/> INSERT INTO #tblProduct (product_name) VALUES('FFF')<br/> INSERT INTO #tblProduct (product_name) VALUES('GGG')<br/> </code> </blockquote> <p> Once you have populated the table with data, Run this query to get the row values in to one column. </p> <blockquote class="code"> <code> DECLARE @productarr VARCHAR(8000) SET @productarr = '' SELECT @productarr = @productarr + '"' + CAST(product_Id AS VARCHAR) + '",' FROM #TBLPRODUCT ORDER BY product_Id <br/> SELECT LEFT(@productarr,LEN(@productarr)-1) <br/> DROP TABLE #tblProduct </code> </blockquote> <p> That’s all you have done with the job </p>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com4tag:blogger.com,1999:blog-667583308504814633.post-38335092202324897582010-11-29T13:47:00.000+05:302017-11-25T21:41:49.697+05:30How to find which query is currently running in SQL Server 2000.<p> The below given function will return the currently running query for the process id. </p> STEP1: Create the below function in your server database. <blockquote class="code"> <code> CREATE FUNCTION SHOW_MY_PROCESS (@SPID INT) <br/> RETURNS VARCHAR(8000)AS<br/> BEGIN <br/> DECLARE @sql_handle BINARY(20), @handle_found BIT <br/> DECLARE @stmt_start INT, @stmt_end INT <br/> DECLARE @line NVARCHAR(4000), @wait_str VARCHAR(8) <br/> DECLARE @sql_process AS VARCHAR(8000) <br/> SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END <br/> FROM master.dbo.SYSPROCESSES <br/> WHERE spid = @spid AND ecid = 0<br/> SELECT @sql_process = SUBSTRING(TEXT, COALESCE(NULLIF(@stmt_start, 0), 1), CASE @stmt_end WHEN -1 THEN DATALENGTH(TEXT) ELSE (@stmt_end - @stmt_start)END) <br/> FROM ::fn_get_sql(@sql_handle)<br/> RETURN @sql_process<br/> END </code> </blockquote> <p> STEP2: Run the below query </p> <blockquote> <code> SELECT dbo.SHOW_MY_PROCESS(SPID) FROM MASTER.DBO.SYSPROCESSES WHERE SPID > 50 </code></blockquote>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0tag:blogger.com,1999:blog-667583308504814633.post-38005826354345917502010-11-28T12:59:00.000+05:302017-11-25T21:43:43.938+05:30How to find the Date is first day of the Week in SQL.<p> Some time you asked to find the given date is first day of the week or not? here is the code. </p> <blockquote class="code"> <code> IF DATEPART(DW,GETDATE()) = @@DATEFIRST <br/> BEGIN <br/> PRINT CONVERT(VARCHAR,GETDATE(),110) + ' IS FIRST DAY OF THE WEEK'<br/> END<br/> ELSE <br/> BEGIN<br/> PRINT CONVERT(VARCHAR,GETDATE(),110) + ' IS NOT A FIRST DAY OF THE WEEK'<br/> END</code></blockquote>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0tag:blogger.com,1999:blog-667583308504814633.post-12545122523484510622010-11-27T00:29:00.000+05:302017-11-25T21:45:27.846+05:30How to find whether the date is end date of the month in SQL<p> To know the given date is end date of the month, You can use the dateadd and day functions. that is, you should add 1 day to the given date and check whether the result date's day is 1 or not. If the result date's day is one then the given date is end date of the month otherwise it is not the end date of the month. </p> <blockquote class="code"> <code> SELECT CASE WHEN DAY(DATEADD(D,1,GETDATE()))=1 THEN 'MONTH END' ELSE 'NOT MONTH END' END AS PERIOD </code></blockquote>Rajhttp://www.blogger.com/profile/16675923664343730913noreply@blogger.com0