SQL Server DBA Interview Questions
Q1) MS SQL Server Vs MySQL
Answer: MS SQL Server and MySQL both are used widely for enterprise database systems
MySql is open-source Real-time Database management system,MS SQL Server is the licensed proprietary software, MySQL is used to update the database,MySQL server is used to maintain the data
Q2) What purpose does the model database server?
Answer: It is used for the template for all kinds of databases which is created for the instance of MS SQL Server. When we started SQL Server it will create TEMPDB is create. This kind of databases should exist on SQL Servers only know more at Sql server dba online training
Q3) How do you trace the traffic hitting a SQL Server?
Answer: It is the SQL Sever profiler to monitor the traffic. By using this utility we control the traffic on a particular instance.
Q4) What types of replication are supported in SQL Server?
Answer: It is divided into 3 types of replications. They are
Snap: It is used to capture snap short of instance
Merge: It uses the snap replication and it will act as a central repository serve
Dealing: it will be based on both merge and snap
Q5) Why would you use SQL Agent?
Answer: SQL Agent is a mechanism of job programming, it is used to monitor the job for the events in server
Q6) What happens on checkpoint?
Answer: Checkpoints are one of the data recovery mechanisms in the SQL server. It is used to maintain the modifications and changes in a cache of the database page
Q7) What is DBCC?
Answer: DBCC Stands for database console commands it is available in 4 types.They are
- Maintenance
- Miscellaneous
- Validation
- Informational
Q8) How can you control the amount of free space in your index pages?
Answer: This will tell the SQL Server what proportion free house to depart within the index pages once re-indexing.
performance of profit here is some page to splits (where the SQL Server needs to rows copy from the one index page associate to other to the different} to create space for the row inserted) as a result of there is space for growth engineered into the index.
Q9) Why would you call Update Statistics?
Answer: It is employed to force the computation of question optimization statistics for a table or indexed read.
Query optimization statistics area unit mechanically re-computed, but it is some times, a query might benefit from update those kinds of statistics to more frequently, Beware although that re-compute the question statistics are causes to queries to be re-compiled.
Q10) What is a correlated sub-query?
Answer: It is the nested query which is linked to the outer side query. To the instance, we can say that needed to search out all the workers in WHO they do not enter time for the whole week. know more at SQL SERVER dba training
Q11) What authentication modes does SQL Server support?
Answer: SQL Server supports Windows Authentication and mixed-mode. Mixed-mode permits you to use each Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note that if you use Windows Authentication, you will not be able to log in as sa.
Q12) Explain about your SQL Server DBA Experience.
Answer: This is a generic question typically asked by several interviewers. Explain what ar the various SQL Server Versions you have got worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the solution that lets the asker skills appropriate ar you for the position to that you’re being interviewed.
Q13) What are the different SQL Server Versions you have worked on?
Answer: It is depending the candidate worked on versions, Mainly SQL servers are 2000,2008,2005, SQL Server 7
Q14) What are the different types of Indexes available in SQL Server?
Answer: It is mainly divided into two types. They are Non-Clustered Indexes and Clustered
Q15) What is the difference between Clustered and Non-Clustered Index?
Answer: When the clustered index is formed on a table, the data pages are arranged according to its key. There will solely be a Clustered index on a table.
In a Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages. There will multiple non-clustered indexes on one table.
Q16) What are the new features in SQL Server 2005 when compared to SQL Server 2000?
Answer: Below is the list
- Database Snapshots
- Dynamic Management Views
- Resource Database
- SQL Server Integration Services
- Database Partitioning
- System Catalog Views
- Support for Analysis Services on a Failover Cluster.
Q17) What are the High-Availability solutions in SQL Server and differentiate them briefly.
Answer: Log Shipping, Database Mirroring, Failover agglomeration and Replication are the features High-available in SQL Server.
Q18) How do you troubleshoot errors in a SQL Server Agent Job?
Answer: The job activity monitor displays the present standing of all the roles in the instance. Choose the actual job that unsuccessful, right-click and choose view history from the drop-down menu. The execution history of the duty is displayed and you will select the execution time (if the duty unsuccessful multiple times throughout the constant day). There would info like the time it took to execute that Job and details concerning the error occurred.
Q19) What is the default Port No on which SQL Server listens?
Answer: 1433
Check Out SQL Server DBA Tutorials
know more at SQL server dba online course
Q20) what number files will a piece of information contain in SQL Server? How many forms of information files exist in SQL Server? How many of those files can exist for a single database?
Answer:
- A piece of information will contain most of thirty-two,767 files.
- There are Primarily 2 types of data files Primary data file and Secondary data file(s)
- There are just one Primary file and multiple secondary information files as long as the total # of files is a smaller amount than thirty-two,767 files
Q21) What is DCL?
Answer: DCL stands for Data Control Language.
Q22) What are the commands used in DCL?
Answer: RANT, DENY and REVOKE.
Q23) What is Fill Factor?
Answer: Fill issue could be a setting that’s applicable to Indexes in SQL Server. The fill issue price determines what proportion information is written to the AN index page once it’s created/rebuilt.
Q24) What is the default fill factor value?
Answer: By default, the fill issue price is about to zero.
Q25) Where do you find the default Index fill factor and how to change it?
Answer: The easiest thanks to noticing and alter the default fill issue price are from Management Studio, right-click the SQL Server and choose properties. You can change to the desired value there and click OK to save the changes.
The other possibility of viewing and dynamic this price is victimization
Q26) What is a system database and what is a user database?
Answer: System databases area unit the default databases that area unit put in once the SQL Server is put in. Basically, there area unit four system databases: Master, MSDB, TempDB, and Model. It is highly recommended that these databases are not modified or altered for the smooth functioning of the SQL System.
User info could be info that we have a tendency to produce to store information and begin operating with the info.
Q27) What are the recovery models for a database?
Answer: There area unit three recovery models on the market for info. Full, Bulk-Logged and straightforward area unit the 3 recovery models on the market. know more at SQL server dba online training from India
Q28) 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 own a token or no information loss, selecting the complete recovery model could be a good selection. Depending on the recovery model of info, the behavior of database log file changes. I would suggest you scan a lot of material on log backups and log file behavior and then on to grasp comprehensive.
Q29) What is Replication?
Answer: Replication could be a feature in SQL Server that helps America publish info objects and information and duplicate (replicate) it to at least one or a lot of destinations. It is typically thought of joined of the High-Availability choices. One of the advantages of Replication is that it can be con
