Interview Questions.

Question 1. What Is The Difference Between Lock, Block And Deadlock?

Answer :

Lock: DB engine locks the rows/page/desk to access the facts that is worked upon in line with the query.

Block: When one system blocks the sources of some other method then blocking happens. Blocking can be identified by using

SELECT * FROM sys.Dm_exec_requests wherein blocked <> 0
SELECT * FROM grasp..Sysprocesses where blocked <> 0

Deadlock: When something occurs as follows: Error 1205 is reported by means of SQL Server for impasse.

Question 2. What Is The Meaning Of Lock Escalation And Why/how To Stop This?

Answer :

remember that whole table would be locked for the processing thenn this is better to apply TABLOCK trace and get entire table blocked. This is a pleasing way to avoid the wastage of sq. Server DB engine processing for lock escalation. Somewhere you may also want to apply TABLOCKX whilst you want an distinct lock at the table inside the query.

SQL Server 2000 Interview Questions
Question three. How To Truncate The Log In Sql Server 2008?

Answer :BACKUP LOG TestDB WITH TRUNCATE_ONLY is long past. SQL server doesn’t can help you truncate the log now in any other case complete cause of a DB is defeated.

Question 4. What Changes In The Front End Code Is Needed If Mirroring Is Implemented For The High Availability?

Answer :

You want to add best FAILOVER PARTNER information in your the front cease code. “Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;”.

SQL Server 2008 Tutorial
Question five. Where Does The Copy Job Runs In The Log Shipping Primary Or Secondary?

Answer :

Secondary server. This question is basically asked to discover whether or not you have a palms on work on log transport or now not.

SQL Server 2008 Interview Questions
Question 6. What Are The Ways To Find What Code Is Running For Any Spid?

Answer :

Well there are many approaches to do this.

Find the spid that you want to research. An spid is assigned as quickly as a consumer connection is mounted with the SQL server. To find the spid you may run any of the following command:
SP_WHO2 ‘ACTIVE’ — This will come up with handiest energetic spids.
SELECT * FROM sys.Dm_exec_requests
Get the spid from above two queries and use any of the following query to get what's happening behind that spid.
Dbcc inputbuffer()
sql2005 and sql2008 – SELECT * FROM sys.Dm_exec_sql_text()
sql2005 and sql2008 – SELECT * FROM fn_get_sql()
Question 7. When You Get Following Error? Error 3154: The Backup Set Holds A Backup Of A Database Other Than The Existing Database?

Answer :

The error comes when you are trying to repair the DB which already exists. Use WITH REPLACE option to repair the DB with a exceptional name

Oracle DBA Tutorial SQL Server 2005 Interview Questions
Question 8. Does Dbcc Checkdb Requires Db To Be In Single_user Mode?

Answer :

Yes and No. This is difficult query. If you're the use of restore alternative with CHECKDB then you need to have the DB in single consumer mode. Following is the technique to have your DB in a unmarried user mode.

Use master
move sp_dboption dbname, unmarried, real

Following is the mistake that you get when you run the DBCC CHECKDB with restore alternative wo having the DB in single user mode. The identical is proper for DBCC CHECKDB also

Question nine. How To View The Error Log For Any Specific Instance?

Answer :

There are many methods but I select following method. Take a state of affairs whilst you need to discover the error log whilst the DB become installed a unmarried person mode.

CREATE TABLE #Errorlog (Logdate Datetime, Processinfo
VARCHAR(20),Text VARCHAR(2000))
INSERT INTO #Errorlog
EXEC xp_readerrorlog
SELECT * FROM #Errorlog
WHERE Text Like ‘%SINGLEp.CUSER%’

Oracle DBA Interview Questions
Question 10. According To You What Goes Into Making The Best Database Administrator?

Answer :

The number one task of DBAs is to comfy the facts. They must be able to hold it secure in addition to reproduce it successfully, whenever required. So as in step with my view, a Database Administrator who can satisfy the necessities of Securing Data and Retrieving Data is the quality DBA.

When I rent a DBA I constantly ask them questions about backup techniques and efficient restoring methodologies.

MySQL Tutorial
Question 11. I Have All The Primary Data Files, Secondary Data Files As Well As Logs. Now, Tell Me Can I Still Restore The Database Without Having A Full Backup?

Answer :

You can't repair the database without having a complete database backup. However, if you have the copy of all the statistics documents (.Mdf and .Ndf) and logs (.Ldf) whilst database was in operating circumstance (or your favored kingdom) it's far possible to connect the database using sp_attach_db.

MySQL Interview Questions
Question 12. As Per Your Opinion What Are The Five Top Responsibilities Of A Dba?

Answer :

I charge the following five duties as the key obligations of a DBA.

Securing database from physical and logical integrity harm.
Restoring database from backup as a part of disaster management plan.
Optimizing queries performance with the aid of suitable indexing and optimizing joins, wherein conditions, select clause etc.
Designing new schema, guide legacy schema, and legacy database systems.
Helping builders improve their SQL-associated code writing talent.
 

SQL Server 2000 Interview Questions
Question 13. One Of The Developers In My Company Moved One Of The Columns From One Table To Some Other Table In The Same Database. How Can I Find The Name Of The New Table Where The Column Has Been Moved?

Answer :

This question may be spoke back by means of querying system perspectives.

For SQL Server 2005 run the subsequent code:

SELECT OBJECT_NAME(OBJECT_ID) TableName
FROM sys.Columns
WHERE name = 'YourColumnName'

The previous query will return all the tables that use the column name detailed within the WHERE situation. This is a very small but a totally available script.

Question 14. What Is The Difference Between Sql Server 2000 Object Owner And Sql Server 2005 Schema?

Answer :

Let us first see the absolutely qualified query name to get admission to a table for SQL Server 2000 and SQL Server 2005.

SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
SQL Server 2008: [DataBaseServer].[DataBaseName].[Schema].[Table]

In SQL Server 2000, previous to dropping the consumer who owns database gadgets, all of the items belonging to that person both need to be dropped or their owner has to be modified. Every time a consumer is dropped or modified, gadget admin has to undergo this inconvenient procedure.

In SQL Server 2005 and the later variations, as opposed to accessing a database thru database proprietor, it could be accessed through a schema. Users are assigned to schemas, and via the usage of this schema a consumer can get admission to database gadgets. Multiple customers may be assigned to a single schema, and they all can routinely obtain the equal permissions and credentials as the schema to which they are assigned. Because of the identical cause in SQL Server 2005 and the later versions – when a person is dropped from database – there's no bad impact on the database itself.

Question 15. What Is Bi? I Have Heard This Term Before But I Have No Idea About It?

Answer :

BI stands for Business Intelligence. Microsoft started to sell the acronym BI since the launch of SQL Server 2005. However, it has been in use for a long term. The simple idea of BI is pretty similar to Data Warehousing. Business intelligence is a technique for storing and imparting accurate and well timed key organization records to CXO, IT Managers, Business Consultants, and dispensed teams of a corporation, to offer them with up to date records to drive smart choices for enterprise achievement, which in the long run ends in improved revenue, reduced risk, decreased value, and better operational manipulate for commercial enterprise agility and competitiveness. An powerful BI empowers quit users to apply statistics to apprehend the cause that led to a specific enterprise end result, to decide at the direction of motion primarily based on beyond records, and to correctly forecast future results.

TeraData DBA Interview Questions
Question 16. What Is Your Recommendation For A Query Running Very Slow?

Answer :

Well, your query is very hard to answer without searching on the code, software and physical server. In such conditions, there are some matters that have to be paid interest to proper away.

Restart Server
Upgrade Hardware

Check Indexes on Tables and Create Indexes if essential Make sure SQL Server has priority over other operating machine approaches in SQL Server settings.

Update statistics at the database tables.

Question 17. What Should Be The Fill Factor For Indexes Created On Tables?

Answer :

Fill element specifies a percent that shows how complete the Database Engine ought to make the leaf level of every index web page all through index advent or alteration. Fill thing ought to be an integer fee from 1 to 100. The default is zero. I opt to hold my servers default fill aspect as ninety.

Oracle MySQL 5.6 Database Administrator Interview Questions
Question 18. Which Feature In Sql Server 2008 Has Surprised You? You Can Name Just One.

Answer :

Plan Freezing is a brand new characteristic I by no means notion of. I locate it very exciting! It is covered in SQL Server 2008 CTP5. SQL Server 2008 enables more question performance balance and predictability by using offering new capability to fasten down query plans. This empowers companies to sell solid question plans across hardware server replacements, server upgrades, and production deployments.

SQL Server 2008 Interview Questions
Question 19. How Do You Test Your Database?

Answer :

This is a very generic query. I would like to explain my widely wide-spread database checking out technique in addition to stored procedure checking out techniques.

Testing Databases:

Table Column facts kind and data value validation.
Index implementation and overall performance improvement.
Constraints and Rules have to be established for statistics integrity.
Application area duration and type should match the corresponding database field.
Database objects like saved strategies, triggers, functions must be examined using extraordinary sorts of enter values and checking the anticipated output variables.

Testing Stored Procedures:

Understand the necessities in terms of Business Logic.
Check if the code follows all the coding standards.
Compare the fields’ requirements of software to the fields retrieved by a saved method. They need to suit.
Repeatedly run the saved strategies numerous instances with specific input parameters after which examine the output with the predicted effects.
Pass invalid input parameters and see if a stored technique has true error handling.

Question 20. What Are System Databases Into Sql Server (2005/2008)

Answer :

TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource.

Oracle Performance Tuning Interview Questions
Question 21. What Stored By The Tempdb ?

Answer :Row variations, cursor, temp gadgets.

Question 22. What Stored By The Model?

Answer :

Templates of latest database items, like tables and column.

Question 23. What Stored By The Master?

Answer :

Server’s configurations and logins.

Sql Server Dba Interview Questions
Question 24. What Stored By The Msdb?

Answer :

Scheduled jobs, Backup/Restore and DTA information.

SQL Server 2005 Interview Questions
Question 25. Can We Perform Backup Restore Operation On Tempdb?

Answer :

NO

Question 26. What Is Stored In The Mssqlsystemresource Database?

Answer :

Definition of sys items, which logically shows into all database and DMVs.

Question 27. Where The Sql Logs Gets Stored?

Answer :

It’s saved into root folder SQL server, LOG folder.

Oracle DBA Interview Questions
Question 28. What Are The Joins In Sql Server?

Answer :

Inner Join, Outer (Left Outer & Right Outer) Joins and Cross join.

Question 29. Describe The Left Outer Join & Right Outer Join.

Answer :

Left Outer be part of Retrieves the all records from LEFT table and matching from the RIGHT desk, and null values in which is no suit. Right Outer Join simply opposite.

Question 30. How To Find The Version Of Sql Server?

Answer :

Select @@model

Question 31. How To Find The Service Pack Installed?

Answer :

Select @@version Or pick out serverproperty (‘productlevel’)

Question 32. What Are The Difference Between Primary Key And Unique Key?

Answer :

An precise key cant now not be referenced as foreign key. And it is able to allow on null.

Question 33. What Is Mean By Clustered Index And Non Clustered Index, Give Syntax Of Creation?

Answer :

create clustered index index_name on empmst(card)

MySQL Interview Questions
Question 34. What Is Scan Table/view And Seek Table/view When Its Occurs?

Answer :

A Table/view SCAN takes place whilst no useful indexes exist. A TABLE SCAN reads all information, row through row, to locate the match.

Question 35. What Is Sql Profiler. What Are The Default Templates With It?

Answer :

SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an example of the Database Engine or Analysis Services. You can capture and shop statistics about every event to a record or table to research later.

Question 36. What Are The Dmvs?

Answer :

Dynamic Management Views (DMV) go back server state facts that can be used to screen the health of a server example, diagnose issues, and song performance.

TeraData DBA Interview Questions
Question 37. What Is The Syntax To Execute The Sys.Dm_db_missing_index_details?

Answer :

Select * from sys.Dm_db_missing_index_details

Question 38. What Is Lock Escalation?

Answer :

The Query Optimizer to begin with locks the required Rows for DML operations / Retrieval operations. This also get applicable Pages and Completely desk to be 'INTENT' Locked. If greater than 50% of rows are mainly locked then this mechanically receives the complete PAGE or TABLE to be locked. Lock Escalation mechanism may be controlled via the use of Locking Hints.

Question 39. How To Truncate The Log In Sql Server 2012?

Answer :

BACKUP LOG TestDB WITH TRUNCATE_ONLY is long past. SQL server does now not permit you to truncate the log now in any other case entire reason of a DB is defeated. You should make sure whether or not you need log or not. If you do not want log then have the restoration version easy rather than full. If you do no longer want the log to be amassed in some specific bulk logging then exchange the recuperation model BULK LOGGED for that duration and take one TLog Backup simply earlier than and after this change. I shall speak this later in my later weblog. BACKUP LOG command backs up the t-log and frees the gap within the log file.

Question forty. What Is The Purpose Of Sql Profiler In Sql Server?

Answer :

SQL profiler is a device to display overall performance of diverse saved tactics. It is used to debug the queries and techniques. Based on overall performance, it identifies the sluggish executing queries. Capture any troubles by way of shooting the occasions on manufacturing surroundings in order that they can be solved.

Oracle MySQL 5.6 Database Administrator Interview Questions
Question forty one. What Is Copyonly Data Backup? How This Is Useful?

Answer :

There are 21 varieties of Backups in SQL Server. A non-base FULL BACKUP is called COPYONLY Data Backup. This is used to provider 'on call for' Backup requests from cease users. Using this kind of Backups, we can carrier the 'on call for' backup requests from end customers without stressful the Backup collection of Jobs / Maintenance Plans or other differential Backups. This gives us smooth repair paths as properly in case of DR manner.

Question 42. What Are Truncate Options Available In Sql Server?

Answer :

Use TRUNCATE_ONLY alternative for SQL Server 2005 structures while acting Backup. This alternative is not available in SQL Sever 2008 and R2. Instead, we use ON_TRUNCATE option to be had inside the Backup announcement.

Oracle Performance Tuning Interview Questions
Question 43. What Are Advantages Of Peer-peer Replication?

Answer :

Peer-Peer Replication decreases / nullifies the dependency on Distributor. In this Replication topology every node is Publisher, Distributor and Subscriber. This will increase availability of the database system and Failure of any node does not impact the health of Replication system. This topology also gives automated warfare detection and correction. Hence, recommended in Realtime.

Question forty four. What Options We Use To Secure Replication Data?

Answer :

Ensure that SQL Browser is walking and TCP/IP is enabled. Enforce TDE (Transparent Data Encryption) so that every statistics bit is encrypted.

Question 45. What Are The Uses Of Standby Mode In Log Shipping?

Answer :

If the Restore Jobs is scheduled / not on time for longer intervals this selection may be used. As uses needs to be disconnected each time Restore Job runs(to make certain well timed shipping of Transaction Logs) its advocated now not to use this option for normal, much less c program languageperiod Log Shipping configurations. Using this option gives us benefit of Load Balancing for READONLY connections and Reporting purposes at the value of well timed disconnections to help achievement of Restore Jobs.

Question 46. When Does Error 3154 Occur?

Answer :

The errors comes while you are trying to restore the DB which already exists. Use WITH REPLACE option to restore the DB with a distinct database name.

Question forty seven. What Are Recommended Options To Be Used While Using Db Mirroring?

Answer :

Database Mirroring is to be configured with TCP Protocol and make certain that statistics over every endpoint is encrypted. Better to utilize TDE for extra safety.

Question forty eight. Where Can You Find The Error Log Information?

Answer :

We can employ SQL Profiler , SQL Server Log or use xp_readerrorlog prolonged Stored Procedure to retrieve the error log data.

Question 49. What Is The Status Of Services On Passive Node For Failover Cluster In Sql Server?

Answer :

SQL offerings can be in guide and stopped. Cluster service might be in computerized and began mode on each the nodes.

Question 50. Can You Move The Resources After Pausing The Node?

Answer :

Yes sources can be moved after pausing the node. But we cannot flow them again until the node is paused.

Question fifty one. What Happens If We Start The Full Text Service On Passive Node.

Answer :

This may be commenced on both the nodes as this does not have any dependency on SQL service or any aid which is possessed with the aid of energetic node.

Question 52. What Is Data Compression?

Answer :

In SQL SERVER 2008 R2, Data Compression is available in two sorts viz., Row Compression in which It minimizes the metadata (column data, period, offsets, and so forth.) related to every file. Numeric facts sorts and glued period strings are saved in variable-length garage format, similar to Varchar. Page compression uses the Row compression approach internally and additionally applies prefix level compression.For each column in a web page, replica prefixes are recognized. These prefixes are saved in compression information headers (CI) which are living after web page headers. A reference number is assigned to those prefixes and that reference range is changed anywhere those prefixes are getting used.

Question fifty three. What Are The Basic Functions For Master, Msdb, Model, Tempdb And Resource System Databases?

Answer :

The master database holds statistics for all databases positioned at the SQL Server instance. As SQL Server cannot start with out a functioning grasp database, we ought to administer this database with care and display Startup Parameters in Configuration Manager.
The msdb database shops facts regarding database backups, SQL Agent statistics, DTS applications, SQL Server jobs, and a few replication information which includes for log delivery.
The tempdb holds brief gadgets inclusive of worldwide and neighborhood temporary tables and stored processes.
The model is essentially a template database used inside the introduction of any new user database created in the instance.
The aid Database is a study-most effective database that includes all of the gadget items which are included with SQL Server. SQL Server system items, together with sys.Objects, are physically persevered inside the Resource database, but they logically appear within the sys schema of every database. The Resource database does now not include person facts or consumer metadata.
Question 54. What Is Service Broker?

Answer :

Service Broker is a message-queuing generation in SQL Server that allows builders to integrate SQL Server fully into disbursed programs. Service Broker is feature which offers facility to SQL Server to ship an asynchronous, transactional message. It permits a database to ship a message to every other database without awaiting the reaction, so the application will maintain to feature if the remote database is quickly unavailable.

Question fifty five. Where Sql Server User Names And Passwords Are Stored In Sql Server?

Answer :

They get stored in System Catalog Views sys.Server_principals and sys.Sql_logins.

Question fifty six. What Is Policy Based Management (pbm)?

Answer :

Policy Based Management in SQL SERVER 2012 Administration lets in you to define and put into effect guidelines for configuring and coping with SQL Server across the business enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and enlarge the Management node and the Policy Management node; you may see the Policies, Conditions, and Facets nodes.

Question 57. What Is Replication With Database Mirroring?

Answer :

Database mirroring can be used with replication to provide availability for the e-book database. Database mirroring entails  copies of a single database that typically live on exceptional computers. At any given time, most effective one replica of the database is presently to be had to customers which might be known as the main database. Updates made by means of customers to the primary database are implemented on the alternative reproduction of the database, called the reflect database. Mirroring involves making use of the transaction log from each insertion, update, or deletion made at the principal database onto the mirror database.

Question 58. What Are Sparse Columns In Sql Server?

Answer :

A sparse column is another tool used to reduce the quantity of physical storage used in a database. They are the ordinary columns that have an optimized garage for null values. Sparse columns lessen the gap requirements for null values at the cost of greater overhead to retrieve non null values.

Question 59. What Are The Steps To Take To Improve Performance Of A Poor Performing Query?

Answer :

Steps to take to improve overall performance of queries:

Use indexes efficaciously
Create all number one and foreign keys and relationships amongst tables.
Avoid using cursors
Avoid the use of Select*, rather point out the wanted columns and slim the resultset as wanted.
Denormalize
Use partitioned perspectives
Use temporary tables and desk variables
Reduce joins and heavy clauses like GROUP BY if now not wanted
Implement queries as stored strategies.
Have a WHERE Clause in all SELECT queries.
Use information kinds accurately
Instead of NULLS use string values consisting of N/A
Question 60. What Is A Deadlock And What Is A Live Lock? How Will You Go About Resolving Deadlocks?

Answer :

Deadlock happens while two person strategies/transactions have locks on 2 separate objects and each process is trying to gather a lock at the object that has been obtained by means of the alternative technique. In this kind of state of affairs every method is awaiting the opposite technique to release the lock to accumulate a lock at the object itself. When a request for specific lock is denied time and again because a sequence of overlapping shared locks are interfering with each other and to adapt from each different they preserve on converting the status, it is called stay lock.
One can solve deadlocks by the usage of TRY CATCH blocks. If the code interior a TRY statement fails, the CATCH robotically catches the control of the drift letting the transaction rollback and resume execution.
Question 61. What Is Blocking And How Would You Troubleshoot It?

Answer :

Blocking happens whilst a system has obtained lock on a hard and fast of rows, and any other system is trying to acquire a lock at the identical set of rows. In this type of case, the other procedure has to attend till the first system finishes its activity and releases the lock on the above stated rows.

Use sp_lock method to see kind of locks received by using diverse sessions at the server to discover the reason of blocking.
Problem is hinted with the aid of the WAIT repute is a lot of rows which are again as an output of sp_lock saved process execution.
Use sp_who and sp_who2 to return extra columns to get greater facts across the blocking off.
Use DBCC INPUTBUFFER (spid).This will show the closing 128 characters of the final T-SQL assertion finished from connection referred through spid. This way you could identify the stored system or software module that triggered blocking.
To solve blocking off, you may disconnect the relationship inflicting the blocking off the usage of KILL command. If this doesn't remedy the problem permanently, then rewrite the stored process/module inflicting the block greater efficaciously.
Question 62. Explain The Different Types Of Backups Available In Sql Server?

Answer :

Types of backups to be had in SQL Server:

Complete: This creates a whole stand on my own picture of the database. This backup is self based and may be restored to either the identical or a new database on same or other server.
Differential: This backs up most effective the changed contents because the last backup. They do now not provide a great deal flexibility.
Transaction log: This backs up all transaction logs for the reason that previous transaction log backup or the whole transaction log backup if there has not been one in beyond.
Files and Filegroups backup: This option is appropriate when time constraints are high and one cannot find the money for to carry out a complete database backup. It also desires transaction logs backup to take region to make it worth selecting this option. After restoring report backup, practice transaction logs to roll the file contents forward to make it steady with the database..
Question sixty three. What Is Database Isolation In Sql Server?

Answer :

Database isolation comes into play whilst we want to isolate the database and shield it from other matters at the community. This safety is done using locks. The type of lock and the extent of isolation degree needed is referred as isolation stage in SQL Server.

Types of isolation levels:

READ COMMITTED: Shared locks are held at the same time as any facts is being examine.

READ UNCOMMITTED: Specifies isolation degree 0 locking. There are consequently no shared locks or exceptional locks. Lease restrictive of all the isolation stages.

REPEATABLE READ: Locks are carried out on all facts being used by a question. However, new phantom rows may be inserted into the facts set by some other person and are protected in later reads in the present day transaction.

SERIALIZABLE: Issues a variety lock on records set, stopping different users to update or insert statistics into dataset until the transaction is entire.