question

Index8 avatar image
Index8 asked

SQL 2008 VS. SQL 2005

Have been playing with SQL 2005; Recently our Sharepoint SQL Server have been migrated to SQL server 2008. Saw some more folders/features/controls from the 2K8 MMS, didn't really get to use any new STUFF. Just using it as 2K5.

To those who have enjoyed the goodies in 2k8, wanna share some nice things you have done with 2008 that 2005 didn't have? THANKS!

sql-server-2008sql-server-2005features
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jorge Segarra avatar image
Jorge Segarra answered

Arguably the most potent tool for DBA's in 2008 would be Policy Based Management. Tom LaRock did a nice 3-video walk-through with Brent Ozar which you should probably check out:

http://tomlarock.com/?s=policy+based+management

I also cover PBM in my blog/talks/presentations

http://sqlchicken.com/category/policy-based-management/

My PBM fanboy-ism aside, here's the list to all the new features available in SQL 2008

http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

K. Brian Kelley avatar image
K. Brian Kelley answered

PBM is nice as Jorge has mentioned. From the security side, the Audit object and Transparent Data Encryption are also nice, but both require Enterprise Edition.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

For me, the single most important enhancement is the sys.sql_expression_dependencies system view - at last a dependencies view that isn't broken.

A close second comes filtered indexes - but really SQL Server is on catch up in that arena with regard to other DBMSs.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RBarryYoung avatar image
RBarryYoung answered

Well, I've used the Date & Time datatypes, compound operators ( @i += 1 ), GROUPING SETS, MERGE, Row constructors and the enhanced Declare.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

The enhancements to SSMS2008 over SSMS2005 are fairly significant, I especially like adding missing index information to execution plans and the addition of intellisense. The ability to query multiple servers at once is very useful in some limited situations. http://www.sqlservercentral.com/articles/Management+Studio/63536/ provides an introduction to some of the SSMS enhancements.

Also, small tweaks to TSQL such as the ability to initialize a variable when it is declared are nice in a readability sense.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

My company has found that the most useful new function is spatial data. We deal with location information all the time, and being able to query directly against the data is a huge advantage.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

I think some of the best features in SQL2008 is Date datatype (no more conversions to strip the time), Data compression (requires less IO operations) and filtered indexes.

I activated PAGE compression in one of our databases and reduced the size from about 1 Tb to 400Gb and increased the performance due to less IO. Data compression uses more CPU, but we are using a server with 4 quad processors and 64Gb memory. :)

With filtered indexes i can create more covering indexes with increased read performance with only slight write overhead. Covering indexes reduces Key lookups.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

I did a post recently on features in SQL 2008 that you can take advantage of without having to change your applications. It's at http://msmvps.com/blogs/robfarley/archive/2009/12/18/high-roi-items-for-sql-server-2008.aspx

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.