T-SQL (RSS)

Favorite BI Features in SQL Server 2008

If you haven’t heard yet, CTP 3, which is a public beta of SQL Server 2008 has now been released and can be downloaded at the Connect website. After a bit of unscientific research, here’s my top 5 BI features in SQL Server 2008. It’s ordered in my priority order. It’s important to note that some of these features have not been introduced in CTP 3 yet but will be coming soon.

  1. No more IIS requirement for SSRS! – Finally, there’s not the overhead of having to install IIS as part of the popular reporting tool.
  2. CDC and Merge – While this is a database engine improvement, it will tremendously help your BI effort in SSIS. The ability to do Change Data Capture on a SQL Server table will help warehousing scenarios and reduce your load time by a factor of 9x.
  3. SSRS scalability – The reporting engine has been significantly improved to scale to million row reports. Also introduced is a best of both worlds table and matrix style report called tablix.
  4. SSIS thread scheduler improvements – In some early testing, the thread improvements have netted a 20-70% improvement in data flow performance. The main improvement is going to be with a long list of synchronous transforms.
  5. Authoring Reports in Excel and Word – Thanks to some smart licensing of Soft Artisians’ product suites, you’ll now be able to author a report in Word or Excel. I would expect this to be a pretty big blow to competing reporting platforms since users will be able to write reports in where they spend most their time in.

-- Brian Knight

Announcing Visual Studio Team Edition for Database Professionals

Really cool tool that I'll have to start blogging about! Here's the announcement I just received from Microsoft. I added some screen shots below.

Visual Studio Team Edition for Database Professionals delivers a market-shifting database development product designed to manage database change, improve software quality through database testing and bring the benefits of Visual Studio Team System and life cycle development to the database professional. It delivers on Microsoft’s commitment to provide tools that reduce communication barriers and complexity across software development teams and fulfils increasing demand in the market for more advanced database change management tools. Database professionals such as database architects, database developers and database administrators, can now employ integrated change management functionality to streamline changes to their databases, ensure quality, and speed deployment.

Visual Studio Team Edition for Database Professionals includes a number of great new features:

·         A new Visual Studio Database Project allows you to import your database schema and place it under source control. When the time comes to deploy schema changes the new project system allows you to quickly build update scripts or packages and then provides a mechanism to deploy them to the database of our choice.

·         Rename Refactoring allows you to easily rename any object in your database and be assured that all references to that object will be renamed to correspond to the change

·         A New T-SQL Editor allows you to be more productive when writing T-SQL code from within Visual Studio including support for parallel execution of queries and viewing of execution plans.

·         SchemaCompare allows you to quickly compare the schema of two databases (or your source controlled project and a database) and script updates to bring the database schemas into sync

·         DataCompare allows you to quickly compare two databases and script updates to bring the data in these databases into sync

·         The Database Unit Testing infrastructure allows you to create database unit tests using T-SQL or managed code.

·         DataGenerator lets you create data generation plans that produce repeatable sets of meaningful data based upon your existing production databases that can be deployed to a database prior to running unit tests thus ensuring consistent test results

You can find out more about this great new release, see screenshots and find out how to get the early community technology preview which will be available on June 11th at http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/default.aspx

We are making Visual Studio Team Edition for Database Professionals part of the Visual Studio Team Suite, so you’ll get this product for free when we RTM this edition if you are a Visual Studio Team Suite subscriber through MSDN. You can learn more about how to upgrade to Visual Studio Team Suite at http://msdn.microsoft.com/vstudio/howtobuy/renewal/#step

Visual Studio Team Edition for Database Professionals will also be available as a stand alone Edition in the Visual Studio Team System family. You can learn more about how to buy Visual Studio Team System at http://msdn.microsoft.com/vstudio/howtobuy/default.aspx

The team has already started blogging. You can find out more information directly from the product team by visiting the following blogs:

http://blogs.msdn.com/gertd/
http://blogs.msdn.com/camerons
http://blogs.msdn.com/rwaymi
http://blogs.msdn.com/mattnunn
http://blogs.msdn.com/thomas_murphys_agile_db_blog
http://blogs.msdn.com/tsdatabl

 


Take Control of Database Change

  


Automated Database Testing

  


Improve Collaboration and Communication

Quickly Retrieving the Row Count for All Tables

My typical query I run for retrieiving a list of tables and how many records in each table there are is to use the sp_msforeach stored procedure. The sproc loops through each table in a database and performs an action. Given that, you can run a query like this to obtain a list of tables and how many records are in each table:

sp_msforeachtable "Print '?' select count(*) from ?"

The problem for me with this above query is that it's not sortable without some additional customization. So, I needed a different answer. The sysindexes table holds some very valuable information for a developer or DBA. The table shows you a record for each index in your database as well as the number of rows that are estimated to be in the table. It's not guaranteed to be up to date as far as the number of rows but it's a good number to start with. To model a query at that table, you can use the following syntax:

select convert(varchar(30),object_name(id)) [Table Name], rows from sysindexes
where object_name(id) not like 'sys%' and indid = 1
order by object_name(id)

Instantly you retreive in a single recordset the number of estimated records in each table. The key thing to keep in mind with the above query is I'm weeding out any table that began with sys. If you have a user table that begins with sys, it will be excluded. The indid=1 part of the syntax retrieves only the primary index. There are flaws to this method because it's not exact but it does give you a rought idea of your system information.

-- Brian Knight