Sybase Database Interview Questions and Answers

How many rows may be retrieved by the select statement in the following SQL

2009-08-20T19:59:00.002-07:00

create table t1 (k int unique) insert t1 values (rand()*1000) insert t1 values (rand()*1000) insert t1 values (rand()*1000) [...1000 additional inserts...] select * from t1 where k = rand()*1000
Ans: 0 or 1

Explain the set commands given below

2009-08-20T19:59:00.001-07:00

1) set rowcount (Instruct the server to rturn only the first n rows of data)
2) set nocount on ( stops reporting the number of rows returned)
3) set noexec on ( parse and optimize, but don’t exec the query (used with show plan for looking at the plan without running a query)) showplan gives the final optimization plan for a query)
4) set statistics time on (requests the execution time)
Construct the Query using the Tables album and track of music database
album
Asin
Title
artist
price
release
label
Rank
B00000255F
Born to Run
Bruce Springsteen
13.98
1975-08-25
Sony
521
B000002UAU
Sgt. Pepper's Lonely Hearts Club Band
The Beatles
13.49
1967-06-01
Capitol
218
...
Track
Album
dsk
posn
Song
B00000255F
1
1
Thunder Road
B00000255F
1
2
Tenth Avenue Freeze-Out
B00000255F
1
3
Night
B00000255F
1
4
Backstreets
B00000255F
1
5
Born to Run
...
B000002UAU
1
1
Sgt. Pepper's Lonely Hearts Club Band
B000002UAU
1
2
With a Little Help from My Friends
B000002UAU
1
3
Lucy in the Sky With Diamonds
B000002UAU
1
4
Getting Better
B000002UAU
1
5
She's Leaving Home
...
A "title track" is where the song is the same as the title. Find the title
Tracks
select song "title tracks" from album,track where album.asin=track.album and album.title=track.song
Find the songs that appear on more than 2 albums. Include a count of the number of times each shows up
select song, count (album) from album, track where album.asin=track.album group by song having count(song)>2
A "good value" album is one where the price per track is less than 50 pence. Find the good value album - show the title, the price and the number of tracks
select title,price,count(song) from album,track where album.asin=track.album group by title having (price/count(song))<0.5
Wagner's Ring cycle has an imposing 173 tracks, Bing Crosby clocks up 101 tracks
List albums so that the album with the most tracks is first. Show the title and the number of tracks
select title, count(song) from album,track where album.asin=track.album group by title order by count(song) desc

Find the Top album of each company

2009-08-20T19:59:00.000-07:00

select title ,label ,min(rank) from album group by label

Find the title and artist who recorded the song 'Alison'

2009-07-04T02:39:00.000-07:00

Ans: select title,artist from album,track where track.song="Alison"

Explain timestamp datatype?

2009-07-03T02:38:00.000-07:00

Construct the Query using the Tables album and track of music database

album
Asin	title	artist	price	release	label	Rank
B00000255F	Born to Run	Bruce Springsteen	13.98	1975-08-25	Sony	521
B000002UAU	Sgt. Pepper's Lonely Hearts Club Band	The Beatles	13.49	1967-06-01	Capitol	218
...

Track
Album	dsk	posn	Song
B00000255F	1	1	Thunder Road
B00000255F	1	2	Tenth Avenue Freeze-Out
B00000255F	1	3	Night
B00000255F	1	4	Backstreets
B00000255F	1	5	Born to Run
...
B000002UAU	1	1	Sgt. Pepper's Lonely Hearts Club Band
B000002UAU	1	2	With a Little Help from My Friends
B000002UAU	1	3	Lucy in the Sky With Diamonds
B000002UAU	1	4	Getting Better
B000002UAU	1	5	She's Leaving Home
...

How many rows may be retrieved by the select statement in the following SQL code samples?

2009-07-01T01:33:00.000-07:00

1. count() Returns the number of rows found

2. having keyword should always follow the group by

3. A result set based on a query with a proper join condition is called a cartesian product

4. distinct requires an additional sorting step using a worktable

5. subquery always return single column of data

6. once you create a column and declare its datatype, you cannot change that datatype without dropping and recreating the table.

Ans: FTFTF(F)

How many rows may be retrieved by the select statement in the following SQL code samples?

2009-06-30T02:33:00.000-07:00

 create table t1  (k int unique)

               insert t1 values (rand()*1000)

               insert t1 values (rand()*1000)

               insert t1 values (rand()*1000)

               [...1000 additional inserts...]

               select * from t1 where k = rand()*1000

Ans: 0 or 1

What will be the output of the following query?

2009-06-28T02:34:00.000-07:00

Suppose I have a table with following column

E_Id

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

BLL12

BRR

BDGT8

NW23L

KO9G0

B___B

___BN

BSNMA

___12

BNG

“select E_Id from table where E_Id like ‘___’”

Ans: BRR

% -0 or more

_ - any single chr

[]- any single chr listed in the bracket

How do you find the name of the employees those have missed more than 5 days of work in last 30 days

2009-06-27T02:33:00.000-07:00

There are two tables master is Employees and the detail is Attendance. The Employee_ID field is common to both tables. How do you find the name of the employees those have missed more than 5 days of work in last 30 days

Select name from Employees a where a.id in (select b.id from attendance b where a.id=b.id and date between (lastmonth+1 ,lastmonth) presence=0 group by id having count(presence)>4)

What command do we use to rename a database?

2009-06-25T02:29:00.000-07:00

Ans: sp_renamedb ‘oldname’ , ‘newname’
Well sometimes sp_renamedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases? - In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.

Give me the Global variable names for the description given below

2009-06-24T02:32:00.000-07:00

1. Error number reported for last SQL statement ( @@error)
2. Current transaction mode (chained or unchained)(@@tranchained)
3. Status of prevoius fetch statement in a cursor(@@sqlstatus)
4. Transaction nesting level(@@trancount)
5. Current process ID(@@spid)

What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?

2009-06-24T02:31:00.000-07:00

Ans: 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.
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.
But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.
The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view

What is default? Is there a column to which a default can’t be bound?

2009-06-22T02:29:00.000-07:00

Ans: A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them. See CREATE DEFAULT in books online

Write a query to find the duplicate rows from a table?

2009-06-21T02:32:00.000-07:00

Select name, count(*) from tablename group by name having count(*)>1

What is the advantage of stored procedure?

2009-06-20T02:29:00.000-07:00

Ans: Faster Execution: after first exec it become memory resident and do not need to be reparsed, recompiled
Reduced network traffic
Modular programming
Reduced operator error-less info to pass

What is the difference between a sub-query and a correlated sub-query?

2009-06-19T02:29:00.000-07:00

Ans: A subquery is a query that SQL Server must evaluate before it can process the main query. It doesn’t depend on a value in the outer subquery.
A correlated subquery is the one that depends on a value in a join clause in the outer subquery

Sybase problems 3

2008-08-01T03:29:00.003-07:00

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

2008-08-01T03:29:00.001-07:00

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

2008-08-01T03:28:00.000-07:00

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

2008-08-01T03:27:00.000-07:00

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

2008-08-01T03:25:00.000-07:00

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

2008-08-01T03:22:00.001-07:00

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?

Q 122

2008-08-01T03:17:00.000-07:00

I) 1. Give some notes on Indexes.

2. Expalin the steps involved during the first execution and subsequent executions of a stored

procedure.

3.State whether the following statements are true or false

1) Wildcards can be contained in values passed to stored procedure-T(like)

2) Rules, defaults and column properties do not apply to parameters defined with user defiend datatypes -T

3) Parameter name should be 20 characters in length-F(29)

4) In order to make changes in the stored procedure, we can alter the stored procedure-F(drop)

5) The stored procedure return 0 on success and 1 for errors-F (0 , -1 to -99)

4. Explain the set commands given below

1) set rowcount (Instruct the server to rturn only the first n rows of data)

2) set statistics io on (Asks the server for the no. of logical and physical page requests)

3) set nocount on ( stops reporting the number of rows returned)

4) set noexec on ( parse and optimize, but don’t exec the query (used with show plan for looking at the plan without running a query)) showplan gives the final optimization plan for a query)

5) set statistics time on (requests the execution time)

5. What is isolation level, list different isolation levels in Sybase and what is default?

To avoid the manual overriding of locking, we have transaction isolation level which are tied

with transaction.

List of different isolation levels are isolation level 0,1,2,3.

Isolation level 1- this allow read operation can only read pages. No dirty reads are allowed.

Isolation level 0-This allows reading pages that currently are being modified. It allows dirty

read

Isolation level 2-Allows a single page to be read many times within same transaction and guarantees that same value is read each time. This prevent other users to read

Isolation level 3- preventing another transaction from updating, deleting or inserting rows for pages previously read within transaction

Isolation level 1 is the default.

II) The database has the Following relations:

Classes(class,type,country,numGuns,bore,displacement)

Ships(name,class,launched)

Battles(name,date)

Outcomes(ship,battle,result)

Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, class name does not coincide with any ship name in the database). The relation Classes includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The relation Ships includes the ship's name, its class name, and launch year. The relation Battles covers the name and date of the battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the relation Outcomes. Note: the relation Outcomes may include the ships not included in the relation Ships.

Point out the battles in which at least three ships from the same country took part.
Find all ship names beginning with letter "R".
For each class, define the number of ships of this class that were sunk in battles. Result set: class, number of sunked ships.
For each class, consisting of at least three ships, define the number of ships (that must be at least 1) of this class sunk in battles. Result set: class, number of sunken ships.

Q 121

2008-08-01T03:16:00.000-07:00

I) 1. State whether the following is true or false

1) The object names of the SQL server can be upto 50 characters in length and are case-sensitive

2) The name of the tables can be same in the same database

3) The default property for the identity column is null

4) Identity columns must use the numeric datatype and have a scale of 0

Ans: F-30, T, F, T

2. Answer the following

1) A numeric column allows to store upto _38__ decimal places

2) The scale of the numeric datatype for identity column is_0___

3) Views can have 16levels of nesting

4) Maximum number of parameters the stored procedure can accept is _255__

(set identity_insert table_name on)

3. Explain the views with check option

4. Give the function name for the description given

1) Largest integer less than or equal to the specified value-floor(num_expr)

2) Current active roles for user-show_role()

3) Returns a specified part of date_expr value as a string- datename(datepart,dateexpr)

4) Replace expr1, if null with expr2- isnull(expr1, expr2)

5) Generates string of int_expr spaces- space(int_exp)

6) Returns length of expr in bytes-datalength(expr)

5. What are the different kinds of locks in sybase?

Locking: The process of restricting access to resources in a multi-user environment to

maintain security and prevent concurrent access problems. SQL Server

automatically applies locks to tables or pages.

Shared locks—SQL server applies shared lock for read operations. All the processes can read, but no process can write. (Eg- select statement.)

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.

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

II) The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. For each model number specifying pc in the relation "PC", its listed speed (of the processor in MGz), total RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed which is replaced by screen size (in inches). For each printer model in the relation "Printer" it is pointed whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

1) Find the maker producing at least two different PC having speed

not less than 750 MGz. Result set: Maker.

2) Find out Nth min price which includes pc,laptop,printer

2) For each group of laptops with the identical model number, add following record

into PC table: code: minimal code among laptops in the group +20;model:

laptop's max model number +1000;speed: maximal speed among laptops in the

group;ram: maximal ram size among laptops in the group *2 ,hd: maximal hd

capacity among laptops in the group *2;cd: default value;price: maximal price

among laptops in the group divided by 1.5;

insert pc(code,model,speed,ram,hd,price) select min(code)+20,model+1000,max(speed),max(ram)*2,max(hd)*2,max(price)/1.5 from laptop group by model

Sybase Interview Questions and Answers

2008-07-23T01:36:00.000-07:00

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 Questions set

2008-07-23T01:29:00.001-07:00

Questions

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

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

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

What are the difference between clustered and Non clustered index?

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

What are the different types of Indexes?

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

How do you add an index to a column?

What is the difference between primary key and indexes?

How many clustered and non clustered index on a table?

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

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

Can you create indexes on temporary tables?

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

What is the use of clustered and non clustered index?

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

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

How many index a table can have?

What is pros and cons of indexes?

Give me the advantage and disadvantage of forcing the index?

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

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

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

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

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

How would you implement a merge sort in indexes?

Trigger

What is a Trigger?

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

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

What are magic table and where are they accesiable?

How do you create a Trigger?

What are the different types of Trigger?

Why we need Trigger?

How Trigger checks for a particular data update?

What are the Trigger advantage and disadvantage?

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

Explain the Trigger in a Update scenario?

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

How to find out trigger on tables?

How to get program listing of trigger on a tables?

Why do we need Trigger?

Compare truncate and trigger?

What are he trigger upper limits on a table?

Which one will fire first trigger or Referential integrity?

What is the difference between Trigger and Cursors?

What are the limitation on Trigger?

Global Variables

What are the different global variables?

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

How do you get the version of the sybase?

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

What is @@identity?

System Stored Procedures

What is sp_helpdb,sp_helpindex?

What is sp_who?

How do you check the text of the SP?

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

What is sp_dboption?

Sybase Tools

What are the types of BCP?Explain their difference?

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

Is the trigger fires during BCP in?

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

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

What sort of checks can be done in BCP?

How do handle error in BCP?

How do you upload the data?

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

How data validation works before bcp?

In which case fast bcp occurs?

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

What is the option for identity column in bcp?

What steps did you take to tune the BCP?

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

How do you send a selected rows into a file?

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

In bcp how would I specify that commit size?

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

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

How do you copy output to a file using isql?

What is the default field delimeter in bcp?

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

What does -E mean in BCP?

How do you handle error in bcp?

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

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

What is interface file?

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

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

Locks

What are the different locks in sybase?

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

How do you trace the deadlock?

what is lock escalation?

what is row level and page level locking?

What is lock?

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

How do you avoid Deadlock?

How many types of locks are there in sybase?

What are Locks?

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

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

How can you force the lock on a table?

What is the locking level in sybase?

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

How to see the types of locks on all tables?

What would be the status of sp_lock in a deadlock?

How to see deadlock information in sybase?

What is demand locks?

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

Compare deadlock and Blocking?

Performance Tunning

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

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

Explain the steps involed in Tunning the Databases?

How do you Tune a SP ? Explain all steps?

What is your first steps towards Query optimizing?

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

What is show plane used for?

What is Ghost Record?

What is optimization?

Why denormailzation is needed?

What is Normailzation?

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

What are the performance issues in cursor?

How do you force the join order?

What is index covering?

What is Update Statistics?

where do you use update statistics and why?

What are the reasons for a query to run slow?

When is subquery used or avoided?

What are the techniquies followed in denormailzation?

What does set rowcount do?

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

What Indexing will be better for larger master table?

Which one we prefer joins or subqueries?

What is distribution page?

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

What does deffered in the output of the show plan ?

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

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

What is proactive Tunning?

What is reactive Tunning?

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

What happen when you are deleting 100 rows?

Explain about dbcc 3604 and dbcc 3605?

Isolation Levels

What are the Isolation levels?

Can you set the isolation level for single query?

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

How shall I stimulate from level0 to level3?

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

What is Isolation level 3?

Built In Function

Name some of the Buit in functions?

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

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

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

How do you calculate last month's day?

How to get the yesterday's date?

Transaction

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

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

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

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

What is Transaction Blocks?

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

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

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

How do you truncate transaction log?

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

What is savepoint?

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

Compare chain and unchain mode in Transaction?

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

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

What is commit?

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

What are the different modes of transaction?

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

Explain about transaction save points and check points?

What is transaction management?

What is the default transaction level?

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

How does sybase handle rollback?

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

Why the transaction log is called write a head log?

what is the difference between log segment and data segment?

How do you find out that transactio log is full?

What is the default transaction level?

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

Sybase Query Optimization

2008-07-23T01:20:00.000-07:00

The goal is to optimize a set of queries to run faster, improve performance. By knowing the meaning of data you can help query optimizer to develop a better query plan and in some cases you can override the query optimizer to follow a certain query plan.

Suppose you have a batch of queries, which you want to optimize. 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.

Use ‘set showplan on’ to see what query plan is executed by the SQL Server? If query is taking a lot of time to execute it is not possible to execute it again and again. Use ‘set noexec on’ to just build the query plan and not to execute it. When these two options are set SQL Server will parse the query, optimize it and develop a query plan, but will not submit it to execute.

As SQL server uses cost based query optimizer,it’s always better to turn on the following options to check the statistics of the query:

set statistics io on: gives actual logical and physical page reads incurred by the query

set statistics time on: gives total CPU and elapsed time to execute a query and the time to parse and compile the query

---------INDEX------------

Look at the query plan that is generated by the optimizer. Check whether the optimizer is picking up proper indexes for the SARG, OR clauses and Join operations. See whether it is using indexes to execute the query or not, and if yes which indexes are used. If there is an index on the table and optimizer is not using that index to resolve the query, try to figure out why optimizer is not using the index.

---------UPDATE STATISTICS-------------------

As a general advice, once the table gets 20% updated, we have to run the update statistics for the table. Update statistics for a table updates the distribution page[server keeps distribution information for each index on a separate page in the datebase](so distribution statistics) for the indexes used in the table.

Use ‘update statistics’ command to update the statistics for all indexes on a table, using following commands:

To update the statistics of all the indexes related to the table:

update statistics

To update the statistics of a particular index on the table:

update statistics .

Run dbcc traceon 302 for getting the meta-information to know how the query optimizer is handling the index selection and OR clauses. Run dbcc traceon 310 for getting the meta-information to know how query optimizer is picking up the join order (the permutation details).

Again run that query and see if it using the indexes or not. If the query is still not using any indexes, and 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 number after the table name in the query. SQL Server would use that index to satisfy the query. You can always use ‘set statistics io’ option to verify whether there was an I/O savings over the optimizers choice or not.

Identify which indexes to use to resolve the query: clustered or non-clustered. Clustered index is better for range queries, if data is in sorted order, column has number of duplicate values, and column is frequently referenced in the order by clause. Non-clustered index is better for single row lookups, queries containing joins, queries with small range retrieval.

Keep following things in mind for the indexes:

·Unless the OR strategy is applied, SQL server uses only one index per table to satisfy the query.

·Sometimes a table scan may be cheaper than a candidate index in terms of total I/O. e.g. large range retrievals on a non-clustered-index column.

·A non-clustered index that covers a query will be faster than a similarly defined clustered index.

----------JOIN AND SUBQUERY---------------

If the query contains any joins, look what is the join order selected by the optimizer. Joins are performed as a set of nested loops. Generally Join queries consume more memory than sub query, Subquery involves the table creation so affects performance, because of I/O fetches need for processing. Subquery is always better than join if we have hardware bottleneck (memory constraints). Check if you can replace join with the subquery. But if you can scale up your hardware (nowadays it is not a big deal) than go with join.

Order in which the join is performed has effect on the total number of I/O performed. By knowing the meaning of data you can select a particular join order and check if it improves performance. You can force join order with the ‘ set forceplan’ option:

set forceplan {on / off}

It might be possible that optimizer was using different indexes before forcing the join order, as certain indexes may not be useful for certain join orders. As the number of table increases, query optimizer takes more time to determine the join order. If the optimizer is picking the proper join order but taking a lot of time in determining the join order, you can force that particular join order and save time. One another reason query not picking up proper indexes may be “ set table count value” is set to lower number but the number of tables involved in the join operations is relatively higher.

-------------DML-------------

Another factor while optimizing the query is to avoid the transaction log in data modification queries. In the following cases we can avoid the transaction to be logged.

·Use fast BCP instead of slow BCP to avoid the data to be logged.

·Use truncate table instead of delete.

·Use select_into instead of creating temporary table to avoid the transaction log.

A table having indexes and triggers takes long time to perform data modification queries. Depending on how important the query is, you can think of dropping the indexes during the daytime and rebuilding the indexes at end of the day. For these types of queries, in many cases index usage is discouraged, because the update statements .

If you need to perform large data modification its better to drop indexes and rebuild indexes after the data modification operation is performed.

Another factor you want to consider is whether query is using any worktable/temporary table to resolve the query. Creating the worktable to solve the query always take much time to process. Queries containing ‘order by’, ‘group by’, ‘distinct’ always uses a worktable. Check if you can satisfy the query by avoiding these clauses. Probable solutions to avoid these clauses are:

·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 don’t 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.

Check for the following scenarios in the where clause of the query:

·Negative Logic (!=, <>): negative logic always results in the table scan unless index covering is applied.

·Calculated comparison: if right side of an expression needs to be calculated first, it will not be evaluated until it reaches the execution stage.

·If a query is frequently using computed expressions, insert a column of the computed expression in the table. You need to write a trigger to maintain that column. e.g. suppose a query always calculates discounted price from a table, its better to insert a column of discounted price in the table.

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. e.g. During the day you need to handle a lot of queries for OLTP, at that time you can consider to drop the trigger and run stored procedure at end of the day.

Depending upon the type of queries you can think of de-normalizing the tables. Following are the options to de-normalize the data depending upon the importance of the queries:

·Inserting calculated columns: it incurs another overhead to maintain the calculated column value. When a data row is inserted, you need to calculate the value of that column. Depending upon the importance of the query you can insert a calculated column.

·Vertical merge of tables: If a query always performs the join of two or more tables. Its better to merge the tables and build a new table to satisfy that query faster. Again it depends on how much important the query is and how you are going to manage the duplicate data. Duplicating the data improves the query to run faster but it incurs another overhead to maintain the duplicate data.

·Vertical split of tables: If query always select certain columns from the table, its better to separate the columns from the table and perform the query to improve the performance.

·Horizontal merge of tables: Merge the data horizontally. e.g. If a query shows the transaction done by the customer in the current month only and if the user wants to see the record for certain months, you can horizontally combine data from old transactions table and recent transactions table to give the result.

·Horizontal split of tables: Split the data horizontally. e.g. in a online banking scenario, split the data in most recent transactions and old transactions. You can just show the most recent transactions, instead of showing the whole transactions.

Sybase Database Interview Questions and Answers

How many rows may be retrieved by the select statement in the following SQL

Explain the set commands given below

Find the Top album of each company

Find the title and artist who recorded the song 'Alison'

Explain timestamp datatype?

How many rows may be retrieved by the select statement in the following SQL code samples?

How many rows may be retrieved by the select statement in the following SQL code samples?

What will be the output of the following query?

How do you find the name of the employees those have missed more than 5 days of work in last 30 days

What command do we use to rename a database?

Give me the Global variable names for the description given below

What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?

What is default? Is there a column to which a default can’t be bound?

Write a query to find the duplicate rows from a table?

What is the advantage of stored procedure?

What is the difference between a sub-query and a correlated sub-query?

Sybase problems 3

Sybase problems 2

Sybase problems

Set III

Set II

Questions and Answers Set I

Q 122

Q 121

I) 1. State whether the following is true or false

1) The object names of the SQL server can be upto 50 characters in length and are case-sensitive

2) The name of the tables can be same in the same database

3) The default property for the identity column is null

4) Identity columns must use the numeric datatype and have a scale of 0

Ans: F-30, T, F, T

2. Answer the following

1) A numeric column allows to store upto _38__ decimal places

2) The scale of the numeric datatype for identity column is_0___

3) Views can have __16__levels of nesting

4) Maximum number of parameters the stored procedure can accept is _255__

(set identity_insert table_name on)

3. Explain the views with check option

4. Give the function name for the description given

1) Largest integer less than or equal to the specified value-floor(num_expr)

2) Current active roles for user-show_role()

3) Returns a specified part of date_expr value as a string- datename(datepart,dateexpr)

4) Replace expr1, if null with expr2- isnull(expr1, expr2)

5) Generates string of int_expr spaces- space(int_exp)

6) Returns length of expr in bytes-datalength(expr)

5. What are the different kinds of locks in sybase?

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

Sybase Interview Questions and Answers

Sybase Questions set

Sybase Query Optimization

3) Views can have 16levels of nesting