Post reply

Query Builder - Creating Giant View

  • March 26, 2009 at 9:49 am

    #207735

    Hi,

    I have a task of building a cutomized query builder in .NET. One solution I was proposed is to create one giant view containing around 170 columnsand thousands of rows to start with and they can potentially grow in future.

    First thing I wanna ask is that is it even possible to create view with these number of columns considering if exist any any limits on the row size of the view since is a form of table which can handle only 800 bytes of data.

    Secondly, would it be a good design from database point of view since we might only select 5-6 columns and it will have to go through this giant non-indexed view.

    Any suggestions or thoughts would be greatly appreciated.

  • March 27, 2009 at 12:01 am

    #967212

    Mastermind (3/26/2009)

    Hi,

    I have a task of building a cutomized query builder in .NET. One solution I was proposed is to create one giant view containing around 170 columnsand thousands of rows to start with and they can potentially grow in future.

    First thing I wanna ask is that is it even possible to create view with these number of columns considering if exist any any limits on the row size of the view since is a form of table which can handle only 800 bytes of data.

    Secondly, would it be a good design from database point of view since we might only select 5-6 columns and it will have to go through this giant non-indexed view.

    Any suggestions or thoughts would be greatly appreciated.

    Max numbers of columns in a select statement is 4096 and max number of tables per select is 256. Okay SQL allows alot .... there is no row byte size limit for views because views don't store data they get it on the fly.

    When you run a view it substitutes the SQL in view def with your select then executes it. So it is a really bad idea to create a view that is catch all; because you are going to have terrible performance on it selecting from multiple tables.

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply