Monday, April 27, 2009

SQL

What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

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

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

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

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

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

What is the difference between clustered and a non-clustered index?
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 nonclustered 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 node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the different index configurations a table can have?
A table can have one of the following index configurations:

No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes

What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor

What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.
DBCC CHECKALLOC - To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What are different type of Collation Sensitivity?
Case sensitivity
A and a, B and b, etc.

Accent sensitivity
a and á, o and ó, etc.

Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.




What is a NOLOCK?
Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What types of Joins are possible with Sql Server?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.

What is sub-query? Explain properties of sub-query.
Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

Properties of Sub-Query
A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

What are types of sub-queries?
Single-row subquery, where the subquery returns only one row.
Multiple-row subquery, where the subquery returns multiple rows,.and
Multiple column subquery, where the subquery returns multiple columns.


What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is User Defined Functions?
User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

What kind of User-Defined Functions can be created?
There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

Scalar User-Defined Function
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

Inline Table-Value User-Defined Function
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number.both on client and the server.

What are the authentication modes in SQL Server? How can it be changed?
Windows mode and mixed mode (SQL & Windows).

To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Where are SQL server users names and passwords are stored in sql server?
They get stored in master db in the sysxlogins table.

Which command using Query Analyzer will give you the version of SQL server and operating system?
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
What is SQL server agent?
SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror?
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

What is log shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

What is the difference between a local and a global variable?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What command do we use to rename a db?
sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

What is sp_configure commands and set commands?
Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:

Transactional
Snapshot
Merge
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

What are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

What are three SQL keywords used to change or set someone’s permissions?
GRANT, DENY, and REVOKE.

What does it mean to have quoted_identifier on? What are the implications of having it off?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

What is the STUFF function and how does it differ from the REPLACE function?
STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurance. Using this syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
SELECT *
FROM table1
SELECT COUNT(*)
FROM table1
SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID(table1)
AND indid < 2
How to rebuild Master Database?
Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In the Rebuild Master dialog box, click Browse.
In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.
To continue, you may need to stop a server that is running.
Source: http://msdn2.microsoft.com/en-us/library/aa197950(SQL.80).aspx

What is the basic functions for master, msdb, model, tempdb databases?
The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.

What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is data integrity? Explain constraints?
Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What are the properties of the Relational tables?
Relational tables have six properties:

Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.
What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

How to get @@error and @@rowcount at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset.
And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers, the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that.

What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

How do you load large data to the SQL server database?
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

Can we rewrite subqueries into simple select statements or with joins?
Subqueries can often be re-written to use a standard outer join, resulting in faster performance. As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values. Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.

Can SQL Servers linked to other servers like Oracle?
SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

How to know which index a table is using?
SELECT table_name,index_name FROM user_constraints

How to copy the tables, schema and views from one SQL server to another?
Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple destinations.

What is Self Join?
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.

What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Which virtual table does a trigger use?
Inserted and Deleted.

List few advantages of Stored Procedure.

Stored procedure can reduced network traffic and latency, boosting application performance.
Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
Stored procedures help promote code reuse.
Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
Stored procedures provide better security to your data.
What is DataWarehousing?

Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting;
Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.
What is OLTP(OnLine Transaction Processing)?
In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

How do SQL server 2000 and XML linked? Can XML be used to access data?
FOR XML (ROW, AUTO, EXPLICIT)
You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve XML results, use the FOR XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.

OPENXML
OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.

What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

Monday, April 20, 2009

DOT NET

Using a delegate allows the programmer to encapsulate a reference to a method inside a delegate object.

The delegate object can then be passed to code which can call the referenced method,
without having to know at compile time which method will be invoked.



Delegates have the following properties:
· Also know as custom events.
· Delegates allow methods to be passed as parameters.
· Delegates can be used to define callback methods.
· Delegates can be chained together; for example, multiple methods can be called on a single event.

Example:

public delegate void SimpleDelegate();
public delegate void ButtonClickHandler(int obj1, int obj2);

class Program
{
static void Main(string[] args)
{
A a = new A();
SimpleDelegate sd = new SimpleDelegate(a.Sum);
ButtonClickHandler handler = new ButtonClickHandler(a.Sum);
}
}

public class A
{
public A() { }
int i;
float f;
public void Sum() { }
public void Sum(int x,int y){}
}
Delegates & Events very well explained at:


Nice explanation about Events in C# --


2. What is the use of private constructor - It will not allow the instantiation of the object (new)

A private constructor is a special instance constructor.
It is commonly used in classes that contain static members only.
If a class has one or more private constructors and no public constructors, then other classes (except nested classes) are not allowed to create instances of this class.
The declaration of the empty constructor prevents the automatic generation of a default constructor.
Note that if you don't use an access modifier with the constructor it will still be private by default.
However, the private modifier is usually used explicitly to make it clear that the class cannot be instantiated.
Private constructors are useful to prevent creation of a class when there are no instance fields or methods, such as the Math class , or when a method is called to obtain an instance of a class.


3. What is Aggregation and Composition

4. How is caching done

Cache Application Programming Interfaces (APIs) allow you to programmatically store arbitrary objects to memory
so that your application can save the time and resources that it takes to re-create them.
Cache APIs allow you to expire items from the cache based on the following credentials:
· Time
· File dependencies
· Cache key dependencies

ASP.NET has a powerful, easy-to-use caching mechanism that allows you to store objects that require a
large amount of server resources to create in memory.

It is implemented by the Cache class,
with instances private to each application, and its lifetime is tied to that of the application.
When the application is restarted, the Cache object is recreated.

The Cache class has been designed for ease of use.
By using keys paired with values, you can place items in the Cache and later retrieve them.

While the Cache class offers a simple interface for you to customize cache settings,
it also offers powerful features that allow you to customize how items are cached and how long they are cached.
For example, when system memory becomes scarce, the cache automatically removes seldom used or unimportant items to
allow memory to be used to process a high volume of requests. This technique is called scavenging.

Scavenging is one of the ways that the cache ensures that data that is not current does not consume valuable
server resources.

You can instruct the Cache to give certain items priority over other items when it performs scavenging.
To indicate that a specific item is of greater or lesser importance than another, specify one of the
CacheItemPriority
enumeration values when you add an item using the Cache.Add method or Cache.Insert method.

You can also establish an expiration policy for an item when you add it to the Cache using the Add method or Insert method.

You can define the lifetime for an item by using the absoluteExpiration parameter, which is of the type DateTime
and allows you to specify the exact time the
item will expire. You can also use the slidingExpiration parameter, which is of the type TimeSpan
.
It allows you to specify the elapsed time
before the item expires based on the time it is accessed. Once the item expires, it is removed from the cache.
Attempts to retrieve its value will return null unless the item is added to the Cache again.

For volatile items that are stored in the Cache, such as those that have regular data refreshes,
or those that are valid for only a set amount of time, set an expiration policy that keeps those
items in the Cache as long as their data remains current. For example, if you are writing an application
that tracks sports scores by obtaining the data from a frequently updated Web site, you can cache the scores
for a game as long as those scores do not change on the source Web site. In this case, you can set an expiration
policy that is based on how often the Web site updates the scores.

You can write code that determines if an up-to-date score is in the Cache. If the score is not up to date,
the code can update the score from the source Web site.

Finally, ASP.NET allows you to define the validity of a cached item, based on an external file, a directory,
or another cached item. These are called file dependencies and key dependencies.

If a dependency changes, the cached item is invalidated and removed from the Cache.
You can use this technique to remove items from the Cache when their data source changes.
For example, if you write an application that processes financial data from an XML file
and renders it in a graph, you can insert the data from the file in the Cache and maintain
a dependency on that XML file. When the file is updated, the item is removed from the cache,
your application rereads the file, and a new version of the item is inserted.
Note The Cache has no information about the content of the items it contains.
It merely holds a reference to those objects. It also provides methods to track their
dependencies and set expiration policies.


5. What is the purpose of global.asax file

The Global.asax file (also known as the ASP.NET application file) is an optional file that is located in the
application's root directory and is the ASP.NET counterpart of the Global.asa of ASP.
This file exposes the application and session level events in ASP.NET and provides a gateway to
all the application and the session level events in ASP.NET.
This file can be used to implement the important application and session level events such as
Application_Start, Application_End, Session_Start, Session_End, etc.
This article provides an overview of the Global.asax file, the events stored in this file and how
we can perform application wide tasks with the help of this file.

è For in-depth reading
Global.asax is an optional file used to declare and handle application- and session-level events and objects
The Global.asax file, also known as the ASP.NET application file, is an optional file that contains code for
responding to application-level events raised by ASP.NET or by HttpModules.
The Global.asax file resides in the root directory of an ASP.NET-based application.
At run time, Global.asax is parsed and compiled into a dynamically generated .NET Framework class derived
from the HttpApplication base class.
The Global.asax file itself is configured so that any direct URL request for it is automatically rejected;
external users cannot download or view the code written within it.

The Global.asax file is optional.
If you do not define the file, the ASP.NET page framework assumes that you have
not defined any application or session event handlers.

When you save changes to an active Global.asax file, the ASP.NET page framework detects that the file
has been changed. It completes all current requests for the application, sends the Application_OnEnd
event to any
listeners, and restarts the application domain.
In effect, this reboots the application, closing all browser sessions and flushing all state information.
When the next incoming request from a browser arrives, the ASP.NET page framework reparses and recompiles
the Global.asax file and raises the Application_OnStart
event.


6. What are the # of pages/forms you have developed in your career (strange question)

7. What is the purpose of sealed keyword
A sealed class cannot be used as a base class. For this reason, it cannot also be an abstract class.
Sealed classes are primarily used to prevent derivation.
Because they can never be used as a base class, some run-time optimizations can make calling sealed class
members slightly faster.
Sealing a class means one cannot derive from it.
Sealing a method means one cannot override it.
In C# structs are implicitly sealed; therefore, they cannot be inherited.
If we try to inherit from a sealed class in another class we will get compile time error about Inconsistent accessibility

public sealed class cSealed
{
// Class members here.
public string ID;
public double Price;
}

The most likely situation in which we make a class or method sealed will be if the class or method is internal to the operation of the library, class, or other classes that we are writing. Because any attempt to override some of its functionality will cause problems. We can mark a class or method as sealed for commercial reasons, in order to prevent a third party from extending our classes. For example, in the .NET base class library string is a sealed class.

We should not use the sealed key word on a method unless that method is itself an override of another method in some base class. If we are defining a new method and we don’t want anyone else to override it, we should not declare it as virtual in the first place. If however, we have overridden a base class method, the sealed keyword provides a way of ensuring that the override supplied to a method is a “final” override that means no one else can override it again.

8. Have you used application blocks? What is the application blocks used.

With a view to promote enterprise development, enhance developer productivity, reduce development time, facilitate efficient code maintenance and rapid application development , the Patterns and Practices group from Microsoft has introduced a collection of extensible and configurable application blocks that are commonly known as the Enterprise Library Application Blocks. These blocks are actually a collection of some reusable code snippets and code libraries that encapsulate some of the best coding and design strategies that are recommended by Microsoft. The basic features provided by each of these blocks are:
è Simplicity and ease of use
è Extensibility as the source code for each of these blocks are available for free
è Integration with other blocks
è Consistency

9. What are web services

Web Services provide a simple technique for accessing a method on an object that is running on a local or remote computer. Web Services can be accessed by an application written in any language and running on any operating system . They utilize HTTP as the underlying transport, which allows function requests to pass through corporate firewalls (as they move through port 80). In ASP.NET we can write a Web Service as simple as a normal business object and the only difference is that the functions are preceded with a special attribute that makes them as Web Services.

Synchronous & Asynchronous communication
Web Services support both synchronous and asynchronous communication between the client and the server that hosts the Web Service. Under synchronous communication, the client sends a request to the server and waits for the response. This prevents the client from performing other operations while waiting for the results. On the other hand, in asynchronous communication, the client continues processing other tasks as it waits for a response. The client responds to the result of the service request when it becomes available.
Common Terminology used in Web Services
SOAP Simple Object Access Protocol (SOAP) is a way to structure data so that any computer program in any language can read SOAP and send messages in SOAP. This is because it is XML based and language and platform independent. SOAP supports any transport protocol and use Hyper Text Transfer Protocol (HTTP), File Transfer Protocol (FTP), Simple Mail Transfer Protocol (SMTP) and Post Office Protocol 3 (POP3) to carry documents.
Web Services Description Language (WSDL)
Web Services Description Language (WSDL) is an open standard language used in conjunction with XML-based languages. A WSDL file is an XML document that describes a set of SOAP messages and how the messages are exchanged.
Universal Description, Discovery, and Integration (UDDI)
Universal Description, Discovery, and Integration (UDDI) is an open, Internet-based specification that is the building block on which businesses may quickly and easily locate desired services and perform business with one another.
DISCO
Abbreviation of DISCO is Discovery.It is basically used to club or group common services together on a server and provide links to the schema documents of the services it describes may require.
Transport Protocol for Web Service
Unless we specify otherwise, .NET will attempt to bind Web Services to three separate protocols: HTTP/POST, HTTP/GET, and SOAP. Bindings for these three protocols will be included in the WSDL file automatically generated by .NET and consumer clients will have the option of choosing any one of them for communication with service. However, GET and POST are limited to sending and receiving name/value pairs of data whereas we can use SOAP to serialize complex structure, such as ASP.NET DataSets, complex arrays, custom types and XML nodes.
We can easily remove these bindings by adding the followings as specified in Listing 1 to our web.config file as:
Listing 1






This section will tell the WSDL generator not to include bindings for HTTP/POST and HTTP/GET. The two remove sections specify that HttpPost and HttpGet should not be supported. With regard to interoperability where SOAP is a widely-used standard for Web Service communication, HTTP/GET and HTTP/POST are not. As a result, many automatic proxy generation tools were not designed to understand the HTTP/GET and HTTP/POST bindings included by default in a .NET-generated WSDL document. If our service does not make use of these bindings, removing them can increase our service's interoperability.
The Web Service Attribute
The Web Service Attribute is a member of the System.Web.Services namespace. This we can use to let the clients know where to find information about a Web Service. There are three properties of the Web Service attribute. They are as follows: 1. Description 2. Namespace 3. Name Description The Description property is used to provide a brief description of the functionality of the class. We can write the same as specified in Listing 2. Listing 2 [WebService(Description="This class contains methods for working with Addition of numbers and Population of DataSet")] Namespace The Namespace property sets the XML namespace for the service. Generally we use an URL. It does not have to be an actual URL; it can be any string value. The idea is that it should be unique. It is common practice to use URL because a URL is always unique. Name When the WSDL is generated for an ASP.NET Web Service, the name of the class is used for the service name within the WSDL. When a proxy uses the WSDL and builds a proxy class, the name of the class generated corresponds to the name value of service. This property allows overriding the default value.
Extension - .asmx

In .NET’s Remoting technology we require the Server and the Client both to be of the same technology; in other words, they should both be .NET applications and require the .NET framework to be running in both of these environments. This is in contrast to Web Services where a typical Web Service implemented in .NET can even be invoked for Java.

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// Description ………………
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
public Service () { InitializeComponent(); }
[WebMethod]
public string Add(int a, int b)
{
return Convert.ToString(a + b);
}

[WebMethod]
public DataSet Populate(string con, string sql)
{
DataSet DS = new DataSet();
SqlConnection conn = new SqlConnection(con);
SqlDataAdapter DA = new SqlDataAdapter(sql, conn);
DA.Fill(DS);
return DS;
}

}

10. What is the difference between String and String Builder

The String object is immutable. Every time you use one of the methods in the System.String class, you create a new string object in memory, which requires a new allocation of space for that new object. In situations where you need to perform repeated modifications to a string, the overhead associated with creating a new String object can be costly. The System.Text.StringBuilder class can be used when you want to modify a string without creating a new object. For example, using the StringBuilder class can boost performance when concatenating many strings together in a loop.

StringBuilder myStringBuilder = new StringBuilder ( "Hello World!" );




11. What will happen if a class is implementing multiple interfaces which have same methods?
It’s up to you on how you implement the method inside your own class. It may cause problems on higher scale, if similarly named methods from different interfaces expect different data, but as far as compiler is concerned, it’s okay.

12. What is an abstract class?
An abstract class only allows other classes to inherit from it and cannot be instantiated. When we create an abstract class, it should have one or more completed methods but at least one or more uncompleted methods and these must be preceded by the key word abstract. If all the methods of an abstract class are uncompleted then it is the same as an interface, but there is a restriction that it cannot make a class inherit from it, which means it cannot work as a base class.

13. What is an interface?

An interface is defined by the key word interface. An interface has no implementation; it only has the definition of the methods without the body. When we create an interface, we are basically creating a set of methods without any implementation. A class implementing an interface must provide the implementation of the interface members. All the methods and properties defined in an interface are by default public and abstract.

14. Explain when to choose one over the other (interface and abstract class).

è Abstract classes can add more functionality without destroying the child classes that were using the old version. Abstract classes provide a simple and easy way to version our components. By updating the base class, all inheriting classes are automatically updated with the change. In an interface, creation of additional functions will have an effect on its child classes due to the necessary implementation of interface Methods in classes.
è Abstract classes should be used primarily for objects that are closely related, whereas interfaces are best suited for providing common functionality to unrelated classes. Say there are two classes, bird and airplane, and both of them have methods called fly. It would be ridiculous for an airplane to inherit from the bird class just because it has the fly() method. Rather, the fly() method should be defined as an interface and both bird and airplane should implement that interface. If we want to provide common, implemented functionality among all implementations of component, we should use an abstract class.
è Abstract classes allow us to partially implement classes, whereas interfaces contain no implementation for any members. So the selection of interface or abstract classes depends on the needs and design of our project. We can make an abstract class, interface, or combination of both depending on our needs.

15. Explain briefly Garbage collection in .Net?

Garbage Collection is a technique introduced in Microsoft .NET that manages memory automatically and to free unused objects that go out of the scope automatically.

A "garbage" object is one that is no longer needed, is unreachable from the root or goes out of the scope in which it is created. Microsoft .NET uses the information in the metadata to trace the object graph and detect the objects that need to be garbage collected. Objects that are not reachable from the root are referred to as garbage objects and are marked for garbage collection. It is to be noted here that there is a time gap between the time when an object is identified as garbage and the time when the object is actually collected. It is also to be noted that objects in the managed heap are stored in sequential memory locations. This is unlike C and C++ and makes allocation and de-allocation of objects faster.
Strong and Weak References
The garbage collector can reclaim only objects that have no references. An object that is reachable cannot be garbage collected by the garbage collector. Such a reference is known as a strong reference.
An object can also be referred to as a weak reference; another term for a weak reference is the target. An object is eligible for garbage collection if it does not contain any strong references, irrespective of the number of weak references it contains. Weak references are of the following types:
è Short Weak Reference
è Long Weak Reference
A short weak reference does not track resurrection while a long weak reference tracks resurrection. The primary advantage of maintaining weak references to an object is that it allows the garbage collector to collect or reclaim memory of the object if it runs out of memory in the managed heap.

16. Can you explicitly call garbage collection?
Yes - by using the System.GC class
The System.GC class represents the garbage collector and contains many of methods and properties that are described in this section.
GC.Collect Method
This method is used to force a garbage collection of all the generations. It can also force a garbage collection of a particular generation passed to it as a parameter. The signatures of the overloaded Collect methods are:

public static void Collect();
public static void Collect(Integer int);

Generations
A generational garbage collector collects the short-lived objects more frequently than the longer lived ones. Short-lived objects are stored in the first generation, generation 0. The longer-lived objects are pushed into the higher generations, 1 or 2. The garbage collector works more frequently in the lower generations than in the higher ones.
When an object is first created, it is put into generation 0. When the generation 0 is filled up, the garbage collector is invoked. The objects that survive the garbage collection in the first generation are promoted onto the next higher generation, generation 1. The objects that survive garbage collection in generation 1 are promoted onto the next and the highest generation, generation 2. This algorithm works efficiently for garbage collection of objects, as it is fast. Note that generation 2 is the highest generation that is supported by the garbage collector.

Garbage Collection is one of the most striking features introduced in Microsoft .NET. It is however, not advisable to implement the finalize method unless it is mandatory. An object that has the finalize method implemented has to undergo two generations before it gets garbage collected from the managed heap. Hence, it slows down the operations.


17. What is the difference between public, internal and protected modifiers?

Access modifiers are keywords used to specify the declared accessibility of a member or a type. This section introduces the four access modifiers:
· public
· protected
· internal
· private
The following five accessibility levels can be specified using the access modifiers:
public protected internal internal protected private

public - The public keyword is an access modifier for types and type members. Public access is the most permissive access level. There are no restrictions on accessing public members.

The protected keyword is a member access modifier. A protected member is accessible from within the class in which it is declared, and from within any class derived from the class that declared this member.
A protected member of a base class is accessible in a derived class only if the access takes place through the derived class type.

The internal keyword is an access modifier for types and type members. Internal members are accessible only within files in the same assembly. For more information on assemblies, see Components and Assemblies .
A common use of internal access is in component-based development because it enables a group of components to cooperate in a private manner without being exposed to the rest of the application code. For example, a framework for building graphical user interfaces could provide Control and Form classes that cooperate using members with internal access. Since these members are internal, they are not exposed to code that is using the framework.
It is an error to reference a member with internal access outside the assembly within which it was defined.
Caution An internal virtual method can be overridden in some languages, such as textual Microsoft intermediate language (MSIL) using Ilasm.exe, even though it cannot be overridden using C#.

The private keyword is a member access modifier. Private access is the least permissive access level. Private members are accessible only within the body of the class or the struct in which they are declared.
Nested types in the same body can also access those private members.
It is a compile-time error to reference a private member outside the class or the struct in which it is declared.

Declared accessibility Meaning
public Access is not restricted.
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.
private Access is limited to the containing type.
Only one access modifier is allowed for a member or type, except for the protected internal combination.


18. What are value and reference types?

Value types directly contain their data are either allocated on the stack or allocated in-line in a structure. Reference types store a reference to the value's memory address, and are allocated on the heap. Reference types can be self-describing types, pointer types, or interface types. Variables that are value types each have their own copy of the data, and therefore operations on one variable do not affect other variables. Variables that are reference types can refer to the same object; therefore, operations on one variable can affect the same object referred to by another variable. All types derive from the System.Object base type.

19. What is boxing/unboxing?
Boxing permits any value type to be implicitly converted to type object or to any interface type implemented by value type.Boxing is process in which a object instances created and copying value types value in to that instance. Unboxing is vice versa of boxing operation where the value is copied from the instance in to appropriate storage location. Below is sample code of boxing and unboxing where integer data type is converted in to object and then vice versa.

Dim x As Integer
Dim y As
Object x = 10
‘ boxing process
y = x
‘ unboxing process
x = y

20. State the advantages n disadvantages of Array & ArrayList

ArrayList - Implements the IList interface using an array whose size is dynamically increased as required.
ArrayList is: a datatype collection. In order to fill an ArrayList, one can use the .Add property. ArrayLists are very dynamic in the sense that when you add and/or remove items from it, the performace stays the same.
The internal structure of an ArrayList is an array.
Examples:
o ArrayList myArray = new ArrayList();
o myArray .Add(“Steph”);
o string str = myArray [0];

Array is: a datatype, thatcan be used by calling indexes. during runtime, one cannot really change the size of the array, unless you use the method of copying the array and getting rid of the old one.
In .NET, the Visual Studio makes use of a special class to store the data. Because of this, the performance is actually quite fast. This is also because in an array, you need to specify the size and thus, the data is stored one after the other.
Examples:
o int[ ] myNumbers= new int[5];
o myNumbers[0] = 16;
Most of the time, we tend to choose array lists rather than arrays since we have no idea how big it is going to turn out. Arrays are ideal when you know how many items you are going to put in it. Whenever possible, it is recommended to use arrays as this drastically improves the performance.


21. Do we need to manually deallocate memory in .Net (Garbage Collection) (GC.Collect(), GC.SuppressFinalize())

There are two types of allocation used - Managed and unmanaged memory.
If the application is using Managed memory, then there is no need to manually de-allocate the memory, but if its using unmanaged memory allocation (for ex. Files, stream etc.) Then we should manually de-allocate the memory

22. Namespace used by generics? About Generics . . .

using System.Collections.Generic;

class Program
{
static void Main(string[] args)
{
A a = new A();
B b = (B)a;
GenericsDemo gd = new GenericsDemo();
int x = gd.sum(5,6);
SimpleDelegate sd = new SimpleDelegate(a.Sum);
ButtonClickHandler handler = new ButtonClickHandler(a.Sum);
}
}

public class GenericsDemo
{
T i;
T f;

public T sum(T t1, T t2)
{
return t1;
}
}

Very well explained in msdn:
http://msdn.microsoft.com/en-us/library/ms379564.aspx#csharp_generics_topic4

Generics are a new concept that has been introduced with C# 2.0 and it helps us to defer the binding of a generic type to a data type until its point of usage arrives. It is one of the most powerful and anticipated feature of the C# 2.0 language. The basic idea behind generics is to develop universal classes and methods that can accept a type as a parameter to promote reusability, efficiency and maintainability of code.
Generics introduce to the .NET Framework the concept of type parameters, which make it possible to design classes and methods that defer the specification of one or more types until the class or method is declared and instantiated by client code.

Generics facilitate type safety, improved performance and reduced code. It promotes the usage of parameterized types on our types and is also known as parametric polymorphism. The Common Language Runtime (CLR) compiles any Generic type to IL and Metadata as it does with the other types; but it stores added information pertaining to the generic types which is used to bind the generic type to a specific type at runtime when the generic type is instantiated. Note that for generic types that are bound to value types, the generic types are instantiated for each value type that it is bound to. Unlike this, for generic types that are bond to reference types, the generic type instance refers to the location in memory of the reference type to which it is bound for all the instances of the generic type.

23. Does C# support multi level inheritance? ---- Yes

24. What happens if a class has private constructor?

A private constructor is one that prevents the creation of the object of the class. It is commonly used in classes that contain only static members.

A constructor is a member function that has the same name as the class name and is invoked automatically when the class is instantiated. A constructor is used to provide initialization code that gets executed every time the class is instantiated.
Points to be noted on constructors:
· Constructors cannot be "virtual".
· They cannot be inherited.
· Constructors are called in the order of inheritance.
· If we don't write any constructor for a class, C# provides an implicit default constructor, i.e., a constructor with no argument.
· Constructors cannot return any value.
· Constructors can be overloaded.
C# supports two types of constructor, a class constructor (static constructor) and an instance constructor (non-static constructor).
Static constructor is used to initialize static data members as soon as the class is referenced first time, whereas an instance constructor is used to create an instance of that class with keyword. A static constructor does not take access modifiers or have parameters and can't access any non-static data member of a class.
Since static constructor is a class constructor, they are guaranteed to be called as soon as we refer to that class or by creating an instance of that class.
Non-static constructors can be public, private, protected, external, or internal. A public constructor is one that is called when the class is instantiated. A private constructor is one that prevents the creation of the object of the class. It is commonly used in classes that contain only static members. A class containing an internal constructor cannot be instantiated outside of the assembly. An internal constructor can be used to limit concrete implementations of the abstract class to the assembly defining the class. A protected constructor allows the base class to do its own initialization when subtypes are created. When constructor declaration includes an extern modifier, the constructor is said to be an external constructor. An external constructor declaration provides no actual implementation and hence does not contain any definition. It is to be noted that a public constructor can access a private constructor of the same class through constructor chaining.


27. What is encapsulation? In a class, we have a variable called varIlist, and some method is returning this variable, do you think is it breaking the Encapsulation?

Encapsulation is a property of OOPS that combines the data and the members of a class inside a unit resulting in an isolation of the unit from the external world. It exposes only the functional details, but hides the implementation details of the class.
The benefits of encapsulation:
· Modularity
· Information-hiding


28. What is interface inheritance?
Inheritance is a feature by virtue of which a child class inherits its parent. A subclass inherits all the members of its base, except those that are private. A sub class may have one or more base classes, i.e. it can be inherited from one or more bases. Such type of inheritance is known as Multiple Inheritance. A sub class can also extend the behavior of its inherited members and add new members. Here it needs to be mentioned that there are two relationships in OOPS- "has-a" and "is-a." The former indicates a composition, while the later indicates inheritance. Inheritance offers the following benefits:
· Reusability
· Implement Abstract Data Types
Multiple inheritances are possible for Interface Inheritance
29. What is overriding and overloading?
Overloading occurs when a method has more than one definition in the same scope. It's important to remember two key points from the previous statement: same name and same scope. The method implementations have the same name because they do similar tasks. For instance, if we need to implement a method that gets the student name, there are many ways to do that. We can get the name using an id and we can get the name using a social security number. One way to implement the methods is as follows:
class Student
{ ...
public string GetName( int id) {...}
public string GetName( string ssn) {...}
...
}

The method GetName() is overloaded since the two implementations are in the same scope (class scope) and have the same name. If the methods are declared in different scopes (for example, different classes), then we are not talking about overloading.
Overriding has to do with parent and child classes. If you are not satisfied with the implementation of a method in the parent class, you can keep the same declaration (signature and return type), but provide a different implementation. For example, a Rectangle class inherits the drawing functionality from a Shape class, but it overrides this functionality in order to be able to draw a rectangle.
30. What is the use of private constructors and static constructors? Ref to Q. 24

31. Can protected /sealed be used in interface? - NO. It is public by default and can’t be changed

32. What are destructors?

Destructors are used to destruct instances of classes.
· Destructors cannot be defined in structs. They are only used with classes.
· A class can only have one destructor.
· Destructors cannot be inherited or overloaded.
· Destructors cannot be called. They are invoked automatically.
· A destructor does not take modifiers or have parameters
class Car
{
~ Car() // destructor
{
// cleanup statements...
}
}

33. There is one class A with its constructor and class B with its constructor. Class B inherits Class A. Does constructor of Class A get called? --- Yes

34. What is difference between Response.Redirect and Server.Transfer?

Response.Redirect - Causes the browser to redirect the client to a different URL

Server.Transfer - Sends all of the information that has been assembled for processing by one .asp file to a second .asp file.

35. What is view state?

View state's purpose in life is simple: it's there to persist state across postbacks. (For an ASP.NET Web page, its state is the property values of the controls that make up its control hierarchy.) This begs the question, "What sort of state needs to be persisted?" To answer that question, let's start by looking at what state doesn't need to be persisted across postbacks. Recall that in the instantiation stage of the page life cycle, the control hierarchy is created and those properties that are specified in the declarative syntax are assigned. Since these declarative properties are automatically reassigned on each postback when the control hierarchy is constructed, there's no need to store these property values in the view state.
For example, imagine we have a Label Web control in the HTML portion with the following declarative syntax:
Text="Hello, World!">
When the control hierarchy is built in the instantiation stage, the Label's Text property will be set to "Hello, World!" and its Font property will have its Name property set to Verdana. Since these properties will be set each and every page visit during the instantiation stage, there's no need to persist this information in the view state.
What needs to be stored in the view state is any programmatic changes to the page's state. For example, suppose that in addition to this Label Web control, the page also contained two Button Web controls, a Change Message Button and an Empty Postback button. The Change Message Button has a Click event handler that assigns the Label's Text property to "Goodbye, Everyone!"; the Empty Postback Button just causes a postback, but doesn't execute any code. The change to the Label's Text property in the Change Message Button would need to be saved in the view state. To see how and when this change would be made, let's walk through a quick example. Assuming that the HTML portion of the page contains the following markup:
Font-Name="Verdana" Text="Hello, World!">


Text="Change Message" ID="btnSubmit">



And the code-behind class contains the following event handler for the Button's Click event:
private void btnSubmit_Click(object sender, EventArgs e)
{
lblMessage.Text = "Goodbye, Everyone!";
}


36. Which one do you prefer client-side validation, server-side validation?
Client-side validation
37. If JavaScript is disabled from browser then how will client side validation fire?
38. What is association, aggregation and composition
39. Can we access private property in derived classes? - No
40. What is polymorphism? Poly - different forms (Overloading & Overriding )

41. Can we overload a method of base class in derived classes? - Yes
42. Can I have abstract sealed class? I said no(Asked me to explain why I said no){No use in creating such class}
43. Can I have private method signatures in Interface? --- NO It is public by default and can’t be changed
44. What do you mean by AJAX? Advantage of using AJAX
45. What is ENUM?
46. What is the difference between Array and Array List? Dynamic memory
47. Can I have a single Array with different datatypes?
48. Can I have a single ArrayList with different types of objects?

49. Explain delegate (better to give example)
50. What is the use of private constructor - It will not allow the instantiation of the object (new)
51. What is aggregation
52. How is caching done
53. What is the purpose of global.asax file
54. What are the # of pages/forms you have developed in your career (strange question)
55. What is the purpose of sealed keyword
56. Have you used application blocks? What is the application blocks used.
57. What are web services
58. What is the difference between String and String Builder
59. What will happen if a class is implementing multiple interfaces which have same methods?
60. What is an abstract class?
61. What is an interface?
62. Explain when to choose one over the other (interface and abstract class).
63. Explain briefly Garbage collection in .Net?
64. Can you explicitly call garbage collection?
65. What is the difference between public, internal and protected modifiers?
66. What are value and reference types? (were not asked but expected questions)
67. What is boxing/unboxing? (were not asked but expected questions)
68. State the advantages n disadvantages of Array & ArrayList
69. Do we need to manually deallocate memory in .Net (Garbage Collection) (GC.Collect(), GC.SuppressFinalize())
70. Namespace used by generics?
71. Does C# support multi level inheritance?
72. What happens if a class implements 2 interfaces having same method?
73. What happens if a class has private constructor?
74. Have you worked on web services?
75. What is boxing and unboxing?
76. Can an abstract class contain an abstract method?
77. Can an interface contain member variables?
78. What access specifies are there in .net?
79. What is internal keyword?
80. What is encapsulation? In a class, we have a variable called varIlist, and some method is returning this variable, do you think is it breaking the Encapsulation?
81. What is interface inheritance?
82. What is overriding and overloading?
83. What is aggregation and composition?
84. What is web service? What are WSDL, DISCO, and UDDI?
85. What are application blocks?
86. What is the use of private constructors and static constructors?
87. Did you work on C# only?
88. Are you familiar with OOPS? What is interface and encapsulation? Give examples
89. What is protected and private in C#?
90. What is garbage collection? Need for GC?How do you force GC?
91. Can protected /sealed be used in interface?
92. What are destructors?
93. There is one class A with its constructor and class B with its constructor. Class B inherits Class A. Does constructor of Class A get called?
94. Which areas (front tier,DB,middle) are you comfortable?
95. Which DB did you work on?
96. What is difference between Response.Redirect and Server.Transfer?
97. What is view state?
98. Which one do you prefer client-side validation, server-side validation?
99. If JavaScript is disabled from browser then how will client side validation fire?
100. What is WSDL?
101. What is association, aggregation and composition
102. What is a DISCO,UDDI?Explain.
103. Can we access private property in derived classes? - No
104. What is polymorphism? Poly - different forms (Overloading & Overriding )
105. Can we overload a method of base class in derived classes? - Yes
106. Can I have abstract sealed class? I said no(Asked me to explain why I said no){No use in creating such class}
107. Can I have private method signatures in Interface? -- I said no (Asked me to explain why I said no){No Access specifies}
108. What do you mean by AJAX? Advantage of using AJAX
109. What is ENUM?
110. What is the difference between Array and Array List? Dynamic memory
111. Can I have a single Array with different datatypes?
112. Can I have a single ArrayList with different types of objects?
113. What is the use of static constructors? Only for initializing static variables.

ADO.NET - Sushma

Two Data Providers supported by the .NET Framework implement the OleDbConnection in the System.Data.OleDB namespace (Properties- ConnectionString, ConnectionTimeout, Database, State, Methods - BeginTransaction, , CreateCommand, Open and Close) and the SqlConnection in the System.Data.SqlClient namespace.

1) Which data object is used for reading?

DataReader object implements the System.Data.IDataReader
Close - Closes the DataReader but not the underlying Connection.
GetSchemaTable - Retrieves a DataTable object with information about the schema for the
current result set.
NextResult - When executing a Command that returns multiple result sets, you must use NextResult( ) to move from one result set to another. This method returns true if there are more result sets.
Read - Loads the next row into the DataReader. This method returns true if there are more rows left to be read.

2) When do you go for data adapter and data reader?

3) Explain the steps for retrieving data using data set?

//Typed Dataset access
string s = dsCustomersOrders1.Customers[0].CustomerID;
//Untyped Dataset access
string s = (string) dsCustomersOrders1.Tables["Customers"].Rows[0]["CustomerID"];


Strongly typed DataSets are a collection of classes that inherit from the DataSet, DataTable, and
DataRow classes, and provide additional properties, methods, and events based on the DataSet
schema.

4) Is it possible to read information to & fro using data reader?
No, Forward-only
5) What is the difference between Connection.Close() and Connection.Dispose()?
Closes the SqlConnection obj. Dispose closes and releases all the unmanaged resources associated with the Conn obj
6) What are the basic objects in ADO.Net
DataAdapter, DataReader, Command, Connection
Connection (Connects to the data source), Command (Executes commands against the data
source), DataReader (A forward-only, read-only connected result set), Parameter (Defines
parameters for parameterized SQL statements and stored procedures), Transaction (Groups
statements modifying data into work units that are either committed in their entirety or cancelled)
and DataAdapter (Bridges the connected components to the disconnected components, allowing
a DataSet and DataTable to be filled from the data source and later reconciled with the data source).

Example:
string SQL = "SELECT ContactName FROM Customers";
// Create ADO.NET objects.
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);
SqlDataReader dr = null;
// Execute the command.
try
{
con.Open();
dr = cmd.ExecuteReader();
// Iterate over the results.
while (dr.Read())
{
Console.WriteLine(dr ["ContactName"]);
}
}
catch (Exception e)
{
Console.WriteLine(err.ToString());
}
finally
{
if (dr != null) dr.Close();
con.Close();
}

7) What are DataAdapter?
Set of objects used to communicate between a data source and a dataset. That means reading data from a database into a dataset, and then writing changed data from the dataset back to the database.
Adapter supports the following four properties:
_ SelectCommand - reference to a command (SQL statement or stored procedure
name) that retrieves rows from the data store.
_ InsertCommand - reference to a command for inserting rows into the data store.
_ UpdateCommand - reference to a command for modifying rows in the data store.
_ DeleteCommand - reference to a command for deleting rows from the data store.

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM orders", sConnectionString);

Note: No connection open(), close() for data adapter

Example:
// connection string and the select statement
String sConnString = "”;
String sSelectSQL = "SELECT * FROM Orders";
SqlDataAdapter da = new SqlDataAdapter(sSelectSQL, sConnString);
// create a new DataSet to receive the data
DataSet ds = new DataSet();
// read all of the data from the orders table and loads it into the
// Orders table in the DataSet

da.Fill(ds, "Orders");

Design Patterns

114. Explain Singleton pattern
115. What is MVC pattern? Explain.
116. What is MVC Patter? Does Asp.NET/C#.NET is suitable for implanting the MVC?
117. What is Singleton pattern? When do you use?
118. What is Factory method design pattern?

Project Management questions:

119. How do you manage projects in an onsite offshore environment?
120. How do you allocate work between onsite and offshore?
121. What type of job do you prefer onsite to do?
122. How do you manage remotely?
123. Have you experienced situations where client team members were not favorable to offshoring? How did / would you work around it?
124. Have you maintained project plans, change management process etc?
125. Have you worked in development projects or production support? ( highlight atleast one development project, they are keen on expsoure to full SDLC cycle).

Processes/Patterns related questions: UML - Sushma

UML is an open method used to specify, visualize, construct and document the artifacts of an object-oriented software-intensive system under development.UML offers a standard way to write a system's blueprints.
Two different views:
Static (or structural) view: static structure of the system using objects, attributes, operations and relationships. The structural view includes class diagrams and composite structure diagrams
Dynamic (or behavioral) view: Emphasizes the dynamic behavior of the system by showing collaborations among objects and changes to the internal states of objects. This view includes sequence diagrams, activity diagrams, use case diagrams and state machine diagrams

http://en.wikipedia.org/wiki/Unified_Modeling_Language#Unified_Modeling_Language_topics

126. Do you know use cases? Have you heard about RUP? - If you have theoretical knowledge, you can mention that you have been trained by Cognizant and sound confident
127. Have you worked in a water fall model project or a iterative model?
Iterative development slices the deliverable business value (system functionality) into iterations. In each iteration a slice of functionality is delivered.
Waterfall development completes the project-wide work-products of each discipline in a single step before moving on to the next discipline in the next step. Business value is delivered all at once, and only at the very end of the project.


128. What are the different UML diagrams you have used?
Sequence, Activity, Use case, Class diagrams.
129. What is Use Case diagram and what are its main components
Actors & usecase (actions done by the system)
Relationships (include, extend, generalization)
130. Give any example for exception work flow in the use case?
131. Tell me about sequence diagrams?
A sequence diagram shows, as parallel vertical lines ("lifelines"), different processes or objects that live simultaneously, and, as horizontal arrows, the messages exchanged between them, in the order in which they occur.
Simply, different obj’s communication. Live obj - dotted vertical lines, messages - horizontal lines.
132. Explain n-tier architecture?
A multilayered software architecture is using different layers for allocating the responsibilities of an application
The concepts of layer and tier are often used interchangeably. However, one fairly common point of view is that there is indeed a difference, and that a layer is a logical structuring mechanism for the elements that make up your software solution, while a tier is a physical structuring mechanism for the system
Three-tier or multi-tier or n-tier is a client-server architecture in which the user interface, functional process logic ("business rules"), computer data storage and data access are developed and maintained as independent modules, most often on separate platforms.
133. What are the different types of test cases that should be covered
( positive
negative
exception)
134. What is a basic flow (in terms of UML)? --- Sequence of steps the occur.

Technical questions:

8) Every system has some entities, so as a developer how would you identify them?
9) Explained me a scenario of information being redundant with in entities, so as a developer what would be the work around? (Related to inheritance and abstract class).
10) Does u prefer to have a web method with 3 parameters or single parameter which is in XML format?
11) In which kind of applications would you like to work?(windows, web)
12) What are the advantages/disadvantages of windows and web applications?

XML, XSD, DTD, HTML, XPath, XQuery - Sushma

1. Where did you use XML, XSL, and XSLT?
2. What is the difference between DTD and XSD?
3. What is XML
4. What is XSL
5. What is XSLT
6. What is XPath
7. Is xml case sensitive - Yes.



- .Net Assembly FAQ’s

- State Management

- Http Handlers and HTTP Modules
- ASP.NET Page Life Cycle
- ASP.NET Application Life Cycle