File Name: sql server dba scenario based interview questions and answers .zip
You are at accurate place.
- SQL DBA Interview Questions & Answers
- SQL Server DBA Interview Questions and Answers
- Top 50 SQL Server Interview Questions & Answers
What are the different types of backups? Answer: The SQL server offers 4 types of backups to suit the need of the administrator. Complete backup :The complete back up is just zipping the content of the entire database in terms of the different tables and procedures etc. This back up can server as an independent entity that can be restored in different systems with just the base SQL server installed. Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server.
SQL DBA Interview Questions & Answers
You are at accurate place. SVR Technologies give SQL Server training online at your registered time, you can choose classes where, when, and how it accommodates you the best.
Here is the top 50 objective standard model SQL Server Dba Interview questions and their answers are provided just underneath to them. We have taken complete care to provide accurate answers to all the questions. What is BCP?
When does it use? BCP does not copy the structures same as source to destination. When would you use it? If this option is turned on it will display a query execution plan in a separate window when the query is run again. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables? The one-to-one relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table. Explain primary key in Sql Server?
Primary Key has a unique constraint defined on the column and the value in the column cannot be NULL. Explain foreign key in Sql Server? Foreign key relationship to be created between two tables by referencing a column of the table to the primary key of another table.
These are maintained by SQL server for there Internal processing. When we use update insert or delete on tables these magic tables are used.
These are not physical tables but are Internal tables. Whenever we use insert statement is fired the Inserted table is populated with newly inserted Row and whenever delete statement is fired the Deleted table is populated with the delete drow. But in case of update statement is fired both Inserted and Deleted table used for records the Original row before updation get store in the Deleted table and new row Updated get store in Inserted table.
List out the different types of locks available in Sql Server? What is a recursive stored procedure in Sql Server? How the authentication mode can be changed? Few of them are listed here :. And if Recordcount is checked before the error-checking statement then Error would get reset. To get error and rowcount at the same time do both in the same statement and store them in local variable. Explain Sql server authentication modes?
Wildcards are used for data retrieval process from the table. Some of the wildcards are. Explain Indexing and what are the advantages of it? Indexes are created in a table to retrieve the data quickly.
So Indexing improves the performance as the retrieval of data takes less time. Indexing will be done for columns which are being used more often while retrieving. If this constraint has not set then by default columns will accept NULL values too. The parent or outer query is being called as the main query and the inner query is called as inner query or subquery. Different types of Sub Queries are. Union checks the number of columns given in the SELECT statement should be equal or not and the datatypes are also should be same and same applied to UnionAll.
Why use Cursor in Sql Server? This can be considered as a pointer pointing to one row at a time in the list of rows. Cursors can be used for retrieval, removal or addition of records in a table.
List all types of constraints in Sql Server? What are the differences between Union, Intersect, and Minus operators? Explain about Link Server in Sql Server? With Linked servers, we can create easy SQL statements which will allow remote data to be joined, combined and retrieved with data in local. What are the advantages of user-defined functions over stored procedures in Sql Server? Once after the rows are committed or rolled back no need to use No Lock. For example.
What is the significance of master, tempdb and model databases? Explain about unique identifier data type in Sql Server? Below is a list of Sql statements using Drop keyword.
Can SQL servers link to other servers? What are the kinds of subquery? What is the SQL server agent? What are scheduled tasks in SQL Server? This scheduling of jobs benefits to decrease human interference throughout the night time and feed can be produced at an appropriate time.
A user can further order the jobs in which it allows to be produced. This function is used to return a non-null from more than one column in the arguments. What structure can you implement for the database to speed up table reads? Answer : A Follow the rules of DB tuning we have to:. What are statistics, under what circumstances they go out of date, how do you update them? If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values.
The query optimizer uses these indexes in determining whether to choose an index or not while executing a query. In what sequence SQL statement is processed? Can we add an identity column to decimal datatype? What is meant by Active — Passive and Active-Active clustering setup?
An Active- Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. An Active- Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up after fixing the issue that caused the node failure.
The instance is then failed over back to its designated node. How do you generate file output from SQL? What is the importance of a recovery model? Answer : Primarily, the recovery model is chosen keeping in view the amount of data loss one can afford to. If one expects to have minimal or no data loss, choosing the Full recovery model is a good choice.
Depending on the recovery model of a database, the behavior of database log file changes. I would recommend you read more material on log backups and log file behavior and so on to understand in depth. What is Index, cluster index and nonclustered index? Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore the table may have only one clustered index. Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk.
The leaf nodes of a non-clustered index do not consist of the data pages. What are the different authentication modes in SQL Server and how can you change authentication mode?
Answer: The one-to-one relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. Peer-to-Peer Replication Database Mirroring Different types of Sub Queries are Correlated — It is not an independent subquery. It is an inner query which is referred by the outer query. Non-Correlated — It is an independent subquery. It can be executed even without an outer query. There are two types of user-defined functions — Scalar — This type of functions are used for returning a single scalar value.
Table-Valued — This type of function are used for returning a table which has a list of rows. Sql supports data type called table which is used here for returning a table. Interact operator is used to returning the common list of records between two result sets. Minus operator is used to getting the list of records from the first result set and which is not there in the second result set. Answer: master — This database will have data and catalog of all the databases of SQL Server instance.
Answer: Alternate Key — To identify a row uniquely we can have multiple keys one of them is called primary key and rest of them are called alternate keys. Candidate Key — Set of fields or columns which are uniquely identified in a row and they constitute candidate keys.
Composite Key — One key formed by combining at least two or more columns or fields. What are subquery and its properties?
SQL Server DBA Interview Questions and Answers
I like your post very much. It is very much useful for my research. I hope you to share more info about this. Keep posting sql server DBA online training hyderabad. Very informative post I was searching for a few days. I am sure it will help me
In this article, we will discuss a number of questions that you may be asked when applying to a junior SQL Server database administrator DBA position. Which permission should you grant him? Q2: During your absence, you ask one of your colleagues to terminate a specific session if it takes more than one hour. To which Fixed Server-Level role should you add his account to perform that task? What server scoped permissions should you grants to that engineer in order to perform that task? Q5: A request received from the development team to grant permission for a specific service account that will create a new database in the SQL Server instance at the first application launch.
Top 50 SQL Server Interview Questions & Answers
Are you prepared in attending an interview? Are you worried for job interview preparation? Then do not worry, you are at a right place. Below are the list of frequently asked SQL DBA interview questions and answers which can make you feel comfortable to face the interviews:. Question 1.
Download PDF 1. What are the two authentication modes in SQL Server? There are two authentication modes — Windows Mode Mixed Mode Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page. This is mainly used to capture and save data about each event of a file or a table for analysis.
Струя горячего воздуха, напоенного фреоном, ударила ему прямо в лицо. Клубы пара вырвались наружу, подкрашенные снизу в красный цвет контрольными лампами.