What changes have you made to your SQL code in SQL2008?

SQL 2008 introduced a bunch of new features, some of which I took annoyance at - the naming for all the different types of new date / time datatypes. (And I thought that varchar(MAX) was a poor name choice too - why not just make TEXT become that? or if a new type was needed why not LONG VARCHAR?)

Anyways, what things in SQL2008 have you gone back and changed old code to take advantage of, or changing your coding style for new code

EDIT Squirrel asked: "It is not broken why change?"

For me the reasons would be

  • performance, so we can get more from our servers
  • maintainability - less development time, or less chance of runtime errors.

but also I might change the way we write CRUD routines if there where enhancements that improved them (MERGE might be one); changing our templates for these would mean that new routines used the new style, and over time old routines would be refactored (for us that would be whenever a change was needed to that routine, or if there was a benefit in the new template that we specifically wanted to apply to certain tables)

more ▼

asked Oct 07, 2009 at 07:06 AM in Default

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

10 answers: sort voted first

Here's what I love -- you can finally assign default values in a declare statement!

declare @i int = 12            
declare @name varchar(10) = 'jeff'            

As far as I can tell, this is new in 2008. And I love it. About time!

more ▼

answered Oct 07, 2009 at 03:46 PM

Jeff Smith gravatar image

Jeff Smith ♦
67 1

I'm old fashioned. I still DECLARE first and ASSIGN separately (any language). I think that Assigning as part of the Declaration adds complexity to the debugging process (i.e. Cost and Time lost). Declarations (OK, maybe not in SQL) start to include Function calls, and all sorts of complexity, all wrapped up in innocent looking DECLARATION statements.
Oct 07, 2009 at 05:12 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

I'm now using merge a lot - never thought I would. Use date and time datatypes to save space but not sure it's such a good idea as it needs a convert to use for some date functions.

Had to use ssis instead of bulk insert/bcp to import utf-8 data - not sure whether that stopped working in 2005 or 2008 though.

Started using reporting services more as it seems to work in 2008.

more ▼

answered Oct 07, 2009 at 02:45 PM

nigelrivett gravatar image


(comments are locked)
10|1200 characters needed characters left

T-SQL wise there's no need to change anything.
IMO the single best T-SQL upgrade is the MERGE statement.
It allows for some really powerful conditional table manipulation all in one statement.
It also lessens the number of locks taken during the so called UPSERT that the previous if exists update else insert method.

more ▼

answered Oct 07, 2009 at 09:51 AM

MladenPrajdic gravatar image

MladenPrajdic ♦
282 2 2 3

(comments are locked)
10|1200 characters needed characters left

It depends on what you're upgrading from. From 2005, very few changes to the core TSQL language that we're not already taking advantage of. From 2000, whoa, the list is long. Let's see, CTE's, OVER(ORDER BY...), XPath... just off the top of my head.

more ▼

answered Oct 07, 2009 at 02:34 PM

Grant Fritchey 1 gravatar image

Grant Fritchey 1

(comments are locked)
10|1200 characters needed characters left

One of my favorite new features of SQL Server 2008 is composable DML.

more ▼

answered Oct 07, 2009 at 04:23 PM

Peso gravatar image

1.6k 5 6 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 07, 2009 at 07:06 AM

Seen: 1632 times

Last Updated: Oct 08, 2009 at 02:38 AM