Sybase Interview Questions and Answers X
| Sl.No | Questions | Answers |
|
|
|
|
| 1 | In a table A have name and id,table B have Id and deposit and table C have id and withdrawal ,write a query to retrive the name and final amount I.e deposit-withdrawal | select name,b.deposit-c.withdrawl from a,b,c where a.id=b.id and b.id=c.id |
| 2 | A)Is the Stored Procedure is reentrant ? B) What is not reentrant? | It is not reentrant ,only one user can access SP one at the time |
| 3 | A)What is the use of creating view with check option? B)Can I update View? | A)Check flag prevent insertion or updation of a rows that will subsequently not meet the view criteria B) Yes |
| 4 | explain about @@spid? | current proccess id |
| 5 | A)What is the performance issue on CURSOR? B)What are the step involved in creating Cursor? | A)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. B)Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors |
| 6 | A)What are the Properties of Transaction? B) What is Chained Mode in Transaction? | server maintain transaction log,server performs automatic recovery upon restart ,server locks table pages during transaction,transaction control statement |
| 7 | what is -A option on bcp? | change the network packet size for bcp session |
| 8 | How would you update SP if the tables are modified? | we have to drop the Sp and recreate it |
| 9 | A)How will you minimizse the Lock contention? B)What is Hotspots? | 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. |
| 10 | What is optimistic locking? | Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
|
| 11 | What is -X option in defn copy? | password encryption |
| 12 | You have two tables a and b with column c1 in both of them. Column
c1 in table a has values 1, 2 and 3 and the column in tables b has values 3, 4, 5. How many rows would the following query return? select * from a, b where a.c1 <> b.c1 | 1row |
| 13 | What happen when we have cascading effect on unique index ? | optimizer will go for deffered update |
| 14 | A) What are the types of index? B)What is the difference between Clustered and non clustered index on performance wise? | B)When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater,clustered index is better for range queries,more duplicates in table and non clustered for single row lookup.one clustered index and 249 non clustered index. |
| 15 | What is Checkpoint? | The point at which all data pages that have been changed are guaranteed to have been written to the database device.
|
| 16 | A)What is deadlock ? B) how it is differed from demand lock? | A)A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources. B) 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. |
| 17 | A)What is dirty read ? B)how it is deferred from phantom read? | A) Such reads occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change. If the first transaction rolls back the change, the information read by the second transaction becomes invalid
B)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. |
| 18 | Why would we use stored procedures over triggers for optimizing
queries? | A trigger gets fired for every transaction. It is another overhead in the query. Depending on how important the query is you can drop triggers and write a store procedure for that. And at end of the day you can run that stored procedure to perform the operations performed by the trigger. |
| 19 | what is str()? | numeric to character conversion |
| 20 | How do you list out all the indexes? | sp_helpindexes |
| 21 | What is Rollback trigger and how its differed from rollback Tran? | 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 |
| 22 | 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."
|
| 23 | What is the difference in creating Temp table using select into and create command? | select into will create with rows and it is faster ,create command will create only table structure. |
| 24 | How do you delete the identity gaps in identity columns? | bcp out all the rows to a flat file and truncte the table,then bcp in the rows from file to that table |
Sybase Interview Questions and Answers IX
| Sl.No | Questions | Answers |
|
|
|
|
| 1 | What is the difference between decimal and Float datatypes? | Float is approximate numeric datatypes and precision value is 15 digits,Decimal in exact numeric datatype and precision value is 28 |
| 2 | The table Emp with (name,dept,id,salary) find employee name who is having max salary among all the emp in dept wise. | select * from emp group by dept having max(salary) |
| 3 | A)What are the limitation while passing the parameter in stored procedures? B)What is Default paameter in SP? | A)Parameters name may be upto 29 character in length,upto 255 parameter may be defined,rules,defaults and column properites may not be applied but wildcard can applied,text,image datatype can pass trough for read only , B)While we create sp we have to assgin the default value for the parameter. |
| 4 | How do you list out all the Rules and Defaults in your database? | select name from sysobjects where type ='D' or type='R' |
| 5 | A)How the Stored Procedure is said to be Faster execution ? B)What are the advantage of SP? | A)Sp after their first execution become memory resident and no need to reparsed,reoptimized or recompiled . B)faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency |
| 6 | A)What are limitation on view that cannot include? B)Can I drop a view? | select into,compute clause,union,order by.yes |
| 7 | A)What are the limitation of Trigger? B)What happen when we update the table using Trigger? | A)any create commend,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger . B)The delete row will entered into the Deleted table and newly inserted row will enter into the inserted table and base table inside the trigger |
| 8 | explain about @@transtate? | returns the current transaction state. |
| 9 | A)what are the steps involed in creating cursor? B)What is the syntax for cursor declaration for update? | A)Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors B)declare cursor_name cursor for select_statement for update [of col_list] |
| 10 | A)What is nested transaction ? B)In Which Mode nested transaction is eliminated? | A)You can nest transactions within other transactions. When you nest begin transaction and commit transaction statements, the outermost pair actually begin and commit the transaction. The inner pairs just keep track of the nesting level. Adaptive Server does not commit the transaction until the commit transaction that matches the outermost begin transaction is issued. Normally, this transaction "nesting" occurs as stored procedures or triggers that contain begin/commit pairs call each other,The @@trancount global variable keeps track of the current nesting level for transactions. B) Chained Mode
|
| 11 | How can I select the second 500 rows from a table having million rows & generate a flat file? | BCP -F and -L |
| 12 | A)What is Hotspot ? | Whan a multipe processes attempt to modify a same page in table,It can be resolved using heap table and clustered index |
| 13 | If I want current host computer name of client process? | host_name() |
| 14 | What are the SP related with syskeys? | sp_primarykey,sp_common key |
| 15 | A)What is the difference between isolation level 2 and 3? B)In which level Dirty read is eliminated? | A)Level 2 allow phantom read and prevent dirty read and non repeatable read and always go for page level locks: Level 3 prevents all the three reads and go for table level locks B)Level 1,2,& 3 |
| 16 | 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. |
| 17 | What is str()? | gives the sustring from string |
| 18 | How do you handle error in BCP? | -e |
| 19 | Explain -Z option in defncopy? | No Answer |
| 20 | Explain me the I and II normal form ? | Normal I : all repeating groups have to moved into separate tables and one column contain exactly one value. Normal II: A nonkey fields must depend on the entire primary key. |
Sybase Interview Questions and Answers VIII
| Sl.No | Questions | Answers |
|
|
|
|
| 1 | In a table A have name and id,table B have Id and deposit and table C have id and withdrawal ,write a query to retrive the name and final amount I.e deposit-withdrawal | select name,b.deposit-c.withdrawl from a,b,c where a.id=b.id and b.id=c.id |
| 2 | A)Is the Stored Procedure is reentrant ? B) What is not reentrant? | It is not reentrant ,only one user can access SP one at the time |
| 3 | A)What is the use of creating view with check option? B)Can I update View? | A)Check flag prevent insertion or updation of a rows that will subsequently not meet the view criteria B) Yes |
| 4 | explain about @@spid? | current proccess id |
| 5 | A)What is the performance issue on CURSOR? B)What are the step involved in creating Cursor? | A)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. B)Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors |
| 6 | A)What are the Properties of Transaction? B) What is Chained Mode in Transaction? | server maintain transaction log,server performs automatic recovery upon restart ,server locks table pages during transaction,transaction control statement |
| 7 | what is -A option on bcp? | change the network packet size for bcp session |
| 8 | How would you update SP if the tables are modified? | we have to drop the Sp and recreate it |
| 9 | A)How will you minimizse the Lock contention? B)What is Hotspots? | 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. |
| 10 | What is optimistic locking? | Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
|
| 11 | What is -X option in defn copy? | password encryption |
| 12 | You have two tables a and b with column c1 in both of them. Column
c1 in table a has values 1, 2 and 3 and the column in tables b has values 3, 4, 5. How many rows would the following query return? select * from a, b where a.c1 <> b.c1 | 8 rows |
| 13 | What happen when we have cascading effect on unique index ? | optimizer will go for deffered update |
| 14 | A) What are the types of index? B)What is the difference between Clustered and non clustered index on performance wise? | A) Clustered and Non clustered index B) B)When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater,clustered index is better for range queries,more duplicates in table and non clustered for single row lookup.one clustered index and 249 non clustered index. |
| 15 | What is Checkpoint? | The point at which all data pages that have been changed are guaranteed to have been written to the database device.
|
| 16 | A)What is deadlock ? B) how it is differed from demand lock? | A)A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources. B) 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. |
| 17 | A)What is dirty read ? B)how it is deferred from phantom read? | A) Such reads occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change. If the first transaction rolls back the change, the information read by the second transaction becomes invalid
B)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. |
| 18 | Why would we use stored procedures over triggers for optimizing
queries? | A trigger gets fired for every transaction. It is another overhead in the query. Depending on how important the query is you can drop triggers and write a store procedure for that. And at end of the day you can run that stored procedure to perform the operations performed by the trigger. |
| 19 | what is str()? | numeric to character conversion |
| 20 | How do you list out all the indexes? | sp_helpindexes |
| 21 | What is Rollback trigger and how its differed from rollback Tran? | 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 |
| 22 | 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."
|
| 23 | What is the difference in creating Temp table using select into and create command? | select into will create with rows and it is faster ,create command will create only table structure. |
Sybase Interview Questions and Answers VII
Sl.No Questions Answers
1 A)How do find how many rows are deleted using trigger? A)By checking no rows entered into the deleted table inside the trigger
2 How do you create empty copy of an existing table ? Select * into table1 from table2 where 1=2
3 How do you get the value return by the stored procedures? Using OUTPUT keyword
4 A)What is the difference between a return (0) and a return(X) ,X being any integer in a SP? B) What are the limitation Paramters in SP? A)every SP automatically returns an integer status value:0 is returened on successful compltion,-1 to -99 are returned for sql server detected error B)Parameters name may be upto 29 character in length,upto 255 parameter may be defined,rules,defaults and column properites may not be applied but wildcard can applied,text,image datatype can pass trough for read only
5 A)Can I update view? B)What are the limitation on view ? A)yes,but view had created using only one table than update is possiable B)View can have aggregate function,joins,distinct clause but not include select into,compute,union,order by clause
6 Explain about @@identity? return the lastly inserted identity value
7 What is Timestamp? "timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
"
8 A)what is Cursor? B) What is CURSOR and performance? "A) Its a Pointer used for row by row operation . B)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.
"
9 A)What is DeadLock? B)What is error code for Deadlock? "A) A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resourcesB)errorcode for dead lock is 1205 and for stored procedure is -3.
"
10 What are the stored procedure that are related with syskeys? sp_primarykey,sp_forigenkey,sp_commonkey
11 A) How do I force the Lock ? A)Using Hold Lock
12 There are three tables A(col1,col2),B(col1,col2),C(col1,col2,col3) what will be the result Select * from A,B,C where A.col1=B.col1 no of rows selected by A.col1=B.col1 * no of rows in C
13 Write a query to convert the date to dd/mm/yy? "select convert(char,date_exp,101)
"
14 What is Distribution page? server keeps distribution information for each index on a separate page in the datebase
15 A)What is the difference between isolation level 2 and 3? B)In which level Non Repeatable read is eliminated? A)Level 2 allow phantom read and prevent dirty read and non repeatable read and always go for page level locks: Level 3 prevents all the three reads and go for table level locks B)Level 3
16 A)Which one we have to create first clustered or non clustered index ? A)Clustered index
17 Find the name from EMP which strats with a or b or c. select name from EMP where name like'[abc]%'
18 What is replicate()? Repeats char_exp, int_exp times
19 What is -T option in bcp? This is the size of text or image data,default size is 32KB
20 Explain -E in isql? It is used to specfiy an editor,vi is default
21 How do you transfer the Table syntax to a file ? using isql we can do it
23 What is the drawaback 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.""
"
1 A)How do find how many rows are deleted using trigger? A)By checking no rows entered into the deleted table inside the trigger
2 How do you create empty copy of an existing table ? Select * into table1 from table2 where 1=2
3 How do you get the value return by the stored procedures? Using OUTPUT keyword
4 A)What is the difference between a return (0) and a return(X) ,X being any integer in a SP? B) What are the limitation Paramters in SP? A)every SP automatically returns an integer status value:0 is returened on successful compltion,-1 to -99 are returned for sql server detected error B)Parameters name may be upto 29 character in length,upto 255 parameter may be defined,rules,defaults and column properites may not be applied but wildcard can applied,text,image datatype can pass trough for read only
5 A)Can I update view? B)What are the limitation on view ? A)yes,but view had created using only one table than update is possiable B)View can have aggregate function,joins,distinct clause but not include select into,compute,union,order by clause
6 Explain about @@identity? return the lastly inserted identity value
7 What is Timestamp? "timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
"
8 A)what is Cursor? B) What is CURSOR and performance? "A) Its a Pointer used for row by row operation . B)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.
"
9 A)What is DeadLock? B)What is error code for Deadlock? "A) A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resourcesB)errorcode for dead lock is 1205 and for stored procedure is -3.
"
10 What are the stored procedure that are related with syskeys? sp_primarykey,sp_forigenkey,sp_commonkey
11 A) How do I force the Lock ? A)Using Hold Lock
12 There are three tables A(col1,col2),B(col1,col2),C(col1,col2,col3) what will be the result Select * from A,B,C where A.col1=B.col1 no of rows selected by A.col1=B.col1 * no of rows in C
13 Write a query to convert the date to dd/mm/yy? "select convert(char,date_exp,101)
"
14 What is Distribution page? server keeps distribution information for each index on a separate page in the datebase
15 A)What is the difference between isolation level 2 and 3? B)In which level Non Repeatable read is eliminated? A)Level 2 allow phantom read and prevent dirty read and non repeatable read and always go for page level locks: Level 3 prevents all the three reads and go for table level locks B)Level 3
16 A)Which one we have to create first clustered or non clustered index ? A)Clustered index
17 Find the name from EMP which strats with a or b or c. select name from EMP where name like'[abc]%'
18 What is replicate()? Repeats char_exp, int_exp times
19 What is -T option in bcp? This is the size of text or image data,default size is 32KB
20 Explain -E in isql? It is used to specfiy an editor,vi is default
21 How do you transfer the Table syntax to a file ? using isql we can do it
23 What is the drawaback 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.""
"
Sybase Interview Questions and Answers VI
| Sl.No | Questions | Answers |
|
|
|
|
| 1 | 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. |
| 2 | A)What is the use of creating view with check option? B) Can I drop a View? | A)Check flag prevent insertion or updation of a rows that will subsequently not meet the view criteria B)yes |
| 3 | A)Explain about @@sqlstatus? B)What are possiable value of @@sqlstatus? | A)It returns the status of the pervious fetch statement in a cursor B)0,1,2 |
| 4 | A)What is the performance issue on CURSOR? B)What are the steps involed in creating the cursor? | A)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.
B)Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors |
| 5 | A)What are the Properties of Transaction? B)What are the modes of transaction ? | A)server maintain transaction log,server locks table pages during transaction,server performs automatic recovery upon restart ,transaction control statement B)Chained and Unchained |
| 6 | How will bcp out the Identity column? | -E |
| 7 | A) what are the two modes in BCP? B)explain about how to achive fast BCP? | B)Table does'nt have indexes and trigger and we have to set select into bulk copy option for the particular database |
| 8 | Can I transfer table syntax from database to flat file using defncopy? | No ,It can be done using isql |
| 9 | A)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). |
| 10 | A)What is optimistic locking? | Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
|
| 11 | What is Checkpoint? | The point at which all data pages that have been changed are guaranteed to have been written to the database device.
|
| 12 | There's a performance issue What will be your very first step towards the solution? | I will run the Update Sataiscis and 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.
|
| 13 | A)What is Phantom Reads? | A)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.
B) Such reads occur when one transaction reads a row and a second transaction modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield different results than the original read. |
| 14 | 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.
|
| 15 | What is datediff? | return the date difference accordding to the datapart |
| 16 | What is the system procedure to view current lock and processes respectively? | No Answer |
| 17 | Compare not IN and not EXISTS? | in allow duplicate values and sub query have one column,exists not allow duplicates and inner query can have multi columns |
| 18 | A)What is Rollback trigger ? B) how it is differ from Rollback transaction? | 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 |
| 19 | A)What happen when we delete the table using Trigger? B) What are the limitation of Trigger? | A)The delete row will entered into the Deleted table inside the trigger B ) any create commond,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger |
| 20 | 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.
|
| 21 | How do you call the remote SP? | severname.database.owner.Spname |
| 22 | Compare Truncate and Delete? | DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back. |
| 23 | Explain sp_recompile? | Sp_recompile is used when updated statistics have been updated on the table and when an index has been added to a table that you want the optimizer to consider to generate new query plan for the SP |
| 24 | What is Sp_dboption? | It is used to enable or diable option for database |
| 25 | Write a query for selecting all persons with lastname like 'smith'(anycase)from a table? | select lastname from table where lastname like '%[Ss][Mm][Ii][Tt][Hh]%' |
| 26 | Given two tables A and B having 3 and 2 columns respectively,If I run select * from A,B what will be the number of rows? | Cartiesan product |
| 27 | What is money and smallmoney? | it is exact datatye with four digit decimal precision ,money with 8 bytes and small money with 4 bytes |
| 28 | What is recursive SP? | you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. |
| 29 | What is system procedure related with syskeys? | sp_primarykey,sp_commonkey |
| 30 | How do you list out all the indexes in a database? | sp_helpindexes |
| 31 | How do I get the current database name? | db_name() |
| 32 | 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. |
| 33 | A) What are two types of Index? B)Explain Index and Performance? | A) Clustered and Non Clustered Index B)Clustered index not reusing available space on preceding pages which makes pages grow longer and the search takes long time to complete. It is better to use cluster index for data value that is somewhat randomly distributed throughout the table.
In non-cluster point of view, first check the impact on the performance of OLTP environment for addition of non-clustered index. It is better to use non-cluster where low number of duplicates are present. It is good for large number of duplicate values. It is more effective if less than 10 to 20% of data is to be accessed through non clustered. Non-clustered is useful for single row lookups, joins, queries on columns that are highly selective and for queries with small range retrievals |
| 34 | What is bit datatype ? | Bit datatype is used to store boolean information like 1 or 0 (true or false). and there was no support for NULL. |
Sybase Interview Questions and Answers V
| Sl.No | Questions | Answers |
|
|
|
|
| 1 | What is the difference between decimal and Float datatypes? | Float is approximate numeric datatypes and precision value is 15 digits,Decimal in exact numeric datatype and precision value is 28 Float have presion upto 7 and it is float datatype,Decimal is numeri datatype |
| 2 | The table Emp with (name,dept,id,salary) find employee name who is having max salary among all the emp in dept wise. | select * from emp group by dept having max(salary) |
| 3 | A)What are the limitation while passing the parameter in stored procedures? B)What is Default paameter in SP? | Parameters name may be upto 29 character in length,upto 255 parameter may be defined,rules,defaults and column properites may not be applied but wildcard can applied,text,image datatype can pass trough for read only , B. char,varchar |
| 4 | How do you list out all the Rules and Defaults in your database? | select name from sysobjects where type ='D' or type='R' |
| 5 | A)How the Stored Procedure is said to be Faster execution ? B)What are the advantage of SP? | Sp after their first execution become memory resident and no need to reparsed,reoptimized or recompiled . faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency |
| 6 | A)What are limitation on view that cannot include? B)Can I drop a view? | select into,compute clause,union,order by. B. yes |
| 7 | A)What are the limitation of Trigger? B)What happen when we update the table using Trigger? | Any create commend,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger . The delete row will entered into the Deleted table and newly inserted row will enter into the inserted table and base table inside the trigger |
| 8 | explain about @@transtate? | returns the current transaction state. |
| 9 | A)What are the steps involed in creating cursor? B)What is the syntax for cursor declaration for update? | Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors declare cursor_name cursor for select_statement for update [of col_list] |
| 10 | 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). |
| 11 | What is optimistic locking? | Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
|
| 12 | A)What is nested transaction ? B)In Which Mode nested transaction is eliminated? | You can nest transactions within other transactions. When you nest begin transaction and commit transaction statements, the outermost pair actually begin and commit the transaction. The inner pairs just keep track of the nesting level. Adaptive Server does not commit the transaction until the commit transaction that matches the outermost begin transaction is issued. Normally, this transaction "nesting" occurs as stored procedures or triggers that contain begin/commit pairs call each other,The @@trancount global variable keeps track of the current nesting level for transactions. B. Chained Mode
|
| 13 | How can I select the first 500 rows from a table having million rows & generate a flat file? | BCP -F and in out filename -L |
| 14 | A)What is Hotspot ? B) How can be Resolved? | Whan a multipe processes attempt to modify a same page in table. B)It can be resolved using heap table and clustered index |
| 15 | Table has a column varchar(10) that contain numeric and alphanumeric values ,I want alpha numeric value how shall I get it? | using wildcards |
| 16 | If I want current host computer name of client process? | host_name() |
| 17 | What are the SP related with syskeys? | No Answer |
| 18 | What is the difference between isolation level 2 and 3? | Not Clear |
| 19 | Explain Worktables for performance? | If column has a unique/primary key, than there is no need to specify the distinct clause.
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. If there is an index on a column, which keeps the column sorted, you do need to specify order by clause in the query. If you know that data is unique, than there is no need to specify the group by clause in the query. |
| 20 | What is str()? | numeric to character conversion |
| 21 | How do you handle error in BCP? | -e |
| 22 | Explain -Z option in defncopy? | This tells defncopy to put message and prompts in a language other than default language for server |
| 23 | Explain me the I and II normal form ? | Normal I : all repeating groups have to moved into separate tables and one column contain exactly one value. Normal II: A nonkey fields must depend on the entire primary key. |
Sybase Interview Questions and Answers IV
| Sl.No | Questions | Answers |
|
|
|
|
| 1 | Explain Correlated Subquery with examples? |
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.Using Exists is fine example for correlated subquery. |
| 2 | Is the Stored Procedure is reentrant and What is not reentrant? | It is not reentrant ,only one user can access SP one at the time |
| 3 | What is the use of creating view with check option? | Check flag prevent insertion or updation of a rows that will subsequently not meet the view criteria |
| 4 | explain about @@servername? | name of the local sql server |
| 5 | 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. |
| 6 | 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 |
| 7 | During BCP out How do I exclude a column? | Create a view based on the table that you want to exclude a column from and
then bcp out from the view or using format file. |
| 8 | 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). |
| 9 | What is optimistic locking? | Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
|
| 10 | You have two tables a and b with column c1 in both of them. Column c1 in table a has values 1, 2 and 3 and the column in tables b has values 3, 4, 5. How many rows would the following query return? select * from a, b where a.c1 <> b.c1
| 1 row |
| 11 | What is deffered update? | No Answer |
| 12 | What is the difference between Clustered and non clustered index on performance wise? | When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater,clustered index is better for range queries,more duplicates in table and non clustered for single row lookup.one clustered index and 249 non clustered index. |
| 13 | What is Distribution page? | server keeps distribution information for each index on a separate page in the datebase |
| 14 | How shall I simulate from level 0 to level 3 in Isolation? | using holdlock |
| 15 | Why would we use stored procedures over triggers for optimizing
queries? | A trigger gets fired for every transaction. It is another overhead in the query. Depending on how important the query is you can drop triggers and write a store procedure for that. And at end of the day you can run that stored procedure to perform the operations performed by the trigger. |
| 16 | What is ceiling()? | Smallest integer greater than or equal to specified value |
| 17 | What is the stored procedure to view current lock and processes respectively? | sp_lock and sp_who |
| 18 | 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 |
| 19 | 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."
|
| 20 | Write a query for selecting all persons with lastname like'smith'(anycase)from a table? | select name from table where name like " " |
| 21 | How do you delete the identity gaps in identity columns? | Set Identity ON and set identity value is NULL |
| 22 | What is the difference in creating Temp table using select into and create command? | select into will create with rows and it is faster ,create command will create only table structure. |
Sybase Interview Questions and Answers III
| S.No | Questions | Answers |
|
|
|
|
| 1 | Display the name starts with small letters? | select name from table where name like'[a-z]%' |
| 2 | Does datatypes listed in sysobjects? a. Where it will be stored? | NO |
| 3 | What is cascade delete in sybase? a.What is the referential Integrity? | In Sybase 11, triggers are needed to complement the declarative referential integrity constraints (foreign key) specifications only for referential integrity constraints associated with cascades, nullifies or multi-target delete rules. Sybase 11 trigger procedures generated from the OPM schema .SYB11; we show below only part of this file, namely the procedures associated with the relations representing object class |
| 4 | Comapre exists and like? a. What is the difference between IN and Exists? | Like is used for Wildcards and it support the subqueries return single value,Exists supports for subqueries return multi value as well as subqueries can have multi column selection in it. |
| 5 | What is the difference between select into and insert? a.How do you create empty copy of an existing table ? | select into used to create a new table dynamically and insert the rows into the new table ,where insert used to insert the values into already exixting table and it will log the details. Select * into table1 from table2 where 1=2 |
| 6 | What is the use of creating view with check option? a. can I update view? | Check flag prevent insertion or updation of a rows that will subsequently not meet the view criteria yes,but view had created using only one table than update is possiable,If it is more than one we can update with some restrictions. |
| 7 | What is stored procedures? a. Why should I use stored procedures? | Security - you can revoke access to the base tables and only allow users to access and manipulate the data via the stored procedures.
Performance - stored procedures are parsed and a query plan is compiled. This information is stored in the system tables and it only has to be done once. Network - if you have users who are on a WAN (slow connection) having stored procedures will improve throughput because less bytes need to flow down the wire from the client to ASE. Tuning - if you have all your SQL code housed in the database, then it's easy to tune the stored procedure without affecting the clients (unless of course the parameter change). Modularity - during application development, the application designer can concentrate on the front-end and the DB designer can concentrate on the ASE. Network latency - a client on a LAN may seem slower if it is sending large numbers of separate requests to a database server, bundling them into one procedure call may improve responsiveness. Also, servers handling large numbers of small requests can spend a surprising amount of CPU time performing network IO. Minimise blocks and deadlocks - it is a lot easier to handle a deadlock if the entire transaction is performed in one database request, also locks will be held for a shorter time, improving concurrency and potentially reducing the number of deadlocks. Further, it is easier to ensure that all tables are accessed in a consistent order if code is stored centrally rather than dispersed among a number of apps. |
| 8 | What is recursive SP ? a. how it is differ from Nested SP? | Recursive Sp is Sp call by itself,Nested Sp is Sp call the another SP |
| 9 | How do you display the information on a current login ? | sp_login |
| 10 | For cursor what is the possible values of @@sqlstatus? | 0-successful,1-error,2-no more rows to fetch |
| 11 | Why we need Trigger? a. Why would we use stored procedures over triggers for optimizing queries? | Provide sophisticated auditing
Prevent invalid transactions Enforce referential integrity (either those actions not supported by declarative integrity constraints or across nodes in a distributed database) Enforce complex business rules Enforce complex security authorizations Provide transparent event logging Automatically generate derived column values Enable building complex views that are updatable Track system events |
| 12 | How do you force the optimizer to use the index for a table? a. What is Index Covering? | you think that an index can be used to execute the query faster, you can force the optimizer to use the index by specifying the Index ID or Index name number after the table name in the query. SQL Server would use that index to satisfy the query. |
| 13 | What is the advantage of table level over row level lock? a. What is the syntax for tabel level lock? | Larger log files (much larger in some cases). Binary log will contain data for large statements that were rolled back.
When using row-based replication to replicate a statement (for example, an UPDATE or DELETE statement), each changed row must be written to the binary log |
| 14 | What would be the status of sp_lock in a deadlock? a. What are the Transaction properties? | Transaction is aborted when a deadlock occurs,any locking information that could be returned by sp_lock no longer is available for that transaction. |
| 15 | How would you transfer 1000 rows from 10000 rows using BCP? | Using options -F and -L |
| 16 | What is dirty read ? How it is deffered from phantom read? | dirty reads. Such reads occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change. If the first transaction rolls back the change, the information read by the second transaction becomes invalid
Phantoms occur 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. |
| 17 | What does deffered in the output of the show plan ? a. What is the defered Update? | It means the optimizer goes for deffered update |
| 18 | If you have to begin,commit and rollback tran in your query ,can you create the temp table in that transaction? | yes |
| 19 | What is Normalization? a.Explain all the Rules? | A database is said to be normalized if it complies with a set of formal rules, referred to as normal forms. These formal rules define how data that represents different entities should be organized in a database.
First Form: A table complies with the first normal form if no columns define similar attributes and if no column contains multiple values. second form: A table complies with the second normal form if it complies with the first normal form, and if each column that is not part of the primary key depends on all of the columns that are covered by the primary key in the table and not on a subset of the columns that are covered by the primary key. Third form: A database complies with the third normal form if it complies with the second normal form and if, in each table, columns that are not covered by the primary key do not depend on each other. |
| 20 | What is the difference between stuff and Substring()? What is str()? | stuff is used to replace one string inside the another string and Index value start with 1,Substring is used for get some part of character from the String and Index value start with 0. |
| 21 | What is the performance issue on the following query,select name,id,age,salary from emp where salary<>300 ,I have indexed the salary column? | since we have negative logic it goes for table scan,we have avoid this by Index covering.. |
| 22 | How will I get 2000 rows from 50000 rows from one database and put it in another database What is -T option in bcp? | using BCP instead of using select into command This is the size of text or image data,default size is 32KB |
| 23 | You have table customer and order with cusid and ordid respectively and cusid is also present in order table ,write a query to get all the customer without order? | select name from customer where cusid not in(select cusid from order) |
| 24 | What is the Advantage of System Procedures? | To change data dictionary properties or manipulate data dictionary objects (e.g., Tables, Referential Integrity, Advantage Extended Procedures, etc.),System Procedures that can be used to manipulate data dictionary objects using SQL |
Sybase Interview Questions and Answers II
| Sl.No | Questions | Answers |
|
|
|
|
| 1 | 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. |
| 2 | A)What is the use of creating view with check option? B) Can I drop a View? | A)Check flag prevent insertion or updation of a rows that will subsequently not meet the view criteria B)yes |
| 3 | A)Explain about @@sqlstatus? B)What are possiable value of @@sqlstatus? | A)It returns the status of the pervious fetch statement in a cursor B)0,1,2 |
| 4 | A)What is the performance issue on CURSOR? B)What are the steps involed in creating the cursor? | A)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.
B)Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors |
| 5 | A)What are the Properties of Transaction? B)What are the modes of transaction ? | A)server maintain transaction log,server locks table pages during transaction,server performs automatic recovery upon restart ,transaction control statement B)Chained and Unchained |
| 6 | How will bcp out the Identity column? | -E |
| 7 | A) What are the two modes in BCP? B)Explain about how to achive fast BCP? | B)Table does'nt have indexes and trigger and we have to set select into bulk copy option for the particular database |
| 8 | Can I transfer table syntax from database to flat file using defncopy? | No ,It can be done using isql |
| 9 | A)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). |
| 10 | A)What is optimistic locking? | Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
|
| 11 | What is Checkpoint? | The point at which all data pages that have been changed are guaranteed to have been written to the database device.
|
| 12 | There's a performance issue What will be your very first step towards the solution? | I will run the Update Sataiscis and 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.
|
| 13 | A)What is Phantom Reads? | A)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.
B) Such reads occur when one transaction reads a row and a second transaction modifies that row. If the second transaction commits its change, subsequent reads by the first transaction yield different results than the original read. |
| 14 | 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.
|
| 15 | What is datediff? | return the date difference accordding to the datapart |
| 16 | What is the system procedure to view current lock and processes respectively? | sp_lock and sp_who |
| 17 | Compare not IN and not EXISTS? | IN allow duplicate values and sub query have one column,EXISTS not allow duplicates and inner query can have multi columns |
| 18 | A)What is Rollback trigger ? B) how it is differ from Rollback transaction? | 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 |
| 19 | A)What happen when we delete the table using Trigger? B) What are the limitation of Trigger? | A)The delete row will entered into the Deleted table inside the trigger B ) any create commond,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger |
| 20 | 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.
|
| 21 | How do you call the remote SP? | severname.database.owner.Spname |
| 22 | Compare Truncate and Delete? | DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back. |
| 23 | Explain sp_recompile? | Sp_recompile is used when updated statistics have been updated on the table and when an index has been added to a table that you want the optimizer to consider to generate new query plan for the SP |
| 24 | What is Sp_dboption? | It is used to enable or diable option for database |
| 25 | Write a query for selecting all persons with lastname like 'smith'(anycase)from a table? | select lastname from table where lastname like '%[Ss][Mm][Ii][Tt][Hh]%' |
| 26 | Given two tables A and B having 3 and 2 columns respectively,If I run select * from A,B what will be the number of rows? | Cartiesan product |
| 27 | What is money and smallmoney? | it is exact datatye with four digit decimal precision ,money with 8 bytes and small money with 4 bytes |
| 28 | What is recursive SP? | you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. |
| 29 | What is system procedure related with syskeys? | sp_primarykey,sp_commonkey |
| 30 | How do you list out all the indexes in a database? | sp_helpindexes |
| 31 | How do I get the current database name? | db_name() |
| 32 | 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. |
| 33 | A) What are two types of Index? B)Explain Index and Performance? | A) Clustered and Non Clustered Index B)Clustered index not reusing available space on preceding pages which makes pages grow longer and the search takes long time to complete. It is better to use cluster index for data value that is somewhat randomly distributed throughout the table.
In non-cluster point of view, first check the impact on the performance of OLTP environment for addition of non-clustered index. It is better to use non-cluster where low number of duplicates are present. It is good for large number of duplicate values. It is more effective if less than 10 to 20% of data is to be accessed through non clustered. Non-clustered is useful for single row lookups, joins, queries on columns that are highly selective and for queries with small range retrievals |
| 34 | What is bit datatype ? | Bit datatype is used to store boolean information like 1 or 0 (true or false). and there was no support for NULL. |
Cybase Interview Questions and Answers
| S.No | Questions | Answer |
|
|
|
|
| 1 | Display the name starts with small letters? |
|
| 2 | Does datatypes listed in sysobjects? a. Where it will be stored? |
|
| 3 | What is cascade delete in sybase? a.What is the referential Integrity? | In Sybase 11, triggers are needed to complement the declarative referential integrity constraints (foreign key) specifications only for referential integrity constraints associated with cascades, nullifies or multi-target delete rules. Sybase 11 trigger procedures generated from the OPM schema .SYB11; we show below only part of this file, namely the procedures associated with the relations representing object class
|
| 4 | Comapre exists and like? a. What is the difference between IN and Exists? | Like is used for Wildcards and it support the subqueries return single value,Exists supports for subqueries return multi value as well as subqueries can have multi column selection in it. |
| 5 | What is the difference between select into and insert? a.How do you create empty copy of an existing table ? | select into used to create a new table dynamically and insert the rows into the new table ,where insert used to insert the values into already exixting table and it will log the details. Select * into table1 from table2 where 1=2 |
| 6 | What is the use of creating view with check option? a. can I update view? | Check flag prevent insertion or updation of a rows that will subsequently not meet the view criteria yes,but view had created using only one table than update is possiable,If it is more than one we can update with some restrictions. |
| 7 | What is stored procedures? a. Why should I use stored procedures? | Security - you can revoke access to the base tables and only allow users to access and manipulate the data via the stored procedures.
Performance - stored procedures are parsed and a query plan is compiled. This information is stored in the system tables and it only has to be done once. Network - if you have users who are on a WAN (slow connection) having stored procedures will improve throughput because less bytes need to flow down the wire from the client to ASE. Tuning - if you have all your SQL code housed in the database, then it's easy to tune the stored procedure without affecting the clients (unless of course the parameter change). Modularity - during application development, the application designer can concentrate on the front-end and the DB designer can concentrate on the ASE. Network latency - a client on a LAN may seem slower if it is sending large numbers of separate requests to a database server, bundling them into one procedure call may improve responsiveness. Also, servers handling large numbers of small requests can spend a surprising amount of CPU time performing network IO. Minimise blocks and deadlocks - it is a lot easier to handle a deadlock if the entire transaction is performed in one database request, also locks will be held for a shorter time, improving concurrency and potentially reducing the number of deadlocks. Further, it is easier to ensure that all tables are accessed in a consistent order if code is stored centrally rather than dispersed among a number of apps. |
| 8 | What is recursive SP ? a. how it is differ from Nested SP? | Recursive Sp is Sp call by itself,Nested Sp is Sp call the another SP |
| 9 | How do you display the information on a current login ? |
|
| 10 | For cursor what is the possible values of @@sqlstatus? | 0-successful,1-error,2-no more rows to fetch |
| 11 | Why we need Trigger? a. Why would we use stored procedures over triggers for optimizing queries? | Provide sophisticated auditing
Prevent invalid transactions Enforce referential integrity (either those actions not supported by declarative integrity constraints or across nodes in a distributed database) Enforce complex business rules Enforce complex security authorizations Provide transparent event logging Automatically generate derived column values Enable building complex views that are updatable Track system events |
| 12 | How do you force the optimizer to use the index for a table? a. What is Index Covering? | you think that an index can be used to execute the query faster, you can force the optimizer to use the index by specifying the Index ID or Index name number after the table name in the query. SQL Server would use that index to satisfy the query. |
| 13 | What is the advantage of table level over row level lock? a. What is the syntax for tabel level lock? | Larger log files (much larger in some cases). Binary log will contain data for large statements that were rolled back.
When using row-based replication to replicate a statement (for example, an UPDATE or DELETE statement), each changed row must be written to the binary log |
| 14 | What would be the status of sp_lock in a deadlock? a. What are the Transaction properties? | Transaction is aborted when a deadlock occurs,any locking information that could be returned by sp_lock no longer is available for that transaction. |
| 15 | How would you transfer 1000 rows from 10000 rows using BCP? | Using options -F and -L |
| 16 | What is dirty read ? How it is deffered from phantom read? | dirty reads. Such reads occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change. If the first transaction rolls back the change, the information read by the second transaction becomes invalid
Phantoms occur 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. |
| 17 | What does deffered in the output of the show plan ? a. What is the defered Update? | It means the optimizer goes for deffered update |
| 18 | If you have to begin,commit and rollback tran in your query ,can you create the temp table in that transaction? | yes |
| 19 | What is Normalization? a.Explain all the Rules? | A database is said to be normalized if it complies with a set of formal rules, referred to as normal forms. These formal rules define how data that represents different entities should be organized in a database.
First Form: A table complies with the first normal form if no columns define similar attributes and if no column contains multiple values. second form: A table complies with the second normal form if it complies with the first normal form, and if each column that is not part of the primary key depends on all of the columns that are covered by the primary key in the table and not on a subset of the columns that are covered by the primary key. Third form: A database complies with the third normal form if it complies with the second normal form and if, in each table, columns that are not covered by the primary key do not depend on each other. |
| 20 | What is the difference between stuff and Substring()? What is str()? | stuff is used to replace one string inside the another string and Index value start with 1,Substring is used for get some part of character from the String and Index value start with 0. |
| 21 | What is the performance issue on the following query,select name,id,age,salary from emp where salary<>300 ,I have indexed the salary column? | other than 300 |
| 22 | How will I get 2000 rows from 50000 rows from one database and put it in another database What is -T option in bcp? | using BCP instead of using select into command This is the size of text or image data,default size is 32KB |
| 23 | You have table customer and order with cusid and ordid respectively and cusid is also present in order table ,write a query to get all the customer without order? | select name from customer where cusid not in(select cusid from order) |
| 24 | What is the Advantage of System Procedures? | To change data dictionary properties or manipulate data dictionary objects (e.g., Tables, Referential Integrity, Advantage Extended Procedures, etc.),System Procedures that can be used to manipulate data dictionary objects using SQL |
SOAP (Simple Object Access Protocol) Interview Questions and FAQ's
SOAP was originally developed by Microsoft, IBM, DevelopMentor and Userland Software and was then submitted to the Internet Engineering Task Force (IETF), who eventually made it an official recommendation. Though Microsoft camp seem to have played a major role in defining the specification of SOAP.
The motivation of developing this protocol was the widely used inter-application communication protocols like Microsoft’s DCOM protocol or Java’s RMI or OMG’s IIOP protocols which have till recent times fulfilled the needs of giving component services over an interconnected network .The richness of these protocols helped them gain a strong hold over distributed computing over controlled environment. Almost all these protocols need a smart client able to run the proxies of the server components; moreover, these protocols reveal their limitations when it comes to INTERNET. Internet cannot guarantee what kind of client and server will be operating at either end of the connection — it can only guarantee that they are both communicating via HTTP.
SOAP (Simple Object Access Protocol) is a simple solution for interaction of different applications built in different languages and running on different platforms as it uses HTTP as its transport and XML as its payload for sending and receiving messages. It’s a lightweight and a loosely coupled protocol for exchange of information in a decentralized and a distributed environment.
What’s so light in SOAP?
Soap relies on HTTP as a transport mechanism to send XML based messages, the messages are packed in what is called a SOAP envelop and send to the server to process in a Request/Response fashion. SOAP unlike proprietary protocols like DCOM or RMI does not require strong connection between client and the server and the SOAP messages are sting based messages passed from the Client to Server and vice versa in the form of SOAP envelops.
What makes SOAP loosely coupled?
Most proprietary protocols require the applications of the same breed to be running on both the ends, what if the server is implemented in a different programming language. The ability to access service of a component in a language/location and platform transparent manner reduces the tight coupling between the client and the server. SOAP enables “incompatible” systems to interoperate.
How does SOAP Message look like and How is it different from a method invoked on a Object?
To demonstrate how SOAP messages are different from method invocation on an component Let’s take a look at this ActiveX component, which accepts two integers and returns the total, as a return value would look something like this,
Server ActiveX Component (MyComponent.MathComponent)
Public Function Add (Num1 as integer, Num2 as integer)
Add = Num1 + Num2
End Function
The Client would create an instance of this component and would invoke the method to get the total.
Sub Button1_click()
Dim objAdd as new MyComponent.MathComponent
Dim intResult as integer
intResult = objAdd.Add(10,20)
end sub
This MyComponent.MathComponent would get instantiated and serve the request from the client application.
At a higher level a SOAP request to fulfill this request would look something like this
In order to ensure that server can correctly identify the method call it is wrapped up in a SOAP envelop
This is just an abstract example to give you a gist of the SOAP message.
The real SOAP message will contain more information like the component name and stuff.
This message is sent to a SOAP listener through HTTP in a Request/Response fashion.
The SOAP Listener accepts this SOAP message and does the processing on the component and returns the SOAP message back for the client.
In the Server you will need to implement a SOAP listener, wsdlgen.exe is a tool which comes with the SOAP Toolkit will create a SOAP listener and a WSDL (Web Service description Language) file for a given COM Component. For more information on WSDL visit http://www.msdn.microsoft.com/xml/general/wsdl.asp
OH GODDDDDDDD, do I need to
The answer is NO, The Microsoft SOAP Toolkit 2 will take care of the marshaling and UN-marshaling of the XML messages behind the scene for you.
The SOAP Toolkit also comes with a set of COM Component to access SOAP Web Services easily, Visual Basic SOAP client
Sub Button1_click()
Dim objSOAPClient As Object
Set objSOAPClient = CreateObject("MSSOAP.SoapClient")
objSOAPClient.mssoapinit "http://ServerName/VirtualDirectory/wsdlfile.WSDL"
MsgBox objSOAPClient.Add(100,400)
End sub
That’s it, the SOAP message marshaling and UN-marshaling will taken care by the SOAP client and what you will get is the result.
SOAP and Security
SOAP as it works on HTTP get the benefits of all the security that are currently available to HTTP, a SOAP message can pass through the firewall of the web server normally from port 80.
Limitations of SOAP
Though SOAP is evolving, and has a number of striking features, which are important in development of, distributed applications but still there are certain things which SOAP cannot support which I would like to address
SOAP is a simple protocol: As the name suggests it’s a simple protocol and works on HTTP, we cannot expect all the functionality’s offered by other protocols like DCOM or RMI.
SOAP may turn out to be slower compared to other proprietary protocols as it requires additional XML processing.
Still SOAP fares well in most areas compared to other wire protocol. As per the specifications, SOAP can also use other transport carrier like SMTP to transmit SOAP messages. Still lot of information on this topic in not available.
Summary
SOAP is a compelling and innovative use of available technologies that are not tied to a specific vendor. SOAP will play a major role in Microsoft’s “.NET” platform for building WebService. And Microsoft is expecting huge revenue to be generated from WebService
No comments:
Post a Comment