April 2010 - Posts
If you are a SQL Server Professional in the UK and ideally in the London area and would be interested in working with me on some interesting SQL Server Projects then please contact me. http://sqlblogcasts.com/blogs/simons/contact.aspx
Work will be performance tuning, SSIS and general SQL development.
You will see if you use views and functions that SQL Server holds the rowset metadata for this in system tables. This means that if you change the underlying tables, columns and data types your views and functions can be out of sync.
This is especially the case with views and functions that use select *
To get the metadata to be updated you need to use sp_refreshsqlmodule.
This forces the object to be “re run” into the database and the meta data updated.
Thomas mentioned sp_refreshview which is a wrapper for sp_refreshsqlmodule in his blog post http://thomaslarock.com/2010/04/refresh-your-views-with-sp_refreshview/ This talks about best practice when upgrading and moving databases around.
Don’t forget that if you complete you session and conference feedback for SQLBits then you will be entered into a draw for an XBox Super Elite. Not only that, we also have a registration for the PASS Summit in November this year to give away.
The survey is essential for us to make SQLBits conference better. If you don’t tell us what doesn’t work then we can’t fix it. We listened this time and gave you better signage and more information in your agenda about sessions and the abstracts. So please complete the feedback
If you don’t think you will be lucky you can always register for the PASS summit using the SQLBits promotion code. To get this you need to login to the SQLBits site (www.sqlbits.com/login.aspx ) and then go to the community page (www.sqlbits.com/about/community.aspx ) to find the code. This will not only give you a great discount it will also help support SQLBits.
Don’t forget you can still win an XBox or a registration for the PASS summit by spending 5 minutes completing the survey.
Just found this really simple but very useful site to give you the latitude and longitude of a location
The great thing is that you can select from a map and it also gives you the WKT you can use to generate you geography in SQL Server.
This is the location for SQLBits VI
If you can’t make it to SQLBits and you are registered then please unregister. We are currently at our limit and so in unregistering you leave the possibility that someone else might be able to attend.
We all know that Brent Ozar did the MCM training/certification over the past few weeks. He wrote an interesting article on Friday about the bad bits (http://www.brentozar.com/archive/2010/04/sql-mcm-now-bad-stuff/) of the training and it lead me to thinking about the certification process again(I often think about it, and it appears often in response to something from Brent http://sqlblogcasts.com/blogs/simons/archive/2010/02/12/Whats-missing-in-the-SQL-Certification-process-.aspx)
This time what got me thinking was an overriding feeling from Brent that he should be able to attend the training and pass the exams and labs. Brent has never hidden the fact that some of the more developer areas CLR, Service Broker etc are not his strong point and so its this in conjunction with the expectation to pass that I’ve been thinking about.
We all know the normal MOC training stuff is cramable, you can learn for them. In fact many companies make considerable amount of money out of it. The MCM however I feel should be different. It should be experience based. You should have to know the stuff and know how to use it. ok you can be rusty and maybe not know all the facets of something but you should still have a deep understanding based on experience. Just because the best people in the industry have taught you doesn’t mean 6 months later you will know what to do.
IT often looks in relation to other industries a bit of a mess. If you want to build a multi million pound building you would get a team of architects that have spent 7 years studying. Thats not the same for IT. You might say that its just not possible in IT, things change too much. well things do change but they do in the building industry as well, new materials, new fixings but there is still a set of underlying principles that underpin the design of a building, managing loads, moving people, providing lighting etc.
I find it really odd that there is such a high amount of content to learn in the MCM. Everyone I’ve spoken to that has done it, passed or not, have said you spend all the 3 weeks eating, sleeping, learning or revising, and you are generally doing the last two whilst doing the first two. I contacted Brent and his comment was
“…they're teaching you stuff that's not out yet or is outside of conventional DBA work. … That's where the cram parts come in - there's so much you couldn't have known going in even if you work full time building training materials for new versions”
That was one of Brents other points which I’m not going to go into and one I’m not sure where I sit, I can see merits in giving an overview but I probably err on the side of it not being a examinable part of the MCM
Anyway I really do hope that you can’t just cram your way through the MCM, I’ve heard you can’t, but I guess there is a line that has to be drawn. I would love to experience it myself to see where is has been but as an independent can’t justify the time and loss of revenue.
I just hope that the line is drawn nearer the side of experience rather than one of memory recall and it appears it is and it isn’t.
I will leave the last word to Brent who reassured me that the core knowledge for the MCM isn’t crammable, and really is a word of warning to those that are contemplating the MCM.
“..The lab is unstudyable. It's 100% surprise hard work scenarios that are all about real world experience. The class did have a few people who tried to cram, and the lab really showed who was winging it. It required an amaaaazing ability to string multiple features together in undocumented ways that weren't covered in any book. Other attendees couldn't figure out how it was even possible...”
Rob Farley is doing a live meeting this month on understanding what SARGable means. It is at 1pm BST and so if you are in the UK will be a very useful hour spent.
for more details go to http://www.sqlpass.org/Events/ctl/ViewEvent/mid/521.aspx?ID=341
The description of the session is
Understanding SARGability (to make your queries run faster)
SARGable means Search ARGument able. It relates to the ability to search through an index for a value, but unfortunately, many database professionals don’t really understand it. This can lead to queries which miss out on the potential to run much quicker. This is a talk involving lots of demos, showing plenty of queries and execution plans. Any time I’m not typing on the screen, I’ll be waving my hands around to explain things, with apologies to those tuned in through Live Meeting.
It was very amusing going to PASS and the MVP summit this year and people coming up to me asking how my baby was. Well thats not so amusing, how they know I‘ve got a baby is. During the last 24hrs of PASS my wife was overdue having our 3rd child, she had gone out and so I was on alert if the phone rang. Guess what it rang half way through my presentation on reporting services tips and tricks, luckily it wasn’t my wife but we did have the baby the next day. That was close.
So 24hrs of PASS is back on the 19th May and I am doing the 11PM GMT session on high performance functions. This has become a bug bear of mine and something I mention at almost all sessions I present.
If you are writing TSQL then you need to come to my session.
You are able to register by going to http://www.sqlpass.org/24hours/2010/default.aspx.
See you there.
The best way to avoid SQL Injection is with parameters. With parameters you can’t get SQL Injection.
You only get SQL Injection where you are building a SQL statement by concatenating your parameter values in with your SQL statement.
Annoyingly many TSQL statements don’t take parameters, CREATE DATABASE for instance, or really annoyingly ALTER USER. In these situations you have to rely on using QUOTENAME or REPLACE to avoid SQL Injection. (Kimberly Tripp takes about this in her recent blog post Little Bobby Tables, SQL Injection and EXECUTE AS)
The same applies if you are dynamically choosing columns or tables, in these cases you have to concatenate your values together but it is essential that you protect yourself.
To use parameters you use sp_executesql like this
ALTER PROCEDURE GetMembers
DECLARE @ExecStr nvarchar(2000)
SELECT @ExecStr = N'SELECT * FROM dbo.member WHERE FirstName = @FirstName'
EXEC sp_executesql @ExecStr, N'@Firstname nvarchar(2000)', @FirstName
You can see here that our SQL statement uses parameters and so we are protected from SQLInjection.
I am just loving the fact that the Ordance Survey has now released a huge amount of data that can be used freely.
I’ve downloaded the Panorama (tm) data http://www.ordnancesurvey.co.uk/oswebsite/products/land-form-panorama-contours/index.html. which is all the contours for the UK
This I’ve loaded into SQL Server using Safe Computing’s FME (http://www.safe.com/). This is because the data is a Autocad DXF file and translating that to SQL Server spatial data is not easy. The FME workbench is not too intuitive straight off but once you get the nack of how it works its very easy.
Almost 800,000 Contours take up ~ 1GB of space, and an index on top uses 200MB.
Its very cool.
This is the output of a query to get all the contours near where I grew up. I chose this because the is a very distinctive limestone ridge called the Wenlock Edge that runs to one side of the village. You can see a picture of it here http://www.bbc.co.uk/england/sevenwonders/midlands/wenlock-edge/index.shtml
I wondered if I would be able to figure it out from just the contours and you can. You can also see the hill I used to go sledging down.
So what use is this, well you can use a find nearest type query to get the nearest contour and therefore a rough approximation of your elevation.
To do a nearest the best solution is to find those within a specific distance and then order by distance. Currently the optimiser can’t optimise the straight order by STDistance which would be the simplest. If you don’t find a nearest then you need to look a little wider. This is the best route as it can efficiently use an index, the choice of your filter distance is the only thing you need to decide on.
select top 1 CAST(Elevation as varchar(100)) elevation
where Shape.STDistance(@point) < 500
order by Shape.STDistance(@point)
To display the results above I’ve also used a trick I use a lot to display the point I’m looking for. You will see that it is a circle (my house). To achieve this, union all the results of your query with query that returns the additional point i.e.
select CAST(Elevation as varchar(100)) elevation
where Shape.STDistance(@point) < 3000
select 'Home', @point.STBuffer(50),0
You might ask why you have to do a nearest neighbour query. This is because the contours are not always closed lines. I am contemplating working out have to close the lines so I can get polygons. Thats an interesting challenge, as is finding the two contours either side of a point so that you can interpolate the elevation more accurately. Those will be another blog post.
You could of course use an API to get the elevation, like the google maps api http://maps.google.com/maps/api/elevation/xml?locations=lat,long&sensor=false I couldn’t find a bing maps equivalent.