September 30, 2014 at 6:58 am
arrjay (9/30/2014)
V. strange. I have tried changing the field lengths from 320 to MAX (is this what you mean by changing the table options?), this fixed the problem but I'm still unsure why the original would not move onto a separate page. Thanks.
I meant your suggestion from before - EXEC sp_tableoption 'demo.OneColVarcharMaxOut', 'large value types out of row', 1;
Maybe my arithmetic was out, if changing the 320's to MAX has fixed it then I think you were over 8060 bytes & the change has moved them out of row.
September 30, 2014 at 9:26 am
FWIW, my code for computing max possible row length returns a length of 11870 for that table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 30, 2014 at 9:54 am
Hi - Thanks for your input.
I have found the reason why this keeps happening - My query drops the column before populating it. I have had to repeat this dropping and re-creating of the column multiple times. What I need to do is reclaim the space from the data page by running DBCC CLEANTABLE.
Thanks for everyone's help on this. Maybe if I had detailed the dropping/recreating the field we would have got there quicker. My bad.
Thanks.
--------------------------------------------
Laughing in the face of contention...
September 30, 2014 at 10:13 am
ScottPletcher (9/30/2014)
FWIW, my code for computing max possible row length returns a length of 11870 for that table.
Cheers Scott, I did it by eye & calculator so I'd trust your figure more!
October 2, 2014 at 2:35 am
Sean Lange (9/29/2014)
Koen Verbeeck (9/29/2014)
Sean Lange (9/29/2014)
I know that this is not neccesarily your design but starting all your table names with tbl_ and storing the datatype used in the column name is a seriously poor design choice. What happens when the datatype needs to change? Either your column name is no longer correct or you have to update all the code that references it. YUCK!!!+1 This will only bite you in the end
hehe "bite you in the end"...I see what you did there double entrende.
I swear I'm not intentionally that funny 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 2, 2014 at 9:57 am
Koen Verbeeck (10/2/2014)
Sean Lange (9/29/2014)
Koen Verbeeck (9/29/2014)
Sean Lange (9/29/2014)
I know that this is not neccesarily your design but starting all your table names with tbl_ and storing the datatype used in the column name is a seriously poor design choice. What happens when the datatype needs to change? Either your column name is no longer correct or you have to update all the code that references it. YUCK!!!+1 This will only bite you in the end
hehe "bite you in the end"...I see what you did there double entrende.
I swear I'm not intentionally that funny 🙂
Some people are just lucky. They can be that funny without any effort. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply