Sybase Interview Questions & Answers

  1. Question 1. How Do I Tell Which Tables Have Identities In Sybase?

    Answer :

    You can tell if a table has identities one of two ways:
    1. sp_help [tablename]: there is a field included in the sp_help output
    describing a table called “Identity.” It is set to 1 for identity fields, 0 otherwise.
    2. Within a database, execute this query:

    •  select object_name(id) “table”,name “column”, prec “precision”
    •  from syscolumns
    •  where convert(bit, (status & 0x80)) = 1
    •  go

    this will list all the tables and the field within the table that serves as an identity, and the size of the identity field.

  2. Question 2. Explain About The Sybase Group?

    Answer :

    Sybase exclusively focuses on mobilizing and managing information. It is known to be the third largest database management company after Oracle and IBM. Sybase products on Investment banking are well known to the world over and it offers many customized solutions for business intelligence.

  3. IBM DB2 Interview Questions

  4. Question 3. State Some Of The Products Of Sybase?

    Answer :

    Sybase has worlds leading products some of them are

    •  ASE
    •  Avantgo
    •  Power builder and power designer
    •  SQL ianywhere
    •  Sybase IQ
    •  Sybase replication server
    •  Sybase RTE
    •  Sybase data federation
    •  Main frame connect
  5. Question 4. What Is The Depth Of Triggers?

    Answer :

    16 Levels i.e. 16 nested triggers can be there in Sybase

  6. IBM DB2 Tutorial

  7. Question 5. State Some Of The Criticism About Sybase Iq?

    Answer :

    Sybase storage systems are optimized for Dataware housing applications where data cannot be modified can be modified by searching and accessing the data. Its column based search criteria gives high performance for search but it negates the writing capability to the data. Performance for row based relational based databases is worse because it is very difficult for updating and modifying them.

  8. RDBMS Interview Questions

  9. Question 6. What Are Triggers? Why Do We Use Them? Diff Types Of Triggers? What Are Magic Tables?

    Answer :

    A trigger is a stored procedure bound to a table, which is implicitly executed when a user attempts to modify data with an insert, delete, or update command.
    Use :   i. Validation purpose
               ii.Referential integrity
    Type of trigger:
               i. update
               ii. delete
    Two tables are created at the time of trigger execution
    1. Inserted
    2. Deleted  which are called magic tables.

  10. Question 7. What Are Stored Procedures?

    Answer :

    Stored procedure is a database object which is collection of SQL statements or constructs. Parameters can be passed and returned, and error codes may be checked. Stored procedures, after their first execution, become memory resident and do not need to be reparsed, reoptimized, or recompiled. So they are faster.

  11. PHP Tutorial
    DBMS Interview Questions

  12. Question 8. Explain What Is Update Statistics In Sybase?

    Answer :

    The update statistics command helps the server make the best decisions about which indexes to use when it processes a query, by providing information about the distribution of the key values in the indexes. The update statistics commands create statistics, if there are no statistics for a particular column, or replaces existing statistics if they already exist. The statistics are stored in the system tables systabstats and sysstatistics.

  13. Question 9. Explain The Structure Of Adaptive Server Enterprise?

    Answer :

    Structure of ASE installation hosts several databases and it comprises of one data server. Also system files occupy only one meta data. User databases are stored in the form of tables. Security and information privacy can be protected by setting permissions.

  14. PHP Interview Questions

  15. Question 10. Explain About The Features Of Sybase Iq?

    Answer :

    Sybase IQ stores data base in the form of columns of data rather in rows. This storage model improves the accessibility and search criteria for the data. Performance of the search criteria improves greatly because it searches only columns rather than the rows and columns of the database. This is also environment friendly.

  16. SQL Server 2008 Tutorial

  17. Question 11. Explain The Benefits You Can Get From Mainframe Connect?

    Answer :

    These are the following benefits you can get from mainframe connect: –
    1) Client applications can be enabled. External software’s can be connected to the mainframe connect for higher performance.
    2) Mainframe applications connect to external data through LAN.
    3) New applications can be created which enables you to connect to external data or source of information.

  18. SQL Server 2008 Interview Questions

  19. Question 12. Explain About The Mainframe Connect Tools From Sybase?

    Answer :

    This connectivity tool gives access to high performance tools which connects you very fast to the main frame data sources. It gives you read and write access to databases. It also has a facility by which you can connect the main frame data to secondary data storage facilities.

  20. IBM DB2 Interview Questions

  21. Question 13. Explain About Power Designer Data Modeling Software?

    Answer :

    Power designer has two unique tools known as Link and Sync technologies.This technology offers Data base designers to design efficient data models through which one can offer efficient design. Also Data base designers can share resources among them which will improve the productivity and performance

  22. Oracle 11g Tutorial

  23. Question 14. Explain About The Data Integration Suite Features?

    Answer :

    Data integration suite offers advanced development and management tools. Integration of data is very easy and efficient as you can navigate your data to a specified address without much difficulty. The five important features are ETL, Data federation, Replications, Real time events and search events.

  24. Question 15. What Is The Difference In Storage Type Of Numeric And Decimal Data Types In Sybase?

    Answer :

    The numeric and decimal types are identical in all respects but one:
    Only numeric types with a scale of 0 can be used for the IDENTITY column

  25. Oracle 11g Interview Questions

  26. Question 16. Explain About Enterprise Scalability And Roi Of Data Integration Suite?

    Answer :

    Enterprise scalability provides increased capacity without any new addition of hardware. It increases the capacity by increasing the availability in the enterprise data layer.
    ROI: – Streamlining of application projects and reducing data management costs, data federation increases ROI. Complexity and superior decision making choices make ROI best in its class.

  27. DB2 Using SQL Tutorial

  28. Question 17. Give The Benefits Of Data Integration Suite?

    Answer :

    Some of the benefits which you can get from a Data integration suite are 
    Flexibility: ­This suite gives a greater flexibility to incorporate data from various sources such as web, file systems, etc and it can transfer this data to third party applications such as SQL, production systems, etc.

  29. DB2 Using SQL Interview Questions

  30. Question 18. What Is Procedure Cache And Data Cache In Sybase?

    Answer :

    Procedure cache is the memory area where compiled query tree run e.g, procedure, batch query. Data Cache is the memory area where data that is required for the current querys running is bought from Disk on to memory for building result sets.

  31. RDBMS Interview Questions

  32. Question 19. Explain About Mirror Activator Disk Mirroring?

    Answer :

    This software works with storage replication systems and replicates the database to an available ASE or any database back up devices such as oracle. It increases the security of applications and also reduces the fail over time for data applications.

  33. Java Tutorial

  34. Question 20. Explain About Open Switch Business Continuity Software?

    Answer :

    Open switch business software helps you to work on the back up system without any hassles making your primary database server secured. It gives you connectivity to the primary database from the secondary server even in the middle of the application.

  35. MYSQL DBA Interview Questions

  36. Question 21. Explanation About Replication Server Software?

    Answer :

    Replication server software makes you to manage multiple data platforms very easily. Some of the features which it provides are heterogeneous replication and synchronization across various applications bi directionally. It provides services from the client to server and server to client.

  37. Question 22. What Is A Coalesce? What Is The Equivalent Of Oracle/db2 Coalesce Function In Sybase?

    Answer :

    Coalesce is also available in Sybase This functions evaluates the listed expressions and returns the first non­null value. If all the expressions are null, coalesce returns null 
    USAGE: coalesce(expression, expression [, expression]…)
    EXAMPLE: coalesce(initialqty,finalqty,middleqty)
    Returns the first occurrence of a non­NULL value in either the initialqty or finalqty or middleqty


  38. Question 23. What Is The Relationship Between Jdbc And Sybase?

    Answer :

    JDBC is a Application programming Interface which helps Java Developers to interact with Database like Sybase via DML Statements (Insert, Update, Delete and so on).

  39. Database Administration Interview Questions

  40. Question 24. What Is The Command For Quitting From Sybase?

    Answer :

    syb_quit(0) command can be used to exit from Sybase database .


  41. DBMS Interview Questions

  42. Question 25. How To Start/stop Ase When Cpu Reboots?

    Answer :

    Below is an example of the various files (on Irix) that are needed to start/stop an ASE. The information can easily be extended to any UNIX platform. The idea is to allow as much flexibility to the two classes of administrators who manage the machine:
    * The System Administrator
    * The Database Administrator
    Any errors introduced by the DBA will not interfere with the System Administrator’s job. 
    With that in mind we have the system startup/shutdown file /etc/init.d/sybase invoking a script defined by the DBA: 



  43. Question 26. What Is Open Server In Sybase?

    Answer :

    Sybase open server is a vital component of Sybase and Microsoft Open Data Services . The Sybase open server is a major component for handling distributed database systems and thus forms a vital and powerful server side component of Sybase systems . One of the major feature of this is Sybase open server is interoperability.


  44. Autosys Interview Questions

  45. Question 27. Does Table Partitioning Require User Defined Segments In Sybase?

    Answer :

    No. By design, each table is intrinsically assigned to one segment, called the default segment. When a table is partitioned, any partitions on that table are distributed among the devices assigned to the default segment.In the example under “How Do I Create A Partitioned Table That Spans Multiple Devices?”, the table sits on a user­defined segment that spans three devices

  46. PHP Interview Questions

  47. Question 28. How Do I Choose Which Tables To Partition In Sybase?

    Answer :

    You should partition heap tables that have large amounts of concurrent insert activity. (A heap table is a table with no clustered index.) Here are some examples:
    1. An “append­only” table to which every transaction must write.
    2. Tables that provide a history or audit list of activities.
    3. A new table into which you load data with bcp in. Once the data is loaded in, you can unpartition the table. This enables you to create a clustered index on the table, or issue other commands not permitted on a partition table.

  48. Question 29. Can I Partition Any Table In Sybase?

    Answer :

    No. You cannot partition the following kinds of tables:
    1. Tables with clustered indexes (as of release 11.5 it is possible to have a clustered index on a partitioned table).
    2. ASE system tables.
    3. Work tables.
    4. Temporary tables.
    5. Tables that are already partitioned. However, you can unpartition and then re­partition tables to change the number of partitions.

  49. Java Interview Questions

  50. Question 30. How To View The Log Files In Sybase?

    Answer :

    cd $SYBASE/$SYBASE_ASE/install

  51. Question 31. How To Save Space When Inserting Rows Monotonically?

    Answer :

    If the columns that comprise the clustered index are monotonically increasing (that is, new row key values are greater than those previously inserted) the following System 11 dbcc tune will not split the page when it’s half way full. Rather it’ll let the page fill and then allocate another page: 
    dbcc tune(ascinserts, 1, “my_table”) 
    By the way, SyBooks is wrong when it states that the above needs to be reset when ASE is rebooted. This is a permanent setting.
     To undo it:
    dbcc tune(ascinserts, 0, “my_table”)

  52. Question 32. Given A Table Which Contains Some Rows With Duplicate Keys, How Would You Remove The Duplicates?

    Answer :

    create temporary table with same table structure and then create unique index on this temporary table with option ignore_dup_row. now insert data into temp table from the table in which duplicate records exists. while inserting record into temp table, duplicate rows will get ingored. Finally temp table will have unique records
    select distinct * from table_1 into temp_table_1
    truncate table_1
    insert into table_1
    select * from temp_table_1
    This is one of the ways to eliminate duplicates.


  53. DB2 SQL Programming Interview Questions

  54. Question 33. Can I Run Any Transact­sql Command On A Partitioned Table In Sybase?

    Answer :

    No. Once you have partitioned a table, you cannot use any of the following Transact­SQL commands on the table until you unpartition it:
    1. drop table
    2. sp_placeobject
    3. truncate table
    4. alter table table_name partition n 
    On releases of ASE prior to 11.5 it was not possible to create a clustered index on a partitioned table either.


  55. SQL Server 2008 Interview Questions

  56. Question 34. How Will You Optimize A Query?

    Answer :

    • Check the query plan.
    • Make sure that the preceding columns of index are available in search criteria.
    • Make sure the Statistics are up-to-date: – Run update statistics on the tables in question if the data in these tables is changed drastically.
    • Check Search Arguments: – If possible avoid inequality operator. Avoid operations on columns and constant expressions that can not be evaluated at query compile time.
    • If possible, use index covering.
    • If stored procedure accepts parameters, it is better to recompile it at every execution.
    • Check the indexes are on proper columns.
    • Describe the tables in from clause so as the smaller table occurs first and then the larger tables 
  57. Question 35. What Is An Index? How Many Types Of Indexes Are There?

    Answer :

    Index is used to provide faster access to the data in a table than scanning every page. Sometimes index can be used as mechanism for enforcing uniqueness.
    There are 2 types of indexes: Clustered and Non clustered

  58. Question 36. What Is The Difference Between Clustered And Non-clustered Indexes?

    Answer :

    Clustered Index: – Data is physically sorted. The bottom or leaf level of a clustered index contains the actual data pages of the table. So there can be only one clustered index per table.
    Non-Clustered Index: – With a non-clustered index, the physical order of the rows is not the same as their indexed order. The leaf level of a non-clustered index contains pointers to rows on data pages. More precisely, each leaf page contains an indexed value and a pointer to the row with that value. Up to 249 non-clustered indexes are allowed in a table.

  59. Oracle 11g Interview Questions

  60. Question 37. What Is Index Covering?

    Answer :

    When all the keys of non-clustered index are used in select statement and also in where clause then the non-clustered index does not need to go to last level of index it just pick the data from leaf-level so it takes less time to execute the query this is called Index-Covering.


  61. Question 38. How Can You Force An Index?

    Answer :

    Just write the name or the index number in the bracket to force the index.

  62. Question 39. What Are Different Types Of Locks, Explain?

    Answer :

    Shared Lock: – used by process that is reading pages. Multiple shared locks can be held on one page; a single shared lock prevents any exclusive lock from being acquired. Shared locks typically are held only for the duration of the read on a particular page. If holdlock is specified, shared locks are held until the completion of the command or the transaction it is in.
    Update lock: – used by process that updates or deletes data, but have not yet done so. Update locks are acquired as the set of rows to be updated or deleted is being determined. Update locks are read-compatible with shared locks during the pre modification phase, but no other update lock or exclusive lock can be acquired on the page. Update locks automatically are updated to exclusive locks when the data change occurs.
    Exclusive Lock: – used by process that currently adding, changing or deleting information on data pages. Exclusive locks prevent any other type of lock (exclusive, update or shared) from being acquired. Exclusive locks are held on all affected pages until an explicit transaction or a command in the implicit transaction is complete.

  63. Question 40. How Many Triggers Will Be Fired If Ore Than One Row Is Inserted?

    Answer :

    The numbers of rows you are inserting into a table, that many number of times trigger gets fire.

  64. DB2 Using SQL Interview Questions

  65. Question 41. What Are Advantage Of Using Triggers?

    Answer :

    To maintain the referential integrity.

  66. Question 42. How Do You Optimize A Stored Procedure?

    Answer :

    By creating appropriate indexes on tables. Writing a query based on the index and how to pick up the appropriate index.

  67. MYSQL DBA Interview Questions

  68. Question 43. How Do You Optimize A Select Statement?

    Answer :

    Using the SARG’s in the where clause, checking the query plan using the set show plan on. If the query is not considering the proper index, then will have to force the correct index to run the query faster.

  69. Question 44. How Do You Force A Transaction To Fail?

    Answer :

    By killing a process you can force a transaction to fail.

  70. Question 45. What Are Constraints? Explain Different Types Of Constraints?

    Answer :

    Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.

  71. Question 46. How Frequently You Defrag The Database?

    Answer :

    When ever there are insertions, updations & deletions in a table we do defrag.

  72. Question 47. How Do You Tell The Data Time Of Server Started?

    Answer :

    select “Server Start Time” = crdate from master..sydatabases where name = “tempdb” or
    select * from sysengines


  73. Question 48. Is There Any Disadvantage Of Splitting Up Your Application Data Into A Number Of Different Databases?

    Answer :

    When there are relations between tables / objects across the different databases, then there is a disadvantage indeed: if you would restore a dump of one of the databases, those relations may not be consistent anymore. This means that you should always back up a consistent set of databases is the unit of backup / restore. Therefore, when making this kind of design decision, backup/restore issues should be considered (and the DBA should be consulted).

  74. Question 49. What Are The Main Advantages And Disadvantages Of Using Identity Columns?

    Answer :

    The main advantage of an identity column is that it can generate unique, sequential numbers very efficiently,requiring only a minimal amount of I/O. The disadvantage is that the generated values themselves are not transactional, and that the identity values may jump enormously when the server is shutdown the rough way (resulting in “identity gaps”). 

  75. Question 50. How Do You Bcp Only A Certain Set Of Rows Out Of A Large Table?

    Answer :

    If you’re in ASE 11.5 or later, create a view for those rows and BCP out from the view. In earlier ASE versions, you’ll have to select those rows into a separate table first and BCP out from that table. In both cases, the speed of copying the data depends on whether there is a suitable index for retrieving the rows.