To measure the length of VARCHAR fields the function LEN(varcharfield) is useful.
To measure the length of TEXT fields the function is DATALENGTH(textfield). Len will not work for text field.
Example:
SELECT DATALENGTH(yourtextfield) AS TEXTFieldSize
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
84 Comments. Leave new
Really useful
Just what I wondered about.
Hi, It is really useful.
I have the database – in which i have to generate script for 10 tables only – as i dont have Enterprise Manager, i want to generate the script through Query Analyser onl.
ie the script should like the result of –Enterprise Manager—> Select the Table(s)–Rt Click–All Tasks — Generate SQL Script.
How can i get this?
Thanks in Advance.
Regards — Poov
Thnx!!
This was just what I needed
xx
Thank you! Before this I found a bunch of other SQL’s _languages_ that accept just LENGTH, but THIS is what I really needed.
Thank you!
This is what I was looking for.
Thanks
Nice …
but now how would i do that in Hibernate …
: )
Thanks for the info! Just what I was looking for…
Thanks :) You saved me lots of trouble…
I need some statement to check database field size in the code before inserting any value
@Antonio,
You can use this stord procedure
SP_HELP table_name
it will list out all the fields, their sizes, constraints, indexes….
It gives very good information.
Hope this helps.
Thanks,
Imran.
This is the most helpful thing EVER. Thank you!
thanks , very useful
Thanks! You are the SQL man!
Vey helpful dude!! Keep up the good work!
Jai Hind!
Thank you sir! Just what I was looking for!
Thanks. Short and usefull info.
Really useful…
i want to store more than 8000 chars in table field..
i hv used varchar(max) , text(max) ,but i m not able to get success in it..
everytime data gets truncated whn i insert it..
so can u tell me how to overcome from this problem..?
Note that there is no such datatype as text(max)
How did you insert data?
Can you post the code?
Are you use access database?
if yes then take data type memo.
if you use sqlserver 2005/08 there are no text data type
there you can use nvarchar(max)
I am having the same issue as Ramani. Any solution for this ?
I have a text field and what ever number of characters that I save on that field, it returns only 1001 characters. But when I checked with DATALENGTH() function it shows that it is having the same number of characters that I entered.
@Chaminda,
Open SQL Server Management Studio
Go to Tools – Options (Tools exists at the top left of SSMS)
Expand Query Results
Expand SQL Server
Click on Results to Grid
On right side, Set Value for :
Maximum Characters retrieved : Set a higher value.
Because of this, SQL Server is returning fewer characters. Change this value to a higher value and see the difference
~ IM.