T-SQL, SQL Administration and SSRS Articles Link List – March 2011

Here’s a quick wrap up of the articles published on SQLServerCurry.com in the month of March 2011

SQL Server Administration Articles

Repair SQL Server Database marked as Suspect or Corrupted - There can be many reasons for a SQL Server database to go in a suspect mode when you connect to it - such as the device going offline, unavailability of database files, improper shutdown etc. This post shows how to resolve this issue.

SQL Server: SSMS 2008 Intellisense Stops Working after Installing VS 2010 SP1 - After installing VS 2010 SP1, Intellisense stopped working in SQL Server Management Studio SSMS 2008 and R2 databases. This post has some solutions.

SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them - Sometimes you may notice that some T-SQL queries are taking too much time to execute and thus slowing down the performance of SQL Server and other applications. You can find and rectify these queries using the methods shown in this post.

SQL Server: Move Table to a new File Group - This article shows why and how to move a SQL Server Table from one File group to another.

SQL Server: Delete Backup History to reduce MSDB database size – This article demonstrates the use of the sp_delete_backuphistory database engine stored procedure which makes it very simple to delete backup history that is older than the specified date.

T-SQL Articles

SQL Server: Search Non Round Numbers - Here’s a simple query that searches all non round numbers from a table. The following are non-round numbers 20.00, 24.0 and the following are round numbers 20.20, 24.42 and so on.

SQL Server: Combine Multiple Rows Into One Column with CSV output - In response to one of my posts on Combining Multiple Rows Into One Row, SQLServerCurry.com reader “Pramod Kasi” asked a question – How to Combine Multiple Rows Into One Column with CSV (Comma Separated) output. This post explains how to do so.

SQL Server: Insert Date and Time in Separate Columns - If there is a need to store date and times values in separate columns, you can store Date values in the Datetime column and Time values in either the char datatype or the time datatype (Sql Server 2008), as shown in this post

SQL Server: Group By Days and Create Categories - In this post, we will see how to Group records by Days in SQL Server and then create categories.

SQL Server: Return Boolean from Stored Procedure - Sometimes you may need to return a Boolean value from a SQL Server stored procedure. This article explains how to do so.

SQL Server: Handle Delimited String passed as a Parameter to Stored Procedure - In your application, you may need to pass multiple values as a parameter to a stored procedure. This article shows how to pass delimited values to the procedure and get data

SQL Server: Count based on Condition - Many a times, you may want to do a count in SQL Server, based on a condition. The easiest way is to use a combination of SUM and CASE as shown in this article

SQL Server Reporting Services (SSRS) Articles

SQL Server Reporting Services Tutorial: Designing a Basic Report using SSRS 2008 and VS 2008 BI Studio - In this article, we will see how to create a basic SSRS developer report using Visual Studio 2008 BI Studio. We will create a simple drill down report which will contain a total and subtotal. We will also analyze the options for reports in Visual Studio 2008.

Design Parameterized Report using SSRS 2008 - In this article, we will see how to create a SSRS report with parameters. We will create the parameters and then add these parameters to our reports, in different ways. For this demonstration, we will use SQL Server 2008 database ‘AdventureWorks’ for designing the report as well as parameters.

SQL Server: Delete Backup History to reduce MSDB database size

SQL Server keeps a track of the backup history of your server, in the msdb database. Every time a backup or restore operation occurs on your database, additional rows are added to the backup and restore history tables. If you do not keep a check on this, you will find your msdb database growing over time.

SQL Server provides the sp_delete_backuphistory database engine stored procedure which makes it very simple to delete history that is older than the specified date. Here’s how to use this stored procedure to delete backup history that is older than Jan 31, 2011, 12:00 A.M. in the backup and restore history tables.

Similarly if you want to automatically delete records that is say 2 months old, create a stored procedure that calculates the date and executes the sp_delete_backuphistory procedure

Here's the same query for you to try out:

CREATE PROCEDURE [dbo].[DeleteBackupHistory]
AS
 
DECLARE @BckDate DATETIME
SET @BckDate = CONVERT(varchar(10), DATEADD(dd, -60, GETDATE()), 101)

EXEC sp_delete_backuphistory @BckDate

Now whenever you want to delete records 2 months prior to the current date, just call the DeleteBackupHistory stored procedure.

Similarly, you may also want to look at the sp_purge_jobhistory and sp_maintplan_delete_log to remove other history information and keep your msdb database from growing over time.

SQL Server: Count based on Condition

Many a times, you may want to do a count in SQL Server, based on a condition. The easiest way is to use a combination of SUM and CASE as shown in this example

Here’s the same query to try out

DECLARE @TT Table (
CourseID int, StudentID int, EnrolledBy varchar(25)
)

INSERT INTO @TT
SELECT 1, 1, 'Hrishi' UNION ALL
SELECT 1, 2, 'Sagar' UNION ALL
SELECT 1, 3, 'Tony' UNION ALL
SELECT 1, 2, 'James' UNION ALL
SELECT 1, 4, 'Krish' UNION ALL
SELECT 2, 5, 'Tony' UNION ALL
SELECT 2, 5, 'Molly' UNION ALL
SELECT 2, 6, 'Tony'

SELECT
COUNT(*) as 'Total Enrolled',
SUM(CASE
WHEN EnrolledBy = 'Tony'
THEN 1 ELSE 0 END) as 'EnrolledbyTony'
FROM
@TT

In the query shown above, we are using SUM and CASE to count data only for those records, which are Enrolled by Tony. Similarly instead of SUM, you can also use COUNT which only counts the non-null values.

OUTPUT

Design Parameterized Report using SSRS 2008

In this article, we will see how to create a SSRS report with parameters. We will create the parameters and then add these parameters to our reports, in different ways. For this demonstration, we will use SQL Server 2008 database ‘AdventureWorks’ for designing the report as well as parameters.

This article is a continuation of ‘Designing Basic Report using SSRS 2008 and VS 2008 BI Studio’. If you have not gone through it, I will highly recommend you to go through it first and then start with this article.

In my previous article, we have seen how to create a simple drill down report which is fetching the data from SQL Server 2008 database engine from the database ‘AdventureWorks’. So let’s open the solution ‘AdvReports’ which we created during our last demonstration and preview the report.

Now let’s open the ‘Report Data’ window. Right click the Dataset1 from ‘AdventureWorksDS’ and go to ‘Dataset Properties’ as shown below –

This will open a Dataset Properties Window. If you observe the window, it shows you multiple options as described below –

1. Query – from the ‘Query’ section, you can modify the query of the report. You can change the name of the Dataset. You can even change the Data source or create a new Data source from Data Source dropdown list or by clicking a ‘New’ button respectively. You can even import a query from ‘.SQL’ file or ‘RDL’ file.
2. Parameters – from the parameters section, you can add new parameters to the report or change the existing parameters.
3. Fields – from the fields section, you can change the display name of the field or do the mapping of the field with the source field.
4. Options – this section allows you to change collation, change case sensitivity and other options.
5. Filters – from this section, you can add filters to your report.

Now let’s get back to Query section and click on ‘Query Designer’ button as shown below –

Once you open the ‘Query Designer’ window, let’s modify our query to add the parameter(s). To add the parameter(s) to our report, replace the existing query by the query shown below which has parameter declaration.

Modified Query –

SELECT Sales.SalesTerritory.Name AS Region,
Production.ProductCategory.Name AS Category,
Production.ProductSubcategory.Name AS SubCategory,
SUM(Production.Product.ListPrice * Sales.SalesOrderDetail.OrderQty) AS Sales,
SUM(Production.Product.StandardCost * Sales.SalesOrderDetail.OrderQty) AS Cost
FROM Sales.SalesOrderHeader
INNER JOIN Sales.SalesOrderDetail
ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
INNER JOIN Production.Product
ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID
INNER JOIN Production.ProductSubcategory
ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory
ON Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID
INNER JOIN Sales.SalesPerson
ON Sales.SalesPerson.SalesPersonID = Sales.SalesOrderHeader.SalesPersonID
INNER JOIN Sales.SalesTerritory
ON Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID
INNER JOIN Sales.Customer
ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
WHERE (Sales.SalesTerritory.[Group] = @Territory)
AND (Sales.Customer.CustomerType = 'S')
GROUP BY Sales.SalesTerritory.Name,
Production.ProductCategory.Name, Production.ProductSubcategory.Name

Now if you observe the above query and compare it with our previous article, we have made a small modification. The modification is that instead of using Sales.SalesTerritory.[Group]=’North America’ we are using Sales.SalesTerritory.[Group]=@Territory, where ‘@Territory’ is a parameter.

After replacing your old query with the new query shown above, click on the ‘Run’ button to test the query. It will show you a ‘Query Parameter’ dialog box. Fill the value of the parameter as ‘North America’ and click ‘OK’ button as shown below –

Now you will see the result shown below in a ‘Query Designer’ window. Click ‘OK’ button on the query designer window and check your ‘Parameters’ section in ‘Dataset Properties’ window. You will now see the parameter ‘@Territory’ in the parameters section as shown below –

Now click ‘OK’ button of ‘Dataset Properties’ window. Build your report by going to ‘Build’ menu and ‘Build AdvReport’ as shown below –

Now hit ‘F5’ and run your report. Your report will be displayed in a ‘Report Viewer’ with a parameter ‘Territory’ and a ‘Textbox’ where we need to pass the value of the parameter. So let’s pass ‘North America’ and click a ‘View Report’ button and see how the report is getting displayed. The report is shown below –

So this is how you create a simple parameterized report in SQL Server Reporting Services 2008.

Choose a Parameter using a DropDown List

But now if you observe the report parameter, it is asking you to write a name of the ‘Territory’. What if your customer does not want to remember the names of all the ‘Territories’? Can we make our customer’s life much easy by providing a list of all the ‘Territories’? This way our customer will see the report as per his/her convenience by selecting a ‘Territory’ of his/her own choice.

To make our customer’s life much simpler and to give a professional approach to our report, let’s design our parameter in a different way and add it to our report.

To do this, first of all open ‘Report Data’ window. Right click ‘AdventureWorksDS’ and create a new Dataset as shown below –

Now once you click the ‘Add Dataset’ menu, you will see a ‘Dataset Properties’ window as shown below –

Change the name of the dataset as ‘AllTerritories’. Make use of the ‘AdventureWorksDS’ data source. At the end, write the following query in the ‘Query’ section –

SELECT distinct [Group] FROM Sales.SalesTerritory

You can test your query by clicking the ‘Query Designer’ button as shown in the step earlier. Now click the ‘OK’ button of ‘Dataset Designer’ window and check your ‘Report Data’ window. You will now see the new dataset ‘AllTerritories’. Now expand the Parameters section from ‘Report Data’ window. Right click the ‘Territory’ parameter and click on ‘Parameter Properties’ menu as shown below –

It will open a ‘Report Parameter Properties’ window. From that window, go to ‘Available Values’ Section and choose ‘Get values from a Query’. Then choose the dataset ‘AllTerritories’ from the dropdown list. After that, select the value field ‘Group’ and Label field ‘Group’ as shown below –

Click on the ‘OK’ button. Build your report and hit ‘F5’ to see the report in ‘Report Viewer’ tool. Now if you observe your report in report viewer, you will see a dropdown list instead of a ‘Textbox’. When you click on the dropdown list, you will see all the available ‘Territories’ which the customer can choose as per his/her convenience and see the report. The report is as shown below –

In this article, we have seen how to create an SSRS report with parameters and let the user supply the value of the parameter.

SQL Server: Move Table to a new File Group

If a database is expected to grow rapidly, it becomes challenging to maintain the data files
in the same location/drive. To manage disk space well, it one solution to this problem is to identify large tables and accordingly move new data to a new File group, which can be mapped to a different drive path.

This article shows how to move a SQL Server Table from one File group to another. Let us see the steps. To demonstrate, we will create a sample table, find out the file group and then move it to a new File group:

Create a test table

CREATE TABLE test(i int , names varchar(100))

and see which File group is the table created in, using the following command:

EXEC sp_help test

See the fifth resultset. The index_description column says that the table is in the PRIMARY file group

Now create a new file group

ALTER DATABASE test
ADD FILEGROUP file_test

and add a logical path to this file, as shown below:

alter database test
add file
(
name = test_readonly1,
filename = 'c:\file_test.ndf',
size = 10mb,
maxsize = 200mb,
filegrowth = 5mb
) to filegroup file_test;

Now to move table test to this File group, create an index on that table by specifying the
File Group

CREATE CLUSTERED INDEX IDX_i ON test(i)
ON file_test

and run the command again and observe the fifth resultset

EXEC sp_help test

As you can see, the index_description column says that the table has been moved to the file_test file group

SQL Server: Handle Delimited String passed as a Parameter to Stored Procedure

In your application, you may need to pass multiple values as a parameter to a stored procedure. This article shows how to pass delimited values to the procedure and get data

Consider this table:

Now if the id’s '1,4' are passed as a parameter, the records for these ids 1 and 4 should be returned. Create the following procedure

create procedure proc_testing
(
@id varchar(100)
)
as
select id,names from testing
where charindex(','+CAST(id as varchar(10))+',',','+@id+',')>0

go

In the stored procedure shown above, the WHERE condition checks if the value of id is found in the parameter string. The commas are added on both sides for accuracy.

Execute this procedure by passing ids, as shown below:

EXEC proc_testing '1,4'

The result includes records for the id’s 1 and 4

EXEC proc_testing '2,3,5'

The result includes records for the ids 2,3 and 5 as shown below

SQL Server Reporting Services Tutorial: Designing a Basic Report using SSRS 2008 and VS 2008 BI Studio

In this article, we will see how to create a basic SSRS developer report using Visual Studio 2008 BI Studio. We will create a simple drill down report which will contain a total and subtotal. We will also analyze the options for reports in Visual Studio 2008.

First of all make sure that your ‘Report Server’ is running. To check this, go to Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager as shown below –

Once you open the ‘Configuration Manager’ from SQL Server Services, check if the ‘SQL Server Reporting Services’ Service is running. If not, right click on the service and click on the start menu as shown below –

Now for this demonstration we are going to use ‘AdventureWorks’ database which we will use to fetch the data for designing our reports.

So let’s first write a query and test it using ‘SQL Server Management Studio’ by connecting to our database engine. Start ‘SSMS’ as shown below –

Now connect to the server as shown below –

Your server name may be different than mine. So please change the details and connect to your own server installed on the local machine or on your network.

Now write a query for our report and test the same in our SSMS by connecting to the AdventureWorks database as shown below –

USE AdventureWorks
SELECT
Sales.SalesTerritory.Name AS Region,
Production.ProductCategory.Name AS Category,
Production.ProductSubCategory.Name AS SubCategory,
SUM(Production.Product.ListPrice * Sales.SalesOrderDetail.OrderQty) AS Sales,
SUM(Production.Product.StandardCost * Sales.SalesOrderDetail.OrderQty) AS Cost
FROM Sales.SalesOrderHeader
INNER JOIN Sales.SalesOrderDetail ON
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
INNER JOIN Production.Product ON
Sales.SalesOrderDetail.ProductID=Production.Product.ProductID
INNER JOIN Production.ProductSubCategory ON
Production.Product.ProductSubcategoryID=
Production.ProductSubCategory.ProductSubcategoryID
INNER JOIN Production.ProductCategory ON
Production.ProductSubCategory.ProductCategoryID=
Production.ProductCategory.ProductCategoryID
INNER JOIN Sales.SalesPerson ON
Sales.SalesPerson.SalesPersonID = Sales.SalesOrderHeader.SalesPersonID
INNER JOIN Sales.SalesTerritory ON
Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID
INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
WHERE (Sales.SalesTerritory.[Group]='North America')
AND (Sales.Customer.CustomerType='S')
GROUP BY
Sales.SalesTerritory.Name,
Production.ProductCategory.Name,Production.ProductSubCategory.Name

Now paste the above query and hit ‘F5’. You will see the result similar to the following –

This shows that our query is working fine. Now let’s create a report using Microsoft Visual Studio 2008 for displaying the result in a much better way to the stakeholders/customers.

Open Visual Studio 2008 and create a new Project as shown below –

Now click the ‘OK’ button to create a report. It will show a Report Wizard with a Welcome page as shown below –

Now click on the ‘Next’ button. The next step in this wizard is creating a connection for the underlying data source to fetch the data. So let’s configure the connection string as shown below –

Click on the ‘Edit’ button and configure the connection to connect to the database ‘AdventureWorks’ as shown below –

Test the connection and click the ‘OK’ button. You can make your data source common to all the reports by checking a check box ‘Make this a Shared Data Source’. Now let’s move to the ‘Next’ step of the wizard.

In this step, we are going to write and test a T-SQL query. But if you remember, we have already written a query and tested it in SSMS. So let’s copy and paste the above query in our query builder by clicking a ‘Query Builder’ button as shown below –

Click on the ‘OK’ button. Now let’s go to the next step by clicking the ‘Next’ button on the Wizard. This step will ask you to select the ‘Report Type’. It will be either a ‘Tabular’ report or ‘Matrix’ report. For our demonstration, we will choose a ‘Tabular’ report as shown below –

Now click the ‘Next’ button to decide how to display the data on the report and how to group the data? Here’s the screenshot for the same –

If you see the above figure, we are paging the data ‘Region’ wise, Grouping the data ‘Category’ and ‘SubCategory’ wise and showing the ‘Details’ of the sales.

Now click the ‘Next’ button to go to next step. In this step, we will decide the ‘Layout’ of the table which will get displayed in our report as shown below –

Click ‘Next’ button to go to next step. In this step, we will select a style of a table which will be displayed in our report. Make a choice of a style as per your convenience and click on the next button. (I have made a choice of ‘Ocean’ style for my report).

In this step, we will decide the deployment location for our report(s) - on a centralized location as shown below –

Now click on Next button and give a title to our report. I have given a title as ‘Adventure Works Sales Report’ and click on finish button to finish the wizard.

Once your report is displayed, let’s increate the height and width of the columns of our report as shown below –

Now click on the ‘Preview’ report tab, and check how your report gets displayed in the report viewer -

That’s all. Your basic report is ready. Now let’s see the different options available in Visual studio 2008 for report designing.

The most important part of any report is the source of the data. Adding new data fields, calculated fields, modifying existing data structure, create new data source, create new data sets, create parameters and some other operations can be performed by using a ‘Report Data Window’.

To view this window, go to ‘View’ menu and click on ‘Report Data’ –

Now let’s have a closer look at our Solution Explorer –

Using the Solution explorer, we can add data sources as well as multiple reports.

Now let’s have a closer look ‘Toolbox’ for designing a report –

Using these tools we can design the reports or modify the existing reports using report designer.

In this article we have seen how to use Microsoft Visual Studio 2008 Business Intelligence Studio for designing a basic SSRS report. This report is a drill down report which is fetching the data from the SQL Server 2008 database ‘AdventureWorks’.

SQL Server: Return Boolean from Stored Procedure

Sometimes you may need to return a Boolean value from a SQL Server stored procedure. This article explains how to do so.

Create a sample procedure with an output parameter of the ‘bit’ datatype. The supported datatype of boolean in SQL Server is bit.

Here's the same code for you to try out:

CREATE PROCEDURE test
(
@date datetime,
@status bit output
)
AS
IF @date<GETDATE()
SET @status=1
ELSE
SET @status=0
GO

The above stored procedure will return 1 if the date value passed to the parameter is less than the current date and time, otherwise it will return 0.

DECLARE @date date, @b bit
SET @date='20081101'
EXEC test @date,@b output

SELECT @b

GO

OUTPUT

DECLARE @date date, @b bit
SET @date='20110419'
EXEC test @date,@b output

SELECT @b

The first code returns 1 and second code returns 0. @bit is of type bit and it receives output from the stored procedure.

SQL Server: Group By Days and Create Categories

In this post, we will see how to Group records by Days in SQL Server and then create categories. To demonstrate, we will take a sample Student-Course table where the start date and end date of each course for each student, will be recorded. We will then categorize these records by a group of days and see how many students could complete the course in the given category

Consider this table:

Now in order to categorize these records by days, use the following query:

Here’s the same query for you to try out:

DECLARE @TT Table (
CourseID int, StudentID int,
StartDt datetime, EndDt datetime
)
Insert Into @TT
SELECT 1, 1, '03/01/2011', '03/10/2011' UNION ALL
SELECT 1, 2, '03/01/2011', '03/07/2011' UNION ALL
SELECT 1, 3, '03/01/2011', '03/04/2011' UNION ALL
SELECT 2, 2, '03/01/2011', '03/11/2011' UNION ALL
SELECT 2, 4, '03/01/2011', '03/06/2011' UNION ALL
SELECT 2, 5, '03/01/2011', '03/08/2011' UNION ALL
SELECT 3, 3, '03/01/2011', '03/04/2011' UNION ALL
SELECT 3, 5, '03/01/2011', '03/05/2011' UNION ALL
SELECT 3, 6, '03/01/2011', '03/07/2011'

SELECT
CourseID,
SUM(CASE WHEN DATEDIFF(day, StartDt, EndDt)
BETWEEN 1 AND 4 THEN 1 ELSE 0 END) AS [4 or Less Days],
SUM(CASE WHEN DATEDIFF(day, StartDt, EndDt)
BETWEEN 5 AND 8 THEN 1 ELSE 0 END) AS [5 to 8 Days],
SUM(CASE WHEN DATEDIFF(day, StartDt, EndDt) > 8
THEN 1 ELSE 0 END) AS [9 or More Days]
FROM
@TT
GROUP BY CourseID

All we are doing here is using the DATEDIFF function to calculate the number of days between StartDt and EndDt and use a CASE condition to categorize records. The following output shows how many students were able to complete the course in a given range of days.

OUTPUT

SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them

Sometimes you may notice that some T-SQL queries are taking too much time to execute and thus slowing down the performance of SQL Server and other applications. You can find and rectify these queries using the following methods:

Method 1 : Use Dynamic Management View

SELECT
txt.text, total_elapsed_time
FROM
sys.dm_exec_query_stats stat
CROSS APPLY sys.dm_exec_sql_text (stat.sql_handle) txt
ORDER BY
total_elapsed_time desc

The view sys.dm_exec_query_stats will have statistical information of the cached queries
The view sys.dm_exec_sql_text will show the actual query executed. The output will show the results based on the time, the query takes to run, which you can identify and improve upon.

Method 2 : Use SQL profiler

Sometime a query may have code that runs for ever. In such cases, the query never seems to complete execution. You can identify such queries using a SQL profiler.

For eg: Run this code

while 1=1
print 1

The above code will print 1 for ever, thus consuming too much memory. To identify these queries, run a SQL profiler and see the result. As you notice the column CPU, Reads, Writes and Duration will be NULL for that code. To rectify, you can stop that code to release the memory.

You may also find the following posts helpful

Find the Most Time Consuming Code in your SQL Server Database

List Dynamic Management Views (DMV) by Category in SQL Server 2008