Analyzing Existing MS SQL Database for Data Types and Sizing

Question

Analyzing Existing MS SQL Database for Data Types and Sizing

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101207 More actions · Answer 1

For a first pass, you could try just ISDATE, IS NULL, ISNUMERIC and a few other key attributes:

CREATE TABLE dbo.column_profile ( ... );

INSERT INTO dbo.column_profile ( ... ) SELECT /* replace "column1" with the actual column name */ SUM(column1) AS column1_count, MAX(LEN(column1)) AS column1_MAXLEN, MIN(LEN(column1)) AS column1_MINLEN, SUM(CASE WHEN column1 IS NULL THEN 1 ELSE 0 END) AS column1_NULL_count, SUM(CASE WHEN ISDATE(column1) = 1 THEN 1 ELSE 0 END) AS column1_ISDATE_count, SUM(CASE WHEN ISNUMERIC(column1) = 1 THEN 1 ELSE 0 END) AS column1_ISNUMERIC_Count --,...column2...as above... --,...column3... FROM dbo.table_name

Those columns that were all NULL or ISDATE = 1 could be converted to a date.
Those columns that were all NULL or ISNUMERIC = 1 could be further analyzed -- TRY_CAST to int / bigint / decimal, for example -- or you just try converting them to int. Presumably most would work. The rest you could analyze further.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

andrew.jones 69458 Old Hand Points: 321 More actions · Answer 2

Thanks Scott. I will give that a try. I also have a query that for remaining text columns will find the longest record. That could help cut down on the size of the nvarchar(255) columns as well. Between the two I should be able to better optimize these tables.

Thanks again.

Andrew J

ScottPletcher SSC Guru Points: 101207 More actions · Answer 3

Back in the day, I would do a pattern search to determine eligible data types:

SUM(CASE WHEN column NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END) AS column_has_only_digits,
etc.

but I think the TRY_CAST presumably would be more efficient.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".