1. How can you tell if the query will be
scalable in production?
Ans:-
Profiler, Query Analyzer, Query Execution Plan.
2. Best
Method to insert large data into database:-
Ans:- Insert
with Select=> This works with data in same database.
If
data is in different database then Bulk Copy and Bulk Insert is used.
3. Where
do you write Business Logic in application or DB? Will you use Dynamic SQL or
Stored Procedure?
Ans:- Dynamic
SQl is trade-off. Its expensive.
Stored
Porcedure is
optimized by DB therefore faster,it is secured and also provides injection.
Disadvantage
of Stored Procedure:- Debugging is expensive and large.
4. Why
do we need transaction?
Ans:- To
insure atomicity. ACID
properties….
5. Difference
between stored procedure and functions
Ans:-
- Stored procedure is optional using out variable to return whereas functions must return.
- Stored procedure can’t be used with select statement. Whereas functions can be.
- Mutating Table Error is the problem with functions. To overcome this Compound Trigger is used. If a function is used with select statement for a table and if function is updating that table that gives mutating table error.
- Stored procedure can be implemented using execute statement whereas function can’t.
No comments:
Post a Comment