Sql Server All Question Ans

1) what is ssrs in sql server 2005?
Reporting Services (SSRS) provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports.

2 )what are the basic differences between clustered & non-clustered indexes?
A clustered index is a speical type of index that orders the records in the way they are stored physically on the disk. A table can have only one clustered index. The leaf nodes of clustered index contain data pages.

A non-clustered index is a specail type of index in which logical order of records don't match with physical order of records. A table can have multiple non-clustered indexes. The leaf nodes contain index rows.

3) What is a linked server in SQL Server?

It enables SQL server to address diverse data sources like OLE DB similarly. It allows Remote server access and has the ability to issue distributed queries, updates, commands and transactions.
A linked server allows remote access. Using this, we can issue distributed queries, update, commands, and transactions across different data sources.

A linked server has an OLE DB provider and data source.

4) What is SQL service broker?

A service broker allows you to exchange messages between applications using SQL server as the transport mechanism. Message is a piece of information that needs to be shared. A service broker can also reject unexpected messages in disorganized format. It also ensures the messages come only once in order. It provides enhanced security as messages are handled internally by the database.

SQL service broker provides asynchronous queuing functionality to SQL server. Once message is sent to the SQL server, the client can continue with some other task instead of waiting for any notification from the server.

5) What is SQL Server English Query?

English query allows accessing the relational databases through English Query applications. Such applications permit the users to ask the database to fetch data based on simple English instead of using SQL statements.

English query allows the developer to question the database using English rather than SQL queries. The English query tool has enhanced features like support for oracle, a graphical user interface to query the database etc.

6) Explain the phases a transaction has to undergo.

The several phases a transaction has to go through are listed here. Database is in a consistent state when the transaction is about to start.

1. The transaction starts processing explicitly with the BEGIN TRANSACTION statement.
2. Record is written to the log when the transaction generates the first log record for data modification.
3. Modification starts one table at time. Here database is in inconsistent state.
4. When all of the modifications have completed successfully and the database is once again consistent, the application commits the transaction.
5. If some error it undoes (or rolls back) all of the data modifications. This process returns the database to the point of consistency it was at before the transaction started.

* Active state: This phase is divided into two states:
Initial phase: This phase is achieved when the transaction starts.
Partially Committed phase: This is achieved when the transactions final statement has been executed. Even though the final statement is finished execution, the transaction may abort due to some failure.
* Failed state: This state is reached when the normal execution fails.
Aborted state: A transaction is aborted when the system feels it needs to be failed. This state should not have any effect on the system and thus all changes done until it were aborted; are rolled back.
* Committed: After the transaction is successfully executed, it enters the committed state. In this state all changes are committed. These committed changes cannot be undone or aborting.

7) What is Log Shipping?

Log shipping defines the process for automatically taking backup of the database and transaction files on a SQL Server and then restoring them on a standby/backup server. This keeps the two SQL Server instances in sync with each other. In case production server fails, users simply need to be pointed to the standby/backup server. Log shipping primarily consists of 3 operations:

Backup transaction logs of the Production server.
Copy these logs on the standby/backup server.
Restore the log on standby/backup server.

Log shipping enables high availability of database. It the process of shipping the transaction log to another server. It copies the replica of the database. Both the databases are in synch. In case of failure of primary server or database, the secondary server can be used. In this process, another server called as monitor that tracks the history and status of backup and restore operations.


8) What are the Authentication Modes in SQL Server?

a. Windows Authentication Mode (Windows Authentication): uses user’s Windows account

b. Mixed Mode (Windows Authentication and SQL Server Authentication): uses either windows or SQL server

Authentication modes in SQL Server:

Windows: Allows user to authenticate based on the MS Windows account credentials.

Mixed Mode: Allows users to connect either through Windows authentication or an SQL Server authentication mode. Administrator might maintain user accounts in SQL Server.


9) Describe in brief system database.

Master Database

Master database is system database. It contains information about server’s configuration. It is a very important database and important to backup Master database. Without Master database, server can't be started.
MSDB Database

It stores information related to database backups, DTS packages, Replication, SQL Agent information, SQL Server jobs.

TEMPDB Database

It stores temporary objects like temporary tables and temporary stored procedure.
Model Database

It is a template database used in the creation of new database.

The system database contains information/metadata for all database present on an SQL Server instance. The system database stores information regarding logins, configuration settings, connected servers etc. It also holds various extended stored procedures to access external processes and applications.

Major system databases :

*
Master: Core system database to mange Sql Server instance.
*
Resource: Responsible for physically storing all system objects.
*
TempDB: This is a temporary database used to store temporary, tables, cursors, indexes, variables etc.
*
Model: This acts as a template database for all user created databases.
*
MSDB: Database to manage SQL Server agent configurations.
*
Distribution: Database primarily used for SQL Server replication.
*
ReportServer: Main database for reporting services to store metadata and other object definitions.
*
ReportServerTempDB: Acts as a temporary storage for reporting services.

10) What are the events recorded in a transaction log?

The start and end of each transaction
Every data modification
Every extent allocation or deallocation
The creation or dropping of a table or index

Events recorded in a transaction log:

*
Broker event category includes events produced by Service Broker.
*
Cursors event category includes cursor operations events.
*
CLR event category includes events fired by .Net CLR objects.
*
Database event category includes events of data.log files shrinking or growing on their own.
*
Errors and Warning event category includes SQL Server warnings and errors.
*
Full text event category include events occurred when text searches are started, interrupted, or stopped.
*
Locks event category includes events caused when a lock is acquired, released, or cancelled.
*
Object event category includes events of database objects being created, updated or deleted.
*
OLEDB event category includes events caused by OLEDB calls.
*
Performance event category includes events caused by DML operators.
*
Progress report event category includes Online index operation events.
*
Scans event category includes events notifying table/index scanning.
*
Security audit event category includes audit server activities.
*
Server event category includes server events.
*
Sessions event category includes connecting and disconnecting events of clients to SQL Server.
*
Stored procedures event category includes events of execution of Stored procedures.
*
Transactions event category includes events related to transactions.
*
TSQL event category includes events generated while executing TSQL statements.
*
User configurable event category includes user defined events.

11) What is RAID (Redundant Array of Inexpensive disks)? Explain its level.

RAID is a mechanism of storing the same data in different locations. Since the same data is stored, it is termed as redundant. The data is stored on multiple disks which improves performance. The drive’s storage is divided into units ranging from a sector (512 bytes) up to several megabytes. This is termed as disk stripping.

There are NINE types of RAID plus an additional non-redundant array (RAID-0). However, RAID levels 0, 1, and 5 are the most commonly found.

* RAID 0: This level does involve stripping but no redundancy of data. Offers the best performance at the cost of NO fault tolerance.
* RAID 1: This level is termed as data mirroring consisting of at least two drives that duplicate the storage of data. No stripping involved. Often used for multi user system for best performance and fault tolerance.
* RAID 2: It involves stripping with some disks storing error checking and correcting (ECC) information.
* RAID 5: Consists of 3 or more disks in a way that protects data against loss of any one disk.
* RAID 6: Has stripped disks with dual parity.
* RAID 10: uses both striping and mirroring.
* RAID 53: Merges the features of RAID level 0 and RAID level 3
------
RAID controller is used when one drive fails and the other is still running well. The controller will automatically rebuild the data from the other devices and restores the same to the crashed system. Hence the RAID controller technology, depending on the importance of the data, is used to restore the data automatically from the other systems.


12) What are the lock types?

SQL server supports following locks

Shared lock
Update lock
Exclusive lock
Shared lock

Shared Lock allows simultaneous access of record by multiple Select statements.
Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading.
If update process is going on then read command will have to wait until updating process finishes.
Update locks

This lock is used with the resources to be updated.
Exclusive locks

This kind of lock is used with data modification operations like update, insert or delete.
--------

Main lock types:

*
Shared: Applied to read only operations where the data is not modified. E.g.: Select statements.
*
Update: Applied to resources which can be updated. It resolves dead locks in case of multiple sessions are reading, locking or updating resources later.
*
Exclusive: Used for operations involving data modification. E.g.: Insert, Update, and Delete. This ensures that multiple updates are not made to the same data at the same time.
*
Intent: Establishes a lock hierarchy. E.g.: Intent shared Intent exclusive and Shared with intentexclusive.
*
Schema: Used when schema dependent operations are being executed. E.g.: Schema modification and Schema stability.
*
Bulk update: Used while bulk copying of data and Tablock is specified.

13) Explain the use of NOLOCK query optimizer hint.

NOLOCK table hint used using WITH clause, is similar to read uncommitted. This option allows dirty reads are allowed. Using this option, shared locks cannot be issues to other transactions. This prevents existing transactions to not read incorrect data.
------------

NOLOCK is used to improve concurrency in a system. Using NOLOCK hint, no locks are acquired when data is being read. It is used in select statement. This results in dirty read - another process could be updating the data at the exact time data is being read. This may result in users seeing the records twice.
------------

NOLOCK would be deprecated in the future releases. It has been replaced by READUNCOMMITTED.
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations.

They are ignored by the SQL query optimizer in the FROM clause that apply to the target table of an UPDATE or DELETE statement.

They specify that dirty reads are allowed. Transactions can modify data while it is being read by a transaction. Shared locks are not issued to prevent it.

Exclusive locks set by other transactions do not block the current transaction from reading the locked data.

Allowing dirty reads provide higher concurrency but it may generate errors for a transaction and present users with data that was never committed or may have the records displayed twice.

14) What is blocking?

When one connection from an application holds a lock and a second connection requires a conflicting lock type

15) What is database replicaion?

The process of copying/moving data between databases on the same or different servers.

Snapshot replication,

Transactional replication,

Merge replication

16) Define COLLATION.

Collation is the order that SQL Server uses for sorting or comparing textual data. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary

17) List out the difference between CUBE operator and ROLLUP operator.

Difference between CUBE operator and ROLLUP operator

CUBE operator is used in the GROUP BY clause of a SELECT statement to return a result set of multidimensional (multiple columns) nature.

Example:

A table product has the following records:-

Apparel Brand Quantity
Shirt Gucci 124
Jeans Lee 223
Shirt Gucci 101
Jeans Lee 210

CUBE can be used to return a result set that contains the Quantity subtotal for all possible combinations of Apparel and Brand:

SELECT Apparel, Brand, SUM(Quantity) AS QtySum
FROM product
GROUP BY Apparel, Brand WITH CUBE

The query above will return:

Apparel Brand Quantity
Shirt Gucci 101.00
Shirt Lee 210.00
Shirt (null) 311.00
Jeans Gucci 124.00
Jeans Lee 223.00
Jeans (null) 347.00
(null) (null) 658.00
(null) Gucci 225.00
(null) Lee 433.00

ROLLUP:- Calculates multiple levels of subtotals of a group of columns.

Example:

SELECT Apparel,Brand,sum(Quantity) FROM Product GROUP BY ROLLUP (Apparel,Brand);

The query above will return a sum of all quantities of the different brands.

---------

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

ROLLUP generates a result set that represents aggregates for a hierarchy of values in the selected columns.

---------

CUBE ROLLUP
It’s an additional switch to GROUP BY clause. It can be applied to all aggregation functions to return cross tabular result sets. It’s an extension to GROUP BY clause. It’s used to extract statistical and summarized information from result sets. It creates groupings and then applies aggregation functions on them.
Produces all possible combinations of subtotals specified in GROUP BY clause and a Grand Total. Produces only some possible subtotal combinations.

18) Define @@Error and raiseerror.

Raiseerror is used to produce an error which is user defined or used to invoke an existing error present in sys.messages. They are most commonly used in procedures when any condition fails to meet.

Example:

SELECT COUNT(*) INTO :rows FROM student
WHERE studentid = : studentid;

IF :rows <> 0 THEN
RAISE ERROR 1 MESSAGE 'Student id exists in the "Student" table.';
ENDIF;

@@error is used to hold the number of an error. When a T-SQL statement is executed, @@error value is set to 0 by the SQL server. If an error occurs, the number of that error is assigned as a value.

Example: the value of @@error can be checked for “0” value to be safe.
------

@@Error

* It is system variable that returns error code of the SQL statement.
* If no error, it returns zero.
* @@Error is reset after each SQL statement.

Raiseerror

Raiseerror command reports error to client application.

---------
SQL Server provides @@Error variable that depicts the status of the last completed statement in a given set of statements. If the statement was executed successfully the variable holds 0 value else it holds the number of the error message that occurred. Raiseerror is used to send messages to applications using the same format as a system error or warning generated by SQL Server engine. It can also return a user defined message. RAISEERROR is often used to help in troubleshooting, check values of data, returns variable value based messages, cause an execution to jump to a CATCH from TRY.

19) When do we use the UPDATE_STATISTICS command?

UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account

20) Define Local temporary table and global temporary table.

Local temporary table is created by prefixing name with pound sign like (#table_name). Global temporary table is created by prefixing name with Double pound sign like (##table_name).
Local temporary table is dropped when the stored procedure completes. Global temporary tables are dropped when session that created the table ends