Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!us.feeder.erje.net!newsfeed.fsmpi.rwth-aachen.de!newsfeed.straub-nv.de!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Best Practices for Wide Columns in Tables Date: Thu, 30 Apr 2015 12:29:38 +0000 (UTC) Organization: Erland Sommarskog Lines: 32 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Injection-Date: Thu, 30 Apr 2015 12:29:38 +0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="5d404a5d864426cbf50ddbcd9433a6e4"; logging-data="399"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19m3y6A8RwMc1HJsGb+7FVh" User-Agent: Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32) Cancel-Lock: sha1:7ATSaO/GOOPJFCy7abR11Upf2PY= Xref: csiph.com comp.databases.ms-sqlserver:1913 Michael Cole (noone@invalid.com) writes: > This is one where there seems to be a lot of conflicting advice out > there in google-land. > > If we have tables with one or more wide columns - VarChar with size of > over 8K in total - should these columns be moved to their own > wide-column table with the initial table simply linking to them? Is > there much of an advantage/disadvantage to this, or does it actually > depend on how much is stored in them, not the empty column widths? Yes, that's the answer: it depends. For instance, If I add an xml column to store some freer-form data, I may not want to store it out of table just because it could fit more than 8K of data. And if I have table where the main meat is the BLOB data, it is not likely that I would like to store it in a separate table. But it the blob is not often accessed and the table have lots of columns and I expect scans to be common place, storing it in a separte table makes sense. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx