8 advanced SQL Server Questions you should know to crack your next interview
Congratulations on getting called for a SQL Server Interview. You’re just one step away from fulfilling your dream career as SQL Server Developer. However, scoring an interview call is just half the battle won. It's always difficult to predict what to expect when going in for that SQL Server interview.
Here, we list a few frequently asked SQL questions that should help you crack your next SQL interview.
1. Can you explain the different types of SQL commands?
There are five distinct types of SQL commands which can be classified as under:
• DML – Data Manipulation Language
• DCL – Data Control Language
• DDL – Data Definition Language
• TCL – Transaction Control Language
• DQL – Data Query Language
2. What is Fill Factor and the default fill factor value?
Fill Factor can be described as a setting that applies to Indexes in SQL Server. Whenever an index page is rebuilt or created, fill factor value is used to determine how much data or percentage of space is written to it/filled by it. By default, the fill factor value is set to 100, which is the same as value 0.
3. Can you touch upon the recovery models available for a database?
There are three recovery models available for a database.
• Full recovery model
• Bulk-Logged recovery model
• Simple recovery model
4. Can you explain the concept of SQL Constraints?
SQL constraints are used to specify rules for the data that can be entered into a table. These constraints can be applied on a column level or table level. SQL constraints not only helps in preventing errors but also aids in improving the reliability of the database on the whole. Some of the commonly used constraints are as listed below:
• NOT NULL Constraint
• DEFAULT Constraint
• UNIQUE Constraint
• CHECK Constraint
• INDEX Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint
5. Can you explain which authentication modes are in SQL Server?
There are two authentication modes available for use in SQL Server.
• Windows mode – default mode that cannot be disabled.
• Mixed Mode – enables both SQL and Windows authentication.
6. Can you uncover the difference between a UNION and a JOIN?
Although both, Unions and Joins can be used in order to combine data from one or more tables, the key differentiator lies in how the data is combined. While a union selects rows to combine data, a Join selects columns from two or more tables to combine data.
7. What are the properties of a transaction?
Commonly referred to as ACID properties, there are four properties of ACID which guarantee that the database transactions are processed accurately and reliably at all times.
8. What are the different types of attributes?
An attribute constitutes a character and describes the various characteristics of an entity. They are as below:
• Simple/ Atomic Attribute
• Composite/ Compound Attribute that can be split into columns
• Single-Valued Attribute
• Multi-valued Attribute
• Derived Attribute
• Computed Attribute
• Derived persistent Attribute
• Key Attributes
• Non-key Attributes
• Optional/ null value Attributes
Besides the above-mentioned list of questions, we have compiled an additional list of eight Frequently Asked Questions to help you crack your all-important SQL Server interview.
1. Explain what Replication is along with the different types of Replication and their uses?
2. Explain the concept of an ER Diagram?
3. What is Normalization and describe the different forms of Normalization?
4. Describe what a Subquery is and highlight the various types of sub-query
5. What are various types of Operators available in SQL?
6. Touch upon the difference between Delete and Truncate?
7. What are the various string functions available in SQL?
8. Explain the concept of Self-Join and Cross-Join in SQL?
Although it's easy to get flustered during an interview but it's important that you always stay calm and focused. Keep in mind that interviewers don’t always expect you to know all the answers. Sometimes, they just may be testing your ability to keep composed and analyse whether you crack under pressure. If you hit a roadblock or find yourself unable to answer any question, don’t fret. Take some time to gather your thoughts and try to work through your thoughts on how to approach the question.
Looking for SQL Server Jobs in top cities? Click at the links below:
• SQL Server Jobs in Bangalore
• SQL Server Jobs in Mumbai
• SQL Server Jobs in Chennai
• SQL Server Jobs in Hyderabad
• SQL Server Jobs in Pune