VIEWS 3

Question

VIEWS 3

Viewing 15 posts - 16 through 30 (of 45 total)

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

mtassin SSC-Insane Points: 23099 More actions · Answer 1

Koen Verbeeck (3/20/2012)
According to CREATE VIEW (Transact-SQL):
When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.
Information that is stored in system tables also consumes space...

Yah... I worried when I selected the "correct" answer that this would be used against me.

I was glad that it wasn't but I suspected somebody would say something about it.

some days when I answer these I'm convinced that no matter which answer I pick it will be wrong because it could be argued either way.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

DBA_Dom SSCrazy Points: 2902 More actions · Answer 2

dawryn (3/20/2012)
Koen Verbeeck (3/20/2012)
That is the space for the CREATE VIEW statement. That is stored in the sys.sql_modules catalog view. What I'm going after is that extra information is also stored in other catalog views. Not just the CREATE statement.
All mentioned is stored in system catalogs to make views operational and takes minimal space required after create view statement is executed. Catalog views are one form of means to query data from system catalogs.

I also agree. Can you CREATE anything without the extra minimal space consumed by the metadata.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks Ron.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SanDroid SSChampion Points: 10068 More actions · Answer 4

Great question Bit Bucket. Concise and to the point with a clear answer. 😎

sknox SSChampion Points: 12388 More actions · Answer 5

Koen Verbeeck (3/20/2012)
According to CREATE VIEW (Transact-SQL):
When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.
Information that is stored in system tables also consumes space...

None of those system catalog views are indexed. Which means they aren't materialized. Which means data in them doesn't take up additional space.

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 6

tks for the questions - straight forward and to the point - cheers

Gazareth One Orange Chip Points: 27737 More actions · Answer 7

sknox (3/20/2012)
Koen Verbeeck (3/20/2012)
According to CREATE VIEW (Transact-SQL):
When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.
Information that is stored in system tables also consumes space...
None of those system catalog views are indexed. Which means they aren't materialized. Which means data in them doesn't take up additional space.

Agreed. But I think the point is that more space is taken up somewhere, rather than just a copy of the create view statement, which the question implies is the only information about the view that's stored.

Ken SSCoach Points: 17191 More actions · Answer 8

mtassin (3/20/2012)
Koen Verbeeck (3/20/2012)
According to CREATE VIEW (Transact-SQL):
When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.
Information that is stored in system tables also consumes space...
Yah... I worried when I selected the "correct" answer that this would be used against me.
I was glad that it wasn't but I suspected somebody would say something about it.
some days when I answer these I'm convinced that no matter which answer I pick it will be wrong because it could be argued either way.

+1 I almost second guessed myself because technically everything created will take up some space.

EL Jerry SSCertifiable Points: 7061 More actions · Answer 9

Thanks for the question, Ron.

As far as I understand every T-SQL statement takes up database space, even if minimal. I suppose Ron meant by "minimal space" the space it takes to store the view definition.

From the reference article (http://msdn.microsoft.com/en-us/library/dd171921(v=sql.100).aspx):

"In the case of a nonindexed view, the portions of the view necessary to solve the query are materialized at run time. Any computations such as joins or aggregations are done during query execution for each query referencing the view [Note1] . After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

[Note1] The view does not always need to be fully materialized. The query can contain additional predicates, joins, or aggregations that can be applied to the tables and views referenced in the view, which eliminates the need for full materialization."

This is where I got my conclussion and selected the right answers.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

zymos SSCommitted Points: 1960 More actions · Answer 10

zymos

SSCommitted

Points: 1960

March 20, 2012 at 1:22 pm

#1461856

Great question and thanks.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 11

Great back-to-basics and straightforward question.

Thank you.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Gazareth (3/20/2012)
sknox (3/20/2012)
Koen Verbeeck (3/20/2012)
According to CREATE VIEW (Transact-SQL):
When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.
Information that is stored in system tables also consumes space...
None of those system catalog views are indexed. Which means they aren't materialized. Which means data in them doesn't take up additional space.
Agreed. But I think the point is that more space is taken up somewhere, rather than just a copy of the create view statement, which the question implies is the only information about the view that's stored.

But the question doesn't say "required to hold the create view statement", so it doesn't seem to imply that a copy of the create statement is the only data stored. It says "required for the the create view statement" which to me seems to mean required to hold all the metadata created by executing the create view statement - if the answer had claimed it meant anything else I would have regarded it as a (somewhat feeble and implausible) trick question.

So it seems to me to be a good cear question with a good clear answer. Thanks for that, Ron (but I hate your capital Ds for Does).

Tom

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 13

I knew about non indexed view, but I would have bet fifty box that an indexed view would not take space...

Good thing you don't take bets :laugh:

Thanks for the question, I learned again!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 14

L' Eomot Inversé (3/20/2012)
Gazareth (3/20/2012)
sknox (3/20/2012)
Koen Verbeeck (3/20/2012)
According to CREATE VIEW (Transact-SQL):
When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.
Information that is stored in system tables also consumes space...
None of those system catalog views are indexed. Which means they aren't materialized. Which means data in them doesn't take up additional space.
Agreed. But I think the point is that more space is taken up somewhere, rather than just a copy of the create view statement, which the question implies is the only information about the view that's stored.
But the question doesn't say "required to hold the create view statement", so it doesn't seem to imply that a copy of the create statement is the only data stored. It says "required for the the create view statement" which to me seems to mean required to hold all the metadata created by executing the create view statement - if the answer had claimed it meant anything else I would have regarded it as a (somewhat feeble and implausible) trick question.
So it seems to me to be a good cear question with a good clear answer. Thanks for that, Ron (but I hate your capital Ds for Does).

Making the key statement bold .. done by this poster. Thanks, you have stated the facts as I meant them to be. But alas and alack we do have those who love to criticize ....

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 15

bitbucket-25253 (3/20/2012)
But alas and alack we do have those who love to criticize ....

I do not criticize questions just for the sake of criticizing them.

Only when they are open to interpretation 🙂

except for the minimal space required for the CREATE VIEW statement

To me, this looks a lot like

except for the minimal space required to store the CREATE VIEW statement

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP