Sql Server Interview Questions with Answers

9 November 2013

Sql Server Interview Questions with Answers

•  What are Constraints  or Define Constraints ?

Generally we use Data Types to limit the kind of Data in a Column. For example, if we declare any column with data type INT then ONLY Integer data can be inserted into the column. Constraint will help us to limit the Values we are passing into a column or a table. In simple Constraints are nothing but Rules or Conditions applied on columns or tables to restrict the data.

• Different types of Constraints ?

There are THREE Types of Constraints.

1. Domain
2. Entity
3. Referential

Domain has the following constraints types -

1. Not Null
2. Check

Entity has the following constraint types -

1. Primary Key
2. Unique Key

Referential has the following constraint types -

1. Foreign Key

• What is the difference between Primary Key and Unique Key ?

Both the Primary Key(PK) and Unique Key(UK) are meant to provide Uniqueness to the Column on which they are defined. PFB the major differences between these two.

1. By default PK defines Clustered Index in the column where as UK defines Non Clustered Index.
2. PK doesn’t allow NULL Value where as UK allow ONLY ONE NULL.
3. You can have only one PK per table where as UK can be more than one per table.
4. PK can be used in Foreign Key relationships where as UK cannot be used.

• What is the difference between Delete and Truncate ?

Both Delete and Truncate commands are meant to remove rows from a table. There are many differences between these two and pfb the same.

1. Truncate is Faster where as Delete is Slow process.
2. Truncate doesn’t log where as Delete logs an entry for every record deleted in Transaction Log.
3. We can rollback the Deleted data where as Truncated data cannot be rolled back.
4. Truncate resets the Identity column where as Delete doesn’t.
5. We can have WHERE Clause for delete where as for Truncate we cannot have WHERE Clause.
6. Delete Activates TRIGGER where as TRUNCATE Cannot.
7. Truncate is a DDL statement where as Delete is DML statement.

• What are Indexes or Indices ?

An Index in SQL is similar to the Index in a  book. Index of a book makes the reader to go to the desired page or topic easily and Index in SQL helps in retrieving the data faster from database. An Index is a seperate physical data structure that enables queries to pull the data fast. Indexes or Indices are used to improve the performance of a query.

• Types of Indices in SQL ?

There are TWO types of Indices in SQL server.

1. Clustered
2. Non Clustered

• How many Clustered and Non Clustered Indexes can be defined for a table ?

Clustered – 1
Non Clustered – 999

MSDN reference – Click Here

• What is Transaction in SQL Server ? 

Transaction groups a set of T-Sql Statements into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, atransaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.by programmers to group together read and write operations. In Simple Either FULL or NULL i.e either all the statements executes successfully or all the execution will be rolled back.

• Types of Transactions ?

There are TWO forms of Transactions.

1. Implicit – Specifies any Single Insert,Update or Delete statement as Transaction Unit.  No need to specify Explicitly.
2. Explicit – A group of T-Sql statements with the beginning and ending marked with Begin Transaction,Commit and RollBack. PFB an Example for Explicit transactions.

BEGIN TRANSACTION
Update Employee Set Emp_ID = 54321 where Emp_ID = 12345
If(@@Error <>0)
ROLLBACK
Update LEave_Details Set Emp_ID = 54321 where Emp_ID = 12345
If(@@Error <>0)
ROLLBACK
COMMIT
In the above example we are trying to update an EMPLOYEE ID from 12345 to 54321 in both the master table “Employee” and Transaction table “Leave_Details”. In this case either BOTH the tables will be updated with new EMPID or NONE.

• What is the Max size and Max number of columns for a row in a table ?

Size – 8060 Bytes

Columns – 1024

• What is Normalization and Explain different normal forms.

Database normalization is a process of data design and organization which applies to data structures based on rules that help building relational databases.
1. Organizing data to minimize redundancy.
2. 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.
1NF: Eliminate Repeating Groups
Each set of related attributes should be in separate table, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
1. Table must be in 1NF.
2. All fields are dependent on the whole of the primary key, or a relation is in 2NF if it is in 1NF and every non-key attribute is fully dependent on each candidate key of the relation. 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
1. The table must be in 2NF.
2. Transitive dependencies must be eliminated. All attributes must rely only on the primary 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
for every one of its non-trivial functional dependencies X → Y, X is a superkey—that is, X is either a candidate key or a superset thereof. 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.
For example, if you can have two phone numbers values and two email address values, then you should not have them in the same table.
5NF: Isolate Semantically Related Multiple Relationships
A 4NF table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys. There may be practical constrains on information that justify separating logically related many‐to‐many relationships.

• What is Denormalization ?

For optimizing the performance of a database by adding redundant data or by grouping data is called de-normalization.
It is sometimes necessary because current DBMSs implement the relational model poorly.
In some cases, de-normalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.
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.

• Query to Pull ONLY duplicate records from table ?

There are many ways of doing the same and let me explain one here. We can acheive this by using the keywords GROUP and HAVING. The following query will extract duplicate records from a specific column of a particular table.
Select specificColumn
FROM particluarTable
GROUP BY specificColumn
HAVING COUNT(*) > 1
This will list all the records that are repeated in the column specified by “specificColumn” of a “particlarTable”.

• Types of Joins in SQL SERVER ?

There are 3 types of joins in Sql server.

1. Inner Join
2. Outer Join
3. Cross Join

Outer join again classified into 3 types.

1. Right Outer Join
2. Left Outer Join
3. Full Outer Join.

• What is Table Expressions in Sql Server ?

Table Expressions are subqueries that are used where a TABLE is Expected. There are TWO types of table Expressions.

1. Derived tables
2. Common Table Expressions.

• What is Derived Table ?

Derived tables are table expression which appears in FROM Clause of a Query. PFB an example of the same.

select * from (Select Month(date) as Month,Year(Date) as Year from table1) AS Table2

In the above query the subquery in FROM Clause “(Select Month(date) as Month,Year(Date) as Year from table1) ” is called Derived Table.

• What is CTE or Common Table Expression ?

Common table expression (CTE) is a temporary named result set that you can reference within a

SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement. There are TWO types of CTEs in Sql Server -

1. Recursive
2. Non Recursive

• Difference between SmallDateTime and DateTime datatypes in Sql server ?

Both the data types are meant to specify date and time but these two has slight differences and pfb the same.

1. DateTime occupies 4 Bytes of data where as SmallDateTime occupies only 2 Bytes.
2. DateTime ranges from 01/01/1753 to 12/31/9999 where as SmallDateTime ranges from 01/01/1900 to 06/06/2079.

• What is SQL_VARIANT Datatype ? 

The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values. (The only types of values that cannot be stored are TIMESTAMP values.) Each value of an SQL_VARIANT column has two parts: the data value and the information that describes the value. (This information contains all properties of the actual data type of the value, such as length, scale, and precision.)

• What is Temporary table ? 

A temporary table is a database object that is temporarily stored and managed by the database system. There are two types of Temp tables.

1. Local
2. Global

• What are the differences between Local Temp table and Global Temp table ? 

Before going to the differences, let’s see the similarities.

1. Both are stored in tempdb database.
2. Both will be cleared once the connection,which is used to create the table, is closed.
3. Both are meant to store data temporarily.

PFB the differences between these two.

1. Local temp table is prefixed with # where as Global temp table with ##.
2. Local temp table is valid for the current connection i.e the connection where it is created where as Global temp table is valid for all the connection.
3.  Local temp table cannot be shared between multiple users where as Global temp table can be shared.

• Whar are the differences between Temp table and Table variable ?

This is very routine question in interviews. Let’s see the major differences between these two.

1. Table variables are Transaction neutral where as Temp tables are Transaction bound. For example if we declare and load data into a temp table and table variable in a transaction and if the transaction is ROLLEDBACK, still the table variable will have the data loaded where as Temp table will not be available as the transaction is rolled back.
2. Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
3. Table variables don’t participate in transactions, logging or locking. This means they’re faster as they don’t require the overhead.
4. You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don’t need to define your temp table structure upfront.

• What is the difference between Char,Varchar and nVarchar datatypes ?

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

• What is the difference between STUFF and REPLACE functions in Sql server ?

The Stuff function is used to replace characters in a string. This function can be used to delete a certain length of the string and replace it with a new string.

Syntax – STUFF (string_expression, start, length, replacement_characters)

Ex - SELECT STUFF(‘I am a bad boy’,8,3,’good’)

Output – “I am a good boy”

REPLACE function replaces all occurrences of the second given string expression in the first string expression with a third expression.

Syntax - REPLACE (String, StringToReplace, StringTobeReplaced)

Ex – REPLACE(“Roopesh”,”pe”,”ep”)

Output – “Rooepsh” – You can see PE is replaced with EP in the output.

• What are Magic Tables ? 

Sometimes we need to know about the data which is being inserted/deleted by triggers in database. Whenever a trigger fires in response to the INSERT, DELETE, or UPDATE statement, two special tables are created. These are the inserted and the deleted tables. They are also referred to as the magic tables. These are the conceptual tables and are similar in structure to the table on which trigger is defined (the trigger table).

The inserted table contains a copy of all records that are inserted in the trigger table.
The deleted table contains all records that have been deleted from deleted from the trigger table.
Whenever any updation takes place, the trigger uses both the inserted and deleted tables.

• Explain about RANK,ROW_NUMBER and DENSE_RANK in Sql server ?

Found a very interesting explanation for the same in the url Click Here . PFB the content of the same here.
Lets take 1 simple example to understand the difference between 3.
First lets create some sample data :
– create table
CREATE TABLE Salaries
(
Names VARCHAR(1),
SalarY INT
)
GO
– insert data
INSERT INTO Salaries SELECT
‘A’,5000 UNION ALL SELECT
‘B’,5000 UNION ALL SELECT
‘C’,3000 UNION ALL SELECT
‘D’,4000 UNION ALL SELECT
‘E’,6000 UNION ALL SELECT
‘F’,10000
GO
– Test the data
SELECT Names, Salary
FROM Salaries
Now lets query the table to get the salaries of all employees with their salary in descending order.
For that I’ll write a query like this :
SELECT names
, salary
,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER
,rank () OVER (ORDER BY salary DESC) as RANK
,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK
FROM salaries
>>Output

NAMES	SALARY	ROW_NUMBER	RANK	DENSE_RANK
F	10000	1	1	1
E	6000	2	2	2
A	5000	3	3	3
B	5000	4	3	3
D	4000	5	5	4
C	3000	6	6	5

Interesting Names in the result are employee A, B and D.  Row_number assign different number to them. Rank and Dense_rank both assign same rank to A and B. But interesting thing is what RANK and DENSE_RANK assign to next row? Rank assign 5 to the next row, while dense_rank assign 4.
The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.  The RANK function does not always return consecutive integers.  The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.
So question is which one to use?
Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don’t leave any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.
So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and if you are ranking students according to there marks you should be using RANK(Though it is not mandatory, depends on your requirement.)

•  What are the differences between WHERE and HAVING clauses in SQl Server ? 

PFB the major differences between WHERE and HAVING Clauses ..

1.Where Clause can be used other than Select statement also where as Having is used only with the SELECT statement.
2.Where applies to each and single row and Having applies to summarized rows (summarized with GROUP BY).
3.In Where clause the data that fetched from memory according to condition and In having the completed data firstly fetched and then separated according to condition.
4.Where is used before GROUP BY clause and HAVING clause is used to impose condition on GROUP Function and is used after GROUP BY clause in the query.

• Explain Physical Data Model or PDM ?

Physical data model represents how the model will be built in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Features of a physical data model include:

1. Specification all tables and columns.
2. Foreign keys are used to identify relationships between tables.
3. Specying Data types.

EG -

Reference from Here

•  Explain Logical Data Model ?

A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the database. Features of a logical data model include:

1. Includes all entities and relationships among them.
2. All attributes for each entity are specified.
3. The primary key for each entity is specified.
4. Foreign keys (keys identifying the relationship between different entities) are specified.
5. Normalization occurs at this level.

Reference from Here

• Explain Conceptual Data Model ?

A conceptual data model identifies the highest-level relationships between the different entities. Features of conceptual data model include:

1. Includes the important entities and the relationships among them.
2. No attribute is specified.
3. No primary key is specified.

Reference from Here

• What is Log Shipping ?

Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover.
Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.

•  What are the advantages of database normalization ?

Benefits of normalizing the database are

1. No need to restructure existing tables for new data.
2. Reducing repetitive entries.
3. Reducing required storage space
4. Increased speed and flexibility of queries.

•  What are Linked Servers  ?

 Linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:

1. The ability to access data from outside of SQL Server.
2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
3. The ability to address diverse data sources similarly.
4. Can connect to MOLAP databases too.

•  What is the Difference between the functions COUNT and COUNT_BIG ?

Both Count and Count_Big functions are used to count the number of rows in a table and the only difference is what it returns.

1. Count returns INT datatype value where as Count_Big returns BIGINT datatype value.
2. Count is used if the rows in a table are less where as Count_Big will be used when the numbenr of records are in millions or above.

Syntax -

1. Count – Select count(*) from tablename
2. Count_Big – Select Count_Big(*) from tablename

•  How to insert values EXPLICITLY  to an Identity Column ? 

This has become a common question these days in interviews. Actually we cannot Pass values to Identity column and you will get the following error message when you try to pass value.

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'tablename' when IDENTITY_INSERT is set to OFF.

To pass an external value we can use the property IDENTITY_INSERT. PFB the sysntax of the same.

SET IDENTITY_INSERT <tablename> ON;

Write your Insert  statement here by passing external values to the IDENTITY column.

Once the data is inserted then remember to SET the property to OFF.

• How to RENAME a table and column in SQL ?

We can rename a table or a column in SQL using the System stored procedure SP_RENAME. PFB the sample queries.

Table - EXEC sp_rename @objname = department, @newname = subdivision

Column - EXEC sp_rename @objname = ‘sales.order_no’ , @newname = ordernumber

• How to rename a database ?

To rename a database please use the below syntax.

USE master;
GO
ALTER DATABASE databasename
Modify Name = newname ;
GO

•  What is the use the UPDATE_STATISTICS command ?

UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account.

• How to read the last record from a table with Identity Column ?

We can get the same using couple of ways and PFB the same.

First - 

SELECT *
FROM    TABLE
WHERE  ID = IDENT_CURRENT(‘TABLE’)

Second - 

SELECT *
FROM    TABLE
WHERE   ID = (SELECT MAX(ID)  FROM TABLE)

Third - 

select top 1 * from TABLE_NAME  order by ID desc

• What is Worktable ?

A worktable is a temporary table used internally by SQL Server to process the intermediate results of a query. Worktables are created in the tempdb database and are dropped automatically after query execution. Thease table cannot be seen as these are created while a query executing and dropped immediately after the execution of the query.

• What is HEAP table ?

A table with NO CLUSTERED INDEXES is called as HEAP table. The data rows of a heap table are not stored in any particular order or linked to the adjacent pages in the table. This unorganized structure of the heap table usually increases the overhead of accessing a large heap table, when compared to accessing a large nonheap table (a table with clustered index). So, prefer not to go with HEAP  tables .. 

• What is ROW LOCATOR ?

If you define a NON CLUSTERED index on a table then the index row of a nonclustered index contains a pointer to the corresponding data row of the table. This pointer is called a row locator. The value of the row locator depends on whether the data pages are stored in a heap or are clustered. For a nonclustered index, the row locator is a pointer to the data row. For a table with a clustered index, the row locator is the clustered index key value.

•  What is Covering Index ?

A covering index is a nonclustered index built upon all the columns required to satisfy a SQL query without going to the base table. If a query encounters an index and does not need to refer to the underlying data table at all, then the index can be considered a covering index.  For Example
Select col1,col2 from table
where col3 = Value
group by col4
order by col5
Now if you create a clustered index for all the columns used in Select statement then the SQL doesn’t need to go to base tables as everything required are available in index pages.

•  What is Indexed View ?

A database view in SQL Server is like a virtual table that represents the output of a SELECT statement. A view is created using the CREATE VIEW statement, and it can be queried exactly like a table. In general, a view doesn’t store any data—only the SELECT statement associated with it. Every time a view is queried, it further queries the underlying tables by executing its associated SELECT statement.

A database view can be materialized on the disk by creating a unique clustered index on the view. Such a view is referred to as an indexed view. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing costly operations during query execution. After the view is materialized, multiple nonclustered indexes can be created on the indexed view.

• What is Bookmark Lookup ?

When a SQL query requests a small number of rows, the optimizer can use the nonclustered index, if available, on the column(s) in the WHERE clause to retrieve the data. If the query refers to columns that are not part of the nonclustered index used to retrieve the data, then navigation is required from the index row to the corresponding data row in the table to access these columns.This operation is called a bookmark lookup.

ASP.Net Interview Question and Answer for 2+ or 3+ years experience

1.) What is asp.net life cycle ? 
Life Cycle Events

PreInit

The properties like IsPostBack have been set at this time.

This event will be used when we want to:

1. Set master page dynamically.
2. Set theme dynamically.
3. Read or set profile property values.
4. This event is also preferred if want to create any dynamic controls.

Init

1. Raised after all the controls have been initialized with their default values and any skin settings have been applied.
2. Fired for individual controls first and then for page.

LoadViewState

1. Fires only if IsPostBack is true.
2. Values stored in HiddenField with id as _ViewState decoded and stored into corresponding controls.

LoadPostData

Some controls like:

1. Fires only if IsPostBack is true.
2. Some controls like Textbox are implemented from IPostBackDataHandler and this fires only for such controls.
3. In this event page processes postback data included in the request object pass it to the respective controls.

PreLoad

• Used only if want to inject logic before actual page load starts.

Load

• Used normally to perform tasks which are common to all requests, such as setting up a database query.

Control events

1. This event is fired when IsPostBack is true.
2. Use these events to handle specific control events, such as a Button control's Click event or a TextBox control's TextChanged event.

PreRenderRaised after the page object has created all the controls that are required for rendering which includes child controls and composite controls.

1. Use the event to make final changes to the contents of the page or its controls before the values are stored into the viewstate and the rendering stage begins.
2. Mainly used when we want to inject custom JavaScript logic.

SaveViewState

• All the control values that support viewstate are encoded and stored into the viewstate.

RenderGenerates output (HTML) to be rendered at the client side.

• We can add custom HTML to the output if we want here.

Unload

1. Fired for individual controls first and then for page.

   2. Used to perform cleanup work like closing open files and database connections.  


2.) How the request is handled by IIS ?

We give an URL to an aspx page in the browser address bar and press enter. What happens next? We get the response in terms of rendered HTML but how?

1. We are requesting something from the browser, which means indirectly we are requesting something from the Web Server, that means IIS. IIS, based on the file extension, decides which ISAPI extension can serve the request.
   
   And in case of ASP.Net (.aspx) it will be aspnet_isapi_dll so the request is passed to it for processing.
    
2. When the first request comes to the website,
   
   an application domain is created by the ApplicationManager class where exactly the website runs, and which creates an isolation between 2 web applications.
   Within the application domain an instance of the HostingEnvironment class is created which provides access information about the application such as the name of the folder where the application is stored.
    
3. Next ASP.Net creates core objects like HttpContext, HttpRequest,HttpResponse.
    
4. Finally the application starts by creating an instance of the HttpApplication Class (which can be reused for multiple requests to maximize performance).

3.) What is Difference between Session and Cookies  ? 

The basic and main difference between cookie and session is that cookies are stored in the user's browser but sessions can't store in user's browser. This specifies which is best used for.

• A cookie can keep all the information in the client's browser until deleted. If a person has a login and password, this can be set as a cookie in their browser so they do not have to re-login to your website every time they visit. You can store almost anything in a browser cookie.

• Sessions are not reliant on the user allowing a cookie. They work like a token in the browser which allowing access and passing information while the user has opened his browser. The problem in sessions is when you close the browser the session will automatically lost. So, if you had a site requiring a login, this couldn't be saved as a session but it can be saved as a cookie, and the user has to re-login every time they visit.
cookies are nothing but a small piece of information on the client machine. before we create a cookies we should check whether the cookies are allowed at the browser side. They are limited in a size 4k.(they are 2 types of cookies peristant cookie , and session cookies)

Sessions cookies are stored in a server memory during the client browser session.When the browser is closed the session cookies are lost. 


4.) Advantages and disadvantages of Session?
 
Following are the basic advantages and disadvantages of using session. I have describe in details with each type of session at later point of time.

Advantages:

• It helps maintain user state and data all over the application.
• It is easy to implement and we can store any kind of object.
• Stores client data separately.
• Session is secure and transparent from the user.

Disadvantages:

• Performance overhead in case of large volumes of data/user, because session data is stored in server memory.
• Overhead involved in serializing and de-serializing session data, because in the case of StateServerand SQLServer session modes, we need to serialize the objects before storing them.

Besides these, there are many advantages and disadvantages of session that are based on the session type. I have discussed all of them in the respective sections below.

5). What is state management?
Ans: State management is the process by which you maintain state and page information over multiple requests for the same or different pages.

6). Http is stateless, What does this mean?
Ans: Stateless protocol is a communications protocol that treats each request as an independent transaction that is unrelated to any previous request so that the communication consists of independent pairs of requests and responses.

7). What is Session?
Ans: We know that Http is stateless, means when we open a webpage and fill some information and then move to next page then the data which we have entered will lost.
It happed do to Http protocol stateless nature. So here session come into existence, Session provide us the way of storing data in server memory. So you can store your page data into server
memory and retrieve it back during page postbacks.

8).  What are the Advantage and disadvantage of Session?
Ans: Advantages:
Session provide us the way of maintain user state/data.
It is very easy to implement.
One big advantage of session is that we can store any kind of object in it. :eg, datatabe, dataset.. etc
By using session we don't need to worry about data collesp, because it store every client data separately.
Session is secure and transparent from the user.
Disadvantages:
Performance overhead in case of large volumes of data/user, because session data is stored in server memory.
Overhead involved in serializing and de-serializing session data, because in the case of StateServer and SQLServer session modes, we need to serialize the objects before storing them.

9). What is Session ID in Asp.net?
Ans: Asp.Net use 120 bit identifier to track each session. This is secure enough and can't be reverse engineered. When client communicate with server, only session id is transmitted, between them. When client request for data, ASP.NET looks on to session ID and retrieves corresponding data.

10). By default where the sessions ID's are stored ?
Ans: By default, the unique identifier for a session is stored in a non-expiring session cookie in the browser. You can specify that session identifiers not be stored in a cookie by setting the cookieless attribute to true in the sessionState configuration element.
We can also configure our application to store it in the url by specifying a "cookieless" session
The ASP Session cookie has this format:-
ASPSESSIONIDACSSDCCC=APHELKLDMNKNIOJONJACDHFN


11). Where does session stored if cookie is disabled on client’s machine?
Ans: If you want to disable the use of cookies in your ASP.NET application and still make use of session state, you can configure your application to store the session identifier in the URL instead of a cookie by setting the cookieless attribute of the sessionState configuration element to true, or to UseUri, in the Web.config file for your application.
The following code example shows a Web.config file that configures session state to use cookieless session identifiers.

Code:

<configuration>
  <system.web>
    <sessionState
      cookieless="true"
      regenerateExpiredSessionId="true"
      timeout="30" />
  </system.web>
</configuration>

12). Can you describe all the property set in web.config under session state?
Ans: 

Code:

<configuration>
  <sessionstate
      mode="inproc"
      cookieless="false"
      timeout="20"
      sqlconnectionstring="data source=127.0.0.1;user id=<user id>;password=<password>"
      server="127.0.0.1"
      port="42424"
  />
</configuration>

Mode: The mode setting supports three options: inproc, sqlserver, and stateserver. As stated earlier, ASP.NET supports two modes: in process and out of process. There are also two options for out-of-process state management: memory based (stateserver), and SQL Server based (sqlserver). We'll discuss implementing these options shortly.
Cookieless: The cookieless option for ASP.NET is configured with this simple Boolean setting.
Timeout: This option controls the length of time a session is considered valid. The session timeout is a sliding value; on each request the timeout period is set to the current time plus the timeout value
Sqlconnectionstring: The sqlconnectionstring identifies the database connection string that names the database used for mode sqlserver.
Server: In the out-of-process mode stateserver, it names the server that is running the required Windows NT service: ASPState.
Port: The port setting, which accompanies the server setting, identifies the port number that corresponds to the server setting for mode stateserver.

13). What are Session Events?
Ans: There are two types of session events available in ASP.NET:
Session_Start
Session_End
You can handle both these events in the global.asax file of your web application. When a new session initiates, the session_start event is raised, and the Session_End event raised when a session is abandoned or expires.

14). How you can disable session?
Ans: If we set session Mode="off" in web.config, session will be disabled in the application. For this, we need to configure web.config the following way:

Code:

<configuration>
  <sessionstate  Mode="off"/>
</configuration>

15). If I have more than one version of one assemblies, then how will I use old version (how/where to specify version number?) in my application?

Ans.The version number is stored in the following format: …. The assembly manifest can then contain a reference to which version number we want to use.

16). How do you create threading in.NET? What is the namespace for that?
Ans.
System.Threading;

//create new thread using the thread class’s constructor

Thread myThread = new Thread(new ThreadStart (someFunction));

17). What do you mean by Serialize and MarshalByRef?

Serialization is the act of saving the state of an object so that it can be recreated (i.e deserialized) at a later date.
The MarshalByRef class is part of the System.Runtime.Remoting namespace and enables us to access and use objects that reside in different application
domains. It is the base class for objects that need to communicate across application domains. MarshalByRef objects are accessed directly within their own
application domain by using a proxy to communicate. With MarshalByValue the a copy of the entire object is passed across the application domain

 18). What is the difference between Array and LinkedList?
An array is a collection of the same type. The size of the array is fixed in its declaration.
A linked list is similar to an array but it doesn’t have a limited size.

19). What is Asynchronous call and how it can be implemented using delegates?

A synchronous call will wait for a method to complete before program flow is resumed. With an asynchronous call the program flow continues whilst the method executes.

//create object
SomeFunction objFunc = new SomeFunction();

//create delegate
SomeDelegate objDel = new SomeDelegate(objFunc.FunctionA);

//invoke the method asynchronously (use interface IAsyncResult)
IAsyncResult asynchCall = SomeDelegate.Invoke();

 20). How to create events for a control? What is custom events? How to create it?
Ans.
An event is a mechanism used in a class that can be used to provide a notification when something interesting happens. (typical evens in a windows application
include: change text in textbox, double click or click a button, select an item in dropdown box).
A custom event is an event created by the user that other developers can use. For example assuming that we have a CashTransaction class and we have a bank
balance property in that class. We may want to set-up an event that provides a notification when the bank balance drops below a certain amount. In order to
produce an event the process would be roughly as follows:
Create the class for the event derived from EventArgs.
Create a delegate with a return type of void.
Create a class containing the method that will activate the event.
Create a class with methods to handle the event.

 21). If you want to write your own dot net language, what steps you will you take care?

We will need to ensure that the high level code is compiled to MSIL (Microsoft intermediate language) so that it can be interpreted by the CLR.

22). Describe the diffeerence between inline and code behind - which is best in a loosely coupled solution?

The term ‘code behind’ refers to application code that is not embedded within the ASPX page and is separated out into a separate file which is then referenced
from the ASPX page. Inline code is the traditional ASP architectural model where business logic code was embedded within the ASP page. Separating the business
logic code from the presentation layer offers several advantages:
1) It allows graphic designers and web developers to work on the presentation layer whilst the application developers concentrate on the business logic.
2) The codebehind file is compiled as a single dll increasing the efficiency of the application,
3) The codebehind model offers a true OO development platform,
4) It speeds up development time as it allows developers to fully maximise the features of the .NET framework such as Cahing, ViewState, Session, Smart Navigation etc.
5) Code is much easier to maintain and susceptible for change.
6) The compiler and VS.NET provides much better support for error checking, intellisense and debugging when using the code behind model.

23). How dot net compiled code will become platform independent?

The raison d’etre for .NET was to cater for multiples languages on a single windows platform whereas the aim of Java was to be a single language on multiple
platforms. The only way that .NET can be platform independent is if there is a version of the .NET framework installed on the target machine.

 24). Without modifying source code if we compile again, will it be generated MSIL again?
Ans.No.

25). How does you handle this COM components developed in other programming languages in.NET?

Ans.
use TlbImp.exe to import the COM types into your .NET project. If no type library for the COM component then use System.Runtime.InteropServices
use RegAsm.exe to call a .NET developed component in a COM application.

 26). How CCW (Com Callable Wrapper) and RCW (Runtime Callable Wrappers) works?

CCW: When a COM application calls a NET object the CLR creates the CCW as a proxy since the COM application is unable to directly access the .NET object.
RCW: When a .NET application calls a COM object the CLR creates the RCW as a proxy since the .NET application is unable to directly access the .COM object.

 27). What are the new thee features of COM+ services, which are not there in COM (MTS)?

Role based security.
Neutral apartment threading.
New environment called context which defines the execution environment

  28). What are the differences between COM architecture and.NET architecture?

.Net architecture has superseded the old COM architecture providing a flexible rapid application development environment which can be used to create windows,
web and console applications and web services. .NET provides a powerful development environment that can be used to create objects in any .NET compliant language.
.NET addresses the previous problems of dll hell with COM by providing strongly named assemblies and side-by-side execution where two assemblies with the same name can run on the same box.

29). Can we copy a COM dll to GAC folder?

Ans.
No. It only stores .NET assemblies.

30). What is Shared and Repeatable Inheritance?

Shared Inheritance-: Shared Inheritance-: ITt is multiple times using same class. The mechanism of deriving a new class from an existing class is called inheritance. Shared inheritance introduces a new opportunity of ambiguity and additional implementation complexity. Assume D inherits from B and C, both of which inherits from A. Here A in shared. Single copy made from both derived classes is called shared inheritance.

  31). Can you explain what inheritance is and an example of when you might use it?
 
Ans.
Inheritance is a fundamental feature of any OO language. It allows us to inherit the members and attributes from a base class to a new derived class. This
leads to increased code reusability and also makes applications easier to develop, maintain and extend as the new derived class can contain new features not
available in the base class whilst at the same time preserving the attributes inherited from the base class.

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

Ans.
Use the singleton design pattern.

 public sealed class Singleton 
 { 
   static readonly Singleton Instance=new Singleton(); 
      static Singleton() 
      { 
      } 
      Singleton() 
      { 
      } 
      public static Singleton Instance 
      { 
           get 
           { 
                return Instance; 
           } 
      } 
 }

 33). What are virtual destructures?


A constructor can not be virtual but a destructor may. Use virtual destructors when you want to implement polymorphic tearing down of an object.

  34). What is close method? How its different from Finalize and Dispose?
  
finalise is the process that allows the garbage collector to clean up any unmanaged resources before it is destroyed.
The finalise method can not be called directly; it is automatically called by the CLR. In order to allow more control over the release of unmanaged resources
the .NET framework provides a dispose method which unlike finalise can be called directly by code.
Close method is same as dispose. It was added as a convenience.

 35). What is Boxing and UnBoxing?

Ans.
Boxing is the process of converting a value type to a reference type. More specifically it involves encapsulating a copy of the object and moving it from
stack to heap. Unboxing is the reverse process.

  36). What is check/uncheck?

Ans.
checked: used to enable overflow checking for arithmetic and conversion functions.
unchecked: used to disable overflow checking for arithmetic and conversion functions

  37). What is the use of base keyword? Tell me a practical example for base keyword’s usage?

Ans.
The base keyword is used to access members of the base class from within a derived class:
* Call a method on the base class that has been overridden by another method.
* Specify which base-class constructor should be called when creating instances of the derived class.

A base class access is permitted only in a constructor, an instance method, or an instance property accessor.
It is an error to use the base keyword from within a static method.
Example:In this example, both the base class, Person, and the derived class, Employee, have a method named Getinfo. By using the base keyword,
it is possible to call the Getinfo method on the base class, from within the derived class.
// keywords_base.cs
// Accessing base class members

 using System; 
 public class Person 
 { 
 protected string ssn = "444-55-6666"; 
 protected string name = "John L. Malgraine"; 
 public virtual void GetInfo() 
 { 
 Console.WriteLine("Name: {0}", name); 
 Console.WriteLine("SSN: {0}", ssn); 
 } 
 } 
 class Employee: Person 
 { 
 public string id = "ABC567EFG"; 
 public override void GetInfo() 
 { 
 // Calling the base class GetInfo method: 
 base.GetInfo(); 
 Console.WriteLine("Employee ID: {0}", id); 
 } 
 } 
 class TestClass { 
 public static void Main() 
 { 
 Employee E = new Employee(); 
 E.GetInfo(); 
 } 
 } 
 

38). Difference Between Query String and Session

 

Querystring	Session
Querystring is client side state management technique.	Session is server side state management technique.
Querystring data is page specific i.e. can be accessed in that page only.	Session data can be accessed throughout the session.
Querystring data is visible to user and can be seen in browser url.	Session data is not visible to user.
Data is not secured and can be altered hence insensitive data is stored in querystring.	Data is secured hence sensitive data such as user information is stored.
Querystring has constraint of Maxlength.	Session does not have such constraint.

 

39). Difference between Query string and Cookies

cookies is a text file stored on client machine when we surf ant thing on internet by the server automatically we dont have to create it

query string is used to transfer data from 1 page to anothe but this is not safe s it shows in url what data we r sending
pen any site and see url after question mark tht is url

Cookies: - Cookies are little pieces of information that a server stores on a browser. They are of two types
1. Temporary cookie
2. Persistent cookie

Temporary cookie: - They are also known as session cookies. These are volatile in nature. When the browser is shutdown they are erased.

Persistent cookie:- These may be called as permanent cookies. These are especially saved in files. It may remain for a month or year. 

Properties of cookies 
Some properties of cookie
Name: - represent the name of cookie.
Name value: - represent a collection of key values of cookie
Domain: - represent the domain associated with a specific cookie.
Path: - the path associated with a cookie.
Expires: - expired time of cookie.
Hashkey: - identifies whether the cookie is a cookie dictionary.
Secure: - specifies whether the cookie is to be sent in an encrypted connection or not
Query string is the limited way to pass information to the web server while Transferring from one page to another page. This information is passed in url of the request. see below the code sample


Code Sample

//Retrieving values from query string
String name;
//Retrieving from query string
name = Request.Param["umar"].ToString();

But remember that many browsers impose a limit of 255 characters in query strings. You need to use HTTP-Get method to post a page to server otherwise query string values will not be available.

   

40). How can we identify that the Page is Post Back?

Page object has an "IsPostBack" property, which can be checked to know that is the page posted back.

41). What is the lifespan for items stored in ViewState?

PThe items stored in ViewState live until the lifetime of the current page expires including the postbacks to the same page

42). What is AutoPostBack?

If you want a control to postback automatically when an event is raised, you need to set the AutoPostBack property of the control to True

43). Why do you use the App_Code folder in ASP.NET?

The App_Code folder is automatically present in the project. It stores the files, such as classes, typed data set, text files, and reports. If this folder is not available in the application, you can add this folder. One of the important features of the App_Code folder is that only one dll is created for the complete folder, irrespective of how many files it contains.

44). In which event of page cycle is the ViewState available?

After the Init() and before the Page_Load().

45). How long the items in ViewState exists?

They exist for the life of the current page.

46). Where the viewstate is stored after the page postback?

ViewState is stored in a hidden field on the page at client side. ViewState is transported to the client and back to the server, and is not stored on the server or any other external source.

  47). What are the different Session state management options available in ASP.NET?

1. In-Process
2. Out-of-Process.

In-Process stores the session in memory on the web server.

Out-of-Process Session state management stores data in an external server.  The external server may be either a SQL Server or a State Server.  All objects stored in session are required to be serializable for Out-of-Process state management.

48). What is the difference between web config and machine config?

Web config file is specific to a web application where as machine config is specific to a machine or server. There can be multiple web config files into an application where as we can have only one machine config file on a server.

49). What are the different types of cookies in ASP.NET?

Session Cookie – Resides on the client machine for a single session until the user does not log out.
Persistent Cookie – Resides on a user’s machine for a period specified for its expiry, such as 10 days, one month, and never.

50). What is the difference between ExecuteScalar and ExecuteNonQuery?

ExecuteScalar returns output value where as ExecuteNonQuery does not return any value but the number of rows affected by the query. ExecuteScalar used for fetching a single value and ExecuteNonQuery used to execute Insert and Update statements.

  

How to open new page in pre-fixed size in new window?

 protected void Button4_Click(object sender, EventArgs e)
    {
        Response.Write("<script>window.open('pincode_enquiry_web.aspx', '_blank', 'width=500,height=300');</script>");
    }

How to bind Table in asp.net?

example:

public DataSet ds;

protected void Page_Load(object sender, EventArgs e)
{
    ds = new DataSet();
    //load your ds from database
}

Following is the code to use table 0 from database to create table in aspx

<table>
<% foreach(DataRow row in ds.Tables[0].Rows) { %>
<tr>
<td><%= row["columnName"] %></td>
</tr>
<% } %>
</table>

Hope this helps

How to change dynamic connection string?

Change dynamic connection string.
--------------------------------------

using System.Configuration;
using System.Web.Configuration;
 
void ConfigurnewConnectionString(string server, string database, string userid, string password)
    {
 
        string str = "server=" + server + ";database=" + database + "; User ID=" + userid + "; Password=" + password + "";
        //Configuration myConfiguration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
        //str = System.Web.Configuration.WebConfigurationManager.AppSettings["myKey"];
        //myConfiguration.Save();
        System.Configuration.Configuration Config1 = WebConfigurationManager.OpenWebConfiguration("~");
        ConnectionStringsSection conSetting = (ConnectionStringsSection)Config1.GetSection("connectionStrings");
        ConnectionStringSettings StringSettings = new ConnectionStringSettings("conn", "Data Source=" + server + ";Database=" + database + ";User ID=" + userid + ";Password=" + password + ";");
        conSetting.ConnectionStrings.Remove(StringSettings);
        conSetting.ConnectionStrings.Add(StringSettings);
        Config1.Save(ConfigurationSaveMode.Modified);
        //Configuration myConfiguration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
        //myConfiguration.AppSettings.Settings.Item("myKey").Value = txtmyKey.Text;
        //myConfiguration.Save();
    }

 
Pass servername,databasename,userid,pasword from this function