Category Archives: SQL 2000

SQL Server 2000 Tips

SQL 2000

Delmited string to table

CREATE FUNCTION usf_SplitString (@delimitedString nvarchar(max), @mDelimiter char(1) = ‘y’)

Returns @tblStringToTable Table (Colvalue nvarchar(50))AS

BEGIN

Declare @colvalue nvarchar(50)
Declare @startpos int, @length int
while LEN(@delimitedString) > 0

BEGIN
SET @StartPos = CHARINDEX(@mDelimiter, @DelimitedString)
IF @StartPos < 0 set @StartPos = 0
SET @Length = LEN(@DelimitedString) – @StartPos – 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @colvalue = SUBSTRING(@delimitedString,1,@StartPos – 1)
Set @delimitedString = SUBSTRING(@delimitedString. @StartPos+ 1, LEN(@delimitedString ) – @StartPos)
END

Else
BEGIN

Set @colvalue = @delimitedString;
Set @delimitedString = ”

END
IF @Colvalue != ”
IF not exists(Select 1 from @delimitedString where Colvalue = @Colvalue)
INSERT @tblStringToTable (Colvalue)  VALUES (@Colvalue)
END
RETURN

END

GO

See Below for example usage – don’t forget you must specify database owner

select * from dbo.usf_SplitString (‘1,2,3,4′,’,’)

Returns a table

Colvalue 1 2 3 4 ‘

SQL 2000

Getting space used from tables – in one command

2000 only getting space used from tables

This isn’t a new tip – you may have seen many references to using sp_MSForEachTable and it’s partner sp_MSForEachDB – just put it into context with getting the data back out in a clean format by using insert .. exec


/* create a table to hold the results of the exec command */
create table #SpaceUsed (name nvarchar(129), rows char(11), reserved varchar(18),data varchar(18), index_Size varchar(18), unused varchar(18))
insert #SpaceUsed exec sp_msforeachTable 'sp_spaceused "?"'
--Run the script - then
select * from #SpaceUsed order by cast(rows as int) desc
--gives you the number of rows
select * from #SpaceUsed order by cast(left(reserved,len(reserved) - 3)as int) desc

–Gives you the data based on space used (including indexes)

Note that in SQL 2008 and subsequent versions – you can simply use the standard reports in the database e.g. right click on the database -> Reports ->Standard Reports->Disk Usage by Tables which can then be exported e.g. in excel by right clicking on the report -> Export -> Excel

I haven’t added that in as a SQL 2008 tip since most people already know about the custom reports – however, worth bearing in mind that compatability must be set to at least 90 for the custom reports to work!

SQL 2000

Using BCP Format Files

Format files give a lot of the flexibility of DTS Transformations – you can skip columns, reorder the columns and do most of what you want.

They can also be used in conjunction with Bulk Insert to get the speed efficiency from Bulk Insert.

But if possible – order the table and the source data in the same way and use a sensible delimiter which isn’t in the data – if the data is from a PC based system then try to use tab delimiters – if it’s sourced from a mainframe look at ‘%’ ‘#’ or ‘$’ since they tend not to be extensively used in mainframes.

If the source file is generated by COBOL then it’s likely to be fixed length – so use that instead to create your format files – saves you 1 byte per column – if you’ve got a lot of fields e.g. y/n indicators then you can halve the data by using fixed format. FTP’s will be faster

SQL 2000

COBOL Generated Files

Yes they still do exist in the new technological era – they contain a lot of the information that client server systems want! Whilst most versions of COBOL support the STRING verb, it’s rarely used in data migration (in my humble opinion) and most systems follow the sensible approach of using fixed field some with delimiters But we always have the following problems, what delimiter to use – well a radical answer – don’t use a delimiter keep it fixed format and map it using the cobol field definitions.

Saves all those unwelcome bytes of data between fields and the problems when someone sets up a name as c!o xyz corp #01234 456789 You’ve picked ! as the delimiter and suddenly you have an extra column in your data – same if you use # which will “never appear in the data”