Basic SQL Server

Data regions are report items used to display data from a single dataset. You can perform grouping, sorting and various aggregate functions on data in data region. In SSRS 2005, there were 4 data regions:-

1. Table

2. Matrix

3. List

4. Chart

While in SSRS 2008, there are one additional data region namely Gauge.

Let’s explain each one of them:

1. Table - Table Data region has fixed tabular structure i.e. fixed number of columns. It is useful for displaying data grouped by row. You can have maximum of 1 report item per cell. The size of table depends on number of rows dataset fetches i.e., if number of rows returned by dataset is more; it can expand to multiple pages.

2. Matrix – A matrix data region display data in pivot table format, hence also popularly known as pivot table or crosstab report. It has minimum of one row group and one column group. The size of matrix data region depends on columns and rows fetched.

3. List - A list data region is free layout. It is useful for complex reporting resign. The list can be used to display multiple table and matrix. Each getting data from different dataset.

4. Chart – This data region is for displays the data graphically i.e., in form of chart. A various chart types are available in SSRS 2008 namely line, pie chart, columns etc.

5. Gauge - This can be used in a table or matrix to show the relative value of a field in a range of values in the data region. You can also add a gauge to the design surface to show a single relative value.

There are various ways in which you can enhance your report:

1. Display your data in graphic format using Chart Region.

2. Use sorting.

3. If couple of reports are related, you can make them interactive using connect them using bookmark link, hyper link or drill through report link.

4. Adding sub-report. Sub-report is a stand-alone report which can be link to another report based on its content using parameter.

5. Add custom fields. Custom fields provide with same functionality as alias columns provide in SQL server query. It is the timing of the operation that differs from the alias columns. The calculation is performed on dataset by report server.

6. Using expression.

7. Using custom code. SSRS allows including custom code written in VB.Net. 8. Add document map (navigational links to report item once report is rendered) to report.

There are 3 ways in which you can integrate reports into your application:-

1. Navigating to URL i.e. https:\servernamereportservernamereportname – This is simplest and most popular way. A separate login might be required since we are directly calling the report from report server. Address of report server gets expose to user.

2. Using IFrame, Browser control or Report Viewer Control – In this approach, we embed the URL of report server in our application, hence address of reportserver is not exposed. No separate window opens. A user does not come to know that he has moved to different server.

3. Programmatically sending a web request using SOAP to report server.

Yes, we can. SSRS allows developer to add custom code in your report. You can write the code directly into embedded VB.Net and call it using property expression or you can write a custom class library and refer it in report server. The advantage of first method is that it is simple and easy to use but disadvantage is that it is available for that report only. While the second method has advantage of being available for multiple reports but it has much of configuration overhead.

To write custom code, right click on Report Designer outside report body and select Properties and go to Code tab and you can write custom code here.

To add custom class library, right click on Report Designer outside report body and select Properties and go to Reference tab and add the reference by browsing to the assembly of your class library. Note that you need to create class library and then compile it before referencing it in your SSRS report.

Reporting services maintain role based security. When a user logs into reporting services, a Report Manager (whose duty is to maintain security of Reporting Services) first checks the identity of user and then determine what rights he have to perform on report.

Report Manager manages the security at 2 levels –

1. System-level – Administer the report server globally

2. Item-level – Security at report and dataset level

System-level roles are:-

1. System Administrator – can manage report server and report manager security

2. Site User - view basic information like report properties and schedules.

Item-level roles – User can use any of predefined item-level roles or create their own roles by using combination of predefined item-level roles.

Pre-defined Item-level roles are:-

1. Browser – can navigate to report and run them.

2. My Reports – these users’ rights is restricted to reports present in their MyReports folder. However, they can create, view and manage reports in their folder.

3. Publisher – As name suggest, publisher user has rights to publish reports to Reporting Server database.

4. Content Manager – has all permission at item-level.

SQL Injection is an attack in which attacker take the advantage of insecure application over internet by running the SQL command against the database and to steal information from it that too using GUI of the website.

This attack can happen with the applications in which SQL queries are generated in the code.

The attacker tries to inject their own SQL into the statement that the application will use to query the database.

For example suppose the below query string is used for a search feature in a website and a user may have the inserted “Arpit” as the keyword to search. If in the code behind the keyword is directly used into the SQL statement, it will be like.

String sql = “Select EmpName, City from EmployeeMaster where EmpName like ‘%” + txtSearch.Text + “%’”;

But the attacker might enter the keyword like

‘ UNION SELECT name, type, id from sysobjects;--

This way attacker can get details of all the objects of the application database and using that attacker can steal further information.

View result set doesn’t save anywhere on disk and executes the query definition whenever they are called, while materialized view are disk based and its result set table is updated periodically.

Materialized view is similar to regular views but the output of select query has been saved to a table.

View shows the latest data all the time while the materialized view only shows the fresh data after its result table is updated either by setting a schedule or based on the change in the underlying tables.

The performance of the view depends on how good the selected statement the view has. If the select statement has too many joins then it the view will perform poorly.

While in the case of materialized view, we are querying a table, which may also be indexed, that increase its performance.

When a complex SQL statement has number of joins then it can be made easier by using Common Table Expression.

Consider the following SQL statement.

SELECT * FROM (

SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp

left join Department dept on emp.DeptID = dept.DeptID) E

WHERE E.Gender = ‘Male’

ORDER BY T.EmpName

The syntax of CTE is as follow

- The CTE Name (followed by WITH keyword)

- The Column List (Optional)

- The Query (Appears within parentheses after the AS keyword)

If we write the above messy query using CTE it would be like

With E(EmpName, Department, Gender)

AS

(

SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp

left join Department dept on emp.DeptID = dept.DeptID

)

SELECT * FROM E

WHERE E.Gender = ‘Male’

ORDER BY E.EmpName

This way the query can be made more readable and easy to understand.

A table can have only one Clustered Index at a time which is generally created on primary key and can have more than one non clustered indexes (maximum up to 999)

The leaf level of clustered index is actual data pages of the table. Whereas in case of non-clustered index the leaf level is a pointer to the data.

Non-clustered index is faster than clustered index because when we use DML statement on clustered index, performance issues may occurred since it has to update the index every time a DML statement is executed.

Syntax of creative clustered / non clustered index is as follow

CREATE [CLUSTERED | NON CLUSTERED] INDEX index_name

ON <object> (column [ASC | DESC] [,…n])