(1)what is view?

A view is basically predefined query (a SELECT statement) that is stored in the database for later use. (Or) basically a predefined query stored in a database.

(I)        Using views, users don't query the tables directly.

(ii)        Views can be used to horizontally partition the data in a table.(users are allowed to see only two of these three columns.)

(iii)       Information schema views can be used as an alternative way to deal directly with system tables.

(iv)       Indexes can be created on views.

(v)        Distributed partitioned views that are updatable.

(vi)       Views is the introduction of instead-of triggers.(this new type of trigger can be defined on views,)

            A maximum of 256 tables can be referenced in a SELECT statement.

If you have a extracting information from more than 256 tables, use temporary tables or derived tables to store partial

(4) What is the Stored Procedure? And the difference statements?

            A stored procedure is a database object that comprises one or more Transact-SQL statements.

            Stored procedure can be reused just by calling its name.

            Usually, stored procedures are used to encapsulate or enforce business rules in your databases.

            The following are the benefits and advantages of stored procedures:

            (i)They are precompiled statements (ii) They optimize network traffic

            (ii)They can be used as a security mechanism (iv) They allow modular programming

            (v) They can be set to execute automatically when SQL Server starts (vi) They can use parameters

            A trigger is a stored procedure that is called automatically whenever you execute the action to which the trigger is defined. You cannot call a trigger directly, but you can execute the action that fires the trigger.

            A trigger does not accept parameters and cannot call the RETURN statement to return a value, but it can return results, as any other stored procedure, although it is not recommended. A trigger executes in the background

            and it shouldn't return anything other than error messages, if required.

            A cursor is a means for accessing the individual rows in a table or result set. The rows are accessed one at a time. Accessing individual rows via cursors is like using a select statement to return a single row. SQL Server takes care of tracking the cursor position with the result set or table.

            you would be required to process individual rows from a result set in a specific order and, in these cases, you can use cursors.

             Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.

                       

            you can use a single SELECT statement to update many rows of data.? There are times when you want to loop through a series of records a perform processing for each record.? In this case you can use a cursor.?

            SQL Server 2000 supports four types of cursors:

·                       Forward-only cursors can retrieve data from the first to the last row, without any other navigation capabilities.

                        Static cursors provide a snapshot of the data to navigate without being affected by other connections.(Static cursors provide a fixed set of data that does not detect changes made by other connections.)

                        Dynamic cursors retrieve a dynamic result set that sees modifications to the data made from outside the cursor. ( Dynamic cursors provide a flexible set of data that reflects changes made to the data by other connections.)

                        Keyset-driven cursors create a fixed set of rows to navigate.

example: select id group by id order by id

GROUP BY - you use this so that no duplicate will exist in IDs.

ORDER BY - means you sorted your id.

So that if you have data like F002, F003,F004,F002,F003,F004...

What returned from your query (from above sample) was: F002,F003,F004

A transaction is a sequence of operations executed as a single logical operation. 

You can consider three different types of transactions:

(i) Auto commit transactions— SQL Server always starts a transaction whenever any statement needs to modify data.

(ii)Explicit transactions— The programmer specifically declares the transaction starting point and decides either to commit or rollback changes                    depending on programming conditions.

    (iii)Implicit transactions -but it is the programmer's responsibility to specify the transaction ending point and confirm or reject applied changes.

To confirm the changes made inside a transaction, you must execute the COMMIT TRANSACTION (or COMMIT TRAN) statement.

            To cancel the changes applied during a transaction, you can use the ROLLBACK TRANSACTION (or ROLLBACK TRAN) statement.

In a multiuser environment, as several users try to access the same data at the same time, trying to perform different actions, you can encounter various currency problems.

Lost Updates:  You can experience a lost update situation whenever two connections modify the same data in sequence,because SQL Server will maintain only the last                            successful update. ex:  This problem can be prevented by writing atomic UPDATE statements in both Connection A and Connection B.

Uncommitted Dependency (Dirty Read) :     Reading data without using locks can produce unexpected results.Reading data without using locks can produce                                                                                   unexpected results.In this case, you could say that you are reading dirty data, because the data has not been committed t.

ex: SQL Server 2008 prevents this problem automatically by using READ COMMITTED as the default isolation level.

            ex: SET ISOLATION LEVEL READ COMMITTED        

Inconsistent Analysis (Nonrepeatable Read): Trying to execute a long-running process, such as a monthly report, can produce some inconsistencies because of changes                                                     produced in the database from the beginning of the report to the end. This can be considered an inconsistent analysis,because every time you read data, the data is different. This situation is called nonrepeatable reads.

ex: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Phantom Reads           :           If other connections are inserting data in the range of data you are analyzing, you can find that those new rows appear in your result sets with                                           no apparent reason, from your connection point of view. These new rows are called phantoms.

            ex: You can use the SERIALIZABLE isolation level, or the SERIALIZABLE optimizer hint, to prevent phantom reads.

            ex: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

                       

            Normazation is set of rules in relation data base .(how normalized" a relational database is are called normal forms (abbrev. NF).

            First normal form — the key. One primary key is used to uniquely identify each row. No groups are repeated .

            Second normal form — the whole key. No non-key field is dependent upon a portion of the key. All non-key fields are dependent upon the entire primary key.

            Third normal form — nothing but the key. Non-key fields cannot be dependent upon other non-key fields.

            Fourth normal form — remove independent multiple relationships. No table can contain multiple one-to-many (1:n) or many-to-many (n:m) relationships that are not                         Fifth normal form — remove semantically related multiple relationships. A common example is closely related record types that collect slightly different data.

            Denormalization is the process of putting one fact in numerous places

            However, you can create an artificial attribute, called a surrogate key, that uniquely identifies every row, working as a simplification of the natural PRIMARY KEY.

            A primary key is a system used for indexing, and is usually a number that is automatically assigned by Access to each new record. These can also be used as

            ID numbers for each record. There can only be one primary key per data source.The value of the primary key field will be unique for each record and can be used to         distinguish records with the same information.

            A primary key is a table column that can be used to uniquely identify every row of the table. Any column that has this property will do -- these columns are called                   candidate keys. A table can have many candidate keys but only one primary key. The primary key cannot be null.

(13) what  is composite primary key ?

A primary key consists of one or more columns (from that table). If a primary key consists of two or more columns it is called a composite primary key. It is defined as follows:

CREATE TABLE Results(
  QuestionID NUMERIC,
  MemberID NUMERIC,
  PRIMARY KEY (QuestionID, MemberID)
);

  

(I)    SP returns more than one value at a time while function returns only one value at a time.

(ii)   Function does not return the images, text whereas sp returns all

(iii)  Functions MUST return a value, procedures need not be.

(iv)  We can select the fields from function. in the case of procedure we cannot select the fields.

(v)   UDF can run an executable file from SQL SELECT or an action query, while Stored      Procedures (SPROC) use EXECUTE or EXEC to run

(vi)   Stored Procedure is pre compiled exaction plan where as functions are not.

(vii) UDFs can't change the server environment or your operating system environment, while a SPROC can.

(viii) UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

(ix)       You'll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be.

(x)   Function parameters are always IN, no OUT is possible

             You might be tempted to believe that stored procedures add more overhead to your server than a UDF