Posts

Index in SQL Server

Image
What is an Index? The index is used by queries to fetch the data from the tables quickly.   Indexes are created on Tables and views. Let us assume Index in SQL server to the index in a Textbook. If we want to go directly to a particular topic in the textbook, we refer the index for the page number and locate the topic. If there is no index in the textbook, we will have to check each and every page. Similarly, if we create an index on the column of a table, it would be easy to fetch the records from the table. If not, the query engine will scan every record in the table which is called as Table scan . Table scan is bad for performance. Create an index: Let us assume a table emp as below. Syntax:  create index IX_tablename_columnname on tablename ( columnname order ) Here order indicates either Ascending or Descending . Now let us create an index on salary column in emp table. create index IX_emp_salary on emp ( salary...
Post a Comment
Read more

Find Nth Highest Salary

Image
Let us assume we have an employees table as below. First let us find out the highest salary using MAX() function. 2nd highest salary. Nth highest salary. For example consider N=3  Similarly we can replace 3 with any N value as per our requirement.
Post a Comment
Read more

Location of SQL setup LOG

This question has been asked in most of the interviews for SQL DBA role. Let us find out the location of SQL setup log file. %programfiles%\Microsoft SQL Server\ nnn \Setup Bootstrap\Log\summary.txt The numbers in the path nnn correspond to the version of SQL being installed. SQL2017 => 140 SQL2016 => 130 SQL2014 => 120 SQL2012 => 110 SQL2008 R2 => 100 SQL2008 => 100 SQL2005 => 90 SQL2000 => 80 EXAMPLE: If the SQL version that is being installed in my server is 2014 then the Location of Summary file would be. C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\Summary.txt
Post a Comment
Read more