Interview Questions.

Question 1. What Purpose Does The Model Database Server?

Answer :

The model database, as its call implies, serves because the version (or template) for all databases created on the same instance. If the model database is modified, all next databases created on that example will pick up the ones adjustments, but earlier created databases will now not. Note that TEMPDB is also constructed from version whenever SQL Server starts offevolved up.

Question 2. How Do You Trace The Traffic Hitting A Sql Server?

Answer :

SQL profiler is the SQL Server utility you can use to trace the site visitors at the SQL Server example. Traces may be filtered to slender down the transactions which are captured and reducing the overhead incurred for the trace. The hint documents can be searched, saved off, or even replayed to facilitate troubleshooting.

SQL Server 2000 Interview Questions
Question three. What Types Of Replication Are Supported In Sql Server?

Answer :

SQL Server has 3 varieties of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a image of the facts (factor-in-time image of the information) to supply to the subscribers. This is a superb kind to apply whilst the information modifications from time to time, there may be a small quantity of facts to duplicate, or huge adjustments arise over a small time period.
Merge replication uses a picture to seed the replication. Changes on both aspects of the e-book are tracked so the subscriber can synchronize with the publisher when linked. A standard use for this sort of replication is in a client and server scenario. A server could act as a valuable repository and a couple of customers might independently update their copies of the information until linked. At which time, they would all send up their adjustments to the imperative store.
Transaction replication also begins with a photograph best this time changes are tracked as transactions (as the name implies). Changes are replicated from writer to subscriber similar to they took place at the publisher, in the identical order as they befell, and in near actual time. This type of replication is useful when the subscriber wishes to know every change that passed off to the facts (no longer point-in-time), whilst the trade extent is excessive, and while the subscriber desires near actual-time get admission to to the changes.
Question 4. Why Would You Use Sql Agent?

Answer :

SQL Agent is the task scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or whilst a particular event occurs. Jobs also can be performed on demand. SQL Agent is most customarily used to agenda administrative jobs inclusive of backups.

T-SQL Tutorial
Question 5. What Happens On Checkpoint?

Answer :

Checkpoints, whether scheduled or manually performed, cause the transaction log to be truncated up to the start of the oldest open transaction (the energetic portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing devoted transactions in the cache offers a performance advantage for SQL Server. However, you do now not need the transaction log to get too large because it would eat too many sources and, ought to your database fail, take too lengthy to manner to get better the database.

One vital aspect to word here is that SQL Server can best truncate up to the oldest open transaction. Therefore, if you are not seeing the anticipated alleviation from a checkpoint, it may very well be that a person forgot to dedicate or rollback their transaction. It may be very critical to finalize all transactions as soon as feasible.

PL/SQL Interview Questions
Question 6. What Is Dbcc?

Answer :

DBCC statements are Database Console Commands and come in 4 flavors:

Maintenance, Informational, Validation, and Miscellaneous. Maintenance instructions are those instructions that permit the DBA to perform upkeep activities at the database which includes shrinking a file. Informational commands offer remarks concerning the database which include presenting statistics about the technique cache. Validation commands encompass instructions that validate the database consisting of the ever-famous CHECKDB. Finally, miscellaneous instructions are people who glaringly don’t healthy in the different three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.

Question 7. How Can You Control The Amount Of Free Space In Your Index Pages?

Answer :

You can set the fill component on your indexes. This tells SQL Server how a great deal loose space to depart within the index pages while re-indexing. The overall performance advantage here is fewer page splits (in which SQL Server has to copy rows from one index page to every other to make room for an inserted row) because there may be room for growth built in to the index.

SQL Server 2008 Tutorial T-SQL Interview Questions
Question 8. Why Would You Call Update Statistics?

Answer :

Update Statistics is used to force a recalculation of question optimization information for a table or listed view. Query optimization data are automatically recomputed, but in some instances, a question may additionally advantage from updating the ones records extra often. Beware even though that re-computing the question statistics reasons queries to be recompiled. This may also or might not negate all performance gains you may have achieved by calling replace data. In truth, it is able to have a terrible effect on overall performance depending on the traits of the machine.

Question nine. What Is A Correlated Sub-query?

Answer :

A correlated sub-query is a nested query that is related to the outer question. For example, say I desired to discover all of the employees who've no longer entered their time for the week. I should query the Employee desk to get their first and last name, but I need to take a look at the TimeEntry desk to peer if they’ve entered their time or not. I can’t do a instantly be a part of right here because I’m searching out the absence of time facts, so I’ll do a correlated sub-question much like this:

SELECT FirstName, LastName

FROM EMPLOYEE e

WHERE NOT EXISTS (SELECT 1 FROM TimeEntry te

WHERE te.EmpID = e.EmpID

AND te.WeekID = 35)

Notice that the inner query relates to the outer question at the employee ID, for that reason making it a correlated sub-question. The internal query may be evaluated as soon as in line with outer query row.

SQL Server 2008 Interview Questions
Question 10. What Authentication Modes Does Sql Server Support?

Answer :

SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use each Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s vital to be aware that if you use Windows Authentication, you will now not be able to log in as sa.

SQL Database Tutorial
Question eleven. Explain About Your Sql Server Dba Experience?

Answer :

This is a ordinary question regularly requested by means of many interviewers. Explain what are the one-of-a-kind SQL Server Versions you have worked on, what sort of management of those times has been accomplished by means of you. Your role and duties achieved in your earlier projects that might be of importance to the ability agency. This is the answer that we could the interviewer know how appropriate are you for the location to which you are being interviewed.

SQL Server 2005 Interview Questions
Question 12. What Are The Different Sql Server Versions You Have Worked On?

Answer :

The answer might be depending at the versions you've got labored on, I could say I have enjoy running in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have got labored simplest the a few version be sincere in pronouncing that, take into account, no one might be running on all versions, it varies from man or woman to individual.

SQL Server 2000 Interview Questions
Question 13. What Are The Different Types Of Indexes Available In Sql Server?

Answer :

The most effective answer to that is “Clustered and Non-Clustered Indexes”. There are different types of Indexes what may be mentioned along with Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.

MySQL Tutorial
Question 14. What Is The Difference Between Clustered And Non-clustered Index?

Answer :

In a clustered index, the leaf level pages are the actual records pages of the desk. When a clustered index is created on a desk, the facts pages are organized accordingly primarily based at the clustered index key. There can simplest be one Clustered index on a table.

In a Non-Clustered index, the leaf level pages does now not incorporate data pages as a substitute it carries pointers to the statistics pages. There can a couple of non-clustered indexes on a single table.

Question 15. What Are The New Features In Sql Server 2005 When Compared To Sql Server 2000?

Answer :

There are pretty quite a few adjustments and improvements in SQL Server 2005. Few of them are indexed right here :

Database Partitioning
Dynamic Management Views
System Catalog Views
Resource Database
Database Snapshots
SQL Server Integration Services
Support for Analysis Services on a a Failover Cluster.

Profiler being capable of hint the MDX queries of the Analysis Server.
Peer-toPeer Replication
Database Mirroring
SQL Database Interview Questions
Question sixteen. What Are The High-availability Solutions In Sql Server And Differentiate Them Briefly?

Answer :

Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features to be had in SQL Server. I might advise reading this weblog of mine which explains the differences between those 4 functions.

Question 17. How Do You Troubleshoot Errors In A Sql Server Agent Job?

Answer :

Inside SSMS, in Object explorer under SQL Server Agent search for Job Activity Monitor. The process pastime screen shows the modern-day repute of all of the jobs on the example. Choose the precise job which failed, right click and select view history from the drop down menu. The execution history of the process is displayed and you could select the execution time (if the job failed multiple instances all through the identical day). There would data inclusive of the time it took to execute that Job and information about the error occurred.

MySQL Interview Questions
Question 18. What Is The Default Port No On Which Sql Server Listens?

Answer :

1433

PL/SQL Interview Questions
Question 19. How Many Files Can A Database Contain In Sql Server?How Many Types Of Data Files Exists In Sql Server? How Many Of Those Files Can Exist For A Single Database?

Answer :

A Database can contain a maximum of 32,767 files.
There are Primarily 2 forms of data documents Primary records file and Secondary facts report(s)
There can be best one Primary records document and a couple of secondary facts documents as long as thetotal # of documents is less than 32,767 documents
Question 20. What Is Dcl?

Answer :

DCL stands for Data Control Language.

PostgreSQL Interview Questions
Question 21. What Are The Commands Used In Dcl?

Answer :

GRANT, DENY and REVOKE.

Question 22. What Is Fill Factor?

Answer :

Fill Factor is a putting this is relevant to Indexes in SQL Server. The fill aspect fee determines how a whole lot facts is written to an index web page while it's miles created / rebuilt.

Question 23. What Is The Default Fill Factor Value?

Answer :

By default the fill issue value is about to 0.

SQL DBA Interview Questions
Question 24. Where Do You Find The Default Index Fill Factor And How To Change It?

Answer :

The simplest manner to locate and alternate the default fill issue price is from Management Studio, proper-click on the SQL Server and pick residences. In the Server Properties, pick Database Settings, you ought to see the default fill factor cost within the pinnacle section. You can trade to a preferred fee there and click OK to keep the changes. The other choice of viewing and converting this value is using

T-SQL Interview Questions
Question 25. What Is A System Database And What Is A User Database?

Answer :

System databases are the default databases that are established when the SQL Server is set up. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is extraordinarily advocated that these databases aren't modified or altered for smooth functioning of the SQL System.

A person database is a database that we create to store statistics and begin operating with  the statistics.

Question 26. What Are The Recovery Models For A Database?

Answer :

There are three recuperation models to be had for a database. Full, Bulk-Logged and Simple are the three restoration models available.

SQL Interview Questions
Question 27. What Is The Importance Of A Recovery Model?

Answer :

Primarily, recuperation version is selected maintaining in view the quantity of statistics loss one can afford to. If one expects to have minimum or no data loss, deciding on the Full recovery model is a good preference. Depending at the healing model of a database, the conduct of database log report adjustments. I might recommend you study more fabric on log backups and log document behavior and so on to recognize intensive.

SQL Server 2008 Interview Questions
Question 28. What Is Replication?

Answer :

Replication is a function in SQL Server that enables us post database items and records and duplicate (mirror) it to 1 or greater destinations. It is often considered as one of the High-Availability alternatives. One of the advantages with Replication is that it can be configured on databases which can be in simple healing version.

Question 29. What The Different Types Of Replication And Why Are They Used?

Answer :

There are essentially 3 styles of replication: Snapshot, Transactional and Merge Replication. The kind of Replication you choose, depends on the requirements and/or the desires one is making an attempt to reap.For example Snapshot Replication is useful best while the facts inside the tables does not exchange regularly and the amount of information isn't too big, consisting of a month-to-month summary desk or a product listing desk and so on. Transactional Replication would useful whilst preserving a copy of a transactional table consisting of sales order tables etc. Merge Replication is more useful in case of  remote / disbursed systems where the information float may be from a couple of web sites, as an instance income accomplished at a promotional events which might not be linked to the significant servers constantly.

Anz Sql Server Interview Questions
Question 30. What The Different Components In Replication And What Is Their Use?

Answer :

The 3 major additives in Replication are Publisher, Distributor and Subscriber. Publisher is the records source of a ebook. Distributor is responsible for distributing the database objects to at least one or extra destinations. Subscriber is the vacation spot wherein the publishers facts is copied / replicated.

Question 31. What The Different Topologies In Which Replication Can Be Configured?

Answer :

Replication can be configured in any topology relying retaining in view of the complexity and the workload of the complete Replication. It can be any of the subsequent:

Publisher, Distributor and Subscriber at the identical SQL Instance.
Publisher and Distributor on the same SQL Instance and Subscriber on a separate Instance.
Publisher,Distributor and Subscriber on character SQL Instances.
Question 32. If You Are Given Access To A Sql Server, How Do You Find If The Sql Instance Is A Named Instance Or A Default Instance?

Answer :

I would visit the SQL Server Configuration Manager.In the left pane of the tool, I would select SQL Server Services, the right aspect pane displays all the SQL Server Services / components which are hooked up on that machine. If the Service is displayed as (MSSQLSERVER), then it shows it's miles a default instance, else there can be the Instance name displayed.

Complex SQL Queries Interview Questions
Question 33. What Are The Different Authentication Modes In Sql Server And How Can You Change Authentication Mode?

Answer :

SQL Server has 2 Authentication modes: Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode.

SQL Server 2005 Interview Questions
Question 34. What Are The Differences In Clustering In Sql Server 2005 And 2008 Or 2008 R2?

Answer :

On SQL Server 2005, putting in SQL Server failover cluster is a single step procedure whereas on SQL Server 2008 or above it's far a multi-step method. That is, in SQL Server 2005, the Installation system itself installs on all the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we might want to put in one at a time on all the nodes. 2 times if it's far a 2 node cluster or three times in a 3 node cluster and so forth.

Question 35. What Is Meant By Active – Passive And Active – Active Clustering Setup?

Answer :

An Active – Passive cluster is a failover cluster configured in a manner that only one cluster node is lively at any given time. The other node, referred to as as Passive node is always on line but in an idle situation, watching for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this turns into the Active Node, the preceding Active Node now being a Passive Node.

An Active –Active cluster is a failover cluster configured in a way that both the cluster nodes are lively at any given factor of time. That is, one Instance of SQL Server is walking on each of the nodes always; whilst one of the nodes has a failure, each the Instances run at the handiest one node until the failed node is delivered up (after fixing the difficulty that brought about the node failure). The example is then failed over returned to its specified node.

Question 36. List Out Some Of The Requirements To Setup A Sql Server Failover Cluster.?

Answer :

Virtual network call for the SQL Server, Virtual IP cope with for SQL Server, IP addresses for the Public Network and Private Network(additionally referred as Hearbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log documents, Quorum Disk and MSDTC Disk.

SQL Database Interview Questions
Question 37. On A Windows Server 2003 Active – Passive Failover Cluster, How Do You Find The Node Which Is Active?

Answer :

Using Cluster Administrator, connect with the cluster and select the SQL Server cluster.  Once you have got decided on the SQL Server group, in the right hand facet of the console, the column“Owner” gives us the records of the node on which the SQL Server organization is presently energetic.

Question 38. How Do You Open A Cluster Administrator?

Answer :

From Start -> Run and sort CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you could also go to Start -> All packages -> Administrative Tools -> Cluster Administrator.

Question 39. Due To Some Maintenance Being Done, The Sql Server On A Failover Cluster Needs To Be Brought Down. How Do You Bring The Sql Server Down?

Answer :

In the Cluster Administrator, rick click on the SQL Server Group and from the popup menu object choose Take Offline.

Question 40. What Are The Different Ways You Can Create Databases In Sql Server?

Answer :

T-SQL; Create Database command.
Using Management Studio
Restoring a database backup
Copy Database wizard
MySQL Interview Questions
Question 41. When Setting Replication, Can You Have Distributor On Sql Server 2005, Publisher On Sql Server 2008?

Answer :

No you can not have a Distributor on a previous version than the Publisher.

Question 42. When Setting Replication, Is It Possible To Have A Publisher As sixty four Bit Sql Server And Distributor Or Subscribers As A 32 Bit Sql Server.

Answer :

Yes it is feasible to have numerous configurations in a Replication surroundings.

PostgreSQL Interview Questions
Question 43. What Is The Difference Between Dropping A Database And Taking A Database Offline?

Answer :

Drop database deletes the database along side the physical files, it isn't viable to deliver returned the database except you've got a backup of the database. When you're taking a database offline, you the database is not available for customers, it is not deleted physically, it is able to be introduced lower back online.

Question 44. Which Autogrowth Database Setting Is Good?

Answer :

Setting an autogrowth in multiples of MB is a better option than placing autogrowth in percentage (%).

Question forty five. What Are The Different Types Of Database Compression Introduced In Sql Server 2008?

Answer :

Row compression and Page compression.

Question forty six. What Are The Different Types Of Upgrades That Can Be Performed In Sql Server?

Answer :

In-area improve and Side-by using-Side Upgrade.

Question forty seven. What Is Transparent Data Encryption?

Answer :

Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you may guard the SQL Server Database files from unauthorized get admission to via encryption. Also, TDE can protect the database backups of the example on which TDE was setup.

Question 48. Does Transparent Data Encryption Provide Encryption When Transmitting Data Across Network?

Answer :

No, Transparent Data Encryption (TDE) does now not encrypt the statistics all through switch over a communique channel.

Question 49. What Are The Operating Modes In Which Database Mirroring Runs?

Answer :

Database Mirroring runs in 2 running modes High-Safety Mode and High-Performance Mode.

Question 50. What Is The Difference Between The 2 Operating Modes Of Database Mirroring?

Answer :

High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized nation, that is the transactions are devoted on the same time on each servers to make certain consistency, but there is/is probably a time lag.
High-Performance Mode is to make certain that the Principal database run quicker, via now not anticipating the Mirrored database to devote the transactions. There is a mild danger of information loss and additionally the Mirrored database may be lagging behind (in phrases being updated with Principal database) if there is a heavy load on the Mirrored Server.