August 23, 2012 at 5:01 am
There's also the following:
Catalog views return information that is used by the SQL Server Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.
August 23, 2012 at 5:07 am
Horses for courses. I use INFORMATION_SCHEMA views because, in many situations (such as the one in this thread), they expose all the information I need in one view, without having to write fiddly joins between the catalog views. It is indeed true that there is a lot of information in the catalog views that isn't in the INFORMATION_SCHEMA views, and so I use catalog views when I need that.
John
August 23, 2012 at 5:11 am
Heres the explanation given by my technical lead to the client for using views for reporting purposes while justifying no performance impact to App users accessing the base tables:
"Yes, you are right that views are virtual and they don’t create their own copies of data. However, when a view is created, it creates an execution plan as per the records being fetched into it. When we fetch the data through the view next time, it would show the execution plan as per the query embedded into it. However, it does not hit the table and uses internal pointers to fetch the data. Next time the schema of the referenced tables is changed, it again creates a fresh execution plan. Hence, the table is not locked for external usage and there is rarely any impact. We are using the same mechanism in Enterprise Data Warehouse where the BO Reporting tools accesses the data through views of actual DW tables. "
I dont buy it but the client seems okay with it.
August 23, 2012 at 5:24 am
To the best of my knowledge this is totally incorrect.
Fetch the data from where? If there is no copy of the data, it must come from the table - so the explanation contradicts itself.
August 23, 2012 at 5:33 am
Ankur Bajaj (8/23/2012)
Heres the explanation given by my technical lead to the client for using views for reporting purposes while justifying no performance impact to App users accessing the base tables:"Yes, you are right that views are virtual and they don’t create their own copies of data. However, when a view is created, it creates an execution plan as per the records being fetched into it. When we fetch the data through the view next time, it would show the execution plan as per the query embedded into it. However, it does not hit the table and uses internal pointers to fetch the data. Next time the schema of the referenced tables is changed, it again creates a fresh execution plan. Hence, the table is not locked for external usage and there is rarely any impact. We are using the same mechanism in Enterprise Data Warehouse where the BO Reporting tools accesses the data through views of actual DW tables. "
I dont buy it but the client seems okay with it.
Your technical lead might fare better as a fiction writer - almost all of this statement is incorrect. In Europe & the USA this could lead to your client taking legal action against your company, and you.
Your client seems ok for now. You could end up in very hot water if this statement is seen in the future by someone qualified to evaluate it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 23, 2012 at 8:48 am
ChrisM@Work (8/23/2012)
Ankur Bajaj (8/23/2012)
People having a good laugh at my cost! +1 to that....
Nobody is laughing at you. Some of the folks who have responded on this thread may at some point in their long careers have been persuaded to action a similar illogical directive, and remember the consequences. It's not funny, it's tragic.
+1
I followed a directive from a "lead" early in my career very similar to this one. It ended up costing both of us our jobs. The lead for demanding such a ridiculous methodology and me for not taking my concerns up the flag pole.
I offered my advice in the hopes that maybe it would save you the problems I had. It is obvious you are not crazy about this approach and for good reason. I will refrain from posting anymore about the topic. I voiced my concerns, demonstrated the huge holes in the logic and explained a lot of reason why this approach is a terrible direction.
The decision is yours, either run your concerns to your boss or plug your nose as you write smelly code. We all have times in our career where we have to plug our nose and surge forward. There are also times we take a stand. I can't tell you what is right, that decision is yours.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 24, 2012 at 2:47 am
ChrisM@Work (8/23/2012)
Your technical lead might fare better as a fiction writer - almost all of this statement is incorrect.
Correct Chris. That is quite scary.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 24, 2012 at 12:57 pm
Here's a slightly better way to go than that horrific Cursor thing:
First create a scalar UDF like this:
/*
Returns a formatted string of all of the column names for the specified
Table or View, in order (probably).
*/
CREATE FUNCTION [dbo].[ColumnString]
(
@TableName As SYSNAME,
@Before As NVarchar(MAX) = N'',
@Between As NVarchar(MAX) = N', ',
@After As NVarchar(MAX) = N''
)
RETURNS NVarchar(MAX) AS
BEGIN
DECLARE @STR As NVarchar(MAX);
SELECT TOP 999
@STR = COALESCE(@str + @Between + @Before + COLUMN_NAME, @Before + COLUMN_NAME) + @After
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= COALESCE(PARSENAME(@TableName, 2), N'dbo')
And TABLE_NAME = PARSENAME(@TableName, 1)
ORDER BY ORDINAL_POSITION
RETURN @STR;
END
Then create a stored procedure like this:
CREATE PROC dbo.WrapAllTables AS
DECLARE @sql As NVarchar(MAX);
SET @sql = N'';
SELECT @sql = @sql + '
IF EXISTS (SELECT * FROM sys.views
WHERE object_id = OBJECT_ID('''+TABLE_SCHEMA+'.vw_'+TABLE_NAME+'''))
DROP VIEW ['+TABLE_SCHEMA+']vw_.['+TABLE_NAME+'];
EXEC(''
CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' AS
SELECT '
+ dbo.ColumnString(TABLE_SCHEMA+'.'+TABLE_NAME, '
[', ',', ']')
+'
FROM '+ TABLE_SCHEMA +'.'+ TABLE_NAME + '
WITH SCHEMABINDING
'')
'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
print 'EXECuting:'
print @sql
EXEC (@sql);
Execute this stored procedure to create all of your table wrapper views. If any of the table definitions change, then just re-execute this stored procedure. (I just run a SQL Agent Job that automatically re-executes these once a night).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 24, 2012 at 1:43 pm
Out of interest, what do you use the table-wrapper views for?
August 24, 2012 at 2:15 pm
Ankur Bajaj (8/23/2012)
Heres the explanation given by my technical lead to the client for using views for reporting purposes while justifying no performance impact to App users accessing the base tables:"Yes, you are right that views are virtual and they don’t create their own copies of data. However, when a view is created, it creates an execution plan as per the records being fetched into it. When we fetch the data through the view next time, it would show the execution plan as per the query embedded into it. However, it does not hit the table and uses internal pointers to fetch the data. Next time the schema of the referenced tables is changed, it again creates a fresh execution plan. Hence, the table is not locked for external usage and there is rarely any impact. We are using the same mechanism in Enterprise Data Warehouse where the BO Reporting tools accesses the data through views of actual DW tables. "
I dont buy it but the client seems okay with it.
“Yes, you are right that views are virtual and they don’t create their own copies of data.”
Ok, that’s one true statement. Unless it is a materialized view, which functions a lot like a table, but not what you are talking about.
“However, when a view is created, it creates an execution plan as per the records being fetched into it.”
Views don’t have execution plans, queries have execution plans.
“When we fetch the data through the view next time, it would show the execution plan as per the query embedded into it.”
I have no idea what that means or was supposed to mean.
“However, it does not hit the table and uses internal pointers to fetch the data.”
That’s nonsense.
“Next time the schema of the referenced tables is changed, it again creates a fresh execution plan.”
Again, views don’t have execution plans, queries have execution plans.
“Hence, the table is not locked for external usage and there is rarely any impact.”
That’s more nonsense. Views reference the underlying table, so locking works the same as a direct reference to the table.
August 24, 2012 at 2:36 pm
Easily provable to be false. Run a large select from a view and examine the locks through SSMS or sp_lock while it's running. You'll see locks on the underlying objects. Your lead is leading you over a cliff...
August 24, 2012 at 5:09 pm
laurie-789651 (8/24/2012)
Out of interest, what do you use the table-wrapper views for?
I assume that you meant me?
Well, I use them for three things:
1. Security Layer. Part of a larger security model for new databases/development that I usually implement for customers that allows them to have absolute control over who can access what, in a way that makes sense to both the developers and the DBAs.
2. Redirection. So that tables/views in another database appear as if they are in this database. This is by far my most common use for them.
3. Abstraction Layer. So that later on, I can change the physical design, without necessarily having to alter the logical presentation as well.
All three of these do require some way of adding and preserving customizations to the wrappers, which is more complicated (and usually customer-specific) and is not included in my example.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2012 at 2:40 pm
RBarryYoung (8/24/2012)
laurie-789651 (8/24/2012)
Out of interest, what do you use the table-wrapper views for?I assume that you meant me?
Yes - should have quoted!
Thanks for the gen. I haven't come across anything like that myself yet...
August 25, 2012 at 6:01 pm
Ankur Bajaj (8/23/2012)
Heres the explanation given by my technical lead to the client for using views for reporting purposes while justifying no performance impact to App users accessing the base tables:"Yes, you are right that views are virtual and they don’t create their own copies of data. However, when a view is created, it creates an execution plan as per the records being fetched into it. When we fetch the data through the view next time, it would show the execution plan as per the query embedded into it. However, it does not hit the table and uses internal pointers to fetch the data. Next time the schema of the referenced tables is changed, it again creates a fresh execution plan. Hence, the table is not locked for external usage and there is rarely any impact. We are using the same mechanism in Enterprise Data Warehouse where the BO Reporting tools accesses the data through views of actual DW tables. "
I dont buy it but the client seems okay with it.
I'll join the others in saying that's mostly wrong.
I get the fact that some leads will be wrong and there's no detering them from going over the cliff. Just make sure you write down this whole incident so you can laugh at the folly of it all when things finally go BOOM! and the time wasted when you try to add a column to a table and also need to "recompile" the view.
--Jeff Moden
April 3, 2013 at 7:23 am
RBarryYoung (8/24/2012)
Here's a slightly better way to go than that horrific Cursor thing:First create a scalar UDF like this:
/*
Returns a formatted string of all of the column names for the specified
Table or View, in order (probably).
*/
CREATE FUNCTION [dbo].[ColumnString]
(
@TableName As SYSNAME,
@Before As NVarchar(MAX) = N'',
@Between As NVarchar(MAX) = N', ',
@After As NVarchar(MAX) = N''
)
RETURNS NVarchar(MAX) AS
BEGIN
DECLARE @STR As NVarchar(MAX);
SELECT TOP 999
@STR = COALESCE(@str + @Between + @Before + COLUMN_NAME, @Before + COLUMN_NAME) + @After
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA= COALESCE(PARSENAME(@TableName, 2), N'dbo')
And TABLE_NAME = PARSENAME(@TableName, 1)
ORDER BY ORDINAL_POSITION
RETURN @STR;
END
Then create a stored procedure like this:
CREATE PROC dbo.WrapAllTables AS
DECLARE @sql As NVarchar(MAX);
SET @sql = N'';
SELECT @sql = @sql + '
IF EXISTS (SELECT * FROM sys.views
WHERE object_id = OBJECT_ID('''+TABLE_SCHEMA+'.vw_'+TABLE_NAME+'''))
DROP VIEW ['+TABLE_SCHEMA+']vw_.['+TABLE_NAME+'];
EXEC(''
CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' AS
SELECT '
+ dbo.ColumnString(TABLE_SCHEMA+'.'+TABLE_NAME, '
[', ',', ']')
+'
FROM '+ TABLE_SCHEMA +'.'+ TABLE_NAME + '
WITH SCHEMABINDING
'')
'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
print 'EXECuting:'
print @sql
EXEC (@sql);
Execute this stored procedure to create all of your table wrapper views. If any of the table definitions change, then just re-execute this stored procedure. (I just run a SQL Agent Job that automatically re-executes these once a night).
Thanks for the code.
I found that making some adjustments would work in SQL 2012.
ALTER PROC [dbo].[WrapAllTables] AS
DECLARE @sql As NVarchar(MAX);
SET @sql = N'';
SELECT @sql = @sql + '
IF EXISTS (SELECT * FROM sys.views
WHERE object_id = OBJECT_ID('''+TABLE_SCHEMA+'.vw_'+TABLE_NAME+'''))
DROP VIEW ['+TABLE_SCHEMA+'].[vw_'+TABLE_NAME+'];
EXEC(''
CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + '
WITH SCHEMABINDING AS
SELECT '
+ dbo.ColumnString(TABLE_SCHEMA+'.'+TABLE_NAME, '
[', ',', ']')
+'
FROM '+ TABLE_SCHEMA +'.'+ TABLE_NAME + '
;
'')
'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--print 'EXECuting:'
--PRINT LEN(@sql)
--PRINT LEFT(@sql,3700)
--SELECT @sql
EXEC (@sql);
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply