Oracle interview questions

By karthick , in , 0 Comments




Oracle interview questions


Explain the difference between trigger and stored procedure.

Trigger in act which is performed automatically before or after a event occur
Stored procedure is a set of functionality which is executed when it is explicitly invoked.


Explain Row level and statement level trigger.

Row-level: - They get fired once for each row in a table affected by the statements.
Statement: - They get fired once for each triggering statement.


Advantage of a stored procedure over a database trigger

Firing of a stored procedure can be controlled whereas on the other hand trigger will get fired whenever any modification takes place on the table.


What are cascading triggers?

A Trigger that contains statements which cause invoking of other Triggers are known as cascading triggers. Here’s the order of execution of statements in case of cascading triggers:
·        Execute all BEFORE statement triggers that apply to the current statement.
·        Loop for each row affected statement.
·        Execute all BEFORE row triggers that apply to the current statement in the loop.
·        Lock and change row, perform integrity constraints check; release lock.
·        Execute all AFTER row triggers that apply to the current statement.
·        Execute all AFTER statement triggers that apply to the current statement.


What is a JOIN? Explain types of JOIN in oracle.

A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is to be queries i.e. common data between 2 tables, unique data, total data, or mutually exclusive data.
Types of JOINS:
JOIN Type
Example
Description
Simple JOIN
SELECT p.last_name, t.deptName
FROM person p, dept t
WHERE p.id = t.id;
Find name and department name of students who have been allotted a department
Inner/Equi/Natural JOIN
SELECT * from Emp INNER JOIN Dept WHERE Emp.empid=Dept.empid
Extracts data that meets the JOIN conditions only. A JOIN is by default INNER unless OUTER keyword is specified for an OUTER JOIN.
Outer Join
SELECT distinct * from Emp LEFT OUTER JOIN Dept Where Emp.empid=Dept.empid
It includes non matching rows also unlike Inner Join.
Self JOIN
SELECT a.name,b.name from emp a, emp b WHERE a.id=b.rollNumber
Joining a Table to itself.



What is object data type in oracle?

New/User defined objects can be created from any database built in types or by their combinations. It makes it easier to work with complex data like images, media (audio/video). An object types is just an abstraction of the real world entities. An object has:
·        Name
·        Attributes
·        Methods
Example:
Create type MyName as object (first varchar2(20), second varchar2(20));

Now you can use this datatype while defining a table below: 

Create table Emp (empno number(5),Name MyName);
One can access the Atributes as Emp.Name.First and Emp.Name.Second



What is composite data type?

Composite data types are also known as Collections .i.e RECORD, TABLE, NESTED TABLE, VARRAY.
Composite data types are of 2 types:
PL/SQL RECORDS
PL/SQL Collections- Table, Varray, Nested Table


Differences between CHAR and NCHAR in Oracle

NCHAR allow storing of Unicode data in the database. One can store Unicode characters regardless of the setting of the database characterset


Differences between CHAR and VARCHAR2 in Oracle

CHAR is used to store fixed length character strings where as Varchar2 can store variable length character strings. However, for performance sake Char is quit faster than Varchar2.
If we have char name[10] and store “abcde”, then 5 bytes will be filled with null values, whereas in case of varchar2 name[10] 5 bytes will be used and other 5 bytes will be freed.


Differences between DATE and TIMESTAMP in Oracle

Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. It fails to provide granularity and order of execution when finding difference between 2 instances (events) having a difference of less than a second between them.
TimeStamp datatype stores everything that Date stores and additionally stores fractional seconds.
Date: 16:05:14
Timestamp: 16:05:14:000

Define CLOB and NCLOB datatypes.

CLOB: Character large object. It is 4GB in length.
NCLOB: National Character large object. It is CLOB datatype for multiple character sets , upto 4GB in length.


What is the BFILE datatypes?

It refers to an external binary file and its size is limited by the operating system.


What is Varrays?

Varrays are one-dimensional, arrays. The maximum length is defined in the declaration itself. These can be only used when you know in advance about the maximum number of items to be stored.
For example: One person can have multiple phone numbers. If we are storing this data in the tables, then we can store multiple phone numbers corresponding to single Name. If we know the maximum number of phone numbers, then we can use Varrays, else we use nested tables.


What is a cursor? What are its types?

Cursor is used to access the access the result set present in the memory. This result set contains the records returned on execution of a query.
They are of 2 types:
1.      Explicit
2.      Implicit


Explain the attributes of implicit cursor

  1. %FOUND - True, if the SQL statement has changed any rows.
  2. %NOTFOUND - True, if record was not fetched successfully.
  3. %ROWCOUNT - The number of rows affected by the SQL statement.
  4. %ISOPEN - True, if there is a SQL statement being associated to the cursor or the cursor is open.


Explain the attributes of explicit cursor.

  1. %FOUND - True, if the SQL statement has changed any rows.
  2. %NOTFOUND - True, if record was not fetched successfully.
  3. %ROWCOUNT - The number of rows affected by the SQL statement.
  4. %ISOPEN - True, if there is a SQL statement being associated to the cursor or the cursor is open.


What is the ref cursor in Oracle?

REF_CURSOR allows returning a recordset/cursor from a Stored procedure.
It is of 2 types:
Strong REF_CURSOR: Returning columns with datatype and length need to be known at compile time.
Weak REF_CURSOR: Structured does not need to be known at compile time.
Syntax till Oracle 9i
create or replace package REFCURSOR_PKG as
TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;
Procedure returning the REF_CURSOR:
create or replace procedure test( p_deptno IN number , p_cursor OUT 
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
open p_cursor FOR 
select *
from emp
where deptno = p_deptno;
end test;
Since Oracle 9i we can use SYS_REFCURSOR
create or replace procedure test( p_deptno IN number,p_cursor OUT SYS_REFCURSOR)
is
begin
open p_cursor FOR 
select *
from emp
where deptno = p_deptno;
end test;
For Strong
create or replace procedure test( p_deptno IN number,p_cursor OUT REFCURSOR_PKG.STRONG 
REF_CURSOR)
is
begin
open p_cursor FOR 
select *
from emp
where deptno = p_deptno;
end test;


What are the drawbacks of a cursor?

Cursors allow row by row processing of recordset. For every row, a network roundtrip is made unlike in a Select query where there is just one network roundtrip. Cursors need more I/O and temp storage resources, thus it is slower.


What is a cursor variable?

In case of a cursor, Oracle opens an anonymous work area that stores processing information. This area can be accessed by cursor variable which points to this area. One must define a REF CURSOR type, and then declare cursor variables of that type to do so.
E.g.:
/* Create the cursor type. */
TYPE company_curtype IS REF CURSOR RETURN company%ROWTYPE;
 
/* Declare a cursor variable of that type. */
company_curvar company_curtype;
 

What is implicit cursor in Oracle?

PL/SQL creates an implicit cursor whenever an SQL statement is executed through the code, unless the code employs an explicit cursor. The developer does not explicitly declare the cursor, thus, known as implicit cursor.
E.g.:
In the following UPDATE statement, which gives everyone in the company a 20% raise, PL/SQL creates an implicit cursor to identify the set of rows in the table which would be affected.
UPDATE emp
SET salary = salary * 1.2;


Can you pass a parameter to a cursor? Explain with an explain

Parameterized cursor:
/*Create a table*/
create table Employee(
ID VARCHAR2(4 BYTE)NOT NULL,
First_Name VARCHAR2(10 BYTE)
);
/*Insert some data*/
Insert into Employee (ID, First_Name) values (‘01’,’Harry’);
/*create cursor*/
declare
cursor c_emp(cin_No NUMBER)is select count(*) from employee where id=cin_No;
v_deptNo employee.id%type:=10;
v_countEmp NUMBER;
begin
open c_emp (v_deptNo);
fetch c_emp into v_countEmp;
close c_emp;
end;

/*Using cursor*/
Open c_emp (10);





What is a package cursor?

A Package that returns a Cursor type is a package cursor.
Eg:
Create or replace package pkg_Util is
    cursor c_emp is select * from employee;
    r_emp c_emp%ROWTYPE;
end;
/*Another package using this package*/
Create or replace package body pkg_aDifferentUtil is
    procedure p_printEmps is
    begin
        open pkg_Util.c_emp;
        loop
            fetch pkg_Util.c_emp into pkg_Util.r_emp;
            exit when pkg_Util.c_emp%NOTFOUND;
            DBMS_OUTPUT.put_line(pkg_Util.r_emp.first_Name);
        end loop;
        close pkg_Util.c_emp;
     end;
end;


Explain why cursor variables are easier to use than cursors.

Cursor variables are preferred over a cursor for following reasons:
A cursor variable is not tied to a specific query.
One can open a cursor variable for any query returning the right set of columns. Thus, more flexible than cursors.
A cursor variable can be passed as a parameter.
A cursor variable can refer to different work areas.


What is locking, advantages of locking and types of locking in oracle?

Locking is a mechanism to ensure data integrity while allowing maximum concurrent access to data. It is used to implement concurrency control when multiple users access table to manipulate its data at the same time.

Advantages of locking:
a.      Avoids deadlock conditions
b.      Avoids clashes in capturing the resources
Types of locks:
a.      Read Operations: Select
b.      Write Operations:  Insert, Update and Delete



What are transaction isolation levels supported by Oracle?

Oracle supports 3 transaction isolation levels:
a.      Read committed (default)
b.      Serializable transactions
c.      Read only


What is SQL*Loader?

SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk. It is used for high performance data loads.


What is Program Global Area (PGA)?

The Program Global Area (PGA): stores data and control information for a server process in the memory. The PGA consists of a private SQL area and the session memory.


What is a shared pool?

The shared pool is a key component. The shared pool is like a buffer for SQL statements.  It is to store the SQL statements so that the identical SQL statements do not have to be parsed each time they're executed. 


38. What is snapshot in oracle?

A snapshot is a recent copy of a table from db or in some cases, a subset of rows/cols of a table. They are used to dynamically replicate the data between distributed databases. 

What is a synonym?

A synonym is an alternative name tables, views, sequences and other database objects.


What is a schema?

A schema is a collection of database objects. Schema objects are logical structures created by users to contain data. Schema objects include structures like tables, views, and indexes. 




What are Schema Objects?

Schema object is a logical data storage structure. Oracle stores a schema object logically within a tablespace of the database.



What is a sequence in oracle?

Is a column in a table that allows a faster retrieval of data from the table because this column contains data which uniquely identifies a row. It is the fastest way to fetch data through a select query. This column has constraints to achieve this ability. The constraints are put on this column so that the value corresponding to this column for any row cannot be left blank and also that the value is unique and not duplicated with any other value in that column for any row.


Difference between a hot backup and a cold backup

Cold backup: It is taken when the database is closed and not available to users.  All files of the database are copied (image copy).  The datafiles cannot be changed during the backup as they are locked, so the database remains in sync upon restore. 

Hot backup: While taking the backup, if the database remains open and available to users then this kind of back up is referred to as hot backup.  Image copy is made for all the files.  As, the database is in use the entire time, so there might be changes made when backup is taking place. These changes are available in log files so the database can be kept in sync


What are the purposes of Import and Export utilities?

Export and Import are the utilities provided by oracle in order to write data in a binary format from the db to OS files and to read them back.

These utilities are used:
·        To take backup/dump of data in OS files.
·        Restore the data from the binary files back to the database.
·        move data from one owner to another


Difference between ARCHIVELOG mode and NOARCHIVELOG mode

Archivelog mode is a mode in which backup is taken for all the transactions that takes place so as to recover the database at any point of time.
Noarichvelog   mode is in which the log files are not written. This mode has a disadvantage that the database cannot be recovered when required. It has an advantage over archivelog mode which is increase in performance.







What are the original Export and Import Utilities?

SQL*Loader, External Tables


What are data pump Export and Import Modes?

It is used for fast and bulk data movement within oracle databases. Data Pump utility is faster than the original import & export utilities.


What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE: It returns the error number for the last encountered error.
SQLERRM: It returns the actual error message of the last encountered error. 


Explain user defined exceptions in oracle.

A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as exception. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. RAISE statement can also be used to raise internal exceptions. 

Exception: 

DECLARE
userdefined  EXCEPTION;


BEGIN
<Condition on which exception is to be raised>
RAISE userdefined;


EXCEPTION
WHEN userdefined THEN
<task to perform when exception is raised>
 END;


Explain the concepts of Exception in Oracle. Explain its type.

Exception is the raised when an error occurs while program execution. As soon as the error occurs, the program execution stops and the control are then transferred to exception-handling part.
There are two types of exceptions:
1.      Predefined : These types of exceptions are raised whenever something occurs beyond oracle rules. E.g. Zero_Divide
2.      User defined: The ones that occur based on the condition specified by the user. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. 


How exceptions are raised in oracle?

There are four ways that you or the PL/SQL runtime engine can raise an exception:
·        Exceptions are raised automatically by the program.
·        The programmer raises a user defined exceptions. 
·         The programmer raises pre defined exceptions explicitly.


What is tkprof and how is it used?

tkprof  is used for diagnosing performance issues.  It formats a trace file into a more readable format for performance analysis. It is needed because trace file is a very complicated file to be read as it contains minute details of program execution.


What is Oracle Server Autotrace?

It is a utility that provides instant feedback on successful execution of any statement (select, update, insert, delete).  It is the most basic utility to test the performance issues.




SQL Server interview questions

By karthick , in , 0 Comments



SQL Server interview questions


Explain the use of keyword WITH ENCRYPTION. Create a Store Procedure with Encryption.

It is a way to convert the original text of the stored procedure into encrypted form. The stored procedure gets obfuscated and the output of this is not visible to

CREATE PROCEDURE Abc
WITH ENCRYPTION
AS
<<    SELECT statement>>
GO


What is a linked server in SQL Server?

It enables SQL server to address diverse data sources like OLE DB similarly. It allows Remote server access and has the ability to issue distributed queries, updates, commands and transactions.


Features and concepts of Analysis Services

Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data One can create data mining models from data sources and use it for Business Intelligence also including reporting features.
Some of the key features are:
·        Ease of use with a lot of wizards and designers.
·        Flexible data model creation and management
·        Scalable architecture to handle OLAP
·        Provides integration of administration tools, data sources, security, caching, and reporting etc.
·        Provides extensive support for custom applications


What is Analysis service repository?

Every Analysis server has a repository to store metadata for the objects  like cubes, data sources etc. It’s by default stored in a MS Access database which can be also migrated to a SQL Server database.


What is SQL service broker?

Service Broker allows internal and external processes to send and receive guaranteed, asynchronous messaging. Messages can also be sent to remote servers hosting databases as well. The concept of queues is used by the broker to put a message in a queue and continue with other applications asynchronously. This enables client applications to process messages at their leisure without blocking the broker. Service Broker uses the concepts of message ordering, coordination, multithreading and receiver management to solve some major message queuing problems. It allows for loosely coupled services, for database applications.


What is user defined datatypes and when you should go for them?

User defined data types are based on system data types. They should be used when multiple tables need to store the same type of data in a column and you need to ensure that all these columns are exactly the same including length, and nullability.
Parameters for user defined datatype:
Name
System data type on which user defined data type is based upon.
Nullability.
For example, a user-defined data type called post_code could be created based on char system data type.


What is bit datatype?

A bit datatype is an integer data type which can store either a 0 or 1 or null value.


Describe the XML support SQL server extends.
SQL Server (server-side) supports 3 major elements:
  1. Creation of XML fragments: This is done from the relational data using FOR XML to the select query. 
  2. Ability to shred xml data to be stored in the database.
  3. Finally, storing the xml data.
Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of
  • XML Views:  providing bidirectional mapping between XML schemas and relational tables.
  • Creation of XML Templates:  allows creation of dynamic sections in XML.


What is SQL Server English Query?

English query allows accessing the relational databases through English Query applications. Such applications permit the users to ask the database to fetch data based on simple English instead of using SQL statements.


What is the purpose of SQL Profiler in SQL server?

SQL profiler is a tool to monitor performance of various stored procedures. It is used to debug the queries and procedures. Based on performance, it identifies the slow executing queries. Capture any problems by capturing the events on  production environment so that they can be solved.


What is XPath?

XPath is an expressions to select a xml node in an XML document.
It allows the navigation on the XML document to the straight to the element where we need to reach and access the attributes.


What are the Authentication Modes in SQL Server?

a.      Windows Authentication Mode  (Windows Authentication): uses user’s Windows account
b.      Mixed Mode (Windows Authentication and SQL Server Authentication): uses either windows or SQL server


Explain Data Definition Language, Data Control Language and Data Manipulation Language.

Data Definition Language (DDL):- are the SQL statements that define the database structure.
Example:
a.      CREATE
b.      ALTER
c.      DROP
d.      TRUNCATE
e.      COMMENT
f.       RENAME

Data Manipulation Language (DML):- statements are used for manipulate or edit data.
Example:
a.      SELECT - retrieve data from the a database
b.      INSERT - insert data into a table
c.      UPDATE - updates existing data within a table
d.      DELETE
e.      MERGE
f.       CALL
g.      EXPLAIN PLAN
h.      LOCK TABLE
Data Control Language (DCL):-statements to take care of the security and authorization.
Examples:
  1. GRANT
  2. REVOKE

What are the steps to process a single SELECT statement?

Steps

a.      The select statement is broken into logical units
b.           A sequence tree is built based on the keywords and expressions in the form of the logical units.
c.           Query optimizer checks for various permutations and combinations to figure out the fastest way using minimum resources to access the source tables. The best found way is called as an execution plan.
d.      Relational engine executes the plan and processes the data


Explain GO Command.

Go command is a signal to execute the entire batch of SQL statements after previous Go.

What is the significance of NULL value and why should we avoid permitting null values?

NULL value means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined. It is different from zero or "". They should be avoided to avoid the complexity in select & update queries and also because columns which have constraints like primary or foreign key constraints cannot contain a NULL value.




What is the difference between UNION and UNION ALL?

UNION selects only distinct values whereas UNION ALL selects all values and not just distinct ones.
UNION: SELECT column_names FROM table_name1
UNION
SELECT column_names FROM table_name2
UNION All: SELECT column_names FROM table_name1
UNION ALL
SELECT column_names FROM table_name2


What is use of DBCC Commands?

DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency. They are grouped as:
Maintenance: Maintenance tasks on Db, filegroup, index etc. Commands include DBCC CLEANTABLE, DBCC INDEXDEFRAG, DBCC DBREINDEX, DBCC SHRINKDATABASE, DBCC DROPCLEANBUFFERS, DBCC SHRINKFILE, DBCC FREEPROCCACHE, and DBCC UPDATEUSAGE.
Miscellaneous: Tasks such as enabling tracing, removing dll from memory. Commands include DBCC dllname, DBCC HELP, DBCC FREESESSIONCACHE, DBCC TRACEOFF, DBCC FREESYSTEMCACHE, and DBCC TRACEON.
Informational: Tasks which gather and display various types of information. Commands include DBCC INPUTBUFFER, DBCC SHOWCONTIG, DBCC OPENTRAN, DBCC SQLPERF, DBCC OUTPUTBUFFER, DBCC TRACESTATUS, DBCC PROCCACHE, DBCC USEROPTIONS, and DBCC SHOW_STATISTICS.
Validation: Operations for validating on Db, index, table etc. Commands include DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKDB.              


What is Log Shipping?

Log shipping defines the process for automatically taking backup of the database and transaction files on a SQL Server and then restoring them on a standby/backup server. This keeps the two SQL Server instances in sync with each other. In case production server fails, users simply need to be pointed to the standby/backup server. Log shipping primarily consists of 3 operations:
Backup transaction logs of the Production server.
Copy these logs on the standby/backup server.
Restore the log on standby/backup server.






What is the difference between a Local and a Global temporary table?

Temporary tables are used to allow short term use of data in SQL Server. They are of 2 types:
Local
Global
Only available to the current Db connection for current user and are cleared when connection is closed.
Available to any connection once created. They are cleared when the last connection is closed.
Multiple users can’t share a local temporary table.
Can be shared by multiple user sessions.


What is the STUFF and how does it differ from the REPLACE function?

Both STUFF and REPLACE are used to replace characters in a string.
 
select replace('abcdef','ab','xx') results in xxcdef
 
select replace('defdefdef','def','abc') results in abcabcabc
We cannot replace a specific occurrence of “def” using REPLACE.
 
select stuff('defdefdef',4, 3,'abc') results in defabcdef
 
where 4 is the character to begin replace from and 3 is the number of characters to replace.


What are the rules to use the ROWGUIDCOL property to define a globally unique identifier column?

Only one column can exist per table that is attached with ROWGUIDCOL property. One can then use $ROWGUID instead of column name in select list.


What is the actions prevented once referential integrity is enforced?

Actions prevented are:
·        Breaking of relationships is prevented once referential integrity on a database is enforced.
·        Can’t delete a row from primary table if there are related rows in secondary table.
·        Can’t update primary table’s primary key if row being modified has related rows in secondary table.
·        Can’t insert a new row in secondary table if there are not related rows in primary table.
·        Can’t update secondary table’s foreign key if there is no related row in primary table.


What are the commands available for Summarizing Data in SQL Server?

Commands for summarizing data in SQL Server:
Command
Description
Syntax/Example
SUM
Sums related values
SELECT SUM(Sal) as Tot from Table1;
AVG
Average value
SELECT AVG(Sal) as Avg_Sal from Table1;
COUNT
Returns number of rows of resultset
SELECT COUNT(*) from Table1;
MAX
Returns max value from a resultset
SELECT MAX(Sal) from Table1;
MIN
Returns min value from a resultset
SELECT MIN(Sal) from Table1;
GROUP BY
Arrange resultset in groups
SELECT ZIP,City FROM Emp GROUP BY ZIP
ORDER BY
Sort resultset
SELECT ZIP,City FROM Emp ORDER BY City


List out the difference between CUBE operator and ROLLUP operator

Difference between CUBE and ROLLUP:
CUBE
ROLLUP
It’s an additional switch to GROUP BY clause. It can be applied to all aggregation functions to return cross tabular result sets. .
It’s an extension to GROUP BY clause. It’s used to extract statistical and summarized information from result sets. It creates groupings and then applies aggregation functions on them.
Produces all possible combinations of subtotals specified in GROUP BY clause and a Grand Total.
Produces only some possible subtotal combinations.


What are the guidelines to use bulk copy utility of SQL Server?

Bulk copy is an API that allows interacting with SQL Server to export/import data in one of the two data formats. Bulk copy needs sufficient system credentials.
·        Need INSERT permissions on destination table while importing.
·        Need SELECT permissions on source table while exporting.
·        Need SELECT permissions on sysindexes, sysobjects and syscolumns tables.
bcp.exe northwind..cust out "c:\cust.txt" –c -T
Export all rows in Northwind.Cust table to an ASCII-character formatted text file.




What are the capabilities of Cursors?

Capabilities of cursors:
·        Cursor reads every row one by one.
·        Cursors can be used to update a set of rows or a single specific row in a resultset
·        Cursors can be positioned to specific rows.
·        Cursors can be parameterized and hence are flexible.
·        Cursors lock row(s) while updating them.


What are the ways to controlling Cursor Behavior?

There are 2 ways to control Cursor behavior:
·        Cursor Types: Data access behavior depends on the type of cursor; forward only, static, keyset-drive and dynamic.
·        Cursor behaviors: Keywords such as SCROLL and INSENSITIVE along with the Cursor declaration define scrollability and sensitivity of the cursor.


What are the advantages of using Stored Procedures?

Advantages of using stored procedures are:
·        They are easier to maintain and troubleshoot as they are modular.
·        Stored procedures enable better tuning for performance.
·        Using stored procedures is much easier from a GUI end than building/using complex queries.
·        They can be part of a separate layer which allows separating the concerns. Hence Database layer can be handled by separate developers proficient in database queries.
·        Help in reducing network usage.
·        Provides more scalability to an application.
·        Reusable and hence reduce code.


What are the ways to code efficient transactions?

Some ways and guidelines to code efficient transactions:
·        Do not ask for an input from a user during a transaction.
·        Get all input needed for a transaction before starting the transaction.
·        Transaction should be atomic
·        Transactions should be as short and small as possible.
·        Rollback a transaction if a user intervenes and re-starts the transaction.
·        Transaction should involve a small amount of data as it needs to lock the number of rows involved.
·        Avoid transactions while browsing through data.



What are the differences among batches, stored procedures, and triggers?
              
Batch
Stored Procedure
Triggers
Collection or group of SQL statements. All statements of a batch are compiled into one executional unit called execution plan. All statements are then executed statement by statement.
It’s a collection or group of SQL statements that’s compiled once but used many times.
It’s a type of Stored procedure that cannot be called directly. Instead it fires when a row is updated, deleted, or inserted.


What security features are available for stored procedures?

Security features for stored procedures:
·        Grants users permissions to execute a stored procedure irrespective of the related tables.
·        Grant users users permission to work with a stored procedure to access a restricted set of data yet no give them permissions to update or select underlying data.
·        Stored procedures can be granted execute permissions rather than setting permissions on data itself.
·        Provide more granular security control through stored procedures rather than complete control on underlying data in tables.


What are the instances when triggers are appropriate?

Scenarios for using triggers:
·        To create a audit log of database activity.
·        To apply business rules.
·        To apply some calculation on data from tables which is not stored in them.
·        To enforce referential integrity.
·        Alter data in a third party application
·        To execute SQL statements as a result of an event/condition automatically.


What are the restrictions applicable while creating views?

Restrictions applicable while creating views:
·        A view cannot be indexed.
·        A view cannot be Altered or renamed. Its columns cannot be renamed.
·        To alter a view, it must be dropped and re-created.
·        ANSI_NULLS and QUOTED_IDENTIFIER options should be turned on to create a view.
·        All tables referenced in a view must be part of the same database.
·        Any user defined functions referenced in a view must be created with SCHEMABINDING option.
·        Cannot use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, COMPUTE BY in views.

What are the events recorded in a transaction log?

Events recorded in a transaction log:
·        Broker event category includes events produced by Service Broker.
·        Cursors event category includes cursor operations events.
·        CLR event category includes events fired by .Net CLR objects.
·        Database event category includes events of data.log files shrinking or growing on their own.
·        Errors and Warning          event category includes SQL Server warnings and errors.
·        Full text event category include events occurred when text searches are started, interrupted, or stopped.
·        Locks event category includes events caused when a lock is acquired, released, or cancelled.
·        Object event category includes events of database objects being created, updated or deleted.
·         OLEDB event category includes events caused by OLEDB calls.
·        Performance event category includes events caused by DML operators.
·        Progress report event category includes Online index operation events.
·        Scans event category includes events notifying table/index scanning.
·        Security audit event category includes audit server activities.
·        Server event category includes server events.
·        Sessions event category includes connecting and disconnecting events of clients to SQL Server.
·        Stored procedures event category includes events of execution of Stored procedures.
·        Transactions event category includes events related to transactions.
·        TSQL event category includes events generated while executing TSQL statements.
·        User configurable event category includes user defined events.


Describe when checkpoints are created in a transaction log.

Activities causing checkpoints are:
·        When a checkpoint is explicitly executed.
·        A logged operation is performed on the database.
·        Database files have been altered using Alter Database command.
·        SQL Server has been stopped explicitly or on its own.
·        SQL Server periodically generates checkpoints.
·        Backup of a database is taken.



Define Truncate and Delete commands.

TRUNCATE
DELETE
This is also a logged operation but in terms of deallocation of data pages.
This is a logged operation for every row.
Cannot TRUNCATE a table that has foreign key constraints.
Any row not violating a constraint can be Deleted.
Resets identity column to the default starting value.
Does not reset the identity column. Starts where it left from last.
Removes all rows from a table.
Used delete all or selected rows from a table based on WHERE clause.
Cannot be Rolled back.
Need to Commit or Rollback
DDL command
DML command





Subscribe to: Posts (Atom)


Your IP Address is:

Browser: