Sybase Insights

1)If I want current database name?

select db_name()

2) how to check what is size of table and corresponding index size and total no. of rows in tables?

sp_spaceused <tablename>

e.g.

1> sp_spaceused item_detail
2> go
name rowtotal reserved data index_size unused
——————– ———– ————— ————— ————— —————
item_detail 1000 78 KB 68 KB 0 KB 10 KB

(1 row affected)
(return status = 0)

we can check total no of rows by count(*) also but sp_spaceused is always faster.

1> select count(*) from item_detail
2> go

———–
1000

3)what is the difference between below statement

1)select * into employee from temp_employee
2)insert into employee select * from temp_employee

1st statement will create new table employee and first is example of fast BCP whereas in the second statement employee table is already there.

4)how many kind of triggers can be there on a table?

three kind- Insert ,Update ,Delete

5)What are magic tables.

Magic tables are intermediates tables which are created during execution of triggers

6)how to check version of sybase?

select @@version

eg.

1> select @@version
2> go

—————————————————————————————————————————————————————————————————————————————————————
Adaptive Server Enterprise/12.5.2/EBF 11793/P/RS6000/AIX 5.1/ase1252/1831/64-bit/FBO/Fri Apr 9 06:55:58 2004

7)How many index can have for a table?

250
1(clustered)+249(Non clustered)

8)How do I get the Duplicate rows from a table?

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

9)How to get second highest value or third highest value from a table

1> select * from salary
2> go
sno
———– —– —–
1
2
3
4

(4 rows affected)

1> select max(sno) from salary where sno<(select max(sno) from salary where sno<(select max(sno) from salary))

2> go

———–
2

(1 row affected)

10)what is difference between Primary key and Unique key?

Primary Key                                                                          Unique Key

1)It don’t allow null values                                                               1)It allows one null value

2)on a table only one column can be primary key                2) On a table there can be multiple unique key

3)By default it creates clustered index                                       3)By Default it creates non-clustered index

11) How to do batch updates?

Batch update is required when there are huge no of rows to update like 1 Million or more than that. Otherwise it will fill transaction log.

while ((select count(*) from <referenced_table>)>0)
begin
set rowcount 10000
update target_table set <update statement>
from target_table t,referenced_table r
where t.column_name=r.column_name
delete from referenced_table
end

to follow above procedure just copy original referenced into temporary referenced table so that there will be no data loss.

12) Mention some system table name

sysobjects

syscolumns

syscomments

13) Mention some system stored procedure

sp_addmessage

Adds user-defined messages to sysusermessages for use by stored procedure print and raiserror calls and by sp_bindmsg.

sp_addmessage message_num, message_text 
 [, language [, with_log [, replace]]]

sp_bindefault

Binds a user-defined default to a column or user-defined datatype.

sp_bindefault defname, objname [, futureonly]

sp_who