Facts about SQL Concepts with Example

Facts about SQL Concepts with Example 



*UDF  Will allow only Select statement, it will not allow us to 

use DML statements.

*Table Variable Can use it inside UDF But Temporary Tables can't use inside UDF


*Transactions not allowed inside UDF


*UDF Can use inside/within SP But SP cant use inside/within UDF


*We can use UDF Inside Views Also We can use View inside UDF


*View did n't Accept Parameter (If you achieved you can Table 

Valued function to achieve that)



*We can't use SP inside View  but We can use View inside SP
Demo:

You Can't use Stored procedures Inside function/UDF It will throw error as see below

But You can use UDF inside Stored Procedures see below

you can see above UDF can used  inside SP code see below

*You Can't use Stored Procedures Inside View. It will throw error as see below

But We can use VIEW inside SP Kindly see below

We can use UDF Inside Views

Also We can use View inside UDF see below

View did n't Accept Parameter (If you achieved you can Table Valued functiont to achieve that)

also We can able to ALTER VIEW

View info see below

See We can able to ALTER VIEW

Also you can't use Begin and End Statements inside VIEW

see below

Also you can able to create UDF(User Defined Function) without any Parameter see below

Also Cant use DML Statements inside UDF

Also Can't use Temporary Tables Within/Inside UDF see below

But we can use Table Variable Inside/Within in UDF See below

Transaction not allowed within in UDF see below

Kindly see below Inside View You cant able to Declare User Defined Data Type it is true

 Also you can see View can able to use Inside UDF without any issue

Also Table Valued UDF can able to create it without any parameter defined as input see below

OPTION D is not correct because

You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported

for more ref see below link
https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx

create type cidType as Table
    (
    CompanyID int
    )

    declare @ctvp cidType

    insert into @ctvp(CompanyID)
    values(154)

    SELECT E.CompanyID FROM 
     dbo.Partners P
    join dbo.Events E on P.CompanyID=E.CompanyID
    join @ctvp cvp on cvp.CompanyID=P.CompanyID

when you use in normal SQL Statement TVP can use it  but TVP cant use in Views but we can use it in SP

Limitations of Table-Valued Parameters

There are several limitations to table-valued parameters:

• You cannot pass table-valued parameters to CLR user-defined functions.
• Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.
• Table-valued parameters are read-only in Transact-SQL code. You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.
• You cannot use ALTER TABLE statements to modify the design of table-valued parameters.