SQL Server Interview Questions
A database administrator must be able to respond quickly to issues regarding database connectivity, timeouts and data issues. Interviewing for a DBA role? You need to know how to work with SQL Server, but the technical questions span a myriad of topics that you should be prepared to answer. Here are some of the top questions asked by interviewers.
1. What port number does SQL Server run on by default?
Answer: SQL Server runs on port 1433 by default, but you can set an alternative port in the configurations.
2. What is the difference between a clustered and a non-clustered index?
Answer: SQL Server orders rows in a physical or logical order. A clustered index orders records physically while a non-clustered index orders records logically.
3. How do you reference an external SQL Server on the network?
Answer: You can set up a linked SQL Server to query an external server.
4. How do you set up a column that auto-numbers rows?
Answer: SQL Server lets you set an auto-incrementing column that increments a number by 1 each time a row is entered. You can set this column as the clustered or non-clustered index of a database table.
5. If you have a left join in a stored procedure, what happens to the data set when you query tables?
Answer: a left join gets all records from the referenced table and returns null if the linked table does not contain a record or returns the linked record data if a record exists. If duplicate rows exist in the joining table, multiple records are returned on the linked, joined table.
6. What is the main difference between a primary key and a unique key?
Answer: A primary key is a clustered index by default. A unique key is a non-clustered index by default.
7. What is one similarity between a primary key and a unique key?
Answer: primary keys must also be unique in a table, so primary keys and unique keys are both unique data within a table.
8. What is the difference between a delete statement and a truncate statement?
Answer: A truncate statement clears all records from the table and you cannot roll back transactions. The table data is deleted and you must recover from a backup to get the records back. A delete statement removes records one by one and can be rolled back from a transaction log including from a stored procedure.
9. If you are having issues with a stored procedure or SQL statement, how can you identify which statement or stored procedure is causing the issue?
Answer: the SQL Profiler tool is a graphical interface that lets you identify which stored procedures or statements are slowing down your database and causing issues.
10. What is a foreign key?
Answer: A foreign key links with a primary key within another table. A foreign key is a part of table constraints and provides referential integrity across the database. Use a foreign key to identify where a table’s data links with another table’s data to prevent orphaned records.
Certification can be a great way to stand out in an interview. Fill out the form below if you are interested in learning more about our award winning Microsoft SQL training.