Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thaniparthi.rao
Yak Posting Veteran
96 Posts |
 Posted - 2008-03-30 : 04:04:14
|
| Hello i attended one interview . I have some doubts 1.) Indexes decreases the performance for insert ,UPDATE commands.? what will u do to increase the performance with these statements. 2.).tell me the 5 steps do u consider while writing a stored procedure to increase the performance.? 4).Is truncate a DDL or DML operation ?5.) How to delete duplicate rows from a table?6).table1 has an identity coluimn with 5 rows. delete opearation is performed on table 1.If user enters a new row what wiil be identity column no 1 or 6? table2 has an identity coluimn with 5 rows. truncate opearation is performed. .If user enters a new row what wiil be identity column no 1 or 6?7.) when do we go for sub queries and when do we go for joins ?8) could me tell the difference between sp and function.?waiting for ur replies.SRIPATHI |
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-30 : 05:51:12
|
| Which company it was & how big it is?What do you want to know from us?You want to know the answers? |
 |
|
|
thaniparthi.rao
Yak Posting Veteran
96 Posts |
Posted - 2008-03-30 : 05:56:20
|
| ACCENTURE Yes i want to know the answers |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-03-30 : 06:28:38
|
| Answer 1Dont use indexes on columns that are frequently modified.Use indexes on a columns that are mostly used in a where condition.Answer 2Avoid cursors & loops.Always Set No count on.Use proper locks and isolation levels.Avoid use of temporary tables.Avoid Dynamic SQL until absolutely necessary.Answer 3Truncate is a DDL while delete is a DML.Answer 4Delete t from(Select duplicatecolumn,row_number over(partition by duplicatecolumn order by duplicatecolumn desc)as rowid from yourtable)t where rowid<>1Answer 5If truncate is performed the next value in the identity column will be from 1 but if delete is performed then the new value will be next value from the last deleted value.So answer will be 6 for delete and answer will be 1 for truncate.Answer 6Use joins when you need to have the columns from a table to which you are joining to else use sub queries.Sub queries should be avoided until absolutely necessary.Answer 7Major difference is that function cannot physically manipulate data in a table of a database. |
|
|
|
|
|
|
some doubts