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.

What are the transaction properties?

0 Answers  

---

How to see existing views in ms sql server?

0 Answers  

---

How to create new tables with "create table" statements in ms sql server?

0 Answers  

---

How do I find the port number for sql server?

0 Answers  

---

What are the different acid properties?

0 Answers  

---

What is user-defined function?

0 Answers  

---

How to convert numeric expression data types by assignment operations?

0 Answers  

---

How fixed length strings are truncated and padded?

0 Answers  

---

What are the limitation of the Online Index Rebuild Operation?

1 Answers  

---

What is de-normalization and when do you do it?

3 Answers  

---

What is the Main Difference between ACCESS and SQL SERVER?

0 Answers   MindCracker,

---

Difference between LEN() and DATALENGTH() in sql server ?

0 Answers   HCL,

---