SQL-Interview Questions Part-2

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

(2)Benefit of view?

(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,)

(3) How many tables reference in the SELECT Statement?

            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

(5)  What is Triggers? What are the benefits of triggers?

            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.

(6) What is cursor?

            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.

(7) What is the difference between order by and group by?

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

(8) What is a transaction?

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

                       

(9)     what is normalization? what are the types of normalization?

            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.

(10)  What is denormalization?

            Denormalization is the process of putting one fact in numerous places

(11) what is surrogate key?

            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.

(12) what is 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)
);

  

(14) Difference between function and stored procedure?

(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

Using NOLOCK in SQL Server

When data in a database is read or modified, the database engine uses special types of controls, called locks, to maintain integrity in the database. Locks basically work by making sure database records involved in a transaction cannot be modified by other transactions until the first transaction has committed, ensuring database consistency.

When designing database applications, you should keep in mind the different types of locks that will be issued, and the different levels of isolation your transactions will occur. Typically, the SQL Server defaults work fine for what you are trying to accomplish. However, there will be times when it is advantageous to manually make hints to how locks are issued on your tables in your SQL statements.

This article focuses on table : NOLOCK. I'll set up a table to use for our example queries. Execute the script in  to create the Orders table and populate the table with data.

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'Orders')

DROP TABLE Orders

GO

CREATE TABLE Orders ( OrderId int, ProductName varchar (30), OrderDare Date, Price money, )

GO

--Insert values to the table

INSERT INTO Orders VALUES (1,'Bag',GETDATE(),1500.00)

INSERT INTO Orders VALUES (1,'Book',GETDATE(),200.00)

GO

NOLOCK

This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

The benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance. The downside is that, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read. A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems. The trick is being able to know when you should use NOLOCK.

The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the SalesHistory table.

BEGIN TRANSACTION

INSERT INTO Orders

    (OrderId, ProductName (30), OrderDare, Price)

     VALUES

     (3,'Shoes',GETDATE(),2000.00)

        
                  The transaction is still open, which means that the record that was inserted into the table still has locks issued against it. In a new query window, run the following script,

select * from Orders

The above query is in Executiong stage only and resultwon’t display.

Since the table Orders is locked by the insert statement.

With  using  the NOLOCK table hint in returning the  records in the Orders table.

select * from Orders WITH(NOLOCK)

The number of records returned is 3. Since the transaction that entered the record into the Orders table has not been committed, I can undo it. I'll roll back the transaction by issuing the following statement:

ROLLBACK TRANSACTION

This statement removes the record from the SalesHistory table that I previously inserted. Now I run the same SELECT statement that I ran earlier:

SELECT COUNT(*) FROM Orders WITH(NOLOCK)

This time the record count returned is 2. My first query read a record that was not yet committed -- this is a dirty read.

When to use  WITH (NOLOCK)

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it IMHO.

Most banking applications can safely use nolock because they are transactional in the business sense. You only write new rows, you never update them

Most of the Real time scenario’s when we used to read the date we need to use the WITH(NOLOCK)

The question is what is worse, a Deadlock or a wrong value?

 For finanical databases, deadlocks are far worse than wrong values. I know that sounds backwards, but hear me out. The traditional example of DB transactions is you update two rows, subtracting from one and adding to another. That is bullshit.

In a financial database you use business transactions. That means adding one row to each account. It is of upmost importance that these transactions complete and the rows are successfully written.

Getting the account balance temporarily wrong isn't a big deal, that is what the end of day reconciliation is for. And an overdraft from an account is far more likely to occur because two ATMs are being used at once than because of a uncommitted read from a database.