Sql DBA related Questions and Answeres

http://www.pinaldave.com/best-sql-server-download.cfm?download=SQL-SERVER-2008-Download-Interview-Questions-and-Answers

What is a SQL view?

An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.

Postgres

CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;

SqlServer

CREATE VIEW view_name
[(column_name[,column_name]….)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

How to know the no of connection to the server?

Postgres
select count(*) from pg_stat_activity--Along with dbname and no of Connections

No of Connections
select count(*) from pg_stat_activity

Sql server

SELECT DB_NAME(dbid) as 'DbNAme', COUNT(dbid) as 'Connections' from master.dbo.sysprocesses with (nolock)
where dbid >0 group by dbid

What are triggers?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster

Syntax
CREATE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
EXECUTE PROCEDURE trigger_function();

What are cursors? Explain different types of cursors. What are the disadvantages of cursors?

Cursors allow row-by-row processing of the result-sets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one roundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Diff in dropping a table || truncating a table || deleting all records

Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes

Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete

Delete : (Data alone deleted), Doesn’t perform automatic commit

Coders: DBMS/RDBMS

Coders: DBMS/RDBMS

Coders: POSTGRESQL vs MYSQL

Coders: POSTGRESQL vs MYSQL

Coders: DML DDL DCL TCL

Coders: DML DDL DCL TCL

Coders: Difference between Cluster and Non-cluster index

Coders: Difference between Cluster and Non-cluster index

Coders: UNIQUE constraints Vs UNIQUE index

Coders: UNIQUE constraints Vs UNIQUE index: "UNIQUE constraints Vs UNIQUE index"

Diff Between Function and Stored Procedure

A FUNCTION is always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.

A Function can be used in the Sql Queries.
A PROCEDURE cannot be used