122 Multiple choice questions
- Can be issued against character-based data in a table when there is a full-text index on that table.
- Supports nesting in the resulting XML.
WITH CUBE or WITH ROLLUP
to create and delete databases/tables, define table rows and columns,
create indexes and take other actions that change the structure of the
- Use ____ to return a ranking value for each row within a ____. They are nondeterministic.
- Specifies that the DML trigger can be fired only when all operations in the triggering statement are successfully completed.
- Used to specify the number of rows to return as soon as the OFFSET is processed.
- Computes the average of all values in a numeric expression.
- Used to determine the partitioning and ordering of a rowset before allowing the associated window function to be applied.
- Used to return the product but not the sum of the two sources. Returns the set of all possible row combinations.
- You ____ run TRUNCATE TABLE on tables that participate in an ____ and they cannot ____ any trigger.
that can be used to specify a value less than or equal to the maximum
and greater than or equal to the minimum value specified.
to ensure that no changes to the underlying data can be made through
the view that would cause the data to not conform to the definition. Can
also be used to ensure the data will remain visible through the view
when modified through the view.
- The DDL trigger is for applying to the entire database.
to specify that the DML trigger is to be executed instead of the
triggering statement. Cannot be used for DDL or logon triggers.
- Used to ensure that the base table cannot be modified to affect the view definition.
- Used to generate subtotals in a result set. ntext, text and image cannot be dealt with through these.
- Used to invoke when a user modifies data through DML.
- Used to retrieve all the rows from both sides where the join condition is evaluated to true.
- If CREATE TRIGGER is part of a ____, it must be the ____ statement.
- Used to specify either the BROWSE or the XML option.
- The DDL/logon trigger is to be applied to the current server.
- Produces the total of all values in a numeric expression.
- Members of the ____/db_owner fixed database roles or the ____ fixed server fol can always run ____ at wish.
- Used in a regular SELECT and WHERE class to find a specified pattern in a column.
- All referential ____ actions and ____ checks must succeed before a DML trigger fires.
- Gives the lowest value in an expression
can use ____ only with a ____ statement. May want to use it inside a
GROUP BY clause, and text, image and ntext's cannot be used.
- For explicitly specifying the security context under which the current trigger fires.
- Used to order the result set of a query and at the same time limit the rows that are returned.
- Used to join a table/view to itself.
- Rowset-valued function that is referenced like a table for using CONTAINS type search.
____ to calculate on a set of values in order to return a ____ value.
They generate summary values in query results set. Other than COUNT they
will ____ null values by default. They are all deterministic.
- Only a specified first set or percent of rows can be returned.
will be transferred to the associated CATCH block. Can provide
information on the error that caused the CATCH block to run.
- You can create a DDL trigger with ____ scope if you have ____ DDL TRIGGER permission in the current database.
- Used to tell the sequence object if the sequence should restart. Default is NO.
- Rowset-valued function that is referenced like a table for using FREETEXT type search.
- Used to specify that updates be allowed when you view data under a DB-Library browse mode cursor.
- There are three types of outer joins: ____, ____, and ____.
for telling the windows function to be applied to each partition
separately and that computation will be restarted for each partition.
- Used to retrieve data from two or more tables or views.
- Used to combine rows from two sources where all of the left side rows will be included.
- Used to set NO_BROWSETABLE to function.
alternative to RAISERROR. Can raise an exception and transfer execution
to CATCH within TRY...CATCH block. Will cause a statement batch to end.
- Used to create a new table in the default filegroup with the insertion of the resulting rows from the query into it.
- There is a maximum of ____ of nesting possible with subqueries.
- Block of code used for processing errors in T-SQL code.
- Specified in the WHERE or HAVING clauses. Used for precise or fuzzy matches.
- The window will start at the first row of the partition.
- Gives the highest value in an expression
- Used to specify lower bounds for the sequence object.
- Used to fine-tune error-handling. Can have multiple severities that will handle the errors differently.
- Used to prevent the trigger from being published during replication.
- Used to specify the number of rows to be skipped.
- The highest secured communication mode you can use in a non-clustered setup.
- You can create a DDL trigger with ____ scope or a ____ trigger if you have ____ permission on the server.
- Only unique rows can appear in the result set.
- Can be inherited from the schema permissions or CONTROL permissions on a table.
- Used to produce a new date/time value which is based on adding an interval to the concerned date.
- Used to delete all rows from a table without logging the individual row deletions.
- A user-defined schema bound object for generating a sequence of numeric values.
- Gives the exact number of selected rows
- Generates a single <row> element per row in the returned rowset.
- Used to specify that operation should be performed following a different collation.
- Gives more control over how the XML results are formatted.
- You can create a ____ trigger if you have ____ permission on the table or view it will be created on.
- Specified in the WHERE or HAVING clauses. Used to match the meaning of words, phrases and strings.
triggers that fire primarily in response to the LOGON event, which is
raised only when a user session is getting established.
- A new view can have a maximum of ____ columns.
server service that can coordinate transactions that update multiple
transaction protected resources on a single server computer or
distributed across networked server computers.
- Used to specify a value increment or decrement in the sequence object.
- Subqueries can have an ____ clause when a ____ clause is also used.
- An inner select, which is always enclosed in parentheses.
- Cannot have tables joined ____ on ntext, test, or image columns. However, this can be done ____ using ____.
- Used to produce and integer that forms a specified part of a date.
- Will only return an informational message to the batch without invoking CATCH.
to combine the results of multiple queries into a single result set
which includes all the rows that belong to all queries participating.
Removes duplicates unless ALL is included.
- You ____ run TRUNCATE TABLE on tables that are referenced by a ____ constraint unless it is referencing ____.
- Argument that can be used to specify multiple groupings of data in a single query.
- To perform multi-statement transactions you need a ____ at the beginning and a ____ at the end.
- Users with the ____ and the ____ fixed database roles may create, alter and drop sequence objects.
____ to compute an aggregate value based on a group of row. It is
possible for them to return more than one row for each group. Use them
to ____ things like moving averages, percentages, etc.
- You ____ use SELECT....INTO to create a ____ table.
- Will directly close the database connection without invoking CATCH.
- Used to retrieve the current date and time in SQL Server.
- Used for specifying search conditions for a group or an aggregate.
- Where the error can be dealt with. Once dealt with control will be transferred to the first statement after END CATCH.
- Results of the query will be returned as an XML document.
- ____ INSTEAD OF trigger per ____, UPDATE or ____ statement on a table/view.
- Used to produce the number of date/time boundaries that get crossed between two different dates.
- It is recommended whenever you use the ____ statement you use the ____ clause to indicate the rows that are to be affected.
- Used to return all rows from the joined tables/views even where there is no matching row between them.
- Used to combine rows from two sources where all of the right side rows will be included.
- Users with the ____ and ____ fixed database roles may also update sequence objects.
- Subqueries cannot have any ____ or ____ clauses.
- CREATE TRIGGER can apply to ____ table in the current database, though it can ____ objects in another.
- Used to specify upper bounds for the sequence object.
- Inner joins can be specified in the ____ clause or the ____ clause. Outer joins can be specified only in the ____ clause.
- Disable nested triggers by setting the ____ triggers options of ____ to a value of 0.
- Used to group a selected set of rows into a set of summary rows by the values of columns or expressions.
- A sequence of operations performed as a single logical unit of work.
- A ____ is ____ to any table.
TABLE ____ are granted to the ____ owner, the members of the ____ fixed
server role and the db_owner/____ fixed database roles.
- Used to create a trigger, which is a special kind of stored procedure that will automatically run when an event takes place.
- You can create a view only in the ____ database and CREATE VIEW must be the ____ in the query.
that every piece of work in the transaction is an integral part of a
single unit. All works within the transaction much complete for it to be
considered as committed.
- Used to undo all of the work since the last BEGIN TRAN statement.
- Cannot be used in SELECT statements joined by the UNION operator.
- Option to turn on so that all the SELECT statements will behave as if the FOR BROWSE option has been appended to the statements.
- Used to create a sequence.
- You should not use the ____ aggregate in a subquery for ____ an existence check.
- By default triggers can be ____ up to a maximum of ___ levels.
- Used to run queries and add, delete or edit records.
- Used to create a view.
- Used to combine and add records from two sources to a query's results when the values can meet the criteria defined.
that can be used to generate the simple GROUP BY aggregate rows as well
as the subtotal/super-aggregate rows and also a grand total row.