The largest Interview Solution Library on the web

Interview Questions
« Previous | 0 | 1 | 2 | 3 | 4 | Next »

61.How will I create a primary key constraint with non-clustered index ?(default is clustered)

alter table tt add constraint pk primary key nonclustered (c1)

62.What is the meaning of User-defined datatypes?

User-defined datatype is not really a new datatype, but a way of describing an existing datatype. It can enforce datatype consistency in the databases and can simplify management of frequently used rules and defaults.
You can bind rules and defaults directly to user-defined datatypes. Any column created with such datatypes automatically inherits the rule and default but a rule or default bound explicitly to a column overrides a rule or default bound to a datatype.
User-defined datatype is not a database object, so not listed in sysobjects. It is listed in systypes.

63.Why is save tran used for?

To save the transaction if rollback occurred. Commit the transaction upto save point.

64.What is a transaction? What are diff modes of transaction?

A transactionis a set of Transact-SQL statements so that they are treated as a unit. Either all statements in the group are executed or no statements are executed. Adaptive Server automatically manages all data modification commands, including single-step change requests, as transactions. By default, each insert, update, and delete statement is considered a single transaction.

Adaptive Server supports the following transaction modes:

  • The SQL standards-compatible mode, called chainedmode, implicitly begins a transaction before any data retrieval or modification statement. These statements include: delete, insert, open, fetch, select, and update. You must still explicitly end the transaction with commit transaction or rollback transaction.
  • The default mode, called unchainedmode or Transact-SQL mode, requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction. You can set either mode using the chained option of the set command. However, do not mix these transaction modes in your applications. The behavior of stored procedures and triggers can vary, depending on the mode, and you may require special action to run a procedure in one mode that was created in the other. The SQL standards require every SQL data-retrieval and data-modification statement to occur inside a transaction, using chained mode. A transaction automatically starts with the first data-retrieval or data-modification statement after the start of a session or after the previous transaction commits or aborts. This is the chained transaction mode. You can set this mode for your current session by turning on the chained option of the set statement: set chained on
However, you cannot execute the set chained command within a transaction. To return to the unchained transaction mode, set the chained option to off. The default transaction mode is unchained. In chained transaction mode, Adaptive Server implicitly executes a begin transaction statement just before the following data retrieval or modification statements: delete, insert, open, fetch, select, and update. For example, the following group of statements produce different results, depending on which mode you use:
In unchained transaction mode, the rollback affects only the delete statement, so publishers still contains the inserted row. In chained mode, the insert statement implicitly begins a transaction, and the rollback affects all statements up to the beginning of that transaction, including the insert. All application programs and ad hoc user queries should know their current transaction mode. Which transaction mode you use depends on whether or not a particular query or application requires compliance to the SQL standards. Applications that use chained transactions (for example, the Embedded SQL precompiler) should set chained mode at the beginning of each session.

65.When will Sp_recompile not work?

If object related to the table exists in different database then sp_recompile won’t work. Sp_recompile will also not work for system tables
In the following cases sp_recompile will not work: –
A stored procedure contains select * statement. Alter table to add new columns. The procedure will not pick up new columns even if executed by using the with recompile option. This is because the old columns list is stored in parse tree. To pick up the new columns, a new parse tree should be created. The only way to do this is to drop and re-create the procedure.
A procedure refers customer table. Rename customer table as old_customer. Create new customer table with diff values. Procedure will continue to use old_customer table though re-compiled with sp_recompile option or with recompile option is used. This is because stored procedure resolves object references by object ID rather than name. To refer the new customer table, you have to either drop old_customer table (now the stored procedure will be compiled and a new parse tree is generated to refer new table) or drop and recreate the procedure (if you want to keep old_customer table)

66.What are functions in Sybase? Can I create my own functions?

The functions return special information from the database.

67.I want to add a column in a table but it should be not null? Is it possible?

create table tt
c1 int not null,
c2 int not null

insert tt values(2,2)

alter table tt
add c3 int default 0 not null

select * from tt

68.How will you give access to a particular table only through a stored procedure?

Revoke all rights on of the table from all users and Grant rights for the procedure to the users. Hence now the table will be accessed by using procedure only

69.I have created a raiseerror message. I want to show that message in diff procedures or location. How will I do it?

Using sp_getmessage and then print statement you can get the user defined error message.
Sp_getmessage will get the userdefined message in an output variable.
Print will print the message

Eg :-
sp_addmessage 25123 , “Message displaying one : ‘%1!’ two : ‘%2!’ “
declare @message varchar(200)
exec sp_getmessage 25123 , @message output
print @message ,’one’,’two’
The resultant will be

70.73 Why do we use group by?

If we want to have an vector aggregate value we use group by clause
( Vector aggregate :- Where we are getting the aggregated value in relation with some other field.
Eg. Select name_fs , sum(amount) from TableA group by name_fs
Scalar aggregate : This aggregate is independent value and only one value will be returned.
Eg. Select sum(amount) from TableA )

71.74 Can I have two different triggers on the same table for insert operations? One insert trigger inserts records into history table. One insert trigger updates records in other table. Is it possible? In that case which trigger gets executed?

You can define as many triggers as u want. But only the last defined trigger remain active for the table. If u drop that trigger, the previous latest should fire.

72.75 Is there anyway with which we can display the output of a single record in two lines. (ex.)

Emp IDEmp NameDesignation
—————- —————-—————-
E1 SolomonSSE
E2 SantoshSSE
Output what I want is:
Employee Name := Solomon
Employee Desig := SSE

Employee Name := Santosh
Employee Desig := SSE

So the Name in first line followed by Designation in next line and finally the “—-” characters in separate line.
Can this be done using a Select Statement and some special characters? NO CURSOR.

select ‘Employee Name = ‘+emp_name +char(13)+ ‘Employee Desig = ‘+emp_desg
from tmp1

73.76 Can I have a Select statement which has an aggregate function but does not have a “Group By” clause?

For ex:
Select type,count(*)
From titles

Select type,count(*)
From titles
Having count(*) > 1
Can such a statements work and give output.

It does not work in SQL as it does not accept aggregate functions without Group By clause. In Sybase, both will give same result but the result will be wrong.

74.78 Can I have an Update cursor without a unique record in the select statement?

No. It will return an error saying Optimizer could not find a unique index.

75.80 Can I get the showplan of a procedure without breaking it or without executing the procedure?

set showplan on
set fmtonly on
set exec off

76.81 I have got a 50,000 records table in tempdb and want to remove 10,000 records. Ii have got the where condition but when I try to delete it will full the tempdb log. Is there any way of deleting these records without making the log full?

Use set rowcount function. This will limit your number of rows.





« Previous | 0 | 1 | 2 | 3 | 4 | Next »

copyright © 2014 - all rights riserved by