The latest articles on DEV Community by yarkul (@yarkul). https://dev.to/yarkul https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F1013283%2Fd0fb5580-244d-4cce-8c51-1e520e406546.png https://dev.to/yarkul en yarkul Wed, 25 Jan 2023 12:04:43 +0000 https://dev.to/yarkul/the-simple-sql-question-you-may-be-asked-on-interview-and-fail-2l09 https://dev.to/yarkul/the-simple-sql-question-you-may-be-asked-on-interview-and-fail-2l09 <p>At the interview, you may be asked a question about database indexes. And here is a very simple question that even an experienced developer can stumble on.</p> <h2> Question </h2> <p><strong>The table has an index(IX_Salary) on the Salary column. Will it work for this SQL Statement?</strong><br> <code>SELECT Count(1)<br> FROM [YarkulTestDb1].[dbo].[Person]<br> WHERE Salary + 50 &gt; 1000</code></p> <h2> Answer </h2> <p><strong>No</strong>, because index does not work in case there is an operation on column in <em>WHERE</em> expression. It will not be able to use that index to optimize the query because it is not filtering on the salary column directly. Instead, it is performing a calculation on the salary column (Salary + 50) and then filtering on the result of that calculation (&gt; 1000). In this case, the query optimizer will likely have to scan the entire table to find the rows that match the condition.</p> <h2> Case IX_Salary works well </h2> <p><a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2F6xssfpt3zuzplbj9bsu6.png" class="article-body-image-wrapper"><img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2F6xssfpt3zuzplbj9bsu6.png" alt="Image description" width="780" height="430"></a></p> <h2> Case IX_Salary does <strong>not</strong> do its job </h2> <p><a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2Fxdwkdqgp93d8dpjnme6o.png" class="article-body-image-wrapper"><img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fuploads%2Farticles%2Fxdwkdqgp93d8dpjnme6o.png" alt="Image description" width="800" height="440"></a></p> <h2> About Me </h2> <p>I am a Full Stack Developer with +15 years of experience. Learning new concepts and always looking for new challenges. Visit my personal blog <a href="https://yarkul.com/" rel="noopener noreferrer">https://yarkul.com/</a> to learn .NET, SQL, and Web Development.</p> discuss webdev