« February 2009 | Main | April 2009 »

New Video: SQL Server 2008 T-SQL Enhancements

Curious about what kind of improvements SQL Server 2008 provides in terms of development and programming? Then check out this video to learn about new T-SQL enhancements in SQL Server 2008.


Video Length: 00:09:25
Video Summary: Curious to see what kinds of benefits SQL Server 2008 provides for developers? Check out this video tutorial to learn about T-SQL enhancements in SQL Server 2008 including: Row Constructors, Inline Variable Assignment, Compound Assignment Operators, and DML enhancements for the OUTPUT Clause. Likewise, get a sneak peak at the new T-SQL DML MERGE statement and Table-Valued Parameters.

SQL Server Consulting

In case anyone was wondering: I love SQL Server. And for those that don't know, I also run a SQL Server Consulting business over at SQLServerAudits.com.

So if you're in the market for some professional SQL Server consulting help, make sure to check out the site and feel free to contact me – as my initial consultations are free.


Referral Bonuses
If you know of someone who needs help, send them my way.

Just make sure to give me a heads-up when you send someone, as I'm happy to pay a $50 referral fee (either in cold-hard cash or as an Amazon Gift Card, etc.) for any client that books over 5 hours of consulting help.

And, if you send yourself, just let me know and you'll get a half-hour discount.

Aggregating SQL Server 2008 Date and Time Values

As I covered in a recent video outlining the new Date and Time Data Type improvements to T-SQL 2008, T-SQL now has distinct Date and Time data types. And given that the Time Data Type only tracks time, you might be inclined to think that the following would work as a way to get the sum of all times – because, after all… Time is the only value being tracked:

	TimeValue time NOT NULL
INSERT INTO @Times (TimeValue)
VALUES ('00:12:00'), ('00:22:18'),('00:03:49')

SELECT SUM(TimeValue) FROM @Times

So, it's sort of natural to just assume that SQL Server will add up all the time values in the temp-table and return their SUM as '00:38:07'.

Only, if you try running such a query, you'll get an error:

Msg 8117, Level 16, State 1, Line 8
Operand data type time is invalid for sum operator.

That, of course, is because you're throwing SQL Server for a loop by not being specific with what you want aggregated. Do you want to know the total number of hours? minutes? seconds? milliseconds? nano-seconds?

Accordingly, if you want to aggregate time, you need to specify exactly what units you wish to aggregate. So, the following would work:


But it, of course, only tracks minutes. So, if you're interested in other DATEPARTs you'd have to account for those independently as well – and the sticky part comes when you need to 'carry' over values from one time segment to its parent and so on. (Such as in the case when 67 seconds needs to be converted to 1 minute and 7 seconds.) As such, this is why you'll typically see these kinds of aggregations done from with applications or within UDFs…

New Video: Date and Time Data Types in SQL Server 2008

With any luck, I should have a few new videos out this week and the next. Then I think I might go 'underground' for a while to work on some Business Intelligence Videos.

Otherwise, check out this latest release to learn about new SQL Server 2008 data types designed for larger and more precise date and time storage options.


Video Length: 00:09:08
Video Summary: SQL Server 2008 introduced a number of new date and time data types. In this video you'll learn about these new data types, see some of the common traits that they share, and learn about changes and additions to intrinsic T-SQL date and time functions that have been provided to make working with these new data types that much easier.

Review: Miner3D 7.1

Along with the review of Red-Gate's SQL Data Generator that I recently did for SQL Magazine, I also had the chance to review a really cool BI Visualization Tool called Miner3D. My full review of this product was also in this month's edition of SQL Magazine, so if you're interested in BI, make sure to check out the review and check out Miner3D as well.

If you're serious about BI, I think you'll be very pleasantly surprised at how easy Miner3D is to set up, and how responsive it is when allowing analysis of your data. Even better (and as I pointed out in my review), I think you'll love how it effectively lets your analysts set up what amount to dynamically defined KPIs with virtually no effort or development time.

Review: Red-Gate's SQL Data Generator

If you need to generate testing or performance data, make sure to check out Red-Gate's great SQL Data Generator utility.

I've been using it for quite some time now, and just love how versatile and useful it is.

Check out SQL Server Magazine for my full review.