InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 12:39:38 GMT20http://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxNice question and nice reference in answer.. I lost patiencce on reading the entire explaination provided in BOL..But really a great stuff.Thu, 10 Jan 2013 06:15:19 GMTDineshbabuhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxthanks for your suggestion HugoWed, 10 Oct 2012 05:13:50 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]kapil190588 (10/10/2012)[/b][hr]hi Hugo,thanks a lot....I am new in area of DBA so can you plz suggest how can i improve my skills in sql area..any site or books?[/quote]The most important thing is to get practice. As much as you can get. If you haven't already, install an instance of SQL Server on your private laptop and/or home desktop. SQL Server Express edition is a free download that enables you to play around with most features; if you need acccess to the full feature set, you can buy Developer Edition for, I think, about 50 dollars per license. (I think you will need one license for each computer you install it on). Developer Edition gives you access to the full feature set of Enterprise Edition, but the license does not cover production use - development and testing only.It's also very important to know where to ask for help. Always try to find a solution first, using Books Online, trial and error, and Google/Bing - but don't hesitate to ask for help if needed. There are forums on this site, but there are also forums on many other sites. The most popular are probably the MSDN forums. Also, if you're on Twitter, you can get quick answers to short questions by using the #sqlhelp tag.For reading, I can recommend lots of sites. MSDN is mostly reference. This site has lots of articles, but unfortunately, the quality varies from extremely good to extremely bad, and everything in between. When you read an article here, always check if there was any debate on the forums (each article has a link to it's own forum discussion, just like the daily questions), and then still double check what you read. The daily question here is also nice, but beware that question authors often focus on edge cases, whereas in your actual work you will need the normal stuff most often.A source of very high quality information is the blogger site [url=http:sqlblog.com]sqlblog.com[/url]. (And I'm not saying that becuase I blog there, but because there are many great bloggers there - and note that there are also many great bloggers who blog elsewhere, so don't limit yourself to just that one site). However, due to the nature of blogs, it may be hard to find material of your own level.It's hard for me to recommend books, as I don't read that many books - and those I do read are not really targeted at the beginner level. One book I can recommend wholeheartedly is [url=http://www.amazon.com/Microsoft-Server-2008-Bible-Wiley/dp/0470257040]Paul Nielsens "SQL Server 2008 Bible"[/url]. (For full disclosure, I will say upfront that I was involved in the production of this book - I was the lead technical editor. I was payed a fixed amount for this, so I have no financial interest in driving its sales). I believe this book does achieve what it sets out to do - cover almost everything in SQL Server, starting at a level suitable for relative newcomers, but not stopping short of the more advanced stuff.Be warned that this is a pretty hefty volume. Don't plan to read it on a plane, you'll have to pay for excess weight. And check it out before buying, it may be more than you need at this time. I think there is also a "SQL Server 2012 Bible", but I have not seen any of its contents, so I can't comment on the quality. I know that Paul Nielsen, the original author of the "SQL Server ... Bible" series, was no longer involved in this edition.For more book reading suggestions, you might want to start a new discussion in an appropriate forum and see what other suggestions you get.Wed, 10 Oct 2012 05:04:17 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxhi Hugo,thanks a lot....I am new in area of DBA so can you plz suggest how can i improve my skills in sql area..any site or books?Wed, 10 Oct 2012 03:56:25 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]kapil190588 (10/10/2012)[/b][hr]can anyone tell me how to add a constraint to a already created view?I write as-alter view chk_salesasselect * from SalesDates,add chk_unique constraint unique (id>2)this is giving me error...[/quote]You cannot create any constraints on views,You can, however, create indexes on views. The first one you create has to be a clustered unique index; after that you can continue to create additional nonclustered indexes, that can be unique or nonunique, can include additional columns, and can be filtered. Using unique indexes on a view is a way to mimic the behaviour of constraints - but my recommendation is to do this only when required (which, since the addition of filtered indexes in SQL Server 2008, should be almost never); it's much better, both for performance and for future maintenance, to put constraints on the base tables.Wed, 10 Oct 2012 03:46:41 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]kapil190588 (10/10/2012)[/b][hr]sorry I pasted wrong query- this is the query below in which I am getting error-alter view chk_salesasselect * from SalesDates,add constraint chk_unique unique values (id>2)[/quote]You cannot create a constraint on the columns of a view.Constraints belong on the base tables.Views make use of predicates...Wed, 10 Oct 2012 01:58:49 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxsorry I pasted wrong query- this is the query below in which I am getting error-alter view chk_salesasselect * from SalesDates,add constraint chk_unique unique values (id>2)Wed, 10 Oct 2012 01:53:04 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxcan anyone tell me how to add a constraint to a already created view?I write as-alter view chk_salesasselect * from SalesDates,add chk_unique constraint unique (id>2)this is giving me error...Wed, 10 Oct 2012 01:49:25 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxGood straightforward question. Thanks for submitting.Mon, 02 Apr 2012 19:13:03 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxWe can't forget to count the amount of space these blog posts add to non-indexed views.My non-indexed view is that replacing "for the CREATE VIEW statement" with "to create views" would have made "correct" correct.In any case, Ron, thanks for the question and all your numerous excellent contributions to this web site.Chris[hr][i]All is not yet lost.[/i] [Nom DePlume]Thu, 22 Mar 2012 12:16:42 GMTchris.ctr.codyhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]marlon.seton (3/22/2012)[/b][hr]I'm probably exposing my ignorance here but with a view on just one table is there any difference between having an index on the view and having an index on the relevant columns in the table the view looks at?[/quote]An index on the view persists this view to the database.If it makes a difference regarding performance: it depends :-)Thu, 22 Mar 2012 07:40:15 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]marlon.seton (3/22/2012)[/b][hr]I'm probably exposing my ignorance here but with a view on just one table is there any difference between having an index on the view and having an index on the relevant columns in the table the view looks at?[/quote]Yes. An indexed view is materialised, in other words, stored separately from the base data. If the view is not indexed then SQL Server still has to execute the view's SELECT statement in order to get the data.JohnThu, 22 Mar 2012 07:39:59 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxI'm probably exposing my ignorance here but with a view on just one table is there any difference between having an index on the view and having an index on the relevant columns in the table the view looks at?Thu, 22 Mar 2012 07:30:00 GMTmarlon.setonhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxNice basic question and nice conversation...Wed, 21 Mar 2012 23:47:19 GMTHardy21http://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxIrony overload.Wed, 21 Mar 2012 03:20:24 GMTMichael Lysonshttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]L' Eomot Inversé (3/20/2012)[/b]But the question doesn't say "required to hold the create view statement ... It says "required for the the create view statement" [/quote]I've read this several times and am still struggling to see the difference :-)Wed, 21 Mar 2012 03:17:01 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]bitbucket-25253 (3/20/2012)[/b][hr]But alas and alack we do have those who love to criticize ....[/quote]I do not criticize questions just for the sake of criticizing them.Only when they are open to interpretation :-)[quote]except for the minimal space required for the CREATE VIEW statement[/quote]To me, this looks a lot like[quote]except for the minimal space required to store the CREATE VIEW statement[/quote]Tue, 20 Mar 2012 23:52:10 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]L' Eomot Inversé (3/20/2012)[/b][hr][quote][b]Gazareth (3/20/2012)[/b][hr][quote][b]sknox (3/20/2012)[/b][hr][quote][b]Koen Verbeeck (3/20/2012)[/b][hr]According to [url=http://msdn.microsoft.com/en-us/library/ms187956.aspx]CREATE VIEW (Transact-SQL)[/url]:[quote]When a view is created, [b]information about the view is stored[/b] in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.[/quote]Information that is stored in system tables also consumes space...[/quote]None of those system catalog [b]views[/b] are indexed. Which means they aren't materialized. Which means data in them doesn't take up additional space.[/quote]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.[/quote]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. [b] 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[/b] - 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 [i]hate[/i] your capital Ds for Does).[/quote]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 ....Tue, 20 Mar 2012 20:48:24 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxI 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!Tue, 20 Mar 2012 19:50:42 GMTtilew-948340http://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]Gazareth (3/20/2012)[/b][hr][quote][b]sknox (3/20/2012)[/b][hr][quote][b]Koen Verbeeck (3/20/2012)[/b][hr]According to [url=http://msdn.microsoft.com/en-us/library/ms187956.aspx]CREATE VIEW (Transact-SQL)[/url]:[quote]When a view is created, [b]information about the view is stored[/b] in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.[/quote]Information that is stored in system tables also consumes space...[/quote]None of those system catalog [b]views[/b] are indexed. Which means they aren't materialized. Which means data in them doesn't take up additional space.[/quote]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.[/quote]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 [i]hate[/i] your capital Ds for Does).Tue, 20 Mar 2012 16:53:39 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxGreat back-to-basics and straightforward question.Thank you.Tue, 20 Mar 2012 13:48:58 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxGreat question and thanks.Tue, 20 Mar 2012 13:22:45 GMTzymoshttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxThanks 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 ([url]http://msdn.microsoft.com/en-us/library/dd171921(v=sql.100).aspx[/url]):"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.Tue, 20 Mar 2012 12:10:18 GMTEL Jerryhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]mtassin (3/20/2012)[/b][hr][quote][b]Koen Verbeeck (3/20/2012)[/b][hr]According to [url=http://msdn.microsoft.com/en-us/library/ms187956.aspx]CREATE VIEW (Transact-SQL)[/url]:[quote]When a view is created, [b]information about the view is stored[/b] in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.[/quote]Information that is stored in system tables also consumes space...[/quote]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.[/quote]+1 I almost second guessed myself because technically everything created will take up some space.Tue, 20 Mar 2012 11:31:37 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]sknox (3/20/2012)[/b][hr][quote][b]Koen Verbeeck (3/20/2012)[/b][hr]According to [url=http://msdn.microsoft.com/en-us/library/ms187956.aspx]CREATE VIEW (Transact-SQL)[/url]:[quote]When a view is created, [b]information about the view is stored[/b] in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.[/quote]Information that is stored in system tables also consumes space...[/quote]None of those system catalog [b]views[/b] are indexed. Which means they aren't materialized. Which means data in them doesn't take up additional space.[/quote]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.Tue, 20 Mar 2012 10:54:08 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxtks for the questions - straight forward and to the point - cheersTue, 20 Mar 2012 08:49:52 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]Koen Verbeeck (3/20/2012)[/b][hr]According to [url=http://msdn.microsoft.com/en-us/library/ms187956.aspx]CREATE VIEW (Transact-SQL)[/url]:[quote]When a view is created, [b]information about the view is stored[/b] in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.[/quote]Information that is stored in system tables also consumes space...[/quote]None of those system catalog [b]views[/b] are indexed. Which means they aren't materialized. Which means data in them doesn't take up additional space.Tue, 20 Mar 2012 08:18:10 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxGreat question Bit Bucket. Concise and to the point with a clear answer. :cool:Tue, 20 Mar 2012 07:53:49 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxThanks Ron.Tue, 20 Mar 2012 07:45:44 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]dawryn (3/20/2012)[/b][hr][quote][b]Koen Verbeeck (3/20/2012)[/b][hr]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.[/quote]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 [url=http://msdn.microsoft.com/en-us/library/ms189082.aspx]query data from system catalogs[/url].[/quote]I also agree. Can you CREATE anything without the extra [u]minimal[/u] space consumed by the metadata.Tue, 20 Mar 2012 07:03:51 GMTDBA_Domhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]Koen Verbeeck (3/20/2012)[/b][hr]According to [url=http://msdn.microsoft.com/en-us/library/ms187956.aspx]CREATE VIEW (Transact-SQL)[/url]:[quote]When a view is created, [b]information about the view is stored[/b] in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.[/quote]Information that is stored in system tables also consumes space...[/quote]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.Tue, 20 Mar 2012 07:01:46 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxGreat easy question. I think I sat there with the answers already checked for a few minutes thinking to myself, "What's the catch?" and trying to figure out what it was, but there wasn't one. :-)Tue, 20 Mar 2012 06:52:28 GMTDana Medleyhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]Koen Verbeeck (3/20/2012)[/b][hr]It takes minimal space, but space is space. And it ain't only the CREATE VIEW statement...[/quote]I agree any metadata takes space, there is no question about it. Everything about database structure is stored in such form and it takes a lot less space than data and indexes.Tue, 20 Mar 2012 05:50:55 GMTdawrynhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]dawryn (3/20/2012)[/b][hr][quote][b]Koen Verbeeck (3/20/2012)[/b][hr]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.[/quote]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 [url=http://msdn.microsoft.com/en-us/library/ms189082.aspx]query data from system catalogs[/url].[/quote]It takes minimal space, but space is space. And it ain't only the CREATE VIEW statement...Tue, 20 Mar 2012 05:43:14 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]Koen Verbeeck (3/20/2012)[/b][hr]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.[/quote]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 [url=http://msdn.microsoft.com/en-us/library/ms189082.aspx]query data from system catalogs[/url].Tue, 20 Mar 2012 05:39:59 GMTdawrynhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxThanks for the nice easy question. Had to think about the objects held in the system views, but figured that wasn't what you were testing and where trying to cover with the "space used for the Create Statement" - since you can't create it without populating all the system views!Tue, 20 Mar 2012 05:00:39 GMTMichael Riemerhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]Koen Verbeeck (3/20/2012)[/b]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.[/quote]+1I chose the same "wrong" answer for the same reason. The question was explicit in excluding the CREATE, so it's a fair assumption that the other system tables are not excluded (or it should have said 'minimal space required for the system tables').Tue, 20 Mar 2012 03:31:44 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxGood back-to-basics question, thanks RonTue, 20 Mar 2012 03:24:02 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspxNice question, got it right without having to look anything up. I must be slowly learning something.Tue, 20 Mar 2012 03:17:01 GMTskankerhttp://www.sqlservercentral.com/Forums/Topic1269316-1222-1.aspx[quote][b]dawryn (3/20/2012)[/b][hr][quote][b]Koen Verbeeck (3/20/2012)[/b][hr]According to [url=http://msdn.microsoft.com/en-us/library/ms187956.aspx]CREATE VIEW (Transact-SQL)[/url]:[quote]When a view is created, [b]information about the view is stored[/b]in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.[/quote]Information that is stored in system tables also consumes space...[/quote]Of course it does. You explained :w00t:[quote]…[b]minimal space required[/b] for the CREATE VIEW statement…[/quote][/quote]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.Tue, 20 Mar 2012 02:54:43 GMTKoen Verbeeck