What are the steps you will take to improve performance of a poor performing query?

Some general issues that you could talk about would be: No indexes, excess recompilations of stored procedures, procedures and triggers , poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

What are constraints? Explain different types of constraints

...Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults. Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY.

What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?

.....DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE 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 TABLE 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. 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. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

Index in sql server

//Important thing to note: By default a clustered index gets created on the primary key
and where are unique creates a nonclustered index by default

CREATE clusteredINDEX myIndex ON myTable(myColumn)

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

//link for Composite key understand

http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

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.

Access view state value in another page

//Find out the view state value in another page but you can find only Init and page load event and only redirect on another page Server.Transfer than you can get the value otherwise it will give error to you...

//first page
take one text box with name of textbox1.text and fill the value of text box.....

//second page init and load event
Page Poster = this.PreviousPage;
TextBox txtNewTest = (TextBox)Poster.FindControl("TextBox1");

string sDisplay = txtNewTest.Text;

Response.Write(sDisplay);