Is there anything like a virtual Coulumn - pointing to another table

• fw 10048

  SSC-Addicted

  Points: 419

  More actions

  May 28, 2018 at 5:12 am

  #358553

  Hi,
  this is a simple example of  the current table what i have now, see image below: 
  Plus: I have a lots (many hundreds) of differerent querys making selects, updates and inserts
  My Problem is that the table is huge (many tens of GBytes)
  and Index modifications are a problem because of workload,

  

  My Idea was to remove  the Text Column to another table and getting the size of the News table below 1 GByte
  being able to make Index modifications again.
  I would then have 2 tables like this:

  

  What I would NOT like to do is lookup all occurances of the querys and having to modify them.
  Is the anything like a "virtual colum" that could be created in the "News" table pointing to the "NewsText" table? 
  I do not think that a view would be any help because that would meen I have to change the querys which I would like to avoid

  Any suggestions appreciated!

  Thanks, Greetings from Germany
  Frank

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  May 30, 2018 at 12:35 pm

  #1992371

  fw 10048 - Monday, May 28, 2018 5:12 AM

  Hi,
  this is a simple example of  the current table what i have now, see image below: 
  Plus: I have a lots (many hundreds) of differerent querys making selects, updates and inserts
  My Problem is that the table is huge (many tens of GBytes)
  and Index modifications are a problem because of workload,

  

  My Idea was to remove  the Text Column to another table and getting the size of the News table below 1 GByte
  being able to make Index modifications again.
  I would then have 2 tables like this:

  

  What I would NOT like to do is lookup all occurances of the querys and having to modify them.
  Is the anything like a "virtual colum" that could be created in the "News" table pointing to the "NewsText" table? 
  I do not think that a view would be any help because that would meen I have to change the querys which I would like to avoid

  Any suggestions appreciated!

  Thanks, Greetings from Germany
  Frank

  Unless a synonym can do this, you'd actually need to rename the original table, then create a view with the former name of the table as the name of the view.

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)

• ScottPletcher

  SSC Guru

  Points: 101180

  More actions

  May 30, 2018 at 2:05 pm

  #1992392

  You don't need to move it to another table to get it out of the main data area.  Just tell SQL to force all max column(s) out of row (this is an all-or-nothing option, you can't specify some max columns in, some out).

  
EXEC sys.sp_tableoption 'News', 'large value types out of row', 'ON'

  
  Existing data won't immediately be moved (luckily, or it'd be too much overhead to set on).  You'd need to update the max column(s) in existing rows (in batches, in clus key order, to reduce logging size) to physically force them out of row.  We'd have to test whether a "dummy" UPDATE, such as setting the column to itself, will force the data out of the main table and into overflow.

  All new rows, though, will have all max columns stored outside of the main table.

  Edit: If you're using different filegroups, it's possible the overflow data could go to a different filegroup than it is now.  If space is restricted in your environment, you might want to check on that first.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".