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 is the difference between Temporary table variable and a Table variable? Or Which Table variable I should use inside Stored procedure?

3 Answers   Microsoft, TCS, Techastrum,

---

Why do we use sql limitations? Which constraints can we use while making a database in sql?

0 Answers  

---

CREATE TABLE [dbo].[HPMS_CompetencyTypes](CompetencyType varchar(50) ) go create trigger hpms_create_Insert on HPMS_CompetencyTypes for insert as if Exists ( select * from [HPMS_CompetencyTypes] where CompetencyType=(select * from [HPMS_CompetencyTypes])) begin Rollback tran Raiserror ('duplicate value',12,6) go insert HPMS_CompetencyTypes (CompetencyType) values ('new') I'm new to trigger can any one tell me where is the issue. Please.

2 Answers  

---

How to get number of days in a given year?

4 Answers   ADP,

---

Difference between drill down and drill through report.

0 Answers  

---

What is the use of RDBMS?

0 Answers   Wipro,

---

What are the different ways you can create databases in sql server?

0 Answers  

---

How to Debug a Stored Procedure?

5 Answers   Allianz,

---

What is a view? is View updatable?

17 Answers   IBM, L&T,

---

What will be the maximum number of indexes per table?

0 Answers  

---

What is database replicaion? What are the different types of replication you can set up in SQL Server?

5 Answers   Aptech, HCL, Perpetual, SAIC,

---

What happens if strings are casted into wrong code pages in ms sql server?

0 Answers  

---