1.
What
is SQL?
SQL
is a standard language for accessing and manipulating databases.
2.
What
is T-SQL?
Transact-SQL (T-SQL)
is Microsoft's and Sybase's proprietary extension to SQL. SQL, often expanded
to Structured Query Language, is a standardized computer language that was
originally developed by IBM for querying, altering and defining relational
databases, using declarative statements. T-SQL expands on the SQL standard to
include procedural programming, local variables, various support functions for
string processing, date processing, mathematics, etc. and changes to the DELETE
and UPDATE statements. These additional features make Transact-SQL Turing
complete.
Transact-SQL is
central to using Microsoft SQL Server.
All applications that
communicate with an instance of SQL Server do so by sending Transact-SQL
statements to the server, regardless of the user interface of the application.
3.
What
is SQL SERVER?
Microsoft
SQL Server is a relational database management
system developed by Microsoft.
As a database, it is a software product whose primary function is to store and
retrieve data as requested by other software applications, be it those on the
same computer or those running on another computer across a network (including
the Internet).
4.
What
is DBMS?
Stands
for "Database Management System." It is a software system that uses a
standard method of cataloging, retrieving, and running queries on data. The
DBMS manages incoming data, organizes it, and provides ways for the data to be
modified or extracted by users or other programs.
5.
What
is RDBMS?
6.
What
is Functions?
Function
is a block of sql statements that accept parameters, perform an action, such as
a complex calculation, and return the result of that action as a value. The
return value can either be a single scalar value or a result set.
Types of Functions
Scalar Function
User-defined
scalar functions return a single data value of the type defined in the RETURNS
clause. For an inline scalar function, there is no function body; the scalar
value is the result of a single statement. For a multistatement scalar
function, the function body, defined in a BEGIN...END block, contains a series
of Transact-SQL statements that return the single value. The return type can be
any data type except text, ntext, image, cursor, and timestamp.
Table-Valued Functions
User-defined
table-valued functions return a table data type. For an inline table-valued
function, there is no function body; the table is the result set of a single
SELECT statement.
System Functions
SQL
Server provides many system functions that you can use to perform a variety of
operations. They cannot be modified.
7. What
is Procedures?
Stored
Procedure group of sql statements which is grouped to perform a specific task.
The main benefit of using a stored procedure is that it increases the
performance of the database. Typical use for stored procedures includes data
validation or access control mechanisms. It may return value in
case select case.
Stored
procedures are similar to user-defined functions
(UDFs). The major difference is that UDFs can be used like any other expression
within SQL statements, whereas stored procedures must be invoked using the CALL
statement.
In
SQL we are having different types of stored procedures are there
a)
System Stored Procedures
b)
User Defined Stored procedures
c)
Extended Stored Procedures
System Stored Procedures:
System
stored procedures are stored in the master database and these are starts with a
sp_ prefix. These procedures can be
used to perform variety of tasks to support sql server functions for external
application calls in the system tables
Ex:
sp_helptext [StoredProcedure_Name]
User Defined Stored Procedures:
User
Defined stored procedures are usually stored in a user database and are
typically designed to complete the tasks in the user database. While coding
these procedures don’t use sp_ prefix because if we use the sp_ prefix
first it will check master database then it comes to user defined database
Extended Stored Procedures:
Extended
stored procedures are the procedures that call functions from DLL files. Now a
day’s extended stored procedures are depreciated for that reason it would be
better to avoid using of Extended Stored procedures.
8. What is Triggers?
A
database trigger is procedural
code that is automatically executed in response to certain
events on a particular table or view
in a database.
Triggers are used to enforce data integrity and business
rules
such as automatically updating summary data.
9. What is Joins?
A JOIN is a means for combining fields from two or
more tables by using values common to each.
When you join tables, the type of join
that you create affects the rows that appear in the result set. You can create
the following types of joins:
1. Inner
join A join that
displays only the rows that have a match in both joined tables. (This is the default
type of join in the Query and View Designer.)
NoteNote
Columns containing NULL do not match any values when you are creating an
inner join and are therefore excluded from the result set. Null values do not
match other null values.
2. Outer join A join that includes rows even if they do not
have related rows in the joined table. You can create three variations of an outer
join to specify the unmatched rows to be included:
A. Left outer
join All rows from the first-named table
(the "left" table, which appears leftmost in the JOIN clause) are
included. Unmatched rows in the right table do not appear.
B. Right outer
join All rows in the second-named table
(the "right" table, which appears rightmost in the JOIN clause) are
included. Unmatched rows in the left table are not included.
C. Full outer join All rows in all joined tables are included,
whether they are matched or not. For example, a full outer join between titles and
publishers shows all titles and all publishers, even those that have no match in
the other table.
NoteNote
Some databases, such as Oracle, do not support full outer joins.
3. Cross
join A join whose result
set includes one row for each possible pairing of rows from the two tables. This
join combines all the rows from the left table with every row from the right
table. This type of join is needed when we need to select all the possible
combinations of rows and columns from both the tables. This type of join is
generally not preferred as it takes lot of time and gives a huge result that is
not often useful.
4.
Self
Join Joining the table
itself called self join. Self join is used to retrieve the records having some
relation or similarity with other records in the same table. Here we need to
use aliases for the same table to set a self join between single table and
retrieve records satisfying the condition in where clause.
SELECT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;
10.
What
is View?
A
view can be thought of as either a virtual table or a stored query. The data
accessible through a view is not stored in the database as a distinct object.
What is stored in the database is a SELECT statement. The result set of the
SELECT statement forms the virtual table returned by the view. A user can use
this virtual table by referencing the view name in Transact-SQL statements the
same way a table is referenced. A view is used to do any or all of these
functions:
- Restrict
a user to specific rows in a table.
- Restrict
a user to specific columns.
- Join
columns from multiple tables so that they look like a single table.
- Aggregate
information instead of supplying details.
11.
What
is Indexes?
An
index is an on-disk structure associated with a table or view that speeds
retrieval of rows from the table or view. An index contains keys built from one
or more columns in the table or view. These keys are stored in a structure
(B-tree) that enables SQL Server to find the row or rows associated with the
key values quickly and efficiently.
If
a table is created with no indexes, the data rows are not stored in any
particular order. This structure is called a heap.
A
table or view can contain the following types of indexes:
- Clustered
- Clustered
indexes sort and store the data rows in the table or view based on their
key values. These are the columns included in the index definition. There
can be only one clustered index per table, because the data rows
themselves can be sorted in only one order.
- The
only time the data rows in a table are stored in sorted order is when the
table contains a clustered index. When a table has a clustered index, the
table is called a clustered table. If a table has no clustered index, its
data rows are stored in an unordered structure called a heap.
- Nonclustered
- Nonclustered
indexes have a structure separate from the data rows. A nonclustered
index contains the nonclustered index key values and each key value entry
has a pointer to the data row that contains the key value.
- The
pointer from an index row in a nonclustered index to a data row is called
a row locator. The structure of the row locator depends on whether the
data pages are stored in a heap or a clustered table. For a heap, a row
locator is a pointer to the row. For a clustered table, the row locator
is the clustered index key.
- You
can add nonkey columns to the leaf level of the nonclustered index to
by-pass existing index key limits, 900 bytes and 16 key columns, and
execute fully covered, indexed, queries.
Both
clustered and nonclustered indexes can be unique. This means no two rows can
have the same value for the index key. Otherwise, the index is not unique and
multiple rows can share the same key value. Indexes are automatically
maintained for a table or view whenever the table data is modified.
12.
What
is Constraints?
Constraints
define rules regarding the values allowed in columns and are the standard
mechanism for enforcing integrity.
·
NOT NULL specifies that the column does
not accept NULL values.
·
CHECK constraints enforce domain
integrity by limiting the values that can be put in a column.
·
UNIQUE constraints enforce the
uniqueness of the values in a set of columns.
·
PRIMARY KEY constraints identify the
column or set of columns that have values that uniquely identify a row in a
table.
·
FOREIGN KEY constraints identify and
enforce the relationships between tables. A foreign key in one table points to
a candidate key in another table.
13.
What is a Database Schema?
A
database schema is a way to logically group objects such as tables, views,
stored procedures etc. Schemas can be created and altered in a database, and
users can be granted access to a schema. A schema can be owned by any user, and
schema ownership is transferable.
It
separate database users from database object owners. They give DBA’s the
ability to protect sensitive objects in the database, and also to group logical
entities together.
14.
DML,
DDL, DCL, TCL????
DML
is abbreviation of Data Manipulation Language. It is used to retrieve,
store, modify, delete, insert and update data in database.
SELECT
– Retrieves data from a table
INSERT - Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table
DDL
is abbreviation of Data Definition Language. It is used to create and
modify the structure of database objects in database.
CREATE
– Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to
initial value.
DCL
is abbreviation of Data Control Language. It is used to create roles,
permissions, and referential integrity as well it is used to control access to
database by securing it.
GRANT
– Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT
command
TCL
is abbreviation of Transactional Control Language. It is used to manage
different transactions occurring within a database.
COMMIT
– Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in
transactions
SAVE TRANSACTION – Sets a savepoint within a transaction
15.
Union,
UnionAll??
The
UNION ALL operator is used to
combine the results of two SELECT statements including duplicate rows.
The
SQL UNION clause/operator is used to combine the results of two or more
SELECT statements without returning any duplicate rows.
16.
What
Is Cursor?
CURSOR is a server
side tool. It gives row-by-row solution to the result set.
17.
Difference
Between View and Stored Procedure?
A stored procedure:
* accepts parameters
* can NOT be used as building
block in a larger query
* can contain several
statements, loops, IF ELSE, etc.
* can perform modifications to
one or several tables
* can NOT be used as the target
of an INSERT, UPDATE or DELETE
statement.
A view:
* does NOT accept parameters
* can be used as building block
in a larger query
* can contain only one single
SELECT query
* can NOT perform modifications
to any table
* but can (sometimes) be used as
the target of an INSERT, UPDATE or
DELETE statement.
18.
Difference between Stored Procedure and
Function in SQL Server
Stored
Procedures are pre-compile objects which are compiled for first time and its
compiled format is saved which executes (compiled code) whenever it is called.
But Function is compiled and executed every time when it is called.
Basic Difference
1.
Function
must return a value but in Stored Procedure it is optional( Procedure can
return zero or n values).
2.
Functions
can have only input parameters for it whereas Procedures can have input/output
parameters .
3.
Function
takes one input parameter it is mandatory but Stored Procedure may take o to n
input parameters..
4.
Functions
can be called from Procedure whereas Procedures cannot be called from Function.
Advance
Difference
1.
Procedure
allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas
Function allows only SELECT statement in it.
2.
Procedures
can not be utilized in a SELECT statement whereas Function can be embedded in a
SELECT statement.
19.
How to copy one table to another?
Method
1 : INSERT INTO SELECT
This
method is used when
table is
already created in the database
earlier and data is
to be inserted into
this table from
another table. If columns listed in insert clause and select clause are
same, they are are not
required to
list them. I always list them for readability and
scalability purpose.
USE AdventureWorks
----Create TestTable
CREATE TABLE
TestTable (FirstName
VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO
TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE
TestTable
Method
2 : SELECT INTO
This
method is used when
table is not created earlier and
needs to be created when
data from one table
is to be
inserted into newly created table from another table. New table is created with same data types
as selected columns.
USE AdventureWorks
----Create new table and insert into table using SELECT
INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
20.
Difference
between Temporary table and table variables?
21. Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT
In most of our application scenario, we need to get latest inserted
row information through SQL query. And for that, we have multiple
options like:
@@IDENTITY SCOPE_IDENTITYIDENT_CURRENT
All three functions return last-generated identity values. However, the scope and session on which
last is defined in each of these functions differ.
@@IDENTITY
It returns the last identity value generated for any table in the current session, across all scopes.
Let me explain this... suppose we create an
insert trigger on table which inserts a row in another table with generate an identity column, then
@@IDENTITY returns that identity record which is created by trigger.
SCOPE_IDENTITY
It returns the last identity value generated for any table in the current session and the current scope.
Let me explain this... suppose we create an insert trigger on table
which inserts a row in another table with generate an identity column,
then
SCOPE_IDENTITY result is not affected but if a trigger
or a user defined function is affected on the same table that produced
the value returns that identity record then
SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.
IDENT_CURRENT
It returns the last identity value generated for a specific table in any session and any scope.
In other words, we can say it is not affected by scope and session,
it only depends on a particular table and returns that table related
identity value which is generated in any session or scope.
CREATE TABLE Parent(id int IDENTITY);
CREATE TABLE Child(id int IDENTITY(100,1));
CREATE TRIGGER Parentins ON Parent FOR INSERT
AS
BEGIN
INSERT Child DEFAULT VALUES
END;
SELECT id FROM Parent;
SELECT id FROM Child;
INSERT Parent DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/
SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/
SELECT IDENT_CURRENT('Child');
/* Returns value inserted into Child, that is in the trigger.*/
SELECT IDENT_CURRENT('Parent');
