RE: Optimising SQL column data

[GregAtGregLowDotCom]

Hi Greg,

 

Which edition of SQL Server? (Compression at the page level is awesome for
this but not everyone has it)

 

It would be really good if the product had a real concept of an enumeration
but it doesn't:

 

Regards,

 

Greg

 

Dr Greg Low

 

1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax


SQL Down Under | Web:   www.sqldownunder.com

 

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com]
On Behalf Of Greg Keogh
Sent: Wednesday, 26 June 2013 3:36 PM
To: ozDotNet
Subject: Optimising SQL column data

 

Folks, I'm loading hundreds of thousands of logging lines into a SQL Server
table and I noticed that one of the columns contains only short words taken
from a handful of choices. So the column reads like FOO FOO FOO FOO BAR FOO
FOO FOO FOO BAR BANG FOO FOO FOO etc. The column will rarely be used.

 

Would SQL boffins in here consider turning this column into something more
space saving, like a single byte flag for example? Perhaps to save space or
improve performance.

 

Greg K

Re: Optimising SQL column data

[David Connors]

On Wed, Jun 26, 2013 at 3:35 PM, Greg Keogh  wrote:

> Folks, I'm loading hundreds of thousands of logging lines into a SQL
> Server table and I noticed that one of the columns contains only short
> words taken from a handful of choices. So the column reads like FOO FOO FOO
> FOO BAR FOO FOO FOO FOO BAR BANG FOO FOO FOO etc. The column will rarely be
> used.
>
> Would SQL boffins in here consider turning this column into something more
> space saving, like a single byte flag for example? Perhaps to save space or
> improve performance.
>

A packed/encoded format will use less space - but whether it is worth doing
depends on your app and the rest of the table (i.e. if it is 5000 bytes per
row then saving a few dozen in one field isn't going to make much
difference).

If the column is rarely used then you will rarely see a performance
difference.

Unless you're writing a very high throughput system, I would value
readability / maintainability over absolute performance or storage space.

David.

Re: Optimising SQL column data

[Greg Keogh]

>
> Which edition of SQL Server? (Compression at the page level is awesome for
> this but not everyone has it)
>

I have both SQL Server 2012 standard (for fiddling with), but I mostly use
the previous one, what was it 2008? I haven't heard about the new
compression you mention, so I'll run a few searches on it. However, it
sounds rather low-level and dangerous -- Greg K

RE: Optimising SQL column data

[GregAtGregLowDotCom]

Actually it's the best thing in the box for 2008 but it's Enterprise Edition
only. That's why I was checking whether you were working on a project with
it.

 

Regards,

 

Greg

 

Dr Greg Low

 

1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax


SQL Down Under | Web:  <http://www.sqldownunder.com/> www.sqldownunder.com

 

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com]
On Behalf Of Greg Keogh
Sent: Wednesday, 26 June 2013 5:53 PM
To: ozDotNet
Subject: Re: Optimising SQL column data

 

Which edition of SQL Server? (Compression at the page level is awesome for
this but not everyone has it)

 

I have both SQL Server 2012 standard (for fiddling with), but I mostly use
the previous one, what was it 2008? I haven't heard about the new
compression you mention, so I'll run a few searches on it. However, it
sounds rather low-level and dangerous -- Greg K