Explore topic-wise InterviewSolutions in .

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.

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.

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”). 

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”). 

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).

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).

select “SERVER START TIME” = crdate from master..sydatabases where NAME = “TEMPDB” or
select * from sysengines

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

When EVER there are INSERTIONS, updations & DELETIONS in a table we do defrag.

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

CONSTRAINTS ENABLE the RDBMS ENFORCE the integrity of the database automatically, without needing you to CREATE TRIGGERS, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

By KILLING a PROCESS you can FORCE a TRANSACTION to FAIL.

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

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.

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.

By creating APPROPRIATE INDEXES on TABLES. Writing a query BASED on the index and how to pick up the appropriate index.

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

To MAINTAIN the REFERENTIAL INTEGRITY.

To maintain the referential integrity.

The NUMBERS of rows you are inserting into a table, that many NUMBER of TIMES trigger GETS fire.

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

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.

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.

Just WRITE the name or the INDEX number in the bracket to FORCE the index.

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

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.

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.

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

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

• 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 

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.

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.

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
Ex:
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.

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
Ex:
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.

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”)

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”)

CD $SYBASE/$SYBASE_ASE/install

cd $SYBASE/$SYBASE_ASE/install

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.

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.

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.

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.

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

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

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.

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.

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: 
/usr/sybase/sys.config/{start,stop}.sybase 
/etc/init.d/sybas

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: 
/usr/sybase/sys.config/{start,stop}.sybase 
/etc/init.d/sybas

syb_quit(0) command can be USED to EXIT from SYBASE DATABASE .

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

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).

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).

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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.

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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
iii.insert
Two tables are created at the time of trigger execution
1. INSERTED
2. Deleted which are called magic tables.

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
iii.insert
Two tables are created at the time of trigger execution
1. Inserted
2. Deleted which are called magic tables.

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.

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.

16 LEVELS i.e. 16 NESTED TRIGGERS can be there in SYBASE

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

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

Sybase has worlds leading products some of them are

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.

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.

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.

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:

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