12 Best SQL Server Interview Questions

12 Best SQL Server Interview Questions

The Questions That You May Be Asked During a SQL Interview

You may be preparing for a SQL interview. If you are then you need to be prepared for the interview so that you can impress the interviewer most. The first thing that you need to do is to be prepared with some SQL server interview questions. There are some other things too that you must be prepared with before you seat in the hot seat. By reading through the questions you will be able to understand what nature of questions you can be asked and what would be the correct answer if you are asked such a question. There are other preparations also that you need to make before you go for an interview. You need to plan the travel details of yours properly. The travel should be so planned that you do not be late for the interview. It is better to reach the venue of the interview a bit early than to be late. The next thing is not to be panicked in any situation. You may not be sure about a question. If it so happens then take your time to think about it and then answer. If you cannot recollect the answer it is better to tell the interviewer that you do not know the answer than to give a wrong answer. Be yourself during the interview. Do not try to imitate others. You must pay attention to your body language. You must have direct eye contact with the interviewer it will help you to have some confidence.  Now, as you know what to do before and during the interview let us see some of the questions that you may be asked.

Name the two-authentication mode of the SQL server?

This may be the first SQL server interview questions that you may be asked. The question is definitely easy. The answer is the two authentication modes are:

  • Windows mode
  • Mixed mode

The modes can be altered by working on the tools menu of the SQL server which deals with properties of configuration and choosing the security page.

Explain what is a profiler in SQL?

If you are the system administrator and you want to monitor the events in SQL server then this SQL Profiler is the one that you should use. The main usage of this tool is to capture and save the data about every event for analysis pertaining to any file or table. This is the answer, which you may know but it is good to have it in one place so that you can look through it for ready reference.

Explain sub query and the properties that it has?

This may be the next question that you are asked from the SQL server interview questions. You may know the answer but do not show that overconfidence to the interviewer. Be composed and provide the answer. The answer is: The main query are Select, Insert, Delete or Update. Within these main queries, there are subsets which are called a subquery. They can be used when the usage of expression is allowed. There are various properties of sun query. The properties are:

  • There should not be order by clause for any subquery
  • The position of the sub query should be on the right side of the operator of a comparison in the main query
  • As it needs to be executed first it should be in the parenthesis of the main query
  • There can be multiple sub query

Name the types of sub query that you know?

This also has a straight forward answer. There are three types of sub query that can be stated over here. The types are:

  • Sub query which has only a single row
  • Sub query that has multiple rows
  • Sub query that has various columns. It gives various columns to the main query. The main query will also be worked upon along with the sub query in this instance.

Hearing this answer your interviewer will know that you know SQL well and can be relied upon for the job.

Can you explain the scheduled tasks in SQL Server?

This the next question that is asked. The answer is quite simple. These natures of tasks are those which help to have automation on to SQL server. By means of this certain jobs will be automatically done as per schedule time that is fixed.

Can you explain what is Trigger?

This is the next amongst the SQL server interview questions. The answer should be given with confidence. The answer is: when you use the commands like insert, update or delete to be executed in a table this feature of the trigger is used to execute those properly. It is seen that this feature automatically comes in place when a data is changed. The operations of insert, delete or update are those where this feature can be automatically operated.

I think it would be pertinent to say over here that the answer that you should be giving during an interview should be such that they explain the question in depth. It is better not to use too much of technicality if not asked for.

Explain the difference between UNION and UNION ALL?

This is also an easy question to answer. The answer is that UNION is the command that is used while searching information from two different tables. Whereas UNION ALL is that command that is similar to UNION except that it will select all information and it will not delete columns or rows which are the duplicate.

Can you explain how you can hide SQL server instances?

This is the next question that is asked from the SQL server interview questions. The question may look confusing. Are you confused? There is nothing to worry about. Just continue reading and you will know the correct answer. The answer is quite simple. The answer is that: In order to hide a SQL Server instance, there is need of making a change in SQL Server Configuration Manager. In order to do this you need to open SQL Server Configuration Manager and perform the following:

  • You have to select the instance of SQL Server
  • You then need to right-click and opt for Properties.
  • After you have selected properties you need to set Hide Instance to “Yes”
  • Then you need to click OK or Apply.
  • After you see that the change is made, you just need to start again the instance of SQL Server
  • This will then not render the name of the instance.

So, now you know the answer. You must be confident to answer the same in front of the interviewer. If you continue reading you will know of various other questions which you may be asked and have the same nature of confidence. So, just continue reading and know the proper answers to those questions.

How can we hot add CPU to a SQL Server?

Yes, the questions are becoming technical. But as you are over here you do not need to fear. The answer is: Yes you can hot-add CPU to a SQL server. Adding CPUs can be done in various methods. The methods that can be utilized for this addition are

  • By adding new hardware
  • You can logically add by online partitioning of hardware
  • You can virtually hot add a CPU to SQL server through a layer of virtualization.
  • For this you need to start with SQL Server 2008, SQL Server supports hot add CPU.
  • You will be requiring hardware that gives you the option to hot add CPU.
  • You will also be requiring the 64-bit edition of Windows Server 2008 Datacenter or the Windows Server 2008 Enterprise Edition.
  • You also need to have SQL Server Enterprise.
  • Once you have added the CPU you need to run RECONFIGURE then SQL server will recognize the CPU that you have added.

Explain the necessary steps to make SQL server secure?

This is the next question that you are asked. This is a must question amongst the SQL server interview questions that you will be asked if you are seating for an interview for having the job of SQL Server developer. There are various steps that you can take for adding the required security to the SQL server. The steps that you can take are:

  • You can Prefer to have NT authentication
  • Using server, database and application roles to control access to the data
  • You can use NTFS permissions to access the physical database files
  • You can use a SA password that will restrict any unwanted access to the SQL Server
  • You can likewise rename the Administrator account on the SQL Server computer
  • You can disable the Guest account.
  • You can enable auditing by the usage of multiprotocol encryption
  • You can set up SSL, firewalls and isolate the required SQL Server from the web server that is used.

What would you do if you see that the SQL server is not responding?

This may the next bomb shell that is exploded. I think you are ready to handle this issue. Let us see what you should do in such instance. The steps that you must follow if you see that the SQL server is not responding is that you need to connect by using DAC which can be done through usage of CMD or SSMS. As you connect you need to go to the master data base and you need to run the diagnostic queries. This will help you to know the issues and then you have to fix those. After the issues are fixed the server need to be restarted.

How can you upgrade the SQL server 2000 to SQL server 2008?

This is the next question that you are asked and it may be so that this is the question that determines whether you are selected for the job. So, let us answer this amongst the SQL server interview questions properly.  The safest approach that you can take is to have a side-by-side upgrade. It can be done by you either if you use backup and restore approach or you can also detach or attach the database files. It can be said that the backup & restore as the safer approach is the best. The steps are that you must follow is:

  • You need to run the Upgrade Analysis tool from Microsoft. You need to address any issues if any that are raised
  • You then need to identify the DTS packages. These packages are to be migrated by hand. If you do not want that then you have to purchase Pragmatic Works excellent software. You then have to rebuild the DTS packages as per the SSIS.
  • The next thing that has to be done is to script out all the agent jobs of SQL
  • You need to withdraw all security
  • You need to backup the systems and authenticate the backups• Run the security script on the new system
  • After you have done so you need to run the restore on the new system.
  • Authentication of the databases is required by running DBCC
  • The next step is to manually renew all statistics
  • The last step is to run the agent script of SQL

It must be said that there is instability connected with interviews yet in the event that you can ensure that you get your work done legitimately before going for an interview you will have the capacity to face that with more certainty. Never demonstrate your anxiety or disarray to the questioner. They may mix the words in such a way, to the point that straightforward inquiries may appear to be troublesome. Keep a collected mind and consider the question and afterward answer that. It can without much of a stretch is said you know all in regards to SQL Server along these lines, you can answer any of the SQL server interview questions that are tossed at you by the questioner.

Leave a Reply

Close Menu