Sybase Interview Questions and Answers

Questions	Answers
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
compare Rollback transaction and Rollback Trigger?	Explained
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
Compare Subquery and Correlated Subquery?	Sub query : A select statement that is nested inside another select, insert, update or delete statement, or inside another subquery. Correlated subquery: A subquery that cannot be evaluated independently, but that depends on the outer query for its results. Also called a repeating subquery, since the subquery is executed once for each row that might be selected by the outer query.
Advantage of Stored Procedure?	faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency
can I update view?	yes,but view had created using only one table than update is possiable
explain about @@sqlstatus?	It returns the status of the pervious fetch statement in a cursor
What is the disadvantage of CURSOR?	Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors.
What are the Properties of Transaction?	server maintain transaction log,server locks table pages during transaction,server performs automatic recovery upon restart ,transaction control statement
How will bcp out the Identity column?	-E
What is lock escalation?	Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).
How do I get the Duplicate rows from a table?	select * from table name group by column1 having count(*)>1
What is Checkpoint?	The point at which all data pages that have been changed are guaranteed to have been written to the database device.
What is Distribution page?	server keeps distribution information for each index on a separate page in the datebase
How shall I simulate from level 0 to level 3 in Isolation?	using holdlock
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.
What is ceiling()?	Smallest integer greater than or equal to specified value
What is the stored procedure to view current lock and processes respectively?	sp_lock and sp_who
Compare IN and EXISTS?	in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns
What is Rollback trigger in a Trigger?	You can roll back triggers using either the rollback trigger statement or the rollback transaction statement (if the trigger is fired as part of a transaction). However, rollback trigger rolls back only the effect of the trigger and the statement that caused the trigger to fire; rollback transaction rolls back the entire transaction
What happen when we delete the table using Trigger?	The delete row will entered into the Deleted table inside the trigger
What is the Drawbacks of Normalization?	Although most successful databases are normalized to some degree, there is one substantial drawback of a normalized database: reduced database performance. The acceptance of reduced performance requires the knowledge that when a query or transaction request is sent to the database, there are factors involved, such as CPU usage, memory usage, and input/output (I/O). To make a long story short, a normalized database requires much more CPU, memory, and I/O to process transactions and database queries than does a denormalized database. A normalized database must locate the requested tables and then join the data from the tables to either get the requested information or to process the desired data. A more in-depth discussion concerning database performance occurs in Hour 18, "Managing Database Users."