Question:
What is PRIMARY KEY?
Answer:
The PRIMARY KEY is a
field or column that uniquely identifies each record of the table, each table
can have only one key declared as a primary key. We can have PRIMARY KEY
constraint on more than one column, so duplicate values allowed in one of the
columns, but the combination of values from all the columns in the primary key
must be unique. By default, primary key creates clustered index.
Question:
What is FOREIGN KEY?
Answer:
A FOREIGN KEY is a
column or combination of columns used to establish and enforce a link between
the data in two tables. It ensures that the values that can be entered in a
particular column (foreign key) exist in a specified table (primary key). A
link is created between two tables by adding the column or columns that hold one
table's primary key values to the other table, this column becomes a foreign
key in the second table. Therefore, we can say that a FOREIGN KEY in one table
points to a PRIMARY KEY in another table.
Question:
What are COMPOSITE
KEY, CANDIDATE KEY and ALTERNATE KEY?
Answer:
A key formed by
combining at least two or more columns is called a composite key. When multiple
fields used as a primary key, they are called a COMPOSITE KEY. One primary key
contains more than one column.
A CANDIDATE KEY is one
that can identify each row of a table uniquely. A candidate key becomes the
primary key of the table. If the table has more than one candidate key, one of
them will become the primary key, and the rest are called ALTERNATE KEYs.
Question:
What is the difference
between a PRIMARY KEY and a UNIQUE key?
Answer:
Primary keys do not
allow NULL values, but unique keys allow one NULL value.
A primary key creates
a clustered index on the column and unique keys create a non-clustered index,
by default.
We can define only one
primary key per table.
We can have more than
one UNIQUE constraint per table.
Primary and unique
keys enforce uniqueness of the column on which they are defined.
You can use primary
key constraints to enforce uniqueness as well as referential integrity.
Primary key defined on
a table helps to relate that table to other tables. These relationships
help to normalize a database.
Question:
What is NORMALIZATION?
Answer:
Normalization is the
process of systematically organizing the data in a database. Normalization
process consists of eliminating redundant data by storing the same data in more
than one table, and ensuring data dependencies by only storing related data in
a table.
Normalization helps
to reduce redundancy of data and to make sure that all tables are properly
dependent.
The characteristic of
a normalized database is the number of small tables join together and
produce the required details.
To normalizing the
logical database design using formal methods to split the data into multiple
and number of related tables.
Reasonable
normalization also improves the performance query. When useful indexes are
available, and efficient joins between tables are used boost up performance.
Normalization is part
of successful database design, without normalization, database systems can be
inaccurate, inefficient, and slow they might not produce the data you expect.
Question:
What is DENORMALIZATION?
Answer:
Denormalization is the
process of deliberately adding redundant data or grouping data to optimize the
read performance of a database. Some times more normalization in a database
also reduce the performance of the process, such as in OLTP applications.
In some cases, denormalization helps cover up the inefficiencies inherent
in relational database software. A relational normalized database imposes a
heavy access load over physical storage of data even if it is well tuned for
high performance. It is the opposite of normalization process.
Denormalization helps
to speed up database access by moving from higher to lower form of
normalization.
Question:
What is ACID
Properties?
Answer:
ACID is an acronym for
Atomicity, Consistency, Isolation, and Durability.
ACID property ensures
the quality of the transaction.
Atomicity: If the
transaction is completed its execution then at the end, either all of the
transaction is successful, or all of the transaction fails. If a partial data
is written to disk, the atomic property is violated. It is an all-or-none
proposition.
Consistency: This
property ensures that a transaction never leaves the database in a partially
finished state.
Isolation: This
isolation level must implies that the transaction must either visible to the
data in its previous state or the transaction must wait until the changes from
other transactions are committed. It keeps transactions separated from each
other until they are finished.
Durability: This
property ensures that once the user has been notified of success, the
transaction will persist, and not be undone. The database will keep track of
pending changes in such a way that the server can recover from an abnormal
termination. This means it will survive system failure, and the database system
has checked the integrity constraints and will not need to abort the
transaction. In other words, when a client application receives notification
that a transaction has completed its work successfully, it is guaranteed that
the data is changed permanently.
Question:
What is the difference
between DBMS and RDBMS
Answer:
DBMS (Database
Management System) has to be persistent, i.e. it should be accessible when the
program created the data ceases to exist or even the application that created
the data restarted. A DBMS also has to provide some uniform methods independent
of a specific application for accessing the information that is stored.
DBMS is used for
easier business applications
RDBMS (Relational Data
Base Management) adds the additional condition that the system provides a
tabular structure for the data, with enforced relationships between the tables
in the database. This excludes the databases that do not provide a tabular
structure or don't enforce relationships between tables.
RDBMS is used for
complex applications.
RDBMS that enforces
the rules of foreign key concept
Question:
What are the types of
constraints?
Answer:
-> PRIMARY KEY
A primary key defines
the column or combination of columns that allow a row to be uniquely identified. Each table can have only one key
declared as a primary key.
Example:
You implement a
primary key on the CustomerID column of the Customers table as follows:
CREATE TABLE Customers
(
CustomerID
INT IDENTITY(1,1) PRIMARY KEY,
CustomerName
VARCHAR(50) NOT NULL,
CreatedDate DATETIME DEFAULT (GETDATE()),
)
GO
-> FOREIGN KEY
Foreign key
constraints can be used to implement a concept called referential integrity. It
ensures that the values that can be entered in a particular column exist in a
specified table (i.e. PRIMARY KEY table). Values cannot be inserted in this column
that do not exist in the specified table. A FOREIGN KEY in one table points to
a PRIMARY KEY in another table.
Example:
Create Orders table
and add foreign key constraint to Customers table CustomerID column.
CREATE TABLE Orders(
OrderID
INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT NOT NULL REFERENCES
Customers(CustomerID),
OrderDate DATETIME NOT NULL
DEFAULT CURRENT_TIMESTAMP
)
-> CHECK
CONSTRAINTS
A CHECK constraint
defines a condition for one or more columns in a table at the time of INSERT
and UPDATE operations.
Condition can be
defined by any expression that returns TRUE or FALSE value. If the condition
returns value TRUE then
The operation continues,
but if the condition returns FALSE value, the operation is rolled back.
Example:
CREATE TABLE Employees
(
EmployeeID
INT IDENTITY(1,1) NOT NULL,
EmployeeName
VARCHAR(50) NOT NULL,
ZipCode
VARCHAR(5) NOT NULL CHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]')
)
GO
Values rejected by the
CHECK constraints that evaluate to FALSE. Because NULL values evaluate to
UNKNOWN, their presence in expressions may override a constraint. For example,
suppose there is a constraint on an INT column "Unit" specifying that
"Unit" can contain only the value 20 (Unit=20). If we try to insert
the value NULL into "Unit" then the database engine inserts NULL and
does not return an error for this.
-> DEFAULT
CONSTRAINT
DEFAULT condition can
be defined for columns to avoid repetitive data entry. If a column has a
DEFAULT
Constraint then the
default value will be supplied if you don't provide a specific value for the
column in the INSERT statement. We can provide DEFAULT definition as any
expression that evaluates to a single scalar value with a data type that is
compatible with the data type of the column in which the DEFAULT definition is
defined.
This constraint is
applied only in INSERT operations.
This expression can be
a constant value, any scalar function in the system, the result of a scalar
user defined function, any scalar expression created from any combination
constant value or system scalar function or user defined function, including
mathematical expressions.
Example:
CREATE TABLE Customers
(
CustomerID
INT IDENTITY(1,1) NOT NULL,
CustomerName
VARCHAR(50) NOT NULL,
CreatedDate DATETIME DEFAULT (GETDATE()),
)
GO
-> RULES
Rules provide the same
functionality as check constraints, except that rules are created as a separate
object. Because rules are not associated with a specific table or column when
you create them, they cannot reference columns or tables in their definition.
Instead, you use variables as placeholders. We define check constraints within
the table definition and cannot reuse them. Rules provide the same features and
complex comparisons via AND, OR, and NOT as check constraints and allow pattern
matching. The following examples show the previous two check constraints
implemented as rules:
CREATE RULE
EmailValidatorRule
AS
@EmailValue like
‘%@%.[a-z][a-z][a-z]‘ OR @EmailValue like ‘%@%.[a-z][a-z].[a-z][a-z]‘;
After defining a rule,
you then bind it to columns or user-defined data types by using the sp_bindrule
system stored procedure as EXEC sp_bindrule 'EmailValidatorRule',
'dbo.mytesttable.Email'.
-> UNIQUE
CONSTRAINT
We can create a
PRIMARY KEY to enforce uniqueness in a field or group of fields, but we can
have only one PRIMARY KEY per table.
Therefore, if we
require enforcing uniqueness in other columns, you can create a UNIQUE
constraint. Unique keys allow one NULL value.
Example:
CREATE TABLE Employees
(
EmployeeID
INT NOT NULL UNIQUE NONCLUSTERED,
EmployeeName VARCHAR(50) NOT
NULL
)
GO
Question:
What is a Subquery?
Answer:
A subquery or Inner
query or Nested query is a nested select statement where the inner select
statement is evaluated first. It is a query in a query. We can use the results
of the inner select statement as the input for another select statement. For
example, you can use the results of a subquery as a search condition that uses
the IN ( ) function or EXISTS operator. A subquery is usually added in the
WHERE Clause of the sql statement.
Example:
USE NORTHWIND
GO
SELECT * FROM Orders
WHERE OrderID
IN(SELECT OrderID FROM [Order Details] WHERE OrderId = 90425)
Question:
What is a Correlated
Subquery?
Answer:
It is a subquery in
which the inner query is evaluated once for every value returned by the outer
query. Generally a query which has a subquery will execute the subquery once
and replace the resulting value or values into the WHERE clause of the outer
query.
Correlated subquery
(also known as a synchronized subquery or a repeating subquery) is a subquery
that uses values from the outer query in its WHERE clause. The subquery is
evaluated once for each row processed by the outer query. In queries that
include a correlated
subquery, the subquery
depends on the outer query for its values.
For example, using the
Northwind database, if you want to find out the price of all the books ordered,
you can use a correlated subquery. Price information about each book is in the
[Order Details] table, and the
Orders table has all
the books ordered.
Example:
USE NORTHWIND
GO
SELECT
o.OrderID,
o.OrderDate,
(SELECT MAX (d.UnitPrice) FROM [Order Details] AS d WHERE d.OrderID =
o.OrderID) AS MaximumUnitPrice
FROM Orders AS o
Question:
What is Stored
Procedures?
Answer:
A stored procedure is
a set of named Transact-SQL statement that is stored in the database in
compiled form so that it can be shared by a number of programs. The main
difference between a stored procedure and a set of statements is that a stored
procedure can be reused just by calling its name. Therefore, if you want to
re-run the code, you do not have to execute the whole set of statements that
compose the stored procedure one by one.
Question:
What are advantages of
Stored Procedures?
Answer:
- Stored Procedures
allow modular programming
- Stored Procedures
are precompiled statements
- Stored Procedures
provide faster code execution and optimize network traffic.
- Stored Procedures
provide security mechanism by needing to execute any of the statements directly.
- Stored Procedures
can be set to execute automatically when SQL Server starts
- Stored Procedures
can use parameters
Question:
Can we call a function
and stored procedure from a select statement?
Answer:
It is possible to call
a function from a select statement. It is not possible to call a stored
procedure from a select statement.
Question:
What is INDEX in SQL
Server?
Answer:
Indexes are created on
columns in tables or views. The index provides a fast way to access data based
on the values within those columns. Indexes are constructed as B-tree, which
enables SQL Server to search very large volumes of data without affecting the
performance from one query to the next. Indexes are useful only if it can help
find data quickly regardless of the volume of data stored.
Question:
What are the benefits
of Indexes?
Answer:
The database engine
can use indexes to boost performance in a number of different queries
-- Searching For
Records: When searching for rows with specific key values. These are queries
with a
WHERE clause to
restrict the query to specific values for every key column. (Exact-match
queries)
-- Sorting Records: to
produce an ordered output when using the ORDER BY clause.
-- Range queries: when
solving queries that search for a range of values in a column.
-- Filtering for
values in the foreign key to solve a join operation: When using a JOIN
predicate to search for rows in a table based on keys from a second table.
-- Hash and merge join
operations: In some cases, having an index can speed up the execution of a
JOIN algorithm,
because the data is exactly in the order that the JOIN algorithm uses.
-- Covering a query:
To avoid a full-table scan, when a narrow index contains all the required data.
-- Avoiding
duplicates: To check for the existence of suitable indexes in an INSERT or
UPDATE
operation in an
attempt to avoid duplicates.
Question:
Is it possible to
create Non-Clustered index on a primary key column?
Answer:
Yes. It is possible to
create a non-clustered Index on a primary key column. We can use syntax given
below to create a Non-Clustered Index on primary key column while creating
table itself.
CREATE TABLE Customers
(
CustomerID
INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
)
Question:
Is it possible to
create index on more than one column?
Answer:
Yes, it is possible to
create index on more than one column. We can include maximum 16 columns as key
columns while creating index. Sum of bytes should not be more than 900 bytes.
Question:
How many non-clustered
indexes can be created in SQL Server?
Answer:
There can be only 249
non-clustered indexes per table possible in SQL Server 2005 and 999 Non
Clustered Indexes per table in SQL Server 2008
Question:
Is it possible to
force a query to use a specific Index?
Answer:
Yes, it is possible to
force a query to use a specific index by using index hints.
Example:
SELECT FristName,
LastName
FROM Employees
WITH(INDEX (indx_firstname, indx_lastname))
What is the difference between
clustered and non-clustered INDEX?
Answer:
Clustered INDEX is
unique for any given table and the leaf level of a clustered index is the actual
data, and the data is re-sorted in case of clustered index. We can have only
one clustered index per table.
In case of
non-clustered INDEX the leaf level is actually a pointer to the data in rows so
we can have as many non-clustered indexes as we can on the database.
Consider the Telephone
Directory as an example of Clustered Index, in which data and index are at the
same page, whereas index in the backside of the book is a good example of
non-clustered index.
Only one clustered
index is possible per table and non-clustered index may be more than one per
table (There can be only 249 non-clustered indexes per table possible in SQL
Server 2005 and 999 Non Clustered Indexes per table in SQL Server 2008).
No comments:
Post a Comment