Sybase Database Interview Questions and Answers: Interview Question's

Showing posts with label Interview Question's. Show all posts

Sybase problems 3

1. I have written a query which you can see below. The query qpproximately takes 50- 60 SEC to run,
which I believe is way too high. The total rows returned is 10,000 .
Any idea to improve the query timings ?

The number of rows in each table are as follows :
ACCOUNTS : 1,580
PERSONNEL : 1,616
DIVISION : 15
LABOR_COMMITTED : 23,000
FISCAL_SYSTEM_PERIODS : 20
FISCAL_YEARS : 20

INDEXES :
ACCOUNTS TABLE- 1. ACCOUNT_ID ( Primary Key), 2. IN_AC_COMBINED(ACCOUNT_ID , ACCOUNT_MANAGER) LABOR_COMMITTED TABLE- 1.LABOR_COMMIT_ID ( Primary Key) 2. IN_LC_COMBINED(PERSONNEL_ID, ACCOUNT_ID) PERSONNEL TABLE- 1. PERSONNEL_ID ( Primary key)

The Actual Query :
SELECT AC.ACCOUNT_NUMBER , AC.ACCOUNT_NAME , AC.ACCOUNT_END_DATE , P1.NAME , D.DIVISION_NUMBER , P.SURNAME_ISI || ' '|| P.FIRST_NAME Person_Name, ISNULL(P.EMPLOYEE_ID,P.VISITOR_NUMBER) Employee_Number, CONVERT(DATETIME , FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FY.FISCAL_YEAR) Month_Year , FSP.FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FY.FISCAL_YEAR Month/Year, ROUND(LC.LCS_EFFORT,2) LCS_Effort , 'ACTUAL' AS 'Labor Type' , (SELECT CODE_VALUE FROM CODES CD WHERE CODE_ID = P.STAFF_CATEGORY_CODE_ID) STAFF_CAT FROM ACCOUNTS AC(INDEX IN_AC_COMBINED), PERSONNEL P1 ,DIVISIONS D, LABOR_COMMITED LC (index in_lc_combined) , PERSONNEL P , FISCAL_SYSTEM_PERIODS FSP , FISCAL_YEARS FY WHERE P1.PERSONNEL_ID IN (SELECT DISTINCT CHILD.PERSONNEL_ID FROM PERSONNEL CHILD, PERSONNEL PARENT WHERE PARENT.PERSONNEL_ID IN (7167,7041) AND CHILD.LFT BETWEEN PARENT.LFT AND PARENT.RGT AND PARENT.LFT <> 0 AND PARENT.RGT <> 0) AND AC.ACCOUNT_MANAGER = P1.PERSONNEL_ID AND P1.DIVISION_ID = D.DIVISION_ID AND AC.ACCOUNT_ID = LC.ACCOUNT_ID AND LC.PERSONNEL_ID = P.PERSONNEL_ID AND FSP.FISCAL_SYSTEM_PERIOD_ID = LC.FISCAL_PERIOD_ID AND FY.FISCAL_YEAR_ID = LC.FISCAL_YEAR_ID AND LC.COMMITED_DATE IS NOT NULL AND CONVERT(DATETIME,FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FISCAL_YEAR) <= CONVERT(DATETIME,'January, 2007') AND CONVERT(DATETIME,FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ' ' || FISCAL_YEAR) >= CONVERT(DATETIME,'February, 2006')

----------------------------------------------------------------------------------------------------------------------------
2. I have a table with 10 million rows. about 9.5 million rows are identified by certain criteria must be deleted from this table.
If i use DELETE statment for this, optimizer takes nearly 15-20 minutes to execute the query,so how can i minimize the execution time?

------------------------------------------------------------------------------------------------------------------------------

3. I was working on a stored procedure for a pending QC . I need to negate the bskSymbol which have multiple consSymbol .

so for that i am using the query given below and saving that in a #table .

select bskSymbol from RSK_IM_ModifyBasket group by bskSymbol having count(*) > 1 and BatchId = 0

or Like ->

select into ... #temporary table
....
bskSymbol not in (select bskSymbol
from RSK_IM_ModifyBasket b
group by bskSymbol
having count(*) > 1 and BatchId = 0
)

does not produce any one to one mapping of bskSymbol and consSymbol .

where as the query given below shows some bskSymbol columns with one to one mapping of bskSymbol and consSymbol . by one to one mapping i mean for some bskSymbol we have count value as 1 .

select bskSymbol,count(consSymbol) from RSK_IM_ModifyBasket where BatchId = 0 group by bskSymbol

can u suggest where am i wrong ?.

---------------------------------------------------------------------------------------------------------------------------------------------------------
Hi friends,
I am facing a problem with bcp process on the following environment
OS - sun solaris
database - sybase

I have 13 tables in my database with max 8450782 rows in a table.
so I have write a script for unix, in which whole bcp process is done in one shot being a single process in background(bcp-out from source table , truncate on destination table,bcp-in ). in this way my all the 13 tables refreshes simultaniously.my tables have not any indexes,or other referential dependency.
some times this whole process slow down significantly.so is there any issue with multiple tables refreshing.
is my this approach is wrong or what i need to speed up the process.
any suggestion will be appriciated.

sol:

think you might be limited by your disk I/O speeds. But things you can use to try and speed up the process is a fifo special file (man mkfifo) as the bcp file and a larger network packet size (bcp -A 8704)
Would it be worth altering your unix script to do one table at a time. It might avoid the competition for resources. It would be good to time each stage so you can see where the issue is.

Do you drop the indexes on the destination table before bcping in, then create them again afterwards? hoping to use fast bcp.

Another option might be to try putting the 13 tables into their own database and simply dump the database at the source and restore it on the destination rather than bcping in and out each table.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Does anyone know of an easy way to merge Sybase users from 2 dataservers into one? Syslogins is different on both servers.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sybase problems 2

Cursor - Not fetching next record
Posted: Apr 13, 2007 5:45 PM Reply

Hi,
I am BusinessObjects Developer and new to Sybase-IQ.
I had created on stored procedure and facing an issue that the cursor is not fetching next record.
Can anybody help to debug this issue.

Below is the stored procedure.
declare @catcode varchar(12);
declare @MX integer;
declare @MTH integer;
declare @Yr integer;
declare @Yr_Max integer;
declare @Yr_Min integer;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;

open CRS_catcode;

set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;

CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;

if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;
set @Yr=@Yr+1
end loop
end if;
if sqlstate = err_notfound then
print sqlstate;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
close CRS_catcode
end
end

-------------------------------------------------------------------------------------------------------------
Re: Cursor - Not fetching next record
Posted: Jul 12, 2007 5:34 PM Reply

hi dear
u have not use fetch next statement in your sp before the close of the cursor

try this , i think it will help u

/* Notice: Formatted SQL is not the same as input */
(52,32)then
DECLARE @catcode VARCHAR(12);
DECLARE @MX INTEGER;
DECLARE @MTH INTEGER;
DECLARE @Yr INTEGER;
DECLARE @Yr_Max INTEGER;
DECLARE @Yr_Min INTEGER;
begin
declare CRS_catcode SCROLL cursor for
select distinct CATEGORY_CODE_13
from DBA.SOD_Catcode
order by convert(integer,CATEGORY_CODE_13) asc;

open CRS_catcode;

set @Yr_MIN=2000;
set @Yr_MAX=2007;
set @Yr=@Yr_MIN;

CAT_LOOP:
LOOP
fetch next CRS_catcode
into @catcode ;

if convert(int,@catcode) >= 842 then
leave CAT_LOOP;
else
set @Yr=@Yr_MIN;
while @Yr <= 2007 loop
print @Yr;
set @MTH=1;
while @MTH <= 12 loop
--Domestic
update dba.SOD_CATCODE set
SOD_CATCODE.Rolling_Month_Amt = AAM from
(select B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,
B.ledger_type,B.SOD_PAGE,SUM(B.ACTUAL_AMOUNT_MTD) as AAM from
dba.SOD_CATCODE as B where
B.category_code_13 = @CATCODE and B.FISCAL_YEAR = @Yr and
B.FISCAL_MONTH >= 1 and B.FISCAL_MONTH <= @MTH and B.LEDGER_TYPE like 'DOM%'
group by B.buss_unit_desc,B.LINE_ITEM_DESC,B.GBO,B.ledger_type,B.SOD_PAGE) as B,dba.SOD_CATCODE
where
SOD_CATCODE.category_code_13 = @CATCODE and SOD_CATCODE.GBO = B.GBO and
SOD_CATCODE.buss_unit_desc = B.buss_unit_desc and SOD_CATCODE.ledger_type like 'DOM%' and
SOD_CATCODE.LINE_ITEM_DESC = B.LINE_ITEM_DESC and SOD_CATCODE.SOD_PAGE = B.SOD_PAGE and
SOD_CATCODE.FISCAL_YEAR = @Yr and SOD_CATCODE.FISCAL_MONTH = @MTH;
commit work;
set @MTH=@MTH+1
end loop;

set @Yr=@Yr+1
end loop
end if;

IF sqlState = Err_NotFound
IF sqlState = Err_NotFound Then
PRINT sqlState;
LEAVE CAT_LOOP;
end if ;
end loop CAT_LOOP;
----------------------------------------------
fetch next CRS_catcode
into @catcode ;
-----------------------------------------------
end

close CRS_catcode

end

Sybase problems

1. I am facing a problem with bcp process on the following environment
OS - Linux
database - sybase

I have 13 tables in my database with max 8450782 rows in a table.
so I have write a script for unix, in which whole bcp process is done in one shot being a single process in background(bcp-out from source table , truncate on destination table,bcp-in ). in this way my all the 13 tables refreshes simultaniously.my tables have not any indexes,or other referential dependency.
some times this whole process slow down significantly.so is there any issue with multiple tables refreshing.
is my this approach is wrong or what i need to speed up the process.
------------------------------------------------------------------------------------------------------------------------------------------------------------
2. I am having five user in each Sybase server,Now i want to merge Sybase users from 2 dataservers into one? Syslogins is different on both servers how do i do this in efficient way.
------------------------------------------------------------------------------------------------------------------------------------------------------------
3. I have the following procedure:

create PROCEDURE sp_SEARCH_Contragents(@nSPFirmID integer,@nFilterID integer,@szName varchar(255)=null,@szTaxNum varchar(255)=null,@szBULSTAT varchar(255)=null,@szFullCode varchar(255)=null,@szCity varchar(255)=null,@szPhone varchar(255)=null,@szAddress varchar(255)=null)
as
begin
declare @szNameSign varchar(2),
@szTaxNumSign varchar(2),
@szBULSTATSign varchar(2),
@szFullCodeSign varchar(2),
@szCitySign varchar(2),
@szPhoneSign varchar(2),
@szAddressSign varchar(2)

create table #tResult(
Name varchar(255) null,
TaxNum varchar(30) null,
BULSTAT varchar(10) null,
FullCode varchar(100) null,
City varchar(255) null,
Phone varchar(100) null,
Address varchar(255) null,
FoldID integer null,
)

insert into #tResult( Name,TaxNum,BULSTAT,FullCode,City,Phone,Address,Fo ldID)
select..........from where.............

insert into #tResult(Name,TaxNum,BULSTAT,FullCode,City,Phone,A ddress,FoldID)
select.................. from where..............

select Name as Name,
TaxNum as TaxNum,
BULSTAT as BULSTAT,
FullCode as FullCode,
City as City,
Phone as Phone,
Address as Address,
FoldID as FoldID
from #tResult
end

If I execute the procedure from ISQL with some input values it is working correctly. But if I try to execute it from Java like

ResultSet res = stmt.executeQuery("exec sp_SEARCH_Contragents ");

or like this

CallableStatement cs = conn.PreparedCall("{call sp_SEARCH_Contragents )}");

I don't get any rows or error,could you fick the problem where i am having ?

Set III

If you are create trigger for insert in which you are going to insert 10 rows how many time it will fire?

Only once

Compare IN and EXISTS?

in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns

What is Index Covering?

Index covering is a mechanism for using the leaf level of nonclustered index the way the data page of a clustered index would work. Index covering occurs when all columns referenced in a query are contained in the index itself.

What is Checkpoint?

The point at which all data pages that have been changed are guaranteed to have been written to the database device.

What is direct update and deffered update?

Direct Update:An update operation that takes place in a single step, that is, the log records are written and the data and index pages are changed. Direct updates can be performed in three ways: in-place update, on-page update, and delete/insert direct update.
Deffered Update:An update operation that takes place in two steps. First, the log records for deleting existing entries and inserting new entries are written to the log, but only the delete changes to the data pages and indexes take place. In the second step, the log pages are rescanned, and the insert operations are performed on the data pages and indexes.

What is @@spid,@@rowcount,@@error?

returns the no of rows affected by the preceding query

I what to see all locks in the server?

sp_lock,syslocks

What is sp_help and sp_helpdb?

sp_help use to display the objects in a database,sp_helpdb used to display the databases in your server

What are the different types of lock in Sybase?

Shared locks :SQL server applies shared lock for read operations. All the processes can read, but no process can write.
Update lock :allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed.

Exclusive lock: SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.

Explain Join order for Performance?

The optimal query plan for a join involves picking the best indexes for each table and the most efficient order to process the tables in the joins. The query optimizer checks all the join orders for cost optimization. The minimum I/O time which reduces cost will be selected for processing. If many tables are joined, SQL breaks into possible tables of a group. Examine the tables in the group and query optimizer performs permutation on each table and makes the final four tables, which minimize the cost and I/Os.

Explain Index and Performance?

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

what is a ghost record ?

These are records that have been logically deleted but not physically deleted from the leaf level of an index.
The reasons for this are complicated, but basically having ghost records simplfies key-range locking and transaction rollback.
The record is marked with a bit that indicates it's a ghost record and cannot be physically deleted until the transaction that caused it to be ghosted commits. Once this is done, it is deleted by an asynchronous background proces (called the ghost-cleanup task) or it is converted back to a real record by an insert of a record with the exact same set of keys.
Ghost records will be mentioned later in the series when I discuss page compaction.

Compare Delete and Truncate?

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.

What is the use of 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.

What are the Inbuilt User defined datatypes available in Sybase?

sysnames,timestamps

Write a query to select all records that do not have a null value?

How the Hotspot can be Resolved?

using heap table

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

set rowcount 500

Write a query to find out the Nth max and min?

To find Nth max:

select * from table_name T1 where (N-1)=(select count (distinct T2.column_name)from table_name T2 where T2.column_name>T1.col_name)

To find Nth min:

select * from table_name T1 where (N-1)=(select count (distinct T2.column_name) from table_name T2 where T2.column_name

What system procedures are used to list all the indexes?

sp_indexes

What are the modes of transaction? And how do you change from one mode to another?

unchained mode: The default mode, called unchained or Transact-SQL mode,
requires explicit begin transaction statements paired with commit
transaction or rollback transaction statements to complete the
transaction.

chained mode: The SQL standards-compatible mode, called chained mode,
implicitly begins a transaction before any data retrieval or
modification statement. These statements include: delete, insert,
open, fetch, select, and update. You must still explicitly end the
transaction with commit transaction or rollback transaction.
You can set either mode using the chained option of the set command.

How do u run a file with lot of queries on ISQL?

isql -U -P -S -iinput file

What is the error code of Deadlock and How will you eliminate Deadlock?

errorcode for dead lock is 1205 and for stored procedure is -3.

How do you handle Error in Sybase?

The @@ERROR Function: SQL Server sets the @@ERROR object. If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designate error code. All SQL Server error codes can be found within the master.dbo.sysmessages system table. One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable.

SP_ADDMESSAGE:Use the sp_addmessage feature to add your own customized messages. The following information is given about each parameter:

RAISERROR:You can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application.

How do you handle error in BCP?

-e

Write a query to convert the date to dd/mm/yy?

select convert(char,date_exp,101)

When a query is sent to the database and an index is not being used, what type of execution is taking

table scan

place?

What is an advantage to using a stored procedure as opposed to passing an SQL query from an

faster execution , avoid dead lock,avoid network traffic

application

What are defaults? Is there a column to which a default can't be bound?

When you are inserting suppose forgot to enter the value for particular column than you will provide some constant value for that .identity ,timestamps column are default cannot bound

Where do you think the users names and passwords will be stored in sql server?

syslogins

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

What are user defined datatypes and when you should go for them?

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

What is bit datatype and what's the information that can be stored inside a bit column?

Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

What are constraints? Explain different types of constraints?

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

What are “GRANT” and “REVOKE’ statements?

What are Wildcard operators in sybase?

Selects documents that contain matches to a character string containing variables. The WILDCARD operator lets you define a search string with variables, which can be used to locate related word matches in documents.

How will you find second maximum value in a table?

select max(column) from table where column<(select max(column) from table)

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.

What are the types of Direct update not in place?

cheap direct update and Expensive direct update

How the Trigger differ from rules and constraints?

If I want current database name?

db_name()

If I want to check my query syntax what shall I do?

set parseonly on

Why we should go for Deallocating the cursors?

How do you call the Remote stored Procedures?

exec server_name.db_name.proc_name

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

Explain BCP and Performance?

Explain Tempdb and Performance?

What is Thresholds and how will you add ?

What is the maximum row length limited by size of data page in SQL Server?

2KB

Set II

what is In & OUT in stored procedure?

The output keyword may be specified in the sp creation statement if the parameters can be passed both in and out of the sp,when passing out you must specifiy output keyword in execution statement

Tell me the limitation of the Trigger?

any create commend,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger

can I update view?

yes,but view had created using only one table than update is possiable

what is difference between constraints and rules?

rules deals with constant and one rule can bound one column,constraints can bound many column and it can be compare with column in another table

how will you bind a rule?

sp_bindrule

explain about @@sqlstatus?

It returns the status of the pervious fetch statement in a cursor

What are the steps involved in creating cursor?

Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors

What is Transaction Log?

It is a database level system table called syslogs,which contains a sequential list of all modification to every objects in the database.

compare Rollback transaction and Rollback Trigger?

How will bcp out the Identity column?

-E

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.

How to get the last month’s last Day?

select datename(dw,dateadd(dd,30-datepart(dd,getdate()),dateadd(mm,-1,getdate())))

What is patindex()?

returns the starting postion of the specified ,else 0

How do I get the Duplicate rows from a table?

select * from table name group by column1 having count(*)>1

what is command permission and object permission?

OP is Grant/revoke to these objects for permissions which includes select,update,delete,execute.CP is grant /revoke permission for create (db,table,view,procedure,defaults)

Explain Stored Procedures on performance?

Query optimizer generates a query plan for a st. proc based on the parameters parsed in the first time it is executed. To make st.prc to perform well, first the quires used in the procedure are to be checked. Also since it is compiled and stored in memory, procedure may become inefficient over time. Further execution used only if it is gain otherwise again the st. proc is recompiled.
For example when it was first compiled, it may be fast as the table size is small and the table scan is better choice. But when the table grows into huge, index selection may be a better option than the table scans for that query. But the procedure will still do only table scan as it is pre-compiled, so as a general rule, if the table size changes by 20%, we have to run update statistics on the table and recompile the stored procedure. For stored proc in which, the parameter has the strong influence over the result set, we have to make it with recompile option, so that it compiles every time it is accessed.

What is sp_dboption?

It is used to enable or disable db option for your database

Explain Denormalization techniques?

Denormalization is used for performance boosting. In which normalized db is denormalized for the sake of performance. According to normalization all columns has to depend on the primary key, but some times creating a manipulated field.

Redundant column may be used in a table when we always join the first table to get a column data from the second table using foreign key. Data partitioning techniques like vertically splitting the tables, horizontally splitting the tables can be used to denormalize a db. Vertical split technique is typically used to minimize the size of the primary table or to move infrequently accessed columns to a separate table. Horizontal split technique involves splitting the table at row level into two or more tables. For ex, if we can split the table into two tables, so that the first table has the recent entries and a table that has historical data, we can drastically increase the size of the new table, whose columns are going to access frequently

Which is better Stored procedure or Trigger?

If you intend to set or modify some column values in the proc/trig, a trig
is the right place to
do it. A BEFORE trigger can directly modify the row buffer before it is
updated/inserted. A
procedure would have to issue a UPDATE, thus a performance penalty.
---------------------
2. The trigger has all the row data automatically available to it. For the
procedure to have the
same information, you'll either have to pass all values to the proc via
parms, or the proc will need
to re-read the data, a performance penalty.
-----------------------------
3. If your application issues a single update, delete, or insert that modifies
multiple rows, a FOR
EACH ROW trigger will be fired for each row. It could be difficult to
provide the SP the
information that allows it to identify the set of rows that were modified.

I made two tables A B id1 age id1 age 1 Asked me to find out all the id1's which are in table A and

select * from A where not exists(select id1 from B)

do not exist in table b

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

Compare left and Right Outer Join?

For left join all row was selected in outer table and for inner table row which are not meet the join condition are filled with NULL. In Right join inner table all row was selected and for outer table the rows which not meet the join conditins will filled with NULL

What is Join and Its Types?

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

what are different global variables ?

@@rowcount-returns no of rows processed by preceeding command.
@@error- returns the error code for last or current query.
@@transtate- returns the current transaction state.
@@tranchained-returns the current transcation mode.
@@server- returns the server name.
@@version-returns the version of the sql server and OS used in a system.
@@spid- current process id.
@@identity-returns the lastly incremented identity value.
@@nestlevel-No of level in stored procedures/trigger.
@@sqlstatus-status of previous fetch statement in cursor.

what is temporary tables and its types?

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.

Compare Union and Union all?

union remove the duplicates and go for worktable creation where as union all allow duplicates and don't go for WT

Compare Subquery and Correlated Subquery?

What is Update Statistics?

Compare Primary key and Unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

Advantages and disadvantages of cursors?

Compare Select into and Insert into?

select into it will create default table and copy the value to that table from other,insert into use to insert the value only in existing table

What is “COMPUTE BY” clause in Sybase?

When ever we use compute by ,we must also include order by

What is Check point in Transaction Log?

Checkpoint writes all dirty pages for the database from cache to disk,starting with the log and also it reduces the amount of work the server need to do at recovery time

Compare 2 nd and 3 rd Normal form?

Second normal form-nonkey fields must depend on the entire pr. Key.
Third normal form---nonkey fields must not depend on other nonkey fields.

What is –I option in Sybase tools?

It is the name of the interface file to use when trying to find a server to connect to

Is there any way to create Primary or Foreignkey on a table other than Query?

sp_primarykey,sp_foreginkey

If I want to round the value 999.99 as 1000 ?

round(999.99,0)

What is Distribution page?

server keeps distribution information for each index on a separate page in the datebase

Compare Composite Index and Multiple Index

What is Row Padding?

Locking of the page for a long time make the other process to wait. So the time required to complete the process will increase which is not desirable. SQL prevents dirty reads by using shared locks. Make the row big enough so that only one row fit on a page which avoid the locking of page. This is called row padding.

Explain Database and Performance?

How will you create Login name and User in Sybase?

sp_addlogin,sp_adduser

Is it necessary to drop & recreate all procedures and triggers every few months ?

What is difference between Count() and Count(*)?

count(*) will inclued null rows and faster

Advantage of Stored Procedure?

faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency

In a query which is better? using IN or EXISTS?

in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns

what are triggers? advantage ? disadvantage?

How do u copy the output results into a file?

isql -Usa -P -S -oos_file_name

Write a query to find out the Nth max and min?

CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after

non clustered index

executing the above statement?

Questions and Answers Set I

Questions
Answers
1
what is stored procedure?what is the maximum parameter can pass through it?
It is a db objects that exists independly of the table ,Which contain a set of query.maximum paraperters pass through it is 255
2
what is Advantage of stored procedure?
faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency
3
what is Trigger?how many trigger can a table have and what are?
It is internal part of a statement that fired when insert,update,delete operation taken place.Three trigger,Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
4
What is magic table?
The table that exists within a trigger named as Inserted and deleted are Magic table
5
what is view?how many table can join for creating view?
It is logical way of looking at physical data located in a table,16
6
how will you add a column and drop a column?
Alter table table name [add][drop] column name
7
explain about @@sqlstatus?
It returns the status of the pervious fetch statement in a cursor
8
compare WHERE clause and HAVING clause?
Where clause cannot have Aggeregate function ,and it select the row before the group by function .having select the row after the group by
9
What is dead lock?
A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources.
10
What is sp_dboption?
It is used to enable or disable db option for your database
11
What is CURSOR?
It is a pointer , use for row by row operation
12
Why we should go for Deallocating the cursors?
When we Deallocate the cursor so that server will clear the memory space occupied by the cursor we can use that space for some other action
13
How many index can have for a table?
250
14
What is Clustered and Nonclustered index?
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
15
How do I get the Duplicate rows from a table?
select * from table group by column having count(*)>1
16
While creating index I forgot to tell what type what will happen?
nonclustered index
17
Compare Primary key and Unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
18
What is BCP?and Types?
It is used for import and export the values from table to os file and vice-ver.Fast bcp and Slow bcp
19
Compare left and Right Outer Join?
For left join all row was selected in outer table and for inner table row which are not meet the join condition are filled with NULL. In Right join inner table all row was selected and for outer table the rows which not meet the join conditins will filled with NULL
20
While bcp in default,rules,constraints can be applied?
Only default can apply
21
What is Hotspot?
Whan a multipe processes attempt to modify a same page in table
22
How do I force the lock?
Using Holdlock
23
What is Isolation levels and explain them?
The categories of locking behavior within transaction that are defined by ANSI,Level are 0,1,2,3
24
how to get Yesterday’s date?
dataadd(dd,-1,getdate())
25
What is –I option in Sybase tools?
It is the name of the interface file to use when trying to find a server to connect to
26
How do u copy the output results into a file?
isql -Usa -P -S -oos_file_name
27
What are defaults? Is there a column to which a default can't be bound?
When you are inserting suppose forgot to enter the value for particular column than you will provide some constant value for that .identity ,timestamps column are default cannot bound
28
If I want current database name?
db_name()
29
If i want creation text of a particular object (such as SP,Trigger,view,rule,default)what shall i do
sp_helptext,defncopy,syscomments
30
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.
31
What are the different types of lock in Sybase?
Shared locks :SQL server applies shared lock for read operations. All the processes can read, but no process can write.
Update lock :allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed.

Exclusive lock: SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.
32
List out all System procedure in your server?
select name from sysobjects where type='P'
33
There's a performance issue What will be your very first step towards the solution?
First 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.
34
What is @@rowcount?
returns the no of rows affected by the preceding query
35
How do you check whether the rollback was correct?
By checking the @@trancount value before rollback statement execute
36
What is Index Covering?
Index covering is a mechanism for using the leaf level of nonclustered index the way the data page of a clustered index would work. Index covering occurs when all columns referenced in a query are contained in the index itself.
37
How do you select unique rows using Sybase?
using Distinct keyword
39
How many database Sybase 11 have ,list out with explanations?
four.Master,Model,sybsystemprocs,tempdb
40
What is Roll Forward and Roll Back?
Roll forward : commited transaction not written to data area are rolled forward into the data.Roll back:uncommited transaction rollback all data modification done with in transaction are reversed
41
If I want to round the value 999.99 as 1000 ?
round(999.99,0)
42
What is Phantom reads?
when one transaction reads a set of rows that satisfy a search
condition, and then a second transaction modifies the data (through an
insert, delete, update, and so on). If the first transaction repeats
the read with the same search conditions, it obtains a different set
of rows.
43
How will you Restart and Exit on while loop?
continue,break
44
Compare Delete and Truncate?
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.
45
How many columns can table have?
250
46
How will you find second maximum value in a table?
select max(column1) from table where column1<(select max(column1) from table)
47
How shall I simulate from level 0 to level 3 in Isolation?
using holdlock
48
Can I explicitly insert a value in a Identity column?
set identity_insert on
49
Can I change the data type of column ?
yes using modify keyword
50
How will you tune a query?

Sybase Interview Questions and Answers

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

"

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

100
400

In order to ensure that server can correctly identify the method call it is wrapped up in a SOAP envelop

100
400

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 Marshall and UN-Marshall the XML messages myself?
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

Sybase Database Interview Questions and Answers