SQL SERVER Basics

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

As OUTER join is applied subsequent to ON clause, all rows eliminated by the ON clause will still be included by the OUTER join as described in the article SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN.

Tip:

When I want to filter records in a query, I usually put the condition in the WHERE clause. When I make an inner join, I can put the condition in the ON clause instead, giving the same result. But with left joins this is not the case. Here is a quote from the SQL Server documentation:

Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.


Views:Views are virtual tables that are compiled at run time. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view.
 A view can be made over one or more database tables. Generally we put those columns in view that we need to retrieve/query again and again. Once you have created the view, you can query view like as table. We can make index, trigger on view.

Types of Views

In Sql Server we have two types of views.

01.System Defined Views

02.User Defined Views-->a)single table b)complex view(on multiple table)

system defined views:

01.Information Schema View
02.Catalog View
03.Dynamic Management View
01.Server-scoped Dynamic Management View(dmv)
These are stored only in the Master database.

02.Database-scoped Dynamic Management View(dmv)
These are stored in each database.


Deep on views:

•SQL SERVER – ORDER BY Does Not Work – Limitation of the View 1
•SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2
•SQL SERVER – Index Created on View not Used Often – Limitation of the View 3
•SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4
•SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
•SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
•SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
•SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8
•SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
•SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10
•SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11


Stored procedure:stored procedure is a precompiled set of one or more SQL statements that is stored on Sql Server.
 Benifit of Stored Procedures is that they are executed on the server side and perform a set of actions, before returning the results to the client side.
This allows a set of actions to be executed with minimum time and also reduce the network traffic. Hence stored procedure improve performance to execute sql statements.

Types of Stored Procedure
01.System Defined Stored Procedure
02.Extended Procedure
03.User Defined Stored Procedure
04.CLR Stored Procedure

1.System Defined Stored Procedure:These stored procedure are already defined in Sql Server.
These are physically stored in hidden Sql Server Resource Database and logically appear in the sys schema of each user defined and system defined database.

2.Extended procedures provide an interface to external programs for various maintenance activities.
These extended procedures starts with the xp_ prefix and stored in Master database. Basically these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.

3.User Defined Stored Procedure:These procedures are created by user for own actions. These can be created in all system databases except the Resource database or in a user-defined database.

Note
1.We can nest stored procedures and managed code references in Sql Server up to 32 levels only. This is also applicable for function, trigger and view.

2.The current nesting level of a stored procedures execution is stored in the @@NESTLEVEL function.

3.In Sql Server stored procedure nesting limit is up to 32 levels, but there is no limit on the number of stored procedures that can be invoked with in a stored procedure




Function:It is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result.
Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s).

Types of Function
01.System Defined Function
02.User Defined Function

02.User Defined Function:These functions are created by user in system database or in user defined database. We three types of user defined functions.

01.Scalar Function
02.Inline Table-Valued Function
03.Multi-Statement Table-Valued Function

Scalar function:User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function

2.Inline table-valued function:User defined inline table-valued function returns a table variable as a result of actions perform by function.
 The value of table variable should be derived from a single SELECT statement.

3.03.Multi-Statement Table-Valued Function
User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements


Cursor:
As we know, the cursors are required when we need to update records in a database table in singleton fashion means row by row.
You should avoid the use of cursor. In this article, I am explaining how
you can use cursor alternatives :
like as WHILE loop, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Cursor:
A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth
and lock resources. Hence it is mandatory to understand the cursor types and its functions so that you can use suitable cursor according to your needs.

Types of Cursors

01.Static Cursors
2.Dynamic cursors
3.forword only cursor
4.keyset driven cursosr


Triggers: Triggers are special stored procedures that an RDBMS executes, or fires, automatically.
You can use Data Modifi cation Language (DML) triggers to enforce data modifi cation rules and
Data Definition Language (DDL) triggers to enforce schema modification rules.
Triggers can fire before or after the statement that is modifying the state of a database.

1. After Triggers (For Triggers)
2. Instead Of Triggers