December 20, 2007 at 9:44 am
I have developers quickly building databases to load client data. Some have built entire databases using nvarchar everywhere. This of course is a performance nightmare and bad practice. I am working with the lead developer on fixing the pre-existing tables and databases and came up with a script to find max number of data items in each fields. The results on the full script are buggy and need some help from you wonderful people.
if I run this script
use DBA_DB
go
select max(len(ACCTNUM)) as ACCTNUMMaxlength
from genericleadsdemo
GO
select max(len('Margin 1 High for ALAP')) as Margin1Maxlength
from genericleadsdemo
GO
select max(len('AGENCYCD')) as AGENCYCDMaxlength
from genericleadsdemo
I get values
13
22
8
respectively
When I run this script
use DBA_DB
go
-- to find table column datatypes and max length of data
SELECT a.name, b.name AS ColumnName,
c.name AS Datatype, b.length as ColLength,
max(len(b.name)) as Maxlength
FROM sysobjects a, syscolumns b, systypes c--, sysindexes
WHERE a.id = b.id AND
b.xtype = c.xtype AND
-- to run for all tables in db run next line
a.xtype = 'U'--and c.name <> 'sysname'
-- comment out below line to run for all tables or modify to different table
and a.name = 'GenericLeadsDemo'
group by a.name, b.name,c.name, b.length
order by a.name, b.name
I get
name ColumnName Datatype ColLength Maxlength
GenericLeadsDemo ACCTNUM nvarchar 100 7
GenericLeadsDemo ACCTNUM sysname 100 7
GenericLeadsDemo AGENCYCD nvarchar 100 8
GenericLeadsDemo AGENCYCD sysname 100 8
GenericLeadsDemo Margin 1 High for ALAP varchar 50 22
December 20, 2007 at 9:50 am
Please don't multi-post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2007 at 10:04 am
that was laptop thinking with out me! I do apologize and tryed to delete the extra post and rebooted machine. 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply