Regardless of the specific programming position that you're applying for, it's safe to assume that you'll be asked some basic database and SQL questions.
Whether you're fresh out of school or you have a few years of practical experience under your belt, you should have a firm grasp on how to update, delete, select and insert records with SQL queries. Here some SQL interview questions to give you an idea of what may be asked:
1. What is the difference between a clustered index and a non-clustered index, how many clustered indexes can be included in each table and which one is quicker?
It's not unusual for interviewers to ask multiple-part questions like this when covering the same general topic.
- Clustered indexes reorder how records are physically stored in the index. the data pages are contained in the leaf nodes. Non-Clustered indexes, on the other hand, are indexes with a logical order not matching the physical stored order of the rows, with the leaf nodes containing index rows rather than data pages.
- There can only be one Clustered index per table. This is due to the fact that the data rows are sorted in only one order.
- a Non-Clustered index is quicker when used for insert and update operations.
2. How many types of collation sensitivity are there? Please list them and tell me something about each type of sensitivity.
There are four types of collation sensitivity. Each sensitivity is defined as follows:
- Accent sensitivity - Accent sensitivity refers to a sensitivity to accents when performing a query.
- Kana sensitivity - This particular type of collation sensitivity allows the server to distinguish between Japanese kana characters.
- Width sensitivity - A collation sensitivity of this nature refers to full-width characters and half-width characters that are treated differently.
3. Can you tell me the differences between primary keys and unique keys?
Both keys serve to enforce the uniqueness of their defined columns. However, primary keys create clustered indexes on each column. Unique keys automatically create non-clustered indexes by default. Additionally, unique keys only permit one null while primary keys do not allow any nulls.
4. Explain Online Transaction Processing (OLTP).
The purpose of OLTP is to facilitate and manage transaction-oriented applications. This class of information systems is usually used for retrieval transaction processing and data entry. OLTP workloads are generally small, interactive transactions requiring sub-second response times with high-concurrency requirements. The success of the system is typically dependent on the middle tier. The timing of specific write operations is often critical.
5. An SQL Server runs on what TCP/IP port and how can it be changed?
Changes can be made from the Network Utility TCP/IP properties of an SQL server running on port 1433.
SQL interview questions like these aren't all that complex. However, you'll notice the questions do tend to cover a wide range of concepts, which is really the reason for posing such questions to perspective candidates in the first place. Rest assured that you're not likely to be asked any trick SQL interview questions during a typical interview. However, some interviewers may give the technical competence part of the interview separately.
Need more interview help? Click Here.