What is SQL Server Agent?

Correct Answer: This command is used when a large processing of data has occurred If any large amount of deletions, any modifications, or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account UPDATE_STATISTICS updates the indexes on these tables accordingly

Correct Answer: Every Analysis server has a repository to store metadata for the objects like cubes, data sources etc It?s by default stored in a MS Access database which can be also migrated to a SQL Server database

Correct Answer: It is a way to convert the original text of the stored procedure into encrypted form The stored procedure gets obfuscated and the output of this is not visible to CREATE PROCEDURE Abc WITH ENCRYPTION AS << SELECT statement>> GO

Correct Answer: Only one column can exist per table that is attached with ROWGUIDCOL property One can then use $ROWGUID instead of column name in select list

Correct Answer: UNION selects only distinct values whereas UNION ALL selects all values and not just distinct ones

Correct Answer: SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel') and SERVERPROPERTY ('edition')

Correct Answer: - A clustered index is a special type of index that reorders the way records in the table are physically stored Therefore table can have only one clustered index - A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk The leaf node of a non clustered index does not consist of the data pages Instead, the leaf nodes contain index rows

Correct Answer: Recursive query can be create in SQL using stored procedure but you can also use CTE (Common table expression) It might be also worth asking about performance as CTE is not always very fast

Correct Answer: Trigger allows us to execute a batch of SQL code when an insert, update or delete command is executed against a specific table Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications They can be executed automatically on the insert, delete and update operation

Correct Answer: SELECT *FROM Employees WHERE EmpName like ?M%?