Sybase interview questions SET II:

1. While bcp in default,rules,constraints can be applied? Only default can apply


2. What is Hotspot?


3. When a multipe processes attempt to modify a same page in table How do I force the lock?Using Holdlock What is Isolation levels and explain them? The categories of locking behavior within transaction that are defined by ANSI,Level are 0,1,2,3


4. How to get Yesterday’s date? dataadd(dd,-1,getdate())


5. What is sp_depends? It is used to display a list of objects referenced by stored procedures


6. What is Normalization? Database is said to be normalized,If it compiles with a set of formal rules.these formal rules defines how data that represents different entities should be organized in adatabase.


7. How do I delete Duplicate rows in a table in a single Query? SELECT DISTINCT * into #Temparory_table from table_nameDELETE table_nameINSERT table_name(column list) select * from #temparory_table
   
8. If i want creation text of a particular object (such as SP,Trigger,view,rule,default) what shall i do other than sp_helptext and defncopy sp_helptext,defncopy,syscomments Compare Join and SubQuery in performance? Generally Join queries consume more memory than sub query. Sub query in turn involve intermediate table creation so affects performance, because of high I/Os fetches need for processing. If the RAM memory is more, then the Joins can be used instead of sub queries. Under memory constraints we can go for sub queries.Sometimes sub queries are flattened to join to improve the performance. Also making the outer query to compare inner query with equality option can materialize the subquery.
   
9. Explain Worktables for performance? a) If column has a unique/primary key, than there is no need to specify the distinct clause. b) If you have more than four tables to joins, it will definitely use a worktable to resolve the query. Try to keep the number of tables to join to minimum. Try to create subquery to avoid join of more than four tables. c) If there is an index on a column, which keeps the column sorted, you don't need to specify order by clause in the query. d) If you know that data is unique, than there is no need to specify the group by clause in the query.


10. There's a performance issue What will be your very first step towards the solution? First you need to identify which query is creating the problem and than to identify where the problem lies in that query. Query that is taking maximum time is the first you want to consider for the optimization. To check which query is taking maximum time to execute place getdate() both before and after the query. Identity the type of the query whether it is a data look-up query, or data modification query.