x

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 '09 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 '09 at 03:46 PM

Jeff Smith gravatar image

Jeff Smith ♦
56 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 '09 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 '09 at 02:45 PM

nigelrivett gravatar image

nigelrivett
92

(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 '09 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 '09 at 02:34 PM

Grant Fritchey 1 gravatar image

Grant Fritchey 1
22

(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 '09 at 04:23 PM

Peso gravatar image

Peso
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1816

asked: Oct 07 '09 at 07:06 AM

Seen: 1322 times

Last Updated: Oct 08 '09 at 02:38 AM