Sql Server Interview Questions and Answers (1758) - Page 22

1. Device Throughput Bytes/sec counter of SQLServer:Backup Device Performance object

2. Backup/Restore Throughput/sec counter of SQLServer:Databases Performance object

A network model is a database model by which we can represent objects and their relationships. These models define a set of network layers and it provides greater flexibility and easy access to data. This model provides the logical relationship among many parent databases.

The hierarchical data model means a base data can have its corresponding branches i.e. the data is organized into tree structure. In this all attributes of a specific record are listed under an entity type. In this model you can form relationship among many tables with certain concepts. Here each record is represented as a row and an attribute as a column.

The object oriented database is a database management system which supports the creation and modeling of data as objects. It should support two criteria i.e. it should be a DBMS and it should be an object oriented system. These databases are used to store complex data, to store information related to multimedia, Engineering databases, spatial databases etc.

The XML database is use to organize data irrespective of whether it is organized or not. These are used in applications such as informational portals, document exchanges, and product catalogs. This data can exported and serialized into below mentioned format. There are two different XML database class exists.

XML-enabled: This is a traditional database such as a relational database which accepts XML as input and rendering XML as output. This shows that the database does the conversion to itself. Basically this is used to store data-centric documents which include highly structured information.

Native XML: These types of databases depend on XML and uses XML documents as a storage unit. Basically this is designed to store semi-structured information, such as marketing brochures or health data and that semi-structured contained data are referred to as document-centric. It manages the documents by grouping them into a logical collection and can manage multiple collections simultaneously.

• The data redundancy and consistency is not easy in File System but in Database it is possible.
• It is difficult to access data in File Processing System but in Database it can be done easily.
• The File System is unable to provide data isolation but Database provides.
• In File System you will not get data integrity but the Database can have data integrity.
• Concurrent access is not possible in File System but possible in Database.
• There is no security in the File System but you will get this facility in Database.

Rollback: The Rollback transaction is a transaction which rolls back the transaction to the beginning of the transaction. The transaction can be rolled back completely by specifying the transaction name in the Rollback statement or to cancel any changes to a database during current transaction. It is permissible to use before Commit transaction.

Rollforward: Recovering a database by applying different transactions that recorded in the database log files. It is nothing but re-doing the changes made by a transaction i.e. after the committed transaction and to over write the changed value again to ensure consistency.

Concurrency allows us the simultaneous access of same data by different users. The process of managing simultaneous execution of transactions in a shared database, to ensure the serializability of transactions, is known as concurrency control. It avoids the adverse effect of one transaction to another transaction. These are of two types.

Pessimistic concurrency control: It assumes when a conflicts happen. This technique detects conflicts as soon as they occur and resolve them using locking. The system lock prevents users from modifying data in a way so that it will not affect other user. After a user performs an action that causes a lock to be applied, so that other users cannot perform any action on that.

Optimistic concurrency control: This is called optimistic because the conflicts between transactions are rare and it doesn't require locking. Here the transactions are executed without any restrictions. It is mainly used when there is low contention for data and it checks for conflicts before the commit.

A Database Transaction is a logical unit of database operations and a unit of work in database management system. These are highly important to use transactions when working with databases. In this the SQL statement is treated as a transaction and will be automatically committed immediately after it is successfully executed. For this successful transaction that is from begin to end transaction, it follows the term ACID (Atomicity, Consistency, Isolation, Durability).

To implement Database Transaction we have to follow the following steps i.e.

beginTransaction();
setTransactionSuccessful();
endTransaction();

Database Administrator: The basic role of a Database Administrator is to store and manage the information in the database. They are responsible for reviewing the contents in the database. They are performing all the activities related to maintaining the database and responsible for designing, implementing the database. Also their responsible is to backed up data regularly and prevent from unauthorized access. They are also known as Database Coordinator or Database Programmer.

The different functionalities of a database administrator are maintaining database system software, developing physical database structures and data dictionary.

Data Administrator: The Data Administrator is responsible for defining data elements, data names and their relationship with the database analyst. The basic responsible is how to install and configure the RDBMS applications and also they have to know the requirements of the software application in terms of functions and assure the data integrity. They are also known as Data Analyst. Following are some basic steps for a Data Administrator,

• Specification of organization data.
• The design and maintenance of data management application.
• Validating the data and files.
• Security of files or databases.

The SUBSTR function returns a specific portion of a string
Example: SUBSTR('DotNetFunda',6)
The result will be: DotNet

The INSTR function provides character position in a pattern of string.
Example: INSTR('Dot-Net-Funda','-',2)
The result will be: 8 (2nd occurrence of '-')

‘=’ is for assigning one value to the other variable.
‘==’ is for the comparison between string with number, number with number etc.
‘===’ is for the comparison between only number with number and string with string.

Example:
// for '=' operator

if(a=b+c)

{

    alert('true')

}

It will be true if it not contains any zero, false, and any empty string.

//for '==' operator

if(a==b+c)

{

    alert('true')

}

Suppose the value of x is "5" and the value of y, z is 8, -3 then the if condition will return value true. Here we are comparing string with the number.

//for '===' operator

if(x===y+z)

{

    alert('true')

}

The above example is not possible for this case because x value and the result of y+z value are either number and string. It will work on the below example.

if(5 === y + z) 

{

    alert('true');

}

View is a Virtual table which contains columns from one or more tables. It does not contain any data directly. A group of Query incorporated into one or more tables which is stored in the Database as a form of an Object.

When run a SELECT statement, The statement is processed by the various factors

1.The PARSER scans/reads the SELECT statement and splits it into some logical units such.
i.e: keywords/reserved word, operators, expressions and identifiers.

2. SEQUENCE TREE or QUERY TREE is built the logical steps needed to transform the source data into the format as needed by the end result.

3. The QUERY OPRIMIZER analyzes the query in various ways and selects the series of steps that returns the end results fastest way. The query tree is updated to record this exact series of steps. The final, optimized version of the query tree is called the execution plan.

4. The RELATIONAL ENGINE starts executing the execution plan which was prepared by the Query Optimizer.

5.The RELATIONAL ENGINE requests the STORAGE ENGINE pass up data from the rowsets requested.

6. The RELATIONAL ENGINE processes the data returned from the storage engine to the client.

Cheers

1. The Model database is a Stencil or Template for creating an user-defined database.

2. If any Objects (Tables, Stored procedures or Users) that need to exist in all our user defined database should be placed in Model database first.

3. When placing these kind of objects in Model database, will increase the minimum size of our user defined database

We can create any type of triggers on a single table . But the thing is if we have Instead Of Trigger created on a table untill and unless it is disabled or deleted we cannot work on After Triggers.
At a single strech we cannot work on both the types.

The procedure to create a DML trigger is :

CREATE TRIGGER (TRIGGER_NAME)
ON (TABLE_NAME)
AFTER (INSERT OR UPDATE OR DELETE)
AS
PRINT 'MESSAGE'
GO

1. The term Backup Dump and Backup Device are the same and does the same activity.
2. Then, why we are using the system stored procedure sp_addumpdevice to define a Backup device.
3. The reason is SQL Server 6.5 and earlier, the concept of Backup Device actually used the syntax as given below

DUMP DATABASE ....

DUMP LOG ....

4. So we still using the procedure(sp_addumpdevice) to define Backup Device for backward compatibility

- Normally, when we take a database backup, we can specify that, How long the Database backup file(.Bak) should be valid.
- After that, The backup file(.bak) no longer available...
- So, How to identity the Expiration date of a backup file(.bak) ?

RESTORE HEADERONLY FROM DISK='<Backup File Path.Bak>'

- It will return a result set with 52 columns, we have a columns like ExpirationDate.
- If the ExpirationDate column is null then, you have not specified any Expiration option while you took the backup.
- If the ExpirationDate column is some date then, The backup file no longer valid after the date specified.

The use of Pivot element is , it is used to reorganize and summarize the selected columns and rows of data in a table to produce the desired reports.

For example ,

Let us create a table :

CREATE TABLE ItemSales(

  SalesPerson VARCHAR(50),

  Item VARCHAR(50),

  ItemAmount INT) 

Insert values into it :

INSERT INTO ItemSales 

         VALUES('Person1', 'Pickles', $100.00) 

INSERT INTO ItemSales 

         VALUES('Person1', 'Pickles', $100.00) 

  INSERT INTO ItemSales 

         VALUES('Person2' ,'Oranges' ,$50.00 )

  INSERT INTO ItemSales 

         VALUES('Person2', 'Pickles', $25.00) 

  INSERT INTO ItemSales 

         VALUES('Person2', 'Oranges', $300.00) 

  INSERT INTO ItemSales 

         VALUES('Person1', 'Oranges', $500.00)

Now create and assigning a pivot element

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles

FROM 

(SELECT SalesPerson, Item, ItemAmount

FROM ItemSales ) ps

PIVOT

(

SUM (ItemAmount)

FOR Item IN

( [Oranges], [Pickles])

) AS Pvt  

After this the complete data is summarized and the report generated is in this format:

-----------------------------------------------
SalesPerson || Oranges || Pickles
-----------------------------------------------
Person1 || 500 || 200
Person2 || 350 || 25