Welcome To My Blog !Have a nice day :): Sql&OOP Tips

SQL and Database Specific

What are joins?

Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join.

How many types of Joins?

Joins can be categorized as:

Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN -The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
RIGHT JOIN or RIGHT OUTER JOIN - A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
FULL JOIN or FULL OUTER JOIN - A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Cross joins - Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products. (A Cartesian join will get you a Cartesian product. A Cartesian join is when you join every row of one table to every row of another table. You can also get one by joining every row of a table to every row of itself.)

What is self join?
A table can be joined to itself in a self-join.

What are the differences between UNION and JOINS?
A join selects columns from 2 or more tables. A union selects rows.

In Join, matching rows are joined side-by-side to make the
result table but whereas in Union rows are joined one-below
the other to make the result table.

join:

The SQL JOIN clause is used whenever we have to select data
from 2 or more tables.
To be able to use SQL JOIN clause to extract data from 2
(or more) tables, we need a relationship between certain
columns in these tables.

union:

The purpose of the SQL UNION command is to combine the
results of two queries together. In this respect, UNION is
somewhat similar to JOIN in that they are both used to
related information from multiple tables. One restriction
of UNION is that all corresponding columns need to be of
the same data type. Also, when using UNION, only distinct
values are selected (similar to SELECT DISTINCT).

What is a view?

A view is used to filter out and control access to data in a database, limiting the results to only those needed for the transaction to complete. Instead of giving access to the original table we create a view and….

What is Stored procedure?
A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.

They allow modular programming.
They allow faster execution.
They can reduce network traffic.
They can be used as a security mechanism.

What is a transaction?

A collection of SQL statements that are treated as a single unit of work, and that are either committed in their entirety or non at all via a roll back. Rule of thumb for transaction is it should have ACID property, stands for Atomicity, Consistency, Isolation, Durability.

A transaction must be:

1. Atomic - it is one unit of work and does not dependent on previous and following transactions.

2. Consistent - data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t.

3.Isolated - no transaction sees the intermediate results of the current transaction.

4 Durable - the values persist if the data had been committed even if the system crashes right after.

List the different isolation levels supported by SQL?

Read Uncommitted, Read Committed (default), Repeatable Read, Serializable

What is an execution plan?

It’s a compiled set of SQL statements. You can view the execution plan using Query Analyzer.

How do you know what fields to create an index on for a particular query?

You should create an index for all fields referenced in the ‘WHERE’ clause and any fields that are used in any joins.

List the different recovery models SQL supports?

Simple, Full, and Bulk Logged

What is Index? It’s purpose?
Indexes in databases are similar to indexes in books. In a database, an index allows the database program to find data in a table without scanning the entire table. An index in a database is a list of values in a table with the storage locations of rows in the table that contain each value. Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees. An index contains an entry with one or more columns (the search key) from each row in a table. A B-tree is sorted on the search key, and can be searched efficiently on any leading subset of the search key. For example, an index on columns A, B, C can be searched efficiently on A, on A, B, and A, B, C.

Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433

Explain about Clustered and non clustered index? How to choose between a Clustered Index and a Non-Clustered Index?

There are clustered and non-clustered indexes:

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Consider using a clustered index for:

Columns that contain a large number of distinct values.
Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
Columns that are accessed sequentially.
Queries that return large result sets.

Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences:

The data rows are not sorted and stored in order based on their non-clustered keys.
The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.
Per table only 249 non clustered indexes.

Disadvantage of index?
Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much.

Given a scenario that I have a 10 Clustered Index in a Table to all their 10 Columns. What are the advantages and disadvantages?
A: Only 1 clustered index is possible.

How can I enforce to use particular index?
You can use index hint (index=<index_name>) after the table name.
SELECT au_lname FROM authors (index=aunmind)

What is Index Tuning?
One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes.

You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.

You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience dexcellent performance gains.

Index Tuning is the process of finding appropriate column for non-clustered indexes.
SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process.

Explain the difference between Index defrag and Index rebuild?(DBCC DBREINDEX & DBCC INDEXDEFRAG)
When you create an index in the database, the index information used by queries is stored in index pages. The sequential index pages are chained together by pointers from one page to the next. When changes are made to the data that affect the index, the information in the index can become scattered in the database. Rebuilding an index reorganizes the storage of the index data (and table data in the case of a clustered index) to remove fragmentation. This can improve disk performance by reducing the number of page reads required to obtain the requested data

In case DBCC DBREINDEX, there will be:

-building the index as new which will result locking the table from access

-usually, it takes relatively longer time

In case of DBCC INDEXDEFRAG:

- Defragments clustered and secondary indexes of the specified table or view.

-It takes relatively short time to defragment depending on the scattered indices.

-every step of the defragmentation will be logged

Can I improve performance by using the ANSI-style joins instead of the old-style joins?
Code Example 1:
select o.name, i.name
from sysobjects o, sysindexes i
where o.id = i.id
Code Example 2:
select o.name, i.name
from sysobjects o inner join sysindexes i
on o.id = i.id
You will not get any performance gain by switching to the ANSI-style JOIN syntax.
Using the ANSI-JOIN syntax gives you an important advantage: Because the join logic is cleanly separated from the filtering criteria, you can understand the query logic more quickly.
The SQL Server old-style JOIN executes the filtering conditions before executing the joins, whereas the ANSI-style JOIN reverses this procedure (join logic precedes filtering).
Perhaps the most compelling argument for switching to the ANSI-style JOIN is that Microsoft has explicitly stated that SQL Server will not support the old-style OUTER JOIN syntax indefinitely. Another important consideration is that the ANSI-style JOIN supports query constructions that the old-style JOIN syntax does not support.

Difference between char and nvarchar / char and varchar data-type?

char[(n)]

Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000.
Storage size is n bytes.
The SQL-92 synonym for char is character.

nvarchar(n)

Variable-length Unicode character data of n characters.
n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered.
The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.

varchar[(n)]

Variable-length non-Unicode character data with length of n bytes.
n must be a value from 1 through 8,000.
Storage size is the actual length in bytes of the data entered, not n bytes.
The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

What is the data size of GUID and how is unique across machines?

128bit
To ensure uniqueness across machines, the ID of the network card is used (among others) to compute the number.

What is the difference between text and image data type?

Use text for character data if you need to store more than 255 characters in SQL Server 6.5, or more than 8000 in SQL Server 7.0. Use image for binary large objects (BLOBs) such as digital images. With text and image data types, the data is not stored in the row, so the limit of the page size does not apply. All that is stored in the row is a pointer to the database pages that contain the data. Individual text, ntext, and image values can be a maximum of 2-GB, which is too long to store in a single data row.

What is derived table?

Derived tables are SELECT statements in the FROM clause referred to by an alias or a user-specified name. The result set of the SELECT in the FROM clause forms a table used by the outer SELECT statement. For example, this SELECT uses a derived table to find if any store carries all book titles in the pubs database:

SELECT
 ST.stor_id, ST.stor_name

FROM stores
 AS ST,

    
 (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count

     
 FROM sales

     
 GROUP BY stor_id

    
 ) AS SA

WHERE
 ST.stor_id = SA.stor_id

AND
 SA.title_count = (SELECT COUNT(*) FROM titles)

What are the different types of Storage Procedure?

Temporary Stored Procedures - SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends. Temporary procedures named with # and ## can be created by any user.
System stored procedures are created and stored in the master database and have the sp_ prefix.(or xp_) System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master. (If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.)
Automatically Executing Stored Procedures - One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.
User stored procedure

How do I mark the stored procedure to automatic execution?
You can use the sp_procoption system stored procedure to mark the stored procedure to automatic execution when the SQL Server will start. Only objects in the master database owned by dbo can have the startup setting changed and this option is restricted to objects that have no parameters.

USE master

EXEC sp_procoption 'indRebuild', 'startup', 'true')

How will know whether the SQL statements are executed?
When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or procedure. If no value is specified on RETURN, a stored procedure returns the value 0. The stored procedures return a value of 0 when no errors were encountered. Any nonzero value indicates an error occurred.

Why one should not prefix user stored procedures with sp_?
It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

The stored procedure in the master database.
The stored procedure based on any qualifiers provided (database name or owner).
The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

What can cause a Stored procedure execution plan to become invalidated and/or fall out of cache?

Server restart
Plan is aged out due to low use
DBCC FREEPROCCACHE (sometime desired to force it)

When do one need to recompile stored procedure?
if a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted).
SQL Server provides three ways to recompile a stored procedure:

The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.
Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon, and causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed.
You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is different or if the data has significantly changed since the stored procedure was created.

What are the different types of Storage Procedure?

Temporary Stored Procedures - SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends. Temporary procedures named with # and ## can be created by any user.
System stored procedures are created and stored in the master database and have the sp_ prefix.(or xp_) System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master. (If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.)
Automatically Executing Stored Procedures - One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.
User stored procedure

How do I mark the stored procedure to automatic execution?
You can use the sp_procoption system stored procedure to mark the stored procedure to automatic execution when the SQL Server will start. Only objects in the master database owned by dbo can have the startup setting changed and this option is restricted to objects that have no parameters.
USE master
EXEC sp_procoption 'indRebuild', 'startup', 'true')

How will know whether the SQL statements are executed?
When used in a stored procedure, the RETURN statement can specify an integer value to return to the calling application, batch, or procedure. If no value is specified on RETURN, a stored procedure returns the value 0. The stored procedures return a value of 0 when no errors were encountered. Any nonzero value indicates an error occurred.

What is a page split?

Page splits cause table fragmentation. When data is to be inserted into a page that doesn’t have enough space to accept it, the page is said to ‘split’. Splitting is where a new page is created and half of the data on the original page is moved to the new page before the new data is to be inserted. i.e. if the data to be inserted will make the page exceed it’s 8KB (kilobytes) of data, the page will split.

What is a page fault and how can you monitor it?

A page fault is an exception that is thrown when an application accesses a piece of memory that has been temporarily moved out to disk. When a page fault occurs, the system has to move that memory back into the system RAM so that the application can continue. A large number of page faults are at indication that the system is running out of system memory, resulting in the system paging out large amounts of memory to disk. To monitor it, you can use PerfMon (performance monitor) to view the page fault system counters. You can also use TaskMan (Task Manager) to get a summary of system page faults.

If you did a full backup on Sunday, a differential backup on Monday, Tuesday, and Wednesday, and then Thursday you lost your SQL server, what would you have to restore and in what order to get back to the most recent point in time?

You would have to restore Sundays full backup followed by restoring the most recent differential, which was Wednesdays. If you were doing any transaction log backups between differentials, you would restore all transaction log backups since your most recent differential.

Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.
Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.
Use table variables instead of temporary tables.
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.
Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.
Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.
Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated sub-query or derived tables, if you need to perform row-by-row operations.
Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.
If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.
Because SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take very many time for the large table. There is another way to determine the total row count in a table. You can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*): SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So, you can improve the speed of such queries in several times.
Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.
Try to restrict the queries result set by using the WHERE clause.
This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.
Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.
Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

I have Two Stored Procedures SP1 and SP2 as given below. How the Transaction works, whether SP2 Transaction succeeds or fails?

CREATE PROCEDURE SP1 AS
BEGIN TRAN
INSERT INTO MARKS (SID,MARK,CID) VALUES (5,6,3)
EXEC SP2
ROLLBACK
GO

CREATE PROCEDURE SP2 AS
BEGIN TRAN
INSERT INTO MARKS (SID,MARK,CID) VALUES (100,100,103)
commit tran
GO
Both will get roll backed.

CREATE PROCEDURE SP1 AS
BEGIN TRAN
    INSERT INTO MARKS (SID,MARK,CID) VALUES (5,6,3)
    BEGIN TRAN
        INSERT INTO STUDENT (SID,NAME1) VALUES (1,'SA')

commit tran
ROLLBACK TRAN
GO
Both will get roll backed.

How will you handle Errors in Sql Stored Procedure?

INSERT NonFatal VALUES (@Column2)
IF @@ERROR <>0
BEGIN
PRINT 'Error Occured'
END

Note

There are two type of errors in SQL Server: fatal and non-fatal. Fatal errors cause a procedure to abort processing and terminate the connection with the client application. Non-fatal errors do not abort processing a procedure or affect the connection with the client application. When a non-fatal error occurs within a procedure, processing continues on the line of code that follows the one that caused the error

The @@ERROR system function is used to implement error handling code

http://www.sqlteam.com/article/handling-errors-in-stored-procedures

How will you raise an error in sql?
RAISERROR - Returns a user-defined error message and sets a system flag to record that an error has occurred. Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. After the message is defined it is sent back to the client as a server error message.

The RAISERROR statement is used to produce an ad hoc error message or to retrieve a custom message that is stored in the sysmessages table

I have a stored procedure like
commit tran
create table a()
insert into table b
--
--
rollback tran
what will be the result? Is table created? data will be inserted in table b?
What do you do when one procedure is blocking the other?
**
How you will return XML from Stored Procedure?
You use the FOR XML clause of the SELECT statement, and within the FOR XML clause you specify an XML mode: RAW, AUTO, or EXPLICIT.
What are the differences between RAW, AUTO and Explicit modes in retrieving data from SQL Server in XML format?
**
Can a Stored Procedure call itself (recursive). If so then up to what level and can it be control?
Stored procedures are nested when one stored procedure calls another. You can nest stored procedures up to 32 levels. The nesting level increases by one when the called stored procedure begins execution and decreases by one when the called stored procedure completes execution. Attempting to exceed the maximum of 32 levels of nesting causes the whole calling stored procedure chain to fail. The current nesting level for the stored procedures in execution is stored in the @@NESTLEVEL function.
eg:
SET NOCOUNT ON
USE master
IF OBJECT_ID('dbo.sp_calcfactorial') IS NOT NULL
DROP PROC dbo.sp_calcfactorial
GO
CREATE PROC dbo.sp_calcfactorial
@base_number int, @factorial int OUT
AS
DECLARE @previous_number int
IF (@base_number<2) SET @factorial=1 -- Factorial of 0 or 1=1
ELSE BEGIN
SET @previous_number=@base_number-1
EXEC dbo.sp_calcfactorial @previous_number, @factorial OUT -- Recursive call
IF (@factorial=-1) RETURN(-1) -- Got an error, return
SET @factorial=@factorial*@base_number
END
RETURN(0)
GO

calling proc.
DECLARE @factorial int
EXEC dbo.sp_calcfactorial 4, @factorial OUT
SELECT @factorial
Nested Triggers
Triggers are nested when a trigger performs an action that initiates another trigger, which can initiate another trigger, and so on. Triggers can be nested up to 32 levels, and you can control whether triggers can be nested through the nested triggers server configuration option.
What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.

Difference between view and stored procedure?

Views can have only select statements (create, update, truncate, delete statements are not allowed)
Views cannot have “select into”, “Group by” “Having”, ”Order by”

What is a Function & what are the different user defined functions?
Function is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.

Scalar Functions
Functions are scalar-valued if the RETURNS clause specified one of the scalar data types
Inline Table-valued Functions
If the RETURNS clause specifies TABLE with no accompanying column list, the function is an inline function.
Multi-statement Table-valued Functions
If the RETURNS clause specifies a TABLE type with columns and their data types, the function is a multi-statement table-valued function.

Temporary Table vs Table Variable

Table variable cannot use a table variable as an input or an output parameter.
scoped to the stored procedure, batch, or user-defined function
The variable will no longer exist after the procedure exits
a table variable will generally use fewer resources than a temporary table.
Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.

Syntax

    DECLARE @ProductTotals TABLE 

(

    ProductID int,  Revenue money

)

    INSERT INTO @ProductTotals (ProductID,
 Revenue)

    SELECT ProductID, SUM(UnitPrice * Quantity)

   
 FROM [Order Details]

   
 GROUP BY ProductID

What are the difference between a function and a stored procedure?

Functions can be used in a select statement where as procedures cannot
Procedure takes both input and output parameters but Functions takes only input parameters
Functions cannot return values of type text, ntext, image & timestamps where as procedures can
Functions can be used as user defined datatypes in create table but procedures cannot
***Eg:-create table <tablename>(name varchar(10),salary getsal(name))
Here getsal is a user defined function which returns a salary type, when table is created no storage is allotted for salary type, and getsal function is also not executed, But when we are fetching some values from this table, getsal function get’s executed and the return
Type is returned as the result set.

How to debug a stored procedure?

What are statistics, under what circumstances they go out of date, how do you update them?
Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.
Some situations under which you should update statistics:

If there is significant change in the key values in the index
If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
Database is upgraded from a previous version

What is fillfactor? What is the use of it ? What happens when we ignore it? When you should use low fill factor?

When you create a clustered index, the data in the table is stored in the data pages of the database according to the order of the values in the indexed columns. When new rows of data are inserted into the table or the values in the indexed columns are changed, Microsoft® SQL Server™ 2000 may have to reorganize the storage of the data in the table to make room for the new row and maintain the ordered storage of the data. This also applies to nonclustered indexes. When data is added or changed, SQL Server may have to reorganize the storage of the data in the nonclustered index pages. When a new row is added to a full index page, SQL Server moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. Page splitting can impair performance and fragment the storage of the data in a table.

When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table's data and reduce the potential for page splits. The fill factor value is a percentage from 0 to 100 that specifies how much to fill the data pages after the index is created. A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table.

For more interview questions please visit <http://vyaskn.tripod.com/iq.htm

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row processing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors. Here is an example:

If you have to give a flat hike to your employees using the following criteria:

Salary between 30000 and 40000 -- 5000 hike

Salary between 40000 and 55000 -- 7000 hike

Salary between 55000 and 65000 -- 9000 hike

In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =

CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000

WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000

WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000

END

Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You 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. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.

What is trigger in sql server?

A trigger is a special procedure, associated with a table, that is executed automatically when anything changes that table. You can designate a trigger to fire when one or more rows in the table are updated, deleted, inserted, or any/all of those. You can define more than one trigger for a given table.

While the trigger is executing, it has access to special in-memory pseudo-tables, called [inserted] and [deleted]. In an UPDATE trigger, the [deleted] table contains a copy of the row[s] as they were before the update, and the [inserted] table contains a copy of the same row[s] after the update. In a DELETE trigger, the [inserted] table is empty, and the [deleted] table contains a copy of the row[s] that were deleted. In an INSERT trigger... you get the idea.

For an example, suppose you wanted to log each time a new row is added, or an existing row is changed, for a table called [MainTable], including who made the change, and when:

CREATE TABLE [MainTable] (

id int IDENTITY(1,1) PRIMARY KEY NOT NULL,

field1 int NULL,

field2 int NULL

)

CREATE TABLE [LogTable] (

id int IDENTITY(1,1) PRIMARY KEY NOT NULL,

MainTableID int NOT NULL,

ChangedBy varchar(128) NULL,

ChangeDate datetime DEFAULT (getdate()) NOT NULL

)

CREATE TRIGGER tr_Log_MainTable_Changes

ON dbo.MainTable

FOR UPDATE, INSERT

set nocount on

INSERT INTO [LogTable] (MainTableID, ChangedBy)

SELECT id, suser_sname() As ChangedBy

FROM [inserted]

-- Now to test it, run the following statements:

insert into [MainTable] (Field1, Field2) values (2, 3)

insert into [MainTable] (Field1, Field2) values (4, 5)

update [MainTable] set Field1 = 7, Field2 = 8

select * from [LogTable] -- should return 4 rows

What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

We first design the table as reflexive r/ship and take the primary key and put it as forign key in the same table..

CREATE
 TABLE emp 

(

empid
 int,

mgrid
 int,

empname
 char(10)

)

INSERT
 INTO emp SELECT 1,2,'Vyas'

INSERT
 INTO emp SELECT 2,3,'Mohan'

INSERT
 INTO emp SELECT 3,NULL,'Shobha'

INSERT
 INTO emp SELECT 4,2,'Shridhar'

INSERT
 INTO emp SELECT 5,2,'Sourabh'

SELECT
 t1.empname [Employee], t2.empname [Manager]

FROM
 emp t1, emp t2

WHERE
 t1.mgrid = t2.empid

Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)

SELECT
 t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]

FROM
 emp t1 

LEFT
 OUTER JOIN

emp
 t2

ON

t1.mgrid = t2.empid

Given an employee table, how would you find out the second highest salary?

SELECT MAX( Salary )

FROM Employee  

WHERE salary NOT IN 

     ( SELECT  TOP 1 
 Salary

      FROM
 Employee 

      ORDER
 BY Salary 

      DESC )

Generaly To find out the nth highest number in a column

SELECT MAX( @column_name )

FROM @table_name  

WHERE @column_name NOT IN 

                   ( SELECT TOP
 (@nth - 1)  @column_name

     FROM
 @table_name 

        
 ORDER BY @column_name 

         DESC
 )

How to find 6th highest salary

SELECT TOP 1 salary

FROM (SELECT DISTINCT TOP 6 salary

FROM employee

ORDER BY salary DESC) a

ORDER BY salary

Write a SQL Query to find first day of month

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE())))

In what version of SQL Server were synonyms released, what do synonyms do and when could you make the case for using them?

Synonyms enable the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment. In short, this means that the original object that is referenced in all of your code is really using a completely different underlying object, but no coding changes are necessary. Think of this as an alias as a means to simplify migrations and application testing without the need to make any dependent coding changes.

Synonyms can offer a great deal of value when converting underlying database objects without breaking front end or middle tier code. This could be useful during a re-architecture or upgrade project.

How can you delete duplicate records in a table with no primary key?

Use the SET ROWCOUNT command. So if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.

How can you capture the length of a column when it is a Text, NText and/or Image data type?
Use the DATALENGTH command to capture the length
The LEN command is invalid for Text, NText and Image data types

What is the native system stored procedure to issue a command against all databases?
EXEC sp_MSforeachdb @command

Is it possible to import data directly from T-SQL commands without using DTS or SSIS? If so, what are the commands?

Yes - Six commands are available to import data directly in the T-SQL language. These commands include:

BCP
Bulk Insert
OpenRowSet
OPENDATASOURCE
OPENQUERY
Linked Servers

Refer for detail http://www.mssqltips.com/tip.asp?tip=1207

Please name 5 commands that can be used to manipulate text in T-SQL code. For example, obtain only a portion of the text, replace a text string, etc

Command	Description
CHARINDEX( findTextData, textData, [startingPosition] )	Returns the starting position of the specified expression in a character string. The starting position is optional.
LEFT( character_expression , integer_expression )	Returns the left part of a character string with the specified number of characters.
LEN( textData )	Returns integer value of the length of the string, excluding trailing blanks
LOWER ( character_expression )	Returns a character expression after converting uppercase character data to lowercase
LTRIM( textData)	Removes leading blanks
PATINDEX( findTextData, textData )	Returns integer value of the starting position of text found in the string
REPLACE( textData, findTextData, replaceWithTextData )	Replaces occurrences of text found in the string with a new value
REPLICATE( character_expression , integer_expression )	Repeats a character expression for a specified number of times.
REVERSE( character_expression )	Returns the reverse of a character expression.
RTRIM( textData)	Removes trailing blanks
SPACE( numberOfSpaces )	Repeats space value specified number of times
STUFF( textData, start , length , insertTextData )	Deletes a specified length of characters and inserts another set of characters at a specified starting point
SUBSTRING( textData, startPosition, length )	Returns portion of the string
UPPER( character_expression )	Returns a character expression with lowercase character data converted to uppercase.

What are the three ways that Dynamic SQL can be issued?

Writing a query with parameters.
Using EXEC.
Using sp_executesql

10 tips for sorting, grouping, and summarizing SQL data

#1: Bring order with a sort

More often than not, all your data really needs is a little order. SQL's ORDER BY clause organises data in alphabetic or numeric order. Consequently, similar values sort together in what appear to be groups. However, the apparent groups are a result of the sort; they aren't true groups. ORDER BY displays each record whereas a group may represent multiple records.

#2: Reduce similar values into a group

The biggest difference between sorting and grouping is this: Sorted data displays all the records (within the confines of any limiting criteria) and grouped data doesn't. The GROUP BY clause reduces similar values into one record. For instance, a GROUP BY clause can return a unique list of ZIP codes from a source that repeats those values:

SELECT ZIP FROM Customers GROUP BY ZIP

Include only those columns that define the group in both the GROUP BY and SELECT column lists. In other words, the SELECT list must match the GROUP BY list, with one exception: The SELECT list can include aggregate functions. (GROUP BY doesn't allow aggregate functions.)

Keep in mind that GROUP BY won't sort the resulting groups. To arrange groups alphabetically or numerically, add an ORDER BY clause (# 1). In addition, you can't refer to an aliased field in the GROUP BY clause. Group columns must be in the underlying data, but they don't have to appear in the results.

#3: Limit data before it's grouped

You can limit the data that GROUP BY groups by adding a WHERE clause. For instance, the following statement returns a unique list of ZIP codes for just Kentucky customers:

SELECT ZIP FROM Customers WHERE State = 'KY' GROUP BY ZIP

It's important to remember that WHERE filters data before the GROUP BY clause evaluates it.

Like GROUP BY, WHERE doesn't support aggregate functions.

#4: Return all groups

When you use WHERE to filter data, the resulting groups display only those records you specify. Data that fits the group's definition but does not meet the clause's conditions won't make it to a group. Include ALL when you want to include all data, regardless of the WHERE condition. For instance, adding ALL to the previous statement returns all of the ZIP groups, not just those in Kentucky:

SELECT ZIP FROM Customers WHERE State = 'KY' GROUP BY ALL ZIP

As is, the two clauses are in conflict, and you probably wouldn't use ALL in this way. ALL comes in handy when you use an aggregate to evaluate a column. For example, the following statement counts the number of customers in each Kentucky ZIP, while also displaying other ZIP values:

SELECT ZIP, Count(ZIP) AS KYCustomersByZIP FROM Customers WHERE State = 'KY' GROUP BY ALL ZIP

The resulting groups comprise all ZIP values in the underlying data. However, the aggregate column (KYCustomersByZIP) would display 0 for any group other than a Kentucky ZIP.

Remote queries don't support GROUP BY ALL.

#5: Limit data after it's grouped

The WHERE clause (# 3) evaluates data before the GROUP BY clause does. When you want to limit data after it's grouped, use HAVING. Often, the result will be the same whether you use WHERE or HAVING, but it's important to remember that the clauses are not interchangeable. Here's a good guideline to follow when you're in doubt: Use WHERE to filter records; use HAVING to filter groups.

Usually, you'll use HAVING to evaluate a group using an aggregate. For instance, the following statement returns a unique list of ZIP codes, but the list might not include every ZIP code in the underlying data source:

SELECT ZIP, Count(ZIP) AS CustomersByZIP FROM Customers GROUP BY ZIP HAVING Count(ZIP) = 1

Only those groups with just one customer make it to the results.

#6: Get a closer look at WHERE and HAVING

If you're still confused about when to use WHERE and when to use HAVING, apply the following guidelines:

WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records.
HAVING comes after GROUP BY; SQL evaluates HAVING after it groups records.

#7: Summarize grouped values with aggregates

Grouping data can help you analyse your data, but sometimes you'll need a bit more information than just the groups themselves. You can add an aggregate function to summarise grouped data. For instance, the following statement displays a subtotal for each order:

SELECT OrderID, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY OrderID

As with any other group, the SELECT and GROUP BY lists must match. Including an aggregate in the SELECT clause is the only exception to this rule.

#8: Summarise the aggregate

You can further summarize data by displaying a subtotal for each group. SQL's ROLLUP operator displays an extra record, a subtotal, for each group. That record is the result of evaluating all the records within each group using an aggregate function. The following statement totals the OrderTotal column for each group:

SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH ROLLUP

The ROLLUP row for a group with two OrderTotal values of 20 and 25 would display an OrderTotal of 45. The first record in a ROLLUP result is unique because it evaluates all of the group records. That value is a grand total for the entire recordset.

ROLLUP doesn't support DISTINCT in aggregate functions or the GROUP BY ALL clause.

#9: Summarise each column

The CUBE operator goes a step further than ROLLUP by returning totals for each value in each group. The results are similar to ROLLUP, but CUBE includes an additional record for each column in the group. The following statement displays a subtotal for each group and an additional total for each customer:

SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE

CUBE gives the most comprehensive summarization. It not only does the work of both the aggregate and ROLLUP, but also evaluates the other columns that define the group. In other words, CUBE summarises every possible column combination.

CUBE doesn't support GROUP BY ALL.

#10: Bring order to summaries

When the results of a CUBE are confusing (and they usually are), add the GROUPING function as follows:

SELECT GROUPING(Customer), OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE

The results include two additional values for each row:

The value 1 indicates that the value to the left is a summary value--the result of the ROLLUP or CUBE operator.
The value 0 indicates that the value to the left is a detail record produced by the original GROUP BY clause.

Design a product table, many kinds of product, each product has many parameters

You have at least these five options for modeling the type hierarchy you describe:

Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types.

Advantages

Single table inheritance mapping is the fastest of all inheritance models, since it never requires a join to retrieve a persistent instance from the database. Similarly, persisting or updating a persistent instance requires only a single INSERT or UPDATE statement.

Disadvantages

The larger the inheritance model gets, the "wider" the mapped table gets, in that for every field in the entire inheritance hierarchy, a column must exist in the mapped table. This may have undesirable consequence on the database size, since a wide or deep inheritance hierarchy will result in tables with many mostly-empty columns.

Class Table Inheritance: one table for Products, storing attributes common to all product types. Then one table per product type, storing attributes specific to that product type.

Advantages

The Class Table strategy has the following advantages:

Using Class Table inheritance results in the most normalized database schema, meaning the schema with the least spurious or redundant data.
As more subclasses are added to the data model over time, the only schema modification that needs to be made is the addition of corresponding subclass tables in the database (rather than having to change the structure of existing tables).
Relations to a base class using this strategy can be loaded through standard joins and can use standard foreign keys, as opposed to the machinations required to load polymorphic relations to table-per-class base types, described below.

Disadvantages

Aside from certain uses of the concrete table strategy described below, the class table inheritance strategy is often the slowest of the inheritance models. Retrieving any subclass requires one or more database joins, and storing subclasses requires multiple INSERT or UPDATE statements. This is only the case when persistence operations are performed on subclasses; if most operations are performed on the least-derived persistent superclass, then this mapping is very fast.

Concrete Table Inheritance: no table for common Products attributes. Instead, one table per product type, storing both common product attributes, and product-specific attributes.

Advantages

it is very efficient when operating on instances of a known class. Under these conditions, the strategy never requires joining to superclass or subclass tables. Reads, joins, inserts, updates, and deletes are all efficient in the absence of polymorphic behavior. Also, as in the class table inheritance strategy, adding additional classes to the hierarchy does not require modifying existing class tables.

Disadvantages

Polymorphic relations to non-leaf classes in a hierarchy have many limitations. When the concrete subclass is not known, the related object could be in any of the subclass tables, making joins through the relation impossible. This ambiguity also affects identity lookups and queries; these operations require multiple SQL SELECTs (one for each possible subclass), or a complex UNION.

With Check Option

This clause is very important because it prevents changes that do not meet the view's criteria.

OOP-I

What are the OOPS concepts?

Encapsulation: It is the mechanism that binds together code and data it manipulates, and keeps both safe from outside interference and misuse. In short it isolates a particular code and data from all other codes and data. A well-defined interface controls the access to that particular code and data. It is an object oriented principle that describes the ability of an object to hide its data and methods from the rest of the world

Inheritance: It is the process by which one object acquires the properties of another object. This supports the hierarchical classification. Without the use of hierarchies, each object would need to define all its characteristics explicitly. However, by use of inheritance, an object need only define those qualities that make it unique within its class. It can inherit its general attributes from its parent. A new sub-class inherits all of the attributes of all of its ancestors.

Polymorphism: It is a feature that allows one interface to be used for general class of actions. The specific action is determined by the exact nature of the situation. In general polymorphism means "one interface, multiple methods", This means that it is possible to design a generic interface to a group of related activities. This helps reduce complexity by allowing the same interface to be used to specify a general class of action. It is the compiler's job to select the specific action (that is, method) as it applies to each situation. The ability at runtime to determine the correct function to call based on the type of the instance, not the type of the base class pointer that is pointing to that instance.

What’s the difference between Struct and class in C#?

Struct	Class
Value Type	Reference type
Doesn’t support inheritance: A struct cannot inherit from another struct or class, and it cannot be the base of a class. Structs, however, inherit from the base class Object. A struct can implement interfaces, and it does that exactly as classes do	Support inheritance
Lightweight: The struct type is suitable for representing lightweight objects such as Point, Rectangle, and Color. Although it is possible to represent a point as a class, a struct is more efficient in some scenarios. For example, if you declare an array of 1000 Point objects, you will allocate additional memory for referencing each object. In this case, the struct is less expensive.
All of its members declared as public by default.	All of its members declared as private by default
Stored in the stack	Stored in the heap
Can be instantiated with or without the new keyword. When you create a struct object using the new operator, it gets created and the appropriate constructor is called. Unlike classes, structs can be instantiated without using the new operator. If you do not use new, the fields will remain unassigned and the object cannot be used until all of the fields are initialized.	Can only be instantiated with the new keyword.
It is an error to declare a default (parameterless) constructor.	Parameter less (default) constructor is possible.
It is an error to initialize an instance(non-static) field in a struct.	Initializing instance field is not an error.

Value type & reference types difference?

Example from .NET Integer & struct are value types or reference types in .NET?
Most programming languages provide built-in data types, such as integers and floating-point numbers, that are copied when they are passed as arguments (that is, they are passed by value). In the .NET Framework, these are called value types. The runtime supports two kinds of value types:

Built-in value types
The .NET Framework defines built-in value types, such as System.Int32 and System.Boolean, which correspond and are identical to primitive data types used by programming languages.
User-defined value types
Your language will provide ways to define your own value types, which derive from System.ValueType. If you want to define a type representing a value that is small, such as a complex number (using two floating-point numbers), you might choose to define it as a value type because you can pass the value type efficiently by value. If the type you are defining would be more efficiently passed by reference, you should define it as a class instead.

Variables of reference types, referred to as objects, store references to the actual data. This following are the reference types:

class 2. interface 3.delegate

This following are the built-in reference types:

object 2. String

What is Method overloading?
Method overloading occurs when a class contains two methods with the same name, but different signatures.

What is Method Overriding? How to override a function in C#?
Use the override modifier to modify a method, a property, an indexer, or an event. An override method provides a new implementation of a member inherited from a base class. The method overridden by an override declaration is known as the overridden base method. The overridden base method must have the same signature as the override method.
You cannot override a non-virtual or static method. The overridden base method must be virtual, abstract, or override.

Can we call a base class method without creating instance?
Its possible If its a static method.
Its possible by inheriting from that class also.
Its possible from derived classes using base keyword.

You have one base class virtual function how will call that function from derived class?
Ans:

class a

{

public virtual int m()

{

return 1;

}

class b : a

{

public int j()

{

return m();

}

In which cases you use override and new base?

Use the new modifier to explicitly hide a member inherited from a base class. To hide an inherited member, declare it in the derived class using the same name, and modify it with the new modifier.

C# Language features

What are Sealed Classes in C#?
The sealed modifier is used to prevent derivation from a class. A compile-time error occurs if a sealed class is specified as the base class of another class. (A sealed class cannot also be an abstract class)

What is Polymorphism? How does VB.NET/C# achieve polymorphism?

class Token

{

public string Display()

{

//Implementation goes here

return "base";

}

class IdentifierToken : Token

{

public new string Display() //1. What is the use of new keyword

{

//Implementation goes here

return "derive";

}

static void Method(Token t)

{

Console.Write(t.Display());

}

public static void Main()

{

IdentifierToken Variable = new IdentifierToken();

Method(Variable); //2. Which Class Method is called here

Console.ReadLine();

}

For the above code What is the "new" keyword and Which Class Method is called here

Answer:

The new key word hides the base class's method
It will call derived class's Method

In which Scenario you will go for Interface or Abstract Class?

Interfaces, like classes, define a set of properties, methods, and events. But unlike classes, interfaces do not provide implementation. They are implemented by classes, and defined as separate entities from classes. Even though class inheritance allows your classes to inherit implementation from a base class, it also forces you to make most of your design decisions when the class is first published.

Abstract classes are useful when creating components because they allow you specify an invariant level of functionality in some methods, but leave the implementation of other methods until a specific implementation of that class is needed. They also version well, because if additional functionality is needed in derived classes, it can be added to the base class without breaking code.

Feature	Interface	Abstract class
Default implementation	An interface cannot provide any code at all, much less default code.	An abstract class can provide complete code, default code, and/or just stubs that have to be overridden.
Constants	Static final constants only, can use them without qualification in classes that implement the interface. On the other paw, these unqualified names pollute the namespace. You can use them and it is not obvious where they are coming from since the qualification is optional.	Both instance and static constants are possible. Both static and instance initializer code are also possible to compute the constants.
Third party convenience	An interface implementation may be added to any existing third party class.	A third party class must be rewritten to extend only from the abstract class (because of multiple inheritance problem).
is-a versus -able or can-do	Interfaces are often used to describe the peripheral abilities of a class, not its central identity, e.g. an Automobile class might implement the Recyclable interface, which could apply to many otherwise totally unrelated objects.	An abstract class defines the core identity of its descendants. If you defined a Dog abstract class then Damamation descendants are Dogs, they are not merely dogable. Implemented interfaces enumerate the general things a class can do, not the things a class is.
Plug-in	You can write a new replacement module for an interface that contains not one stick of code in common with the existing implementations. When you implement the interface, you start from scratch without any default implementation. You have to obtain your tools from other classes; nothing comes with the interface other than a few constants. This gives you freedom to implement a radically different internal design.	You must use the abstract class as-is for the code base, with all its attendant baggage, good or bad. The abstract class author has imposed structure on you. Depending on the cleverness of the author of the abstract class, this may be good or bad. Another issue that's important is what I call
Heterogeneous versus. Homogeneous.	If all the various implementations share is the method signatures, then an interface works best.	If the various implementations are all of a kind and share a common status and behavior, usually an abstract class works best.
Maintenance	If your client code talks only in terms of an interface, you can easily change the concrete implementation behind it, using a factory method.	Just like an interface, if your client code talks only in terms of an abstract class, you can easily change the concrete implementation behind it, using a factory method.
Speed	Slow, requires extra indirection to find the corresponding method in the actual class.	Fast
Terseness	The constant declarations in an interface are all presumed public static final, so you may leave that part out. You can't call any methods to compute the initial values of your constants. You need not declare individual methods of an interface abstract. They are all presumed so.	You can put shared code into an abstract class, where you cannot into an interface. If interfaces want to share code, you will have to write other bubblegum to arrange that. You may use methods to compute the initial values of your constants and variables, both instance and static. You must declare all the individual methods of an abstract class abstract.
Adding functionality	If you add a new method to an interface, you must track down all implementations of that interface in the universe and provide them with a concrete implementation of that method.	If you add a new method to an abstract class, you have the option of providing a default implementation of it. Then all existing code will continue to work without change.

Write one code example for compile time binding and one for run time binding? What is early/late binding?

An object is early bound when it is assigned to a variable declared to be of a specific object type. Early bound objects allow the compiler to allocate memory and perform other optimizations before an application executes.

' Create a variable to hold a new object.
Dim FS As FileStream

' Assign a new object to the variable.
FS = New FileStream("C:\tmp.txt", FileMode.Open)

In contrast, an object is late bound when it is assigned to a variable declared to be of type Object. Objects of this type can hold references to any object, but lack many of the advantages of early-bound objects.
Dim xlApp As Object
xlApp = CreateObject("Excel.Application")

How can you write a class to restrict that only one object of this class can be created (Singleton class)?

public class Singlton

{

private static Singlton instance = null;

private static object mylock = new object();

public static Singlton Instance

{

get

{

lock (mylock)

{

if (instance == null)

{

instance = new Singlton();

}

return instance;

}

What are the access-specifiers available in c#?
Private, Protected, Public, Internal, Protected Internal.

Explain about Protected and protected internal, “internal” access-specifier?

protected - Access is limited to the containing class or types derived from the containing class.

internal - Access is limited to the current assembly.

protected internal - Access is limited to the current assembly or types derived from the containing class.

Difference between type constructor (static constructor) and instance constructor? When it will be fired? And what is its use?

Instance constructor is executed when a new instance of type is created.

Class constructor method is also known as type constructor or type initializer. The class constructor is executed after the type is loaded and before any one of the type members is accessed. It will get executed only 1st time, when we call any static methods/fields in the same class.

A static constructor is used to initialize a class. It is called automatically to initialize the class before the first instance is created or any static members are referenced.

Class constructors are used for static field initialization. Only one class constructor per type is permitted, and it cannot use the vararg (variable argument) calling convention.

What is Private Constructor? and it’s use? Can you create instance of a class which has Private Constructor?
A: When a class declares only private instance constructors, it is not possible for classes outside the program to derive from the class or to directly create instances of it. (Except Nested classes)
Make a constructor private if:

You want it to be available only to the class itself. For example, you might have a special constructor used only in the implementation of your class' Clone method.

You do not want instances of your component to be created. For example, you may have a class containing nothing but Shared utility functions, and no instance data. Creating instances of the class would waste memory.

I have 3 overloaded constructors in my class. In order to avoid making instance of the class do I need to make all constructors to private?
(yes)

Overloaded constructor will call default constructor internally?
(no)

What are virtual destructors?

Destructor and finalize
Generally in C++ the destructor is called when objects gets destroyed. And one can explicitly call the destructors in C++. And also the objects are destroyed in reverse order that they are created in. So in C++ you have control over the destructors.

In C# you can never call them, the reason is one cannot destroy an object. So who has the control over the destructor (in C#)? it's the .Net frameworks Garbage Collector (GC). GC destroys the objects only when necessary. Some situations of necessity are memory is exhausted or user explicitly calls System.GC.Collect() method.

Points to remember:

1. Destructors are invoked automatically, and cannot be invoked explicitly.
2. Destructors cannot be overloaded. Thus, a class can have, at most, one destructor.
3. Destructors are not inherited. Thus, a class has no destructors other than the one, which may be declared in it.
4. Destructors cannot be used with structs. They are only used with classes.
5. An instance becomes eligible for destruction when it is no longer possible for any code to use the instance.
6. Execution of the destructor for the instance may occur at any time after the instance becomes eligible for destruction.
7. When an instance is destructed, the destructors in its inheritance chain are called, in order, from most derived to least derived.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconfinalizemethodscdestructors.asp

What is the difference between Finalize and Dispose (Garbage collection)
Class instances often encapsulate control over resources that are not managed by the runtime, such as window handles (HWND), database connections, and so on. Therefore, you should provide both an explicit and an implicit way to free those resources. Provide implicit control by implementing the protected Finalize Method on an object (destructor syntax in C# and the Managed Extensions for C++). The garbage collector calls this method at some point after there are no longer any valid references to the object.
In some cases, you might want to provide programmers using an object with the ability to explicitly release these external resources before the garbage collector frees the object. If an external resource is scarce or expensive, better performance can be achieved if the programmer explicitly releases resources when they are no longer being used. To provide explicit control, implement the Dispose method provided by the IDisposable Interface. The consumer of the object should call this method when it is done using the object. Dispose can be called even if other references to the object are alive.
Note that even when you provide explicit control by way of Dispose, you should provide implicit cleanup using the Finalize method. Finalize provides a backup to prevent resources from permanently leaking if the programmer fails to call Dispose.
What is close method? How its different from Finalize & Dispose?
**
What is boxing & unboxing?
What is check/uncheck?
What is the use of base keyword? Tell me a practical example for base keyword’s usage?
What are the different .net tools which u used in projects?

try
{
...
}
catch
{
...//exception occurred here. What'll happen?
}
finally
{
..
}
Answer : It will throw exception.

What will do to avoid prior case?
Answer:

try

{

try

{

}

catch

{

//exception occurred here.

}

finally

{

}

catch

{

}

finally

{

}

try

{

}

catch

{

}

finally

{

}

Will it go to finally block if there is no exception happened?
Answer: Yes. The finally block is useful for cleaning up any resources allocated in the try block. Control is always passed to the finally block regardless of how the try block exits.

Is goto statement supported in C#? How about Java?
Goto's are supported in C# to the fullest. In Java goto is a reserved keyword that provides absolutely no functionality.

What’s different about switch statements in C#?

No fall-throughs allowed. Unlike the C++ switch statement, C# does not support an explicit fall through from one case label to another. If you want, you can use goto a switch-case, or goto default.

case 1:

cost += 25;

break;

case 2:

cost += 25;

goto case 1;

OOP-II

How does the Object Oriented approach improve software development?

The key benefits are:

Re-use of previous work: using implementation inheritance and object composition. Real mapping to the problem domain:
Objects map to real world and represent vehicles, customers, products etc: with encapsulation.
Modular Architecture: Objects, systems, frameworks etc are the building blocks of larger systems.

What are the OOPS concepts?

Enforces security and integrity

Polymorphism: It is a feature that allows one interface to be used for general class of actions.

means the ability of a single variable of a given type to be used to reference objects of different types, and automatically call the method that is specific to the type of object the variable references
The benefit of polymorphism is that it is very easy to add new classes of derived objects without breaking the calling code (gives us the ultimate flexibility in extensibility)

Polymorphism exists in three distinct forms:

• Method overloading

• Method overriding through inheritance

• Method overriding through the interface

Inheritance: It is the process by which one object acquires the properties of another object. Inheritance allows well-tested procedures to be reused and enables changes to make once and have effect in all relevant places

There are two types of inheritances
Implementation inheritance (aka class inheritance):

copy attributes and operations defined in a super class into its subclass. We only add new attributes and operations specific to the sub class.
allows a superclass and a subclass to share identical code for the same method.

Disadvantage breaking encapsulation and making future changes a problem (the subclass becomes tightly coupled with the superclass).

Interface inheritance (aka type inheritance):

Interface inheritance: inherit only the signature defined in an abstract operation. We prepare the different implementation of method in each concrete sub class. And we invoke them with the same signature.
promotes the design concept of coding to an interface and reduces coupling.

is
   a [House is a Building]

class
   Building{

.......

}

 class House :Building{

  .........

}

has
   a [House has a Bathroom]

class
   House {

Bathroom
   room = new Bathroom() ;

....

public
   void getTotMirrors(){

room.getNoMirrors();

....

}

What is an interface?

It defines the methods and functions that the inheriting class must implement. (C#)
An interface is an agreement between a client and an object about how they will communicate with each other.

What is Difference Between Interface and abstract Class

Interface :

1.Interface have only Signature.

2.All the Methods are Public , It doesn't have access Modifier Controls

3.It have used Multiple inheritance in the Object oriented Language

4.All the Methods are Abstract.

5.It does not have Constructor, destructor, Fields

6.A Class May inherits several Interfaces

Abstract Class: If we need for a system to implement a default behavior and forces subclasses to provide any specific behavior then we can enforce this feature by defining abstract/ base class

1.Abstract Class have Method definition and Implementation

2.It have control the Access Modifiers

3.It does not allow multiple Inheritance

4.Some methods are concrete

5. It have Constructor and destructor

6.Only one abstract have to derived

How do you express an ‘is a’ relationship and a ‘has a’ relationship or explain inheritance and composition?

Two of the main techniques for code reuse are class inheritance and object composition.
The ‘is a’ relationship is expressed with inheritance and ‘has a’ relationship is expressed with composition.

Composition simply means using instance variables that refer to other objects

What is the difference between composition and aggregation?

Both are an association in which one class belongs to a collection.

Aggregation

This is a part of a whole relationship where a part can exist without a whole.
For example a line item is a whole and product is a part. If a line item is deleted then corresponding product need not be deleted. So aggregation has a weaker relationship.

Composition

This is a part of a whole relationship where a part cannot exist without a whole. If a whole is deleted then all parts are deleted.
For example an order is a whole and line items are parts. If an order is deleted then all corresponding line items for that order should be deleted. So composition has a stronger relationship.

Which one to favor, composition or inheritance?

The guide is that inheritance should be only used when subclass ‘is a’ superclass.

In which Scenario you will go for Interface or Abstract Class?

When to use an abstract class?:

In case where you want to use implementation inheritance
Abstract classes are excellent candidates inside of application frameworks. Abstract classes let you define some default behavior and force subclasses to provide any specific behavior.

When to use an interface?:

If you need to change your design frequently, you should prefer using interface to abstract.
For polymorphic interface inheritance, where the client wants to only deal with a type and does not care about the actual implementation use interfaces.
Coding to an interface reduces coupling and interface inheritance can achieve code reuse with the help of object composition

What is the difference between a Struct and a Class?

The struct type is suitable for representing lightweight objects such as Point, Rectangle, and Color. Although it is possible to represent a point as a class, a struct is more efficient in some scenarios. For example, if you declare an array of 1000 Point objects, you will allocate additional memory for referencing each object. In this case, the struct is less expensive.
When you create a struct object using the new operator, it gets created and the appropriate constructor is called. Unlike classes, structs can be instantiated without using the new operator. If you do not use new, the fields will remain unassigned and the object cannot be used until all of the fields are initialized.
It is an error to declare a default (parameterless) constructor for a struct. A default constructor is always provided to initialize the struct members to their default values.
It is an error to initialize an instance field in a struct.
There is no inheritance for structs as there is for classes. A struct cannot inherit from another struct or class, and it cannot be the base of a class. Structs, however, inherit from the base class Object. A struct can implement interfaces, and it does that exactly as classes do.
A struct is a value type, while a class is a reference type.

What is an interface?

An interface defines a contract stating that any class that inherits the interface guarantees that all functions defined in that interface will be implemented. It’s a class that has all of its functions declared as pure virtual, i.e. all functions are set equal to 0. (C++)
An interface is an agreement between a client and an object about how they will communicate with each other. It defines the methods and functions that the inheriting class must implement. (C#)

Value type & reference types difference? Example from .NET. Integer & struct are value types or reference types in .NET?

A data type is a value type if it holds the data within its own memory allocation. A reference type contains a pointer to another memory location that holds the data.

Most programming languages provide built-in data types, such as integers and floating-point numbers, that are copied when they are passed as arguments (that is, they are passed by value). In the .NET Framework, these are called value types. The runtime supports two kinds of value types:

Built-in value types
The .NET Framework defines built-in value types, such as System.Int32 and System.Boolean, which correspond and are identical to primitive data types used by programming languages.
User-defined value types
Your language will provide ways to define your own value types, which derive from System.ValueType. If you want to define a type representing a value that is small, such as a complex number (using two floating-point numbers), you might choose to define it as a value type because you can pass the value type efficiently by value. If the type you are defining would be more efficiently passed by reference, you should define it as a class instead.

Variables of reference types, referred to as objects, store references to the actual data. This following are the reference types:

class
interface
delegate

This following are the built-in reference types:

object
string

What is Inheritance, Multiple Inheritance, Shared and Repeatable Inheritance?
**
What is Method overloading?
Method overloading occurs when a class contains two methods with the same name, but different signatures.

Overloading lets you define the same operation in different ways for different data.

What is Method Overriding? How to override a function in C#?
Use the override modifier to modify a method, a property, an indexer, or an event. An override method provides a new implementation of a member inherited from a base class. The method overridden by an override declaration is known as the overridden base method. The overridden base method must have the same signature as the override method.
You cannot override a non-virtual or static method. The overridden base method must be virtual, abstract, or override.
Can we call a base class method without creating instance?
Its possible If its a static method.
Its possible by inheriting from that class also.
Its possible from derived classes using base keyword.

You have one base class virtual function how will call that function from derived class?
Ans: base.MyMethod();

What is close method? How its different from Finalize & Dispose?

Garbage collector:

garbage collector is responsible to allocate and de-allocate the memory space for managed resources. although garbage collector is able to track the lifetime of an object that encapsulates an unmanaged resources ,but it does not have specific knowledge about how to clean up the unmanaged resources. So to clean up the unmanaged resources we need to use Dispose() or Finalize().

Close():

when a Close() method is called, any managed resource can be temporarily closed and can be opened once again. It means that, with the same object the resource can be reopened or used

Dispose():

Dispose method is explicitly called by the programmer to free up unmanaged resources, like file handles, database collections. The class implementing Dispose method should implement IDisposable Interface.

Finalize():

Finalize method is implicitly called by the garbage collector to clean up the unmanaged resources by the garbage collector. Finalize method can automatically called by the Garbage collector. But Dispose method can be called by using custom code written by the programmer. To implement the Finalize method we need to use Destructor Syntax.(~)tilde symbol.

Note

Finalize() corresponds to the .Net Framework and is part of the System.Object class. Destructors are C#'s implementation of the Finalize() method

What’s the concept of Threadpool in .NET

Threadpool is included in .Net to be widely used in enterprise applications. In multithreaded .Net applications, creating a thread each time we need one, and then destroying it after finishing is expensive. Using thread pool , and instead of creating a new thread whenever you need one, your application will obtain a thread from the thread pool. After that obtained thread completes its task, it will be returned to the thread pool instead of destroying it - waiting for the next task. This reusability increases the overall application performance, and reduces the cost of excessive thread creation and termination.

1. Describe the difference between a Thread and a Process?

A thread is the basic unit to which the operating system allocates processor time. A process is an executing application that consists of one or more threads.

2. What is the difference between an EXE and a DLL?

EXE indicates an executable file that can be run directly and usually is an application starting point having main method, however DLL is a standalone and reusable file which provides a reference to an exe file at runtime.

3. What is a Windows Service and how does its lifecycle differ from a "standard" EXE

Is an application designed to perform specific functions that do not require user intervention. Windows service can run as background process as long as a window is running and can be configured to start and stop manually or automatic.

4. What is polymorphism?

means the ability of a single variable of a given type to be used to reference objects of different types, and automatically call the method that is specific to the type of object the variable references

5. What’s a critical section? What mechanisms are available in C# to implement a critical section?

Critical section is a piece of code that access a shared resource and that must not be concurrently accessed by more than one thread of execution. In C# we can use ‘Lock’ keyword to hold and release critical section access

6. Familiarity with Software Patterns: Singleton/Abstract/Factory

Singleton

If a system only needs one instance of a class, and that instance needs to be accessible in many different parts of a system, we control both instantiation and access by making that class a singleton.

Factory

If a system need to access objects of the same base type and manipulate them mostly as abstract objects, then we need a factory. A Factory pattern is used to creates objects without exposing the instantiation logic to the client and helps to refer to the newly created object through a common interface

Service

A service application conforms to the interface rules of the Service Control Manager (SCM). It can be started automatically at system boot, by a user through the Services control panel applet, or by an application that uses the service functions

MVC- Model–view–controller, an architectural pattern used in software development

an architecture where all requests to a site are routed via a Controller program to the appropriate (template) View on the data and business Model. These 3 aspects are kept separate (decoupled) with defined interfaces/APIs describing their interaction. ...

In which cases you use override and new base?
Use the new modifier to explicitly hide a member inherited from a base class. To hide an inherited member, declare it in the derived class using the same name, and modify it with the new modifier.

C# Language features

What are Sealed Classes in C#?
The sealed modifier is used to prevent derivation from a class. A compile-time error occurs if a sealed class is specified as the base class of another class. (A sealed class cannot also be an abstract class)

What is Polymorphism? How does VB.NET/C# achieve polymorphism?
**
Write one code example for compile time binding and one for run time binding? What is early/late binding?
An object is early bound when it is assigned to a variable declared to be of a specific object type. Early bound objects allow the compiler to allocate memory and perform other optimizations before an application executes.
' Create a variable to hold a new object.
Dim FS As FileStream
' Assign a new object to the variable.
FS = New FileStream("C:\tmp.txt", FileMode.Open)
By contrast, an object is late bound when it is assigned to a variable declared to be of type Object. Objects of this type can hold references to any object, but lack many of the advantages of early-bound objects.
Dim xlApp As Object
xlApp = CreateObject("Excel.Application")
Can you explain what inheritance is and an example of when you might use it?
How can you write a class to restrict that only one object of this class can be created (Singleton class)?

(Access specifiers)

What are the access-specifiers available in c#?
Private, Protected, Public, Internal, Protected Internal.
Explain about Protected and protected internal, “internal” access-specifier?
protected - Access is limited to the containing class or types derived from the containing class.
internal - Access is limited to the current assembly.
protected internal - Access is limited to the current assembly or types derived from the containing class.

(Constructor / Destructor)
Difference between type constructor and instance constructor? What is static constructor, when it will be fired? And what is its use?
(Class constructor method is also known as type constructor or type initializer)
Instance constructor is executed when a new instance of type is created and the class constructor is executed after the type is loaded and before any one of the type members is accessed. (It will get executed only 1st time, when we call any static methods/fields in the same class.) Class constructors are used for static field initialization. Only one class constructor per type is permitted, and it cannot use the vararg (variable argument) calling convention.
A static constructor is used to initialize a class. It is called automatically to initialize the class before the first instance is created or any static members are referenced.
What is Private Constructor? and it’s use? Can you create instance of a class which has Private Constructor?
A: When a class declares only private instance constructors, it is not possible for classes outside the program to derive from the class or to directly create instances of it. (Except Nested classes)
Make a constructor private if:
- You want it to be available only to the class itself. For example, you might have a special constructor used only in the implementation of your class' Clone method.
- You do not want instances of your component to be created. For example, you may have a class containing nothing but Shared utility functions, and no instance data. Creating instances of the class would waste memory.
I have 3 overloaded constructors in my class. In order to avoid making instance of the class do I need to make all constructors to private?
(yes)
Overloaded constructor will call default constructor internally?
(no)
What are virtual destructors?
Destructor and finalize
Generally in C++ the destructor is called when objects gets destroyed. And one can explicitly call the destructors in C++. And also the objects are destroyed in reverse order that they are created in. So in C++ you have control over the destructors.
In C# you can never call them, the reason is one cannot destroy an object. So who has the control over the destructor (in C#)? it's the .Net frameworks Garbage Collector (GC). GC destroys the objects only when necessary. Some situations of necessity are memory is exhausted or user explicitly calls System.GC.Collect() method.
Points to remember:

Destructors are invoked automatically, and cannot be invoked explicitly.
Destructors cannot be overloaded. Thus, a class can have, at most, one destructor.
Destructors are not inherited. Thus, a class has no destructors other than the one, which may be declared in it.
Destructors cannot be used with structs. They are only used with classes.
5. An instance becomes eligible for destruction when it is no longer possible for any code to use the instance.
6. Execution of the destructor for the instance may occur at any time after the instance becomes eligible for destruction.
7. When an instance is destructed, the destructors in its inheritance chain are called, in order, from most derived to least derived.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconfinalizemethodscdestructors.asp

What is the difference between Finalize and Dispose (Garbage collection)
Class instances often encapsulate control over resources that are not managed by the runtime/ garbage collector, such as window handles (HWND), database connections, and so on. Therefore, you should provide both an explicit and an implicit way to free those resources. Provide implicit control by implementing the protected Finalize Method on an object (destructor syntax in C# and the Managed Extensions for C++). The garbage collector calls this method at some point after there are no longer any valid references to the object.
In some cases, you might want to provide programmers using an object with the ability to explicitly release these external resources before the garbage collector frees the object. If an external resource is scarce or expensive, better performance can be achieved if the programmer explicitly releases resources when they are no longer being used. To provide explicit control, implement the Dispose method provided by the IDisposable Interface. The consumer of the object should call this method when it is done using the object. Dispose can be called even if other references to the object are alive.
Note that even when you provide explicit control by way of Dispose, you should provide implicit cleanup using the Finalize method. Finalize provides a backup to prevent resources from permanently leaking if the programmer fails to call Dispose.

What is boxing & unboxing?

The conversion of value type to reference type is known as boxing and converting reference type back to the value type is known as unboxing.

What is check/uncheck?
What is the use of base keyword? Tell me a practical example for base keyword’s usage?
What are the different .net tools which u used in projects?
try
{
...
}
catch
{
...//exception occurred here. What'll happen?
}
finally
{
..
}

Ans : It will throw exception.

What will do to avoid prior case?
Ans:

Will it go to finally block if there is no exception happened?
Ans: Yes. The finally block is useful for cleaning up any resources allocated in the try block. Control is always passed to the finally block regardless of how the try block exits.

Is goto statement supported in C#? How about Java?
Gotos are supported in C#to the fullest. In Java goto is a reserved keyword that provides absolutely no functionality.
What’s different about switch statements in C#?

No fall-throughs allowed. Unlike the C++ switch statement, C# does not support an explicit fall through from one case label to another. If you want, you can use goto a switch-case, or goto default.

case 1:

cost += 25;

break;

case 2:

cost += 25;

goto case 1;

Welcome To My Blog !Have a nice day :)

Pages

Sql&OOP Tips

SQL and Database Specific

OOP-I

No comments:

Post a Comment