Dariusz Spiewak
Quiz by , created more than 1 year ago

Professional SQL Server Quiz on T-SQL Dev Job, created by Dariusz Spiewak on 22/03/2015.

23
1
0
Dariusz Spiewak
Created by Dariusz Spiewak over 9 years ago
Close

T-SQL Dev Job

Question 1 of 126

1

What are the essential properties of transactions?

Select one or more of the following:

  • Atomicity

  • Durability

  • Consistency

  • Manageability

  • Isolation

  • Co-operativity

Explanation

Question 2 of 126

1

A database that's gone through how many normalization forms is usually considered "good enough"?

Select one of the following:

  • 1

  • 2

  • 3

  • 4

  • 5

Explanation

Question 3 of 126

1

What are the benefits of stored procedures?

Select one or more of the following:

  • Execution plan retention/caching

  • Code reuse

  • Parameter sniffing

  • Improved security

Explanation

Question 4 of 126

1

What are the types of triggers?

Select one or more of the following:

  • INSTEAD OF

  • DELAYED

  • BEFORE

  • AFTER

Explanation

Question 5 of 126

1

Triggers take part in transactions.

Select one of the following:

  • True
  • False

Explanation

Question 6 of 126

1

Changes to table variables within a scope of a transaction get rolled back when the transaction is rolled back.

Select one of the following:

  • True
  • False

Explanation

Question 7 of 126

1

One should prefer using CHECK constraints to triggers.

Select one of the following:

  • True
  • False

Explanation

Question 8 of 126

1

DDL triggers are always AFTER triggers.

Select one of the following:

  • True
  • False

Explanation

Question 9 of 126

1

It's best practice to build views from other views. This promotes code reuse.

Select one of the following:

  • True
  • False

Explanation

Question 10 of 126

1

Some views can be used to update underlying tables.

Select one of the following:

  • True
  • False

Explanation

Question 11 of 126

1

Some views with aggregate functions in them can be used to update the underlying table(s).

Select one of the following:

  • True
  • False

Explanation

Question 12 of 126

1

An indexed view materializes data in the I/O subsystem and requires a clustered index.

Select one of the following:

  • True
  • False

Explanation

Question 13 of 126

1

Heaps are tables without a clustered index on them.

Select one of the following:

  • True
  • False

Explanation

Question 14 of 126

1

It's best practice to put clustered indexes on all tables, even the small ones.

Select one of the following:

  • True
  • False

Explanation

Question 15 of 126

1

The SCHEMABINDING option used with a view prevents schema changes to any underlying tables.

Select one of the following:

  • True
  • False

Explanation

Question 16 of 126

1

What is the main benefit of using an index on a table?

Select one of the following:

  • 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.

Explanation

Question 17 of 126

1

For all intents and purposes, is a clustered index the same as the table itself?

Select one of the following:

  • YES

  • NO

Explanation

Question 18 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 19 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 20 of 126

1

A subquery can be used anywhere an expression can be used.

Select one of the following:

  • True
  • False

Explanation

Question 21 of 126

1

An INNER JOIN will always return at least one row of data.

Select one of the following:

  • True
  • False

Explanation

Question 22 of 126

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 23 of 126

1

If two tables, [A] with m rows and [B] with n rows, are CROSS JOINed, the result set has how many rows?

Select one of the following:

  • m + n - 1

  • m * n - 1

  • m * n

  • ( m - 1 ) * ( n - 1 )

Explanation

Question 24 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 25 of 126

1

If you SELF JON a table, you have to use table aliases.

Select one of the following:

  • True
  • False

Explanation

Question 26 of 126

1

What are the types of SQL user-defined functions?

Select one or more of the following:

  • Scalar

  • Inline table-valued

  • Procedural

  • Functional

  • Multi-statement table-valued

Explanation

Question 27 of 126

1

An inline table-valued function is the best type of function from the point of view of function performance.

Select one of the following:

  • True
  • False

Explanation

Question 28 of 126

1

Multi-statement table-valued functions can have side-effects (e.g., update a table).

Select one of the following:

  • True
  • False

Explanation

Question 29 of 126

1

You can insert data returned straight from a stored procedure by using the INSERT...EXEC statement.

Select one of the following:

  • True
  • False

Explanation

Question 30 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 31 of 126

1

When a table is truncated, an identity field is reset to its starting value.

Select one of the following:

  • True
  • False

Explanation

Question 32 of 126

1

When all the rows in a table are DELETEd, the identity column gets reset to its initial value.

Select one of the following:

  • True
  • False

Explanation

Question 33 of 126

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 34 of 126

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 35 of 126

1

Which constraint enforces referential integrity?

Select one of the following:

  • UNIQUE KEY constraint

  • FOREIGN KEY constraint

  • CHECK constraint

  • TRIGGER

Explanation

Question 36 of 126

1

A CHECK constraint does not come into play if the relevant column receives a NULL value.

Select one of the following:

  • True
  • False

Explanation

Question 37 of 126

1

One should always prefer CHECK constraints to TRIGGERS.

Select one of the following:

  • True
  • False

Explanation

Question 38 of 126

1

The UNIQUE constraint is always implemented as a unique index on the relevant column(s).

Select one of the following:

  • True
  • False

Explanation

Question 39 of 126

1

What is the outcome of the expression under the default settings of SQL Server: SELECT ( 'a' + NULL ) ?

Select one of the following:

  • a

  • NULL

  • an error is returned

Explanation

Question 40 of 126

1

It is best practice not to use query/table hints in production code due to maintenance issues.

Select one of the following:

  • True
  • False

Explanation

Question 41 of 126

1

Which WHERE clause has the potential to benefit from an index?

Select one of the following:

  • WHERE Firstname LIKE '%N'

  • WHERE Firstname LIKE 'N%'

  • WHERE Firstname LIKE '%N%'

Explanation

Question 42 of 126

1

BCP lets one perform data imports and exports using a command-line utility.

Select one of the following:

  • True
  • False

Explanation

Question 43 of 126

1

Dirty reads can happen if you use the isolation level of READ COMMITTED.

Select one of the following:

  • True
  • False

Explanation

Question 44 of 126

1

The SERIALIZABLE isolation level is sufficient to ensure that no phantom rows appear within a transaction.

Select one of the following:

  • True
  • False

Explanation

Question 45 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 46 of 126

1

The DENY permission takes precedence over all other permissions that a user might have for an object.

Select one of the following:

  • True
  • False

Explanation

Question 47 of 126

1

A lock can go from the row level to the page level.

Select one of the following:

  • True
  • False

Explanation

Question 48 of 126

1

The DELETE command has much more overhead in terms of log activity than TRUNCATE.

Select one of the following:

  • True
  • False

Explanation

Question 49 of 126

1

It is best practice to specify collation inline within a query.

Select one of the following:

  • True
  • False

Explanation

Question 50 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 51 of 126

1

The SNAPSHOT isolation level relies heavily on tempdb.

Select one of the following:

  • True
  • False

Explanation

Question 52 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 53 of 126

1

To improve performance of a view one can try to convert it into an indexed view.

Select one of the following:

  • True
  • False

Explanation

Question 54 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 55 of 126

1

Statistics can be created manually and automatically by SQL Server.

Select one of the following:

  • True
  • False

Explanation

Question 56 of 126

1

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).

Select one of the following:

  • True
  • False

Explanation

Question 57 of 126

1

What is the outcome of the following query: SELECT 1 WHERE ( 1 = 0 )?

Select one of the following:

  • 1

  • 0

  • NULL

  • empty set (no rows)

Explanation

Question 58 of 126

1

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

Select one of the following:

  • YES

  • NO

Explanation

Question 59 of 126

1

If you use ORDER BY in a query, what you really get is not a relational set but a cursor.

Select one of the following:

  • True
  • False

Explanation

Question 60 of 126

1

It is best practice to use UNION ALL wherever possible instead of UNION due to performance reasons.

Select one of the following:

  • True
  • False

Explanation

Question 61 of 126

1

A deadlock can happen if two pieces of code access the same objects in reverse order at the same time.

Select one of the following:

  • True
  • False

Explanation

Question 62 of 126

1

Deadlock is a blocking situation but a blocking situation doesn't necessarily have to be a deadlock.

Select one of the following:

  • True
  • False

Explanation

Question 63 of 126

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 64 of 126

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 65 of 126

1

The XML data type in SQL Server can be tied to an XML schema but this is not required.

Select one of the following:

  • True
  • False

Explanation

Question 66 of 126

1

The EXCEPT operator removes all duplicates from the result set and treats two NULL values as being equal.

Select one of the following:

  • True
  • False

Explanation

Question 67 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 68 of 126

1

What does the SET XACT_ABORT ON; directive do?

Select one of the following:

  • 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.

Explanation

Question 69 of 126

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 70 of 126

1

A subquery can contain an ORDER BY clause.

Select one of the following:

  • True
  • False

Explanation

Question 71 of 126

1

To return de-normalized data from a table/tables one can use the PIVOT operator.

Select one of the following:

  • True
  • False

Explanation

Question 72 of 126

1

To how many levels can you nest stored procedures (and managed code)?

Select one of the following:

  • 16

  • 32

  • 64

  • limited only by the memory of the system

Explanation

Question 73 of 126

1

You can only have one PRIMARY filegroup per database but as many user-defined filegroups as you want.

Select one of the following:

  • True
  • False

Explanation

Question 74 of 126

1

It is best practice to only have one log file per database.

Select one of the following:

  • True
  • False

Explanation

Question 75 of 126

1

Data is written to a log file in a sequential manner.

Select one of the following:

  • True
  • False

Explanation

Question 76 of 126

1

Log files can be placed into filegroups in much the same way as data files can.

Select one of the following:

  • True
  • False

Explanation

Question 77 of 126

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 78 of 126

1

One can put a non-clustered index an a heap or on a clustered table.

Select one of the following:

  • True
  • False

Explanation

Question 79 of 126

1

PowerShell cannot be used to change the collation of a server.

Select one of the following:

  • True
  • False

Explanation

Question 80 of 126

1

PowerShell can execute any SQL query against a SQL Server database.

Select one of the following:

  • True
  • False

Explanation

Question 81 of 126

1

It is best practice to prefer using ORs to ANDs when building queries.

Select one of the following:

  • True
  • False

Explanation

Question 82 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 83 of 126

1

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

Select one of the following:

  • error is raised

  • B

  • A

Explanation

Question 84 of 126

1

Temporary tables can have indexes defined on them and SQL Server can create statistics on them when it deems it appropriate.

Select one of the following:

  • True
  • False

Explanation

Question 85 of 126

1

A table variable can be pushed out to tempdb.

Select one of the following:

  • True
  • False

Explanation

Question 86 of 126

1

Parallel query execution plans are always better in terms of performance than single-threaded plans.

Select one of the following:

  • True
  • False

Explanation

Question 87 of 126

1

SQL Server supports directly a many-to-many relationship between tables.

Select one of the following:

  • True
  • False

Explanation

Question 88 of 126

1

CHECKPOINTS truncate the log when the database is in the FULL recovery model.

Select one of the following:

  • True
  • False

Explanation

Question 89 of 126

1

SQL Server supports regular expressions out-of-the-box.

Select one of the following:

  • True
  • False

Explanation

Question 90 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 91 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 92 of 126

1

Which aggregate function(s) do(es) ignore NULLs?

Select one or more of the following:

  • AVG( [ColumnName] )

  • SUM( [ColumnName] )

  • COUNT(*)

  • COUNT( [ColumnName] )

  • MAX( [ColumnName] )

  • COUNT_BIG( [ColumnName] )

Explanation

Question 93 of 126

1

Do you have to take a database offline to change the physical location of one of its files?

Select one of the following:

  • YES

  • NO

Explanation

Question 94 of 126

1

Can we insert data into a table on which the clustered index has been disabled?

Select one of the following:

  • YES

  • NO

Explanation

Question 95 of 126

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 96 of 126

1

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

Select one of the following:

  • 1)

  • 2)

  • 3)

Explanation

Question 97 of 126

1

SELECT...INTO will never lock system objects.

Select one of the following:

  • True
  • False

Explanation

Question 98 of 126

1

Does the order of columns in UPDATE statements matter in any way?

Select one of the following:

  • NO

  • YES

Explanation

Question 99 of 126

1

What are the special memory resident tables available when dealing with triggers?

Select one or more of the following:

  • DELETED

  • UPDATED

  • INSERTED

Explanation

Question 100 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 101 of 126

1

In which database is the metadata about SQL Server Agent jobs stored?

Select one of the following:

  • master

  • tempdb

  • msdb

  • resource

  • model

Explanation

Question 102 of 126

1

Which database(s) should a DBA back up on a regular schedule?

Select one or more of the following:

  • master

  • msdb

  • tempdb

Explanation

Question 103 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 104 of 126

1

Transactions can span multiple batches, that is, pieces of code separated by GO.

Select one of the following:

  • True
  • False

Explanation

Question 105 of 126

1

Activity Monitor lets you see in real-time the most expensive queries executing on the machine.

Select one of the following:

  • True
  • False

Explanation

Question 106 of 126

1

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

Select one of the following:

  • True
  • False

Explanation

Question 107 of 126

1

Which function returns data with greater resolution?

Select one of the following:

  • GETDATE

  • SYSDATETIME

Explanation

Question 108 of 126

1

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

Select one of the following:

  • YES

  • NO

Explanation

Question 109 of 126

1

Can the TRY...CATCH block catch object name resolution errors?

Select one of the following:

  • NO

  • YES

Explanation

Question 110 of 126

1

Does SELECT...INTO transfer indexes to the new table?

Select one of the following:

  • NO

  • YES

Explanation

Question 111 of 126

1

Is it true that each and every user in a database is automatically a member of the public role?

Select one of the following:

  • YES

  • NO

Explanation

Question 112 of 126

1

Can the dbo user in a database be denied access to a table?

Select one of the following:

  • NO

  • YES

Explanation

Question 113 of 126

1

When data is deleted from a table, SQL Server immediately reduces the size of the table.

Select one of the following:

  • True
  • False

Explanation

Question 114 of 126

1

Which functions operate on the XML data type in SQL Server?

Select one or more of the following:

  • query()

  • value()

  • exists()

  • is()

  • modify()

  • nodes()

  • length()

  • datatype()

Explanation

Question 115 of 126

1

In which system database are usernames and passwords (if any) stored?

Select one of the following:

  • model

  • msdb

  • master

  • distribution

Explanation

Question 116 of 126

1

Which are good properties of a clustered index?

Select one or more of the following:

  • Static

  • Narrow

  • Ever-increasing

  • Random

Explanation

Question 117 of 126

1

It is best practice to shrink log files on a regular basis.

Select one of the following:

  • True
  • False

Explanation

Question 118 of 126

1

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.

Select one of the following:

  • True
  • False

Explanation

Question 119 of 126

1

How many different database recovery models does SQL Server support?

Select one of the following:

  • 1

  • 2

  • 3

  • 4

  • 19

Explanation

Question 120 of 126

1

Online index rebuilds are available in all editions of SQL Server except the Express one.

Select one of the following:

  • True
  • False

Explanation

Question 121 of 126

1

What's the outcome of the query: SELECT COALESCE(NULLIF(1, 1), 'A')?

Select one of the following:

  • A

  • NULL

  • conversion error

Explanation

Question 122 of 126

1

Which process is the CXPACKET wait type associated with?

Select one of the following:

  • Reading data from disk to cache

  • Executing parallel query plans

  • Flushing dirty pages to disk

  • Receiving asynchronous messages via SQL Server Broker

Explanation

Question 123 of 126

1

It is best practice to not create indexes on foreign key columns.

Select one of the following:

  • True
  • False

Explanation

Question 124 of 126

1

It is best practice not to give objects names that contain special characters or spaces.

Select one of the following:

  • True
  • False

Explanation

Question 125 of 126

1

It is best practice to turn off the AUTO CREATE and AUTO UPDATE of statistics and take care of them manually.

Select one of the following:

  • True
  • False

Explanation

Question 126 of 126

1

Which one is the preferred way of capturing the last inserted identity value in a session?

Select one of the following:

  • @@Indentity

  • SCOPE_IDENTITY()

Explanation

Previous
Next