Set II

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?



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?



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.



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?

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.


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?



If I want to round the value 999.99 as 1000 ?



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?



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?

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


CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after

non clustered index

executing the above statement?

No comments: