Database

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

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

Roger Wright wrote:

Do they take up space, or are records compressed when fields are left empty

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?

Roger Wright wrote:

I really hate wasted space

Bastard Programmer from Hell

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?

Roger Wright wrote:

I'll have to manage a dozen or more different tables,

Roger Wright wrote:

visual elements

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

Blue_Boy wrote:

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

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

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]

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

Result
MCA(Regular)
MCA(Distance)