Querying T-SQL Interview Questions - Part 1 of 2 (On Comprehension from TK 70-461)

SSAS Comprehension Questions

Question 1

Name two aspects in which T-SQL deviates from the relational model. Answer.

A relation has a body with a distinct set of tuples. A table doesn’t have to have a key. T-SQL allows referring to ordinal positions of columns in the ORDER BY clause.

Hide the Answer.

Question 2

Explain how you can address the two items in question 1 and use T-SQL in a relational way. Answer.

Define a key in every table. Refer to attribute names—not their ordinal positions—in the ORDER BY clause.

Hide the Answer.

Question 3

What is the difference between the WHERE and HAVING clauses? Answer.

The WHERE clause is evaluated before rows are grouped, and therefore is evaluated per row. The HAVING clause is evaluated after rows are grouped, and therefore is evaluated per group.

Hide the Answer.

Question 4

Why are you not allowed to refer to a column alias defined by the SELECT clause in the WHERE clause? Answer.

Because the WHERE clause is logically evaluated in a phase earlier to the one that evaluates the SELECT clause.

Hide the Answer.

Question 5

Why are you not allowed to refer to a column alias defined by the SELECT clause in the same SELECT clause? Answer.

Because all expressions that appear in the same logical query processing phase are evaluated conceptually at the same point in time.

Hide the Answer.

Question 6

What are the forms of aliasing an attribute in T-SQL? Answer.

The forms are AS , , and = .

Hide the Answer.

Question 7

What is an irregular identifier? Answer.

An identifier that does not follow the rules for formatting identifiers; for example, it starts with a digit, has an embedded space, or is a reserved T-SQL keyword.

Hide the Answer.

Question 8

Would you use the type FLOAT to represent a product unit price? Answer.

No, because FLOAT is an approximate data type and cannot represent all values precisely.

Hide the Answer.

Question 9

What is the difference between NEWID and NEWSEQUENTIALID? Answer.

The NEWID function generates GUID values in random order, whereas the NEWSEQUENTIAL ID function generates GUIDs that increase in a sequential order.

Hide the Answer.

Question 10

Which function returns the current date and time value as a DATETIME2 type? Answer.

The SYSDATETIME function.

Hide the Answer.

Question 11

When concatenating character strings, what is the difference between the plus (+) operator and the CONCAT function? Answer.

The + operator by default yields a NULL result on NULL input, whereas the CONCAT function treats NULLs as empty strings.

Hide the Answer.

Question 12

What are the performance benefits in using the WHERE filter? Answer.

You reduce network traffic by filtering in the database server instead of in the client, and you can potentially use indexes to avoid full scans of the tables involved.

Hide the Answer.

Question 13

What is the form of a filter predicate that can rely on index ordering called? Answer.

A search argument, or SARG, for short.

Hide the Answer.

Question 14

How do you guarantee the order of the rows in the result of a query? Answer.

The only way to do so is by adding an ORDER BY clause.

Hide the Answer.

Question 15

What is the difference between the result of a query with and one without an ORDER BY clause? Answer.

Without an ORDER BY clause, the result is relational (from an ordering perspec- tive); with an ORDER BY clause, the result is conceptually what the standard calls a cursor.

Hide the Answer.

Question 16

How do you guarantee deterministic results with TOP? Answer.

By either returning all ties by using the WITH TIES option or by defining unique ordering to break ties.

Hide the Answer.

Question 17

What are the benefits of using OFFSET-FETCH over TOP? Answer.

OFFSET-FETCH is standard and TOP isn’t; also, OFFSET-FETCH supports a skipping capability that TOP doesn’t.

Hide the Answer.

Question 18

What is the difference between the old and new syntax for cross joins? Answer.

The new syntax has the CROSS JOIN keywords between the table names and the old syntax has a comma.

Hide the Answer.

Question 19

What are the different types of outer joins? Answer.

Left, right, and full.

Hide the Answer.

Question 20

What is the difference between self-contained and correlated subqueries? Answer.

Self-contained subqueries are independent of the outer query, whereas correlated subqueries have a reference to an element from the table in the outer query.

Hide the Answer.

Question 21

What is the difference between the APPLY and JOIN operators? Answer.

With a JOIN operator, both inputs represent static relations. With APPLY, the left side is a static relation, but the right side can be a table expression with correlations to elements from the left table.

Hide the Answer.

Question 22

Which set operators does T-SQL support? Answer.

The UNION, INTERSECT, and EXCEPT set operators, as well as the UNION ALL multiset operator.

Hide the Answer.

Question 23

Name two requirements for the queries involved in a set operator. Answer.

The number of columns in the two queries needs to be the same, and corresponding columns need to have compatible types.

Hide the Answer.

Question 24

What makes a query a grouped query? Answer.

When you use an aggregate function, a GROUP BY clause, or both.

Hide the Answer.

Question 25

What are the clauses that you can use to define multiple grouping sets in the same query? Answer.

GROUPING SETS, CUBE, and ROLLUP.

Hide the Answer.

Question 26

What is the difference between PIVOT and UNPIVOT? Answer.

PIVOT rotates data from a state of rows to a state of columns; UNPIVOT rotates the data from columns to rows.

Hide the Answer.

Question 27

What type of language constructs are PIVOT and UNPIVOT implemented as? Answer.

PIVOT and UNPIVOT are implemented as table operators (The other table operators are JOIN and APPLY).

Hide the Answer.

Question 28

What are the clauses that the different types of window functions support? Answer.

Partitioning, ordering, and framing clauses.

Hide the Answer.

Question 29

What do the delimiters UNBOUNDED PRECEDING and UNBOUNDED FOLLOW- ING represent? Answer.

The beginning and end of the partition, respectively.

Hide the Answer.

Question 30

Can you store indexes from the same full-text catalog to different filegroups? Answer.

Yes. A full-text catalog is a virtual object only; full-text indexes are physical objects. You can store each full-text index from the same catalog to a different file group.

Hide the Answer.

Question 31

How do you search for synonyms of a word with the CONTAINS predicate? Answer.

You have to use the CONTAINS(FTcolumn, ‘FORMSOF(THESAURUS, SearchWord1)’) syntax.

Hide the Answer.

Question 32

Which is a more specific predicate, CONTAINS or FREETEXT? Answer.

The FREETEXT predicate is less specific and thus returns more rows than the CONTAINS predicate. It searches for the values that match the meaning of a phrase and not just exact words. The form is much simpler than the form of the CONTAINS predicate: SELECT…FROM…WHERE FREETEXT(FTcolumn, ‘SearchWord1 SearchWord2’). With this, you are searching for rows where the FTcolumn includes any of the inflectional forms and any of the defined synonyms of the words SearchWord1 and SearchWord2.

Hide the Answer.

Question 33

How many full-text search and how many semantic search functions are supported by SQL Server? Answer.

SQL Server supports two full-text search (CONTAINSTABLE and FREETEXTTABL) and three semantic search functions (SEMANTICKEYPHRASETABLE, SEMANTICSIMILARITYTABLE, and SEMANTICSIMILARITYDETAILSTABLE)

Hide the Answer.

Question 34

How can you get an XSD schema together with an XML document from your SELECT statement? Answer.

You should use the XMLSCHEMA directive in the FOR XML clause. For Example:

SELECT [Customer].custid AS [custid], [Customer].companyname AS [companyname], [Order].orderid AS [orderid], [Order].orderdate AS [orderdate]
FROM Sales.Customers AS [Customer]
INNER JOIN Sales.Orders AS [Order] ON [Customer].custid = [Order].custid
WHERE 1 = 2
FOR XML AUTO, ELEMENTS, XMLSCHEMA('TK461-CustomersOrders');

Hide the Answer.

Question 35

What do you do in the Return clause of the FLWOR (For, Let, Where, Order by, Return) expressions? Answer.

The return clause is evaluated once per iteration, and the results are returned to the client in the iteration order. With this clause, you format the resulting XML.

Hide the Answer.

Question 36

What would be the result of the expression (12, 4, 7) != 7? Answer.

The result would be true. This is why:

Boolean predicates select all nodes for which the predicate evaluates to true. XQuery supports logical and and or operators. However, you might be surprised by how comparison operators work. They work on both atomic values and sequences. For sequences, if one atomic value in a sequence leads to a true exit of the expression, the whole expression is evaluated to true. Look at the following example.

DECLARE @x AS XML = N'';
SELECT @x.query('(1, 2, 3) = (2, 4)'); -- true
SELECT @x.query('(5, 6) < (2, 4)'); -- false
SELECT @x.query('(1, 2, 3) = 1'); -- true
SELECT @x.query('(1, 2, 3) != 1'); -- true

Hide the Answer.

Question 37

Which XML data type method would you use to retrieve scalar values from an XML instance? Answer.

The value() XML data type method retrieves scalar values from an XML instance.

Hide the Answer.

Question 38

Can a table or column name contain spaces, apostrophes, and other nonstandard characters? Answer.

Yes, table and column names can be delimited identifiers that contain nonstandard characters.

Hide the Answer.

Question 39

What types of table compression are available? Answer.

You can use either page or row compression on a table. Page compression includes row compression.

Hide the Answer.

Question 40

How does SQL Server enforce uniqueness in both primary key and unique constraints? Answer.

SQL Server uses unique indexes to enforce uniqueness for both primary key and unique constraints.

Hide the Answer.

Question 41

Can a primary key on one table have the same name as the primary key in another table in the same database? Answer.

No, all table constraints must have unique names in a database.

Hide the Answer.

Question 42

Must a view consist of only one SELECT statement? Answer.

Technically, yes, but a workaround to this is that you can unite (using the UNION statement) multiple SELECT statements that together produce one result set.

Hide the Answer.

Question 43

What types of views are available in T-SQL? Answer.

You can create regular views, which are just stored SELECT statements, or indexed views (Create the view first, then index the view by CREATING A UNIQUE CLUSTERED INDEX ), which actually materialize the data, in addition to partitioned views (If the tables are in one database or at least on one instance of SQL Server, it is called a partitioned view or a local partitioned view. If the tables are spread across multiple SQL Server instances, the view is called a distributed partitioned view).

Hide the Answer.

Question 44

What type of data does an inline function return? Answer.

Inline functions return tables, and accordingly, are often referred to as inline table-valued functions.

Hide the Answer.

Question 45

What type of view can an inline function simulate? Answer.

An inline table-valued function can simulate a parameterized view—that is, a view that takes parameters.

Hide the Answer.

Question 46

Does a synonym store T-SQL or any data? Answer.

No, a synonym is just a name. All that is stored with a synonym is the object it refers to.

Hide the Answer.

Question 47

Can synonyms be altered? Answer.

No, to change a synonym, you must drop and recreate it.

Hide the Answer.

Question 48

Why is it recommended to specify the target column names in INSERT statements? Answer.

Because then you don’t care about the order in which the columns are defined in the table. Also, you won’t be affected if the column order is rearranged due to future definition changes, in addition to when columns that get their values automatically are added.

Hide the Answer.

Question 49

What is the difference between SELECT INTO and INSERT SELECT? Answer.

SELECT INTO creates the target table and inserts into it the result of the query. INSERT SELECT inserts the result of the query into an already existing table.

Hide the Answer.

Question 50

Which table rows are updated in an UPDATE statement without a WHERE clause? Answer.

All table rows.

Hide the Answer.

Question 51

Can you update rows in more than one table in one UPDATE statement? Answer.

No, you can use columns from multiple tables as the source, but update only one table at a time.

Hide the Answer.

Question 52

Which rows from the target table get deleted by a DELETE statement without a WHERE clause? Answer.

All target table rows.

Hide the Answer.

Question 53

What is the alternative to a DELETE statement without a WHERE clause? Answer.

The TRUNCATE statement. But there are a few differences between the two that need to be considered.

Hide the Answer.

Question 54

How many columns with an IDENTITY property are supported in one table? Answer.

Question 55

How do you obtain a new value from a sequence? Answer.

With the NEXT VALUE FOR function.

Hide the Answer.

Question 56

What is the purpose of the ON clause in the MERGE statement? Answer.

The ON clause determines whether a source row is matched by a target row, and whether a target row is matched by a source row. Based on the result of the predicate, the MERGE statement knows which WHEN clause to activate and as a result, which action to take against the target.

Hide the Answer.

Question 57

What are the possible actions in the WHEN MATCHED clause? Answer.

UPDATE and DELETE.

Hide the Answer.

Question 58

How many WHEN MATCHED clauses can a single MERGE statement have? Answer.

Two—one with an UPDATE action and one with a DELETE action.

Hide the Answer.

Question 59

How many OUTPUT clauses can a single statement have? Answer.

Two—one with INTO and one without INTO.

Hide the Answer.

Question 60

How do you determine which action affected the OUTPUT row in a MERGE statement? Answer.

Use the $action function.

Hide the Answer.

Question 61

Why is it important for SQL Server to maintain the ACID quality of transactions? Answer.

To ensure that the integrity of database data will not be compromised.

Hide the Answer.

Question 62

How does SQL Server implement transaction durability? Answer.

By first writing all changes to the database transaction log before making changes to the database data. In short, ACID is implemented as follows:

A - through Transaction
C - through constraints
I - through locking and versioning
D - through transaction log

Hide the Answer.

Question 63

How many ROLLBACKs must be executed in a nested transaction to roll it back? Answer.

Only one ROLLBACK. A ROLLBACK always rolls back the entire transaction, no matter how many levels the transaction has.

Hide the Answer.

Question 64

How many COMMITs must be executed in a nested transaction to ensure that the entire transaction is committed? Answer.

One COMMIT for each level of the nested transaction. Only the last COMMIT actually commits the entire transaction.

Hide the Answer.

Question 65

Can readers block readers? Answer.

No, because shared locks are compatible with other shared locks.

Hide the Answer.

Question 66

Can readers block writers? Answer.

Yes, even if only momentarily, because any exclusive lock request has to wait until the shared lock is released.

Hide the Answer.

Question 67

If two transactions never block each other, can a deadlock between them result? Answer.

No. In order to deadlock, each transaction must already have locked a resource the other transaction wants, resulting in mutual blocking.

Hide the Answer.

Question 68

Can a SELECT statement be involved in a deadlock? Answer.

Yes. If the SELECT statement locks some resource that keeps a second transac- tion from finishing, and the SELECT cannot finish because it is blocked by the same transaction, the deadlock cycle results.

Hide the Answer.

Question 69

If your session is in the READ COMMITTED isolation level, is it possible for one of your queries to read uncommitted data? Answer.

Yes, if the query uses the WITH (NOLOCK) or WITH (READUNCOMMITTED) table hint. The session value for the isolation level does not change, just the characteristics for reading that table.

Hide the Answer.

Question 70

Is there a way to prevent readers from blocking writers and still ensure that readers only see committed data? Answer.

Yes, that is the purpose of the READ COMMITTED SNAPSHOT option within the READ COMMITTED isolation level. Readers see earlier versions of data changes for current transactions, not the currently uncommitted data.

Hide the Answer.

Question 71

How can you add custom error messages? Answer.

You can use the system stored procedure sp_addmessage to add your own custom error messages.

Hide the Answer.

Question 72

What is severity level 0 used for? Answer.

When you issue a RAISERROR with severity level 0, only an informational message is sent. If you add WITH NOWAIT, the message will be sent without waiting in the output buffer.

Hide the Answer.

Question 73

What are the main advantages of using a TRY/CATCH block over the traditional trapping for @@ERROR? Answer.

The main advantage is that you have one place in your code that errors will be trapped, so you only need to put error handling in one place.

Hide the Answer.

Question 74

Can a TRY/CATCH block span batches? Answer.

No, you must have one set of TRY/CATCH blocks for each batch of code.

Hide the Answer.

Question 75

Can you generate and execute dynamic SQL in a different database than the one your code is in? Answer.

Yes, because the USE command can be inserted into a dynamic SQL batch.

Hide the Answer.

Question 76

What are some objects that cannot be referenced in T-SQL by using variables? Answer.

Objects that you cannot use variables for in T-SQL commands include the database name in a USE statement, the table name in a FROM clause, column names in the SELECT and WHERE clauses, and lists of literal values in the IN() and PIVOT() functions.

In other words, Dynamic SQL is useful because T-SQL will not permit the direct replacement of many parts of commands with variables, including:
■ The database name in the USE statement.
■ Table names in the FROM clause.
■ Column names in the SELECT, WHERE, GROUP BY, and HAVING clauses, in addition to the ORDER BY clause.
■ Contents of lists such as in the IN and PIVOT clauses.

Hide the Answer.

Question 77

How can a hacker detect that SQL injection may be possible? Answer.

By inserting a single quotation mark and observing an error message.

Hide the Answer.

Question 78

Where is the injected code inserted? Answer.

Between an initial single quotation mark, which terminates the data input string, and a final comment mark, which disables the internal terminating single quotation mark.

Hide the Answer.

Question 79

How can you pass information from sp_executesql to the caller? Answer.

Use one or more OUTPUT parameters. You can also persist the data in a perma- nent or temporary table, but the most direct method is through the OUTPUT parameter.

Hide the Answer.

Question 80

How does sp_executesql help stop SQL injection? Answer.

You can use sp_executesql to parameterize user input, which can prevent any injected code from being executed.

Hide the Answer.

Question 81

What is the result of the parsing phase of query execution? Answer.

The result of this phase, if the query passed the syntax check, is a tree of logical operators known as a parse tree.

Hide the Answer.

Question 82

How would you quickly measure the amount of disk IO a query is performing? Answer.

You should use the SET STATISTICS IO command.

Hide the Answer.

Question 83

How can you get an estimated execution plan in XML format for further analysis? Answer.

You can use the SET SHOWPLAN_XML command.

Hide the Answer.

Question 84

Which DMO gives you detailed text of queries executed? Answer.

You can retrieve the text of batches and queries executed from the sys.dm_exec_sql_text DMO.

Hide the Answer.

Question 85

What are the two types of parameters for a T-SQL stored procedure? Answer.

A T-SQL stored procedure can have input and output parameters.

Hide the Answer.

Question 86

Can a stored procedure span multiple batches of T-SQL code? Answer.

No, a stored procedure can only contain one batch of T-SQL code.

Hide the Answer.

Question 87

What are the two types of DML triggers that can be created? Answer.

You can create AFTER and INSTEAD OF DML-type triggers.

Hide the Answer.

Question 88

If an AFTER trigger discovers an error, how does it prevent the DML command from completing? Answer.

An AFTER trigger issues a THROW or RAISERROR command to cause the trans- action of the DML command to roll back.

Hide the Answer.

Question 89

What are the two types of table-valued UDFS? Answer.

You can create inline or multistatement table-valued UDFs.

Hide the Answer.

Question 90

What type of UDF returns only a single value? Answer.

A scalar UDF returns only a single value.

Hide the Answer.

Question 91

What kind of clustering key would you select for an OLTP environment? Answer.

For an OLTP environment, a short, unique, and sequential clustering key might be the best choice.

Hide the Answer.

Question 92

Which clauses of a query should you consider supporting with an index? Answer.

The list of the clauses you should consider supporting with an index includes, but is not limited to, the WHERE, JOIN, GROUP BY, and ORDER BY clauses.

Hide the Answer.

Question 93

How would you quickly update statistics for the whole database after an upgrade? Answer.

You should use the sys.sp_updatestats system procedure.

Hide the Answer.

Question 94

What are the commands that are required to work with a cursor? Answer.

DECLARE, OPEN, FETCH in a loop, CLOSE, and DEALLOCATE.

Hide the Answer.

Question 95

When using the FAST_FORWARD option in the cursor declaration command, what does it mean regarding the cursor properties? Answer.

That the cursor is read-only, forward-only.

Hide the Answer.

Question 96

How do you create a local temporary table, and how do you create a global one? Answer.

You name a local temporary table by using a single number sign as a prefix and a global one with two number signs as a prefix.

Hide the Answer.

Question 97

Can you name constraints in local temporary tables and in table variables? Answer.

You can name constraints in local temporary tables, although it’s not recommended because it can generate name conflicts. You cannot name constraints in table variables.

Hide the Answer.

Question 98

How would you determine whether SQL Server used the batch processing mode for a specific iterator? Answer.

You can check the iterator’s Actual Execution Mode property.

Hide the Answer.

Question 99

Why might you prefer using plan guides instead of optimizer hints? Answer.

With plan guides, you do not need to change the query text.

Hide the Answer.

SSRS Interview Questions - Part 3 of 3 (On Applications from TK 70-448)

SSAS Application Questions

Case Scenario 1: Building Reports for the AdventureWorks Intranet

You have just successfully installed SSRS 2008. You have also created two shared data sources: one configured to retrieve data from the AdventureWorks relational database and the other to retrieve information from a sales and marketing data mart stored in an OLAP database. The data mart is populated once a week. The schemas of the relational and OLAP databases are the same as the sample databases provided by SQL Server 2008. You are the main developer of a set of reports that will be used in the AdventureWorks portal, and you need to handle the following requirements:
  1. Your end users want a report that gives them near real-time information about sales by Product Category, Product Subcategory, and Product model. The report should show only the past week’s sales and should have only three columns: Name, Quantity, and Amount. Users also want the ability to drill through the report from summary information to greater detail. You do not want to use the Report Wizard. Given these requirements, what is the best way to create the report? Answer.
  2. You can add a new report to the SSRS solution to satisfy this user requirement. Create a dataset that uses the AdventureWorks relational database. In the dataset’s query, filter the information to retrieve only the last week’s sales by product category, subcategory, and model. Use a Table data region and create two additional groupings, one by category and another by subcategory. Set the initial visibility status of the Subcategory and Detail rows to hidden, the toggle property of the Subcategory grouping to change based on Category, and the toggle property of the Detail grouping to change based on Subcategory.
    Hide the Answer.

  3. Your end users want a pivot table report that has Categories, Subcategories, and Models as columns and Year, Quarter, and Month as rows. The cell data should be filled with sales amount information. The information does not need to be real time. Given these requirements, what is the best way to create the report? Answer.

  4. For this requirement, you can use the Report Wizard to create the report. On the Data Source page, select the multidimensional database, and then use the MDX Query Builder to create the MDX query. In the Data pane, drag the Product Model Categories hierarchy, the Date.Calendar hierarchy, and the SalesAmount measure onto the Results pane. Remove the Calendar Semester and Calendar data levels. Select a matrix report, and then assign the date-related information to columns and the product category information to the rows. Last, assign the amount as detail information.
    Hide the Answer.

Case Scenario 2: Creating a Reporting Services Infrastructure

You just successfully installed SSRS 2008. You also created two shared data sources: one configured to retrieve data from the AdventureWorks relational database and the other configured to retrieve information from a marketing/sales data mart stored in an online analytical processing (OLAP) database. The data mart is populated once a week. The schemas of the relational and OLAP databases are the same as the sample databases provided by SQL Server 2008. You will be the main developer of a set of reports that will be used in the AdventureWorks portal, and you need to address the following requirements:
  1. End users want the ability to create their own reports. The users are knowledge workers who have Excel expertise but no database experience. What is the best way to create the reports, without giving end users direct access to the database? How will you build the infrastructure? Answer.
  2. You can configure report models that will let users create their own reports. You need to create two separate models, one for the relational engine and another for the SSAS database. The relational database model should be created and configured in BIDS, and the OLAP data source should be created from SSMS or Report Manager.
    Hide the Answer.

  3. In the previous version of the reports, users had a set of reports that were identical to each other except that each report grouped information at different levels of the organization. Users still want the flexibility to look at the information grouped in different ways, but you want to build a single report rather than multiple reports. Given these requirements, what is the best way to create the new report? Answer.

  4. You could create a parameterized report that prompts the user for the desired level of aggregation and then dynamically creates the group by statement. Alternatively, you could use the same query for all the aggregation levels, use a table or matrix report item, and hide the grouping level based on the user selection. The main advantage of the first approach is that it pulls only the required data when the report is executed. The main advantage of the second option is that it allows the reuse of the cache if the report uses report caching.
    Hide the Answer.

Case Scenario 3: Managing the Report Environment for Adventure Works

Reporting Services has been deployed in Adventure Works, and multiple departments are requesting access to the new server. However, some employees need to access reports from departments other than their own. At the same time, some users from the call center require the flexibility to create reports based on their customers.
Adventure Works uses a mixed database environment, with most applications running on SQL Server 2008 but some running on Oracle.
  1. What is the general security infrastructure required to support the reporting needs of the Adventure Works departments and employees? Answer.
  2. In general, because item-level roles are cumulative, for each department’s folder, you can assign the Browser role to the Active Directory group that represents the employees of each division. Additionally, you can add the Browser role to each person who needs access to more than one department’s folder.
    Hide the Answer.

  3. From a data source security standpoint, how can you satisfy the requirements to access both Oracle and SQL Server from Reporting Services? Answer.

  4. To allow access to the Oracle databases, you will need to define a user with low-level privileges and store the credentials for that user in Report Server. For the SQL Server databases, given the fact that Adventure Works uses a Windows domain, you can enable Kerberos and use the Integrated Windows Authentication mechanism.
    Hide the Answer.

Case Scenario 4: Scaling Out Your SSRS Servers

As you begin the process of setting up a SQL Server 2008 BI environment, you are planning out your hardware architecture. Your solution will consist of an SSRS installation that will access online analytical processing (OLAP) data in SSAS built from a SQL Server data mart. Your manager has approved two blade servers for SSRS so that the service can be configured for scale-out deployment—that will help to balance the workload and will provide some load balancing. Additionally, your system administrator has set up Network Load Balancing (NLB) to distribute the report requests. How will you handle the following two setup requirements?
  1. Your SSRS implementation requires that you set up a scale-out deployment of SSRS on the two allocated servers. You have already installed the first SSRS instance on one of the servers that points to a Report Server database on your database server. You are now ready to install an instance of SSRS on the second computer. How do you use command-line utilities to install and configure this instance to be part of the SSRS scale-out deployment? Answer.
  2. Because the first SSRS server has been installed and configured, your next task is to install an unconfigured instance of SSRS, without having the installation use the default configuration, on the second server. You will then need to configure the virtual directories through Reporting Services Configuration Manager and set the service accounts. Before joining the new instance to the scale-out deployment, connect the SSRS instance to the same Report Server database by using RSConfig.exe with the /c parameter. At this point, you should run the RSKeyMgmt.exe tool with the /j command parameter to join an existing SSRS. You need to run this command statement on the server that is already configured and then reference the new instance that will join the existing scale-out deployment.
    Hide the Answer.

  3. To prevent a single point of failure for SSRS, you also need to handle failures at the report server catalog database level. In the case of a server hardware failure, you do not want the SSRS implementation to also fail. Which is the best high-availability technology to use for this situation and why? Answer.

  4. High availability at the database level can be handled by clustering, database mirroring, replication, or log shipping. However, the best choice is to use clustering. Clustering will prevent a server hardware failure from affecting the SSRS implementation, as the Database Engine will fail over to another available node in the cluster. In addition, this will be seamless to Reporting Services, as the database connection uses the virtual instance name of the database server, which also moves to the other server. With the other technologies, the SSRS implementation would need the catalog database connection to be repointed to the backup database from the mirroring, log shipping, or replication.
    Hide the Answer.

SSRS Interview Questions - Part 2 of 3 (On Comprehension from TK 70-448)

SSRS Comprehension Questions

Question 1

You want to include an image in a report. How do you display the Image Properties dialog box? Answer.
When you drag an image item from the Toolbox window to the Report Designer, the Image Properties dialog box automatically opens.
Hide the Answer.

Question 2

You want to configure an amount to display the value in a currency format. Which property do you use? Answer.
To configure an amount to display a value in a currency format, select the report item, and then set the format property to C or c.
Hide the Answer.

Question 3

What are data regions? Answer.
Data regions are report items that display repeated rows of summarized information from datasets.
Hide the Answer.

Question 4

You want to generate a report that is formatted as a chart. Can you use the Report Wizard to create such a report? Answer.
You need to select the use Existing Tables method when you build the cube from the bottom up.
Hide the Answer.

Question 5

You want to use BIDS to deploy a report to a different server than the one you chose in the Report Wizard. How can you change the server URL? Answer.
You can right-click the project in Solution Explorer and then change the TargetServer URL property.
Hide the Answer.

Question 6

Which rendering formats are affected by the PageSize properties? Answer.
Because only the Adobe PDf file, Word, and Image rendering extensions use physical page breaks, they are the only formats that are affected by the PageSize properties.
Hide the Answer.

Question 7

Can you use a stored procedure to provide data to an SSRS report? Answer.
Yes, you can use a stored procedure to provide data to an SSRS report by configuring the dataset to use a stored procedure command type. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the fi rst one is used for the report dataset.
Hide the Answer.

Question 8

You want to use a perspective in an MDX query. How do you select the perspective? Answer.
Use the Cube Selector in the MDX Query Designer to select a perspective.
Hide the Answer.

Question 9

Can you use data mining models in SSRS? Answer.
Yes, you can use the DMX Designer to create data mining queries for SSRS reports. However, do not forget to flatten the result set returned by the DMX query.
Hide the Answer.

Question 10

You want your report to display a hyperlink that will take users to your intranet. How do you configure such a hyperlink? Answer.
Create a text box item, set the action to Go To uRL, and then confi gure the URL.
Hide the Answer.

Question 11

You want a report to display Sales by Category, SubCategory, and Product. You want users to see only summarized information initially but to be able to display the details as necessary. How would you create the report? Answer.
Group the Sales information by Category, SubCategory, and Product. Hide the SubCategory group and set the visibility to toggle based on the Category item. Hide the Product group and set the visibility to toggle based on the SubCategory item.
Hide the Answer.

Question 12

You want to create an Excel interactive report from SSRS. In SSRS, can you create the same interactive experience in Excel that you would have on the Web? Answer.
No, you cannot create the same experience with SSRS. Although the design of interactive reports is closely related to the HTML rendering format. Microsoft Office Excel, Microsoft Office Word, Image, Acrobat (PDf) file, and Web Archive reports do not support the same type of interaction; details are hidden by default. XML and CSV reports display all information by default.
Hide the Answer.

Question 13

What is the main difference between a Matrix report item and a Table report item? Answer.
The main difference between a Matrix and a Table report item is in the initial template. Actually, both report items are just templates for the Tablix data region.
Hide the Answer.

Question 14

When you do not use report caching, is it better to use parameters to filter information in the query or to use filters in the dataset? Answer.
From a performance perspective, it is better to use parameters because they let SSRS pull fi ltered data from the data source. In contrast, when you use filters, the queries retrieve all data and then filter the information in an additional step.
Hide the Answer.

Question 15

How do you configure a running aggregate in SSRS? Answer.
You can use the RunningValue function to confi gure a running aggregate.
Hide the Answer.

Question 16

What is the main purpose of a report parameter? Answer.
The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select.
Hide the Answer.

Question 17

What is the main purpose of a query parameter? Answer.
The main purpose of a query parameter is to filter data in the data source.
Hide the Answer.

Question 18

You want your users to select a parameter from a list of values in a list box. How should you configure the parameter? Answer.
You should create a data source that contains the possible values and then bind the data source to the parameter.
Hide the Answer.

Question 19

What is the main benefit of using embedded code in a report? Answer.
The main benefit of using embedded code in a report is that the code you write at the report level can be reused in any expression in the report.
Hide the Answer.

Question 20

What programming language would you use to create embedded functions in SSRS? Answer.
An SSRS report supports only Visual Basic .NET embedded code.
Hide the Answer.

Question 21

How do you reference an embedded function in a report expression? Answer.
Use the Code prefix and the name of the function to reference an embedded function in a report expression.
Hide the Answer.

Question 22

Which of the following are valid options for deploying a report? (Choose all that apply.)
  1. With BIDS
  2. With the Computer Management console
  3. With the .NET START command
  4. With SSMS
  5. With Report Manager
Answer.
The correct answers are a and e, BIDS and Report Manager.
Hide the Answer.

Question 23

Why should you not overwrite a shared data source in production? Answer.
When Deploying reports, for the OverwriteDataSources property, you typically will not want to overwrite a data source when you deploy a report in the production environment because an administrator might have changed the data source properties to connect to a production database server rather than the server the developer used when authoring and debugging the report.
Hide the Answer.

Question 24

Can you edit a report that an end user created by using Report Builder in BIDS? Answer.
Yes, if an end user created a report by using Report Builder in BIDS and saved the report definition file, you can open the file in BIDS and edit it.
Hide the Answer.

Question 25

How can you manage reports from your application if the report server is deployed in SharePoint integrated mode? Answer.
Use the ReportService2006 endpoint of the SSRS Web service if your report server is deployed in SharePoint integrated mode.
Hide the Answer.

Question 26

In which processing mode of a report viewer control can you use the full functionality of your report server? Answer.
You should use the remote processing mode to use the full functionality of your report server.
Hide the Answer.

Question 27

What types of roles are available in SSRS 2008, and what are their purposes? Answer.
Item-level roles and system-level roles are the two types of roles available in SSRS 2008. An item-level role is a collection of tasks related to operations on an object of the report object hierarchy of SSRS 2008. A system-level role is a collection of tasks related to operations on server objects outside the report object hierarchy of SSRS 2008.
Hide the Answer.

Question 28

Can a user or group belong to more than one item-level or system-level role? Answer.
Yes, in SSRS 2008, a user or group can have more than one association to a system-level or an item-level role.
Hide the Answer.

Question 29

When storing the credentials of a data source in the server, are those credentials safe? Answer.
Yes, the data source credentials are safe because Reporting Services encrypts them and stores them in the ReportServer SQL Server database.
Hide the Answer.

Question 30

What happens if you do not specify a parameter value in a subscription and the parameter does not have a default value? Answer.
If you do not specify a parameter value in a subscription and the parameter does not have a default value, the execution of the report will fail.
Hide the Answer.

Question 31

You want to create a subscription to a report. However, when you right-click the Subscription subfolder of the report, you notice that the new Subscription option is dimmed. What is wrong? Answer.
When the new Subscription option is dimmed, the report probably does not use stored credentials for accessing the data. SSRS needs these credentials stored in its own ReportServer database to execute a report on a schedule.
Hide the Answer.

Question 32

What can you do if your query with properties for a data-driven subscription does not provide values for all of the subscription properties? Answer.
If your query with properties for a data-driven subscription does not provide values for all of the subscription properties, you can use text and default values instead. These values are then used for parameters of all subscriptions you get from the query.
Hide the Answer.

Question 33

What mechanisms do you identify to reduce the overhead of Reporting Services data sources? Answer.
Snapshots and cached reports can help reduce the processing pressure on data sources and improve report response time.
Hide the Answer.

Question 34

Can you always create a cache of a report? Answer.
No, you can create a cache of a report only when certain requirements, such as having credentials stored in the Report Server, are met.
Hide the Answer.

Question 35

Can you edit the .rdl code associated with a linked report? Answer.
No, because a linked report has no .rdl code of its own. It refers to the .rdl code of the base report.
Hide the Answer.

Question 36

Which of the tools and utilities can change or create the virtual directories for the report server and Report Manager after installation? Answer.
Only Reporting Services Configuration Manager can enable and name the virtual directories for the report server and Report Manager.
Hide the Answer.

Question 37

What is the file name extension for an encryption key backup? Answer.
Encryption key backups have an .snk file name extension.
Hide the Answer.

Question 38

What are the three SSRS command-line utilities and their primary functions? Answer.
RSConfig.exe is used to define the connection properties from the SSRS instance to the Report Server database; RSKeyMgmt.exe performs encryption key operations and scale-out deployment setup; RS.exe runs Report Server Script files that can perform report deployment and management operations.
Hide the Answer.

Question 39

If you want to disable one of the rendering options in the Save As drop-down list when viewing a report through Report Manager, where do you do that? Answer.
The visibility property for any of the rendering devices can be changed by modifying the RSReportServer.confi g file and locating the tag for the specific device.
Hide the Answer.