x

How to find the size of char datatype

I am trying to prove that Char takes the memory even if it has NULL values. Is there any function other than length and datalength?
more ▼

asked Jun 29, 2010 at 10:38 PM in Default

sqlfrenzy 1 gravatar image

sqlfrenzy 1
4 1 1 1

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

3 answers: sort voted first
  1. Create a table with an integer IDENTITY column and a single CHAR column, NULLable.
  2. Insert 10,000 rows with NULL in the char column.
  3. Use [sys].[dm_db_index_physical_stats] to find out the min and max row sizes.
  4. Create another table with an integer IDENTITY column and two CHAR columns, NULLable.
  5. Insert 10,000 rows with NULL in the char columns.
  6. Use [sys].[dm_db_index_physical_stats] to find out the min and max row sizes.
more ▼

answered Jun 30, 2010 at 01:35 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

...or the handy Atlantis Data Space Analyzer--which I would recommend
Jun 30, 2010 at 03:14 PM Scot Hauder
@Scot Hauder - :) thanks!
Jul 01, 2010 at 01:55 AM Matt Whitfield ♦♦
might help to replace zed with S when Googling :)
Jul 01, 2010 at 05:33 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

Why do you need to prove it? Read this [http://msdn.microsoft.com/en-us/library/aa258242(SQL.80).aspx][1] As you can see CHAR is a fixed length variable, but if you SET ANSI_PADDING OFF when CREATE TABLE or ALTER TABLE is executed, the char column defined as NULL is handled as varchar.

[1]: http://msdn.microsoft.com/en-us/library/aa258242(SQL.80).aspx
more ▼

answered Jun 30, 2010 at 12:02 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

Congrats on the 3k sir :)
Jun 30, 2010 at 03:14 PM Matt Whitfield ♦♦
Thank you sir! I have been too busy the last months to keep up with you guys. :) I now work at a company dedicated to SQL server and we have been very busy, but I haven't forgot about AskSSC.
Jun 30, 2010 at 03:39 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

Hakan is completely right, but this question made me think about how to show it expirmentally. So I created a new database, and created one table:

create table CharTestTbl (
col1 char(8000) null)

After that, the database had a size of 3.0 MB with .88 MB free on my system. I then populated that table with 10000 rows of null.

insert into 
    CharTestTbl
(col1)
select top 10000
    null as col1
from
    sys.all_objects
    Join sys.all_objects s2 on 1=1
    Join sys.all_objects s3 on 1=1

After that insert, the size of the database was 179.75 MB with .69 MB free space.

This of course is precisely what you would expect from the reference Hakan provided, but it gives you an experimental demonostration for people that like to see it happen.

more ▼

answered Jun 30, 2010 at 02:57 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

+1 - He asked for proof - you give him proof. All good.
Jun 30, 2010 at 03:14 PM Matt Whitfield ♦♦
thanks...,
Jun 30, 2010 at 10:56 PM sqlfrenzy 1
@Matt, your answer appeared between when I started on mine and finally finished (I was interrupted by a question in between so that was a long time).
Jul 01, 2010 at 06:11 AM TimothyAWiseman
(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:

x13
x7

asked: Jun 29, 2010 at 10:38 PM

Seen: 1869 times

Last Updated: Jun 29, 2010 at 10:38 PM