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?
No comments:
Post a Comment