Test-VarcharLength

[pollusb]

I created a function to calculate the max length of all text fields in a table to evaluate the opportunity to reduce the datatype max to a lower number. This would reduce the excessive memory grant in query plans. This function accept a Sample Rate or Sample Top for testing purposes. Because of course, you want to know the real number before reducing fields size.

Should I take time to send a PR ?

[potatoqualitee]

Sup @pollusb! I have needed similar things in the past, especially with CSV files and text. This command would be a great addition, but would likely be Measure-DbaDbTableVarcharMax. @wsmelton what are your thoughts on the name?

[wsmelton]

Is this using T-SQL method for measuring string character length or PowerShell?

I'd prefer we cover text value columns instead of a specific data type.
If using T-SQL which functions are involved? Be cool to have Measure-DbaDbTextColumn that can use datalength and then return lengths for varchar, nvarchar or even binary types as well.

https://docs.microsoft.com/en-us/sql/t-sql/functions/datalength-transact-sql?view=sql-server-ver15

[pollusb]

Thanks for the feedback.

The function is running a TSQL MAX on each text column in one pass using a TOP. But I am thinking about using also AVG to calculate the real opportunity ratio for improvment. And since I would be there, I could also throw in MIN and STDVAR using a -Detailed switch for those who might need that.

OK, I like the datalenght idea. It would also help with VARCHAR(MAX)

And if I get to choose the name, it would be Measure-DbaDbTextColumn. Sorry @potatoqualitee

[potatoqualitee]

lol that works