Firstly, Hello all!
I’ve decided that after years of learning and “borrowing” ideas, info and techniques from other peoples blogs, it might be about time
I put one up and share some of the interesting, useful or just downright odd things I have come across in my years working in IT and particularly with SQL Server.
Hopefully you will find some of these blogs useful, interesting, or at the very least give you something to argue with me over!
Back Story – I work as a SQL Consultant for Prodata, who are the top SQL Server & BI consultants in Ireland (shameless plug!) .
My main area of expertise is as a production DBA, but I also dip into BI, SSIS, Office (well, excel) a little virtualization and general IT work.
Today’s blog is concerned with SQL Server Views, or more the issues that are encountered when using views, and how to sidestep them.
For this example I am using a subset of the DimCustomers table in AdventureWorksDW2012, available on codeplex. It’s a contrived example but gets the message across.
On running a select * from our Customers table, the results look like this:
Imagine we want to expose this table to certain users/groups via a view. This is something we do pretty regularly when exposing Data Warehouse tables via views to help minimize effect of base table changes on reports:
Create View CustomerInfo
Select * from Customers
It’s probably better to try not to use select * in views in general, but during development it can save a lot of time when schemas are changing regularly.
Running a select * from the view returns the results as shown previously, the view is after all just a select * from the underlying table.
Now, we have a request to alter the Customers Table, so that TotalChildren will be before the BirthDate column, Phone will be before AddressLine1 and LastName will be changed to Surname:
Pretty easy change right? But then look what happens if you execute the view:
The column data for TotalChildren & Phone is in the correct position, however the column headers are completely incorrect! The birthdate column has the data for TotalChildren underneath it, AddressLine1 has the Phone number data underneath it and LastName has not been updated to Surname!
So what has happened here?
A schema change to the base table does not force a metadata update on the view. If we query the view metadata we see the column order and names reflect those of the table before the table schema change:
The issue is easily resolved by one of two ways:
· Drop and recreate the view
· Execute sp_refreshview
Sp_refresh view is usually the preferred solution, and can be run against a specific view or against all views.
This issue can be completely prevented by creating the view with schemabinding, however it can become quite annoying during development to have to remove schema each time a schema change is required, and a refresh of the schema metadata is still required,
however this will happen when you alter the view to re-add schemabinding.
Attached is a short script to demo items discussed.
That’s the first blog out of the way! Phew! Any questions, please just give me a shout.
view_metadata_issues_examples.sql (1.71 kb)