Data storage

Does SQL SErver comprss the data it stores? or is a varchar(50) take up
that much space in the db?Jayme wrote:
> Does SQL SErver comprss the data it stores? or is a varchar(50) take up
> that much space in the db?
>
A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
bytes, plus some overhead. A field defined as VARCHAR(50) containing
the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
in Books Online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Thu, 27 Jul 2006 15:17:05 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>Jayme wrote:
>> Does SQL SErver comprss the data it stores? or is a varchar(50) take up
>> that much space in the db?
>A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
>bytes, plus some overhead. A field defined as VARCHAR(50) containing
>the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
>in Books Online.
CHAR has overhead? I thought only VARCHAR had overhead.
Roy|||Roy Harvey wrote:
> On Thu, 27 Jul 2006 15:17:05 -0500, Tracy McKibben
> <tracy@.realsqlguy.com> wrote:
>> Jayme wrote:
>> Does SQL SErver comprss the data it stores? or is a varchar(50) take up
>> that much space in the db?
>> A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
>> bytes, plus some overhead. A field defined as VARCHAR(50) containing
>> the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
>> in Books Online.
> CHAR has overhead? I thought only VARCHAR had overhead.
> Roy
Unless I'm mistaken, EVERY datatype has a small amount of overhead just
due to the column's existence. Isn't it one byte per column?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Unless I'm mistaken, EVERY datatype has a small amount of overhead just
> due to the column's existence. Isn't it one byte per column?
Perhaps you are thinking about the null bitmap that SQL Server includes for
both fixed and variable length columns. The total row overhead of the
bitmap field is CEILING(#cols * 8).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:u%23jKUZesGHA.3556@.TK2MSFTNGP03.phx.gbl...
> Roy Harvey wrote:
>> On Thu, 27 Jul 2006 15:17:05 -0500, Tracy McKibben
>> <tracy@.realsqlguy.com> wrote:
>> Jayme wrote:
>> Does SQL SErver comprss the data it stores? or is a varchar(50) take up
>> that much space in the db?
>> A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
>> bytes, plus some overhead. A field defined as VARCHAR(50) containing
>> the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
>> in Books Online.
>> CHAR has overhead? I thought only VARCHAR had overhead.
>> Roy
> Unless I'm mistaken, EVERY datatype has a small amount of overhead just
> due to the column's existence. Isn't it one byte per column?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Dan Guzman wrote:
>> Unless I'm mistaken, EVERY datatype has a small amount of overhead just
>> due to the column's existence. Isn't it one byte per column?
> Perhaps you are thinking about the null bitmap that SQL Server includes for
> both fixed and variable length columns. The total row overhead of the
> bitmap field is CEILING(#cols * 8).
>
No, I'm just not wording my thoughts properly. What I'm trying to say
is, the mere fact that a CHAR column exists is causing some overhead in
the table. The column definition has to be stored somewhere. I guess
my original response should have stated that for all practical purposes,
VARCHAR has overhead that CHAR does not.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Fri, 28 Jul 2006 07:52:42 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>The column definition has to be stored somewhere.
syscolumns, or whatever serves that purpose these days.

Data storage

Does SQL SErver comprss the data it stores? or is a varchar(50) take up
that much space in the db?Jayme wrote:
> Does SQL SErver comprss the data it stores? or is a varchar(50) take up
> that much space in the db?
>
A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
bytes, plus some overhead. A field defined as VARCHAR(50) containing
the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
in Books Online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Thu, 27 Jul 2006 15:17:05 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:

>Jayme wrote:
>A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
>bytes, plus some overhead. A field defined as VARCHAR(50) containing
>the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
>in Books Online.
CHAR has overhead? I thought only VARCHAR had overhead.
Roy|||Roy Harvey wrote:
> On Thu, 27 Jul 2006 15:17:05 -0500, Tracy McKibben
> <tracy@.realsqlguy.com> wrote:
>
> CHAR has overhead? I thought only VARCHAR had overhead.
> Roy
Unless I'm mistaken, EVERY datatype has a small amount of overhead just
due to the column's existence. Isn't it one byte per column?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Unless I'm mistaken, EVERY datatype has a small amount of overhead just
> due to the column's existence. Isn't it one byte per column?
Perhaps you are thinking about the null bitmap that SQL Server includes for
both fixed and variable length columns. The total row overhead of the
bitmap field is CEILING(#cols * 8).
Hope this helps.
Dan Guzman
SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:u%23jKUZesGHA.3556@.TK2MSFTNGP03.phx.gbl...
> Roy Harvey wrote:
> Unless I'm mistaken, EVERY datatype has a small amount of overhead just
> due to the column's existence. Isn't it one byte per column?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Dan Guzman wrote:
> Perhaps you are thinking about the null bitmap that SQL Server includes fo
r
> both fixed and variable length columns. The total row overhead of the
> bitmap field is CEILING(#cols * 8).
>
No, I'm just not wording my thoughts properly. What I'm trying to say
is, the mere fact that a CHAR column exists is causing some overhead in
the table. The column definition has to be stored somewhere. I guess
my original response should have stated that for all practical purposes,
VARCHAR has overhead that CHAR does not.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Fri, 28 Jul 2006 07:52:42 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:

>The column definition has to be stored somewhere.
syscolumns, or whatever serves that purpose these days.

Data Source Column Changes

I have some Dynamic SQL that builds a table with the top 20 item numbers as columns, Stores as rows and an X marking the intersection of Store/Item. This produces a table which is a matrix showing which stores are low on which items.
This table is generated once a week and each week will have different column names because the item numbers which the stores are low on changes from week to week.
How can I write a report that will handle the column names of the data table changing without having to do maintenance on the report every week?

Thanks,
Mark Redman.

One way of doing this is to move the dynamic SQL into a stored procedure and return a dataset which always has fixed column names. The actual item information (item number) would be e.g. the first row in that generated dataset.

-- Robert

Data Integration Tools Recommendation

Hi all

In our company we do a lot of data imports from various files/data stores.

I was wondering what are the good Data Integration software out there which can provide "industrial-strength" to this process. I heard one of them is Informatica (www.informatica.com) but that seems more for Data Warehousing.

Our DB is SQL Server. Maybe we should change that too?

Comments/Suggestions, anyone?

Much Appreciated,
ManikWhy can't you use DTS from SQL server?|||Hi Satya

We use DTS right now. However, the problem is that DTS is not giving us very good performance.

What we really need is a software that can help us model the data import process and then run the import and provide logging etc.

DTS works ok for now but we have cases where we have to update several thousand records against a > 1000 row Excel file and we have to do this very often (and also on demand). This is very slow.

Also, the various imports are getting bigger and more cumbersome to manage, hence the need for an integrated tool that can create, manage and execute these imports in a systematic manner.

Regards
Manik|||Not aware of such tools but you can search under SQL Performance (http://www.sql-server-performance.com/) website for any recommendations and information.

HTH