Interview Questions.

Q1. Illustrate Physical Database Architecture In Brief?

The bodily database architecture describes how the database and documents are prepared in a SQL server.

Pages and extents: those describe how the information is stored.

Physical Database Files and File businesses:- describes the running system files used to store data and logs.

Space Allocation and Reuse:- Describes the algorithms used for area allocation.

Table and Index Architecture:- Describes the way pages for tables can be indexed.

Q2. Explain What Are The Database Objects?

Database items including tables, primary key, and foreign key describe the shape of the content of a database. These gadgets additionally represent the houses of a server. Server facet gadgets are items that reside on the server but no longer inside the database. Typical examples of server facet objects consist of, logins, person described error messages etc. The database objects are contained in the database undertaking while the server items are contained within the server venture. These items are defined in a .Sq. Document. Most of these objects are defined in a separate record depending at the situation. E.G it's far essential to specify columns within the same document wherein the desk is defined.

Q3. Explain The Truncate Command?

Truncate command is used to cast off all rows of the column.

The removed statistics are not recorded in the transaction log.

It is the fast way to put off all the data from the table.

The data as soon as removed can’t be rolled back.

It can’t set off cause.

It resets the identification of the column.

Q4. Do You Know What The Restrictions Applicable While Creating Views Are?

Views can be created referencing tables and perspectives handiest inside the present day database.

A view call have to not be the same as any desk owned by that user.

You can construct views on different perspectives and on approaches that reference views.

Rules or DEFAULT definitions cannot be associated with perspectives.

Only INSTEAD OF triggers may be related to perspectives.

The query that defines the view can't encompass the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.

You can not outline full-text index definitions for views.

You can not create temporary perspectives.

You can't create perspectives on transient tables.

Q5. What Is Model Database?

It is a template database used within the creation of latest database.

Q6. What Are Page Splits?

When there is not enough room on a page for a brand new row, a Server splits the web page, allocates a new web page, and actions a few rows to the brand new page.

Q7. What Is Raid And What Are Different Types Of Raid Configurations?

RAID stands for Redundant Array of Independent Disks. RAID defines data storage schemes to divide and reflect records among various disks in order that information reliability and I/O overall performance may be elevated.

The basic configurations of RAID are:

LEVEL 0: Striped set without parity/Non-Redundant Array.

LEVEL 1: Mirrored set without parity.

LEVEL 2: Redundancy thru hamming code.

LEVEL 3: Striped set with committed parity/Bit interleaved parity.

LEVEL 4: Block stage parity.

LEVEL five: Striped set with dispensed parity.

LEVEL 6: Striped set with dual allotted Parity.

Q8. Explain What Are The Restrictions That Views Have To Follow?

Since a view is a virtual desk – Columns of the view cannot be renamed. To exchange something in the view, the view ought to be dropped and create again.

The pick statement at the view can not comprise ORDER BY or INTO TEMP.

When a desk or view is dropped, any perspectives inside the equal database are also dropped.

It isn't feasible to create an index on a view.

It isn't feasible to apply DELETE to replace a view that is defined as a be part of.

Q9. Explain The Microsoft Sql Server Delete Command?

Delete command eliminates facts one at a time and logs into the transaction log.

It may be used without or with where clause.

The facts can be rolled returned.

It activates trigger.

It doesn’t reset the identity of the column.

Q10. What Do You Know About System Database?

The gadget database consists of information/metadata for all database present on an SQL Server instance. The gadget database stores facts regarding logins, configuration settings, related servers and so on. It also holds diverse prolonged saved approaches to access outside strategies and packages.

Major machine databases:

Master: Core gadget database to mange Sql Server example.

Resource: Responsible for bodily storing all system items.

TempDB: This is a brief database used to keep transient, tables, cursors, indexes, variables and many others.

Model: This acts as a template database for all consumer created databases.

MSDB: Database to manipulate SQL Server agent configurations.

Distribution: Database on the whole used for SQL Server replication.

Report Server: Main database for reporting services to save metadata and different item definitions.

ReportServerTempDB: Acts as a transient storage for reporting services.

Q11. What Is Database Architecture?

Database structure describes the design of the database. It explains how the facts is stored. The information of the server is saved in databases. This database is further break up into one or more discs. The database may be taken into consideration to have  layers. Physical layer, that is a transparent layer for the database directors to paintings on. Other users usually paintings on the consumer view layer. Tables, perspectives, procedures paperwork this view.

Q12. What Is The Difference Between Cube Operator And Roll Up Operator?

CUBE generates a end result set that represents aggregates for all combinations of values inside the selected columns.

ROLLUP generates a result set that represents aggregates for a hierarchy of values inside the decided on columns.

Q13. Explain Logical Database Components?

The logical components are commonly used to hook up with the database. Any item that a person can use to access or connect with the database is a logical factor. Triggers, tables, processes, views, keys and so on are regular examples.

Q14. What Is Msdb Database?

It stores records associated with database backups, DTS applications, Replication, SQL Agent statistics, SQL Server jobs.

Q15. What Is Temp Db Database?

It stores transient items like temporary tables and brief stored system.

Q16. What Is Master Database?

Master database is machine database. It incorporates statistics approximately server’s configuration. It is a totally important database and essential to backup Master database. Without Master database, server can't be started out.

Q17. What Are Pages And Extents?

A web page is a unit of statistics garage in SQL. The length of a page is 8Kb. A web page has a header and a frame. Different forms of pages are: Date, text, index , page loose area and many others. The information rows are placed on the page serially after the header.

Extents are units in which space is allocated to tables and indexes. An extent is 8 non-stop pages. SQL Server has two kinds of extents: uniform and mixed extent. For efficient allocation, the SQL server does now not allocate whole extents to tables with small amounts of statistics.