Have been used SQL Server 2008 over the years, feel like it's worth mentioning some obvious benefits to developers for our day-to-day exercise!
New date types: e.g. a Date that you don't constantly have to strip the time out of and a time that you don't have to extract out of a DateTime field - thank heavens!
File stream type: that stores the stream in the file system but keeps a pointer in the database to it. We often do this manually. So now SQL Server will take care of the plumbing for you.
Hierarchical type: which lets you keep track of hierarchies between objects. I have to actually see this in action to understand it better.
The MERGE statement in t-sql: lets you provide data for the database without having to predetermine if it's an insert or an update. Today you first have to query to see if the primary key exists or not. If not, do an Insert, otherwise do an update. Very cool.
Table-Valued parameters: lets you pass shaped data as parameters to a stored procedure. Cool again, or as Michaeal calls it, "wicked".
GROUPING SETS: which work with teh GROUP BY clause to simplify grouping that you might otherwise perform by using a UNION ALL clause on a bunch of GROUP BY clauses.
XXL User Defined Data Types: (the XXL is my term, not official). UDDTs can exceed 8000 bytes.
More to come! Happy coding!