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}

_AS

_{Select * from Customers} 

_GO

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.

Fintan.

view_metadata_issues_examples.sql (1.71 kb)

Help me please my data’s fried

My manager is fuming

My users just cannot get in

Its very time-consuming

Resources have all gone to pot

My memory has amnesia

The processor is eaten up

And the network is freezing!

Our data feeds are going slow

And inserts do not happen

Our dashboards are not up to date

Yeah - Someone’s for a slappin’

Infrastructure took a look,

And bought some new equipment

They assured me things would go

At lightspeed, in an instant

We got a team and hauled it in

New servers, SAN and Rack

We moved the database and app

Improvement? - not jack.

Infrastructure bought some more

Disks, solid state and all

And memory, upgraded to the max

Things better? Not at all

Ah now they said, we have instead

Bought Fusion IO

They  plugged it in, it was a SIN

Performance gain? NO

They asked their devs to take a look

They said it was the SQL

And that it was above their heads

So then they called us people

In we went to take a look

Your database size?, we asked

There was a pause, a big deep breath

A GIGABYTE they gasped

Quite curiously, I gazed

Upon their stored procedure

It was quite long, I will admit

I nearly had a seizure!

With UDF’s, temp tables

And cursors abounding

Trigger-Happy to be sure

It really was astounding!

I took a look at their IO

T’was something like the matrix

Covering Indexes? Not a one

The scanning was CRAZY!

Non-sargable, crossed numa nodes

Blown cache, both plan and dough

A total mess – is this a test?

It really can’t be so!

Using Dynamic SQL doesn't always perform

And no param's is not the norm!

So what to do that is the question?

Maybe they'd take a small suggestion?

When your data fits on a memory stick

More hardware's unlikely to do the trick!!