Database Column Tuning
-
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 ColumnNameDatatypeColLengthMaxlength
GenericLeadsDemoACCTNUMnvarchar1007
GenericLeadsDemoACCTNUMsysname1007
GenericLeadsDemoAGENCYCDnvarchar1008
GenericLeadsDemoAGENCYCDsysname1008
GenericLeadsDemoMargin 1 High for ALAPvarchar5022
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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