SQL QUESTIONS

What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the

view was created with. It should also be noted that as data in the original table changes, so does data

in the view, as views are the way to look at part of the original table. The results of using a view are

not permanently stored in the database. The data accessed through a view is actually constructed using

standard T-SQL select command and can come from one to many different base tables or even other

views.

What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table

to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of

a table, and each index is given a name. The users cannot see the indexes, they are just used to speed

up queries. Effective indexes are one of the best ways to improve performance in a database

application. A table scan happens when there is no index available to help a query. In a table scan SQL

Server examines every row in the table to satisfy the query results. Table scans are sometimes

unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this

reason, each database table may have only one clustered index.

Non-clustered indexes are created outside of the database table and contain a sorted list of references

to the table itself.

What is SQL server agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It

is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the

implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to

schedule your own jobs and scripts.

What are different normalization forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field

contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, remove them to a separate table. All

attributes must be directly dependent on the primary key

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, separate them out into distinct

tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many

relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first

fulfill all the criteria of a 2NF and 1NF database.

  

TRUNCATE?

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the

page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes

and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE can not be Rolled back.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table.

DELETE?

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition

and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE Can be Rolled back.

DELETE is DML Command.

DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as

Stored procedures cannot be.

UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline UDF's can be though of as views that take parameters and can be used in JOINs and other

Rowset operations.