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. 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.
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 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.
Eg :-
sp_addmessage 25123 , “Message displaying one : ‘%1!’ two : ‘%2!’ “ go declare @message varchar(200) exec sp_getmessage 25123 , @message output print @message ,’one’,’two’ go 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 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.)
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:
Can such a statements work and give output.Select type,count(*) From titles Select type,count(*) From titles Having count(*) > 1 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 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. 77.?
78.?
79.?
80.?
|