Sybase Questions set

Questions

What happens to data when a clustered index is created? What happens if non-clustered index is created?

There is non-clustered index on a table.I create a clustred index on the same table without dropping the non-clustred index.what will happen to the data?Wether one index affect the other?why?

How do you force the omptimizer to use the index for a table?

What are the difference between clustered and Non clustered index?

Can you tell me the scenarios when you used the two types of index ?

What are the different types of Indexes?

There is an index on lname and another index on(lname,fname) in a table .if we write a query to select lname like 'smith' which index will be used if it is used?

How do you add an index to a column?

What is the difference between primary key and indexes?

How many clustered and non clustered index on a table?

How to sort data in a table in a table without using order by and without using indexes?

How many indexes can you have in a table and in a column?

Can you create indexes on temporary tables?

Consider in table1 -empid has numeric value and table2 -empid have int value if there is query that joins the two tables which indexes will be used and why?

What is the use of clustered and non clustered index?

How do you list out all the indexes on a table?

Suppose an index has a composite key(a,b,c,d) and we do a select on b and c will it use the index?

How many index a table can have?

What is pros and cons of indexes?

Give me the advantage and disadvantage of forcing the index?

Why index are used in select query what is the reasons?

If you have an index on five column do you have to specify all the five in where clause?

I have a query using 'like' to search on a column ,The table having 10 million rows ,which index should I have on that column to optimise my search?

A table contains EmpId ,name,dept and gender there are two indexes namely on(dept,gender) and (gender,dept) now you write a query select * from table where dept=" " and gender="" which index would it take and when?

You have table with column just contain value like 'male' or 'female' ,what index would you apply and why?

How would you implement a merge sort in indexes?

Trigger

What is a Trigger?

How many trigger can you have on a table? What are they?

You are inserting a million rows into a table having an insert trigger .will the trigger execute a million times?

What are magic table and where are they accesiable?

How do you create a Trigger?

What are the different types of Trigger?

Why we need Trigger?

How Trigger checks for a particular data update?

What are the Trigger advantage and disadvantage?

How do find how many rows are deleted using delete command?

Explain the Trigger in a Update scenario?

What happen if you create delete trigger on a table which have aleady delete trigger?

How to find out trigger on tables?

How to get program listing of trigger on a tables?

Why do we need Trigger?

Compare truncate and trigger?

What are he trigger upper limits on a table?

Which one will fire first trigger or Referential integrity?

What is the difference between Trigger and Cursors?

What are the limitation on Trigger?

Global Variables

What are the different global variables?

For cursor what is the possible values of @@sqlstatus?

How do you get the version of the sybase?

How do you view only top 30 rows of the table?

What is @@identity?

System Stored Procedures

What is sp_helpdb,sp_helpindex?

What is sp_who?

How do you check the text of the SP?

How do you find the size of the database in sybase?

What is sp_dboption?

Sybase Tools

What are the types of BCP?Explain their difference?

How can I select the first 500 rows from a table having million rows & generate a flat file?

Is the trigger fires during BCP in?

There is delete trigger on a table and there is no indexes wether server use fastbcp or slowbcp?

Is it possible to lookup the missing data before loading it into sybase tables?

What sort of checks can be done in BCP?

How do handle error in BCP?

How do you upload the data?

How would you transfer certain number of rows from table to file?

How data validation works before bcp?

In which case fast bcp occurs?

which one of the following gets fired when you do a bcp into a table from a file?defaults,rules,trigger,constraints?

What is the option for identity column in bcp?

What steps did you take to tune the BCP?

When should we use BCP and Insert statements to modify table ?

How do you send a selected rows into a file?

In bcp how would I specify that only one error is allowed?

In bcp how would I specify that commit size?

How would you transfer 1000 rows from 10000 rows using BCP?

How do you do fast bcp with trigger and indexes on the table?

How do you copy output to a file using isql?

What is the default field delimeter in bcp?

How does the following command know host and port number of the server isql -S?

What does -E mean in BCP?

How do you handle error in bcp?

How can you bcp only selected set of rows from a table to file with some conditions?

How do you write an sql from sybase server to os file?

What is interface file?

How do you do bcp in to table from a file which has 2 columns seperated by a comma and one of the column has values with in quotes?

What is the latest tools that can be used to improve the performance?

Locks

What are the different locks in sybase?

How to detect deadlock,how do you avoid the deadlock,What will sybase do when deadlock occurs?

How do you trace the deadlock?

what is lock escalation?

what is row level and page level locking?

What is lock?

What is row level,page level and table level locking which is better and why?

How do you avoid Deadlock?

How many types of locks are there in sybase?

What are Locks?

What kind of lock will be put on data while performing insert,update or delete?

How do you determine the different locks aquired on a table?

How can you force the lock on a table?

What is the locking level in sybase?

How do you move from page level to table level lock?

How to see the types of locks on all tables?

What would be the status of sp_lock in a deadlock?

How to see deadlock information in sybase?

What is demand locks?

What is the advantage of table level over row level lock?

Compare deadlock and Blocking?

Performance Tunning

You have a UpdateTrigger on a table containing a million rows, I update all rows in the table.What will be the performance issues?

If one of my updations fail what is your solution towards it?

Explain the steps involed in Tunning the Databases?

How do you Tune a SP ? Explain all steps?

What is your first steps towards Query optimizing?

How did you know index was the cause of the perfomance degration?

What is show plane used for?

What is Ghost Record?

What is optimization?

Why denormailzation is needed?

What is Normailzation?

If we are joining two tables and what matter join is not working propely suggest some efficient manner to solve it?

What are the performance issues in cursor?

How do you force the join order?

What is index covering?

What is Update Statistics?

where do you use update statistics and why?

What are the reasons for a query to run slow?

When is subquery used or avoided?

What are the techniquies followed in denormailzation?

What does set rowcount do?

How do you restrict multiple users from using a particular table to avoid discrepencies/overlapping of table values?

What Indexing will be better for larger master table?

Which one we prefer joins or subqueries?

What is distribution page?

What does reformatting mean in the output of the showplan output?

What does deffered in the output of the show plan ?

When we use set statistics time on ,what are the different times we see?

I have two tables item and order are newly created without any rules,constraints etc. and I run a query involing both tables it run s very slow what can I do about it?

What is proactive Tunning?

What is reactive Tunning?

Whan would you prefer to do use a stored procedure than a Trigger?

What happen when you are deleting 100 rows?

Explain about dbcc 3604 and dbcc 3605?

Isolation Levels

What are the Isolation levels?

Can you set the isolation level for single query?

What is different between level 2 and level 3 in Isolation?

How shall I stimulate from level0 to level3?

What is dirty read and how it is deferred from phantom read?

What is Isolation level 3?

Built In Function

Name some of the Buit in functions?

Tell me about what are the different String functions in Sybase?

How will you find the pattern in a string on sybase?

When you execute the query select xx(G,'ABCDEFGHI'),what is xx?

How do you calculate last month's day?

How to get the yesterday's date?

Transaction

How does sybase know to what extent a transaction has to be rolledback?how do you check wether the rollback was correct or not?

During updating the table can you access the previsoly held data?How is it internally stored in Sybase?

There are million rows to be inserted & the transaction log gets filled up soon . How do you avoid this situation?

What is Transaction?What happen internally when a transaction take places?

What is Transaction Blocks?

How do take care of transaction while updating tables in batches?

If you have to rollback when a batch updation fails,how would you determine to what extent you have to rollback?

What is the use of checkpoint?what happen internally when a checkpoint is used?how does sybase rollback a transaction to the checkpoint ?

How do you truncate transaction log?

A situation aries where two user are updating a table Simultaneously,How do you maintain the data consistency?

What is savepoint?

What happens if the last record is having an error how many records would be updated?

Compare chain and unchain mode in Transaction?

How do you know who are the user logged in the database?

What are the way to figure out that transaction log is full?

What is commit?

What is Transaction log and how is it maintained in syabse?

What are the different modes of transaction?

In Transaction table1 has unique values,table2 has id which has duplicate rows .begin tran insert into table1(id) select id from table2 commit ,what will happen?

Explain about transaction save points and check points?

What is transaction management?

What is the default transaction level?

I am doing an update on table and a trigger is fired which fails then is the update rolled back?

How does sybase handle rollback?

If you have to begin,commit and rollback tran in your query ,can you create the temp table in that transaction?

Why the transaction log is called write a head log?

what is the difference between log segment and data segment?

How do you find out that transactio log is full?

What is the default transaction level?

I am doing an update on table and a trigger is fired which fails then is the update rolled back?