C1-Create database programmability objects by using Transact-SQL

Question 1

What is an output parameter?

Answer 1

In SQL an output parameter is an stored procedure parameter that passes to the calling application or syntax.

With the OUTPUT statement: will bring values to a calling procedure.

Question 2

What does “With Check” option do?

Answer 2

Ensures that once the data has been updated it is still visible by the view thus preventing any update that does’nt meet the criteria of the SELECT statement;

Question 3

What is COLLATE command?

Answer 3

Collation is a set of rules that tell database engine how to compare and sort the character data in SQL Server.

Collation can be set at different levels in SQL Server. Below are the three levels:

• SQL Server level
• Database level
• Column level

Question 4

How does the COLLATE SQL_Latin1_General_CP1_CI_AS breakdown?

Answer 4

Break down of the collation setting is as below:

• SQL – All SQL Server collations will have the prefix SQL
• Latin1_General – represents the sort rule;
• CI means case insensitive;
• AS means accent sensitive

Question 5

What does “With schemabinding option do”?

Answer 5

prevent tables used in the views to make any modifications that can affect the view’s definition; however but does not stop the data from from being amended

Question 6

What is “WITH METADATA” option?

Answer 6

causes the SQL Server to return the view name when describing columns in the result set and hide the base tables from the client application. However it wont stop the data from being amended.

Question 7

What is SET TRANSACTION ISOLATION LEVEL?

Answer 7

Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. There are 5 types of ISOLATION LEVELS: READ UNCOMMITTED; READ COMMITTED; REPEATABLE READ; SNAPSHOT; SERIALIZABLE.

Important:

Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. Additionally, an update made at the READ_COMMITTED isolation level uses update locks on the data rows selected, whereas an update made at the SNAPSHOT isolation level uses row versions to select rows to update. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

Remarks:

• Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

Question 8

What is READ COMMITTED isolation level?

Answer 8

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

If READ_COMMITTED_SNAPSHOT is set to ON (the default on Azure SQL Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

Question 9

What is READ UNCOMMITTED isolation level?

Answer 9

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction.

READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

Question 10

SNAPSHOT-SET TRANSACTION ISOLATION LEVEL

Answer 10

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.

The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data.

A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

Question 11

What is REPEATABLE READ ISOLATION LEVEL?

Answer 11

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction.

Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.

Question 12

SET TRANSACTION ISOLATION LEVEL-SERIALIAZABLE

Answer 12

Specifies the following:

• Statements cannot read data that has been modified but not yet committed by other transactions.
• No other transactions can modify data that has been read by the current transaction until the current transaction completes.
• Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

​Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Question 13

What Can Stored Procedures Do?

Answer 13

• 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).
• Be much more resource efficent than other operations

Question 14

CREATE PROCEDURE remarks

Answer 14

• Stored procedure determine what operations users are allowed to perform on the underlying tables.
• Stored procedure simplify permissions on the database objects. This means that users do not need permissions to many database objects but only to access them using stored procedures.
• Because stored procedures store their execution plans they execute more efficiently than ad-hoc SELECT statements.
• Stored procedure cannot encrypt data in the underlying tables using the WITH ENCRYPTION option. WITH ENCRIPTION The WITH ENCRYPTION option obfuscates the text of the stored procedure, not the underlying data.

Question 15

What are the characteristics of parameters?

Answer 15

1. If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in;
2. Parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects;
3. Parameters cannot be declared if FOR REPLICATION is specified;
4. 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.

Question 16

What are VARYING, default, OUT|OUTPUT?

Answer 16

1. VARYING-Specifies the result set supported as an output parameter. This parameter is dynamically constructed by the procedure and its contents may vary. Applies only to cursor parameters;
2. default-A default value for a parameter. If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. The default value must be a constant or it can be NULL. The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.
3. OUT|OUTPUT-Indicates that the parameter is an output parameter. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

Question 17

What are READONLY, RECOMPILE, FOR REPLICATION?

Answer 17

1. READONLY-Indicates that the parameter cannot be updated or modified within the body of the procedure. If the parameter type is a table-value type, READONLY must be specified.
   
   • Table types can only be declared as input parameters must always be declared as READONLY
2. RECOMPILE-Indicates that the Database Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure.
3. FOR REPLICATION-Specifies that the procedure is created for replication. Consequently, it cannot be executed on the Subscriber. A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. Parameters cannot be declared if FOR REPLICATION is specified. FOR REPLICATION cannot be specified for CLR procedures. The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

Question 18

What is ATOMIC WITH?

Answer 18

Indicates atomic stored procedure execution. Changes are either committed or all of the changes rolled back by throwing an exception. The ATOMIC WITH block is required for natively compiled stored procedures. XACT_ABORT is ON by default inside an atomic block and cannot be changed. XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

The following SET options are always ON in the ATOMIC block; the options cannot be changed: CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ARITHABORT, NOCOUNT, ANSI_NULLS, ANSI_WARNINGS.

SET options cannot be changed inside ATOMIC blocks. The SET options in the user session are not used in the scope of natively compiled stored procedures. These options are fixed at compile time.

BEGIN, ROLLBACK, and COMMIT operations cannot be used inside an atomic block.

Question 19

What are NATIVE_COMPILATION, SCHEMABINDING, ENCRYPTION​?

Answer 19

NATIVE_COMPILATION-Indicates that the procedure is natively compiled- these allows faster data access and more efficient query execution than interpreted (traditional) Transact-SQL;

SCHEMABINDING-Ensures that tables that are referenced by a procedure cannot be dropped or altered. SCHEMABINDING is required in natively compiled stored procedures.The SCHEMABINDING restrictions are the same as they are for user-defined functions.

ENCRYPTION-Indicates that SQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. Users who have no access to system tables or database files cannot retrieve the obfuscated text. However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

Question 20

How can you return data from a stored procedure?

Answer 20

There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes.

Returning Data Using Result Sets-If you include a SELECT statement in the body of a stored procedure (but not a SELECT… INTO or INSERT… SELECT), the rows specified by the SELECT statement will be sent directly to the client.​

Returning Data Using an Output Parameter-If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the procedure.

Returning Data Using a Return Code-A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement. As with OUTPUT parameters, you must save the return code in a variable when the procedure is executed in order to use the return code value in the calling program.

For example, the assignment variable @result of data type int is used to store the return code from the procedure my_proc

Question 21

What does the CREATE FUNCTION statement does?

Answer 21

Creates a user-defined function in SQL Server and Azure SQL Database. A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.

Question 22

What are some of the best practices of functions?

Answer 22

If a user-defined function 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. We recommend that you 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 function. 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 function.

Including SCHEMABINDING in the function will also make that function deterministic.

Question 23

What is the syntax for scalar T-SQL and CLR types of tables?

Answer 23

CREATE [OR ALTER] FUNCTION [schema_name.] function_name

( [{ @parameter_name [ AS][type_schema_name.] parameter_data_type [= default] [READONLY] } [,…n] ] )

RETURNS return_data_type [WITH [ ,…n] ] [AS] BEGIN function_body RETURN scalar_expression END;

CREATE [OR ALTER] FUNCTION [schema_name.] function_name

( { @parameter_name [AS] [type_schema_name.] parameter_data_type [= default] } [,…n] )

RETURNS { return_data_type } [WITH [ ,…n] ] [AS] EXTERNAL NAME [;]

Question 24

What is the Transact-SQL syntax for Inline Table-Valued and Multi-Statement Tabled Functions?

Answer 24

Table-Valued Function:

CREATE [OR ALTER] FUNCTION [schema_name.] function_name

( [{ @parameter_name [ AS] [type_schema_name.]

parameter_data_type [= default] [READONLY] } [,…n] ] )

RETURNS return_data_type [WITH [ ,…n] ] [AS] BEGIN function_body RETURN scalar_expression END [;]

Transact-SQL Multi-Statement Table-Valued Function Syntax:

CREATE [OR ALTER] FUNCTION [schema_name.] function_name

( [{ @parameter_name [ AS] [type_schema_name.]

parameter_data_type [= default] [READONLY] } [,…n] ] )

RETURNS @return_variable TABLE [WITH [ ,…n] ] [AS] BEGIN function_body RETURN END [;]

Question 25

Which is the most efficient type of function?

Answer 25

Althouth the inline Tabled-Valued function has the table data type it is the most efficient type of function better performing than Multi-Statement Table-Valued function and the CLR Tabled valued function.

Question 26

What are some the Limitiations and restrictions of User Defined Functions?

Answer 26

• 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.
• SET statements are not allowed in a user-defined function
• The FOR XML clause is not allowed
• UDF can be nested up to 32 times;

Question 27

What are user defined functions, why are they used for ?

Answer 27

• DEF: Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set
• Are used for:
  
  • ​They allow modular programming;
  • They allow faster execution;
  • They can reduce network traffic
• Remarks:
  
  • ​If you do not need to pass a parameter you should not use a user defined function

Question 28

What are the types of user defined functions?

Answer 28

Scalar Function:
User-defined scalar functions return a single data value of the type defined in the RETURNS clause. For an inline scalar function, the returned scalar value is the result of a single statement. For a multistatement scalar function, the function body can contain a series of Transact-SQL statements that return the single value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Table-Valued Functions:

User-defined table-valued functions return a table data type. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement.

System Functions​:

SQL Server provides many system functions that you can use to perform a variety of operations. They cannot be modified.

Question 29

Why should you be careful about scalar functions?

Answer 29

You need to be careful how you use a scalar function in your T-SQL statements. Scalar value functions when used in a column list, or WHERE clause perform much like a cursor and are called repeatedly to resolve the query. These repeative evalutions of the function code makes scalar value functions inefficient when used this way. If you are using scalar value functions in your column list or WHERE clause then you should consider re-writing you queries to use one of the methods I demonstrated in this article

Question 30

What are some of the restrictions of tabled valued functions?

Answer 30

You cannot return multiple result sets from a table-valued function. The results of a table-valued function are a single result set that is returned

Question 31

When should we use tabled valued functions?

Answer 31

• Tabled valued functions can be used in place of a table or views in queries;
• Tabled valued functions are a good choice when you have the same T-SQL code but you need to pass one or more parameters. The query optimizer is able to handle table-valued functions efficiently.

Question 32

What are Table-Valued User-Defined Functions?

Answer 32

User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.

Question 33

What are the components of Table-Valued User defined Function?

Answer 33

• The RETURNS clause defines a local return variable name for the table returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function.
• The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.
• When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument.

​No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.

Question 34

What is ROW_NUMBER and what is it’s syntax?

Answer 34

DEF: Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

SYNTAX: ROW_NUMBER ( ) OVER ( [PARTITION BY value_expression , … [ n] ] order_by_clause )

Remarks:

There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true.

1. Values of the partitioned column are unique.
2. Values of the ORDER BY columns are unique.
3. Combinations of values of the partition column and ORDER BY columns are unique.

ROW_NUMBER() is nondeterministic.

You cannot use ROW_NUMBER with a WHERE clause;

Question 35

What is RANK and what is it’s syntax?

Answer 35

DEF:

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.

ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5)

syntax: RANK ( ) OVER ( [partition_by_clause] order_by_clause )

Remarks:

If two or more rows tie for a rank, each tied row receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

The sort order that is used for the whole query determines the order in which the rows appear in a result set.

RANK is nondeterministic.

Question 36

Views def

Answer 36

A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.

A view acts as a filter on the underlying tables referenced in the view. The query that defines the view can be from one or more tables or from other views in the current or other databases. Distributed queries can also be used to define views that use data from multiple heterogeneous sources. This is useful, for example, if you want to combine similarly structured data from different servers, each of which stores data for a different region of your organization.

Views are generally used to focus, simplify, and customize the perception each user has of the database. Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view. Views can be used to provide a backward compatible interface to emulate a table that used to exist but whose schema has changed. Views can also be used when you copy data to and from SQL Server to improve performance and to partition data.

Question 37

CREATE VIEW (Transcat-SQL) def and syntax

Answer 37

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.

syntax:

CREATE [OR ALTER] VIEW

[schema_name .] view_name [(column [ ,…n] ) ]

[WITH [ ,…n] ]

AS select_statement

[WITH CHECK OPTION] [;]

::=

{ [ENCRYPTION] [SCHEMABINDING] [VIEW_METADATA] }

Question 38

Special Types of Views

Answer 38

Indexed Views:

An indexed view is a view that has been materialized. This means the view definition has been computed and the resulting data stored just like a table. You index a view by creating a unique clustered index on it. Indexed views can dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.

Partitioned Views
A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. This makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.

System Views

System views expose catalog metadata. You can use system views to return information about the instance of SQL Server or the objects defined in the instance. For example, you can query the sys.databases catalog view to return information about the user-defined databases available in the instance

Question 39

Create View (Remarks)

Answer 39

• ​A view can insert a record into a table but with high restrictions;
• A view can be used in different places in the system, and the query optimizer expands views, so they run efficiently
• 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;
• A query that uses an index on a view defined with numeric or float expressions may have a result that is different from a similar query that does not use the index on the view. This difference may be caused by rounding errors during INSERT, DELETE, or UPDATE actions on underlying tables.

Question 40

CREATE VIEWS (Updatable Views)

Answer 40

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;

​The previous restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself. Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table.

If the previous restrictions prevent you from modifying data directly through a view, consider the following options: INSTEAD OF Triggers or Partitioned Views

​

Question 41

CREATE VIEWS (Partitioned Views)

Answer 41

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.

In designing a partitioning scheme, it must be clear what data belongs to each partition.

Question 42

Create Indexed Views (Steps before you begin)

Answer 42

This article describes how to create indexes on a view. The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create more nonclustered indexes. Creating a unique clustered index on a view improves query performance because 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.

The following steps are required 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.

Question 43

CREATE INDEXED VIEWS (required SET options)

Answer 43

Evaluating the same expression can produce different results in the Database Engine when different SET options are active when the query is executed. For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression ‘abc’ + NULL returns the value NULL. However, after CONCAT_NULL_YIELDS_NULL is set to OFF, the same expression produces ‘abc’.

To make sure that the views can be maintained correctly and return consistent results, indexed views require fixed values for several SET options. The SET options in the following table must be set to the values shown in the Required Value column whenever the following conditions occur:

• The view and subsequent indexes on the view are created.
• The base tables referenced in the view at the time the view is created.
• There is any insert, update, or delete operation performed on any table that participates in the indexed view. This requirement includes operations such as bulk copy, replication, and distributed queries.
• The indexed view is used by the query optimizer to produce the query plan.

Question 44

CREATE INDEXED VIEWS (Deterministic Views)

Answer 44

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.

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.

Question 45

CREATE INDEXED VIEWS (Additional Requirements)

Answer 45

In addition to the SET options and deterministic function requirements, the following requirements must be met:

• The user that executes CREATE INDEX must be the owner of the view.
• When you create the index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).
• Tables must be referenced by two-part names, schema.tablename in the view definition.
• User-defined functions referenced in the view must be created by using the WITH SCHEMABINDING option.
• Any user-defined functions referenced in the view must be referenced by two-part names, ..
• The view must be created by using the WITH SCHEMABINDING option
• The view must reference only base tables that are in the same database as the view. The view cannot reference other views;
• If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions;
• If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause;

Question 46

CREATE INDEX def

Answer 46

Creates a relational index on a table or view. Also called a rowstore index because it is either a clustered or nonclustered B-tree index. You can create a rowstore index before there is data in the table. Use a rowstore index to improve query performance, especially when the queries select from specific columns or require values to be sorted in a particular order.

Starting with SQL Server 2016 (13.x) and SQL Database, use a nonclustered index on a columnstore index to improve data warehousing query performance. For more information, see Columnstore Indexes - Data Warehouse.

Question 47

CREATE INDEX syntax

Answer 47

– Create a nonclustered index on a table or view:

CREATE INDEX i1 ON t1 (col1);

– Create a clustered index on a table and use a 3-part name for the table: CREATE CLUSTERED INDEX i1 ON d1.s1.t1 (col1);

– Syntax for SQL Server and Azure SQL Database

– Create a nonclustered index with a unique constraint

– on 3 columns and specify the sort order for each column

CREATE UNIQUE INDEX i1 ON t1 (col1 DESC, col2 ASC, col3 DESC);

Question 48

Which are the Guidlines of Designing an Indexed View?

Answer 48

def:Indexed views work best when the underlying data is infrequently updated. The maintenance of an indexed view can be greater than the cost of maintaining a table index. If the underlying data is updated frequently, the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view. If the underlying data is updated periodically in batches but treated primarily as read-only between updates, consider dropping any indexed views before updating, and rebuilding them afterward. Doing this may improve performance of the updates.

Indexed views improve the performance of the following types of queries:

• Joins and aggregations that process many rows;
• Join and aggregation operations that are frequently performed by many queries;
• Decision support workloads.

Indexed views typically do not improve the performance of the following types of queries:

• OLTP systems that have many writes;
• Databases that have many updates;
• Queries that do not involve aggregations or joins.
• Expanding joins. These are views that have result sets that are larger than the original data in the base tables.
• Aggregations of data with a high degree of cardinality for the GROUP BY key.

Question 49

Creating and Modifying Table Basics (Table Properties)

Answer 49

You can define up to 1,024 columns per table. Table and column names must follow the rules for identifiers; they must be unique within a specific table, but you can use the same column name in different tables in the same database.

Although table names must be unique for each schema within a database, you can create multiple tables with the same name if you specify different schemas for each. You can create two tables named employees and designate Jonah as the schema of one and Sally as the schema of the other. When you have to work with one of the employees tables, you can distinguish between the two tables by specifying the schema with the name of the table.

Question 50

Creating and Modifying Table Basics (Temporary Tables)

Answer 50

Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when they are no longer used.

There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability.

• Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server.
• Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.

​Remarks:Many uses of temporary tables can be replaced with variables that have the table data type.

​

Question 51

Creating and Modifying Table Basics (Modifying Tables)

Answer 51

After a table is created, you can change many of the options that were defined for the table when it was originally created. These can include the following:

• Columns can be added, modified, or deleted. For example, the column name, length, data type, precision, scale, and nullability can all be changed, although some restrictions exist. For more information, see Modifying Column Properties.
• If the table is a partitioned table, it can be repartitioned, or individual partitions can be added or removed.
• PRIMARY KEY and FOREIGN KEY constraints can be added or deleted.
• UNIQUE and CHECK constraints and DEFAULT definitions, and objects, can be added or deleted.
• An identifier column can be added or deleted by using the IDENTITY or ROWGUIDCOL property. The ROWGUIDCOL property can also be added to or removed from an existing column, although only one column in a table can have the ROWGUIDCOL property at a time.
• A table and selected columns within the table can be registered for full-text indexing.

Question 52

EXECUTE AS def, syntax

Answer 52

In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.

By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module.

syntax:

{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | ‘user_name’ } DDL Triggers with Database Scope { EXEC | EXECUTE } AS { CALLER | SELF | ‘user_name’ }

Question 53

Execute as syntax, arguments

Answer 53

syntax:

{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | ‘user_name’ } DDL Triggers with Database Scope { EXEC | EXECUTE } AS { CALLER | SELF | ‘user_name’ }

arguments:

**CALLER**
Specifies the statements inside the module are executed in the context of the caller of the module. The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module.

CALLER is the default for all modules except queues, and is the same as SQL Server 2005 (9.x) behavior.

CALLER cannot be specified in a CREATE QUEUE or ALTER QUEUE statement.

SELF
EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the specified user is the person creating or altering the module. The actual user ID of the person creating or modifying the modules is stored in the execute_as_principal_id column in the sys.sql_modules or sys.service_queues catalog view.

SELF is the default for queues.

**OWNER**
Specifies the statements inside the module executes in the context of the current owner of the module. If the module does not have a specified owner, the owner of the schema of the module is used. OWNER cannot be specified for DDL or logon triggers.

• OWNER must map to a singleton account and cannot be a role or group.

Question 54

EXECUTE AS remarks

Answer 54

How the Database Engine evaluates permissions on the objects that are referenced in the module depends on the ownership chain that exists between calling objects and referenced objects. In earlier versions of SQL Server, ownership chaining was the only method available to avoid having to grant the calling user access to all referenced objects.

Ownership chaining has the following limitations:

• Applies only to DML statements: SELECT, INSERT, UPDATE, and DELETE.
• The owners of the calling and the called objects must be the same.
• Does not apply to dynamic queries inside the module.

​Regardless of the execution context that is specified in the module, the following actions always apply:

• When the module is executed, the Database Engine first verifies that the user executing the module has EXECUTE permission on the module.
• Ownership chaining rules continue to apply. This means if the owners of the calling and called objects are the same, no permissions are checked on the underlying objects.

​