One of the repetitive task that DBA need to perform is create maintenance plan for database. Maintenance plans enables you to automate maintenance activities for a database, backups, db integrity checks and index maintenance tasks. We can easily create a maintenance plan using a wizard in sql server 2008 R2. You can use the following steps to create a maintenance plan 1...
Creating Maintenance Plans in SQL Server 2008 R2
One of the repetitive task that DBA need to perform is create maintenance plan for database. Maintenance plans enables you to automate maintenance activities for a database, backups, db integrity checks and index maintenance tasks. We can easily create a maintenance plan using a wizard in sql server 2008 R2. You can use the following steps to create a maintenance plan 1. Select the...
Monitoring running processes in SQL Server 2008 R2
You can view the current connections and processes that running on a sql server instance in two ways
Using Activity Monitor
Using T-SQL statements
Monitoring Process in Activity Monitor
To open the Activity Monitor in SQL Server Management Studio Right click the server then select the Activity Monitor option as shown below
SQL Server Reporting with Analysis Services
SQL Server Analysis Services is used to present the aggregated data to support analysis, ad-hoc reporting and decision making process. Technically CUBE is used in Analysis Services to build the aggregated data. Once it is build then you can easily navigate and produce reports for business analysis. This post discusses using analysis services in SQL Server Reporting with MDX query...
Filtering Techniques for SQL Server 2008 R2 Reports
It is important to consider to filter report data based on user’s selection criteria as many databases contain large amounts of data. This post discusses different techniques to filter report data using report parameter concepts. By using the filters on reports, it greatly reduces the network traffic and helps the report to render efficiently. We are using same data source and query that...
Tabular Reports in SQL Server 2008 R2 Reporting Services
It is important to understand the reporting options available in SQL Server 2008 R2 to provide an appropriate report layout to meet the business requirements and needs of our end users. This post briefly discusses about different report layout types available and explains in detail the steps to create a Tabular Report. It also explains how to sorting and drilldown features to your report...
Database Mail Configuration in SQL Server 2008 R2
This post explains the configuration steps that require to send an email from SQL server 2008 R2. It is useful if we could send an email from SQL Server to our administrators and end users. Database Mail is SQL server component that uses Simple Mail Transfer Protocol (SMTP) to send email messages. After setting up the Database email we can test the email and mail-log. 1. Open the SQL...
PIVOT & UNPIVOT Operators in SQL Server 2008
PIVOT is a new T-SQL operator introduced in SQL Server 2008 which can be used to rotate rows into columns and can create a crosstab query. You can specify the PIVOT operator in FROM clause of your query.
Using PIVOT
Specify the desired values that you want to see in SELECT statement and FROM clause contains the PIVOT statement. Let us take an example
Date & Time Data Types in SQL Server 2008
The four new data types for storing dates and times in SQL Server 2008 are
Date
Time
DateTime2
DateTimeoffset
This post discuss about new date time data types and new date time related functions introduced in SQL Server 2008.
T-SQL TIP in SQL Server 2008
You love this if you write a lot of T-SQL code. This tip I can say as T-SQL language syntax enhancement. The following are the enhancements in T-SQL
Now We can declare and initialize the variables with single statement.