Very Computer

by **monpea** » Tue, 18 Dec 2001 16:22:03

I imported data from sqlserver 6.5 to sqlserver7.0. The content in sqlserver
6.5 is 500MB. But the content in sqlserver 7.0 is increased to 1000MB. Why?

by **Tibor Karasz** » Tue, 18 Dec 2001 18:46:49

Perhaps because non-clustered indexes carries the clustered key in 7.0 (in 6.5 we always had a
"pointer" which were 6 bytes).
Also, null column are always as wide as the column (in 6.x they could be treated as "varchar").

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

> I imported data from sqlserver 6.5 to sqlserver7.0. The content in sqlserver
> 6.5 is 500MB. But the content in sqlserver 7.0 is increased to 1000MB. Why?

by **BP Margoli** » Wed, 19 Dec 2001 09:18:21

Just a minor clarification on Tibor's post ...

In SQL Server 6.5, nullable CHAR columns were internally treated as
VARCHARs. In SQL Server 7.0 / 2000, nullable CHAR columns are actual CHARs.

Thus if you had, for instance, a column declared as CHAR (100) NULL in SQL
Server 6.5, and you usually only had 50 bytes of information in the column,
SQL Server 6.5 would only store the 50 bytes. In SQL Server 7.0 / 2000, the
column always holds a full 100 bytes.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> I imported data from sqlserver 6.5 to sqlserver7.0. The content in
sqlserver
> 6.5 is 500MB. But the content in sqlserver 7.0 is increased to 1000MB.
Why?

by **monpea** » Wed, 19 Dec 2001 11:32:00

Thanks a lot. But I haven't used any indexes in the database except a simple
index on a small table.

Quote:> Perhaps because non-clustered indexes carries the clustered key in 7.0 (in
6.5 we always had a
> "pointer" which were 6 bytes).
> Also, null column are always as wide as the column (in 6.x they could be

treated as "varchar").

Quote:
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:> > I imported data from sqlserver 6.5 to sqlserver7.0. The content in
sqlserver
> > 6.5 is 500MB. But the content in sqlserver 7.0 is increased to 1000MB.
Why?

by **Tibor Karasz** » Wed, 19 Dec 2001 16:26:11

What about nullable columns? See BP's post for elaboration (thanks BP!).

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

> Thanks a lot. But I haven't used any indexes in the database except a simple
> index on a small table.

> > Perhaps because non-clustered indexes carries the clustered key in 7.0 (in
> 6.5 we always had a
> > "pointer" which were 6 bytes).
> > Also, null column are always as wide as the column (in 6.x they could be
> treated as "varchar").

> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

> > > I imported data from sqlserver 6.5 to sqlserver7.0. The content in
> sqlserver
> > > 6.5 is 500MB. But the content in sqlserver 7.0 is increased to 1000MB.
> Why?

by **monpe** » Wed, 19 Dec 2001 18:57:46

Thanks a lot. But most of the columns in my database are decimal and int.
Also it includes some varchar columns, but there are few char columns.

> Just a minor clarification on Tibor's post ...

> In SQL Server 6.5, nullable CHAR columns were internally treated as
> VARCHARs. In SQL Server 7.0 / 2000, nullable CHAR columns are actual CHARs.

> Thus if you had, for instance, a column declared as CHAR (100) NULL in SQL
> Server 6.5, and you usually only had 50 bytes of information in the column,
> SQL Server 6.5 would only store the 50 bytes. In SQL Server 7.0 / 2000, the
> column always holds a full 100 bytes.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.

> > I imported data from sqlserver 6.5 to sqlserver7.0. The content in
> sqlserver
> > 6.5 is 500MB. But the content in sqlserver 7.0 is increased to 1000MB.
> Why?

by **BP Margoli** » Fri, 21 Dec 2001 11:14:19

Execute DBCC SHOWCONTIG on both the SQL Server 6.5 and SQL Server 7.0
databases, and examine the output ... specifically, take a look at the Avg.
Page density. Perhaps you just need to do a DBCC DBREINDEX on (probably) the
clustered index(es).

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

> Thanks a lot. But most of the columns in my database are decimal and int.
> Also it includes some varchar columns, but there are few char columns.

> > Just a minor clarification on Tibor's post ...

> > In SQL Server 6.5, nullable CHAR columns were internally treated as
> > VARCHARs. In SQL Server 7.0 / 2000, nullable CHAR columns are actual
CHARs.

> > Thus if you had, for instance, a column declared as CHAR (100) NULL in
SQL
> > Server 6.5, and you usually only had 50 bytes of information in the
column,
> > SQL Server 6.5 would only store the 50 bytes. In SQL Server 7.0 / 2000,
the
> > column always holds a full 100 bytes.

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.

> > > I imported data from sqlserver 6.5 to sqlserver7.0. The content in
> > sqlserver
> > > 6.5 is 500MB. But the content in sqlserver 7.0 is increased to 1000MB.
> > Why?

by **monpe** » Fri, 21 Dec 2001 14:06:18

Thanks a lot. But most of the columns in my database are decimal and int.
Also it includes some varchar columns, but there are few char columns.

by **monpe** » Sat, 22 Dec 2001 12:06:56

This is the result of excuting DBCC at one of the tables:
sql 7:
- Pages Scanned................................: 6859
- Extents Scanned..............................: 859
- Extent Switches..............................: 858
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.88% [858:859]
- Extent Scan Fragmentation ...................: 87.66%
- Avg. Bytes Free per Page.....................: 1449.0
- Avg. Page Density (full).....................: 82.10%
sql 6.5
- Pages Scanned................................: 5226
- Extent Switches..............................: 655
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.70% [653:656]
- Avg. Bytes free per page.....................: 196.3
- Avg. Page density (full).....................: 90.25%
- Overflow Pages...............................: 5225
- Avg. Bytes free per Overflow page............: 196.3
- Avg. Overflow Page density...................: 90.3%
- Disconnected Overflow Pages..................: 0
So far as the information I can found, it takes up 8 kB for every page
in sqlserver 7.0 and 2kB in sqlserver 6.5. I wonder if this is true.

Besides, I watched the table size of this table in sql 6.5. According
to the size, I found it only takes up a bit more than 2 bytes for a
column of decimal (18.0) in average. I wonder if the data are stored
compressed.

by **BP Margoli** » Sat, 22 Dec 2001 14:52:36

Quote:> So far as the information I can found, it takes up 8 kB for every page
> in sqlserver 7.0 and 2kB in sqlserver 6.5. I wonder if this is true.

Yes, this is true.

Quote:> Besides, I watched the table size of this table in sql 6.5. According
> to the size, I found it only takes up a bit more than 2 bytes for a
> column of decimal (18.0) in average. I wonder if the data are stored
> compressed.

A column defined as DECIMAL (18, 0) in either SQL Server 6.5 or SQL Server
7.0 will take the same amount of space ... 9 bytes. SQL Server does not
store data in a compressed format.

What is peculiar, at least to me, is that the number of pages for the SQL
Server 7.0 table is 6859 while the number of pages for the SQL Server 6.5
table is only 5226. Assuming that the table does not have nullable CHAR
columns, I would have expected that, if the table has the same number of
rows, that the number of pages in SQL Server 7.0 would be approximately
one-quarter that of the number of pages in SQL Server 6.5 ... the 8K page
size vs. 2K page size thing :-)

Can you verify that the number of rows in the table is the same and that
there are no nullable CHAR columns in this table? Could you also post the
complete schema, including constraints and indexes of the table? Enterprise
Manager can help you produce the table schema. Just be sure to request all
the options so that all the constraints and indexes are also printed.

I seem to recall that SQL Server 7.0 and/or SQL Server 2000 would sometimes
produce extraordinary large number of pages for statistics. If you see an
index beginning with something like "_WA_" then that is a statistic
automatically generated by SQL Server, and perhaps that's where the problem
of the discrepancy in size lays.

What do you get when you do:

EXEC sp_help {tablename}
EXEC sp_spaceused {tablename}

for this table?

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> This is the result of excuting DBCC at one of the tables:
> sql 7:
> - Pages Scanned................................: 6859
> - Extents Scanned..............................: 859
> - Extent Switches..............................: 858
> - Avg. Pages per Extent........................: 8.0
> - Scan Density [Best Count:Actual Count].......: 99.88% [858:859]
> - Extent Scan Fragmentation ...................: 87.66%
> - Avg. Bytes Free per Page.....................: 1449.0
> - Avg. Page Density (full).....................: 82.10%
> sql 6.5
> - Pages Scanned................................: 5226
> - Extent Switches..............................: 655
> - Avg. Pages per Extent........................: 8.0
> - Scan Density [Best Count:Actual Count].......: 99.70% [653:656]
> - Avg. Bytes free per page.....................: 196.3
> - Avg. Page density (full).....................: 90.25%
> - Overflow Pages...............................: 5225
> - Avg. Bytes free per Overflow page............: 196.3
> - Avg. Overflow Page density...................: 90.3%
> - Disconnected Overflow Pages..................: 0
> So far as the information I can found, it takes up 8 kB for every page
> in sqlserver 7.0 and 2kB in sqlserver 6.5. I wonder if this is true.

> Besides, I watched the table size of this table in sql 6.5. According
> to the size, I found it only takes up a bit more than 2 bytes for a
> column of decimal (18.0) in average. I wonder if the data are stored
> compressed.

by **monpe** » Sun, 23 Dec 2001 13:13:56

I?m puzzled for some questions as follow:
1. In SQL Server 7.0, when I executed sp_spaceused, it was said that
there are 63365 rows in this table. But if executed ?select count(*)
from meter_freeze_data_2001_10?, it was said that there are 34295
rows consistent with the number of which in SQL Server 6.5.
2. The difference between the two tables is that when I imported the
table from SQL Server 6.5 to 7.0, all the indexes and constraints are
lost. In this case, the content of the table in SQL Server 6.5 should
be larger than that in SQL Server 7.0.
3. There are many null values in this table. The content of the table
in SQL Server 6.5 seems impossible except that the null values are
stored less than 9 bytes.

The result of executing the commands what you said is as follow:

in SQL Server 6.5

Name Owner Type

When_created
------------------------------ ------------------------------
----------------------------------------------------------------------
---------------------------
METER_FREEZE_DATA_2001_10 dbo user
table 10 1
2001 6:00AM

Data_located_on_segment
------------------------------
default

Column_name Type Length
Prec Scale Nullable TrimTrailingBlanks
FixedLenNullInSource
------------------------------ ------------------------------ ------
----- ----- -----------------------------------
-----------------------------------
-----------------------------------
ID smallint 2 5
0 no (n/a)
(n/a)
BIAO_NO tinyint 1 3
0 no (n/a)
(n/a)
FREEZE_DAY tinyint 1 3
0 no (n/a)
(n/a)
FREEZE_HOUR tinyint 1 3
0 no (n/a)
(n/a)
FREEZE_MINUTE tinyint 1 3
0 no (n/a)
(n/a)
DATA1 decimal 9
18 0 yes (n/a)
(n/a)
DATA2 decimal 9
18 0 yes (n/a)
(n/a)
DATA3 decimal 9
18 0 yes (n/a)
(n/a)
DATA4 decimal 9
18 0 yes (n/a)
(n/a)
DATA5 decimal 9
18 0 yes (n/a)
(n/a)
DATA6 decimal 9
18 0 yes (n/a)
(n/a)
DATA7 decimal 9
18 0 yes (n/a)
(n/a)
DATA8 decimal 9
18 0 yes (n/a)
(n/a)
DATA9 decimal 9
18 0 yes (n/a)
(n/a)
DATA10 decimal 9
18 0 yes (n/a)
(n/a)
DATA11 decimal 9
18 0 yes (n/a)
(n/a)
DATA12 decimal 9
18 0 yes (n/a)
(n/a)
DATA13 decimal 9
18 0 yes (n/a)
(n/a)
DATA14 decimal 9
18 0 yes (n/a)
(n/a)
DATA15 decimal 9
18 0 yes (n/a)
(n/a)
DATA16 decimal 9
18 0 yes (n/a)
(n/a)
DATA17 decimal 9
18 0 yes (n/a)
(n/a)
DATA18 decimal 9
18 0 yes (n/a)
(n/a)
DATA19 decimal 9
18 0 yes (n/a)
(n/a)
DATA20 decimal 9
18 0 yes (n/a)
(n/a)
DATA21 decimal 9
18 0 yes (n/a)
(n/a)
DATA22 decimal 9
18 0 yes (n/a)
(n/a)
DATA23 decimal 9
18 0 yes (n/a)
(n/a)
DATA24 decimal 9
18 0 yes (n/a)
(n/a)
DATA25 decimal 9
18 0 yes (n/a)
(n/a)
DATA26 decimal 9
18 0 yes (n/a)
(n/a)
DATA27 decimal 9
18 0 yes (n/a)
(n/a)
DATA28 decimal 9
18 0 yes (n/a)
(n/a)
DATA29 decimal 9
18 0 yes (n/a)
(n/a)
DATA30 decimal 9
18 0 yes (n/a)
(n/a)
DATA31 decimal 9
18 0 yes (n/a)
(n/a)
DATA32 decimal 9
18 0 yes (n/a)
(n/a)
DATA33 decimal 9
18 0 yes (n/a)
(n/a)
DATA34 decimal 9
18 0 yes (n/a)
(n/a)
DATA35 decimal 9
18 0 yes (n/a)
(n/a)
DATA36 decimal 9
18 0 yes (n/a)
(n/a)
DATA37 decimal 9
18 0 yes (n/a)
(n/a)
DATA38 decimal 9
18 0 yes (n/a)
(n/a)
DATA39 decimal 9
18 0 yes (n/a)
(n/a)
DATA40 decimal 9
18 0 yes (n/a)
(n/a)
DATA41 decimal 9
18 0 yes (n/a)
(n/a)
DATA42 decimal 9
18 0 yes (n/a)
(n/a)
DATA43 decimal 9
18 0 yes (n/a)
(n/a)
DATA44 decimal 9
18 0 yes (n/a)
(n/a)
DATA45 decimal 9
18 0 yes (n/a)
(n/a)
DATA46 decimal 9
18 0 yes (n/a)
(n/a)
DATA47 decimal 9
18 0 yes (n/a)
(n/a)
...

read more »

by **BP Margoli** » Mon, 24 Dec 2001 02:58:19

The system stored procedure sp_spaceused, for reasons of performance,
accesses the system tables in order to get the number of rows ... it does
not actually perform a SELECT COUNT(*) against the table. The discrepancy
comes from the fact that Microsoft does not guarantee that the information
on the row count held in the system table is accurate. The sp_spaceused

COUNT(*) against the table and to use that information to update the system
tables.

You have, actually, and perhaps unintentionally, provided some additional
information of importance on this post. I had assumed that you "upgraded"
from SQL Server 6.5 to SQL Server 7.0 using the Upgrade Wizard provided in
SQL Server 7.0. However the Upgrade Wizard would have brought along the
indexes and constraints, so it's clear that you didn't use the Upgrade
Wizard.

From a space viewpoint, the only time NULLs are significant, between SQL
Server 6.5 and SQL Server 7.0 is when one has NULLable char or varchar
columns. NULLable decimal columns just don't really affect size.

BTW, if I might suggest, next time around consider attaching the information
rather than posting it ... it's going to be a lot more readable as an
attachment :-)

Please, try an experiment ... do:

select *
into new_METER_FREEZE_DATA_2001_10
from METER_FREEZE_DATA_2001_10
go

drop table METER_FREEZE_DATA_2001_10
go

exec sp_rename 'new_METER_FREEZE_DATA_2001_10' , 'METER_FREEZE_DATA_2001_10'

and then define a **clustered** index corresponding to the
"indexmeter_freeze_da" index on the SQL Server 6.5 box, and then re-run the
sp_spaceused stored procedure.

Don't repost the table schema information, just post the space used
information.

BTW, if it is at possible, I would suggest using the SQL Server 7.0 Upgrade
Wizard against the SQL Server 6.5 database, and see what the results are.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

by **monpe** » Mon, 24 Dec 2001 20:25:27

I created the new table and a cluster index on it In SQL Server 6.5 (I
haven't replaced the original table because I think there's no
diference to this test). I found that the size is a bit smaller than
before. The result of executing sp_spaceused is as follow:

name rows reserved data
index_size unused
-------------------- ----------- ------------------ ------------------
------------------ ------------------
new_METER_FREEZE_DAT 34295 10048 KB 9978 KB
56 KB 14 KB

I had tried to "upgrade" from 6.5 to 7.0 for times. The two database
are installed in two computer all based on Windows NT 4.0. In the
process of the upgrading, It was said that sql server 6.5 on the
remote computer can't be restarted. So I had to "Import".

by **BP Margoli** » Tue, 25 Dec 2001 03:14:52

Sorry, if I wasn't clear. I meant to replace the table on SQL Server 7.0,
not on SQL Server 6.5. After all, the problem with "too much" space is
occurring on SQL Server 7.0 not on SQL Server 6.5 :-)

Re: the problem with the Upgrade Wizard. Did you use operating system and
SQL Server logins with sufficient permissions? The "best" logins are those
of a domain administrator and SA. Keep in mind that in order to restart SQL
Server, one has to start the SQL Server service. Not even an SA, who lacks
appropriate operating system permissions, can start a service. To WinNT, the
SQL Server service is just another program ... there ain't anything
"special" about it from the viewpoint of the operating system.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> I created the new table and a cluster index on it In SQL Server 6.5 (I
> haven't replaced the original table because I think there's no
> diference to this test). I found that the size is a bit smaller than
> before. The result of executing sp_spaceused is as follow:

> name rows reserved data
> index_size unused
> -------------------- ----------- ------------------ ------------------
> ------------------ ------------------
> new_METER_FREEZE_DAT 34295 10048 KB 9978 KB
> 56 KB 14 KB

> I had tried to "upgrade" from 6.5 to 7.0 for times. The two database
> are installed in two computer all based on Windows NT 4.0. In the
> process of the upgrading, It was said that sql server 6.5 on the
> remote computer can't be restarted. So I had to "Import".

by **Erland Sommarsko** » Tue, 25 Dec 2001 09:12:30

> From a space viewpoint, the only time NULLs are significant, between SQL
> Server 6.5 and SQL Server 7.0 is when one has NULLable char or varchar
> columns. NULLable decimal columns just don't really affect size.

I fairly sure that this incorrect. That is, SQL 6.5 does not store a NULL
in a decimal column at the full size, as I recall. I have some course
material on that at the office, but I don't intend to be there for the
next three days.

However, I ran this script in 6.5, and it seemed to confirmed my beliefs:

use tempdb
go
create table alfons (a decimal(18, 9) NULL)
create table bettan (a decimal(18, 9) NULL)
go
insert alfons
select null
from sysobjects a
cross join sysobjects b
cross join sysobjects c
go
insert bettan
select 12.090904
from sysobjects a
cross join sysobjects b
cross join sysobjects c
go
exec sp_spaceused alfons, true
exec sp_spaceused bettan, true
go

I got the output:

(13824 row(s) affected)

DBCC execution completed. If DBCC printed error messages, see your System Administrator.

name rows reserved data index_size unused
--------- ----------- ------------ --------- ------------ ------------------
alfons 13824 112 KB 108 KB 0 KB 4 KB

DBCC execution completed. If DBCC printed error messages, see your System Administrator.

name rows reserved data index_size unused
--------- ----------- ------------ --------- ------------ ------------------
bettan 13824 254 KB 248 KB 0 KB 6 KB

On SQL2000, the output was

(13824 row(s) affected)

name rows reserved data index_size unused
--------- ----------- ------------ ---------- ------------ ------------------
alfons 13824 328 KB 264 KB 8 KB 56 KB

name rows reserved data index_size unused
--------- ----------- ------------ ---------- ------------ ------------------
bettan 13824 328 KB 264 KB 8 KB 56 KB

Thus, there a radical difference in size on 6.5, but on SQL2000 the
tables are equally big.

--
Erland Sommarskog, Abaris AB

SQL Server MVP

Very Computer

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0

The Increased Content after Upgrading Sqlserver 6.5 to Sqlserver 7.0