IQDefinitions

/*

A view is like a virtual table...
A view can select data from from a single table or multiple tables,
 and can even span multiple databases and servers.

ADV:

The end user or client application doesn't have to know anything about the complexity,
join syntax, or aggregates in the query underlying the view.

the definition of the view can be encrypted so that it cannot be read.

Schema binding - as KEY WORD after ie; with schema binding
Check option--after query ie;with check option

View Limitations :

You cannot pass parameters. Table valued functions are an excellent replacement for                           parameterized views

Rules and Defaults cannot be associated with views.

The ORDER BY clause is invalid in views unless TOP or FOR XML is also specified.

Views cannot be based on temporary tables.

*/



/*

Functions
=========

-- Scalar Functions

Return a Single Value

-- Inline Table Valued functions

Return a table
Based on a single select statement
Can select from, update, join to

-- Multi Table Valued functions

Return a table
Can include multiple T SQL statement and complex logic
Results can be selected from, joined to
Results cannot be updated
Like a SP that returns a read-only table
Cannot perform updates, insert, or deletes
Cannot execute Stored Procedures


Diff b/w Inline and Multi Valued
=================================

In the Inline Table function, the Returns clause cannot contain the structure of the table, the function returns.
Where as, with the multi-statement table valued function, we specify the structure of the table that gets returned.

Inline cannot have Begin and End block but Multi can have

Inline is better for performance than Multi.
Internally, SQL Server treats an inline table valued function like a view
and Multi like a Procedure

Its possible to update the underlying table, using an Inline, but not possible using Multi.



Function Options
================

-- Encryption

Encrypts the definition so that it cannot be read

-- Schemabinding

Underlying objects cannot be dropped


*/


Scope_Identity()
================

Returns the last identity inserted in the current scope and session
 (It is not affected by other connections or tables nor by triggers)

@@Identity
==========

Will return the last identify value inserted in any scope.
(This means that adding replication or auditing triggers to a database can alter the value of @@Identity)


Ident_Current('Stud')
=====================

Returns the last value inserted in to that table on any connection


Note:
=====

As stated earlier scope_identity() is probably the only one of the functions that you will need to use

Only one identity column per table

For existing column we cannot add identity column, or drop the existing one , But we can add new column with Identity


======================

SQL SERVER – User Defined Functions (UDF) Limitations

UDF have its own advantage and usage but in this article we will see the limitation of UDF (Things UDF cannot do) and why Stored Procedure is considered as more flexible then UDFs.

Stored Procedure has more flexibility then User Defined Functions (UDF).

·         UDF has No Access to Structural and Permanent Tables.

o    UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)

• UDF Prohibit Usage of Non-Deterministic Built-in Functions

• Functions GETDATE() etc cannot be used UDFs, but can be used in Stored Procedure

• UDF Returns Only One Result Set or Output Parameter

• Due to this it can be used in SELECT statement but cannot return multiple result set like Stored Procedure

• UDF cannot Call Stored Procedure

• Only access to Extended Stored Procedure.

• UDF cannot Execute Dynamic SQL or Temporary Tables

• UDF cannot run dynamic SQL which are dynamically built in UDF. Temporary Tables cannot be used in UDF as well.

• UDF cannot Return XML

• FOR XML is not allowed in UDF

• UDF does not support SET options

• SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)

• UDF does not Support Error Handling

• RAISEERROR or @@ERROR are not allowed in UDFs.

--------------------------------

Deleting Data in SQL Server with TRUNCATE vs DELETE commands

There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although each achieves the same result, the methods employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.

Solution
Deleting Data Using TRUNCATE TABLE

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it

 bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation.

 Records removed by the TRUNCATE TABLE statement cannot be restored.

 You cannot specify a WHERE clause in a TRUNCATE TABLE statement-it is all or nothing.

The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED,

and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.

TRUNCATE TABLE cannot used be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers.

This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire.

 If all table rows need to be deleted and there is a foreign key referencing the table,

you must drop the index and recreate it.

 If a TRUNCATE TABLE statement is issued against a table that has foreign key references, the following error is returned:

Deleting Data Using DELETE FROM Statement

DELETE TABLE statements delete rows one at a time, logging each row in the transaction log,

 as well as maintaining log sequence number (LSN) information.

Although this consumes more database resources and locks, these transactions can be rolled back if necessary.

You can also specify a WHERE clause to narrow down the rows to be deleted.

When you delete a large number of rows using a DELETE FROM statement, the table may hang on to the empty pages requiring manual release

 using DBCC SHRINKDATABASE (db_name).

When large tables require that all records be deleted and TRUNCATE TABLE cannot be used, the following

statements can be used to achieve the same result as TRUNCATE TABLE:

• DELETE from "table_name"
• DBCC CHECKIDENT("table_name", RESEED, "reseed_value")