Explain various data region available in SSRS with their use..

Explain various data region available in SSRS with their use.

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.

What are various ways to enhance the SSRS report? Explain.

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.

Sql Server interview - July 7, 2011 by Swati Parakh

What are various aggregate functions that are available?

The following are various aggregate functions available:-
1. SUM
2. AVG
3. COUNT
4. COUNTDISTINCT
5. MAX
6. MIN
7. STDEV
8. STDEVP
9. VAR
10. VARP

By default, SUM is the aggregate function used for numeric data type.

How do you integrate the SSRS reports in your application?

There are 3 ways in which you can integrate reports into your application:-
1. Navigating to URL i.e. https:\\servername\reportservername\reportname – 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.

Explain use of Expression builder.

Expressions provide us with flexibility to customize our report. It is written in Visual basic and is used throughout the report to to retrieve, calculate, display, group, sort, filter, parameterize, and format the data in a report. They start with equal sign (=).

S.No.	Functionality	Property, Context and Dialog Box	Expression
1	Format data in a text box depending on value	Colour for a placeholder inside of a text box in the details row for a Tablix	=IIF(Fields!TotalDue.Value < 10000,"Red","Black")
2	Dynamic page header or footer content.	Value for a placeholder inside of a text box that is placed in the page header or footer.	="Page " & Globals!PageNumber & " of " & Globals!TotalPages
3	Specify page breaks for every 20 rows in a Tablix with no other groups.	Group expression for a group in a Tablix.	=Ceiling(RowNumber(Nothing)/20)
4	Shows the user ID of the person running the report	Value	=User!UserID
5	To get first day of the month	Value	=DateSerial(Year(Today()),Month(Today()),1)
6	To get the current date	Value	=Today()
7	To get last day of the month	Value	=DateAdd("d",-1,DateSerial(Year(Today()),Month(Today())+1,1))

Sql Server interview - July 10, 2011 by Swati Parakh

Difference between drill down and drill through report.

Both the drill down and drill through report provide interactive functionality to the SSRS report. The differences are as follows:-

Trait	Drill Down	Drill Through
Retrieves Data	Data retrieved at the same time as main report	Data retrieved one click on link of drill through report
Is processed and rendered when	With the main report	When link is clicked
Performance	Slower since retrieves all data with main report	Faster (but does not retrieve all data with main report)
Is displayed	Within main report	Separately either in separate window or tab

What’s the use of custom fields in report?

Custom fields can be defined as alias column of the report since the operation is performed on report server rather than on database server. The custom field is very useful for the data manipulation like adding some fields whose value can be calculated based on expression, text e.g. instead of CName fetched from database, I want the dataset to display Customer Name etc.

We can add custom fields as right click on dataset, select add in Dataset window. The New field dialog box will open, we can add name of custom field and also mention whether it is database field or calculated one. If it is calculated, then we can mention the computation in this window.

Can we use custom code in SSRS? If so, explain how we can do.

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.

Sql Server interview - July 16, 2011 by Swati Parakh

Difference between report and query parameter. Why do we need different type of parameter?

	Query Parameter	Report Parameter
Defined At	Database Level	Report Level
Created	Automatically if database query or stored procedure has a parameter	Automatically if report has some query parameter and is mapped to query parameter
processed	On Database Server	On Report Server
Use	Filtering of Data, Security of Data	Manipulate data, interconnect reports, filtering data
Processing Output	Number of records returned is based on query parameter	Number of records presented is based on query. Note- Records processed on report parameter would be same as records returned based on query parameter.
Filtering data based on them	Performance is good	Full set of records is retrieved then filtered. Hence, performance is low

How does your SSRS maintain security?

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 Server interview questions and answers - submitted by Arpit Jain

What is SQL Injection?

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.

What is DBCC? Give few examples.

DBCC stands for Database Consistency Checker.

These commands are used to check the consistency of the database like validation tasks, maintenance and status checks.

For example –

1. DBCC CHECKALLOC – It is used to check that all pages are correctly allocated in database.
2. DBCC CHECKDB – It is used to check that
3. DBCC SQLPERF – It generates a report for the current usage of
4. DBCC SQLFILEGROUP – It used to check all the tables file group for any design.

What is difference between View and Materialized view?

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.

What is CTE (Common Table Expression)?

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.

What is difference between clustered and non clustered index?

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

What is use of EXCEPT clause? How it differs from NOT IN clause.

-When we combine two queries using EXCEPT clause, it will returns distinct rows from the first SELECT statement that are not returned by the second one.
-EXCEPT clause works the same way as the UNION operator of SQL and MINUS clause in Oracle.
-The syntax of EXCEPT clause is as follow
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

-The difference between EXCEPT and NOT IN clause is EXCEPT operator returns all distinct rows from the rows returned by first select statement which does not exist in the rows returned by second select statement. On the other hand “NOT IN” will return all rows from returned by first select statement which does not exist in the rows returned by second select statement.

What is difference between Index Seek vs. Index Scan?

Index Seek and Index Scan are operation for query tuning in execution plans.

Table Scan scans every record of the table. So the cost of proportional is the number of rows of that table.

The Index Scan is preferred only when the table is small.

Index Seek only touches the rows which qualify and the pages that contain that qualifying rows, so the cost of proportional is the number of qualifying rows and pages instead of the number of rows in the table.

Index seek is preferred for highly sensitive queries.

What is ROW_NUMBER function?

RANK is one of the Ranking functions which are used to give rank to each row in the result set of a SELECT statement.

For using this function first specify the function name, followed by the empty parentheses.

Then specify the OVER function. For this function, you have to pass an ORDER BY clause as an argument. The clause specifies the column(s) that you are going to rank.

For Example
SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary

In the result you will see that the highest salary got the first rand and the lowest salary got the last rank. Here the rows with equal salaries will not get same ranks.

What is Trigger?

-In SQL the Trigger is the procedural code that executed when you INSERT, DELETE or UPDATE data in the table.

-Triggers are useful when you want to perform any automatic actions such as cascading changes through related tables, enforcing column restrictions, comparing the results of data modifications and maintaining the referential integrity of data across a database.

-For example, to prevent the user to delete the any Employee from EmpDetails table, following trigger can be created.

create trigger del_emp
on EmpDetails
for delete
as
begin
rollback transaction
print "You cannot delete any Employee!"
end

-When someone will delete a row from the EmpDetails table, the del_emp trigger cancels the deletion, rolls back the transaction, and prints a message "You cannot delete any Employee!"

What is Scheduled job and how to create it?

-If we want to execute any procedural code automatically on specific time either once or repeatedly then we can create a Scheduled job for that code.
-Following are the steps to create a Scheduled Job.

1. Connect to your database of SQL server in SQL Server Management Studio.
2. On the SQL Server Agent. There you will find a Jobs folder. Right click on jobs and choose Add New.
3. A New Job window will appear. Give a related name for the job.
4. Click next on the "Steps" in the left menu. A SQL job can have multiple steps either in the form of SQL statement or a stored procedure call.
5. Click on the "Schedules" in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which sql job will run itself. You can specify recurring schedules also.

-Using scheduled job you can also create alert and notifications.

What is OPENXML in SQL Server?

-OPENXML provides an easy way to use an XML document as a data-source for your procedures.

-OPENXML data can be manipulated the same way we deal with database tables by treating xml tags in the form of columns and the value in the form of rows.

-By using OPENXML Data can be inserted or updated very quickly without multiple trips to the database.

-Example:

DECLARE @count int
DECLARE @xml varchar(5000)
SET @xml ='<Employees>
<Employee id="1">
<Name>Arpit</Name>
<Employee >1234</ Employee >
</Employee >
<Employee id="2">
<Name>Rahul</Name>
<PhoneNo>2211</PhoneNo>
</Employee >
</Employees>'

EXEC sp_xml_preparedocument @count OUTPUT, @xml
SELECT *
FROM OPENXML (@count, Employees/Employee')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index

It will give following result.
1 Arpit 1234
2 Rahul 2211

What are Sparse columns?

Sparse column is a type of column which is used to optimize storage for null values.

When a column there is big number of null then by defining that column as spars column we can save a large amount of disk space.

The drawback of sparse column is that it requires more space for the non null values. When we define a column as sparse it requires additional 4 Byte for not null values.

For example, a DATETIME column in a non-sparse column required 8 bytes of storage whereas if we define that column as a sparse column then it will require 12 bytes.

It is not possible to set ROWGUIDCOL and IDENTITY properties in sparse column.

What is RANK function?

-RANK is one of the Ranking functions which are used to give rank to each row in the result set of a SELECT statement.

-For using this function first specify the function name, followed by the empty parentheses.

-Then specify the OVER function. For this function, you have to pass an ORDER BY clause as an argument. The clause specifies the column(s) that you are going to rank.

For Example

SELECT RANK() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary

-In the result you will see that the highest salary got the first rand and the lowest salary got the last rank.

Here the rows with equal salaries will get same ranks.

-Remember that the rank depends on the row's position in the result set, not on the sequential number of the row.

What are cursors and when they are useful?

-When we execute any SQL operations, SQL Server opens a work area in memory which is called Cursor.

-When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used.

-There are two of cursors

1. Implicate Cursor
SQL Server automatically manages cursors for all data manipulation statements. These cursors are called implicit cursors.

2. Explicit Cursor
When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name.
They are managed by OPEN, FETCH and CLOSE.

%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN attributes are used in both types of cursors.

What is log shipping?

-SQL has introduced Log Shipping feature to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server Job Agents is used to make these processes automatic.

-In the case of failure the Log Shipping will not transfer the server. That means it will not redirect your application to other server. This has to be done manually.

-Log shipping synchronizes the database only. The main functions of Log Shipping are as follows:

1. To Back up the transaction log of the primary database
2. To Copy the transaction log backup to every secondary server
3. To Restore the transaction log backup on the secondary database

What is SQL Profiler?

-SQL Server provides a graphical tool which helps system administrators to monitor T-SQL statements of Database Engine.

-SQL Profiler can capture and store data related to every event to a file or a table.

-SQL Server Profiler can be used

1. To create a trace
2. To store the trace results in a table.
3. To watch the trace results when the trace runs
4. To replay the trace results
5. To start, stop, pause, and modify the trace results

What is Similarity and Difference between Truncate and Delete in SQL?

Similarity

-These both command will only delete data of the specified table, they cannot remove the whole table data structure.

Difference

-TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

-We can’t execute a trigger in case of TRUNCATE whereas with DELETE command, we can execute a trigger.

-TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.

-We can use any condition in WHERE clause using DELETE but you can't do it with TRUNCATE.

-If table is referenced by any foreign key constraints then TRUNCATE will not work.

What is Normalization of database? What are its benefits?

-Normalization is set of rules that are to be applied while designing the database tables which are to be connected with each other by relationships. This set of rules is called Normalization.

-Benefits of normalizing the database are
1. No need to restructure existing tables for new data.
2. Reducing repetitive entries.
3. Reducing required storage space
4. Increased speed and flexibility of queries.

What is Fill factor?

The 'fill factor' option indicate how full SQL Server will create each index page.

When the index page doesn’t have free space for inserting a new row, SQL Server will create new index page and transfer some rows from the previous index page to the new index page. This process is called page split.

If we want to reduce the number of page splits then we can use Fill factor option. Using Fill factor SQL will reserve some space on each index page.

The fill factor is a value from 1 through 100 that indicates the percentage of the index page to be left empty. The default value for fill factor is 0.

If the table contains the data which is not changed frequently then we can set the fill factor option to 100. When the table's data is modified frequently, we can set the fill factor option to 80% or as we want.

What are different types of replication in SQL Server?

There are three types of replication in SQL SERVER

1. Snapshot Replication.

In Snapshot Replication snapshot of one database is transferred to another database.

In this replication data can be refreshed periodically and all data will be copied to another database every time the table is refreshed.

2. Transactional Replication

In transactional replication data will be same as in snapshot replication, but later only the transactions are synchronized instead of replicating the whole database.

We can specify the refresh of database either continuously or on periodic basis.

3. Merge Replication

Merge replication replicate data from multiple sources into a single central database.

The initial load will be same as in snapshot replication but later it allows change of data both on subscriber and publisher, later when they come on-line it detects and combines them and updates accordingly.

What is REPLACE and STUFF function in SQL Server?

STUFF: This function is used to replace the part of string with some another string.

Syntax:

STUFF (String1, Position, Length, String2)
String1 - String to be overwritten
Position - Starting Position for overwriting
Length - Length of replacement string
String2- String to overwrite

Example:
SELECT STUFF(‘Arpit',2,2,'mi')
Output: Amit

REPLACE: This function is used to replace all the occurrences of particular string by another string.

Syntax:
REPLACE(String1, String2, String3)

Example:
SELECT REPLACE(‘Arpit Jain’,’i’,’m’)
Output: Arpmt Jamn

If you want to replace the first occurrence of “I”, Replace wouldn't work, because it always replaces ALL occurrences of the string.

Give a example to search for a string in all stored procedure in SQL Server.

-Suppose we have a EmpDetails table in our database and there are certain number of stored procedures in database. We want to know in which stored procedure(s) table EmpDetails is used.

-We can use following query

SELECT
sys.objects.name, sys.objects.type, sys.objects.type_desc,
sys.objects.schema_id, sys.syscomments.text
FROM sys.objects
INNER JOIN sys.syscomments ON sys.objects.object_id = sys.syscomments.id
where sys.syscomments.text like '%EmpDetails%'
And type ='P'

What are Magic tables in SQL Server?

-In SQL Server there are two system tables “Inserted” and “Deleted” called Magic tables.

-These are not the physical tables but the virtual tables generally used with the triggers to retrieve the inserted, deleted or updated rows.

-When a record is inserted in the table that record will be there on INSERTED Magic table.

-When a record is updated in the table that existing record will be there on DELETED Magic table and modified data will be there in INSERTED Magic table.

-When a record is deleted from that table that record will be there on DELETED Magic table.

What is difference between stored procedure and user defined function?

It is not possible to change the table data with the use of User defined functions but you can do it by using stored procedure.

The execution of User defined function will be stopped if any error occurred in it. But in the case of Stored procedure when an error occurs the execution will ignore the error and jumps to the next statement.

We can use User defined function in XML FOR clause but we can use stored procedure in XML FOR clause.

It is not possible to make permanent changes to server environment whereas stored procedure can change some of the server environment.

User defined functions do not return output parameters while stored procedure can return output parameters.

What are ACID properties of Transaction?

Following are the ACID properties for Database.

Atomicity – Transactions may be set of SQL statements. If any of statement fails then the entire transaction fails. The transaction follows all or nothing rule.

Consistency – This property says that the transaction should be always in consistent state. If any transaction is going to effect the database’s consistent state then the transaction could be rolled back.

Isolation – This property says that one transaction can not retrive the data that has been modified by any other transaction until its completed.

Durability – When any transaction is committed then it must be persisted. In the case of failure only committed transaction will be recovered and uncommitted transaction will be rolled back.

What are COMMIT and ROLLBACK in SQL?

COMMIT statement is used to end the current transaction and once the COMMIT statement is exceucted the transaction will be permanent and undone.

Syntax: COMMIT;

Example:
BEGIN
UPDATE EmpDetails SET EmpName = ‘Arpit’ where Dept = ‘Developer’
COMMIT;
END;

ROLLBACK statement is used to end the current transaction and undone the changes which was made by that transaction.

Syntax: ROLLBACK [TO] Savepoint_name;

Example
BEGIN
Statement1;
SAVEPOINT mysavepoint;
BEGIN
Statement2;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO mysavepoint;
Statement5;
END;
END;

What is a Linked Server?

When we want to query on remote database server along with the local database server then we can add the remote SQL server to local SQL server in a same group using the concept called Linked Server.

We can query on both servers using T-SQL.

We can use stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin to add new Linked Server.

By using Linked Server we can SQL statement in clean and easy way to retrieve, join and combine remote data with local data.

What is a WITH(NOLOCK)?

WITH(NOLOCK) is used to unlock the data which is locked by the transaction that is not yet committed. This command is used before SELECT statement.

When the transaction is committed or rolled back then there is no need to use NOLOCK function because the data is already released by the committed transaction.

Syntax: WITH(NOLOCK)

Example:
SELECT * FROM EmpDetails WITH(NOLOCK)

WITH(NOLCOK) is similar as READ UNCOMMITTED

What are the basic functions for master, msdb, model, tempdb databases?

The Master database contains catalog and data for all databases of the SQL Server instance and it holds the engine together. Because SQL Server cannot start if the master database is not working.

The msdb database contains data of database backups, SQL Agent, DTS packages, SQL Server jobs, and log shipping.

The tempdb contains temporary objects like global and local temporary tables and stored procedures.

The model is a template database which is used for creating a new user database.

List few advantages of Stored procedure.

By using stored procedures we can reuse the code.

Stored procedure helps in reducing network traffic and latency.

Stored procedures provide better security to your data.

Stored procedure is cached in SQL Server’s memory. So it helps to reduce the server overhead. It also enhances application performance.

Stored procedures help us in the encapsulation of the code. The code of the stored procedure can be changed without affecting application.

Digital Learner