2336539
Descripción
Test por Dariusz Spiewak, actualizado hace más de 1 año
|
Creado por Dariusz Spiewak
hace alrededor de 9 años
|
|
Pregunta 1
Pregunta
What are the essential properties of transactions?
Respuesta
-
Atomicity
-
Durability
-
Consistency
-
Manageability
-
Isolation
-
Co-operativity
Pregunta 2
Pregunta
A database that's gone through how many normalization forms is usually considered "good enough"?
Respuesta
-
1
-
2
-
3
-
4
-
5
Pregunta 3
Pregunta
What are the benefits of stored procedures?
Respuesta
-
Execution plan retention/caching
-
Code reuse
-
Parameter sniffing
-
Improved security
Pregunta 4
Pregunta
What are the types of triggers?
Respuesta
-
INSTEAD OF
-
DELAYED
-
BEFORE
-
AFTER
Pregunta 5
Pregunta
Triggers take part in transactions.
Respuesta
- True
- False
Pregunta 6
Pregunta
Changes to table variables within a scope of a transaction get rolled back when the transaction is rolled back.
Respuesta
- True
- False
Pregunta 7
Pregunta
One should prefer using CHECK constraints to triggers.
Respuesta
- True
- False
Pregunta 8
Pregunta
DDL triggers are always AFTER triggers.
Respuesta
- True
- False
Pregunta 9
Pregunta
It's best practice to build views from other views. This promotes code reuse.
Respuesta
- True
- False
Pregunta 10
Pregunta
Some views can be used to update underlying tables.
Respuesta
- True
- False
Pregunta 11
Pregunta
Some views with aggregate functions in them can be used to update the underlying table(s).
Respuesta
- True
- False
Pregunta 12
Pregunta
An indexed view materializes data in the I/O subsystem and requires a clustered index.
Respuesta
- True
- False
Pregunta 13
Pregunta
Heaps are tables without a clustered index on them.
Respuesta
- True
- False
Pregunta 14
Pregunta
It's best practice to put clustered indexes on all tables, even the small ones.
Respuesta
- True
- False
Pregunta 15
Pregunta
The SCHEMABINDING option used with a view prevents schema changes to any underlying tables.
Respuesta
- True
- False
Pregunta 16
Pregunta
What is the main benefit of using an index on a table?
Respuesta
-
It prevents updating table statistics too frequently.
-
It stores the data in the table in an ordered state.
-
It makes writing queries easier.
-
It helps locate rows more quickly and efficiently.
Pregunta 17
Pregunta
For all intents and purposes, is a clustered index the same as the table itself?
Respuesta
-
YES
-
NO
Pregunta 18
Pregunta
You should always write queries in a way that takes advantage of set-based operations and avoid cursors.
Respuesta
- True
- False
Pregunta 19
Pregunta
A linked server configuration enables SQL Server to exec commands against an OLE DB data sources on remote servers.
Respuesta
- True
- False
Pregunta 20
Pregunta
A subquery can be used anywhere an expression can be used.
Respuesta
- True
- False
Pregunta 21
Pregunta
An INNER JOIN will always return at least one row of data.
Respuesta
- True
- False
Pregunta 22
Pregunta
It's best practice to always use LEFT OUTER JON instead of RIGHT OUTER JOIN and these two should not be used in the same query for the sake of clarity and ease of understanding.
Respuesta
- True
- False
Pregunta 23
Pregunta
If two tables, [A] with m rows and [B] with n rows, are CROSS JOINed, the result set has how many rows?
Respuesta
-
m + n - 1
-
m * n - 1
-
m * n
-
( m - 1 ) * ( n - 1 )
Pregunta 24
Pregunta
In a FULL OUTER JOIN all of the rows of all of the joined tables are included, whether they are matched or not.
Respuesta
- True
- False
Pregunta 25
Pregunta
If you SELF JON a table, you have to use table aliases.
Respuesta
- True
- False
Pregunta 26
Pregunta
What are the types of SQL user-defined functions?
Respuesta
-
Scalar
-
Inline table-valued
-
Procedural
-
Functional
-
Multi-statement table-valued
Pregunta 27
Pregunta
An inline table-valued function is the best type of function from the point of view of function performance.
Respuesta
- True
- False
Pregunta 28
Pregunta
Multi-statement table-valued functions can have side-effects (e.g., update a table).
Respuesta
- True
- False
Pregunta 29
Pregunta
You can insert data returned straight from a stored procedure by using the INSERT...EXEC statement.
Respuesta
- True
- False
Pregunta 30
Pregunta
An identity field can temporarily be disabled (for instance, during a data load) by using one of the SET directives.
Respuesta
- True
- False
Pregunta 31
Pregunta
When a table is truncated, an identity field is reset to its starting value.
Respuesta
- True
- False
Pregunta 32
Pregunta
When all the rows in a table are DELETEd, the identity column gets reset to its initial value.
Respuesta
- True
- False
Pregunta 33
Pregunta
Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table.
Respuesta
- True
- False
Pregunta 34
Pregunta
It is possible to change the length of a column defined as a PRIMARY KEY constraint without first deleting it and then re-creating it.
Respuesta
- True
- False
Pregunta 35
Pregunta
Which constraint enforces referential integrity?
Respuesta
-
UNIQUE KEY constraint
-
FOREIGN KEY constraint
-
CHECK constraint
-
TRIGGER
Pregunta 36
Pregunta
A CHECK constraint does not come into play if the relevant column receives a NULL value.
Respuesta
- True
- False
Pregunta 37
Pregunta
One should always prefer CHECK constraints to TRIGGERS.
Respuesta
- True
- False
Pregunta 38
Pregunta
The UNIQUE constraint is always implemented as a unique index on the relevant column(s).
Respuesta
- True
- False
Pregunta 39
Pregunta
What is the outcome of the expression under the default settings of SQL Server: SELECT ( 'a' + NULL ) ?
Respuesta
-
a
-
NULL
-
an error is returned
Pregunta 40
Pregunta
It is best practice not to use query/table hints in production code due to maintenance issues.
Respuesta
- True
- False
Pregunta 41
Pregunta
Which WHERE clause has the potential to benefit from an index?
Respuesta
-
WHERE Firstname LIKE '%N'
-
WHERE Firstname LIKE 'N%'
-
WHERE Firstname LIKE '%N%'
Pregunta 42
Pregunta
BCP lets one perform data imports and exports using a command-line utility.
Respuesta
- True
- False
Pregunta 43
Pregunta
Dirty reads can happen if you use the isolation level of READ COMMITTED.
Respuesta
- True
- False
Pregunta 44
Pregunta
The SERIALIZABLE isolation level is sufficient to ensure that no phantom rows appear within a transaction.
Respuesta
- True
- False
Pregunta 45
Pregunta
It is best practice to set FILLFACTOR to 100 (or 0) on an index in a read-only table.
Respuesta
- True
- False
Pregunta 46
Pregunta
The DENY permission takes precedence over all other permissions that a user might have for an object.
Respuesta
- True
- False
Pregunta 47
Pregunta
A lock can go from the row level to the page level.
Respuesta
- True
- False
Pregunta 48
Pregunta
The DELETE command has much more overhead in terms of log activity than TRUNCATE.
Respuesta
- True
- False
Pregunta 49
Pregunta
It is best practice to specify collation inline within a query.
Respuesta
- True
- False
Pregunta 50
Pregunta
The collation of a SQL Server can be changed without re-installation once the server has been installed.
Respuesta
- True
- False
Pregunta 51
Pregunta
The SNAPSHOT isolation level relies heavily on tempdb.
Respuesta
- True
- False
Pregunta 52
Pregunta
It is best practice to have a clustered index on a column against which range searches/scans are performed.
Respuesta
- True
- False
Pregunta 53
Pregunta
To improve performance of a view one can try to convert it into an indexed view.
Respuesta
- True
- False
Pregunta 54
Pregunta
A stale statistics can lead to the optimizer's not picking up an optimal query plan and underestimation of row counts.
Respuesta
- True
- False
Pregunta 55
Pregunta
Statistics can be created manually and automatically by SQL Server.
Respuesta
- True
- False
Pregunta 56
Pregunta
When a statistics is updated with the FULLSCAN option, it means that SQL Server will use data across all the rows to infer the distribution of values in the relevant column(s).
Respuesta
- True
- False
Pregunta 57
Pregunta
What is the outcome of the following query: SELECT 1 WHERE ( 1 = 0 )?
Respuesta
-
1
-
0
-
NULL
-
empty set (no rows)
Pregunta 58
Pregunta
Does the ORDER BY clause paired with TOP(100 PERCENT) used in a view guarantee the order of the returned rows?
Respuesta
-
YES
-
NO
Pregunta 59
Pregunta
If you use ORDER BY in a query, what you really get is not a relational set but a cursor.
Respuesta
- True
- False
Pregunta 60
Pregunta
It is best practice to use UNION ALL wherever possible instead of UNION due to performance reasons.
Respuesta
- True
- False
Pregunta 61
Pregunta
A deadlock can happen if two pieces of code access the same objects in reverse order at the same time.
Respuesta
- True
- False
Pregunta 62
Pregunta
Deadlock is a blocking situation but a blocking situation doesn't necessarily have to be a deadlock.
Respuesta
- True
- False
Pregunta 63
Pregunta
It is best practice to always use UNICODE data types (for textual values) regardless of whether the data comes from a UNICODE source or not.
Respuesta
- True
- False
Pregunta 64
Pregunta
It is best practice to always store integers as BIGINTs because this frees the developer from thinking about the smallest data type there could be to accommodate all the anticipated values.
Respuesta
- True
- False
Pregunta 65
Pregunta
The XML data type in SQL Server can be tied to an XML schema but this is not required.
Respuesta
- True
- False
Pregunta 66
Pregunta
The EXCEPT operator removes all duplicates from the result set and treats two NULL values as being equal.
Respuesta
- True
- False
Pregunta 67
Pregunta
In the new error-handling construct, TRY...CATCH, it is possible to use the FINALLY block known from other languages.
Respuesta
- True
- False
Pregunta 68
Pregunta
What does the SET XACT_ABORT ON; directive do?
Respuesta
-
It ensures that the isolation level of the code following it cannot be changed without raising an error.
-
It ensures that the developer must handle any rollbacks manually.
-
It ensures that a transaction is aborted without a rollback when an error is encountered and SQL Server raises error 825 (written to the error log).
-
It ensures that a transaction is automatically rolled back if an error occurs.
Pregunta 69
Pregunta
A table-valued variable can be used as an argument to a stored procedure only if it matches a user-defined table type and is marked as READONLY.
Respuesta
- True
- False
Pregunta 70
Pregunta
A subquery can contain an ORDER BY clause.
Respuesta
- True
- False
Pregunta 71
Pregunta
To return de-normalized data from a table/tables one can use the PIVOT operator.
Respuesta
- True
- False
Pregunta 72
Pregunta
To how many levels can you nest stored procedures (and managed code)?
Respuesta
-
16
-
32
-
64
-
limited only by the memory of the system
Pregunta 73
Pregunta
You can only have one PRIMARY filegroup per database but as many user-defined filegroups as you want.
Respuesta
- True
- False
Pregunta 74
Pregunta
It is best practice to only have one log file per database.
Respuesta
- True
- False
Pregunta 75
Pregunta
Data is written to a log file in a sequential manner.
Respuesta
- True
- False
Pregunta 76
Pregunta
Log files can be placed into filegroups in much the same way as data files can.
Respuesta
- True
- False
Pregunta 77
Pregunta
It is best practice to always qualify the name of any object by the containing schema because this saves SQL Server the time it needs to look for this object otherwise.
Respuesta
- True
- False
Pregunta 78
Pregunta
One can put a non-clustered index an a heap or on a clustered table.
Respuesta
- True
- False
Pregunta 79
Pregunta
PowerShell cannot be used to change the collation of a server.
Respuesta
- True
- False
Pregunta 80
Pregunta
PowerShell can execute any SQL query against a SQL Server database.
Respuesta
- True
- False
Pregunta 81
Pregunta
It is best practice to prefer using ORs to ANDs when building queries.
Respuesta
- True
- False
Pregunta 82
Pregunta
One can configure log-shipping in such a way that the database to which logs are being shipped is usable for reporting purposes.
Respuesta
- True
- False
Pregunta 83
Pregunta
What's the outcome of the following: IF ( SELECT 1/0 WHERE ( 1 = 0 ) ) IS NULL PRINT 'A' ELSE PRINT 'B'; ?
Respuesta
-
error is raised
-
B
-
A
Pregunta 84
Pregunta
Temporary tables can have indexes defined on them and SQL Server can create statistics on them when it deems it appropriate.
Respuesta
- True
- False
Pregunta 85
Pregunta
A table variable can be pushed out to tempdb.
Respuesta
- True
- False
Pregunta 86
Pregunta
Parallel query execution plans are always better in terms of performance than single-threaded plans.
Respuesta
- True
- False
Pregunta 87
Pregunta
SQL Server supports directly a many-to-many relationship between tables.
Respuesta
- True
- False
Pregunta 88
Pregunta
CHECKPOINTS truncate the log when the database is in the FULL recovery model.
Respuesta
- True
- False
Pregunta 89
Pregunta
SQL Server supports regular expressions out-of-the-box.
Respuesta
- True
- False
Pregunta 90
Pregunta
Cardinality issues stem from the fact that statistics are stale and they almost always lead to the optimizer choosing suboptimal query execution plan.
Respuesta
- True
- False
Pregunta 91
Pregunta
A non-clustered index and tempdb can be created on a separate (physical) drive to improve performance.
Respuesta
- True
- False
Pregunta 92
Pregunta
Which aggregate function(s) do(es) ignore NULLs?
Respuesta
-
AVG( [ColumnName] )
-
SUM( [ColumnName] )
-
COUNT(*)
-
COUNT( [ColumnName] )
-
MAX( [ColumnName] )
-
COUNT_BIG( [ColumnName] )
Pregunta 93
Pregunta
Do you have to take a database offline to change the physical location of one of its files?
Respuesta
-
YES
-
NO
Pregunta 94
Pregunta
Can we insert data into a table on which the clustered index has been disabled?
Respuesta
-
YES
-
NO
Pregunta 95
Pregunta
It is best practice to use the WITH RECOMPILE directive for a stored procedure that will be executed infrequently and the data distribution in the table(s) it uses is very different for different parameters.
Respuesta
- True
- False
Pregunta 96
Pregunta
Which is the preferred way of checking if a table is not empty:
1) ( SELECT COUNT(*) FROM dbo.Table ) > 0
2) EXISTS ( SELECT * FROM dbo.Table )
3) ( SELECT MAX( [ColumnName] FROM dbo.Table ) IS NOT NULL
Respuesta
-
1)
-
2)
-
3)
Pregunta 97
Pregunta
SELECT...INTO will never lock system objects.
Respuesta
- True
- False
Pregunta 98
Pregunta
Does the order of columns in UPDATE statements matter in any way?
Respuesta
-
NO
-
YES
Pregunta 99
Pregunta
What are the special memory resident tables available when dealing with triggers?
Respuesta
-
DELETED
-
UPDATED
-
INSERTED
Pregunta 100
Pregunta
User-defined functions can raise errors inside their bodies the same way stored procedures can by calling the RAISERROR function.
Respuesta
- True
- False
Pregunta 101
Pregunta
In which database is the metadata about SQL Server Agent jobs stored?
Respuesta
-
master
-
tempdb
-
msdb
-
resource
-
model
Pregunta 102
Pregunta
Which database(s) should a DBA back up on a regular schedule?
Respuesta
-
master
-
msdb
-
tempdb
Pregunta 103
Pregunta
The fastest way to improve a performance of a SELECT query is to add a covering index to the table(s).
Respuesta
- True
- False
Pregunta 104
Pregunta
Transactions can span multiple batches, that is, pieces of code separated by GO.
Respuesta
- True
- False
Pregunta 105
Pregunta
Activity Monitor lets you see in real-time the most expensive queries executing on the machine.
Respuesta
- True
- False
Pregunta 106
Pregunta
It is best practice not to use Common Table Expressions as they usually perform much worse than plain subqueries.
Respuesta
- True
- False
Pregunta 107
Pregunta
Which function returns data with greater resolution?
Respuesta
-
GETDATE
-
SYSDATETIME
Pregunta 108
Pregunta
Is it true that errors whose severity level is below 11 cannot be handled by the TRY...CATCH construct?
Respuesta
-
YES
-
NO
Pregunta 109
Pregunta
Can the TRY...CATCH block catch object name resolution errors?
Respuesta
-
NO
-
YES
Pregunta 110
Pregunta
Does SELECT...INTO transfer indexes to the new table?
Respuesta
-
NO
-
YES
Pregunta 111
Pregunta
Is it true that each and every user in a database is automatically a member of the public role?
Respuesta
-
YES
-
NO
Pregunta 112
Pregunta
Can the dbo user in a database be denied access to a table?
Respuesta
-
NO
-
YES
Pregunta 113
Pregunta
When data is deleted from a table, SQL Server immediately reduces the size of the table.
Respuesta
- True
- False
Pregunta 114
Pregunta
Which functions operate on the XML data type in SQL Server?
Respuesta
-
query()
-
value()
-
exists()
-
is()
-
modify()
-
nodes()
-
length()
-
datatype()
Pregunta 115
Pregunta
In which system database are usernames and passwords (if any) stored?
Respuesta
-
model
-
msdb
-
master
-
distribution
Pregunta 116
Pregunta
Which are good properties of a clustered index?
Respuesta
-
Static
-
Narrow
-
Ever-increasing
-
Random
Pregunta 117
Pregunta
It is best practice to shrink log files on a regular basis.
Respuesta
- True
- False
Pregunta 118
Pregunta
Only a log backup can clear the log of inactive transactions and make space inside it reusable when the recovery model of a database is FULL or BULK_LOGGED.
Respuesta
- True
- False
Pregunta 119
Pregunta
How many different database recovery models does SQL Server support?
Respuesta
-
1
-
2
-
3
-
4
-
19
Pregunta 120
Pregunta
Online index rebuilds are available in all editions of SQL Server except the Express one.
Respuesta
- True
- False
Pregunta 121
Pregunta
What's the outcome of the query: SELECT COALESCE(NULLIF(1, 1), 'A')?
Respuesta
-
A
-
NULL
-
conversion error
Pregunta 122
Pregunta
Which process is the CXPACKET wait type associated with?
Respuesta
-
Reading data from disk to cache
-
Executing parallel query plans
-
Flushing dirty pages to disk
-
Receiving asynchronous messages via SQL Server Broker
Pregunta 123
Pregunta
It is best practice to not create indexes on foreign key columns.
Respuesta
- True
- False
Pregunta 124
Pregunta
It is best practice not to give objects names that contain special characters or spaces.
Respuesta
- True
- False
Pregunta 125
Pregunta
It is best practice to turn off the AUTO CREATE and AUTO UPDATE of statistics and take care of them manually.
Respuesta
- True
- False
Pregunta 126
Pregunta
Which one is the preferred way of capturing the last inserted identity value in a session?
Respuesta
-
@@Indentity
-
SCOPE_IDENTITY()
¿Quieres crear tus propios Tests gratis con GoConqr? Más información.