SQL Server Interview Questions and Answers

Posted on Sep 12, 2024 in Technology

Ques: 1 How to eliminate duplicate rows in a table?

Ans:

Use a primary key if the column is like ID or number. Alternatively, you can apply a unique constraint on the column.

Ans:

Deletion of duplicate records:

delete from emp a where a.rowid > (select min(b.rowid) from emp b where a.rowid=b.rowid)

or

delete from table t1 where t1.rowid

Ques: 2 What is the use of the ‘in’ command?

Ans:

Suppose you want to choose a specific department, then you can use the ‘in’ command.

Example:

select ename,sal from emp where deptno in(10,20);

Ques: 3 Class members

Ques: 4 What is the SQL Wildcard?

Ans:

SQL Wildcard is basically used for searching operations, similar to the LIKE function. SQL wildcards can substitute for one or more characters when searching for data in a database.

The following wildcards can be used:

  • > % – A substitute for zero or more characters
  • > _ – A substitute for exactly one character
  • > [charlist] – Any single character in charlist
  • > [^charlist] or [!charlist] – Any single character not in charlist

Ans:

% –

Ques: 5 Describe the TO_DATE function.

Ans:

The TO_DATE function is used when we want to show the timestamp from a character string that has been interpreted using a character template.

Ques: 6 In what sequence are SQL statements processed?

Ans:

The sequence of SQL statements are processed in the following sequence (DB2):

  • > FROM clause
  • > WHERE clause
  • > GROUP BY clause
  • > HAVING clause
  • > SELECT clause
  • > ORDER BY clause
  • > FETCH FIRST clause

Ques: 7 What databases do you know?

Ans:

Yes, we know lots of databases. We are mentioning them here:

  • > Informix
  • > DB2
  • > SQL
  • > Oracle

Ques: 8 What are the main attributes of a database management system?

Ans:

A database management system has many attributes, but here we are mentioning that DBMS is composed of five elements:

  • > Computer hardware
  • > Software
  • > Data
  • > People (users)
  • > Operations procedures

Ques: 9 What are the main components of Database management systems software?

Ans:

The database management system software includes many components:

  • > For storage management
  • > Concurrency control
  • > Transaction processing
  • > Database manipulation interface
  • > Database definition interface
  • > Database control interface

Ques: 10 What is query optimization?

Ans:

In the database, many queries are generated and executed. Query optimization is the part of the query process in which the database system compares different query strategies and chooses the one with the least expected cost.

Ques: 11 What Oracle lock modes do you know?

Ans:

Oracle has two lock modes:

  • > Shared
  • > Exclusive

Shared locks are set on database resources so that many transactions can access the resource.

Exclusive locks are set on resources that ensure one transaction has exclusive access to the database resource.

Ques: 12 What is Oracle locking?

Ans:

Oracle is basically a database. It has many facilities, and one of them is locking, which uses locking mechanisms to protect data from being destroyed by concurrent transactions.

Ques: 13 What does COMMIT do?

Ans:

Its main work is saving all changes made by DML statements.

Ques: 14 Discuss the disadvantages of cursors.

Ans:

There are many disadvantages of cursors:

  • > Each time we fetch a row from the cursor, it causes a network roundtrip, whereas a normal SELECT query makes only one roundtrip, however large the result set is.
  • > They are very costly because they require more resources and temporary storage. Further, there are restrictions on the SELECT statements that can be used with some types of cursors.
  • > Another situation in which developers tend to use cursors: we need to call a stored procedure when a column in a particular row meets a certain condition. We don’t have to use cursors for this. This can be achieved using a WHILE loop, as long as there is a unique key to identify each row.

Ques: 15 What is RAID and what are different types of RAID configurations?

Ans:

RAID stands for Redundant Array of Inexpensive Disks. It is mainly used for providing fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance and fault tolerance.

Ans:

RAID stands for Redundant Array of Inexpensive Disks. It is mainly used for providing fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance and fault tolerance.

Ques: 16 What do you mean by Serialized?

Ans:

It is part of the isolation level. It is basically used for ensuring that data read by the current transaction cannot be changed by another transaction until the current transaction finishes. No new data can be inserted that would affect the current transaction.

Ques: 17 What do you mean by Repeatable Read?

Ans:

It is basically defined as an isolation property and its works on the Isolation level. Data read by a current transaction cannot be changed by another transaction until the current transaction finishes. Any type of new data can be inserted during a transaction.

Ques: 18 What do you mean by Read Committed?

Ans:

It is basically defined as an isolation property. It is used for transactions, where a transaction cannot read data that is being modified by another transaction that has not committed. This is the default isolation level in Microsoft SQL Server.

Ques: 19 What do you mean by Read Uncommitted?

Ans:

First of all, I would like to tell everyone it’s a type of Isolation Level. It is mainly used for transactions, where a transaction can read any data, even if it is being modified by another transaction. This is the least safe isolation level but allows the highest concurrency.

Ques: 20 What is a Cartesian product?

Ans:

A Cartesian product is basically the result from a faulty query. It produces a row in the results for every combination in the join tables.

Ques: 21 What is the basic difference between a join and a union?

Ans:

The basic differences are:

  • > Join is basically used for columns, but in the case of union, it isn’t.
  • > Union is basically used for rows, but in the case of join, it isn’t.
  • > A join selects columns from two or more tables. A union selects rows.

Ques: 22 What is the use of union in Indexing?

Ans:

UNION is basically used to combine the results of two or more queries into a single result set, which consists of all the rows belonging to all queries in the union.

Ques: 23 Why do we use Stored Procedures?

Ans:

Stored procedures have more utilizing content. They are used in every field of SQL Server. There are four major reasons why we use stored procedures:

  • > They can dramatically increase security.
  • > They can assist you in centralizing your code.
  • > They are executed on the server’s machine.
  • > They are precompiled.

Ques: 24 What do you mean by Indexed Views?

Ans:

Indexed views are persistent and can significantly improve application performance by eliminating the work that the query processor must perform to resolve the views. They are basically views, but in some cases, unlike standard views, which SQL Server resolves the data-access path dynamically at execution time, the new indexed views feature allows us to store views in the database just like we can store tables.

Ques: 25 What do you mean by XML Support in SQL Server 2000?

Ans:

The database engine basically uses Extensible Markup Language (XML) documents for returning data. In SQL Server 2000, XML is basically used for:

  • > Inserting values
  • > Updating values
  • > Deleting values in the database

Ques: 26 What do you mean by Cascading Referential Integrity Constraints?

Ans:

Right now, only two new clauses are there: ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.

The ON DELETE and ON UPDATE clauses have two options:

  • NO ACTION: NO ACTION specifies that the deletion/updation fail with an error.
  • CASCADE: CASCADE specifies that all the rows with foreign keys pointing to the deleted/updated row are also deleted/updated.

> ON DELETE: It basically controls what actions are taken if we attempt to delete a row to which existing foreign keys point.

> ON UPDATE: It basically defines the actions that are taken if we attempt to update a candidate key value to which existing foreign keys point.

Ques: 27 What do you mean by INSTEAD OF and AFTER Triggers?

Ans:

There are many differences between INSTEAD OF and AFTER Triggers in SQL Server 2000:

  • > INSTEAD OF triggers work instead of the INSERT, UPDATE, or DELETE triggering action, but AFTER triggers work after the triggering actions.

Ques: 28 How many new data types are in SQL Server 2000?

Ans:

There are many types of new data types:

  • > sql_variant data type: This type allows the storage of data values of different data types.
  • > table data type: This type lets an application store temporary results as a table that we can manipulate by using a select statement or even action queries, just as we can manipulate any standard user table.
  • > bigint data type: This type is an 8-byte integer type.

Ques: 29 What do you mean by Distributed partitioned views?

Ans:

Distributed partitioned views provide the facility to partition tables horizontally across multiple servers. That’s why we can scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers.

Ques: 30 What is database replication? What are the different types of replication you can set up in SQL Server?

Ans:

Replication is basically used for copying and moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

  • > Snapshot replication
  • > Transactional replication (with immediate updating subscribers, with queued updating subscribers)
  • > Merge replication

Ques: 31 What is a Join in SQL Server?

Ans:

Join in SQL Server puts data from two or more tables into a single result set.

Ques: 32 When do you use SQL Profiler?

Ans:

SQL Profiler is basically a utility that allows us to track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs, etc.

Ques: 33 What do you mean by COLLATION?

Ans:

Collation is basically used for ordering. It is the sort order. There are three types of sort order:

  • > Dictionary case sensitive
  • > Dictionary case insensitive
  • > Binary

Ques: 34 What are defaults? Is there a column to which a default can’t be bound?

Ans:

A default is basically a type of value that is used by the columns if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them.

Ques: 35 What is DTS?

Ans:

DTS stands for Data Transformation Services. It is mainly used for a set of graphical tools and programmable objects that we can extract, transform, and consolidate data from disparate sources into single or multiple destinations.

Ques: 36 What is DTC?

Ans:

DTC stands for The Microsoft Distributed Transaction Coordinator (MS DTC). It is basically a type of transaction manager that allows client applications to include several different sources of data in one transaction. MS DTC coordinates committing the distributed transaction across all the servers enlisted in the transaction.

Ques: 37 How do you transfer data from a text file to a database (other than DTS)?

Ans:

We can transfer data from a text file to a database using the BCP utility, where BCP stands for Bulk Copy Program.

Ques: 38 What is the use of the SCOPE_IDENTITY() function?

Ans:

The SCOPE_IDENTITY() function returns the most recently created identity value for the tables in the current execution scope.

Ques: 39 What is the purpose of UPDATE STATISTICS?

Ans:

UPDATE STATISTICS has many purposes, but the main purpose is to update information about the distribution of key values for one or more statistics groups or collections in the specified table or indexed view.

Ques: 40 What is Referential Integrity?

Ans:

Referential integrity ensures consistency that must be maintained between primary and foreign keys.

Ques: 41 What do you mean by Live Lock?

Ans:

A livelock occurs when a single lock request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. When four denials are complete, then SQL Server detects the situation and refuses further shared locks.

Ques: 42 What do you mean by Deadlock?

Ans:

Deadlock is a situation when two processes, each requesting a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait for the other to release the lock unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user’s process.

Ques: 43 What is RAID?

Ans:

RAID stands for Redundant Array of Inexpensive Disks. It is mainly used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance and fault tolerance.

Ques: 44 What’s the maximum size of a row?

Ans:

8060 bytes.

Ques: 45 What is lock escalation?

Ans:

Lock escalation is the process of converting a lot of low-level locks (like row locks) into higher-level locks (like table locks). Every lock is a memory structure, and too many locks would mean more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks.

Ques: 46 What is denormalization and when would you go for it?

Ans:

Denormalization is the reverse process of normalization. It’s mainly used for the controlled introduction of redundancy into the database design. It helps improve query performance as the number of joins could be reduced.

Ques: 47 What are the different methods of loading Dimension tables?

Ans:

There are two different ways to load data in dimension tables:

  • > Direct (Fast): In this way, all the constraints and keys can be disabled before the data is loaded. Once the data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty, it is not included in the index and all future processes are skipped on this data.
  • > Conventional (Slow): All the constraints and keys are validated against the data before it is loaded. This way, data integrity is maintained.

Ques: 48 What is an ER Diagram?

Ans:

ER Diagram stands for Entity Relationship Diagrams. It is a major data modeling tool. It’s a type of diagram used in data modeling for relational databases. These diagrams show the structure of each table and the links between tables. It will also help organize the data in our project into entities and define the relationships between the entities. This process has proved to enable the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner. An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database.

Ques: 49 What is a surrogate key?

Ans:

Surrogate keys are always integer or numeric. It is useful because the natural primary key can change and this makes updates more difficult. A surrogate key is basically a substitution key for the primary key. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table.

Ques: 50 What do you mean by Snow schema?

Ans:

Snow Schema is also known as a snowflake schema. It’s mainly interlinked or may have a one-to-many relationship with other tables. This schema is normalized and results in complex joins and very complex queries as well as slower results.

Ques: 51 What do you mean by Star schema?

Ans:

Star Schema is basically used for getting faster results because it is a single fact table with N number of dimensions, which will be linked directly with a fact table. This schema is denormalized and results in simple joins and less complex queries as well as faster results.

Ques: 52 What do you mean by SCD3?

Ans:

SCD3 is basically used for only the modification and creation of new data from the original data. One record exists in the database – new information is attached with old information in the same row.

Ques: 53 What do you mean by SCD2?

Ans:

SCD2 is mainly used for adding a new record into the customer dimension table. Two records exist in the database – current data and previous history data.

Ques: 54 What is SCD1?

Ans:

SCD1 is mainly used for creating a new record and it can also replace the original record. Only one record exists in the database – current data.

Ques: 55 What are slowly changing dimensions (SCD)?

Ans:

SCD stands for Slowly Changing Dimensions. It’s mainly used for dimensions that change very slowly because it applies to cases where the attribute for a record varies over time.

There are three types of SCD:

  • > SCD1
  • > SCD2
  • > SCD3

Ques: 56 Explain Active/Active and Active/Passive cluster configurations.

Ans:

There are many differences:

  • > Active/Active: It is basically used for the default nodes. Here, the first node will be default and the second node will be a named instance. Both nodes will be active. Move group from cluster administration is possible for both sides. System performance will go down if both resources are in one node.
  • > Active/Passive: It is also basically used for nodes, but in this case, only one active node with the default instance. No system performance degradation will occur for this case even if we switchover to the other node. Both have the same configuration.

Ques: 57 What do you mean by Leaf Nodes?

Ans:

A Leaf Node is basically a type of container that contains the index terms and horizontal pointers for other leaf nodes, which can be many.

Ques: 58 What do you mean by Branch Node?

Ans:

A Branch Node basically contains pointers for leaf nodes or other branch nodes, which can be two or more.

Ques: 59 What is the meaning of Root Node?

Ans:

A Root Node is basically used for containing node pointers to branch nodes, which can be only one.

Ques: 60 What is the meaning of UNIONALL?

Ans:

UNIONALL is basically a command. It is much like a UNION command, but it selects all values.

Ques: 61 What is UNION?

Ans:

UNION is basically a type of command that is used for the selection procedure. It selects the related information from two tables, similar to the JOIN command. When using the UNION command, all selected columns need to be of the same data type. With UNION, only distinct values are selected.

Ques: 62 What is DENSE_RANK() OVER?

Ans:

It returns the rank of rows within the partition of the result set, without any gaps in the ranking.

Ques: 63 What is RANK() OVER?

Ans:

It returns the rank of each row within the partition of a result set.

Ques: 64 What is the meaning of ROW_NUMBER() OVER?

Ans:

It returns the sequential number of the row within the partition of the result set, starting at 1 for the first row in each partition.

Ques: 65 What are Ranking functions?

Ans:

Ranking functions return the ranking of each row in the partition. These ranking functions are non-deterministic. There are many different types of ranking functions:

  • > ROW_NUMBER() OVER([])
  • > RANK() OVER([])
  • > DENSE_RANK() OVER([])

Ques: 66 What is ROW_NUMBER()?

Ans:

ROW_NUMBER() is basically used for returning a column expression that contains the row number within the result set. This is only a number used in the context of the result set; if the result changes, the ROW_NUMBER() will change.

Ques: 67 What is an Aggregate Function?

Ans:

An Aggregate Function is basically a type of function that is used for calculations on a set of values and it returns a single value. It also has a HAVING clause, which is used along with GROUP BY for filtering queries using aggregate values. It ignores NULL values except for the COUNT function. It has many functions:

  • > AVG
  • > MIN
  • > CHECKSUM_AGG
  • > SUM
  • > COUNT
  • > STDEV
  • > COUNT_BIG
  • > STDEVP
  • > GROUPING
  • > VAR
  • > MAX
  • > VARP

Ques: 68 What do you mean by Table Sample?

Ans:

TABLESAMPLE is basically used for sampling rows in the table, which is mainly used for extracting in the FROM clause. The rows are retrieved randomly and not in a simple order. It can be based on a percentage or number of rows. It is used only when sampling of rows is necessary for the application instead of a full result set.

Ques: 69 What is SQLCMD?

Ans:

SQLCMD can work in two modes:

  • > BATCH
  • > Interactive Mode

It is the enhanced version of ISQL (which is deprecated) and OSQL (which is not included in SQL Server 2005 RTM). It is a better replacement for OSQL and ISQL. It provides way more functionality than the other two options.

Ques: 70 What is a Dirty Read?

Ans:

A Dirty Read is basically a type of error that is generated when two operations access data at the same time. For example, two operations (read and write) occur together, giving incorrect or unedited data. Suppose A has changed a row but has not committed the changes. B reads the uncommitted data, but his view of the data may be wrong, so that is a Dirty Read.

Ques: 71 What is Filestream?

Ans:

Filestream is basically used for storing large amounts of data or objects in the file system, which is integrated with the database. It is used in SQL Server-based applications. It enables server-based applications to store unstructured data such as documents, images, audios, videos, etc. in the file system. FILESTREAM basically integrates the SQL Server Database Engine with the New Technology File System (NTFS); it basically stores the data in the varbinary(max) data type. Using this data type, the unstructured data is stored in the NTFS file system and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact-SQL statements, users can insert, update, delete, and select the data stored in FILESTREAM enabled tables.

Ques: 72 What are Pivot and Unpivot?

Ans:

A pivot table is basically used for sorting the table and also for counting and totaling the data that is stored in one table or spreadsheet. It also creates a second table to display the summarized data. It can turn the values of a specified column into column names, effectively rotating a table. Unpivot is the exact opposite of pivot.

Ques: 73 What are Catalog Views?

Ans:

Catalog Views are basically used for returning information used by the SQL Server Database Engine. It is a very important general interface catalog to the metadata and it provides the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.

Ques: 74 What do you mean by the DBCC Command?

Ans:

DBCC stands for Database Console Commands for SQL Server. It works with the T-SQL Programming Language. DBCC commands have many uses for performing new tasks:

  • > It is used for tasks that gather and display various types of information.
  • > Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
  • > Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
  • > Maintenance tasks on a database, index, or filegroup.

Ques: 75 What is the meaning of Dictionary Compression?

Ans:

Dictionary Compression is basically used for searching for duplicate values throughout the page and stores them to CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page.

Ques: 76 What is Page Compression?

Ans:

It is basically used for sharing common data between rows of the given page. It has many techniques for the compression of data:

  • > Row Compression
  • > Prefix Compression

Ques: 77 What is the meaning of Row Compression?

Ans:

Row compression changes the format of physical storage of data. It minimizes the metadata (column information, length, offsets, etc.) associated with each record. Numeric data types and fixed-length strings are stored in a variable-length storage format, just like Varchar.

Ques: 78 What is Data Compression?

Ans:

In the new version of SQL Server 2008, there are different types of Data Compressions:

  • > Row Compression
  • > Page Compression
  • > Dictionary Compression

Ques: 79 What is the XML Datatype?

Ans:

XML is basically a SQL Server datatype that is used for storing XML documents and fragments in SQL Server. XML is basically an instance that is missing a single top-level element. We can create columns and variables of the XML type and store XML instances in them. The XML data type and associated methods help integrate XML into the relational framework of Server.

Ques: 80 What is RaiseError?

Ans:

RaiseError is basically used for generating error messages and initiating error processing in the session. RAISERROR can basically define a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned and displayed as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

Ques: 81 What is NOLOCK?

Ans:

NOLOCK is basically a technique that is useful for busy systems. It helps improve concurrency. When used in a SELECT statement with the NOLOCK hint, data is read without acquiring shared locks. The result is a Dirty Read, which means that another process could be updating the data at the exact time we are reading it. It is not guaranteed that most of the data will be read. The performance advantage of using NOLOCK hints is that our reading of data will not block updates from taking place, and updates will not block our reading of data. SELECT statements take Shared Read locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result is delayed blocking.

Ques: 82 What is the EXCEPT clause?

Ans:

The EXCEPT clause is basically used in Oracle. It is similar to the MINUS operation, which is also used in Oracle. Both queries return all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields, the same data types, and the same result sets.

Ques: 83 What are Isolation Levels?

Ans:

Isolation levels define the degree to which transactions should be isolated from resource or data modifications made by other transactions. Isolation levels specify which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

Ques: 84 What is LINQ?

Ans:

LINQ is an abbreviation for Language Integrated Query. It is used in .NET languages. It adds query capabilities and has some basic features:

  • > It has tools that create classes (usually called entities) mapped to database tables.
  • > It is compatible with LINQ’s standard query operations.

Ques: 85 What are synonyms?

Ans:

Synonyms are basically used for giving an alternate name to database objects. The main use of synonyms is that we can alias object names, for example, using the Employee table as Emp. We can also shorten names. It is mainly used for dealing with two or four name parts; for example, shortening server.database.owner.object to object.

Ques: 86 What is CLR?

Ans:

In the new version of SQL Server 2008, SQL Server mainly uses CLR languages for creating user-defined functions. Common Language Runtime is basically a runtime environment that manages the execution of program code and provides services such as memory and exception management, debugging and profiling, and security. This CLR language support extends not only to user-defined functions but also to stored procedures and triggers. We can develop such CLR add-ons to SQL Server using Visual Studio 2008.

Ques: 87 What are the advantages of using CTE?

Ans:
CTE advantages are there :
> It can be defined, it is used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.
> Its Using CTE for improve the readability and makes maintenance of complex queries easy.
> It can be signifies as a functions,stored procedures, triggers or even views.
> The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.

Ques: 88 What is Filtered Index?

Ans:
Filtered Index is bassically use for indexing, Its mainly used to improves query performance, It can be reduced mentinence costs and it cn be also reduce index storage costs compared with full-table indexes. Its usually use for Index a portion of rows in a table that means it applies filter on INDEX . When we see an Index created with some where clause then that is actually a FILTERED INDEX.
Ques: 89 What is MERGE Statement?

Ans:
MERGE is a bassically use for the data modification and also check for the data then helps on the updation.It is a new feature of the SQl server, That provides an efficient way to perform multiple DML operations. some time before in Sql server when merge statement is not available in the server, We had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now a days using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.
Ques: 90 What is CTE?

Ans:
CTE is stands for Common Table Expression. It is like to a derived table in that it is not stored as an object and lasts only for the duration of the query. A Common Table Expression CTE) is a type of  expression that can be seem like as a temporary result set which is explain within the execution of a single SQL statement. 
Ques: 91 What does TOP Operator Do?

Ans:
The TOP operator is bassically use the new addition of SQL Server 2008 its have a facility provides to accepts the literal values and can be used with INSERT,UPDATE. And DELETES statements.,Its mainly used to be returned by a query from specified no of rows .
Ques: 92 What are Sparse Columns?

Ans:
A sparse column is bassically a tool of the data base which is use for the decrease physical storage amount which is used in a database.Sparse columns also be use for the redusing work Its reduce the space requirements for null values at the cost of more overhead to retrieve no null values. They are the normal columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve no null values.
Ques: 93 What is Replication and Database Mirroring?

Ans:
Database mirroring and Replication can be work together and they are provide availability for the publication database. Database mirroring bassically work in two copies of a single database that are rarlly use on different computers. If we have to given any time for a only one copy of the database is currently available to clients which is called as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.
Ques: 94 What is Policy Management?

Ans:
Policy Managementis mainly use for configuring the policies and its also use for the managing the policies its policy is SQL Server across the enterprise.and Policy-Based Management is bassically configured in the SQL Server Management Studio (SSMS). Its only Navigator to the Object Explorer and Its is also expand the Management node and the Policy Management node, We can see the Policies, and Conditions, and Facets nodes.
Ques: 95 What is Service Broker?

Ans:
Service Broker is the mainly use for developing to integrated SQL Server which is fully into distributed applications, It is bassically  message-queuing technology in SQL Server, It is the feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.
Ques: 96 Define whats the mean by Resoure Database?

Ans:
The Resoure Database is the bassically use for containing the system objects its stand for the read only database,The Resource database does not contain user data or user metadata. Syatem objects are included with SQL Server.SQL Server system objects,such as sys.objects,are physically persisted in the Resource database, but they logically appear in the sys schema of every database. 
Ques: 97 What is the mean of The model?

Ans:
The model is the very important for a template database, Its a mainly used in the creation of any new user database created in the instance.
Ques: 98 What do u mean by The tempdb?

Ans:
The tempdb is the bassically use for holding the temporary objects such as global and local temporary tables and stored procedures.
Ques: 99 Define The msdb database? 

Ans:
The msdb database is the bassically use for stores information regarding database backups,SQL Agent information,DTS packages,SQL Server jobs, and some replication information such as for log shipping.
Ques: 100 What do you mean by The master database?

Ans:
The Master Database is bassically use for the database information whihc all databases are located on the Sqlserver  instances and It is the Clue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, We must administrator this database with care.
Ques: 101 What is an execution plan? When would you use it? How would you view the execution plan?

Ans:
An Execution plan is basically presentation of the map in graphically or textually shows of the dat retrival methods whihc is choosen by the SQL Server abd it can be query optimiser for the  stored procedure or ad‐hoc query and we can also use this as a tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan". If this option is turned on it will display query execution plan in separate window when query is ran again.
Ques: 102 How to implement one-to-one,one-to-many and many-to-many relationships while designing tables?
Ans:
Implementations of relationship is there : 

> One-to-One relationship : Its relationship bassiccaly use for only single table implementation and rarely for two tables with primary and foreign key relationships. 
> One-to-Many relationships :Its relationship is bassically yse for the implemented by splitting the data into two tables with primary key and foreign key relationships.

> Many-to-Many relationships : Its relationship is bassically use for implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
Ques: 103 What is BCP? When does it used?

Ans:
BulkCopy is bassically a using for copy, It is a tool used to copy huge amount of data from tables and views. BULK INSERT command helps to import a data file into a database table or view in a user‐specified format.It does not copy the structures same as source to destination. 
Ques: 104 What are the advantages of using Stored Procedures?

Ans:
There are many different advantages of using Stored Procedures which is following here : 
> Its help promote code reuse.
> It can encapsulate logic. We can change stored procedure code without affecting clients.
> It provide better security to our data.
> It can be reduced network traffic and latency, boosting application performance.
> Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
Ques: 105 What is a Scheduled Jobs or What is a Scheduled Tasks?

Ans:
Scheduled tasks is the bassically use of the completing task which is schededule by any user who use the system administrator, let user automate processes that run on regular or predictable cycles. user can decide his task for the perticule schedule time such as cube prossessing, for running the  during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.
Ques: 106 What is NOT NULL Constraint?

Ans:
A NOT NULL constraint  is stands for basically as a name not null it means that the colums are not accpted the null values. Its is used to signifies a domain intigrity as the check constraints.
Ques: 107 What is CHECK Constraint?

Ans:
A CHECK constraint is bassically a using for limit of the values becouse It can be placed in a columns to tat values. Its used to singnifies a domain integrity.
Ques: 108 What is FOREIGN KEY?

Ans:
A FOREIGN KEY is bassically a type of primary key it is the normal key for he one table but it is can become a primary key for the other table, The foreign key constraints are using a referential integrity, The foreign key constraints are mainly signifies a referential integrity constraint prevents any actions that can be destroy links between tables with the corresponding data values. Its prevent actions that would leave rows with foreign key values when there are no primary keys with that value.
Ques: 109 What is UNIQUE KEY constraint?

Ans:
A UNIQUE constraint bassically mean is the unique attributes its means no values are duplicate its defines a uniqueness of the valuesina set of columns, The unique key constraints are used to signifies entity integrity as the primary key constraints.
Ques: 110 What is PRIMARY KEY?

Ans:
A PRIMARY KEY is the bassically a type of unique identifier which is unique property for any row with in a database table. Primary key is the most important for the unique identityfication in every table and every row becouse each table is identitify for only with primary key constraints.
Ques: 111 What is the STUFF function and how does it differ from the REPLACE function?
Ans:
REPLACE function is bassically use for replace existing characters of all occurrences. Its mainly syntax REPLACE, It is using string_expression,search_string,replacement_string, where every incidence of search_string found in the string_expression will be replaced with replacement_string.STUFF function is bassically using for the overwrite existing characters. Its Syntax is STUFF , Its Using for string_expression, start,length, replacement_characters, string_expression is the string that will have characters substituted, start is the starting position,length is the number of characters in the string that are substituted,and replacement_characters are the new characters interjected into the string.
Ques: 112 What do u mean by Global temporary table?

Ans:
Global temporary tables are distinct within SQL sessions. The table definition is the bassically a type of the database for access when database is opened next time. A global temporary table remains in the database permanently, But the rows exist only within a given connection. The data is dissapper in the global temporary table,When connection is closed.
Ques: 113 What is the mean of Local temporary table?

Ans:
Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.A local temporary table is bassically a like a using for duration of connections. Its exists only for the duration of a connection. If defined inside a compound statement, for the duration of the compound statement.
Ques: 114 What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?

Ans:
When SET QUOTED_IDENTIFIER is OFF, identifiers must follow all T-SQL rules for identifiers and It cannot be quoted , But in case of When SET QUOTED_IDENTIFIER is ON, Identifiers can be defied the boundaries by double quotation marks, And literals must be define the boundaries by single quotation marks. 
Ques: 115 What is Log Shipping?

Ans:
Log shipping is bassically a process of the datatbase and its maily use for transaction of the lof files on the production SQL Server, And its the process of automating the backup of database. And then restoring them onto a standby server. Enterprise Editions is only supports log shipping. In the case of log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same database and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.
Ques: 116 Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

Ans:
Yes we can stored procedure call itself or recursive storeed procedure. Because Stored procedures is bassically are  nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. we can nest stored procedures and managed code references up to 32 levels. T-SQL supports recursion, we can write the stored procedures becouse of call, Recursion have a techniques or methods of problem solving, Where in the solution is appying o the problems it can be use of subset of problems only. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. 
Ques: 117 What is SQL Server Agent?

Ans:
SQL Server Agent is the very important for the Database administrator. Its a very good role or important role in database administrator (DBA) for work on day to day tasks. It is use overlooked as one of the main tools for SQL Server management. Its mainly use of the implementation of tasks for the DBA, with its full function scheduling engine, which allows us to schedule our own jobs and scripts.
Ques: 118 What are the authentication modes in SQL Server? How can it be changed?

Ans:
Mainly Two modes are the Authentication Modes in SQL Server they are following as : 
Windows mode and Mixed Mode : SQL And Windows.
Process of change the Authentication Mode in SQL Server :  To change authentication mode in SQL Server click Start > Programs >  Microsoft SQL Server >  click SQL Enterprise Manager .
        When the open the Window of SWL Enterprise Manager it means it start to run from the Microsoft SQL Server program group. Now Select the server then going to Tools menu > select SQL Server Configuration Properties, and choose the Security page.
Ques: 119 What is SQL Profiler?

Ans:
SQL Profiler is bassically a graphical tool, Which is maily use for alow to system administrators to monitor events in an instance of Microsoft SQL Server. We can capture and save data about each event to a file or SQL Server table to analyze later. For example, we can monitor a production environment to see which stored procedures are hampering performances by executing too slowly. Its mainly Use SQL Profiler to monitor only the events in which we are interested. We can filter the events based on the information we want, when It traces are becoming too large, That's why only a subset of the event data is collected.In server many overhead events are added from the Monitor and the monitoring process and becouse the trace file or trace table to grow very large, Specially when the monitoring process takes place over a long period of time.
Ques: 120 What are the type of SUB-QUERy?

Ans:
There are many types of SUB-QUERY : 
> Single-row sub-query:  where the sub-query returns only one row.
> Multiple-row sub-query : where the sub-query returns multiple rows, 
and
> Multiple column sub-query : where the  Sub-query returns multiple columns.


Ques: 121 What are the properties of SUB-QUERY?

Ans:
There are many Properties of Sub-Query are there :
> Its must be enclosed in the parenthesis.
> Its must be put in the right hand of the comparison operator.
> Its cannot contain an ORDER-BY clause.
> A query can contain more than one sub-query.
Ques: 122 What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Ans:
There are a many differences b/w HAVING CLAUSE and a WHERE CLAUSE : 
               Having Clause is mainly used for the Group By function Its only with the GROUP BY function in a query, But In case of WHERE Clause is using only for rows, Its applied to each row before they are part of the GROUP BY function in a query. Both of Clouses are mainly specify a search condition for a group or an aggregate. But differences are there in the case of  HAVING CLAUSE, it can be used only with the SELECT statement. And It is typically used in a GROUP BY clause. Even GROUP BY is not used, HAVING behaves something like a WHERE clause. 
Ques: 123 When is the use of UPDATE_STATISTICS command?

Ans:
UPDATE_STATISTICS command is mainly used for the solving a problem when a large processing of data has occurred. If a large amount of deletions wants any modification or when the Bulk Copy into the tables has occurred, that time it has to update the indexes to take these changes into account. Its updates the indexes on these tables accordingly.
Ques: 124 What do you mean by DELETE command?

Ans:
Delete Command bassically defines as : 
> It Can be used with or without a WHERE clause.
> It Activates Triggers.
> It can be rolled back.
> It is DML Command.
> It does not reset identity of the table.
> DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
> If we want to retain the identity counter, Which is use DELETE instead. If we want to remove table definition and its data, use the DROP TABLE statement.
Ques: 125 What do you mean by TRUNCATE?

Ans:
Truncate is bassically defined as : 
> It cannot be rolled back.
> It is DDL Command.
> It Resets identity of the table.
> It is faster and uses fewer system and transaction log resources than DELETE.
> It removes the data by deallocating the data pages used to store the table�s data, and only the page deallocations are recorded in the transaction log.
> It removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
> We can't use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
Ques: 126 What is OLTP?

Ans:
OLTP stands for Online Transaction Processing Systems. It is mainly use for relational database design for use the discipline of data modeling and generally follow the Code rules of data normalization, It is in order to ensure absolute data integrity. It is  Using these rules complex information is broken down into its most simple structures or a table, where all of the individual atomic level elements relate to each other and satisfy the  normalization rules.
Ques: 127 What are different types of Collation Sensitivity?

Ans:
There are many diff types of Collation Sensitivity, Which are following here : 
> Case Sensitivity : A and a, B and b, etc .
> Accent Sensitivity : a and �, o and �, etc.
> Kana Sensitivity : When the Japanese kana characters Hiragana and Katakana are treated differently, It is called Kana sensitive.
> Width Sensitivity : it is Bassically a single-byte character or half-width and the same character represented as a double-byte character or full-width are treated differently than it is width sensitive.
Ques: 128 What are the different index configurations a table can have?

Ans:
A Table can have Diff type of the index configurations, Which are following as :
> No Indexes
> A clustered index
> A clustered index and many nonclustered indexes
> A nonclustered index
> Many nonclustered indexes

Ques: 129 What do you mean by A non clustered index?

Ans:
A Non Clustered Index is a special type of index, Which Indexes are in the logical order of the index, Its  does not match the physical stored order of the rows on disk. The leaf node does not consist of the data pages Which is from non clustered index. Instead, The leaf nodes contain index rows.
Ques: 130 What is the clustered index?

Ans:
A Clustered index is bassically a special type of index,its is mainly use for reorders the way of records in the table which is in the physically stored. Even table can have only one clustered index. the data pages contained on the leaf nodes of the clustered index .
Ques: 131 What is DataWarehousing?

Ans:
DataWarehousing is the bassically mean that It is Subject-oriented, Its a meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together.
> It is Time-variant, Its a meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time.
> It is Non-volatile, Its a meaning that data in the database is never over-written or deleted, Onaly once committed, The data ahould be static, And read-only, But retained for future reporting.
> It is Integrated, Its meaning that the database contains data from most or all of an organization's operational applications, And that this data is made consistent.
Ques: 132 What is Identity?

Ans:
Identity is a bassically called as AutoNumber. It is mainly a column that automatically generates numeric values. When A start and increment value can be set, but most DBA leave these at 1. A identity or GUID column also generates numbers; thease type of values cannot be controlled. Identity/GUID columns do not need to be indexed.

Ques: 133 What kind of User-Defined Functions can be created?

Ans:
We can a creat User-Defined Functions in a many ways they are defined as :
> Scalar User-Defined Function :  A Scalar user-defined function is bassically a return value like one of the scalar data types. It is not a type of supported data types like Text,ntext,image and timestamp. These are the type of user-defined functions and most developers are used to in other programming languages. We pass in 0 to many parameters and We get a return value.
> Inline Table-Value User-Defined Function : An Inline Table-Value user-defined function mainly returns the value like a table data type and It is an exceptional alternative to a view as the user-defined function, It can pass parameters into a T-SQL select command and Its is poivide the essence with a parameterized, non-updateable view of the underlying tables.
> Multi-statement Table-Value User-Defined Function : A Multi-Statement Table-Value user-defined function is bassically a returns a table and It is a type of view which we can say like an exceptional alternative view as the function can support multiple T‐SQL statements to build the final result where the view is limited to a single SELECT statement. And Its also the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non‐updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user‐defined function, It can be used in the FROM clause of a T‐SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Ques: 134 What is User Defined Functions?

Ans:
User Defined Functions is bassically signifies UDF, Its allow to define only its own T-SQL functions which is accept 0 or more parameters and return a single scalar data value or a table data type.
Ques: 135 Defeine : Self Join?

Ans:
Self Join is bassically use for the editing in the table itself , This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can joined tables which are the same. A self join is rather unique in that it involves a relationship with only one table. For the example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join also use can be Outer Join or Inner Join.
Ques: 136 What do you mean by Outer Join? and define also how many types of Outer Join?
Ans:
Outer Join is the bassically use for a rows. That join includes rows even if related rows in the joined table are absent is an Outer Join. We can create three different outer join to specify the unmatched rows to be included : 
> Left Outer Join : In Left Outer Join all rows in the first& named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.

> Right Outer Join: In Right Outer Join all rows in the second;named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
> Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.


Ques: 137 give us a definition of Inner Join?

Ans:
Inner Join is a bassically use for presention the tables or formate, A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.




Ques: 138 Define : What is the Cross Join?

Ans:
A cross join is he type of join , That does not have a WHERE clause which is produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is bassically equal to the number of rows in the first table multiplied by the number of rows in the second table. For the example we can see when company wants to combine each product with a pricing table to analyze each product at each price.
Ques: 139 Give the list how many type of Joine?

Ans:
There are diff types of join :
> Cross Join
> Inner Join
> Outer Join
     * Left Outer Join
     * Right Outer Join
     * Full Outer Join
> Self Join

Ques: 140 What is the Sub-Query?And what is the properties of Sub-Query? 

Ans:
Sub-queries are bassically defined as
sub-selects, Its allow a SELECT statement to be
executed arbitrarily within the body of another
SQL statement. A sub-query is mainly executed by
enclosing in a set of parenthesis. Sub-queries
are generally used to return a single row as an
atomic value, They may be used to comparision,
Its may be use for compare the values against
multiple rows with the IN-keyword. A Subquery is
a SELECT statement that is nested within another
T-SQL statement. A Subquery SELECT statement if
executed independently of the T-SQL statement, In
which it is nested, will return a resultset.
Meaning a subquery SELECT statement can stand
alone and is not depended on the statement in
which it is nested. A subquery SELECT statement
can return any number of values, and can be found
in, The column list of a SELECT statement,FROM,
GROUP BY,HAVING,and/or ORDER BY clauses of a
T-SQL statement. A Subquery can also be used as a
parameter to a function call. Basically a
subquery can be used anywhere an expression can
be used.
Ques: 141 What is Difference between Function and Stored Procedure?

Ans:
Many didd are there :
> UDF can be used WHERE/HAVING/SELECT section
anywhere in the SQL statements, But in case of
Stored procedures cannot be. 
> UDF's that return tables can be treated as
another rowset. This can be used in JOINs with
other tables. Inline UDF's can be thought of as
views that take parameters and can be used in
JOINs and other Rowset operations.
Ques: 142 What is Collation?

Ans:
Collation is bassically a collection to a set of
rules that determine how to data is sorted and
compared. Character data is sorted with using
rules that defined the sequence of correct
character, with specifying case sensitivity
options,and accent marks, and kana character
types and character width.
Ques: 143 What is Cursor?
Ans:
Cursor is bassically a database object,which is manily used by applications to
manipulate data in a set on a Row by Row basis, Inastead of use the typical
SQL commands that operate on all the rows in the set at one time. In order to
workwith a cursor we need to perform some steps in theFollowing order : 
> Declare cursor
> Open cursor
> Fetch row from the cursor
> Process fetched row
> Close cursor
> Deallocate cursor


Ques: 144 What is a Linked Server?

Ans:
Linked Servers is bassically a concept in SQL
Server, Its mainly use we can add the other SQL
Server to a Group and query both the SQL Server
dbs using T-SQL Statements. With a linked
server, We can also create very clean,easy to
follow, And SQL statements that allow remote data
to be retrieved, joined and combined with local
data.

Ques: 145 What is the mean of Nested Trigger?

Ans:
A Trigger have a many authorities it can also perform to the INSERT,UPDATE and DELETE oprations or logic within itself, And whenever the trigger is fired the
reason is that data modification, It can also the reason is another data
modification, may be firing another trigger. A Trigger that contains data
modification logic within itself is called a nested trigger.

Ques: 146 What is De-Normalization?

Ans:
De-Normalization is bassically the process, Which
is attempting to optimize the performance of a
database by adding redundant data. It is very
important sometimes because current DBMS's
implement the relational model poorly. A true relational DBMS would allow for a fully
normalized database at the logical level, Even
providing physical storage of data that is tuned
for high performance. De-Normalization is a
technique to move from higher to lower normal
forms of database modeling in order to speed up
database access.
Ques: 147 What are the properties of the Relational tables?

Ans:
Relational tables have six properties which is there :
> In this table Values are atomic.
> In this table Column values are of the same kind.
> In this table Each row is unique.
> In this table The sequence of columns is insignificant.
> In this table The sequence of rows is insignificant.
> In this table Each column must have a unique name.
Ques: 148 What are the use of Null?

Ans:
NULLs are bassically for use in database for descraibe that no value or attributes are exist, 
But are not really values themselves. Instead they are just an indictor that a particular field has no value. Some decussions are clarifying that it is better than no value i mean blank value or a default value. we are individually can see little value in a blank field, but default values can be useful. The interesting point about NULLs is that no Primary Key can contain a NULL and thus it's useful when comparing Candidate Keys to see if one could potentially contain a NULL, thus disqualifying that key.
Ques: 149 What is the Foriegn Key?

Ans:
 
Play History
Catalogue No. -	Date -	Times Played

Supose we have a table whihc name is Play history and its have a three colums Catalogue No and Date anf Times Play.These are three Colums in a 1 row.
In this situation the Catalogue No. number is a Primary Key in the CDs table but only part of the Composite Key in the Play History table as the date is required to form the whole key. An alternative could have been to create a new unique single-attribute Primary Key for the table. As we still need to know the catalogue number in order to work out how many times a DJ has played Britney, Christina or Pink. The catalogue number would become the Foreign Key in the Play History table,that is  Foreign Key is a attribute or column in a table that refers to a Primary Key in another table.
Ques: 150 What do you mean by Candidate key?

Ans:
Condidate key is bassically use for when we added a new attribute in the table, We could also use the combination of other attribute with this new attribute to identify the row, as together they form a Composite Key. Thus we have two potential or Candidate Keys.
Ques: 151 Define the Primary key?

Ans:
The Primary Key is bassically a key that is frequently used to identify a specific row other keys also may exist and all the other values are dependent on that value to be meaningfully identified. A primary key is usually one attribute or column in a relation or table, But can be a combination of attributes.
Ques: 152 Why we use Semicolon after SQL Statements?

Ans:
Some database systems are very curious about the its must be semicolon at the end of each SQL statement. Semicolon is the very good way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. We are always try to using MS Access and SQL Server 2000 and we don't have to put a semicolon after each SQL statement, but some database programs force us to use it.
Ques: 153 Do a view contain data?

Ans:
Ofcourse the View don't contain any data. The basic property of a view is to extract a set of data columns to display from a large number. And this is useful in future by jst stating the view name instead writing the select statement every time.
Ques: 154 What is an Oracle view?

Ans:
A view is a bassically logical table which makes a change complex query to easy term .We can even create a complex view by joining two tables.
Ques: 155 What is Oracle table?

Ans:
The Collection of informations stored in the strutrued format which is in rows and columns formate that is called a table.
Ques: 156 Can a tablespace hold objects from different schemes?

Ans:
Yes we can a template hold objects from different schemes.
Ques: 157 Can objects of the same schema reside in different table spaces?

Ans:
Yes we can objects of the same schema reside in different table space. Schema objects can stored in different tablespace and a tablespace can contained one or more schema objects data.
Ques: 158 What are Schema Objects?

Ans:
Schema is bassically a Associated with each database user . It is a collection of schema objects. Examples of schema objects include tables, views, sequences, synonyms, indexes, clusters, database links, snapshots, procedures, functions, and packages. Schema objects are logical data storage structures. Schema objects don't have a one-to-one correspondence to physical files on disk that store their  information. Even Oracle stores a schema object logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace's datafiles.
Ques: 159 What is schema?

Ans:
Schema bassically Pronounce skee-ma, It is the structure of a database system,It described in a formal language supported by the database management system (DBMS). In the RDBMS (Relational database System) , the schema defines the tables, the fields in each table, and the Schemas are generally stored in a data dictionary . Even a schema is defined in text database language , the term is often used to refer to a graphical depiction of the database structure. 
Ques: 160 Explain the relationship among database, tablespace and data file.

Ans:
Database : Database is the Collection of data is called database.

Table Space : The table space ismany use for storing the data in the database. When a database is created two table spaces are created.
i) System Table Space : This data file stores
all the tables related to the system and dba tables.
ii) User Table Space : This data file stores all the user related tables. We should have separate table spaces for storing the tables and indexes so that the access is fast.

Data Files : Every Oracle Data Base has one or  more physical data files. They store the data for the database. Every data-file is associated with only one database. Once the Data file is created the size cannot change. To increase the size of the database to store more data we have to add data file. 
Ques: 161 What is SYSTEM tablespace and when is it created?

Ans:
Every ORACLE database have a table space which name is SYSTEM . when the database is created taht a time its also automatically created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
Ques: 162 What is a tablespace?

Ans:
A Tablespace is bassically a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one data file which are physically located in the file system of the server. b/w datafile belongs to exactly one tablespace. Each table index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored. There are three types of tablespaces in Oracle:
> Permanent tablespaces
> Undo tablespaces
> temporary tablespaces 
A tablespace is created with the create tablespace sql command. 
Ques: 163 What are the components of logical database structure of Oracle database?

Ans:
The Logical Database Structure of an Oracle include : 
> The schema objects, 
> Data blocks, 
> Extents, 
> Segments and 
> Tablespaces.

> schema objects : means table, index, synonyms, sequences etc.

> Data blocks : are the smallest part of the oracle database defined by DB_BLOCK_SIZE parameter.

> Extent : A group of data blocks forms an extent.

> Segments : An extents groups tends to segments, 

> Table space : lastly a group of segment forms a tablespace.
Ques: 164 What are the components of physical database structure of Oracle database?
Ans:
Physical components of oracle database are: 
> Control files,
> Redo log files and 
> Datafiles.

> Control file : control file is basicaly a type of file .Its read in the mount state of database. control file is a small binary file which records the physical structure of database which includes
* Database name
* names and locations of datafiles and online
redo log files.
* timestamp of database creation
* check point information
* current log sequence number.

Redo log files : Redo log files usually use for the saving to the files. This files saves all the changes that are made to the database as they occur. This plays a great role in the database recovery.

Datafiles : Datafiles are the physicalfiles which stores data of all logical structure.
Ques: 165 What is SQL?

Ans:
SQL is a bassically standard language for accessing and manipulating databases. Its is defines as :
> SQL stands for Structured Query Language
> SQL lets you access and manipulate databases
> SQL is an ANSI (American National Standards Institute) standard.
SQL have a many properties, It can do :
> SQL can execute queries against a database.
> SQL can retrieve data from a database.
> SQL can insert records in a database.
> SQL can update records in a database.
> SQL can delete records from a database.
> SQL can create new databases.
> SQL can create new tables in a database.
> SQL can create stored procedures in a database.
> SQL can create views in a database.
> SQL can set permissions on tables, procedures, 
and views.

Ques: 166 What is Index?

Ans:
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application
. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
Ques: 167 What is View?

Ans:
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
Ques: 168 What is Trigger?

Ans:
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
Ques: 169 What is Stored Procedure

Ans:
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc
Ques: 170 What is normalization?

Ans:
Normalisation is a process whish is the tables in a database are optimised to remove the potential for redundancy. Two main problems may arise if this is not done:
> Repeated data makes a database bigger.
> Multiple instances of the same values make
maintaining the data more difficult and can 
create anomalies.

Ques: 171 What are different normalization forms?

Ans:
 normalisation have a se veral forms that a database structure can be subject to, each with rules that constrain the database further and each creating what is called a Normal Form. These are, in order:
> First Normal Form (1NF)
> Second Normal Form (2NF)
> Third Normal Form (3NF)
> Boyce Codd Normal Form (BCNF)
> Fourth Normal Form (4NF)
> Fifth Normal Form (5NF)
> Optimal Normal Form (ONF)
> Domain-Key Normal Form (DKNF)
Theses are defined as : 
> 1NF: Eliminate Repeating Groups : Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
> 2NF: Eliminate Redundant Data : If an attribute depends on only part of a multi-valued key, remove it to a separate table.
> 3NF: Eliminate Columns Not Dependent On Key : If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
> BCNF: Boyce-Codd Normal Form : If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
> 4NF: Isolate Independent Multiple Relationships : No table may contain two or more 1:n or n:m relationships that are not directly related.
> 5NF: Isolate Semantically Related Multiple : Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships.
> ONF: Optimal Normal Form : A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
> DKNF: Domain-Key Normal Form : A model free from all modification anomalies.
Ques: 172 What is RDBMS?

Ans:
RDBMS stands for Relational Database Management
System. RDBMS is the basis for SQL, and for all 
modern database systems like MS SQL Server, IBM 
DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects 
called tables. A table is a collections of 
related data entries and it consists of columns 
and rows.Relational Data Base Management Systems 
(RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
Ques: 173 Which one is faster DELETE/TRUNCATE?

Ans:
Truncante is more faster than delete because when we delete the records from the database, database has to perform 2 actions.
> Delete from the database
> Write the deleted records into "rollback"  
segments. But incase of "Truncate" the second activity is not required.It is faster than becouse truncate is a ddl command so it does not produce any rollback information and the storage space is released while the delete command is a dml command and it produces rollback information too and space is not deallocated using delete command.
Ques: 174 What are the difference between DDL, DML and DCL commands? 

Ans:
SQL can be divided into two parts: 
> The Data Manipulation Language (DML)
> The Data Definition Language (DDL).
The query and update commands form the DML part of SQL:
> SELECT - extracts data from a database
> UPDATE - updates data in a database
> DELETE - deletes data from a database
> INSERT INTO - inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are: 
> CREATE DATABASE - creates a new database
> ALTER DATABASE - modifies a database
> CREATE TABLE - creates a new table
> ALTER TABLE - modifies a table
> DROP TABLE - deletes a table
> CREATE INDEX - creates an index (search key)
> DROP INDEX - deletes an index
Data Control Language (DCL) statements. Some examples:
> GRANT - gives user's access privileges to database
> REVOKE - withdraw access privileges given with the GRANT command