Create database programmability objects by using Transact-SQL
Exam 70-761: Querying Data with Transact-SQL retired on 31 January, 2021.
CREATE PROCEDURE (Transact-SQL)
Creates a Transact-SQL or common language runtime (CLR) stored procedure in SQL Server, Azure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. Stored procedures are similar to procedures in other programming languages in that they can:
- Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
- Contain programming statements that perform operations in the database, including calling other procedures.
- Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
We use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb database.
Syntax
Guidelines for Transact-SQL procedures
- All Transact-SQL data types can be used as parameters.
- You can use the user-defined table type to create table-valued parameters. Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. For more information, see Use Table-Valued Parameters (Database Engine)
- cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.
Guidelines for CLR procedures
- All of the native SQL Server data types that have an equivalent in managed code can be used as parameters. For more information about the correspondence between CLR types and SQL Server system data types, see Mapping CLR Parameter Data. For more information about SQL Server system data types and their syntax, see Data Types (Transact-SQL).
- Table-valued or cursor data types cannot be used as parameters.
- If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.
Creating User-defined Functions (Database Engine)
We shall now discuss how to create a user-defined function (UDF) in SQL Server by using Transact-SQL.
Limitations and restriction in creating user-defined functions
- User-defined functions cannot be used to perform actions that modify the database state.
- User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.
- User-defined functions can not return multiple result sets. Use a stored procedure if you need to return multiple result sets.
- Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR.
- User-defined functions cannot call a stored procedure, but can call an extended stored procedure.
- User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.
- SET statements are not allowed in a user-defined function.
- The FOR XML clause is not allowed.
- User-defined functions can be nested; that is, one user-defined function can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit.
Also the following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function:
- BEGIN DIALOG CONVERSATION
- END CONVERSATION
- GET CONVERSATION GROUP
- MOVE CONVERSATION
- RECEIVE
- SEND
Permissions to create user-defined function
Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. If the function specifies a user-defined type, requires EXECUTE permission on the type.
Scalar Functions
The following example creates a multi-statement scalar function (scalar UDF) in the AdventureWorks2012 database. The function takes one input value, a ProductID, and returns a single data value, the aggregated quantity of the specified product in inventory.
The ufnGetInventoryStock function to return the current inventory quantity for products that have a ProductModelID between 75 and 80.
Table-Valued Functions
The function takes one input parameter, a customer (store) ID, and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD Total for each product sold to the store.
Best Practices for creating user-defined functions
If a user-defined function (UDF) is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. It is suggested to implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects –
- Specify the WITH SCHEMABINDING clause when you are creating the UDF. This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.
- Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the UDF.
While creating a user-defined functions that does not access data, specify the SCHEMABINDING option. This will prevent the query optimizer from generating unnecessary spool operators for query plans involving these user-defined functions.
CREATE VIEW (Transact-SQL)
Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes –
- To focus, simplify, and customize the perception each user has of the database.
- As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
- To provide a backward compatible interface to emulate a table whose schema has changed.
Creating View
When a view is created by using WITH VIEW_METADATA, all its columns, except a timestamp column, are updatable if the view has INSTEAD OF INSERT or INSTEAD OF UPDATE triggers. A view can be created only in the current database. The CREATE VIEW must be the first statement in a query batch. A view can have a maximum of 1,024 columns.
You can modify the data of an underlying base table through a view, as long as the following conditions are true:
- Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
- The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
- An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
- A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
- The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
- TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
Partitioned Views
A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.
Scalar, Inline and Table valued functions
Scalar function, Inline table-valued functions and Multi-Statement table-valued functions are User defined functions.
- Scalar Functions: A scalar function accepts any number of parameters and returns one value.The term scalar differentiates a single, “flat” value from more complex structured values, such as arrays or result sets. This pattern is much like that of traditional functions written in common programming language.
- Inline Table-Valued Functions: This type of functions returns a result set, much like a view. How ever ,unlike a view,functions can accept parameters. The inline function’s syntax is quite simple.In the function definition, the return type is set to a table. A return statement is used with a select query in parenthesis.
- Multi-Statement Table-Valued Functions: Multi-Statement functions can be used to do some very unique things outside the context of a standard SELECT statement. This type of function returns a table-type result set,but the table is explicitly constructed in script. This can be used to accomplish one of two things: either to process some very unique business logic by assembling a virtual table on the fly,or to duplicate the functionality of an inline function in a more verbose and compiled way. In sort,if you need to select records from an existing a result set,use an inline table-valued function.
Deterministic and Non-Deterministic Functions
- Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database.
- Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
Built-in Function Determinism
You cannot influence the determinism of any built-in function. Each built-in function is deterministic or nondeterministic based on how the function is implemented by SQL Server. For example, specifying an ORDER BY clause in a query does not change the determinism of a function that used in that query. All of the string built-in functions are deterministic.
The following built-in functions from categories of built-in functions other than string functions are always deterministic.
| ABS | DATEDIFF | POWER |
| ACOS | DAY | RADIANS |
| ASIN | DEGREES | ROUND |
| ATAN | EXP | SIGN |
| ATN2 | FLOOR | SIN |
| CEILING | ISNULL | SQUARE |
| COALESCE | ISNUMERIC | SQRT |
| COS | LOG | TAN |
| COT | LOG10 | YEAR |
| DATALENGTH | MONTH | |
| DATEADD | NULLIF |
The following built-in functions from other categories are always nondeterministic.
| @@CONNECTIONS | GETDATE |
| @@CPU_BUSY | GETUTCDATE |
| @@DBTS | GET_TRANSMISSION_STATUS |
| @@IDLE | LAG |
| @@IO_BUSY | LAST_VALUE |
| @@MAX_CONNECTIONS | LEAD |
| @@PACK_RECEIVED | MIN_ACTIVE_ROWVERSION |
| @@PACK_SENT | NEWID |
| @@PACKET_ERRORS | NEWSEQUENTIALID |
| @@TIMETICKS | NEXT VALUE FOR |
| @@TOTAL_ERRORS | NTILE |
| @@TOTAL_READ | PARSENAME |
| @@TOTAL_WRITE | PERCENTILE_CONT |
| CUME_DIST | PERCENTILE_DISC |
| CURRENT_TIMESTAMP | PERCENT_RANK |
| DENSE_RANK | RAND |
| FIRST_VALUE | RANK |
| ROW_NUMBER | |
| TEXTPTR |
Creating Indexed Views
Creating a unique clustered index on a view improves query performance since the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.
Steps to create an indexed view and are critical to the successful implementation of the indexed view
- Verify the SET options are correct for all existing tables that will be referenced in the view.
- Verify that the SET options for the session are set correctly before you create any tables and the view.
- Verify that the view definition is deterministic.
- Create the view by using the WITH SCHEMABINDING option.
- Create the unique clustered index on the view.
Deterministic Views
The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Only deterministic functions can participate in deterministic expressions. For instance, the DATEADD function is deterministic since it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, but the value it returns changes each time it is executed.
To determine whether a view column is deterministic, use the IsDeterministic property of the COLUMNPROPERTY function. To determine if a deterministic column in a view with schema binding is precise, use the IsPrecise property of the COLUMNPROPERTY function. COLUMNPROPERTY returns 1 if TRUE, 0 if FALSE, and NULL for input that is not valid. This means the column is not deterministic or not precise.