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
2
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
3
can I update view?
yes,but view had created using only one table than update is possiable
4
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
5
how will you bind a rule?
sp_bindrule
6
explain about @@sqlstatus?
It returns the status of the pervious fetch statement in a cursor
7
What are the steps involved in creating cursor?
Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors
8
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.
9
compare Rollback transaction and Rollback Trigger?
10
How will bcp out the Identity column?
-E
11
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.
12
How to get the last month’s last Day?
select datename(dw,dateadd(dd,30-datepart(dd,getdate()),dateadd(mm,-1,getdate())))
13
What is patindex()?
returns the starting postion of the specified ,else 0
14
How do I get the Duplicate rows from a table?
select * from table name group by column1 having count(*)>1
15
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)
16
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.
17
What is sp_dboption?
It is used to enable or disable db option for your database
18
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
19
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.
20
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
21
How did you know the index was the cause of the performance degration?
22
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
23
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.
24
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.
25
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.
26
Compare Union and
union remove the duplicates and go for worktable creation where as union all allow duplicates and don't go for WT
27
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.
28
What is Update Statistics?
29
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.
30
Advantages and disadvantages of cursors?
31
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
32
What is “COMPUTE BY” clause in Sybase?
When ever we use compute by ,we must also include order by
33
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
34
Compare 2 nd and 3 rd
Second normal form-nonkey fields must depend on the entire pr. Key.
Third normal form---nonkey fields must not depend on other nonkey fields.
35
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
36
Is there any way to create Primary or Foreignkey on a table other than Query?
sp_primarykey,sp_foreginkey
37
If I want to round the value 999.99 as 1000 ?
round(999.99,0)
38
What is Distribution page?
server keeps distribution information for each index on a separate page in the datebase
39
Compare Composite Index and Multiple Index
40
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.
41
Explain Database and Performance?
42
How will you create Login name and User in Sybase?
sp_addlogin,sp_adduser
43
Is it necessary to drop & recreate all procedures and triggers every few months ?
44
What is difference between Count() and Count(*)?
count(*) will inclued null rows and faster
45
Advantage of Stored Procedure?
faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency
46
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
47
what are triggers? advantage ? disadvantage?
48
How do u copy the output results into a file?
isql -
49
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
50
CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after
non clustered index
executing the above statement?
No comments:
Post a Comment