How Much Space Is Wasted In Unused Fields In SQL Server? - Database Discussion Boards

Good Day All

i have the Following string in a Field

 'This
is the 
 
reason that i did this>> '

i want to remove spaces in TSQL to this

'This is the reason that i did this>>'

THanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com

here it is

declare @NewLine char(2)
set @NewLine=char(13)+char(10)

select  rtrim(ltrim(Replace('This
is the 
 
reason that i did this>> ',@NewLine,'')))

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

You are a Star thanks Smile | :)

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com

You are welcome. Smile | :)

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

That might not be what you want if the string is 'hello\r\nworld'.

I wrote a CLR function to do that, compresses whitespace to a single SPACE:

namespace PIEBALD.Lib.LibExt.Compress
{
  public static partial class LibExt
  { 
    public static string
    Compress
    (
      this string Victim
    )
    {
      System.Text.StringBuilder result = new System.Text.StringBuilder ( Victim.Length ) ;

      bool first = true ;

      for ( int i = 0 ; i < Victim.Length ; i++ )
      {
        if ( !System.Char.IsWhiteSpace ( Victim [ i ] ) )
        {
          result.Append ( Victim [ i ] ) ;

          first = true ;
        }
        else if ( first )
        {
          result.Append ( ' ' ) ;

          first = false ;
        }
      }

      return ( result.ToString() ) ;
    }
  }
}

CREATE ASSEMBLY Library FROM 'C:\bin\Library.Sql.dll'
GO
CREATE FUNCTION dbo.Compress(@Source NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME Library.[PIEBALD.Lib.LibExt.Compress.LibExt].Compress; 
GO

Here's a generic example:

SQL

create table t (s sysname)
insert into t select 'this     is the reason                         that I did this.               '

	-- convert tabs to spaces
	update	t set s = replace(s, '	',' ')
	where	charindex('	', s) > 0

	-- now do the work.
	while 1=1
	begin
		update t
		set		s = substring(s, 1, charindex('  ', s, 1)-1) + ' ' + ltrim(substring(s,charindex('  ', s, 1), 8000))
		where	charindex('  ', s, 1) > 0

		if @@rowcount = 0
			break
	end

	select	s
	from	t

I've been toying with an inventory tracking program for my company for years, but I keep getting stuck on decisions I'm not smart enough to make. I really hate wasted space, and try to optimize table structures to contain only the fields they need, but that means that I'll need a custom table for almost every item in inventory, and that's a nightmare. In this particular dilemma (there are others), I don't know what happens with unused fields when records are stored in a SQL Server database. Do they take up space, or are records compressed when fields are left empty?

For example, we have transformers. They can be substation or distribution transformers, they can be pad-mounted or pole-mounted, they can be oil-filled or dry, they all have primary and secondary voltages, as well as kVA ratings. Any of them can have de-energized tap changers (DETC) which can change their voltage ratios, but which can only be changed while the transformer is off. Substation transformers can also have Load Tap Changers (LTCs), which can be changed on the fly in response to load changes, which can cause the output voltage to vary. If equipped with a LTC, the data collected for a transformer every month has to include how many steps it has moved, and its high and low step values.

My dilemma is that, if I try to create a Transformer table with every possible parameter to be recorded for every transformer, most records will contain a bunch of empty fields. If I try to create a schema that includes a separate table for every variation of transformer type, I'll have to manage a dozen or more different tables, not only in my database, but in the code that accesses it.

From a coding standpoint, a single master Transformer table is far easier to manage, and is the approach I'd prefer to follow. But if 90% of the transformers in use have 40% of their data fields set to null, will they still consume the same storage space as they would if all fields contained data? Will I be taking a performance hit by using the single, universal table structure? Or is it really worth the effort to create different tables for each possible configuration, and to provide customized code to manipulate each table?

Will Rogers never met me.

Roger Wright wrote:
Do they take up space, or are records compressed when fields are left empty

I think this depends on the data type CHAR will use the space allocated but VARCHAR uses the spare required by the data. I'm not 100% sure that is correct as I have had no interest in the issue, I always use VARCHAR unless the field is <5 characters and is required to be filled.

As to your table design, leaving empty fields has little impact on database size but may have different implications. FKs are not valid with empty fields. Maintenance of all those fields can be a bitch.

You could also go down the path of attributes where you have some base types with the fields common to all (or at least the bulk) of items in the machinery type. Then you have a bunch of attributes that can be assigned to each item. Caveat - going too far down this path leads to insanity as you can completely lose control of the attributes.

Never underestimate the power of human stupidity
RAH

Great answer (as usual). Smile | :)

The only additional point I'd add is that regardless of data type, if a field is set to NULL, I don't believe there is any 'wasted' space. But your point about CHAR vs VARCHAR is excellent.

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Great info, as usual! Thanks! Big Grin | :-D

I think I'll skip the attributes and leave the empty fields; none will be used for foreign keys, in any case. The VARCHAR thing I didn't know, but it makes sense to me.

Will Rogers never met me.

When faced with a problem like this, I figure out what the largest record is for both character and numeric data (usually splitting the latter into integer and double). I then build a table with sufficient fields to handle the most complex of data, such that there is a field for every data item in each category, plus a field for item type.

These columns are then labelled as Text01...Textnn, Int01...Intnn and so on. Any text field that needs to be larger than 8 bytes is a VARCHAR, and is populated, by default, with a single space, not left at NULL. No columns permit NULL.

I then build a second table, with the same number of columns, but all CHAR, containing the column headers for each item type, with a one-to-many relationship to the item type in the main table. Each column name in the second table matches the corresponding column name in the main table. Each data item in the second table contains the 'friendly' name for the column in the main table, depending on the item type.

The SQL becomes somewhat more complex, but it is the most space-efficient method I have found to date, and there are (IMHO) considerable advantages in saving the entire inventory in a single table.

Using one table for the entire inventory makes sense only for the master records; details I'll stick into separate tables for different classes of equipment. It makes no sense for bolts to use the same table structure as transformers. Your technique sounds like it would be very hard to keep track of the items in the main table, should a change ever be required. It's interesting, though. Thanks! Big Grin | :-D

Will Rogers never met me.

Yes, I guess you have a wider range of complexity in your items. The last time I used this process was for a large spare parts distributor, and the reason I used this approach is that the stock descriptions could be of very variable lengths. If a new part category came into stock, and I needed some extra fields for it, I always had spare ones.

The grids on the project forms would hide any empty columns (all spaces). The project relied heavily on datasets, but this approach meant that regardless of the number of columns required for an item, the datasets never had to be rebuilt because of a schema change.

Roger Wright wrote:
But if 90% of the transformers in use have 40% of their data fields set to null,
will they still consume the same storage space as they would if all fields
contained data?

Don't know, but it'd be more logical to have the extra data in a separate table that you link to.

Transformer
Number
EntryDate
etc..

TransformerData
Number
Memo

Roger Wright wrote:
I really hate wasted space

Databases aren't optimized to use the minimum in space, but to deliver the data in a speedy way and guarantee integrity.

If you want to have a small-sized database, you'd only put the references in there and keep your memo's in a separate (compressed) file. That way the database won't include the memo's when reserving space. Databases don't reclaim the space when you delete a record; hence the suggestion to save your data outside the database.

Would also mean that you lose a part of the functionality.

Bastard Programmer from Hell Suspicious | :suss:

Good points. Thanks! Big Grin | :-D

Will Rogers never met me.

My pleasure Smile | :)

Roger Wright wrote:
rom a coding standpoint, a single master Transformer table is far easier to manage, and is the approach I'd prefer to follow. But if 90% of the transformers in use have 40% of their data fields set to null, will they still consume the same storage space as they would if all fields contained data?

Say you have 1000 columns for transformers and 900 of them are empty thus can be null.
Also say that a null takes one byte (could take less but one byte is possible.)
The other 100 columns take an average of 1000 bytes which is only 10 bytes per column.

So a record takes 1900 bytes and lets add 100 bytes for overhead (which is way high but makes the math easier.)

So a total of 2000 bytes per record.

TigerDirect (just an example) has an external 2 Terabyte drive for $120 and you need 3 (1 main, 1 failover, 1 backup). So $360

You will be able to store one billion transformers on that drive (one of them.)

Do you have one billion transformers?

And of course if the nulls take no space then you can store even more.
(Note that one design trick which I would suspect might not be relevant but could be is that you put null columns at the end of the table schema. Then it is more likely the database will drop them so no space.)

Roger Wright wrote:
I'll have to manage a dozen or more different tables,

Far as I am concerned you don't have a space problem. You do however have a management problem.

Now if you ONLY need to manage inventory then you should use a meta data design.

The idea in this case is simple. Following tables/columns are examples, variations, are driven by actual requirements. Following are table descriptions.
item: item_id, inventory_number, item_name
property_type: property_type_id, name, description, units
Property: property_id, item_id, property_type_id, the_value

Thus for a pad mounted 24kv transformer the data would look like the following
item: 1, 'tns001', 'big blue transformer'

property_type: 1, 'mount', 'transformer mount', null
property_type: 2, 'volts', 'volts', 'volt'

Property: 1, 1, 1, 'pad'
Property: 2, 1, 2, '24000'

There are many possible variations on the above.

That's really along the path I want to go. I use lookup tables to associate human readable data with numeric identifiers, then code the actual data using the identifiers to conserve space. I also plan to use a few boolean identifiers to control visual elements by turning on and off the displays for controls that are only used for some items. If something new is required, I only have to add a couple of records to the lookup tables.

Will Rogers never met me.

Roger Wright wrote:
visual elements

Text display from the database. Make sure those have their own column.

Hi all,

Can anybody help me in the following issue.

I have a course table contains coursename and type

I want to select course name and type and the display should like

Coursename(type)

th problem is my type values are integers..
For example 1 for Regular type ,2 for distance type
so if type is 1 and coursename is MCA
Output should be

MCA(Regular)

is it possiblle thru the sql statement

Thanks in advance,
Reena

I guess you have to have linked table for type of course,

Here is example how it should be

select 
case when [type]=1 then 'MCA' when [type]=2 then 'Another Title' end + ' (' +
coursename +')' as TypeAndCourse
from courses

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Blue_Boy wrote:
select
case when [type]=1 then 'MCA' when [type]=2 then 'Another Title' end + ' (' +
coursename +')' as TypeAndCourse
from courses

just little changes

SQL

SELECT [Coursename] + '(' + case when [type] = 1 THEN 'Regular' when [type]=2 then 'Another Title' end + ')' as TypeAndCourse
from courses

but i am agree with "Mycroft Holmes" suggestion of creating new table..

Where can we go to find God if we cannot see Him in our own hearts and in every living being
-Swami Vivekananda

Create a table of CourseTypes with a PK field and a description.
This is then an extensible solution if you need to add another type.
Create a view with an inner join (assumes the type is required in the course table and that a course can only have 1 type) to the type table and include the course type description field (I name the view vwCourse)

Or just do it the way the Blue one suggested!

Never underestimate the power of human stupidity
RAH

Try this

Declare @Course table ([Course Name] Varchar(100),[Course Type] Int)
Insert Into @Course
	Select 'MCA',1 Union All
	Select 'MCA',2

;With CTE AS(
	Select [Course Type] = 1,[Course Description] = 'Regular' Union All
	Select [Course Type] = 2,[Course Description] = 'Distance'
)

Select 
		Result = 
		c.[Course Name] 
		+ '('
		+ CTE.[Course Description]
		+ ')'
From @Course c
Join CTE ON c.[Course Type] = CTE.[Course Type]

If you are using Denali CTP 3, you can use the new Choose function

Select
	Result = 
			Concat(
					c.[Course Name] 
					,'('
					,CTE.[Course Description]
					,')'
				  )
From @Course c
Join CTE ON c.[Course Type] = CTE.[Course Type]

Output

Result
MCA(Regular)
MCA(Distance)

Niladri Biswas