Kopieren und bearbeiten

T-SQL Dev Job

Beschreibung

Professional SQL Server Quiz am T-SQL Dev Job, erstellt von Dariusz Spiewak am 22/03/2015.
Dariusz Spiewak
Quiz von Dariusz Spiewak, aktualisiert more than 1 year ago
Dariusz Spiewak
Erstellt von Dariusz Spiewak vor mehr als 9 Jahre
23
1
0

Zusammenfassung der Ressource

Frage 1

Frage
What are the essential properties of transactions?
Antworten
  • Atomicity
  • Durability
  • Consistency
  • Manageability
  • Isolation
  • Co-operativity

Frage 2

Frage
A database that's gone through how many normalization forms is usually considered "good enough"?
Antworten
  • 1
  • 2
  • 3
  • 4
  • 5

Frage 3

Frage
What are the benefits of stored procedures?
Antworten
  • Execution plan retention/caching
  • Code reuse
  • Parameter sniffing
  • Improved security

Frage 4

Frage
What are the types of triggers?
Antworten
  • INSTEAD OF
  • DELAYED
  • BEFORE
  • AFTER

Frage 5

Frage
Triggers take part in transactions.
Antworten
  • True
  • False

Frage 6

Frage
Changes to table variables within a scope of a transaction get rolled back when the transaction is rolled back.
Antworten
  • True
  • False

Frage 7

Frage
One should prefer using CHECK constraints to triggers.
Antworten
  • True
  • False

Frage 8

Frage
DDL triggers are always AFTER triggers.
Antworten
  • True
  • False

Frage 9

Frage
It's best practice to build views from other views. This promotes code reuse.
Antworten
  • True
  • False

Frage 10

Frage
Some views can be used to update underlying tables.
Antworten
  • True
  • False

Frage 11

Frage
Some views with aggregate functions in them can be used to update the underlying table(s).
Antworten
  • True
  • False

Frage 12

Frage
An indexed view materializes data in the I/O subsystem and requires a clustered index.
Antworten
  • True
  • False

Frage 13

Frage
Heaps are tables without a clustered index on them.
Antworten
  • True
  • False

Frage 14

Frage
It's best practice to put clustered indexes on all tables, even the small ones.
Antworten
  • True
  • False

Frage 15

Frage
The SCHEMABINDING option used with a view prevents schema changes to any underlying tables.
Antworten
  • True
  • False

Frage 16

Frage
What is the main benefit of using an index on a table?
Antworten
  • 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.

Frage 17

Frage
For all intents and purposes, is a clustered index the same as the table itself?
Antworten
  • YES
  • NO

Frage 18

Frage
You should always write queries in a way that takes advantage of set-based operations and avoid cursors.
Antworten
  • True
  • False

Frage 19

Frage
A linked server configuration enables SQL Server to exec commands against an OLE DB data sources on remote servers.
Antworten
  • True
  • False

Frage 20

Frage
A subquery can be used anywhere an expression can be used.
Antworten
  • True
  • False

Frage 21

Frage
An INNER JOIN will always return at least one row of data.
Antworten
  • True
  • False

Frage 22

Frage
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.
Antworten
  • True
  • False

Frage 23

Frage
If two tables, [A] with m rows and [B] with n rows, are CROSS JOINed, the result set has how many rows?
Antworten
  • m + n - 1
  • m * n - 1
  • m * n
  • ( m - 1 ) * ( n - 1 )

Frage 24

Frage
In a FULL OUTER JOIN all of the rows of all of the joined tables are included, whether they are matched or not.
Antworten
  • True
  • False

Frage 25

Frage
If you SELF JON a table, you have to use table aliases.
Antworten
  • True
  • False

Frage 26

Frage
What are the types of SQL user-defined functions?
Antworten
  • Scalar
  • Inline table-valued
  • Procedural
  • Functional
  • Multi-statement table-valued

Frage 27

Frage
An inline table-valued function is the best type of function from the point of view of function performance.
Antworten
  • True
  • False

Frage 28

Frage
Multi-statement table-valued functions can have side-effects (e.g., update a table).
Antworten
  • True
  • False

Frage 29

Frage
You can insert data returned straight from a stored procedure by using the INSERT...EXEC statement.
Antworten
  • True
  • False

Frage 30

Frage
An identity field can temporarily be disabled (for instance, during a data load) by using one of the SET directives.
Antworten
  • True
  • False

Frage 31

Frage
When a table is truncated, an identity field is reset to its starting value.
Antworten
  • True
  • False

Frage 32

Frage
When all the rows in a table are DELETEd, the identity column gets reset to its initial value.
Antworten
  • True
  • False

Frage 33

Frage
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.
Antworten
  • True
  • False

Frage 34

Frage
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.
Antworten
  • True
  • False

Frage 35

Frage
Which constraint enforces referential integrity?
Antworten
  • UNIQUE KEY constraint
  • FOREIGN KEY constraint
  • CHECK constraint
  • TRIGGER

Frage 36

Frage
A CHECK constraint does not come into play if the relevant column receives a NULL value.
Antworten
  • True
  • False

Frage 37

Frage
One should always prefer CHECK constraints to TRIGGERS.
Antworten
  • True
  • False

Frage 38

Frage
The UNIQUE constraint is always implemented as a unique index on the relevant column(s).
Antworten
  • True
  • False

Frage 39

Frage
What is the outcome of the expression under the default settings of SQL Server: SELECT ( 'a' + NULL ) ?
Antworten
  • a
  • NULL
  • an error is returned

Frage 40

Frage
It is best practice not to use query/table hints in production code due to maintenance issues.
Antworten
  • True
  • False

Frage 41

Frage
Which WHERE clause has the potential to benefit from an index?
Antworten
  • WHERE Firstname LIKE '%N'
  • WHERE Firstname LIKE 'N%'
  • WHERE Firstname LIKE '%N%'

Frage 42

Frage
BCP lets one perform data imports and exports using a command-line utility.
Antworten
  • True
  • False

Frage 43

Frage
Dirty reads can happen if you use the isolation level of READ COMMITTED.
Antworten
  • True
  • False

Frage 44

Frage
The SERIALIZABLE isolation level is sufficient to ensure that no phantom rows appear within a transaction.
Antworten
  • True
  • False

Frage 45

Frage
It is best practice to set FILLFACTOR to 100 (or 0) on an index in a read-only table.
Antworten
  • True
  • False

Frage 46

Frage
The DENY permission takes precedence over all other permissions that a user might have for an object.
Antworten
  • True
  • False

Frage 47

Frage
A lock can go from the row level to the page level.
Antworten
  • True
  • False

Frage 48

Frage
The DELETE command has much more overhead in terms of log activity than TRUNCATE.
Antworten
  • True
  • False

Frage 49

Frage
It is best practice to specify collation inline within a query.
Antworten
  • True
  • False

Frage 50

Frage
The collation of a SQL Server can be changed without re-installation once the server has been installed.
Antworten
  • True
  • False

Frage 51

Frage
The SNAPSHOT isolation level relies heavily on tempdb.
Antworten
  • True
  • False

Frage 52

Frage
It is best practice to have a clustered index on a column against which range searches/scans are performed.
Antworten
  • True
  • False

Frage 53

Frage
To improve performance of a view one can try to convert it into an indexed view.
Antworten
  • True
  • False

Frage 54

Frage
A stale statistics can lead to the optimizer's not picking up an optimal query plan and underestimation of row counts.
Antworten
  • True
  • False

Frage 55

Frage
Statistics can be created manually and automatically by SQL Server.
Antworten
  • True
  • False

Frage 56

Frage
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).
Antworten
  • True
  • False

Frage 57

Frage
What is the outcome of the following query: SELECT 1 WHERE ( 1 = 0 )?
Antworten
  • 1
  • 0
  • NULL
  • empty set (no rows)

Frage 58

Frage
Does the ORDER BY clause paired with TOP(100 PERCENT) used in a view guarantee the order of the returned rows?
Antworten
  • YES
  • NO

Frage 59

Frage
If you use ORDER BY in a query, what you really get is not a relational set but a cursor.
Antworten
  • True
  • False

Frage 60

Frage
It is best practice to use UNION ALL wherever possible instead of UNION due to performance reasons.
Antworten
  • True
  • False

Frage 61

Frage
A deadlock can happen if two pieces of code access the same objects in reverse order at the same time.
Antworten
  • True
  • False

Frage 62

Frage
Deadlock is a blocking situation but a blocking situation doesn't necessarily have to be a deadlock.
Antworten
  • True
  • False

Frage 63

Frage
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.
Antworten
  • True
  • False

Frage 64

Frage
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.
Antworten
  • True
  • False

Frage 65

Frage
The XML data type in SQL Server can be tied to an XML schema but this is not required.
Antworten
  • True
  • False

Frage 66

Frage
The EXCEPT operator removes all duplicates from the result set and treats two NULL values as being equal.
Antworten
  • True
  • False

Frage 67

Frage
In the new error-handling construct, TRY...CATCH, it is possible to use the FINALLY block known from other languages.
Antworten
  • True
  • False

Frage 68

Frage
What does the SET XACT_ABORT ON; directive do?
Antworten
  • 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.

Frage 69

Frage
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.
Antworten
  • True
  • False

Frage 70

Frage
A subquery can contain an ORDER BY clause.
Antworten
  • True
  • False

Frage 71

Frage
To return de-normalized data from a table/tables one can use the PIVOT operator.
Antworten
  • True
  • False

Frage 72

Frage
To how many levels can you nest stored procedures (and managed code)?
Antworten
  • 16
  • 32
  • 64
  • limited only by the memory of the system

Frage 73

Frage
You can only have one PRIMARY filegroup per database but as many user-defined filegroups as you want.
Antworten
  • True
  • False

Frage 74

Frage
It is best practice to only have one log file per database.
Antworten
  • True
  • False

Frage 75

Frage
Data is written to a log file in a sequential manner.
Antworten
  • True
  • False

Frage 76

Frage
Log files can be placed into filegroups in much the same way as data files can.
Antworten
  • True
  • False

Frage 77

Frage
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.
Antworten
  • True
  • False

Frage 78

Frage
One can put a non-clustered index an a heap or on a clustered table.
Antworten
  • True
  • False

Frage 79

Frage
PowerShell cannot be used to change the collation of a server.
Antworten
  • True
  • False

Frage 80

Frage
PowerShell can execute any SQL query against a SQL Server database.
Antworten
  • True
  • False

Frage 81

Frage
It is best practice to prefer using ORs to ANDs when building queries.
Antworten
  • True
  • False

Frage 82

Frage
One can configure log-shipping in such a way that the database to which logs are being shipped is usable for reporting purposes.
Antworten
  • True
  • False

Frage 83

Frage
What's the outcome of the following: IF ( SELECT 1/0 WHERE ( 1 = 0 ) ) IS NULL PRINT 'A' ELSE PRINT 'B'; ?
Antworten
  • error is raised
  • B
  • A

Frage 84

Frage
Temporary tables can have indexes defined on them and SQL Server can create statistics on them when it deems it appropriate.
Antworten
  • True
  • False

Frage 85

Frage
A table variable can be pushed out to tempdb.
Antworten
  • True
  • False

Frage 86

Frage
Parallel query execution plans are always better in terms of performance than single-threaded plans.
Antworten
  • True
  • False

Frage 87

Frage
SQL Server supports directly a many-to-many relationship between tables.
Antworten
  • True
  • False

Frage 88

Frage
CHECKPOINTS truncate the log when the database is in the FULL recovery model.
Antworten
  • True
  • False

Frage 89

Frage
SQL Server supports regular expressions out-of-the-box.
Antworten
  • True
  • False

Frage 90

Frage
Cardinality issues stem from the fact that statistics are stale and they almost always lead to the optimizer choosing suboptimal query execution plan.
Antworten
  • True
  • False

Frage 91

Frage
A non-clustered index and tempdb can be created on a separate (physical) drive to improve performance.
Antworten
  • True
  • False

Frage 92

Frage
Which aggregate function(s) do(es) ignore NULLs?
Antworten
  • AVG( [ColumnName] )
  • SUM( [ColumnName] )
  • COUNT(*)
  • COUNT( [ColumnName] )
  • MAX( [ColumnName] )
  • COUNT_BIG( [ColumnName] )

Frage 93

Frage
Do you have to take a database offline to change the physical location of one of its files?
Antworten
  • YES
  • NO

Frage 94

Frage
Can we insert data into a table on which the clustered index has been disabled?
Antworten
  • YES
  • NO

Frage 95

Frage
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.
Antworten
  • True
  • False

Frage 96

Frage
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
Antworten
  • 1)
  • 2)
  • 3)

Frage 97

Frage
SELECT...INTO will never lock system objects.
Antworten
  • True
  • False

Frage 98

Frage
Does the order of columns in UPDATE statements matter in any way?
Antworten
  • NO
  • YES

Frage 99

Frage
What are the special memory resident tables available when dealing with triggers?
Antworten
  • DELETED
  • UPDATED
  • INSERTED

Frage 100

Frage
User-defined functions can raise errors inside their bodies the same way stored procedures can by calling the RAISERROR function.
Antworten
  • True
  • False

Frage 101

Frage
In which database is the metadata about SQL Server Agent jobs stored?
Antworten
  • master
  • tempdb
  • msdb
  • resource
  • model

Frage 102

Frage
Which database(s) should a DBA back up on a regular schedule?
Antworten
  • master
  • msdb
  • tempdb

Frage 103

Frage
The fastest way to improve a performance of a SELECT query is to add a covering index to the table(s).
Antworten
  • True
  • False

Frage 104

Frage
Transactions can span multiple batches, that is, pieces of code separated by GO.
Antworten
  • True
  • False

Frage 105

Frage
Activity Monitor lets you see in real-time the most expensive queries executing on the machine.
Antworten
  • True
  • False

Frage 106

Frage
It is best practice not to use Common Table Expressions as they usually perform much worse than plain subqueries.
Antworten
  • True
  • False

Frage 107

Frage
Which function returns data with greater resolution?
Antworten
  • GETDATE
  • SYSDATETIME

Frage 108

Frage
Is it true that errors whose severity level is below 11 cannot be handled by the TRY...CATCH construct?
Antworten
  • YES
  • NO

Frage 109

Frage
Can the TRY...CATCH block catch object name resolution errors?
Antworten
  • NO
  • YES

Frage 110

Frage
Does SELECT...INTO transfer indexes to the new table?
Antworten
  • NO
  • YES

Frage 111

Frage
Is it true that each and every user in a database is automatically a member of the public role?
Antworten
  • YES
  • NO

Frage 112

Frage
Can the dbo user in a database be denied access to a table?
Antworten
  • NO
  • YES

Frage 113

Frage
When data is deleted from a table, SQL Server immediately reduces the size of the table.
Antworten
  • True
  • False

Frage 114

Frage
Which functions operate on the XML data type in SQL Server?
Antworten
  • query()
  • value()
  • exists()
  • is()
  • modify()
  • nodes()
  • length()
  • datatype()

Frage 115

Frage
In which system database are usernames and passwords (if any) stored?
Antworten
  • model
  • msdb
  • master
  • distribution

Frage 116

Frage
Which are good properties of a clustered index?
Antworten
  • Static
  • Narrow
  • Ever-increasing
  • Random

Frage 117

Frage
It is best practice to shrink log files on a regular basis.
Antworten
  • True
  • False

Frage 118

Frage
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.
Antworten
  • True
  • False

Frage 119

Frage
How many different database recovery models does SQL Server support?
Antworten
  • 1
  • 2
  • 3
  • 4
  • 19

Frage 120

Frage
Online index rebuilds are available in all editions of SQL Server except the Express one.
Antworten
  • True
  • False

Frage 121

Frage
What's the outcome of the query: SELECT COALESCE(NULLIF(1, 1), 'A')?
Antworten
  • A
  • NULL
  • conversion error

Frage 122

Frage
Which process is the CXPACKET wait type associated with?
Antworten
  • Reading data from disk to cache
  • Executing parallel query plans
  • Flushing dirty pages to disk
  • Receiving asynchronous messages via SQL Server Broker

Frage 123

Frage
It is best practice to not create indexes on foreign key columns.
Antworten
  • True
  • False

Frage 124

Frage
It is best practice not to give objects names that contain special characters or spaces.
Antworten
  • True
  • False

Frage 125

Frage
It is best practice to turn off the AUTO CREATE and AUTO UPDATE of statistics and take care of them manually.
Antworten
  • True
  • False

Frage 126

Frage
Which one is the preferred way of capturing the last inserted identity value in a session?
Antworten
  • @@Indentity
  • SCOPE_IDENTITY()
Zusammenfassung anzeigen Zusammenfassung ausblenden

Möchten Sie mit GoConqr kostenlos Ihre eigenen Quiz erstellen? eigenen Mehr erfahren.

ähnlicher Inhalt

70-458
Ben Odunjo
SQL Azure Identity Jumping
Mary Macdonald
Managing Master Data
wynand.coetzee77
Data Quality project to clean data
wynand.coetzee77
Deploy package to file system
wynand.coetzee77
Package Properties at Run Time
wynand.coetzee77
Script Components
wynand.coetzee77
DQS Permissions
wynand.coetzee77
BASES DE DATOS
Hernando Castro
INTELIGENCIAS MÚLTIPLES
Nadia Salazar
BASES DE DATOS
Shanella Percel
Bibliothek durchsuchen