Long-Term Learning
Learn efficiently and remember over time.
Start Long-Term Learning
Add this set to a folder
Data definition language (DDL)
Used to create and delete databases/tables, define table rows and columns, create indexes and take other actions that change the structure of the database.
db_datareader, sysadmin, SELECT
Members of the ____/db_owner fixed database roles or the ____ fixed server fol can always run ____ at wish.
Logical processing order
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP
SELECT...INTO
Used to create a new table in the default filegroup with the insertion of the resulting rows from the query into it.
COMPUTE
Used to generate subtotals in a result set. ntext, text and image cannot be dealt with through these.
COUNT(), performing
You should not use the ____ aggregate in a subquery for ____ an existence check.
BROWSE
Used to specify that updates be allowed when you view data under a DB-Library browse mode cursor.
NO_BROWSETABLE
Option to turn on so that all the SELECT statements will behave as if the FOR BROWSE option has been appended to the statements.
GROUP BY
Used to group a selected set of rows into a set of summary rows by the values of columns or expressions.
ROLLUP()
Argument 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.
HAVING, SELECT
You 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.
ORDER BY
Used to order the result set of a query and at the same time limit the rows that are returned.
SELECT TOP, ORDER BY
It is recommended whenever you use the ____ statement you use the ____ clause to indicate the rows that are to be affected.
OVER
Used to determine the partitioning and ordering of a rowset before allowing the associated window function to be applied.
PARTITION BY
Argument for telling the windows function to be applied to each partition separately and that computation will be restarted for each partition.
Ranking functions, partition
Use ____ to return a ranking value for each row within a ____. They are nondeterministic.
Aggregate functions
AVG
MIN
CHECKSUM_AGG
SUM
COUNT
STDEV
COUNT_BIG
STDEVP
GROUPING
VAR
GROUPING_ID
VARP
MAX
Aggregate functions, single, ignore
Use ____ 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.
Analytic functions
CUME_DIST
LEAD
FIRST_VALUE
PERCENTILE_CONT
LAG
PERCENTILE_DISC
LAST_VALUE
PERCENT_RANK
Analytic functions, compute things
Use ____ 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.
DATEADD
Used to produce a new date/time value which is based on adding an interval to the concerned date.
DATEDIFF
Used to produce the number of date/time boundaries that get crossed between two different dates.
Permissions, table, sysadmin, db_ddladmin
TRUNCATE TABLE ____ are granted to the ____ owner, the members of the ____ fixed server role and the db_owner/____ fixed database roles.
Cannot, FOREIGN KEY, itself
You ____ run TRUNCATE TABLE on tables that are referenced by a ____ constraint unless it is referencing ____.
Cannot, indexed view, fire
You ____ run TRUNCATE TABLE on tables that participate in an ____ and they cannot ____ any trigger.
CREATE TRIGGER
Used to create a trigger, which is a special kind of stored procedure that will automatically run when an event takes place.
Only one, reference
CREATE TRIGGER can apply to ____ table in the current database, though it can ____ objects in another.
Logon triggers
Special triggers that fire primarily in response to the LOGON event, which is raised only when a user session is getting established.
EXECUTE AS argument
For explicitly specifying the security context under which the current trigger fires.
AFTER argument
Specifies that the DML trigger can be fired only when all operations in the triggering statement are successfully completed.
Cascade, Constraint
All referential ____ actions and ____ checks must succeed before a DML trigger fires.
INSTEAD OF argument
Used to specify that the DML trigger is to be executed instead of the triggering statement. Cannot be used for DDL or logon triggers.
Only one, INSERT, DELETE
____ INSTEAD OF trigger per ____, UPDATE or ____ statement on a table/view.
Nested, sp_configure
Disable nested triggers by setting the ____ triggers options of ____ to a value of 0.
DML, ALTER
You can create a ____ trigger if you have ____ permission on the table or view it will be created on.
Server, logon, CONTROL SERVER
You can create a DDL trigger with ____ scope or a ____ trigger if you have ____ permission on the server.
Database, ALTER ANY DATABASE
You can create a DDL trigger with ____ scope if you have ____ DDL TRIGGER permission in the current database.
Inner join
Used to combine and add records from two sources to a query's results when the values can meet the criteria defined.
Outer join
Used to return all rows from the joined tables/views even where there is no matching row between them.
FROM, WHERE, FROM
Inner joins can be specified in the ____ clause or the ____ clause. Outer joins can be specified only in the ____ clause.
Left outer join
Used to combine rows from two sources where all of the left side rows will be included.
Right outer join
Used to combine rows from two sources where all of the right side rows will be included.
Full outer join
Used to retrieve all the rows from both sides where the join condition is evaluated to true.
Cross join
Used to return the product but not the sum of the two sources. Returns the set of all possible row combinations.
Directly, indirectly, SUBSTRING
Cannot have tables joined ____ on ntext, test, or image columns. However, this can be done ____ using ____.
UNION
Used 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.
WITH CHECK OPTION
Used 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.
Current, first statement
You can create a view only in the ____ database and CREATE VIEW must be the ____ in the query.
db_owner, db_ddladmin
Users with the ____ and the ____ fixed database roles may create, alter and drop sequence objects.
db_owner, db_datawriter
Users with the ____ and ____ fixed database roles may also update sequence objects.
START WITH
Option 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.
Full-text queries
Can be issued against character-based data in a table when there is a full-text index on that table.
FREETEXT
Specified in the WHERE or HAVING clauses. Used to match the meaning of words, phrases and strings.
CONTAINSTABLE
Rowset-valued function that is referenced like a table for using CONTAINS type search.
FREETEXTTABLE
Rowset-valued function that is referenced like a table for using FREETEXT type search.
Atomic
Means 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.
Distributed Transaction Coordinator (DTC)
Windows server service that can coordinate transactions that update multiple transaction protected resources on a single server computer or distributed across networked server computers.
Mutual Authentication Required
The highest secured communication mode you can use in a non-clustered setup.
BEGIN TRAN, COMMIT TRAN
To perform multi-statement transactions you need a ____ at the beginning and a ____ at the end.
CATCH
Where the error can be dealt with. Once dealt with control will be transferred to the first statement after END CATCH.
RAISERROR
Used to fine-tune error-handling. Can have multiple severities that will handle the errors differently.
Severity 11 to 19
Control will be transferred to the associated CATCH block. Can provide information on the error that caused the CATCH block to run.