SQL

What are the advantages of using Stored Procedures?

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.

Why we use Semicolon after SQL Statements?

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.

What\'s the maximum size of a row?

8060 bytes.

What is a Scheduled Jobs or What is a Scheduled Tasks?

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.

What is NOT NULL Constraint?

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.

What is CHECK Constraint?

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.

What is the STUFF function and how does it differ from the REPLACE function?

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.

What do you mean by Table Sample?

TABLESAMPLE is basscially use for the sampling of the rows in the table which is mainly use for the extracting  in the Form Clause. The rows are retrived in the radomlly and its not in a simple order. Its bassically can be based on a percentage of no of rows. its use only that time when sampling of rows is necessary for the application instead of a full result set.

What is PRIMARY KEY?

A PRIMARY KEY is the basically 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 identification in every table and every row because each table is identify for only with primary key constraints.

What is UNIQUE KEY constraint?

A UNIQUE constraint basically 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.

What is FOREIGN KEY?

A FOREIGN KEY is classically 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.

What is the mean of The model?

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.

What do u mean by The tempdb?

The tempdb is the basically use for holding the temporary objects such as global and local temporary tables and stored procedures.

Define The msdb database?

The msdb database is the basically use for stores information regarding database backups,SQL Agent information,DTS packages,SQL Server jobs, and some replication information such as for log shipping.

What is Replication and Database Mirroring?

Database mirroring and Replication can be work together and they are provide availability for the publication database. Database mirroring basically 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.

What does TOP Operator Do?

The TOP operator is basically 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 .

What are Sparse Columns?

A sparse column is basically 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 reducing 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.

What is CLR?

In the new version of Sql Server 2008, SQL Server mainly use as user-defined functions can be created using such CLR languages. Common Language Runtime is a basically  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.

What is the ROW_NUMBER()?

Row Function is basically use for the retuning a columns which is type of expressions. which is contain the rows no with in 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.

What is the EXCEPT clause?

EXCEPT clause is a basically use on the oracle, It is similar to MINUS Operation which is also use in Oracle. Both are queries are returns 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 should have the same no and also have a same result sets and same data types.

What is the Page Compression?

Its is basically use for the sharing of the two common data b/w rows of the given page. Its have a many techniques for the compression of the data :
> Row Compression
> Prefix Compression

What is the mean of Dictionary Compression?

Dictionary Compression is basically use for the searching . Searching basically the duplicator This Dictionary mainly work is the differentiate between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page. value throughout the page,and stores them to CI

What is Pivot and Unpivote?

A pivot table is basically use for the sorting the table, and also doing the work in table like a counting and totaling the data which is stored in one table or spread sheet and its also create the second table for the display the summarized data,It can be turns the value of a specified column into column names, effectively rotating a table. but in case of Unpivote Its a exactly opposite to the pivote.

What is an Aggregate Function?

Aggregation Function is basically a type of function, which is use for the calculation on the set of values and It is also return the single value , Its also have a HAVING CLAUSE, Which is used along with GROUP BY.for filtering query using aggregate values, Its ignore NULL Values excpt COUNT Function, Its have a many functions which is there :
> AVG 
> MIN
> CHECKSUM_AGG 
> SUM 
> COUNT 
> STDEV 
> COUNT_BIG 
> STDEVP 
> GROUPING 
> VAR 
> MAX 
> VARP

What is SQLCMD?

SqlCmd is basically a very important for that SQL Server It can be work with two modes :
> BATCH and
> Intractive Mode
            It is the Enhaceversion of isql which is deprecated Eventuallyand osql which is not include in SQL Server 2005 RTM, It is the Better replacement of OSQL and ISQL. It provides way more functionality that other two options.
  

What is the Dirty Read?

Dirty Read is basically a type of error its generate when two operations access in the same time for the example Two operations says,read and write occurs together giving the incurrect or  

What is the Filestream?

File stream is the basically use for the store the large amount of data or objects in the file system which is integrated with the database.It is the SQL Server based applications, Its con be enable for server based applications to store unstructured data such as documents, images, audios, videos etc. In the file system. FILESTREAM basically integrates the SQL Sever Database Engine with New Technology File System (NTFS); it basically stores the data in 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.

Explain Active/Active and Active/Passive cluster configurations?

Many Diff are there : 
> Active/Active : It is the basically use for the default nodes, Here first node will be default and second node will be named instance. Both node will be active.
> Its Move group from cluster administration is possible for both side. 
> System performance will go down, if both resources are in one node.

> Active/Passive : Its also basically use for nodes But in this case Only one Active node with default instance. No system performance degradation will be there for this case even if we switchover to the other node.  Both have 
same configuration.

What is denormalization and when would you go for it?

denationalization is the reverse process of normalization it is also indicate the name of denenormalisaiotn . It\'s mainly use for the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

What are the Different methods of loading Dimension tables?

There are many different of ways are there which is two different ways to load data in dimension tables.  
> Direct (Fast) : It is the basically use for the loading purpose in this way All the constraints and keys are can be disabled before the data is loaded. Once a tym data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty it is not included in index and all future processes are skipped on this data.
> Conventional (Slow) : It is basically for the All the constraints and keys are validated against the data before, it is loaded, this way data integrity is maintained.

What is surrogate key?

Surrogate keys are always integer or numeric.  It is useful because the natural primary key can change and this makes updates more difficult .Surrogate key is the basically a type of 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..

What is the Referential Integrity?

Referential integrity is the basically explain to the consistency that must be maintained between primary and foreign keys.

What do you mean by Live Lock?

A live-lock is a single lock which is basically work on the request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. A live lock 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. 

What do you mean by Deadlock?

Deadlock is a basically a name of situation, when two processes are request to the one lock of the one piece of data, attempt to acquire a lock on the others piece. so ofcouse Each process  would wait dor the release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user\'s process.

What is the purpose of UPDATE STATISTICS?

As like as UPDATE STATISTICS have a many purposes but right now we have a main purpose which is explain here :
> It is main;y use for the Updates information about the distribution of key values for one or more statistics groups or collections in the specified table or indexed view.

What is the use of SCOPE_IDENTITY() function?

SCOPE_IDENTITY() function is basically use for the Returns that values which is the most recently created identity value for the tables in the current execution scope.

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

We can Transfer the Data from text file to database Using the BCP utility, Where BCP stands for Bulk Copy Program .

What is a Join in SQL Server?

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

What is RAID and what are different types of RAID configurations?

Redundant Array of Inexpensive Disks is abbreviation of RAID, It is mainly use for providing the facility of  fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. 

What are the main attributes of database management system?

A database management system have a many attributes but here we are mentioned that DBMS is composed of five elements, Which is there : 
> Computer hardware, 
> Software, 
> Data, 
> People (users), and 
> Operations procedures

What are the main components of Database management systems software.

The database management system software includes
many components are there, like :
>  for storage management, 
> concurrency control,
> transaction processing, 
> database manipulation interface, 
> database definition interface, and 
> database control interface.

What is query optimization?

In the database many queries generate and executed or 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.

What is Oracle locking?

Oracle is the basically a database Its have a many facility, that facilities is one of the name is locking which uses locking mechanisms to protect data from being destroyed by concurrent transactions.

What Oracle lock modes do you know?

Oracle has two lock modes : 
> shared or 
> 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

Describe TO_DATE function?

The TO_DATE function using the values when we want to be show the timestamps from a character string that has been interpreted using a character template.

What is the Sql Wild card?

SQL Wild Card is the basically use for the searching operation similarly 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

What are the difference between DDL, DML and DCL commands?

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

Which one is faster DELETE/TRUNCATE?

Truncate 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.

What is RDBMS?

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.

What are different normalization forms?

 normalization 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.

What is normalization?

Normalization is a process which is the tables in a database are optimized 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.

What is Stored Procedure

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

What is Trigger?

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.

What is View?

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.

What is Index?

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.

What is SQL?

SQL is a basically 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.

What are the components of physical database structure of Oracle database?

Physical components of oracle database are: 
> Control files,
> Redo log files and 
> Data files.

> Control file : control file is basically 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 physical files which stores data of all logical structure.

What are the components of logical database structure of Oracle database?

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.

What is a tablespace?

A Tablespace is basically a logical storage unit within an Oracle database. It is logical because a table space 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. 

What is SYSTEM tablespace and when is it created?

Every ORACLE database have a table space which name is SYSTEM . when the database is created that a time its also automatically created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

Explain the relationship among database, tablespace and data file.

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. 

What is schema?

Schema basically Pronounce schema, 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. 

What are Schema Objects?

Schema is basically 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.

Can objects of the same schema reside in different table spaces?

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.

Can a tablespace hold objects from different schemes?

Yes we can a template hold objects from different schemes.

What is Oracle table?

The Collection of informations stored in the strutrued format which is in rows and columns formate that is called a table.

What is an Oracle view?

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.

Do a view contain data?

Of course 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 just stating the view name instead writing the select statement every time.

What is ACID ?

ACID is the properties of the transaction :  

Atomicity
Consistency
Isolation
Durability

Define the Primary key?

The Primary Key is basically 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.

What do you mean by Candidate key?

Candidate key is basically 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.

What is the Foriegn Key?

NA

What are the use of Null?

NULLs are basically for use in database for describe that no value or attributes are exist, 
But are not really values themselves. Instead they are just an indicator that a particular field has no value. Some discussions 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.

What are the properties of the Relational tables?

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.

What is De-Normalization?

De-Normalization is basically 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.

What is the mean of Nested Trigger?

A Trigger have a many authorities it can also perform to the INSERT,UPDATE and DELETE operations 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.

What is a Linked Server?

Linked Servers is basically 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.

What is Cursor?

Cursor is basically a database object,which is mainly used by applications to
manipulate data in a set on a Row by Row basis, Instead of use the typical
SQL commands that operate on all the rows in the set at one time. In order to
work with a cursor we need to perform some steps in the Following order : 
> Declare cursor
> Open cursor
> Fetch row from the cursor
> Process fetched row
> Close cursor
> Deallocate cursor

What is Collation?

Collation is basically 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.

What is Difference between Function and Stored Procedure?

Many Difference are there :> UDF can be used WHERE/HAVING/SELECT sectionanywhere in the SQL statements, But in case ofStored procedures cannot be. > UDF\'s that return tables can be treated asanother rowset. This can be used in JOINs withother tables. Inline UDF\'s can be thought of asviews that take parameters and can be used inJOINs and other Rowset operations.

What is the Sub-Query?And what is the properties of Sub-Query?

Sub-queries are bassically defined assub-selects, Its allow a SELECT statement to beexecuted arbitrarily within the body of anotherSQL statement. A sub-query is mainly executed byenclosing in a set of parenthesis. Sub-queriesare generally used to return a single row as anatomic value, They may be used to comparision,Its may be use for compare the values againstmultiple rows with the IN-keyword. A Subquery isa SELECT statement that is nested within anotherT-SQL statement. A Subquery SELECT statement ifexecuted independently of the T-SQL statement, Inwhich it is nested, will return a resultset.Meaning a subquery SELECT statement can standalone and is not depended on the statement inwhich it is nested. A subquery SELECT statementcan return any number of values, and can be foundin, The column list of a SELECT statement,FROM,GROUP BY,HAVING,and/or ORDER BY clauses of aT-SQL statement. A Subquery can also be used as aparameter to a function call. Basically asubquery can be used anywhere an expression canbe used.

Give the list how many type of Joine?

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

Define : What is the Cross Join?

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 basically 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.

give us a definition of Inner Join?

Inner Join is a basically use for presentation the tables or format, 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.

What do you mean by Outer Join? and define also how many types of Outer Join?

Outer Join is the basically 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.

Defeine : Self Join?

Self Join is basically 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 hierarchical reporting structure whereby one member of staff reports to another. Self Join also use can be Outer Join or Inner Join.

What is User Defined Functions?

User Defined Functions is basically 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.

What kind of User-Defined Functions can be created?

We can a create User-Defined Functions in a many ways they are defined as :
> Scalar User-Defined Function :  A Scalar user-defined function is basically a return value like one of the scalar data types. It is not a type of supported data types like Text,next,image and timestamps. 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 parametrized, 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.

What is Identity?

Identity is a basically 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; these type of values cannot be controlled. Identity/GUID columns do not need to be indexed.

What is DataWarehousing?

DataWarehousing is the basically 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, Only once committed, The data should 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.

What are slowly changing dimensions (SCD)?

SCD is the basically stands for Slowly Changing Dimensions. Its mainly use for the changing dimensions very slowly because its applies to cases  where the attribute for a record varies over time.
There are three types of SCD.
> SCD1
> SCD2
> SCD3

What is the clustered index?

A Clustered index is basically 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 .

What is the SCD1 ?

In The SCD1 mainly use for the create a new record and its also can be replaces the original record. Only one record exist in database - current data.

What do you mean by A non clustered index?

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.

What are the different index configurations a table can have?

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

What are different types of Collation Sensitivity?

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 Basically 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.

What is OLTP?

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.

What do you mean by TRUNCATE?

Truncate is basically 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.

What do you mean by DELETE command?

Delete Command basically 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.

When is the use of UPDATE_STATISTICS command?

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.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

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. 

What are the properties of SUB-QUERY?

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.

What are the type of SUB-QUERy?

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.

What is SQL Profiler?

SQL Profiler is basically a graphical tool, Which is mainly use for allow 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 because the trace file or trace table to grow very large, Specially when the monitoring process takes place over a long period of time.

What are the authentication modes in SQL Server? How can it be changed?

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.

What is SQL Server Agent?

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.

Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

Yes we can stored procedure call itself or recursive stored procedure. Because Stored procedures is basically 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 because of call, Recursion have a techniques or methods of problem solving, Where in the solution is applying 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. 

What is Log Shipping?

Log shipping is basically a process of the database and its mainly 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.

What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?

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. 

What is the mean of Local temporary table?

Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.A local temporary table is basically 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.

What do u mean by Global temporary table?

Global temporary tables are distinct within SQL sessions. The table definition is the basically 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 dispersal in the global temporary table,When connection is closed.

What is ER Diagram ?

ER Diagram is stands for entity Relationship Diagrams , It  is the basically a major data modelling tool . A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables. and 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.

What is BCP? When does it used?

BulkCopy is basically 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. 

What is the UNION?

UNION is the basically a type of a command which is used for the selection procedure, It is select the related information from the two tables, similarly 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.

What is an execution plan? When would you use it? How would you view the execution plan?

An Execution plan is basically presentation of the map in graphically or textually shows of the dat retrival methods whihc is chosen by the SQL Server abd it can be query optimizer 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.

How to implement one-to-one,one-to-many and many-to-many relationships while designing tables?

Implementations of relationship is there : 

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

> Many-to-Many relationships : Its relationship is basically use for implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What do you mean by The master database?

The Master Database is basically use for the database information which 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 master database, We must administrator this database with care.

Define whats the mean by Resoure Database?

The Resource Database is the basically use for containing the system objects its stand for the read only database,The Resource database does not contain user data or user metadata. System 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. 

What is Service Broker?

Service Broker is the mainly use for developing to integrated SQL Server which is fully into distributed applications, It is basically  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.

What is Policy Management?

Policy Managements 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 basically 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.

What is CTE?

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. 

What is MERGE Statement?

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.

What is Filtered Index?

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.

What are the Advantages of using CTE?

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.

What are synonyms?

Synonyms basscially use for the given a alternate name of the database objeats its a mainly use of synonyms, we can alias object names, for the example using the Employee table as Emp. We can also shorten names. Its mainy use for the dealing a two or frour name parts ; for example, shortening server.database.owner.object to object.

What is LINQ?

LINQ is an abriviation as Language Integrated Query,Its using .NET languages. Its have a ability for adds query objects,Its have a some basic feacture which is defined as : 
> Its have a Tools which is create classes That usually called entities, mapped to database tables.
> Its Compatibility with LINQ�s standard query operations.

What is Isolation Levels?

Isolaion level is bassically comes under the transaction Its signifies the degree, which is transaction should be isolated from resource or data modifications made by other transactions. that tyime Isolation levels are explain that type concurrency side-effects, such as dirty reads or phantom reads, are allowed.

What is the NOLOCK?

NOLOCk is the bassically a technique which is useful something for the busy system . Its help on the system which is generally consider good practice to improve the concurrency, When in SELECT statement use whith the NOLOCK hints,when NOLOCK hint is taken then Data is read The result is a Dirty Read, which means that another process could be updating the data at the exact time we are reading it. its not a garunteed for the most of data to be read.in the hints give us a advantages of the performance 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 lock, 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 readshave completed, and reads requested after the update will wait for the updates to complete. The result to our system is delay blocking.

What is the RaiseError?

RaiseError bassically use for the Error messege.Its generate  and initiate error processing in the session. RAISERROR can bassically defined a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned and displays as a server error message to the calling application or to an associated CATCH block of a TRY�CATCH construct.

What is Xml Datatype?

Xml is the bassically that type of Datatype which is use for SQL Server Datatype, Its a Xml documents and fragments in a Sql server. Xml bassically a instances 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.

What is Data Compression?

in the new version of SQl Server 2008 are diff type of Data Compressions :
> Row Compression
> Page Compression
> Dictionary Compression 

What is the mean of Row Compression?

Row compression changes the format of physical storage of data. It minimize the metadata
In SQL SERVE 2008 Data Com column information, length, offsets etc .associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar.

What do you mean by DBCC Command?

DBCC is bassically stands for Database conso;e commands for SQlServer. And it is working with T-SQL Programming Language. DBCC commands have a many uses for the performing new tasks which is following here : 
> It is use as a Tasks that gather and display various types of information.
> Validation operations on a database, table, index, catalog, file group or allocation of database pages.
> Miscellaneous tasks such as enabling trace flags or removing a DLL form memory .
> Maintinence Task on Database , Index or file group.

What is Catalouge Views?

Catalouge Views is basssically use for the returing the information, it is used by the SQL Server Database Engine.It is the very importnt general interface catalouge to the matadata and Its provide the most efficient way to obtain ,transform, and present costomised forms of this information .all user available catalouge matadata is exposed through catalouge views.

What is the Ranking functions?

Ranking Function is the bassically use for the return value which is the ranking of the each row in the partion. These ranking functins are non Deterministic. there are many diff type of ranking functions :
> ROW_NUMBER() OVER([])
> RANK() OVER([])
> DENSE_RANK() OVER([])

What is the mean of ROW_NUMBER() OVER?

It s the sequential no of the row with in the partition pf the resultset,starting at 1 for the First row in each partition.  

What is the RANK() OVER?

It is bassically Return the value as a rank of each row within the partition of a result set.

What is the DESK_RANK() OVER?

Its retun the value as a rank of rows ith in the partition of the result set, without any gap in the ranking .

What is the mean of UNIONALL?

UNIONALL is bassically a command, It is much like a UNION COMMAND, But it is some diff'es like : THis command is select all values.

What is the mean of Root Node?

A Root Node is bassically use for containing the node Pointers to branch node which can be only one.

What do you mean by Branch Node?

A Branch Node is bassically contain the Pointers for leaf nodes or the other Branch nodes , Which can be two or more.

What do you mean by Leaf Nodes?

A Leaf Node is bassically type of Container which is contain the Index terms and horizontal pointer also for other leaf nodes .Which can be many .

What is lock escalation?

Lock escalation is the bassically use for the converting a lot of low level locks like a row lock or the level locks into higher level locks which is like table locks. Every lock is a memory structure 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. 

What do you mean by Snow schema?

Snow Schema is the bassically known as snow flake schema, Its mainly interlinked or may have one to many relationship with other tables. This schema is normalized and results in complex join and very complex query as well as slower results.

What do you mean by Star schema?

Star Schema is bassically use for got a faster results becouse it is a single fact table with N number of Dimension, which will be linked directly with a fact table. This schema is De-normalized and results in simple join and less complex query as well as faster results.

What do you mean by SCD3 ?

SCD3 is bassically use for only the modifcation and create a new data from The original data . One record exist in database-new information are attached with old information in same row.

What do you mean by SCD2?

SCD2 is mainly use for the add a new record into the customer dimension table. Two records exist in database-current data and previous history data.

What is RAID?

RAID is the bassically 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, fault tolerance. 

What is DTC?

DTC is a stands for The Microsoft Distributed Transaction Coordinator (MS DTC), It is bassically 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.

What is DTS?

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

What are defaults? Is there a column to which a default can't be bound?

A default is a bassically a type of value whihc 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.

What do you mean by COLLATION?

Collation is basically use for the ordering It is the sort order. There are three types of sort order Whihc is following here :
> Dictionary case sensitive, 
> Dictonary case insensitive and 
> Binary.

When do you use SQL Profiler?

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

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

Replication is he bassically using for the copy of the data and move the 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

What do you mean by Distributed partitioned views.?

Distributed partitioned views bassically dor providing the facility to the 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.

How many New Data types are in the SQL Server 2000?

there are many types of new data types , Which are signifies here : 
> Sql_variant data type : This type is a type that 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. 

What do you mean by INSTEAD OF and AFTER Triggers.?

There are many diff are in INSTEAD OF and AFTER Triggers in SQL Server 2000. Which is following here : 
> INSTEAD OF triggers are bassically work on instead of the INSERT, UPDATE or DELETE triggering action. But in case of �AFTER� triggers are work on after the triggering actions.

What do you mean by Cascading Referential Integrity Constraints?

Right now only two new clouses are there, Which is : 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 : Its bassically controls what actions are taken if we attempt to delete a row to which existing foreign keys point.

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

Whay do you mean by XML Support in Sql Server 2000?

Database engine bassically use for the returnind the data as Extensible Markup Language (XML) documents. In The SQL Server 2000, XML Bassically use for the  : 
> insert The value , 
> update The value , and 
> delete values , In the database.

What do you mean by Indexed Views>

Indexed views is bassically are persistent, It can be significantly improve application performance by eliminating the work that the query processor must perform to resolve the views. It is bassically a views but in some cases Its Unlike standard views,Which views like SQL Server resolves the data-access path dynamically at execution time, The new indexed views feature are there which is following here : > we can store views in the database as like as  we can store tables. 
            

Why we use Stored Procedure?

Stored procedure is bassically have more utilising content Its uses in a every feild of SQL Server, There are four major reasons why we use this procedure which can be beneficial: 
> It can dramatically increase security.
> They can assist you in centralizing your code.
> They are executed on the Server's machine.
> They are precompiled.

What is the use of union in Indexing?

UNION basically use combine the results , Which results have a two or more queries in to a single result set, Which is consisting of all the rows belonging to all queries in the union.

What do you mean by Read Committed?

t is the bassically defined as a Isolation properties , It is using for the tracsaction, which is 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.

What is the basic difference between a join and a union?

The Basic diff are there :
> Joine is bassically use for the colums but in case of union is does't.
> Union is bassically us efor the rows but in case of the Join is does't.
> A join selects columns from Two or more tables. A union selects rows. 

What is a Cartesian product?

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

What do you mean by Read Uncommitted?

First of all I Would like to tell every one its a Type of Isolation Level , It is the mainly use for the transaction, which 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.

What do you mean by Repeatable Read?

It is the defined basically a isolation properties and its works on the Isolation level as a 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.

What do you mean by Serialized?

It is the part of the isolation level , Its basically use for the 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.

Discuss about Disadvantages of cursors?

Many disadvantages are in Cursor which is defined here :
> Occurs network round trip because Each time we fetch a row from the cursor,; where as a normal SELECT query makes only one round trip, however large the resultset is. 
> It is 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 certain condition. We don\'t have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row.

What does COMMIT do?

Its manly work is Saving all changes made by DML statements.

What databases do you know?

Yes we know lots of Databases, we are mentioned there :
> Informix     
> DB2     
> SQL     
> Oracle 

In what sequence SQL statement are processed?

The sequence of SQL statement are processed in the following sequence (DB2):
> FROM clause
> WHERE clause
> GROUP BY clause
> HAVING clause
> SELECT clause
> ORDER BY clause
> FETCH FIRST clause 

Class members

What is use of \'in\' command?

in command use suppose u want to choose department then you use in command

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

I Have a table in which there is a chance of duplicate rows. Can you please tell me how to eliminate the dup;licate records?

Use primary key If column is like Id or no..Alternate way is you can apply unique constraint on column.. hope u hv cleared ur doubts