Interview is the most significant step of SQL database administrator recruitment. During the interview, a technical person or interviewer asks questions about the technical experience of a candidate. The purpose of these questions is to understand candidate’s experience about SQL Server. The answers to the questions will measure your knowledge and experience. If you cannot prove your experience in this interview, most probably the decision on your employment will be negative. First of all, you should be calm during the interview. This advice is the most significant key to success. The first question of an interview is about your past experience and skills. Being relevant to this question, you should describe your old and current job responsibilities and skills. You must be sure that you demonstrate all of your knowledge and experience to the interviewer. Because this introduction makes the first impact on your potential employer.
Apart from this, it will be useful to recheck some technical details about SQL Server. Because interviewer may ask questions about some headings which you use many times but do not care about the technical detail of this heading. Such as “What is the difference between varchar and nvarchar?” Another advice about the technical interview is to google “SQL server dba interview questions” and study these questions. After this practice, analyze your strong and weak points. If you want, you can support this analysis with the SWOT (Strengths, Weaknesses Opportunities, and Threats) approach. SWOT Analysis is very useful to understand your strengths and weaknesses. (According to Wikipedia: SWOT Analysis is a tool to find out the Strengths, Weaknesses, Opportunities, and Threats that are to be expected in a project or in a business venture or in something similar. It means that the marketing environment (internal and external to the organization or individual) is looked at).
Communication is the common problem during the interview. Try to communicate well with the interviewer. Because this feeling affects the interviewer decision. I want to notice that, this person may be your colleague or team manager if you are hired. Also, you have to be sincere but don’t be disrespectful. So you can communicate better with the interviewer. As a result, your interview process will be more relaxed and stress-free. If your interview process will be in a video conference, be sure the environment lighting is enough for a camera. So your gestures and mimics will make the right effect.
The interviewer can ask the questions which you might not know the answers to. In this case, try to find the answer with your experience, you can be sure interviewer will help. Do not forget that the aim of the interviewer is to measure your knowledge. If we summarize all this:
- Introduce yourself well.
- Be calm and relaxed.
- Good communication.
- Study and analyze your skills.
- Be sincere.
- Try to find an answer which you don’t know.
After this section, I will share some interview questions which can be asked in SQL Server Interviews. This questions will help you to understand a type of SQL Server DBA technical interview questions.
Could you describe a difference between NOLOCK and READPAST query hints?
The Nolock hint reads all dirty pages in SQL Server. The Nolock hint is also known as Readuncommited. The Readpast hint skips uncommitted pages and does not read dirty pages.
Now we will make a proof this answer. First of all, we will create a test table and populate some synthetic data.
DROP TABLE IF EXISTS TestForHint
CREATE TABLE TestForHint
(ID INT IDENTITY(1,1) PRIMARY KEY,
FruitName VARCHAR(100),
Amount INT)
INSERT INTO TestForHint
VALUES ('Apple',20),('Orange',10),('Banana',50)
In this step we will create an open transaction and observe Nolock and Readpast behaviors. The below query will create an open transaction in Apple row.
BEGIN TRAN
UPDATE TestForHint SET Amount='10000' WHERE FruitName='Apple'
In this step, we will execute two query with different hints.
select * from TestForHint WITH(NOLOCK) go select * from TestForHint WITH(READPAST)
This demonstration showed us what is the difference between the ReadPast and Nolock hints. As a result, these two hints have pros and cons. For this reason, consider carefully and make some test before using this query hints in your production environment queries.
What is a contained database?
A contained database is a database that is isolated from other databases and from the instance of SQL Server/ SQL Database (and the master database) that hosts the database. SQL Server supports contained database users for both, Windows and SQL Server authentication.
Now we will create a contained database.
In this first step, we will check the contained database authentication option. If this option value is 0 we need to change this setting.
SP_CONFIGURE 'contained database authentication
SP_CONFIGURE 'contained database authentication',1 GO RECONFIGURE GO SP_CONFIGURE 'contained database authentication'
Or you can change this setting under the SQL Server advanced options tab.
Now we will create a contained database. We will set the Containment type option as Partial.
In this step, we will create a user which has no dependency on the SQL Server. This type of users called as a contained user. Imagine that, we take a backup of the contained database and restore another SQL Server instance. We can connect this database with the contained user.
Tip: If you get the error during the restore operation of the contained database you can dip into this post.
The following query will return a list of contained users.
SELECT name, type_desc, authentication_type, authentication_type_desc FROM sys.database_principals where (type='S' or type = 'U') and authentication_type=2
What is a difference between CHECKPOINT and LAZY WRITER?
The checkpoint process writes dirty pages which exist on Buffer Cache to disk. At the same time CHECKPOINT command can be executed manually.
CHECKPOINT [ checkpoint_duration ]
When you set a value to the checkpoint duration parameter, SQL Server attempts to perform the checkpoint within the requested duration.
Lazy writer’s purpose is to release the buffer pool memory under the memory pressure. Lazy writer mechanism managed by SQL Server Engine. When SQL Server needs much memory, Lazy writer releases the unused pages.
What is the difference between Readable Secondary setting Yes and Read Intent only?
Read-intent only
Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for the read access.
You can find these Readable Secondary options under the Availability Group properties.
When you change your Readable Secondary settings to Read-intent only, we need to make some changes on the connection settings of SQL Server Management Studio. We have to add the ApplicationIntent=ReadOnly parameter to the Additional Connection Parameters setting of Management Studio.
This option helps us to canalize the read-only connections to the secondary replica.
Yes
All connections are allowed to secondary databases of this replica, but only for the read access. The secondary database(s) are all available for the read access.
No
No user connections are allowed to secondary databases of this replica. They are not available for the read access. This is the default setting.
Why would you use SQL Agent?
SQL Agent is the job scheduling mechanism in SQL Server. Jobs can run in specified date and time. This mechanism especially helps us to schedule backup and other database maintenance operations. You can find a lot of options under the SQL Server Agent node.
Can you name some of the new features of SQL Server 2017?
Conclusions
In this post, my aim is to give some interview advice for database administrators. You can google various interview questions but you have to be careful with some points other than questions. Because these points lead us to success.
References
- Database Checkpoints (SQL Server)
- Automatic plan correction in SQL Server 2017
- Adaptive query processing in SQL databases
