Answer / bhaskar

• Table should have primary key
• Table should have minimum of one clustered index
• Table should have appropriate amount of non-
clustered index
• Non-clustered index should be created on columns of
table based on query which is running
• Following priority order should be followed when
any index is created a) WHERE clause, b) JOIN clause, c)
ORDER BY clause, d) SELECT clause
• Do not to use Views or replace views with original
source table
• Triggers should not be used if possible,
incorporate the logic of trigger in stored procedure
• Remove any adhoc queries and use Stored Procedure
instead
• Check if there is atleast 30% HHD is empty - it
improves the performance a bit
• If possible move the logic of UDF to SP as well


• Remove * from SELECT and use columns which are only
necessary in code
• Remove any unnecessary joins from table
• If there is cursor used in query, see if there is
any other way to avoid the usage of this (either by SELECT
… INTO or INSERT … INTO, etc)
There are few hardware upgrades can be considered as well
like separating index on different disk drive or moving
tempdb to another drive. However, I am not suggesting them
here as they are not quick way to improve the performance
of query.

How to how to convert numeric expression data types using the convert() function??

0 Answers  

---

How do I determine how many instances of sql server are installed on a computer?

0 Answers  

---

When multiple after triggers are attached to sql table, how to control the order of execution?

0 Answers  

---

How will you find out if there are expensive SQL statements running or not?

0 Answers   Atos Origin,

---

How to get number of days in a given year?

4 Answers   ADP,

---

I am having table Title with TITLE id,Author ID, Publiser ID , table AUTHOR with Author ID, Author Name, table PUBLISER with Pub ID, Pub name Here hot to find out the publiser id who have relesed the books of patriticular author?

3 Answers  

---

How much memory that we are using in Logshipping Concept?

0 Answers  

---

List the various tools available for performance tuning?

0 Answers  

---

Define indexes?

0 Answers  

---

What is the difference between char, varchar and nvarchar?

0 Answers  

---

Can you tell me some of the common replication dmv's and their use? : sql server replication

0 Answers  

---

Give some Scenario for Non Clusterd index? Can we write system defined functions in side The Function? Wat is the Unique Datatype?

0 Answers   Value Labs,

---