SQL Server 2008 R2 Launch in Toronto

Whole day event on Monday May 31,2010 in MARS.

Time		Description
8:00 - 9:00 am		Registration
9:00 - 9:15 am	( SQL PASS Community Representant ) &Daniel Shapiro	Welcome & Introductions
9:15 - 11:15 am	John Hancock	Keynote Presentation: Data Platform Vision & SQL Server 2008 R2 Introduction
SQL Server 2008 R2 Overview: Trusted & Scalable Platform (20 min.), Managed Self-Service BI (20 min.), IT & Developer Efficiency (20 min.)
11:15– 11:30 am	Break	Break	Break	Break
		DBA TRACK	BI TRACK	Visual Studio TRACK
11:30 – 12:30pm	Session Title:	Ensure Business Continuity with SQL Server 2008 R2, Windows Server 2008 & Hyper-V Live Migration	Enable Managed Self-service BI with PowerPivot for Excel and SharePoint 2010	Developing SQL Applications with Visual Studio 2010
Speaker:	Saleem Kanji	Howard Morgenstern	Adam Gallant and Paul Sanford
Content Link:			TBC
12:30 - 1:30 pm		Lunch	Lunch	Lunch
1:30 – 2:30pm	Session Title:	Simplify management of your SQL Server data platform with Multi-server Management	Achieve Rapid Reporting with Reporting Services and Report Builder 3.0	Managing Change for SQL Server applications using Team Foundation Server
Speaker:	Marc Theoret	Doug Harrison	Adam Gallant and Paul Sanford
Content Link:
2:30 - 2:45 pm	Break	Break	Break	Break
2:45 – 3:45 pm	Session Title:	Deliver unprecedented access to business-critical data at a lower TCO with SQL Server 2008 R2 Parallel Data Warehouse	Importance of Master Data Management	Targeting SQL Azure using Visual Studio
Speaker:	Doug Harrison	Marc theoret	David Remmer
Content Link:

OneNote publishing

I really enjoy using OneNote from Office 2007. It's a little known tool that looks like a notebook on your screen. It allows you to write using keyboard and pen and include other files like video, audio, diagrams... into it.
It is very cool way for people to collaborate on project. I know some companies that were using it to (co)write specs or design documentation in it. All the needed to do was to create a new notebook on shared folder and them everybody can start writing into it (at the same time).

What was missing for me was a way to collaborate over web. Naturally, Microsoft offered a way to collaborate through SharePoint, but I do not have SharePoint available for everything that I do. Is there some alternative that I do not know about?

In cases when you just want to publish your OneNote notebook over the web, you can use OneNote Web Exporter. It creates an .html file and set of OneNote files that you can put on a web server.

Mount ISO file

Somebody in MS, created WinXpVirtualCdControlPanel a small unsupported to for attaching ISO files as virtual CDs. That alleviates a need to burn ISO files on CD or DVD.

SQL Server 2008 Cost Savings Calculator | Microsoft

Microsoft created funny and useful page that compares total cost of ownership beween SQL Server 2008 and Oracle 11g.
SQL Server 2008 Cost Savings Calculator Microsoft

SQL Server 2008 R2 Express Edition supports 10GB databases

One of the major changes to SQL Server 2008 R2 Express Edition is that database size limit will be increased from 4GB to 10GB. It is still limited to use of 1 CPU.

SQL Server - Find biggest table

I've found a nice code snipet on how to find a biggest table in a database. It is based on sp_MsForEachTable, so works on all non-ancient versions of SQL Server.

Running Total in TSQL

There are many ways to do running total on SQL Server. Let's create a test table:

CREATE TABLE [dbo].[t](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Amt] [float] NULL
) ON [PRIMARY]
go

If you have SQL Server 2005 or earlier you could do a running total using a subquery or join:

select t.id, t.amt
, (select Sum(Amt) from dbo.t i where i.id <= t.id) RunTotal
from dbo.t t
order by 1

select t.id, t.amt, sum(a.amt) RunTotal
from dbo.t t
inner join dbo.t a
on a.id <= t.id
group by t.id, t.amt
order by 1

These look "natural" and seem to be using sets. Unfortunately, it is not really like that. If youre result has 4 rows, SQL Server will have to do a sum of 1 row for first, 2 rows for second, 3 rows for 3rd and 4 rows for 4th - in total 10 rows. For resultset with 10,000 rows it will have to total: (10,000^2+10,000)/2 = 50,005,000 (yes - 50 million rows). Even a solution with cursor is better then this.

If everyting is in a table, you could use a strange variation of Update statement to do thi faster.
Let's first add a column to the table:

ALTER TABLE dbo.t

ADD RT float NULL
GO

Now you can use a special form of Update statement that writes both into the table and in a varaiable:

DECLARE @rt float
SET @rt = 0


UPDATE t
SET @rt = RT = @rt + amt
WHERE id = id


select * from t

Apparently, this goes through the table just once and is much lighter on SQL Server. Unfortunately, it is based on non-documented behavior of SQL Server and behind a scene depends on row by row operation.

Alternatively, you can implement cursor a based solution (either in TSQL or in SQLCLR).

Unfortunately, it is not possible to do this using Over clause. SQL Server does not support (not even SQL Server 2008 R2) use of Over clause with Order By when using an aggregation function. We should ask Microsoft to implement this or this.