Few of my colleagues and friends have been asking me for a while to write something on the differences between SQL Server Reporting Services versions 2005 and 2008. Especially when they were preparing for interviews / attending interviews, this was one of the most common questions they were asked by the interviewers. However when they browsed on the net, they could not get a comprehensive list of differences between SSRS 2005 and SSRS 2008 not even in a single place. Hence I came up with this idea of presenting some of the major differences between SSRS 2005 & 2008 so that it is helpful for the people who are curious to know about the differences between SSRS 2005 & 2008 and also for those people who are preparing for an interview on SQL Server Reporting Services.

Below table shows few of the differences between 2005 and 2008 versions of SQL Server Reporting Services in the order of priority (major to minor differences from top to bottom).

Comparison of SSRS 2005 and SSRS 2008

SQL Server 2005 Reporting Services	SQL Server 2008 Reporting Services
Multiple service architecture (IIS Service & Windows Service)	Single service architecture (Windows Service)
Depends on IIS	Comes with Built-In Web Server and hence does not depend on IIS
Complex deployment due to the need for separate configuration for IIS	Deployment is simple due to inbuilt web server and Reporting Services Configuration Manager
Charts, Dials etc. had to be separately installed and integrated	Comes packaged with Charts, Dials, Indicators, Sparklines etc.
Limited exporting options	More exporting options including Microsoft Word etc
You can only format entire text in a textbox with one single formatting style (bold, italicize, colors etc.)	Allows you to format pieces of text or words or letters within a single textbox with different formatting styles like bold, italicize, colors etc. Example: a Disclaimer Textbox, Header Textbox etc.

Apart from this there are quite a few other differences or improvements in SQL Server 2008 Reporting Services. For more details, please see the Product Information Page for SQL Server 2008 Reporting Services.

This post is specially for Suneel who had asked for this quite long time back :-)

If there are any other major differences which are worth mentioning then do leave a comment, so that the post can be updated and other readers can benefit from it.

As always for any comments / suggestions / opinions, please leave a comment below.

Related Articles:

• Features of SQL Server Reporting Services (SSRS)
• Shortcuts for SQL Server Client Applications
• Controlling Exporting Options in Reporting Services (SSRS)
• How to Display Report Execution Time in Reporting Services (SSRS) 
• Other SSRS Related Articles on MyTechnoBook

Yesterday I came across another interesting and useful online tool for formatting T-SQL queries quickly. T-SQL Tidy is a free online tool which can format numerous lines of T-SQL code within seconds.

I had written another article about Instant SQL Formatter (Read more here) which is also a great tool for formatting T-SQL queries & also various other database queries like MS Access, Oracle etc and offers various output options including HTML, C#, VB.Net etc. On the other hand T-SQL Tidy offers more formatting options along with a beautiful HTML output with alternate colored lines.

Here is a snapshot of the online version of the T-SQL Tidy user interface with the formatting options highlighted.

Apart from the online version, T-SQL Tidy also has the following other Versions / Add-ins:

• T-SQL Tidy for Desktops
• SQL Server Management Studio (SSMS) 2008 Add-in
• Version Control Add-in for SQL Management Studio 2008

Here is the official T-SQL Tidy Blog. Visit this blog regularly to stay updated on the improvements & new features / add-ins available for T-SQL Tidy.

Here are few of the important features of T-SQL Tidy:

• Offers more formatting options.
• Version Control Add-in for SSMS (2008) allows you to view the version of objects in SSMS and maintains the history, which will help you identify what changes were made, when and what is the difference between any two particular versions. This can be really useful if you want to revert back to previous version of a Table Structure or any other database object. Watch video demo here.
• HTML output comes with a choice to make alternate rows in different colors.

One of the limitations of this tool is limited output options (T-SQL, HTML etc.) and supports only SQL Server (2000 / 2005 / 2008).

Overall, its a great tool to instantly format your T-SQL queries, which can save you lot of time, especially when your typing speed is slow and you cannot switch between upper & lower case characters, cannot use frequent tabs to include necessary indentation etc.

Read more about another T-SQL formatting tool, Instant SQL Formatter here.

Writing well formatted T-SQL queries is an art and takes quite a bit of patience to do the formatting. While working with various project teams, I often advise my team members and people who are new to SQL Server to format the SQL queries and always follow some conventions / standards (Read more on T-SQL Coding Standards & Best Practices here).

However, quite a few times either due to laziness or due to time constraints, many developers do not format the queries properly. This makes the queries less readable and difficult to maintain.

I came across a really cool tool few months back and wanted to share the same with broader audience, but missed many times due to various distractions. And today finally I decided to post it on my blog :-)

Here is a tool called Instant SQL Formatter which is really cool, easy to use, pretty powerful and can format your T-SQL queries in seconds. Many of you might already be knowing about this tool,  since this has been in the market for quite some time now. However I have presented few of the benefits and other details so that people who do not know about this can get benefited. Here is a screenshot of the online version of the Instant SQL Formatter.

Here are few cool features of Instant SQL Formatter:

• Supports queries from databases like MS Access, DB2, Microsoft SQL, MySQL, Oracle / PLSQL and other Generic queries.
• Supports various output formats like Text, HTML, Java, C#, VB.Net, XML and many more.
• Gives control over how you want to format Keywords (Uppercase, Lowercase, InitCap, Unchanged), Identifiers (Uppercase, Lowercase, InitCap, Unchanged, Init cap each word), Functions (Uppercase, Lowercase, InitCap, Unchanged) etc.
• Formats 100's of lines of lines of code in just few 10's of seconds.

Guess what, the best thing I like about Instant SQL Formatter is its ability to output formatted T-SQL code in Text format along with HTML format, which bloggers like me can use to embed code into blog posts :-)

Apart from the Online Version, here are few other Versions / Add-Ins of Instant SQL Formatter:

• Instant SQL Formatter - Desktop Version
• Instant SQL Formatter - Add-In for Visual Studio
• Instant SQL Formatter - Add-In for SQL Server Management Studio (SSMS) (More on SSMS Shortcuts here)

This is a real time saver tool and is worth giving a try.

Here are few of the most important advantages of writing well formatted T-SQL queries:

• Makes the query readable and makes it look cleaner.
• Easy to maintain due to high readability.
• Using same set of formatting rules / standards across modules / projects ensures consistency.

And as always, for any comments / suggestions, please leave a comment below.

I have seen many people, even though they know what function / clause / keyword to be used for a particular T-SQL / Other type of query / operation, they don't remember the exact syntax [Including me at times :-)]. From my perspective as long as we know what keywords / functions / clauses need to be used for performing a specific operation using T-SQL / Other language (like MDX, DMX etc), we can always get the syntax from various sources like msdn, SQL Server Books Online (Local / Online version) etc. and get the task done.

However, if you have an option of getting the well formatted code snippet with the syntax of all the commonly performed T-SQL / MDX / DMX etc. operations right in the query editor window, then nothing like it. I would love such a feature.

Microsoft as always known for offering most powerful and user friendly tools has provided a separate section / explorer in SQL Server Management Studio 2005 / 2008 which contains the templates / code snippets for all the commonly performed operations in T-SQL, MDX, DMX etc. This feature / explorer is called as Template Explorer. Few of you reading this post might already be knowing about this feature, but explaining it in detail for those readers who have not used this feature before.

Following is a snapshot of Template Explorer in SQL Server 2008 Management Studio showing the list of various well formatted code snippets grouped into various categories. Below snapshot shows all the available categories of code snippets for T-SQL operations in SQL Server 2008 with the list of code snippets expanded for commonly performed T-SQL operations on tables.

Template Explorer in SQL Server Management Studio (SSMS) can be accessed using the following ways:

• SSMS Menus - View -> Template Explorer
• SSMS Shortcut - Ctrl + Alt + T

Following screenshots show the comparison of Template Explorer between SQL Server 2005 & SQL Server 2008 for T-SQL / SQL Server, MDX, Mobile / Compact Editions.

Now let us take a look at how to generate the table creation script for a sample table using the Create Table template available in Template Explorer.

Go to template explorer and expand the Tables folder under SQL Server section in Template Explorer as shown in the above figure 1. Now double click on the Create Table template to open it in a new query editor window.

Following code snippet is present in the Create Table template:

-- ========================================= -- Create table template -- ========================================= USE <database, sysname, AdventureWorks> GO IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table> GO CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table> ( <columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>, <column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>, <column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>, CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>) ) GO

Now go to Query -> Specify Values from Template Parameters... in SSMS. This brings up a pop-up window "Specify Values from Template Parameters". In this window specify the parameters like Column Names, Column Data Type, NULLability, Constraints etc. as shown in the below screenshot.

Now when you click OK, the Table Creation Script for the SampleEmployeeTable gets generated as shown below.

-- ========================================= -- Create table template -- ========================================= USE MyTechnoBook GO IF OBJECT_ID('dbo.SampleEmployeeTable', 'U') IS NOT NULL DROP TABLE dbo.SampleEmployeeTable GO CREATE TABLE dbo.SampleEmployeeTable ( EmployeeId INT NOT NULL, EmployeeName NVARCHAR(255) NULL, EmployeeAddress NVARCHAR(512) NULL, CONSTRAINT PK_SampleEmployeeTable_EmployeeId PRIMARY KEY (EmployeeId) ) GO

Using Template Explorer we can perform the following tasks:

• Edit the existing templates to include additional details like parameters, fields, comments, formatting etc.
• Create new templates for commonly used scripts in a module / project so that they can be reused by other team members and also across various modules / projects.
• Rename existing folders / templates as per your convenience (Though this is not required 99.9% of the times as all the folders which ship with SQL Server are named appropriately).
• Create new folders / Renaming existing folders to add new or existing templates.
• Re-organizing the templates by moving them across folders.

Following are few of the advantages / benefits of using Template Explorer:

• These templates act as a quick reference for getting the syntax in the absence of locally installed SQL Server Books Online or Access to Internet.
• Saves time required for formatting the T-SQL code since the templates are pre-formatted hence ensures consistency in terms of formatting across various modules / projects.
• We can share commonly used scripts across all the team members working across modules / projects.

Please let me know your opinion about this article / feature by leaving a comment below.

Here are couple of related links and articles that I have previously published.

• T-SQL - Coding Standards & Best Practices
• Template Packages in SSIS