Quiz
T-SQL Dev Job
Crie uma conta grátis para terminar este quiz
Salve seu progresso e veja sua nota final.
Já tem uma conta? Inicie sessão
Perguntas do quiz (126)
1What are the essential properties of transactions?
- AAtomicity
- BDurability
- CConsistency
- DManageability
- EIsolation
- FCo-operativity
2A database that's gone through how many normalization forms is usually considered "good enough"?
- A1
- B2
- C3
- D4
- E5
3What are the benefits of stored procedures?
- AExecution plan retention/caching
- BCode reuse
- CParameter sniffing
- DImproved security
4What are the types of triggers?
- AINSTEAD OF
- BDELAYED
- CBEFORE
- DAFTER
5Triggers take part in transactions.
- AVerdadeiro
- BFalso
6Changes to table variables within a scope of a transaction get rolled back when the transaction is rolled back.
- AVerdadeiro
- BFalso
7One should prefer using CHECK constraints to triggers.
- AVerdadeiro
- BFalso
8DDL triggers are always AFTER triggers.
- AVerdadeiro
- BFalso
9It's best practice to build views from other views. This promotes code reuse.
- AVerdadeiro
- BFalso
10Some views can be used to update underlying tables.
- AVerdadeiro
- BFalso
11Some views with aggregate functions in them can be used to update the underlying table(s).
- AVerdadeiro
- BFalso
12An indexed view materializes data in the I/O subsystem and requires a clustered index.
- AVerdadeiro
- BFalso
13Heaps are tables without a clustered index on them.
- AVerdadeiro
- BFalso
14It's best practice to put clustered indexes on all tables, even the small ones.
- AVerdadeiro
- BFalso
15The SCHEMABINDING option used with a view prevents schema changes to any underlying tables.
- AVerdadeiro
- BFalso
16What is the main benefit of using an index on a table?
- AIt prevents updating table statistics too frequently.
- BIt stores the data in the table in an ordered state.
- CIt makes writing queries easier.
- DIt helps locate rows more quickly and efficiently.
17For all intents and purposes, is a clustered index the same as the table itself?
- AYES
- BNO
18You should always write queries in a way that takes advantage of set-based operations and avoid cursors.
- AVerdadeiro
- BFalso
19A linked server configuration enables SQL Server to exec commands against an OLE DB data sources on remote servers.
- AVerdadeiro
- BFalso
20A subquery can be used anywhere an expression can be used.
- AVerdadeiro
- BFalso
21An INNER JOIN will always return at least one row of data.
- AVerdadeiro
- BFalso
22It'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.
- AVerdadeiro
- BFalso
23If two tables, [A] with m rows and [B] with n rows, are CROSS JOINed, the result set has how many rows?
- Am + n - 1
- Bm * n - 1
- Cm * n
- D( m - 1 ) * ( n - 1 )
24In a FULL OUTER JOIN all of the rows of all of the joined tables are included, whether they are matched or not.
- AVerdadeiro
- BFalso
25If you SELF JON a table, you have to use table aliases.
- AVerdadeiro
- BFalso
26What are the types of SQL user-defined functions?
- AScalar
- BInline table-valued
- CProcedural
- DFunctional
- EMulti-statement table-valued
27An inline table-valued function is the best type of function from the point of view of function performance.
- AVerdadeiro
- BFalso
28Multi-statement table-valued functions can have side-effects (e.g., update a table).
- AVerdadeiro
- BFalso
29You can insert data returned straight from a stored procedure by using the INSERT...EXEC statement.
- AVerdadeiro
- BFalso
30An identity field can temporarily be disabled (for instance, during a data load) by using one of the SET directives.
- AVerdadeiro
- BFalso
31When a table is truncated, an identity field is reset to its starting value.
- AVerdadeiro
- BFalso
32When all the rows in a table are DELETEd, the identity column gets reset to its initial value.
- AVerdadeiro
- BFalso
33Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table.
- AVerdadeiro
- BFalso
34It is possible to change the length of a column defined as a PRIMARY KEY constraint without first deleting it and then re-creating it.
- AVerdadeiro
- BFalso
35Which constraint enforces referential integrity?
- AUNIQUE KEY constraint
- BFOREIGN KEY constraint
- CCHECK constraint
- DTRIGGER
36A CHECK constraint does not come into play if the relevant column receives a NULL value.
- AVerdadeiro
- BFalso
37One should always prefer CHECK constraints to TRIGGERS.
- AVerdadeiro
- BFalso
38The UNIQUE constraint is always implemented as a unique index on the relevant column(s).
- AVerdadeiro
- BFalso
39What is the outcome of the expression under the default settings of SQL Server: SELECT ( 'a' + NULL ) ?
- Aa
- BNULL
- Can error is returned
40It is best practice not to use query/table hints in production code due to maintenance issues.
- AVerdadeiro
- BFalso
41Which WHERE clause has the potential to benefit from an index?
- AWHERE Firstname LIKE '%N'
- BWHERE Firstname LIKE 'N%'
- CWHERE Firstname LIKE '%N%'
42BCP lets one perform data imports and exports using a command-line utility.
- AVerdadeiro
- BFalso
43Dirty reads can happen if you use the isolation level of READ COMMITTED.
- AVerdadeiro
- BFalso
44The SERIALIZABLE isolation level is sufficient to ensure that no phantom rows appear within a transaction.
- AVerdadeiro
- BFalso
45It is best practice to set FILLFACTOR to 100 (or 0) on an index in a read-only table.
- AVerdadeiro
- BFalso
46The DENY permission takes precedence over all other permissions that a user might have for an object.
- AVerdadeiro
- BFalso
47A lock can go from the row level to the page level.
- AVerdadeiro
- BFalso
48The DELETE command has much more overhead in terms of log activity than TRUNCATE.
- AVerdadeiro
- BFalso
49It is best practice to specify collation inline within a query.
- AVerdadeiro
- BFalso
50The collation of a SQL Server can be changed without re-installation once the server has been installed.
- AVerdadeiro
- BFalso
51The SNAPSHOT isolation level relies heavily on tempdb.
- AVerdadeiro
- BFalso
52It is best practice to have a clustered index on a column against which range searches/scans are performed.
- AVerdadeiro
- BFalso
53To improve performance of a view one can try to convert it into an indexed view.
- AVerdadeiro
- BFalso
54A stale statistics can lead to the optimizer's not picking up an optimal query plan and underestimation of row counts.
- AVerdadeiro
- BFalso
55Statistics can be created manually and automatically by SQL Server.
- AVerdadeiro
- BFalso
56When 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).
- AVerdadeiro
- BFalso
57What is the outcome of the following query: SELECT 1 WHERE ( 1 = 0 )?
- A1
- B0
- CNULL
- Dempty set (no rows)
58Does the ORDER BY clause paired with TOP(100 PERCENT) used in a view guarantee the order of the returned rows?
- AYES
- BNO
59If you use ORDER BY in a query, what you really get is not a relational set but a cursor.
- AVerdadeiro
- BFalso
60It is best practice to use UNION ALL wherever possible instead of UNION due to performance reasons.
- AVerdadeiro
- BFalso
61A deadlock can happen if two pieces of code access the same objects in reverse order at the same time.
- AVerdadeiro
- BFalso
62Deadlock is a blocking situation but a blocking situation doesn't necessarily have to be a deadlock.
- AVerdadeiro
- BFalso
63It is best practice to always use UNICODE data types (for textual values) regardless of whether the data comes from a UNICODE source or not.
- AVerdadeiro
- BFalso
64It 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.
- AVerdadeiro
- BFalso
65The XML data type in SQL Server can be tied to an XML schema but this is not required.
- AVerdadeiro
- BFalso
66The EXCEPT operator removes all duplicates from the result set and treats two NULL values as being equal.
- AVerdadeiro
- BFalso
67In the new error-handling construct, TRY...CATCH, it is possible to use the FINALLY block known from other languages.
- AVerdadeiro
- BFalso
68What does the SET XACT_ABORT ON; directive do?
- AIt ensures that the isolation level of the code following it cannot be changed without raising an error.
- BIt ensures that the developer must handle any rollbacks manually.
- CIt 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).
- DIt ensures that a transaction is automatically rolled back if an error occurs.
69A 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.
- AVerdadeiro
- BFalso
70A subquery can contain an ORDER BY clause.
- AVerdadeiro
- BFalso
71To return de-normalized data from a table/tables one can use the PIVOT operator.
- AVerdadeiro
- BFalso
72To how many levels can you nest stored procedures (and managed code)?
- A16
- B32
- C64
- Dlimited only by the memory of the system
73You can only have one PRIMARY filegroup per database but as many user-defined filegroups as you want.
- AVerdadeiro
- BFalso
74It is best practice to only have one log file per database.
- AVerdadeiro
- BFalso
75Data is written to a log file in a sequential manner.
- AVerdadeiro
- BFalso
76Log files can be placed into filegroups in much the same way as data files can.
- AVerdadeiro
- BFalso
77It 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.
- AVerdadeiro
- BFalso
78One can put a non-clustered index an a heap or on a clustered table.
- AVerdadeiro
- BFalso
79PowerShell cannot be used to change the collation of a server.
- AVerdadeiro
- BFalso
80PowerShell can execute any SQL query against a SQL Server database.
- AVerdadeiro
- BFalso
81It is best practice to prefer using ORs to ANDs when building queries.
- AVerdadeiro
- BFalso
82One can configure log-shipping in such a way that the database to which logs are being shipped is usable for reporting purposes.
- AVerdadeiro
- BFalso
83What's the outcome of the following: IF ( SELECT 1/0 WHERE ( 1 = 0 ) ) IS NULL PRINT 'A' ELSE PRINT 'B'; ?
- Aerror is raised
- BB
- CA
84Temporary tables can have indexes defined on them and SQL Server can create statistics on them when it deems it appropriate.
- AVerdadeiro
- BFalso
85A table variable can be pushed out to tempdb.
- AVerdadeiro
- BFalso
86Parallel query execution plans are always better in terms of performance than single-threaded plans.
- AVerdadeiro
- BFalso
87SQL Server supports directly a many-to-many relationship between tables.
- AVerdadeiro
- BFalso
88CHECKPOINTS truncate the log when the database is in the FULL recovery model.
- AVerdadeiro
- BFalso
89SQL Server supports regular expressions out-of-the-box.
- AVerdadeiro
- BFalso
90Cardinality issues stem from the fact that statistics are stale and they almost always lead to the optimizer choosing suboptimal query execution plan.
- AVerdadeiro
- BFalso
91A non-clustered index and tempdb can be created on a separate (physical) drive to improve performance.
- AVerdadeiro
- BFalso
92Which aggregate function(s) do(es) ignore NULLs?
- AAVG( [ColumnName] )
- BSUM( [ColumnName] )
- CCOUNT(*)
- DCOUNT( [ColumnName] )
- EMAX( [ColumnName] )
- FCOUNT_BIG( [ColumnName] )
93Do you have to take a database offline to change the physical location of one of its files?
- AYES
- BNO
94Can we insert data into a table on which the clustered index has been disabled?
- AYES
- BNO
95It 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.
- AVerdadeiro
- BFalso
96Which 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- A1)
- B2)
- C3)
97SELECT...INTO will never lock system objects.
- AVerdadeiro
- BFalso
98Does the order of columns in UPDATE statements matter in any way?
- ANO
- BYES
99What are the special memory resident tables available when dealing with triggers?
- ADELETED
- BUPDATED
- CINSERTED
100User-defined functions can raise errors inside their bodies the same way stored procedures can by calling the RAISERROR function.
- AVerdadeiro
- BFalso
101In which database is the metadata about SQL Server Agent jobs stored?
- Amaster
- Btempdb
- Cmsdb
- Dresource
- Emodel
102Which database(s) should a DBA back up on a regular schedule?
- Amaster
- Bmsdb
- Ctempdb
103The fastest way to improve a performance of a SELECT query is to add a covering index to the table(s).
- AVerdadeiro
- BFalso
104Transactions can span multiple batches, that is, pieces of code separated by GO.
- AVerdadeiro
- BFalso
105Activity Monitor lets you see in real-time the most expensive queries executing on the machine.
- AVerdadeiro
- BFalso
106It is best practice not to use Common Table Expressions as they usually perform much worse than plain subqueries.
- AVerdadeiro
- BFalso
107Which function returns data with greater resolution?
- AGETDATE
- BSYSDATETIME
108Is it true that errors whose severity level is below 11 cannot be handled by the TRY...CATCH construct?
- AYES
- BNO
109Can the TRY...CATCH block catch object name resolution errors?
- ANO
- BYES
110Does SELECT...INTO transfer indexes to the new table?
- ANO
- BYES
111Is it true that each and every user in a database is automatically a member of the public role?
- AYES
- BNO
112Can the dbo user in a database be denied access to a table?
- ANO
- BYES
113When data is deleted from a table, SQL Server immediately reduces the size of the table.
- AVerdadeiro
- BFalso
114Which functions operate on the XML data type in SQL Server?
- Aquery()
- Bvalue()
- Cexists()
- Dis()
- Emodify()
- Fnodes()
- Glength()
- Hdatatype()
115In which system database are usernames and passwords (if any) stored?
- Amodel
- Bmsdb
- Cmaster
- Ddistribution
116Which are good properties of a clustered index?
- AStatic
- BNarrow
- CEver-increasing
- DRandom
117It is best practice to shrink log files on a regular basis.
- AVerdadeiro
- BFalso
118Only 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.
- AVerdadeiro
- BFalso
119How many different database recovery models does SQL Server support?
- A1
- B2
- C3
- D4
- E19
120Online index rebuilds are available in all editions of SQL Server except the Express one.
- AVerdadeiro
- BFalso
121What's the outcome of the query: SELECT COALESCE(NULLIF(1, 1), 'A')?
- AA
- BNULL
- Cconversion error
122Which process is the CXPACKET wait type associated with?
- AReading data from disk to cache
- BExecuting parallel query plans
- CFlushing dirty pages to disk
- DReceiving asynchronous messages via SQL Server Broker
123It is best practice to not create indexes on foreign key columns.
- AVerdadeiro
- BFalso
124It is best practice not to give objects names that contain special characters or spaces.
- AVerdadeiro
- BFalso
125It is best practice to turn off the AUTO CREATE and AUTO UPDATE of statistics and take care of them manually.
- AVerdadeiro
- BFalso
126Which one is the preferred way of capturing the last inserted identity value in a session?
- A@@Indentity
- BSCOPE_IDENTITY()
Informações
- Descrição
- Professional SQL Server Quiz sobre T-SQL Dev Job, criado por Dariusz Spiewak em 22-03-2015.
- Autor
- Dariusz Spiewak
- Idioma
- English
- Visualizações
- 40
- Histórico
- Criado em 22 mar 2015 · atualizado em 5 mai 2016
- Etiquetas
- Avaliação
- Ainda sem avaliações Avalie este quiz