SYBASE IMPORTANT QUESTIONS
Sybase General Interview Questions
Q1: How do you load SQLSERVER in single user mode ?
A1: By giving the command “load sqlsrvr -m” at the server console.
Q2: How do you drop a corrupted database ?
A2: Use the command “dbcc dbrepair(database_name,dropdb)”.
Q3: What are the various database options ?
A3: the various options available in Sybase System are:
1) select into/bulkcopy.
2) ddl in tran.
3) allow nulls by default.
4) read only.
5) single user.
6) dbo use only.
7) abort tran on log full.
8) truncate log on checkpoint.
9) no checkpoint on recovery.
10) auto identity.
11) no freespace accounting.
Q4: Whenever the space in a particular segment becomes full, a message must be displayed informing
A4: Create a threshold on a segment and write appropriate code in the procedure of that threshold.
Q5: What does a checkpoint do?
A5: A checkpoint does the following :
1) Commits all the transactions ie Writes all the committed transactions
physically on the disk.
2) Makes an entry in the syslogs so that the recovery becomes easier.
Q6: Can we create views on temporary tables?
A6: No. We cannot create views on temporary tables.
Q7: What are the differences between Batch and Procedure?
A7: “batch” is one or more transact sql statement terminated by end-of-batch signals.
“Procedures” are collection of sql statements and control of flow language Procedures: faster performance, reduced network traffic, better control for sensitive updates, and modular programming.
uses :- call other procedures;
execute remote sql server;
ability to write the power, efficiency and flexibility of sql
Q8: How do you lock a database?
A8: By setting the database option “dbo use only”.
Q9: Can you do bulk copy on temp tables?
A9: No. Bulk copy cannot be performed on temporary tables.
Q10: What is the default group offered by sybase?
A10: “public” is the default group.
Q11: To how many groups can a user belong to?
A11: One only.
Q12: How do you detect a deadlock?
A12: Sql server displays a message when a deadlock occurs. The message number is 1204. One victim is selected and his process is rolled back. This user must submit the process again.
Q13: How do you execute a batch of commands in one statement ?
A13: By creating a procedure which embeds all the statements,
and calling the procedure.
Q14: What will you do to allow nulls in a table without specifying the same in a create table statement?
A14: set the db_option “allow nulls by default”, true.
Q15: Can you dump a database/transaction to an operating system file ?
A15: Yes. Dump database/transaction database_name to “physical_path”
Q16: What are the different roles available in sybase ?
A16: There are six roles in sybase. They are:
1) sa role.
2) sso role.
3) oper role.
4) sybase_ts role.
5) replication role.
6) navigation role.
Q17: How is sa_role different from sso_role?
A17: A person having sso_role can do the following:
1) create/delete logins.
2) change/set the passwords of the users.
3) manage remote logins.
4) grant sso_role to other users.
A person having the sa_role in sybase is considered as the super user of the system. He can do everything except the ones listed above.
Q18: What is the difference between a primary key and a unique key ?
A18: A column defined as a primary key does not allow null values. But a column defined as a unique key allows one null value. Primary key by default creates a clustered index whereas a unique key creates a non clustered index.
Q19: What are dirty reads?
A19: A ‘dirty read’ occurs when one transaction modifies a row and then 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 is called dirty reads.
Q20: How do I insert duplicate rows in a unique index?
A20: You can insert duplicate rows in a unique index by specifying the option “allow duplicate rows ” in the create index statement.
Q21: Which index you prefer for a table that has lot of updations and insertions?
A21: A non-clustered index. Because the clustered index rewrites the whole data in the table based on updations and insertions.
Q22: What are segments? What are the uses of segments?
A segment is a named sub-set of a database device. It is basically used in fine tuning or optimizing the performance of the server. Placing a table on one segment and its non-clustered index on another segment causes the reads and writes to be faster. Similarly placing a database on one segment and its log on separate segment ensures that there is no disk contention during physical reads/writes and logging.
Q23: What are procedures and what are the uses of procedures?
A23: They are system procedures and stored procedures. Uses of procedures are
# Take parameters
# Call other procedures
# Return a status value to a calling procedure or batch
to indicate success or failure, and the reason for failure.
# Return values of parameters to a calling procedure or batch
# be executed on remote sql Servers
Q24: What is the difference between executing a set of statements in a batch and executing the statements in a procedure?
A24: batch takes more time than procedure, because it writes in syslogs.
Q25: What will you do if one out of five users complaints that his system is working slow?
A25: BATCH file PROCESS GOING ON
Q26: A transaction T1 is defined in a procedure X. X calls another procedure Y.
Can one issue “rollback T1″ statement, in procedure Y?
A26: Yes.
Q27: What is the difference between truncate and delete?
A27: Truncate:- truncate table deletes the values and it not recover, make no entry in syslogs and faster than delete
Delete:- delete table it deletes all and it is recoverable, useing rollback statement make entry in syslogs and slow also
Q28: Can one use DDL commands in a transaction ?
A28: Yes, by setting the database option “ddl in tran”,true.
Q29: What are the restrictions on updating a table through views ?
A29: An update operation to any column in the view, is not allowed
Q30: What do you do after issuing the sp_configure command ?
A30: Issue Reconfigure with override.
Q31: What is Intent lock?
A31: intent locks indicate the intention to acquire a shared or exclusive lock on a data page. Intent locks are used to prevent other transactions from acquiring shared or exclusive locks on the given page it is a table lock.
Q32: How do I call a remote procedure?
A32:Execute remote_server_namecall.database_name.object_owner_name.procedure_name
Q33: Why do you dump database after issuing dump tran with no_log statement?
A33: Dump tran database_namewith no_log clears the log without dumping it. Therefore
complete recovery becomes impossible if the database fails or gets corrupted.
To have a copy of all changes made, we should dump database.
Q34: What databases are created during installation?
A34: Four databases are created during installation. They are
a) master b) model c) tempdb d) sybsystemprocs.
options is pubs2, sybsyntax databases.
Q35: How to display the current users role?
A35: By executing sp_displaylogins user_name or select show_roles().
sp_displaylogin (login_name)
select show_roles(user_name) by default current user_name
36. When a record is deleted, what happens to the remaining records on that page?
A36. No physical movement of data occurs at the time of record is deleted. The record is tagged for future physical deletion.
37. When does sybase reduce the amount of space allocated to an object when a large number of records have been deleted?
A37. When no more records reside on an extent, the extent is returned to the pool for use by other objects. There are several ways to accomplish this. One way is to drop the clustered index and re-create it. Another way is to bcp out, truncate table (deallocate extents) and then bcp in the data(allocating only enough extents to hold the data)
38. Explain what happens when clustered index is created?
* Physically sort the data
* Sufficient amount of space (approximately 1.2 times to actual data)
is required for the sorting process.
39. What happens when non-clustered index was created?
A leaf level is created by copying the specified index columns. The leaf level is sorted and uses pointers to the associated data pages.
40. When you install Sybase SQL Server what other server needs to be installed?
The Backup Server
41. Why would we define a fill factor when creating an index?
42. How do we increase the size of database?
A42. Alter database
43. What utility does sybase use to import large volumes of data?
A43. BCP
44. When fast bcp is used to load data, what effect does it have on the transaction log?
A44. When fast BCP is used syb does not log any transactions. instead logs the pages that are written in case of failure.
45. What is stored in syslogs?
A45. the transaction log
46. Would frequent transaction log dumps be used for an application classified as decision support or on line transaction processing?
A46. On line transaction processing
47. What happens when we try to create unique index on a column that contains duplicate values?
48. Does Syb allows null values in a column with unique index?
Yes. one null value, use not null constraint while creating table
49. when creating a nonunique clustered index, why would we use the
‘ignore_dup_rows’ option?
A49. We can complete update the process purpose.
50. What does the ‘update statistics’ do?
A50. It updates the all transactions, page allocations.
51. Describe some scenarios that would cause the transaction log to fill up.
* transaction log not dumped often enough
* when a single insert, update or delete affects large data
* when a transaction remains open for long time
52. What are the DBCC commands ?
A52. dbcc checktable table_name { checks a specific table’s consistency}
dbcc checkdb db_name { checks all tables for a database}
dbcc checkcatalog db_name { checks system tables}
dbcc checkalloc db_name { checks page allocations}
dbcc tablealloc table_name { checks table allocation pointers}
dbcc indexalloc db_name { checks index page pointers}
dbcc fix_alloc db_name { fixes allocation pages reported by checkalloc}
dbcc dbrepair (database_name, dropdb) {drop a corrupt database}
53. what is the command “dbcc dbrepair” used for?
A53. If the database corrupted, then we can repair through dbcc dbrepair.
54. why should we separate transaction log and database on to separate physical devices?
A54.Improve Performance Both Read And Write Large Table Read At A Time Text And Image Data Improve Performance When The Tableis Heavily Used. Manage The Size Of Objects Within The Database Retrevals Fast.
55. when would you use ‘dump tran inward with no_log’?
A55. If the transactions log is full.
56. If an old transaction remains open and is causing the log to fill up, what should you do?
A56. kill that opened process
57. what is the role of sysusages table in MASTER DATABASE?
57A: The creation of a new database is recorded in the master database tables sysdatabases and sysusages.
58. what is the system procedure created by the user that monitors the space usage on the segments and dumps the log when the last-chance thresold is reached?
58A. sp_thresholdaction
59. what is the last-chance threshold?
60. what is the recovery interval? is an estimate of the time required by sql server to recover in case of system failure. *go detail in manual
61. what does ‘truncate log on checkpoint’ do?
62. what happens (internally) when you try to insert a row into a table with clustered index and the data page is full? page split occurs
63. how are rows added to a table that does not have a clustered index? added to the bottom of the table.
64. how do we recover the master database after database becomes corrupt?
* Replace the generic master database “- buildmaster -m”
* Start the SQL Server in single user mode “- startserver -m”
* load the most recent dump of master
* Restart the SQL Server in single user mode
* Check sysusages, sysdevices and sysdatabases against a recent backup copy.
* run dbcc checkalloc and dbcc checkdb on all databases
* dump the master database.
65. How to change configure the values?
65A:sp_configure
66. If syslogs of a database was full what are the steps taken?
66A: (a) dump the particular database
dump database_name with no_log
dump database_name with truncate_only
(b) alter database
67. What are the difference between clustered and non_clustered indexes?
67A: Clustered Indexes dictate the physical order of data. The leaf level of the clustered index is the data. A Non_Clustered index has a row in the leaf level of the index for every row in the table.
68. What does update statement do?
69. What are the constraints in sybase?
70. What does dump database does and dump tran does?
71. Will a file containing rows that have negative values for column b be added during a bulk-copy?
71A: Yes, rules, triggers and constraints are not recognized during bulk-copy operation.
72. What command you use to change the default value in column b to 5?
72A: Alter table table_name replace b default 5
73. what system table contains objects, rules as tables, defaults and triggers within a database?
73A: Sysobjects.
74. How many pages are allocated when a table is created?
74A: An extent. which is 8 pages.
75. What is difference between varchar and char?
75A: char is a fixed length data type with training spaces.
varchar is a variable length data type.
76. How many no of triggers can be created on a table?
76A: 3 i.e. insert,update,delete
77. what is normalization? difference between normalization and denormalization?
77A: Normalization produces smaller tables with smaller rows.
More rows per page(less logical I/O)
More rows per I/O(more efficient)
More rows fit in cash (less physical I/o)
Searching, sorting and creating indexes are faster, since tables are narrower, and more rows fit on a data page.
You usually wind up with more tables. You can have more clustered indexes(you get only one per table) so you get more flexibility in tuning queries.
Index searching is often faster, since indexes tend to be narrower and shorter.
More tables allow better use of segments to control physical placement of data.
You usually wind up with fewer indexes per table, so data modification commands are faster.
You wind up with fewer null values and less redundant data, making your database more compact.
Triggers execute more quickly if you are not maintaining redundant data.
Data modification anomalies are reduced.
Normalization is conceptually cleaner and easier to maintain and change as you needs change.
While fully normalized databases require more joins, joins are generally very fast if indexes are available on the join columns. SQL server is optimized to keep higher levels of the index in cache, so each join performs only one or two physical I/Os for each matching row. The cost of finding rows already in the data cache is extremely low.
First Normal Form, Second Normal Form, Third Normal Form.
78. What types of relationships in sybase10?
78A: Relations become tables, Attributes become columns, Relationships become data references (primary and foreign key references).
79. What is data integrity types?
79A: entity and referential
80. How to update row by row from the database?
80A: THROUGH CURSORS
81. What are the system tables in sybase10?
81A: sybsystemprocs
82. What is difference between sybase 4.2 and sybase10?
82A: cursors, sybsystemprocs,
83. How many types of locks? explain?
83A: Holdlock, noholdlock, or shared
Page locks: Shared Locks, Exclusive Locks, Update Locks
Table locks: Intent lock, shared lock, exclusive lock
Demand locks: Sql Server sets a demand lock to indicate that a transaction is next in
line to lock a table or page.
85. How to create data type?
85A:sp_addtype
86. What type of locking will sybase follow: Row level, Column Level?
86A: Row level
87. What is the diff between Implicit and Non-Implicit cursors?.
87A: Implicit cursors are system created cursors; where as Non-implicit cursors [explicit cursors] are user created cursors.
88. What are sub-related queries, Diff type of Triggers (Automated Triggers)?.
89. What is NLM meant for – Novell Loadable Module?.
90. What are diff type of backups in Unix environment?.
91. What are diff between MS-SQL server and Sybase Server?.
92. How to execute a set of procedures at one time
92A: By calling the other procedure by EXEC command in the main procedure
92 (b) How to Audit System or Explain sso task only?
92 (b): sp_auditoption
sp_auditdatabase dbname
sp_auditobject table_name
sp_auditsproc proc_name
sp_auditlogin login_name ” “, on | off
sp_auditrecord
sp_configure “audit queue size” , #_audit_records
93) What is Sybase ASE Prefetch?
Ans. When you switch on the statistics io on then you can see APF(all page prefetch).
prefetch improves performance by anticipating the pages required for certain well-defined classes of database activities whose access patterns are predictable. The I/O requests for these pages are issued before the query needs them so that most pages are in cache by the time query processing needs to access the page. Asynchronous prefetch can improve performance for:
Sequential scans, such as table scans, clustered index scans, and covered nonclustered index scans
Access via nonclustered indexes
Some dbcc checks and update statistics
For example--
1> set statistics io on
2> go
Total writes for this command: 0
1> select top 1 * from sysobjects
2> go
name id uid type userstat sysstat indexdel schemacnt sysstat2 crdate expdate deltrig instrig updtrig seltrig ckfirst cache audflags objspare versionts loginame identburnmax spacestate erlchgts
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- sysobjects 1 1 S 0 97 1 0 229376 Nov 26 2014 6:43PM Nov 26 2014 6:43PM 0 0 0 0 0 0 0 0 NULL NULL NULL 1 0x0000000000000000
Table: sysobjects scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=1 apf=4 total=5), apf IOs used=0
Total writes for this command: 0
(1 row affected)
94)How will you take database backup when there is no space in file system?
ANS-- a)you can try using remote backup so you can dump to another server with available space. of course you have to configure the interfaces between the servers to allow this.
95)How to avoid Data changes while retrieving data?
Ans--a)you can use holdlock at the end of the query. holdlock will enforce isolation level 3
e.g.
1> select * from test holdlock
2> go
name age
---- -----------
Mich 4
b) Put the retrieval of data in Tranaction.. Isolation level should be 2.
96) What is the maximum nesting level of Stored Procedure and Triggers in Sybase?
Ans-- Maximum nesting level of SP and triggers both are 16.
You can get the current nesting level with help of @@nestlevel global var.
select @@nestlevel
97) What is the use of 'with check' option in views?
ANS--When you create a view using the with check option clause, each insert and update through the view, is validated against the view’s selection criteria. If the rows inserted or updated are out of criteria then it fails and gives error message.
For ex, you have a view named sybase_books
create view sybase_books as select * from books where type=sybase with check
If you try to insert rows through view where type="oracle" then it will fail, for ex
insert into sybase_books values ('Oracle theory','oracle')
98) How to clear the logsegment when the Secondary truncation point is on ?
ANS--
Adaptive Server uses truncation points to ensure that only transactions processed by the RepAgent are truncated. A secondary truncation point marks the place in the primary database log up to which the RepAgent has processed transactions. The RepAgent periodically updates the secondary truncation point to reflect transactions successfully passed to the Replication Server. Adaptive Server does not truncate the log past the secondary truncation point.
At times you may need to truncate the log beyond the secondary truncation point. For example, if the RepAgent cannot access the Replication Server and the log fills, you may want to truncate the log at the secondary truncation point rather than extending the log or preventing clients from updating the primary database. Truncating the log can cause inconsistencies between the primary and replicate databases.
In Summary
Adaptive Server uses truncation points to ensure that only transactions processed by the RepAgent are truncated. A secondary truncation point marks the place in the primary database log up to which the RepAgent has processed transactions. The RepAgent periodically updates the secondary truncation point to reflect transactions successfully passed to the Replication Server. Adaptive Server does not truncate the log past the secondary truncation point.
At times you may need to truncate the log beyond the secondary truncation point. For example, if the RepAgent cannot access the Replication Server and the log fills, you may want to truncate the log at the secondary truncation point rather than extending the log or preventing clients from updating the primary database. Truncating the log can cause inconsistencies between the primary and replicate databases.
- Verify that the secondary truncation point is set for the database.
- Turn off the secondary truncation point in the database.
- Dump the database log.
- Set the secondary truncation point.
1)Verifying the State of the Secondary Truncation Point
Check whether the secondary truncation point is set for
a database.
2)Turning Off the Secondary Truncation Point in a Database
Disable the
secondary truncation point for a database to truncate portions
of the log that have not been transferred. Truncating the log frees only the log pages that the RepAgent has
sent to the Replication Server with confirmation.
3)Setting the Secondary Truncation Point
Reestablish the
secondary truncation before restarting a RepAgent.
The RepAgent begins
scanning the log from the page returned in the ltm_truncpage column
by the dbcc gettrunc command.
In Summary
1. dbcc settrunc(ltm,ignore).
2. sp_stop_rep_agent <dbname> or sp_config_rep_agent 'disable'.
To enable when you are ready. P.S. The order is very important.
If you have used sp_stop_rep_agent
1. dump tran with truncate_only.
2. use db
dbcc settrunc(ltm,valid)
3. On the RSSD.
rs_zeroltm DBSERVER,DBNAME
This step will tell the RepServer to look at the secondary marker from the start of the transaction log.
4. sp_start_rep_agent <dbname>
If you have used sp_config_rep_agent 'disable'
1. dump tran with truncate_only.
2. sp_config_rep_agent 'enable'..... This will also set the valid trunc marker.
3. On the RSSD.
rs_zeroltm DBSERVER,DBNAME
This step will tell the RepServer to look at the secondary marker from the start of the transaction log.
4. sp_start_rep_agent <dbname>
99) How to get the list of tables in Sybase?
Ans-- select * from sysobjects where type="U"
100)Given a table which contains some rows with duplicate keys, how would you remove the duplicates?
Ans-- There are multiple ways that we can do this.
==================1 Way==============
select distinct * into temp_1 from Original ---so no constraints are dropped
truncate table Original
insert into Original
select * from temp_1
drop table temp_1
truncate table Original
insert into Original
select * from temp_1
drop table temp_1
==================2nd way==================
scenario
1> select * from test1
2> go
name age
---- -----------
Mich 4
Mich 4
Lal 5
Lal 5
Lal 5
(5 rows affected)
set rowcount 1
declare @rwcnt int,
@unid varchar(25)
select @rwcnt=2
while (@rwcnt>1)
begin
select @rwcnt=count(*),@unid=name from test group by name having count(*)>1
select @rwcnt=@rwcnt - 1
set rowcount @rwcnt
delete from test where name=@unid
select @rwcnt=@@rowcount
end
====================================3rd way=====
create temporary table with same table structure and then create clustered index on this temporary table with option ignore_dup_row.
now insert data into temp table from the table in which duplicate records exists.
while inserting record into temp table, duplicate rows will get ingored.
Finally temp table will have unique records
select * into test2 from test1 where 1=2
2> go
(0 rows affected)
1> create clustered index idx on test2(name) with ignore_dup_row
2> go
1> insert into test2 select * from test1
2> go
Duplicate row was ignored.
(2 rows affected)
1> select * from test2
2> go
name age
---- -----------
Lal 5
Mich 4
(2 rows affected)
