Karteikarten
Querying SQL Server 2014
Karten in diesem Stapel (100)
- 1Query Hints: Optimize for Unknown1) Only statistics to be considered when compiling the query.
2) Prevents the query optimizer from creating an execution plan that is efficient for one set of values, but not for others. - 2Query Hints: Parameterization Forced1) Can only be used on a plan guide, not the actual query.
2) A plan guide created with the Parameterization Forced option causes the query optimizer to substitute literal values with paramters when optimizing the query. - 3Query Hints: Robust Plan1) Instructs the compiler to only select plans that can support the maximum possible row size that can be generated by the query.
2) Any plan that uses intermediate tables that increase the row size beyond the maximum supported row size will be disregarded. - 4Query Hints: Keepfixed Plan1) Prevents the query from being recompiled, even if the statistics of the underlying tables have changed.
2) Query will only be recompiled if the actual schema of the underlying tables is modified. - 5Cursors: Scroll1) Supports, Updates and Deletes and includes committed results in subsequent fetches.
2) All fetch options:
next. previous, relative and absolute;
are supported. - 6Cursors: Insensitive1) Data for insensitive cursor is written to a temporary table, so no changes are included in subsequent fetches.
- 7Cursors: Dynamic1) Includes changes made to underlying tables.
2) Does not support absolute fetches. - 8Cursors: Fast-Forward1) Read-only Cursor type.
- 9When implicit transactions are enabled and the transaction count is 0, what starts a new transaction?A new transaction is started when a supported data manipulation language (DML) statement is run. If an additional statement is run, it is added to the same transaction, until the transaction is either committed or rolled back.
- 10Which DML statements are supported within a transaction?INSERT
UPDATE
DELETE
TRUNCATE TABLE - 11DENSE _RANK() OVER (ValuesToBeRanked)Uses one up numbering for each group of identical values.
i.e. 1,1,2,2,2,3,3 - 12RANK() OVER (ValuesToBeRanked)Differs to DENSE_RANK in that it counts in numerical order for each group of values, i.e. if DENSE_RANK produced the values:
1,1,2,2,2,3,3
then RANK() would produce the values:
1,1,3,3,3,6,6 - 13ROW_NUMBER() OVER(PARTITION BY ValueToPartitionBy ORDER BY ValueToOrderBy ASC) AS NameOfColumnToOutputThisProduces the Row numbers of a set of values.
- 14What is the effect of using the WITH SCHEMABINDING option on a VIEW?The WITH SCHEMABINDING option prevents the underlying tables of a VIEW from being modified in any way that affects the VIEW definition, such as changing column names in the table.
You must use two-part object names for tables when using this option. - 15Which function has better insert performance: NEWSEQUENTIALID() or NEWID()The NEWSQUENTIALID() function offers better insert performance than the NEWID() function, because the NEWID() function causes random activity and requires more pages to be read from disk when performing an insert.
- 16A SYNONYM for a table can be used in which clause of a SELECT statement?A SYNONYM for a table can be used in the FROM clause of a SELECT statement.
- 17How can you optimize a query when you can't modify the query itself, when the query is within a stored procedure, function or trigger?You should run sp_create_plan_guide and set the type attribute to OBJECT. You can create a plan guide to optimize a query when you cannot modify the query itself. You set the type attribute to OBJECT when you need to optimize a query inside a stored procedure, function or trigger.
- 18How can you optimize a query, when you cannot modify the query itself, when the query is within a batch or a standalone statement?You should run sp_create_plan_guide and set the attribute
- 19ALTER TABLE TableName ADD CONSTRAINT fkForeignKeyName FOREIGN KEY (ColumnInReferencingTable) REFERENCES ReferencedTable(ReferencedColumn)Syntax for adding a Foreign Key Constraint
- 20Which Error Functions can be used inside of the CATCH clause to capture detailed information about an error?ERROR_LINE()
ERROR_MESSAGE()
ERROR_NUMBER()
ERROR_PROCEDURE()
ERROR_SEVERITY()
ERROR_STATE() - 21A TRY...CATCH construct cannot span multiple statement blocks? T or F?True. A TRY...CATCH construct cannot span multiple statement blocks. For example, it cannot include two or more BEGIN...END blocks. Nested transactions within a single statement block are supported.
- 22A TRY_CATCH construct can be nested? T of F?True. A TRY_CATCH construct can be nested. Each TRY clause must be immediately followed by a CATCH clause.
- 23A TRY...CATCH construct cannot trap syntax and compile errors. T or F?True. A TRY...CATCH construct cannot trap syntax and compile errors. It also cannot trap errors from a KILL command.
- 24What is the minimum error severity that can be trapped by a CATCH clause?Errors must have a severity greater than 10 to be trapped by the CATCH clause.
Errors with a severity of 10 or less are considered warnings.
Errors with a severity of 20 or greater that stop the SQL Server Database Engine task processing are not trapped.
Errors with a severity of 20 or greater are trapped as long as database connection is not disrupted. - 25CREATE TRIGGER TriggerName ON TableThatHasDataInsertedOrUpdated FOR INSERT, UPDATE AS INSERT INTO TableToHaveDataInsertedInto SELECT ColumnsThatYouWantToInsert FROM insertedYou use the CREATE TRIGGER statement to create a trigger. The first clause is the ON clause, which defines the table where the trigger is created. Next you need to define the type of query that will cause the trigger to run. In this case, you want the trigger to run as a result of an INSERT or UPDATE statement, so you use the FOR INSERT, UPDATE. This creates an AFTER trigger. Next, you use the AS keyword to indicate the body of the trigger is beginning. To meet the requirements of this example, you need to insert the rows in the inserted table into the table you want to be updated/have rows inserted into.
- 26PERCENTILE_DISC()PERCENTILE_DISC() returns a specific percentile for sorted values in a partition. The value returned is an actual value.
- 27PERCENTILE_CONT()PERCENTILE_CONT() returns a specific percentile value, but the value returned is calculated as not necessarily an actual value.
- 28CUME_DIST()CUME_DIST() returnes the cumulative distribution of a value over a partition and would return one row for each value.
- 29PERCENT_RANK()PERCENT_RANK() returns the relative rank of a row with the partition and would return one row for each value.
- 30Which transaction type is the default transaction mode, where each statement is an individual transaction with an implied BEGIN TRAN and COMMIT TRAN?Autocommit is the default transaction mode, where each statement is an individual transaction with an implied BEGIN TRAN and COMMIT TRAN.
- 31How do you start an implicit transaction?When you enable implicit transactions, a transaction is started when the transaction count is zero and you execute a DML statement. However, the transaction must be explicitly committed or rolled back.
- 32How do you initiate an explicit transaction?An explicit transaction is one that is initiated using the BEGIN TRANSACTION statement.
- 33Which type of transaction occurs when an implicit or explicit transaction is run in a multiple active result set (MARS) session?A batch-scope transaction occurs when an implicit or explicit transaction is run in a multiple active result set (MARS) session.
- 34What is a distributed transaction?A distributed transaction is one that affects multiple resources and all statements must succeed or fail as a group.
- 35How do you create a view where to improve performance, you persist the result of a view in a database?You must create an indexed view to view results stored in the database. To do this, you must use the WITH SCHEMABINDING option when you create the view. You must then create a clustered index on the view. You can create additional indexes on a view, but the first index you create on a view must be a clustered index.
- 36ALTER TABLE TableToHaveForeignKeyAdded ADD CONSTRAINT fkForeignKeyConstraintName FOREIGN KEY (ColumnNameInTableToBecomeFK) REFERENCES ReferencedTableName(ReferencedTableColumnName) ON DELETE SET NULLExample of an INSTEAD OF DELETE constraint. You can add a constraint using the ALTER TABLE statement by specifying the ADD CONSTRAINT clause and the constraint name. Next, you need to use the FOREIGN KEY keyword and identify the column in the referencing table. Next, you need to use the REFERENCES keyword and identify the referenced table, followed by the referenced column in parentheses. Finally, you need to specify the action that should occur if a referenced row is deleted. You can specify ON DELETE SET NULL to have the value in the column set to NULL.
- 37Which system objects can you use to find dependencies with other stored procedures?sys.dm_sql_referenced_entities ?
and
sys.sql_expression_dependencies ? - 38Example of a FOR XML AUTO Select Statement: SELECT PO_Header.PurchaseOrderID, [Status], ProductID, OrderQty, UnitPrice FROM Purchasing.PurchaseOrderHeader PO_Header JOIN Purchasing.PurchaseOrderDetail PO_Detail ON PO_Header.PurchaseOrderId = PO_Detail.PurchaseOrderId FOR XML AUTO Example FOR XML AUTO output:The FOR XML AUTO clause causes the data to be formatted with each table represented by an element and each column represented as an attribute. When used with a join, the table in the JOIN clause is a child element of the table in the FROM clause. When an alias is used for a table, the alias will be used as the name of the element.
- 39—
- 40—
- 41—
- 42—
- 43—
- 44—
- 45—
- 46—
- 47—
- 48—
- 49—
- 50—
- 51—
- 52—
- 53—DENSE_RANK()
- 54—
- 55—
- 56—
- 57—
- 58—
- 59—
- 60—
- 61—
- 62—
- 63—
- 64—
- 65—
- 66—
- 67—
- 68—
- 69—
- 70—
- 71—
- 72—
- 73—
- 74—
- 75—
- 76—
- 77—
- 78—
- 79—
- 80—
- 81—
- 82—
- 83—
- 84—
- 85—
- 86—
- 87—
- 88—
- 89—
- 90—
- 91—
- 92—
- 93—
- 94—
- 95—
- 96—
- 97—
- 98—
- 99—
- 100—
Informationen
- Beschreibung
- MCSA Digital Technologies Karteikarten am Querying SQL Server 2014, erstellt von Donna Atkins am 17/01/2018.
- Erstellt von
- Donna Atkins · Jan 2018
- Aktualisiert
- 8 Mär 2018
- Aufrufe
- 10
- Tags
- Bewertung