Units sales * Unit Price=Total Sales

This is a computed column example

This is deterministic

Example:

 4^2=16 every time!

functions are deterministic when:

they're schema bound

defined with only deterministic user-defined functions or built-in functions

you must make a function this if you want to create an index on computed column or view definition

This is like a view

This returns a dataset (rows/columns)

Based on internal TSQL BEGIN/END statements

which does not delimit function's body

SELECT in the RETURN clause follows VIEW rule concerning SELECT clause

RETURNS specifies data type returned as TABLE :-)

This SELECT statement cannot:

include COMPUTE

include COMPUTE BY

include ORDER BY (unless you have TOP clause)

include INTO keyword (unless you want to drop results into another table)

you cannot:

reference a temp table or table variabl

This is a combo of:

view and stored procedures

returns dataset

based on an

Included SELECT statement

this data type is what is listed after RETURNS in a multistatement table-value function

that is a table or dataset or rows & columns

in addition to containing TABLE data type,

this defines:

table name

table formate

scope of return variable name which is local to the function

returns a different value each time it is run

unlike the Celcius to Fahrenheit formula or

4^2=x

single data value is returned (or passed)

As is defined by RETURN Statement

Used in SELECT

or in a

WHERE Clause

Use EXEC

or

EXECUTE

5 types of functions

Name them:

• built in
• scalar
• inline table-valued (found inside BEGIN/END block)
• multistatement table-valued (routine returns dataset based on an included SELECT statement)
• CLR functions (which can themselves be scalar (as in real time currency conversion) or table-valued)

• avg()
• checksum_agg()
• count_big()
• binary_checksum()
• min()
• stdevp()
• var()
• max()
• checksum()
• count()
• grouping()
• stdev()
• sum()
• varp()

They are functions that support:

• encryption
• decryption
• digital signing
• validation of digital signatures

EncryptByKey()

DecryptByKey()

what do these functions do?

server_name()

db_name()

they give you info on

the server and database configurations

respectively

date and time functions (see page 101)

math functions (see page 102)

others (see page 102 through 103)

ranking functions (see page 103)

String functions (page 103)

this is a function that removes

any trailing spaces inserted by SQL Server

this function deletes a specified length of

characters

it inserts another set of characters

at a specified point

This is where you use the

EXECUTE AS clause

and by default this is limited to the current DB

if outside the limited current DB,

ALTER DATABASE databaseName SET TRUSTWORTHY ON

the calling database must be labeled as trustworthy

This must be set for the user who is trying to access the target instance

and also that user must have login for the target  instance

this refers to EXECUTE AS

This connects the function in question

to the object that it references

tie a function to the object

objects referenced must be in the same database

The other views and user-defined functions referenced

must also be this, schema-bound!