Sybase Interview Questions and Answers : Part 1

List out the limitation on stored procedures?     

SP may not create view,defaults,rules.trigger or procedures ,you can create table ,A table cannot be created,droppedor recreated with same name in single procedure,Sp are reusable,recursive but not reentrant

Tell me the limitation of the Trigger?      

any create commend,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger

what is difference between constraints and rules?         

rules deals with constant and one rule can bound one column,constraints can bound many column and it can be compare with column in another table

What are the steps involved in creating cursor?

Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors

What is Demand lock and Dead lock?     

A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources. Demand Lock:A demand lock prevents any more shared locks from being set on a data resource (table or data page). Any new shared lock request has to wait for the demand lock request to finish.

What are the modes of transaction? And how do you change from one mode to another?

unchained mode: The default mode, called unchained or Transact-SQL mode, requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction. chained mode: The SQL standards-compatible mode, called chained mode, implicitly begins a transaction before any data retrieval or modification statement. These statements include: delete, insert,

open, fetch, select, and update. You must still explicitly end the transaction with commit transaction or rollback transaction. You can set either mode using the chained option of the set command.

what is command permission and object permission?    

OP is Grant/revoke to these objects for permissions which includes select,update,delete,execute.CP is grant /revoke permission for create (db,table,view,procedure,defaults) 

What is Index Covering?              

Index covering is a mechanism for using the leaf level of nonclustered index the way the data page of a clustered index would work. Index covering occurs when all columns referenced in a query are contained in the index itself. 

What is direct update and deffered update?      

Direct Update:An update operation that takes place in a single step, that is, the log records are written and the data and index pages are changed. Direct updates can be performed in three ways: in-place update, on-page update, and delete/insert direct update.

Deffered Update:An update operation that takes place in two steps. First, the log records for deleting existing entries and inserting new entries are written to the log, but only the delete changes to the data pages and indexes take place. In the second step, the log pages are rescanned, and the insert operations are performed on the data pages and indexes.

How will you minimizse the Lock contention?    

keep Transaction as short and concise as possiable,keep transaction in single batch,consider running transaction in stored procedure,commit update in cursor frequently,avoid hotspots.

How will you find second maximum value in a table?       select max(column1) from table where column1<(select max(column1) from table)

what are different global variables ?      

@@tranchained-returns the current transcation mode.

@@sqlstatus-status of previous fetch statement in cursor.

Compare 2 nd and 3 rd Normal form?    

Second normal form-nonkey fields must depend on the entire pr. Key.

Third normal form---nonkey fields must not depend on other nonkey fields.

What is –I option in Sybase tools?           

It is the name of the interface file to use when trying to find a server to connect to

What is Phantom reads?              

when one transaction reads a set of rows that satisfy a search condition, and then a second transaction modifies the data (through an insert, delete, update, and so on). If the first transaction repeats the read with the same search conditions, it obtains a different set of rows.

Explain Denormalization techniques?    

Denormalization is used for performance boosting. In which normalized db is denormalized for the sake of performance. According to normalization all columns has to depend on the primary key, but some times creating a manipulated field.

Redundant column may be used in a table when we always join the first table to get a column data from the second table using foreign key. Data partitioning techniques like vertically splitting the tables, horizontally splitting the tables can be used to denormalize a db. Vertical split technique is typically used to minimize the size of the primary table or to move infrequently accessed columns to a separate table. Horizontal split technique involves splitting the table at row level into two or more tables. For ex, if we can split the table into two tables, so that the first table has the recent entries and a table that has historical data, we can drastically increase the size of the new table, whose columns are going to access frequently