Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miekeandstephen
Starting Member
3 Posts |
 Posted - 2004-07-19 : 19:35:50
|
| I'm looking for an efficient way in Transact SQL to determine number of bytes a single row in a table uses. The row could contain variable length data types such as varchar, text and image.For each user that signs into the system I have developed I want to keep a track of how much space they are using. So after they add a new row, update a row or delete a row I want to update a count of how many bytes they currently have stored in the system.Any help would be greatly appreciated.Cheers. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-19 : 22:53:07
|
| Have a calculated column that does a LEN of each of the columns. Then have a table with the total for each user. I would probably just sum it every hour or so.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-07-20 : 00:49:59
|
| Wouldn't a DATALENGTH() be more appropriateDamian |
|
|
|
miekeandstephen
Starting Member
3 Posts |
Posted - 2004-07-20 : 02:26:13
|
| Yep. I ended up using the DATALENGTH function as it worked for all datatypes. I just sum the DATALENGTH result for each column. Now I can keep track of exactly how many bytes a user is storing in my DB. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-20 : 08:29:01
|
| I'm curious. Why do you want to do this exactly?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
miekeandstephen
Starting Member
3 Posts |
Posted - 2004-07-20 : 17:18:02
|
| I am creating a website where user can store different types of information. Each user has a storage limit. So I wanted to keep a running total of how much space they are currently using and check that they do not exceed their limit.I ended up creating a stored proc for each table that calculates the number of bytes a row uses by adding up the DATALENGTH result of each column in the row. I then use this stored proc in the insert, update and delete stored procs of each table to update the total bytes used by the user.It worked a treat. Now I display a lovely percentage bar to indicate how much of their storage space a user has used. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-20 : 17:52:18
|
| Kewl. You might want to run an "integrity check" just to double-check your work and not needlessly alienate some users. Always have checks in place. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
How can I determine the size of a row?