What
is Normalization?
In
relational database design, the process of organizing data to minimize
redundancy is called normalization. Normalization usually involves dividing a
database into two or more tables and defining relationships between the tables.
What
is Stored Procedure?
Stored
procedure is a named group of SQL statements that have been previously created
and kept in the server database. Stored procedures can accept input parameters
so that a single procedure can be used over the network by several clients
using different input parameters.
What
is Trigger?
A trigger
is a SQL procedure that initiates an action when an event (INSERT, DELETE or
UPDATE) occurs. Triggers are stored in and managed by the DBMS. A trigger
cannot be called or executed. DBMS automatically fires the trigger as a result
of a data modification to the associated table.
What
is View?
A view is a
virtual table and contains columns from different tables. A View does not
contain any data; it is a set of queries that are applied to one or more tables
that is stored within the database as an object.
What
is Index?
An index is
a physical structure containing pointers to the data. Indices are created in an
existing table to find data more quickly and efficiently. It is possible to
create an index on one or more columns of a table. They are just used to speed
up queries.
What are the difference between clustered and a non‐clustered
index?
A clustered index reorders the way records in the table
are physically stored. Therefore table can have only one clustered index.
A
non clustered index is special a type of index in which the logical order of
the index does not match the physical stored order of the rows on disk. Table
can have multiple clustered index.
What
is Cursor?
Cursor is a
database object used to manipulate data in a set on a row‐by‐row basis.
What
is Difference between UDF (User defined function)
· UDFs that return
tables can be treated as another rowset. This can be used in JOINs with other
tables.
· Inline UDF's can be
thought of as views that take parameters and can be used in JOINs and other
Rowset operations.
·
UDF can be used in the SQL statements anywhere in the
WHERE/HAVING/SELECT
What
are built-in functions for performing calculations on data?
- SQL Scalar functions
SQL scalar functions return a single value, based on the
input value.
ü
UCASE()
- Converts a field to upper case
ü
LCASE()
- Converts a field to lower case
ü
MID()
- Extract characters from a text field
ü
LEN()
- Returns the length of a text field
ü
ROUND()
- Rounds a numeric field to the number of decimals specified
ü
NOW()
- Returns the current system date and time
ü
FORMAT()
- Formats how a field is to be displayed
- SQL Aggregate Functions
SQL aggregate functions return a single value, calculated
from values in a column.
ü
AVG()
- Returns the average value
ü
COUNT()
- Returns the number of rows
ü
FIRST()
- Returns the first value
ü
LAST()
- Returns the last value
ü
MAX()
- Returns the largest value
ü
MIN()
- Returns the smallest value
ü
SUM()
- Returns the sum
What
are different Types of Join?
An SQL JOIN clause is used to combine rows from two or more
tables, based on a common field between them.
List of joins:
- Inner Join
- Left Join
- Right Join
- Full Join
- Cross Join
What
is Inner Join?
Inner join
retrieve rows that have a match in both joined tables.
What
is Outer Join?
A join that
includes rows even if they do not have related rows in the joined table is an
Outer Join. There are three types of outer join
- Left Outer Join: In Left Outer Join all rows in the first‐named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
- Right Outer Join: In Right Outer Join all rows in the second‐named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
- Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.
What
is Self Join?
When one
table joins to itself is call self Join. A self join can be of any type, as
long as the joined tables are the same. A self join is rather unique in that it
involves a relationship with only one table.
What
is Cross
Join?
A cross
join that does not have a WHERE clause and hence produces the Cartesian product
of the tables involved in the join. The size of a Cartesian product is the
number of rows in the first table multiplied by the number of rows in the
second table.
What is the difference between Union and Union All?
The UNION
operator is used to combine the result-set of two or more SELECT statements.
Each SELECT statement within the UNION must have the same number of columns.
The columns must also have similar data types. Also, the columns in each SELECT
statement must be in the same order.The UNION operator selects only distinct
values by default. To allow duplicate values, use the ALL keyword with UNION.
What
is difference between DELETE & TRUNCATE commands?
- The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
- TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
Can
a stored procedure call itself recursively? How much level SP nesting is
possible?
Yes, we can
write stored procedures that call themselves. Recursion can be defined as a
method of problem solving wherein the solution is arrived at by repetitively
applying it to subsets of the problem.. You can nest stored procedures up to 32
levels.
What
is the difference between a Local and a Global temporary table?
- A local temporary table available only for the duration of a connection And if it defined inside a compound statement, for the duration of the compound statement.
- A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
What
is PRIMARY KEY?
- Primary keys are the unique identifiers for each row.
- They must contain unique values and cannot be null.
- A table can have only one Primary key.
What
is UNIQUE KEY constraint?
- The UNIQUE constraint uniquely identifies each record in a database table.
- The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
- A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
- Table can have many UNIQUE constraints.
What
is FOREIGN KEY?
- A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
- Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value.
- The foreign key constraints are used to enforce referential integrity.
What
is CHECK Constraint?
- The CHECK constraint is used to limit the value range that can be placed in a column.
- If you define a CHECK constraint on a single column it allows only certain values for this column.
- If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
What
is NOT NULL Constraint?
- NOT NULL constraint enforces a column to NOT accept NULL values.
- NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.
What
are the advantages of using Stored Procedures (SP)?
· SP’s help code
reuse.
- SP’s provide better security to your data.
- SP’s can reduced network traffic and latency, boosting application performance.
- SP’s execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
- SP’s can encapsulate logic and can be changed without affecting clients.
If
it has neither Cluster nor Non‐cluster Index, what you call it.
It called unindexed
table or Heap.
What
is BCP? When does it used?
BulkCopy is
a tool used to copy huge amount of data from tables and views. BCP does not
copy the structures same as source to destination.
What
is the default in SQL server databases? What are there uses?
- master -The master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
- msdb -The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
- tempdb -The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
- model -The model is essentially a template database used in the creation of any new user database created in the instance
What is Filtered Index?
Filtered
Index is used to index a portion of rows in a table that means it applies
filter on INDEX which improves query performance, reduce index maintenance
costs, and reduce index storage costs compared with full‐table indexes. When we
see an Index created with some where clause then that is actually a FILTERED
INDEX.
What
are the authentication modes in SQL Server?
- Windows mode
- Mixed Mode -SQL & Windows.
