Dave does Data

Review: Programming MS SQL Server 2008 – Holistic coverage for an App Developer

Following on from my review of my library. This week, Programming Microsoft SQL Server 2008.

Summary:

It appears to me that the authors started this book with the premise “There is a lot in SQL2008 that can dramatically change the way you architect solutions. Many features elegantly remove some of the clunky poor performance approaches we use today” So they set about drawing your attention to those features & mapping it back to the problems you currently experience when writing applications.

The book is a good read, plenty of code examples & screenshots to help understand the subject quickly (not just to pad out the pages)

This book differs from other SQL 2008 books I’ve reviewed in 3 ways :-

1. It takes a holistic approach. It doesn’t stop at the Relational database but expands your thinking into
   (i) handling non-relational datatypes; Spatial, Text, Video & Audio streaming, Hierarchies.
   (ii) Using the BI Components for Reporting & Analytics instead of the, usually less efficient approach, of doing it all yourself with TSQL.
2. It focuses purely on SQL 2008. There is very little here for those on SQL 2005 or earlier. So it is not a “Hey there is much in the engine you will need, a lot was already there, & this new feature extends it” but more a “This new interface now lets you do it in 3 lines & removes that “huge cliff” you had to program around. OR SQL now has a component that does that, you don’t need to develop your own component from scratch.”
3. It is more for those in development teams. Except for a small section, Chapter 2, the book assumes you will be comfortable in Visual Studio or at least BI Dev Studio; for writing SQL Reports & the Analytics piece. OR even if you don’t code yourself, you want the knowledge to suggest new approaches to architect the app better.

Recommended Audience:

SQL Leaders - Everyone who considers themselves “a database person”, especially if you advise others on how to create more efficient solutions that use SQL Server. 
Architects – more so if you design down to the component level.
Developers – who write code to interact with Databases, Analytics, Reporting or Data Mining.
Anyone who wants a good understanding of the new capabilities of SQL 2008 in 1 book, at 951 pages this is quite a reference.

Version: SQL 2008, & SQL 2008 R2 (when it ships in 2010). Not for SQL 2005 & earlier versions – except the Analytics part. If that was all you wanted, you could find another book just dedicated to that.

Detail:

Like SQL 2008 Internals, this book is written by a team of authors. Handy because it is hard to specialise in everything. Compared to the SQL Internals authors, they must have spent much more time peer reviewing as there is a lot less variation in style between each chapter. Though you can see one author was keen to give a little background while one of the others prefers to dive right in.

Part 1: Core Fundamentals (228 Pages)

This part is great. Buy the book just for Chapter 2 & then flesh out your thinking with the other chapters.

Chapter 1: Overview

Nothing. Just tells you what is in the book.

Chapter 2: T-SQL Enhancements

You’ve gotta read this chapter, even if you just stand there in the book store, it is pure gold. I could tell you that it has a very nice coverage of CTE’s both recursive & not. Or list the TSQL Features that it covers: PIVOT, UNPIVOT, APPLY, TOP, ROW_NUMBER, OVER, Ranking Functions, Try/CATCH, WAITFOR, DDL Triggers,  Date & Time Datatypes, MERGE Statement, OUTPUT clause, Grouping Sets. But Books Online does that too. What sets this apart is their application of these features. They have put a lot of thought into “Does this new feature empower me to solve a problem I’d been unable to fix before”, then serves up a lot of really useful examples & ideas. If you don’t read this thinking “wow I wish I had that in the last project(s) I worked on”, you must be fresh from school. (or did too many drugs in your youth)

Chapter 3: Exploring SQL CLR

This is not the ultimate reference in SQL CLR, it misses some of the “how to ensure your CLR Designs perform well”. That said it is a great tutorial. It covers all you need to know to write all forms of CLRs; Datatypes, Triggers, Aggregates. How to deploy them. How to use the tools. There are a few landmines in CLR development that you can waste days in. This guides you around them, giving you a methodology that helps you avoid a lot of the newbie pain.  

Chapter 4: Server Management

How to programmatically access Server Management Objects (SMO). If your app needs to Manage SQL Server, SMO is preferred over sniffing around the System Catalogue with TSQL scripts ie: “SELECT * FROM sys.objects”. It briefly touches Policy-Based Management. But for PBM you’d get more listening to a Tech-Ed session on the topic.

Chapter 5: Security in SQL Server 2008

Security is always a pain & is often the cause of a lot of “hard to solve” user problems. Largely because is it boring, not well understood & squeezed into the design when the project is nearly complete. This chapter makes it easier. It covers Authorization, Authentication, Encryption (inc Transparent Data encryption), Keys & Certificate.

There is a nice section on the new SQL Server Audit Feature. If you commonly use (i) LastModifiedDate & LastModifiedBy columns OR (ii) Triggers that mirror the DML changes into “Audit” tables. This is a must read for you. 
Also a small piece on common ways hackers will hit you.

Part 2: Beyond Relational (106 Pages)

This part may permit you to remove large inefficient parts of your app & replace them with easier to maintain much more performant alternatives. It may also get you thinking of new exciting features you can offer, that will excite your users &/or customers. 

Chapter 6: XML & the Relational Database

While not a lot of recommendations or things to extend your use of this feature. It is a very comprehensive coverage of SQL’s XML capabilities together with a lot of examples. It should be enough to help figure out what isn’t working & get you going.

Chapter 7 Hierarchal Data and the Relational Database

This is 25 pages on the HierarchyID datatype. Nothing insightful, it just tells you how to use it with good examples.
While I’d prefer the HierarchyID datatype to be more I want it to “just work” ie: figure out & maintain the hierarchy without any involvement from me. It is very powerful & does remove a lot of performance issues from apps that use this style of relationship. eg: Org Charts, Bill of Materials. So it is worthwhile you know about it.

Chapter 8: Using Filestream for Unstructured Storage

This chapter lives up to its title, with a fairly comprehensive coverage on how to incorporate large files; Video, Audio, Picture & Documents, into SQL much more efficiently using the FILESTEAM feature.

While a good chapter I was a little disappointed by the narrow focus. SQL 2008 introduced two new features for BLOB handling to compliment the SQL2005’s VARBINARY(MAX) replacement of the IMAGE datatype. FILESTREAM was one of them & Remote BLOB Storage API was the other. It seems they missed the opportunity to cover Remote BLOB’s here or at least to give them a mention & compare / contrast when to use one over the other.

Chapter 9: Geospatial Data types

This small chapter contains a reasonable coverage of (i) Spatial Types, Methods & (ii) how to integrate with Virtual Earth. Also a small C# example of how write code that uses the Spatial Types.
Call me biased but I think my 12 Blog Posts on the topic gives a deeper coverage on using Spatial Types & methods. And Johannes Kebeck's Blog is an authority source for the Virtual Earth (Bing Maps) interop. But I’d agree if they hadn’t put something in I’d be complaining that they overlooked it, so I’ll shut-up now.

Part 3: Reach Technologies (226 Pages)

This Part is largely a coding guide. Introducing you to features in the development tools & language that will have you up & running quickly. It ensures you are informed & can choose the right technology for you.

Chapter 10: The Microsoft Data Access Machine

This is a 101 for the coder. It covers all those important things you never hear Microsoft talk about in their events. Creating DataSets, Dataset designer, Table Adaptor, Connection management, DataReader, … all the stuff that is easy to do when someone has shown you & time-consuming to figure out by yourself.

After ADO.NET, there is similar depth on Language Integrated Query (LINQ) technologies: LINQ to DataSet, LINQ to SQL, The Entity Framework & EDM, Web Services for Data.

Chapter 11: The many facets of Data Binding

Data Binding can remove a lot of code. This is a good introduction of how to get it working from the different app development environments; Windows Forms (inc LINQ to SQL, Entity Framework), Web with ASP.NET (inc AJAX) & Windows Presentation Foundation (WPF) including XAML & Silverlight.

Chapter 12: Transactions

It seems that most people believe that Transactions are unimportant in a relational system & recommend you use the NOLOCK hint to turn them off. ;-)  But if you believe that data corruption is undesirable in your app this chapter may interest you. It covers everything from the basics; ACID Properties & Isolation level. Then looks at the impact of SQL Configuration Options; ie: AutoCommit vs Explicit Transactions vs Batch Scoped & MARS.  Then finishes with good coverage on Distributed Transactions; 2 Phase commits,  Resource Managers. Examples show xtns in your TSQL Code, your .NET app code & your SQL CLR code.

Chapter 13: Developing Occasionally Connected Systems

70 pages of really detailed coverage looking at the technology alternatives for distributed occasionally connected apps. The new Sync Services & Change Tracking technologies are explained the compared & contrasted with Merge Replication. This chapter has more than sufficient detail for you to use it as a reference with starting to develop your app using either Merge or CT technologies. It does not overlook that most of these solutions will want to sync with PDA/Mobile phones, so SQL Server Compact v3.5 is featured strongly.  
Note: SQL 2008’s Change Tracking (CT) is very different to Change Data Tracking (CDC) though in some apps you could use CT or CDC.

Part 4: Business Intelligence (388 Pages)

This Part is largely a tutorial style, guiding people inexperienced in the SQL BI topics thru the options available to them.

I had planned on comparing this to Microsoft SQL Server 2008 Analysis Services Step by Step but in my generous excitement I gave it away at a SQL User Group meeting. From memory, the content is similar. Of course this book has 3 other parts in it & is more targeted to those writing applications that use the engine.

Chapter 14: Data Warehousing

Much of this chapter is a “What is DW”; comparing Inmon vs Kimbal & similar topic definitions. Change Data Capture gets a very detailed coverage. Whereas features like SPARSE columns, Data Page Compression & Backup Compression are introduced & have 1 example each. 

Chapter 15: Basic OLAP

This is an OLAP 101. It guides you thru using the tools to create a cube. Predominantly it is an introduction to the designers & wizards. Like chapt 14 much of this is applicable to SQL 2005 & perhaps even SQL 2000.

Chapter 16: Advanced OLAP

This is not Advanced OLAP, but it is a useful coverage of all the other features beyond creating a basic cube. eg: Dimensions; Member Grouping, Attribute Relationships, Calculations, Names Sets, KPI’s, Actions, Perspectives, Translations & Security. It covers Proactive caching, Partitions, Aggregation Design & other Storage Settings.
If you have minimal to zero experience in SQL OLAP you will find these 3 chapters have sufficient detail to get you going without getting lost in detail or reading a specialist book on the topic.
If you want Advanced OLAP get the bible.  SQL Server 2005 Analysis Services (not an introductory text)

Chapter 17 OLAP Queries, Tools and Application Development

This covers a multiple of visualisation options.
(i) Excel as a client tool. This will be superseded when the Gemini Execl add-in is released with SQL 2008 R2.
(ii) Excel Services for web BI.
(iii) A tiny glimpse of MDX (Multi-Dimensional eXpression language Analysis Services (SSAS) equivalent to T-SQL )
(iv) .NET development via ADO MD.NET
(v) Analysis Management Objects (AMO) this is the SSAS equivalent to SMO.
(vi) XMLA – XML for Analysis Services.
(vii) Writing “SQL CLR’s” for Analysis Services.

That is a lot of ground to cover. It does a good job of making you aware of the technologies, what they can do with 1 or 2 examples. As you can imagine once you get started on a project you’ll need more than just this chapter as your reference.

Chapter 18 Expanding your Business Intelligence with Data Mining

This is a nice overview of Data Mining; from methodology you should use, a look at the viewers, creating a mining model & how Nested Tables work thru to reasonable coving of DMX (Data Mining eXtension language). There is a few pages on including it in your code & some mention of the SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007
Again a nice succinct chapter but if I were spending any time reading on Dataming I’d get the bible Data Mining with Microsoft SQL Server 2008  see my Blog Post

Chapter 19: Reporting Services.

This would be best described as a “Tutorial Walk thru” it will get you up & running. It does give some examples on how to incorporate reports into your own code. But you do have to extrapolate from the tutorial into your own work. It is not a reference on all the options you have, nor does it have any “Best Practise” Recommendations. Nor does it contain anything that will expand your thinking into new “not so obvious” applications as does chapter 2 of this book.
For a more detailed walkthru I’d consider Microsoft® SQL Server® 2008 Reporting Services Step by Step its 525 pages swamp the 79 pages of this chapter. But still it is an introductory text & one suitable for Report Authors . I’ve yet to see a book that covered the programmatic aspects &/or extending SQL RS in any great detail. But I’ll keep looking.

If you are too busy to walk to a book store & too lazy to type a search into Bing or Google, the following are links to Online Retailers.

Australians:

• MS Press Australia

When getting the link I noticed this promotion.

So many reasons to shop at the Microsoft Press Online Book Store
– 40% off all books purchased until October 31^st 09 and...

Best & Latest Microsoft Press Books	Express Delivery
Quick & Easy Ordering	Amazon Price Match Promise
Regular Promotions	Affiliate Program

Rest of World:

• Barnes and Noble
• Amazon

As always I’m interested in your thoughts. Did you read this far? Was it useful or way too much detail?

Thanks Dave.

Thought for the post:

This took way longer to type up than I thought it would. Then again it took a few weeks to read the book too.

Technorati Tags: TSQL,Book Recommendations,Book Reviews,SQL 2008,SQL Server 2008

Review: MS SQL Server 2008 Internals – Core knowledge for all DBA’s

Following on from my review of my library. This week, Microsoft SQL Server 2008 Internals.

Summary:

This is not as the title might suggest, “special” information for elite DBA’s. It contains the base knowledge that all DBA’s need in order to understand what is happening under the covers. It may stop you from making stupid mistakes, ie: The “computer equivalent of driving from Sydney to Brisbane with the handbrake on” or the classic “I didn’t even consider the IO load of TempDB, is that important?”. It is knowledge you are unlikely to get by just downloading white papers. At more than 700 pages it is only for those who are serious about developing their skills in SQL.
Note: It is only applicable to the Relational Engine. It has no coverage of SSAS, SSRS or SSIS, for those areas, see my comments on Resource Recommendations - Integration Services (SSIS & DTS), Resource Recommendations - Data Mining. (I just realised I haven’t posted the info on the SSAS Bible ( Microsoft SQL Server 2005 Analysis Services)  & nor the SSRS resources. I will do soon).

 

Recommended Audience: SQL Leaders - Everyone who considers themselves “a database person”.  It is stuff you should know, its not everything but forms a good base to build your expertise on.

Version: Predominantly SQL 2008, & SQL 2008 R2 when it ships in 2010. A lot of this book also applies to SQL 2005. But 10 – 20% of the book is only relevant to the new 2008 features & low level changes that do not apply to 2005. Similarly not all the great content in the SQL2005 Internals books made it into this book & they are vey useful in 2008 installations. So I’d consider buying both, or just getting the SQL2005 Internals books if you have yet to convince your management of the significant cost / benefits of upgrading to SQL 2008. Then get this one when they are ready (perhaps it will be called SQL11 or SQL 15 Internals by then) ;-(

Detail:

This book is written by a team of authors. All are very talented. If you’ve been deep in SQL for a while you will notice a difference between the chapters. In some chapters you may think, “I figured that out myself, anyone with a strong knowledge of the once undocumented features like (DBCC PAGE, DBCC LOG) has probably explored to see what that does”. But a few of these chapters will have you thinking “How did they figure that out?”.  At which point it will become clear that these guys are / were Product Managers on the SQL Engineering team. They didn’t just read the source code, they designed it that way. So some of the chapters gives you the why it works the way it does & not just how it works.

Chapter 1: SQL Server 2008 Architecture & Configuration

As the title suggests: Architecture; Storage Engine, Query Processor, Buffer Pool, Checkpoints, Lazy Writer, Resource governor, SQLOS, Scheduler, & the configuration options you need to set to control it all. Especially relevant to those who deal with larger servers with more than 8 CPU’s.

Chapter 2: Change Tracking, Tracing & Extended Events

Unfortunately it would easily be possible to write an entire book on either Extended Events &/or Tracing for load generation, stress testing & performance tuning. This chapter only got 50 pages. It does a good job on highlighting these important & often overlooked features. It details how to set up & use these key features. As I doubt anyone will write a such a niche book, use this as a start & read up on Blogs & white papers for the rest. Extended Events are really powerful & few people have realised what they have.

Chapter 3: Databases & Database Files

Nice coverage of how to control your IO; Understanding the different load characteristics of Log, Data & TempDB. Creating / Shrinking filegroups, Database Options, File formats; GAM, SGAM, IAM. PFS etc. Understanding Database Snapshots.

Chapter 4: Logging & Recovery

This is not When & How to back up. But my favourites for performance tuning; Recovery Mode impacts IO, Understanding Read / Write loads & how they vary with Recovery Mode & checkpoint. Also the core of what is happening in the log files.

Chapter 5: Tables

This provides a lot of detail on the On-Disk layout of the table & explores the impact of different data types / NULLs. It doesn’t provide a lot of recommendations but will be a good reference for these important basics. 

Chapter 6: Indexes: Internals & Management

After the Optimizer, Indexes are the most commonly stuffed up thing in a database. eg: “We don’t have indexes on our foreign keys as maintaining indexes will slow up the updates.”  “So … you don’t think that doing a Table Scan to find each row before you update it could be slowing things a bit? Oh & in a cursor too … priceless.” WELL THERE”S YA PROBLEM!!
This is a big chapter: It shows their on-disk layout, DMV’s to analyse them, Key Index creation clauses & their affect. Commands that let you explore your indexes. Good coverage on Special Structures like Computed Columns & Indexed Views. But unfortunately almost nothing on XML Indexes, Spatial Indexes & integrated Full Text Indexes. A bummer given the huge perf benefit gained over the common “WHERE x LIKE ‘ABC%’ ” queries.  I guess it is hard to sell a 2,000 page book & they do point you to other resources. To compensate they did put a ton of time in addressing the issues people face on Index Maintenance, Fragmentation & what really happens when DML statements (Insert, Update & Deletes) change data.  

Chapter 7: Special Storage

I’ve not seen this info elsewhere, covers new features of  LOB & Row Overflow, Filestream, Sparse Columns, Data Compression, Backup Compression & a small bit on Partitioning Storage. Nothing you couldn’t figure out with DBCC PAGE & a lot of time, but isn’t it nice you can quickly read it instead.

Chapter 8: The Query Optimizer

A few months ago someone asked me “How can you just look at my query & say “it’s gunna suck”? Do you just walk around with the optimiser in your head?”. I thought “well, yer, how else would you tune queries”.  This chapter is the highlight of the book. A lot of details, a little boring, but details you need to know. Once you know how the optimiser views the world & the way it uses statistics, then you are much better able to interpret the show plan output (also covered in this chapter). You are also able create effective test data & realistic load tests. This is a good compliment to the “How to read ShowPlan” documentation in Books Online, Whitepapers & the section in SQL 2005 Internals books & Itzik Ben-Gan’s TSQL Query Tuning 2005 Books

Chapter 9: Plan Caching & Recompilation

Walks thru how your queries can impact plan cache, why it is important & the Dynamic Management Views (DMV’s) you need to understand where to start to tune things.

Chapter 10: Transactions & Concurrency 

This chapter is a good start but doesn’t go far enough. It tells you “What it is & How it works” both very useful but doesn’t give you methodology on how to troubleshoot locking issues. Hopefully it will give you sufficient insight to figure that out on your own. Still, If you’ve even wondered what a SpinLock or a Latch or Intent lock is, this chapter is for you. It shows you how to discover what locks your query is taking. How Lock escalation works, helps you discover how much memory is used by locking & gives insight on the newer features like Row Versioning & Snapshot Isolation.

Chapter 11: DBCC Internals

This is not an exhaustive coverage of all DBCC Commands, many of which are flagged as depreciated. It is mostly about one, the DBCC CHECKDB command & the related consistency commands like TABLE & ALLOC. Given SQL’s reliability you may never need to know about any of this. But if a disk error turns your Database to custard then you will really appreciate the level of insight this chapter gives you in really understanding what the diagnostic messages are telling you. It may make the difference between you “coming up smelling like roses” or merely smelling like Rose fertilizer. (for those not horticulturally inclined I’m told this is best obtained from the back end of horses & cows.)

 If you are too busy to walk to a book store & too lazy to type a search into Bing or Google, the following are links to Online Retailers.

Australians:

• MS Press Australia site (note: Price Match & other benefits compared to Amazon)
• Amazon Price match Promise

Rest of World:

• Amazon
• BarnesAndNoble

Please tell me if you find these reviews useful (or not)

Dave.

Thought for the post:

If there is someone naked or a couple are intimate on the TV we get embarrassed & often stop our kids from watching. We get horrified if our teenagers can use the internet to look at nude people.
Yet we think nothing of them watching action movies where people are shot, hit, stabbed or otherwise killed, injured or maimed without consequence. We buy our kids computer games where they can role play hurting & killing others.

Why does naked or amorous = Unacceptable.
Yet someone walking around with a loaded weapon which they can kill you on a whim = Acceptable.

The “right to bear arms” vs the “right to bare arse”.  Who sold us on that plan?

I too, find myself playing shoot-ups with my kids & stop them looking at porn. The more I think about it, the more I wonder … why was I born so stupid?

(sorry this thought for the post started life as a joke but I just had an epiphany)

 

Tip: Avoiding REALLY hard to reproduce bugs in Production

If everything is bug free in Development, Testing, User Acceptance Testing (UAT) how can it possibly go wrong in Production? The following post looks at how that can occur… Intermittently.

Recommendation:

Ensure that the Server Names & SQL Instance names of your Test servers have the same or more characters than your Production Servers.

Why?

Recently I found an interesting bug. An EXECUTE @sql statement died with an error when the length of the TSQL commands in @SQL string was longer than ~500 chars.

The SQL code being executed included the Server Name as well as a variety of table names. The length of the name of the Production server SQL instance was 4 characters longer than any of the testing servers. As luck would have it, on the test servers, all the SQL Statements generated worked fine. But on the Production Server those extra 4 bytes when combined with the longest table name caused the statement to die with an error. The updates to the longest tables never happened, all the other tables were changed ok, so it was weeks before anyone noticed this “partial failure”.

I learnt 2 things from this:-

1. Always put brackets around @sql when using EXECUTE statement, it is just more robust.
2. Often in code you have a buffer of a finite size. Even if the Production server name is 1 byte longer than your Test server that might be enough to cause your command to lose the last character in the command & cause an error you failed to discover in UAT / Testing. Interesting huh?

In case you wondering if in this robust code, this error was detected & the batch immediately aborted, rolling back the batch & rising an alert. Nope. That small part of the batch failed but the rest pushed on without those rows. I can’t help wonder how many people are out there arguing with some customer service agent & are hearing “Well, thousands of our other customers got XYZ, it is unlikely that our computer just missed out on you”. ;-)

I’m sure this bite you in all sorts of ways not just TSQL Dynamic Scripts.

Dave

Tip SSMS: How to quickly find the line with the Syntax error

When executing code in SQL Server Management Studio. Occasionally people make errors. If you find yourself counting thru your TSQL Batch up to “LINE 231” where your error is supposed to be, then this tip is for you.

 

Tip: Double Click on the Error message in the results pane. (In diagram below, see Lines circled in Red) This should jump immediately to the line of code containing the error & highlight it. (see diagram below, Line circled in Green)

 

9 times out of 10 is works perfectly. The other times the confusion is usually caused by SQL Engine having an issue with an earlier line of code. eg:

• You left the “;” off the statement before a WITH statement. But the Error pointed to the line containing the WITH Statement. 
• Your Statement has multiple lines & it is unable to distinguish the precise line that the error is located. (but you should be close)
• Your “CREATE TABLE” statement failed. So all the SELECT statements that use it, die.
• You are USEing the wrong database so all the objects aren’t there.

Versions supported: SQL 2005, SQL 2008, SQL 2008 R2. (From memory even worked in SQL2000)

I thought everyone knew this, so was surprised by the reaction at Tech-Ed. Hence the post.

Hope you found it useful

Dave

How to get Database Edition Power Tools to work on Visual Studio Team System 2008 Database Edition GDR

If you want to compile sample code or write your own extensions for Visual Studio Team System 2008 Database Edition GDR (also known as DBPro or Datadude) this post will save you hours.

Summary

In Nov 08 when Microsoft shipped the GDR (General Distribution Release) for the Database Edition of VSTS. They changed the object model to make it compatible with the Visual Studio 2010, but that broke most of the existing Add-ins & sample code that you find on the net. Unfortunately the docs telling you how to fix this had errors. So you could spend hours (days) & not get anywhere. This post shows you how to get it all working quickly.
By the way, in May 09 they shipped an update Visual Studio Team System 2008 Database Edition GDR R2 which included some but not all of the power tools capability.

 

Background for DBA’s who’ve not used DBPro.

Datadude is a tantalisingly brilliant tool with promise to solve some of our hardest problems; eg: Bulking up data, Stress Testing, Load testing, Improving TSQL code & knowing you’ve not broken it. Coordinating a team of DBA’s who’ve all changed Indexes, Stored Proc’s, Triggers etc & seeing ALL the changes between it & the Production system. Then creating the scripts to upgrade the Production system to the next release. An easy way to KNOW your app works perfectly with the next release of SQL Server & to precisely QUANTIFY the performance benefits / degradation you will get from deploying on the next SQL release OR new Hardware etc.

Note: I did not say it is perfect nor effortless in doing all / any of the above. Just that it has promise & each release it gets closer. Often that “close but not quite” capability has frustrated me (heaps) as it almost does precisely what I want. And then I couldn’t figure out how to get that last bit working hence it becomes a non-solution. The good news is :-

1. The platform is very extensible, in many cases you can write the bits you need without too much effort/code. And there are some nice Codeplex projects (DBPro extensions) becoming available.
2. It does do a great job on the Use Cases they target & each release it gets better. I would highly recommend you look at it for :-
1. Comparing Data: eg: what rows are the Same / Different / Deleted / Inserted between these two tables. Very handy to have confidence that your new improved version of your stored proc is doing the same as the old slow version in production. Especially handy if there are triggers & other code User Defined Functions & CLR code involved.
2. Comparing Schema:  After many DBA’s have all worked on different objects, how can you be confident that all the changes have been put into the script to upgrade the UAT or Production system to the next release of your app.
3. Unit Testing: If you are starting from scratch & mainly using stored procedures that don’t use Temp Tables, this works great.  But for legacy systems & especially where you want to use these tests for Load testing / Performance comparisons, it is so close but misses by a mile. I have some good ideas on extensions that will make this much easier so if I get it working I’ll post it on codeplex.
4. Load testing: Possible but time-consuming & inflexible. There is a Codeplex project; SQL Load Test that takes a SQL Trace & turns it into a Unit Test. Not perfect but dramatically reduces the work to make this a financially viable idea. Two others I’ve not used but look interesting are T.S.T. the T-SQL Test Tool & Managed Code Interface for T-SQL Test Tool
5. Data Generation: It does a brilliant job for trivial tables ie: Reference or Lookup tables & easy to randomly create new rows by picking real values from a production source. It does do Parent – Child tables. The potential to extend this to very complex distributions seems quite high.  I wouldn’t suggest it to creating the really hard “data & time dependant” transactional tables ie: Where you are Inserting, then updating & finally deleting rows depending on values in other tables. eg: the sort of data you’d keep in a Hospital Patient Scheduling system, where you need to generate appointments for patients but only after they’ve been admitted & before they’ve been discharged.

As this post wasn’t intended to be a product review. I’ll summarise by saying, if you’ve not seriously looked at it you should, with each release this tool gets more & more relevant to the DBA who just wants a tool & doesn’t want to “just code a bit in C# or VB”.

The Tip: The correct way to write VS DBPro Extensions

The doc that shipped with the product has a few tiny errors that guarantee you will fail. The web version does have some corrections but not they didn’t get everything so you will still fail. Below are the steps that work.

1. Open the Documentation VSTS 2008 Database Edition GDR Documentation (or go the the web How to: Upgrade a Custom Test Condition from a Previous Release)
   
2. Go to the section called “How to: Upgrade a Custom Test Condition from a Previous Release”
    
3. When you get to step: “To add an extension compatibility attribute” there is an ERROR.

Do NOT use the namespace they recommend eg:
[DatabaseServicesProviderCompatibility(DspCompatibilityCategory.None)]

Instead use
[DatabaseSchemaProviderCompatibility(DspCompatibilityCategory.None)]

4. Change your references as the doc states. Remove any references relating to Microsoft.VisualStudio.TeamSystem.Data & replace with its new equivalent Microsoft.Data.Schema. You may also find any / all of the following namespaces handy. Depending on what you are writing.

using Microsoft.Data.Schema.UnitTesting;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema;
using Microsoft.Data.Schema.UnitTesting.Conditions;


5. A similar namespace rename needs to happen to the deployment script to register your extension. Again most of the samples I saw on the net & in Books Online were pre-GDR. You need to change Microsoft.VisualStudio.TeamSystem.Data.Extensions to Microsoft.Data.Schema.Extensions in a number of places.

For Example: The old style.

<?xml version="1.0" encoding="utf-8" ?>

<extensions assembly="<ProjectName>, Version=9.1.0.0, Culture=neutral, PublicKeyToken=<Insert Key Here>" version="1" xmlns="urn:Microsoft.VisualStudio.TeamSystem.Data.Extensions"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="urn:Microsoft.VisualStudio.TeamSystem.Data.Extensions Microsoft.VisualStudio.TeamSystem.Data.Extensions.xsd">

<extension type="<Namespace_Name>.<Class_Name>" enabled="true" />

</extensions>


Is rewritten to become

<?xml version="1.0" encoding="utf-8" ?>

<extensions assembly="<ProjectName>, Version=9.1.0.0, Culture=neutral, PublicKeyToken=<Insert Key Here>" version="1" xmlns="urn:Microsoft.Data.Schema.Extensions"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd">

<extension type="<Namespace_Name>.<Class_Name>" enabled="true" />

</extensions>


6. Tip 1: You can also register multiple Test conditions from the one project by adding extra Extention lines.

<?xml version="1.0" encoding="utf-8" ?>

<extensions assembly="<ProjectName>, Version=9.1.0.0, Culture=neutral, PublicKeyToken=<Insert Key Here>" version="1" xmlns="urn:Microsoft.Data.Schema.Extensions"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd">

<extension type="<Namespace_Name>.<Class_Name>" enabled="true" />

<extension type="<Namespace_Name>.<Optional Class_Name2>" enabled="true" />

<extension type="<Namespace_Name>.<Optional Class_Name3>" enabled="true" />

</extensions>


7. Tip 2: To get the 1st line, PublicKeyToken …etc. Run the following command from within VS Command Window

? System.Reflection.Assembly.LoadFrom(@"FilePath\yourCompiled.dll").FullName

eg ? System.Reflection.Assembly.LoadFrom(@"c:\YourSolutionPath\bin\Debug\yourCompiled.dll").FullName

This returns something like the following which you can just paste into your .XML file.

   "ChecksumCondition, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn"

8. Then create your own subdirectory to hold your CustomExtensions make it a child of [Program Files]\Microsoft Visual Studio 9.0\VSTSDB\Extensions\

You can copy your files directly into [Program Files]\Microsoft Visual Studio 9.0\VSTSDB\Extensions but that is considered bad practice as you may overwrite something important.

Note: the total lack of protection in Windows XP makes this step easy. However Vista, Window 7 & Win2008 have security all over this directory, so another reason to create a “CustomExtensions” directory is to change permissions to permit a broader level of access, else you’ll need to be administrator to overcome the “Access Denied” errors.

9. The two files you need to copy into the location you just created are :-

i. Your signed assembly (.dll) – created when you built your project.

ii. YourTestConditionName.Extensions.xml – created in Step 5 above.

 

The rest of what you need to know is in the GDR Help file (Books Online) mentioned in Step 1.

Tip 3: A great place to get code to write your own Checksum test &/or Schema checking tests is from Jamie’s article. I highly recommend you check it out.

Apply Test-Driven Development to your Database Projects

- DatabaseUnitTesting2008_Launch.exe (174 KB) Download the Sample code form Jamie's article. This will create the CheckSum & Schema Tests. Even if you only compile & use them you will find they speed up your TSQL refactoring efforts.

 

Created my first custom data generator for VSTE DBPro (aka DataDude)

 

FIX: You cannot install the RTM version of Microsoft Visual Studio Team System 2008 Database Edition GDR if the RC version of VSTS 2008 Database Edition GDR is installed on the computer

I hope this saves you time. Remember if you create anything really cool put it up on codeplex.

Dave

Tip SSMS: Script your Entire Table including the Data – a hidden gem.

Have you ever written a TSQL SELECT statement that builds INSERT Statements so you can script the entire table? Usually we do it to make it simple to create & load a “Reference or Lookup” table? Sure, Most DBA’s have.
This Waay Cool little feature is buried in the options. You’ll never need to write your own again.

The Basics:

Most people have found you can script out a single table or stored procedure. That capability was there in SQL 2000. Just Right-Click a Table, View, Stored Proc or other object.

The Handy:

But click a little higher, at the Database Node & you discover this little gem. Generate Scripts…

This opens the “Generate SQL Server Scripts Wizard” which might not seem that exciting. Until you get to the 3rd pane & discover this little sleeper. Script Data = True
It is set to False by default. So it will only reward the inquisitve.

And faster than you can say “U Beauty”, instant reference table, just run script.

I’m not saying that SSIS is redundant, Bulk-Copy is highly valuable. But for those little reference tables, this is a winner.

In case you are wondering.
Yes the Generate Scripts Wizard is included in SQL 2005.
No the Script Data option is not in SQL 2005.

So I’d recommend you upgrade (there is heaps of other value you are missing out on, things that save big $$$). But if you can’t, a work around is to install the SQL 2008 tools on one box & this should then work for you. I’ve not checked if this is possible from the SQL 2008 Express Edition. I suspect you may need SQL Server 2008 Developer Edition. 

Enjoy
      Dave.

Crap thought for the Post: Is it possible to catch both Swine Flu & Averian(Bird) Flu while playing a game of Reversi? And if you did, would you dream of Pigs flying backward?

Tip SSMS: How to BLOCK Select

SQL Server Management Studio has so much capability, yet most people I meet don’t seem to have read the manual to discover it.

So I thought I might post a few of features I find invaluable. This works for SQL 2005, SQL2008, SQL2008 R2 & Visual Studio.

Summary:

Tip 1: It is possible to “ALT + Select” to highlight a block of text to work on.

Tip 2: If you press ALT & stay in the same column (vertical line), it doesn’t select a block but instead selects an entire line, not just the partial top & partial bottom lines.

The following is a quick example of what I mean plus a few other uses you may not have thought of.

 

Example:

Tip 1: It is possible to “ALT + Select” to highlight a block of text to work on. eg: Hold the ALT key, Left Click & move your Mouse produces the Blue “Selected” region in picture below. You must press ALT prior to moving the mouse.

 

If I hit DELETE, then I remove just those characters so they all line up next to the NVARCHAR.

Just for comparison. If I just Click & Drag down without the ALT. I’d see all the lines selected. Starting from the Character I clicked on at the start line & finishing on a partial line I released the mouse.

Tip 2: If you press ALT & stay in the same column (vertical line), it doesn’t select a block but instead selects an entire line, not just the partial top & partial bottom lines. eg:

 

Other Uses:

1. Block Select an area of code to limit the effects of other commands.

eg: using CTRL+ALT+U to convert all selected text to Upper case.

2. Same can be done for :-

i) Search & Replace within a small area,
ii) Copying a column of Text next to itself &
iii) many other uses.

The following is somewhat contrived example of building really large WHERE clauses. The sort of TSQL you might create if you wanted to compare every row in the table with every row in the same table in a different database. Some people may do this when they are rewriting stored procedures & need to check that the new code gives the same result as the old code. Of course they’d use a FULL OUTER JOIN to ensure they noticed if they Deleted or Inserted any extra rows. (Clearly the data comparison feature in Visual Studio for Database Professionals is way faster & less error prone but not everyone has installed it.)   eg:

Step A: Create a large table, using SSMS “Script AS” or “SELECT Top N Rows” is handy for this.

 

Step B: ALT+Select the Column of  info you want & Paste it in the WHERE / ON Clause area.

Step C: Repeat but this time grab some Whitespace to push the rows to the right, which ensures the code still lines up so you have a vertical area you can conveniently select.

 Step D: Cut the Column names again & Paste to the left of themselves.

Step E: Highlight one of the columns & use Find & Replace, just to change values in the Highlighted area. eg: Remove the comma “,” & replace with an Equal & Table Alias “= R.”

 

Step F: Repeat above but this time Swap the comma for a Join Clause & Table Alias “ AND F.”  (Yes I also swapped the “WHERE” to an “ON” & added the 3 part table names)

 

Step G: Use some of the Advanced Editing Commands to tidy it up. eg: Delete Horizontal White Space ( Hit Ctrl K, then Ctrl \ ) & “Increase Indent” button.

 

Kind of a long post for such a simple feature. I hope the pictures stimulate your thinking in the wide variety of ways this can change the way you work.

enjoy

Dave.

 

Lame thought for the post. When we were out shopping my girlfriend suggested that it was about time we took our relationship to a whole new level. So I parked on the roof.

Review: MS SQL Server 2008 T-SQL Fundamentals – a must have for most people.

At Tech-Ed Aust last week I had an interesting evening with a few people chatting about finding information & how hard it is to judge the value of textbooks when you are looking online. As I am an avid reader & have a huge library a few of my colleagues asked me to post my 2 cents about the books I’d suggest. 

Summary:

Microsoft SQL Server 2008 T-SQL Fundamentals is not specific to SQL 2008. The emphasis is on fundamentals & 99.9% of the book is just as applicable to SQL 2005. Possibly 75% would also be valuable to those on SQL 2000. So if you want a strong understanding of TSQL & someone to point out where most people come unstuck. This book is for you.

 

Recommended Audience: Everybody. Especially C#, VB, Java Programmers & anybody writing code that talks to a SQL Server database.
I’m forever rewriting a ton of poorly written TSQL. It seems most people uncomfortable with anything but the most simple of joins. Beyond that they resort to Temp Tables, Cursors & chaos. If that is you, then this book is for you. It is only 380 pages so not a large tomb to wade thru.

Disclaimer: It is unlikely I will post a really bad review. Why? Am I biased? Nope. Am I being paid? I wish. Am I concerned about offending the authors? Nope … Anyone who knows me will tell you I’m not backward in “sharing my opinion” about both good & bad. Well? I kill time in book stores flicking thru titles, I have a huge reading backlog & so only buy books that look compelling. So, I can only think of 2 really crappy books I have. Both I was given. Yes if I ever get thru all the good books I may review those.

Detail:

The book is well structured, with a good section (Ch 1) on how the Optimiser uses the different parts of the query (FROM, WHERE, ORDER BY, SELECT etc). A lack of understanding of this is a major reason many struggle with TSQL, they don’t even realise it is important.

Chapter 2 looks at the functions & extensions handy for a single table query. (Way more than SELECT * FROM …). Chapters 3 & 4 builds on this with an easy to read yet comprehensive coverage of Joins; Inner, Outer, Cross, Composite, How NULLs can trip you up. But then builds with the harder stuff; Subqueries, Correlated subqueries. Again, this is not syntax, but solid examples of pitfalls, common errors & recommendations.

Chapter 5 covers Table Expressions; Derived Tables, Common Table Expressions, Inline Table-Valued Functions & the Apply Operator. I use this syntax all the time to easily rewrite code to remove the dependency on Temp Table’s. This reduces the need for Store Procedure recompiles, which reduces the load on the CPU & Proc Cache buffer pages, which improves performance.

Chapter 6:  Set based operations UNION, INTERSECT & EXCEPT. Again a way better idea than “INSERT … INTO #Temp …” & then “UPDATE #Temp” followed by “SELECT DISTINCT #Temp…”. What you want is the UNION Statement.

Chapter 7: OLAP style queries: Pivot, Unpivot, Grouping Sets. Sometimes handy to know but you can skim this. If you use these a lot you may wish to consider installing Analysis Server.

Chapter 8: Is rather large coverage on Data Modification; Insert, Update Delete, Merge, Output Clause. There is lots to know here. If you ever Insert a Row & then select it again to see its IDENTITY value or Defaults, you need to read this chapter. There is a better way to code that will reduce your overhead by 50%.

Chapter 9: Transactions, Locks & Blocking. Not a huge coverage but enough to get you started on Deadlocks & NOLOCKS. If troubleshooting Locks & Waitstates is your issue, this book is not enough.  The SQL Internals &/or SQLCAT Whitepapers would be better for you. I’ll cover that in a future post.

Chapter 10: A plethora of tips on executing dynamic TSQL. Batches, While, IF, Cursors, EXEC, sp_executeSQL, Error Handling. 

Note:

Itzik Ben-Gan & Joe Celko are my favourite authors. Both for the same reason. They are real thought leaders in the SQL Language. Both look at solving relational problems from a “Pure Mathematics – SET Theory” perspective & I believe this sets them apart in the innovation they are able to bring to our industry.

This book is not a showcase for all Itzik’s best “elegant solutions to tricky problems” his other books do that. This is about fundamentals, It is short, sweet & if you knew half of what was in this book your applications would run much faster with much less code.

If you are too lazy or too busy to walk to a book store to following are links to Online Retailers.

Australians:

• MS Press Site (note they Price match Amazon & Tech-Ed attendees can still get 40% off) 
• Amazon Price match Promise

Rest of World:

• Amazon
• Barnes & Noble

Please tell me if you would find this type of post useful. Particularly the type of audience the book is best suited to.
I’ve a huge backlog of innovation / things I could post & what to know what you value most.

Enjoy Dave.

TSQL Tips: The correct way to write your Scripts – ALTER PROC, don’t DROP & CREATE PROC

SQL Management Studio has a very nice Scripting feature that generates scripts to DROP & CREATE your stored procedures, tables & other objects. This is handy, but not good for production systems. Most people use it, … to their peril. This post highlights the potential issues & shows a much safer alternative.

The Problem(s):

Issue 1:
If you are trying to patch the same database on multiple servers, like a branch deployment OR you are a software vendor & you release a new version of your code, you might face the problem that your customers may be running different versions. 
      “Version 2” customers will need the modified version of your stored procedures eg: an ALTER PROC statement.
      “Version 1” Customers didn’t have that stored procedure so they will need a “CREATE PROC” Statement.
Typically the solution to this is to see if the Stored Proc exists & Drop it if it does & then always Create the new one. <see code below>. But that approach often causes other problems (see issue 2). 

-- Bad Practice
IF  OBJECT_ID(N'dbo.p_MyProc',N'P') IS NOT NULL
    DROP PROCEDURE dbo.p_MyProc
GO

CREATE PROCEDURE dbo.p_MyProc ...

Issue 2:
Database Objects have security & other objects related to them. When you DROP & then CREATE a stored procedure, you lose all the security permissions associated with it. With a table it is even worse, you are also dropping any Indexes, Triggers, possibly constraints that were added outside of the table creation statement, typically foreign keys. Not to mention any data in the table.

 

The Solution:

While talking to Tobias Ternstrom, a Program Manager in the SQL Engineering team, he suggested the following. It is so simple & elegant I had to share it with you & suggest it becomes your new “Best Practise”.

IF OBJECT_ID(N'dbo.p_MyProc',N'P') IS NULL -- Doesn’t exist
    EXEC('CREATE PROC dbo.p_MyProc AS SET NOCOUNT ON;') -- Create empty stub.
GO
ALTER PROC dbo.p_MyProc -- Always alter 
AS
…

Why do I like it?

1. You don’t need to maintain 2 identical scripts, one to create the proc & the other to alter it.
2. It overcomes the restriction that “CREATE PROC” must be the 1st & only statement in a batch.

Enjoy
     Dave

Using TSQL Variables: Subtle BUG when assigning values to Variables via a Query

I’m unsure if this is a BUG or “By Design” but for me it is unexpected & likely to cause your application to hang if you aren’t aware of it. So one more article in my “TSQL Best Practise” or “Good TSQL Coding Standards” posts.

Summary:

If you assign a value to a variable using a SELECT statement, the variable is not set to NULL if no rows are returned, instead it keeps the existing value. ie: This type of code is an accident waiting for somewhere to happen.

SELECT @Test = RetVal FROM VarSource WHERE Lookup = 'Exists1'

Where I saw it recently was in the middle of a WHILE loop. Simply put they were recursively walking up a hierarchical tree, looking for the parent. eg: SET @Key = ManagerID … WHERE EmpID = @Key. When it found a manager with appropriate level of signing authority they set the @key = 0 which terminated the loop. Unfortunately when a mid-level manager reported to an overseas boss (not in the same legal entity so not in the DB), instead of setting ManagerID to zero they set it to his EmployeeID. Thus a change in data caused infinite recursion. As their loop code didn’t have any MAXRecursion checks & they hadn’t written this code using Common Table Expression (CTE), it crashed the app. Another tip would be to connect with a query timeout less than infinite & always set the Workstation & Application properties in the Connection, to aid with troubleshooting.

Details:

There are 2 reasons why the above is bad practice.

1. The query may return multiple rows & you’ve not indicated what to do with the extra rows.
2. The query may return zero rows & you are unlikely to realise you are working with the previous value.

So unless you can guarantee, via unique constraints etc that you will get precisely 1 row to match your query, its better to code this differently.

I suggest the following alternatives.

Alternative 0: Always reset the variable to a known state before you use it.

SET @Test = NULL
SELECT @Test = RetVal FROM VarSource WHERE Lookup = 'NotExists1'
If @Test IS NOT NULL BEGIN ....

This may work but it sucks, choose a better, more readable alternative. 

Alternative 1: Wrap the assignment in a SELECT.

SELECT @Test = ( SELECT RetVal FROM VarSource WHERE Lookup = 'NotExists1')

This will protect you against the ZERO Rows returned by ensuring that the @Test IS NULL & not left at some previous value. It is also easy to modify your existing code & unlikely to require immense testing. But leaves you exposed to Multiple results.

 

Alternative 2: Wrap the assignment in a SELECT TOP(1).

SELECT @Test = (SELECT TOP(1) RetVal FROM VarSource WHERE Lookup = 'NotExists1')

Same as above but now explicit in what to do if multiple rows returned.

 

Alternative 3: Use an aggregate function – MIN, MAX, AVG

SELECT @Test = MAX(RetVal) FROM VarSource WHERE Lookup = 'NotExists1'

This is explicit about what to do with multiple rows, & returns NULL where no rows returned. But may be a pain if your syntax was also assigning other variables & you needed them to be non-aggregate values. Clearly MAX() & MIN() would be more predictable than AVG()

 

Alternative 4: Don’t use a variable at all. Use a Join

In many cases I see code that sets a variable in one query (like @StoreID) only to use it as a Key value in another.

SELECT @StoreID = StoreID FROM dbo.Stores WHERE Name = 'City'

SELECT * FROM dbo.Employees WHERE StoreID = @StoreID

could be rewritten as

SELECT * 
FROM dbo.Employees AS E 
JOIN dbo.Stores    AS S ON S.StoreID = E.StoreID
WHERE S.Name = 'City'

or

SELECT * FROM Employees 
WHERE StoreID IN (SELECT StoreID 
                  FROM Stores WHERE Name = 'City')

=========================================================
Another Common use passing a value from a calculation. 

SELECT @TotalEmps = COUNT(ID) FROM dbo.Employees

SELECT 'PctEmp' = Headcount / @TotalEmps
FROM dbo.Stores

which could be rewritten as

SELECT 'PctEmp' = Headcount / ( SELECT COUNT(ID) 
                                FROM dbo.Employees )
FROM Stores

OK. I realise that that would work fine & is not affected by the bug mentioned above. But I’m sure some people could have lookup values or similar that would break. I find it challenging to think up illogical ways to write TSQL, for that I look for inspiration in TSQL written by others. Just as professional C Programmers might look at the OO design of my C code & use it for crappy code in their refactoring blogs. I find them to be a constant source of “Wow I didn’t even think that would work, I’m not surprised perf sucks.”

If I hear you thinking “Yer but my queries are way more complex & the increased complexity of even more joins would kill me. I suggest you read up on the “WITH” statement. It allows you break up your joins into easy to understand parts & is way more efficient than creating TEMP tables. Not to be confused with the “WITH” clause that lets you put query hints in your joins (like the dreaded NOLOCK hint )

-- ================================================================

If you’d like to repro this, Sample Code included below.

-- ==============================
-- Purpose: Demonstrate BUG/Function of 
--          Variable not changed / Set to NULL when no rows returned.
-- AUTHOR:  David Lean
-- Date:    21 Aug 2009
-- ==============================

USE tempdb
GO

CREATE TABLE dbo.VarSource (
     Lookup VARCHAR(20) NOT NULL UNIQUE
    ,RetVal INT NOT NULL  
)
GO
-- 2005 Compatible syntax
INSERT dbo.VarSource VALUES('Exists1', 1);
INSERT dbo.VarSource VALUES('Exists2', 2);
GO

DECLARE @Test INT

-- Ensure it has a value
SELECT @Test = 99

-- Test1: See that the row is changed when a row is returned.
SELECT 'Value Before Test 1' = @Test
SET @Test = NULL
SELECT @Test = RetVal FROM dbo.VarSource WHERE Lookup = 'Exists1'
SELECT 'Value After Test 1 worked correctly' = @Test

-- Test2: BUG? See that the row keeps previous vaule when No rows returned.
SELECT 'Value Before Test 2' = @Test
SELECT @Test = RetVal FROM dbo.VarSource WHERE Lookup = 'NotExists1'
SELECT 'Value After Test 2 – BUG It did not change to NULL' = @Test

-- Test3: See that the row is changed when No rows returned.
SELECT 'Value Before Test 3' = @Test
SELECT @Test = RetVal FROM dbo.VarSource WHERE Lookup = 'Exists2'
SELECT 'Value After Test 3 worked correctly' = @Test

-- Test4: SELECT Workaround See that the row is changed when No rows returned.
SELECT 'Value Before Test 4' = @Test
SELECT @Test = ( SELECT RetVal FROM dbo.VarSource WHERE Lookup = 'NotExists1')
SELECT 'Value After Test 4 = (SELECT ...)' = @Test

-- Test5: Aggregate Workaround See that the row is changed when No rows returned.
SET @Test = 99;
SELECT 'Value Before Test 5' = @Test
SELECT @Test = MAX(RetVal) FROM dbo.VarSource WHERE Lookup = 'NotExists1'
SELECT 'Value After Test 5, = MAX()' = @Test

-- Test6: TOP 1 Workaround See that the row is changed when No rows returned.
SET @Test = 99;
SELECT 'Value Before Test 6' = @Test
SELECT @Test = (SELECT TOP(1) RetVal FROM dbo.VarSource WHERE Lookup = 'NotExists1')
SELECT 'Value After Test 6, = (SELECT TOP(1)...)' = @Test

/*
-- NOTE: Setting Variables like this is a Poor Practice. 
-- If the query returns multiple rows only 1 will be used, the others ignored. 
-- It is better to either :-
a) be explicit on which row to use with aggregate: SELECT @Test = SELECT MAX(RetVal)
b) JOIN with the Lookup Table 
        WHERE key IN ( SELECT RetVal FROM VarSource WHERE Lookup = 'NotExists1')
*/
-- ================================================================

I hope you find this useful. As always I’m interested in your thoughts, comments &/or disagreement.

David Lean

More FREE training for Developers & DBA’s

I tend to forget about this little gem, so this post more of a reminder to me, that I hope you will find handy too.

Ramp Up: eLearning Site

Official News

I’m happy to announce that we’ve just launched 3 brand new learning tracks on Ramp Up* (www.MyRampUp.com):

1. Web Development with ASP.NET
2. Move from ASP to ASP.NET
3. Move from PHP to ASP.NET

Official Description

Ramp Up (www.MyRampUp.com) is a free online learning program for developers. We’ve just launched three ASP.NET tracks: “Web Development with ASP.NET”, “Move from ASP to ASP.NET”, and “Move from PHP to ASP.NET”. These tracks, along with the other currently offered ones (eg, Windows Mobile 6, SharePoint for Developers, Visual Studio 2008), teaches the important skills in a guided path, making the learning process easier and more efficient. The easy-to-access content (provided by subject-matter gurus) is specifically tailored for the Ramp Up program, and offered in a variety of forms (article, v-lab, codecast and slidecast). Check them out now at www.MyRampUp.com, and see how Ramp Up can help you become more employable by learning important and marketable skills.

My 2 cents thoughts

MyRampUp is a page off the MSDN.Microsoft site. It takes you to the Microsoft Learning site which is an aggregate site for all kinds of learning styles. It lets you filter by topic & lists; Textbooks, e-Learning & other courses. If you are looking for something online & free, click over on the Right Margin “Free Products”. You do need to register, it cost you nothing, & it lets you create your own training plan, which you add courses that take your fancy & tick them off when you’ve completed them.

Other related sites definitely worth a look

• MSDN Virtual Labs
• Microsoft Virtual Labs Home Page
• Microsoft SQL Server 2008 Virtual Labs
• TechNet Virtual Labs

Enjoy
    Dave

Thought for the day: Some people make it happen, Some people watch it happen, & some people ask “What happened?”

News: Look at the CTP3 of our upcoming Distributed Cache technology - Velocity

If you develop high scale, scale out/distributed apps, check out

Microsoft Project Code Named “Velocity” Community Technology Preview 3 (CTP3) Released to Web on 7 April 09.

"Velocity" is a distributed in-memory application cache platform for developing scalable, high-performance applications. "Velocity" can be used to cache any common language runtime (CLR) object and provides access through simple APIs. The key aspects of "Velocity" are distributed cache performance, scalability, and availability.

This release contains several security and performance enhancements, as well as the new cache notifications feature which allows clients to be notified when changes are made at either the key, region, or cache level. In addition, it also includes numerous refactorings to its API, making it more conformant to the .NET Framework guidelines as well as easier to consume. “Velocity” is currently slated for release in the middle of this year and will represent an integral piece of our overall web/data story.
When complete it will be part of the .NET platform, ie: Free

 

Get SQL Server 2008 for FREE: FREE Database, FREE Reporting Solution, FREE Spatial

If you are a Student, Teacher or anyone on a budget, don’t feel pushed to use Open Source databases like MySQL just because they have an entry level offering for free. You may be one of many who expressed concern for its quality & design which suffered when SUN purchased MySQL. Who knows what will happen now that Oracle are trying to buy SUN. Take the time to compare it feature for feature to SQL Server you will find it is missing a lot.

Microsoft makes a lot of its software available for free to anyone. In addition, many groups like Primary, High & Unit Students, Teachers, Start-Up Business’s & Microsoft technology partners can qualify for even more free stuff (for some groups we give them every product we produce). See my blog post How do I get FREE stuff from Microsoft?

 

So, it is possible for anyone to get the SQL Server 2008 database for FREE. This includes the Spatial capability, some Replication, Integration, Auditing & Management capability.
It is also possible for anyone to get SQL 2008’s Reporting Services for FREE too. (either as part of SQL Express, or using the Report Viewer Control that ships with Visual Studio). 

 

You can do this by downloading any one of the SQL 2008 Express packaging below. For more details on what Express has, see SQL Server 2008 Express
If you have SQL Workgroup thru to SQL Enterprise don’t use the Express Service pack 1 (below). See this post SQL Server 2008 Service Pack 1 instead.

As part of the recent April 2009 releases mentioned in my blog: SQL Server 2008: April 09 Updates – Handy Links SQL Server 2008 Express was also updated.

Where do I get it? (Available FREE to Everyone)

SQL Server 2008 Express, is a very powerful, full featured database engine. It is compiled from the same source code tree as the Enterprise edition. Any applications that you develop for it will also run exactly the same on any of the other editions; Workgroup, Standard, Enterprise, Developer or Web. ie: “bug for bug compatibility”

The Express edition has been limited in some areas. To compare it to the more fully featured editions see SQL 2008 Compare Edition Features (NB: Remember to expand the “Plus Signs”). But in summary:  It will only use 1 physical processor (could be a quad proc, so not a big limitation). It’s databases are limited to 4Gb. This is actually a lot of data, especially when you consider you can have 1,000’s of databases per instance & 16 Instances per server. So 4TB * 16 Instances. But clearly spraying ove so many database; your CPU, backup strategies &/or distributed schema would become an issue.
If you find that constraining, the upgrade path is to detach the database & attach it to SQL 2008 Workgroup. It costs ~$1,000, limited to 2 Physical CPU’s (Quad Core = 8 CPU’s) & 50GB databases. Or just go straight to Standard or Enterprise editions.

7 April 09 Microsoft SQL Server 2008 Express Edition Service Pack 1

Update to any release of SQL 2008 Express

20 Feb 09  Microsoft SQL Server 2008 Management Studio Express

This is a definite download if you aren’t able to get your hands on the Management tools that ship with SQL Developer/Enterprise/Standard Editions.
 

22 Feb 08 Microsoft SQL Server 2008 Express with Advanced Services

SQL Server Express that includes a graphical management tool, and powerful features for reporting (Reporting Services) and advanced text-based searches.

8 Feb 09  Microsoft SQL Server 2008 Express with Tools

Free SQL Server Express that includes graphical management tools. Same database, just smaller install package, with no Reporting to install.

---

You can also get SQL Server Compact v3.5 (aka SQL Mobile Edition) this is highly optimised for a single user, with a focus on minimising its memory & disk footprint. Great for distributed solutions. The TSQL you write for it, also works on the other SQL 2008 editions. But be aware it is a subset of the language features of the SQL 2008 Editions.

6 Aug 08 Microsoft SQL Server Compact 3.5 Service Pack 1 for Windows Mobile

Service Pack for SQL on Mobile Phones, PDA’s & Laptops (if you want)

How do I get FREE Software from Microsoft?

It is common to hear students & small start-up businesses state “I use Open Source because it is free” or they download pirate Microsoft from really dodgy, spyware prone sites to get it free.

The irony is that many of these people qualify to get the software for FREE or at least heavily discounted prices but they don’t know to ask.

The following post highlights a few of the ways this is possible.

Important: Offer expires 30 June 2009;
Be a qualified “Microsoft Certified Professional” If you are a student, Microsoft will pay the cost of your MS Certification exams. See “Boost your resume” below.

 

Everyone

• A nice but incomplete list can be found here => Free & Trial versions It points you to the Express Editions for Visual Studio, SQL Server & other products.
• I noticed it is missing Virtual PC 2007.
• Recently we made Sharepoint Designer 2007 available for free. Remember there are 2 versions of Sharepoint. Windows Sharepoint Services (which is included with every Windows Server) & Sharepoint Portal Server (which you buy with much more enterprise features) So this still may be useful for you, it is the successor to FrontPage 2003
• More Details on Free SQL Server in my blog post  Get SQL Server 2008 for FREE
• Then there is a long list of add-ons & free tools available on MSDN & Codeplex. For example the  Chart Controls on MSDN & my favourites on Codeplex SQL Spatial Tools, Microsoft SQL Server Community Projects & Samples

Online Education for Developers

Channel 9

Online Education for Infrastructure Folks (IT Professionals)

 TechNet Edge

 

For Students & Teachers - DreamSpark

DreamSpark is a program set up by Microsoft to provide students with software design and development tools at no charge. Click here for full details.

Note to Teachers: This is professional grade s/w, your students will use when they leave school. No need to suffer trying to teach with really old or shareware s/w. Nor do you need to try to teach with desktop s/w that works ok on the Mac but your kids don’t have it at home. The wizards make it easy to learn. It is brilliant for teaching IT & relevant to nearly all high school IT curriculum. (I know because my kids attend High School & study IT)

 

What sort of s/w can I get for free?

• Visual Studio 2008 Professional Edition (Windows & Web, VB, C#)
• Microsoft Expression Studio 2  (includes great graphic design tool, also powerful web design tool)
• SQL Server 2008 Developer Edition (this is everything that ships in the Enterprise Edition)
• Windows Server 2008 Standard
• Robotics Developer Studio 2008
• Xna Game Studio 3.0
• + more

Is there a catch?
YES. Your Education institution has to validate that you are a student. In Australia, I’m told all Universities are connected & you can validate quickly via the Web site.

For School students. The process is made more complex in order to ensure the privacy of the children. The school needs to ask Microsoft for a set of keys. The school distributes the keys to its students. The student can then use the key to anonymously download any of the s/w they are interested in.
If you are a High/Primary School Student please ask your teacher for a code. If they don’t know, point them at the link below.

If you are a Primary / High School Teacher & want to register your school, Click here

 

---

Boost your resume, get a recognised Microsoft Certification FOR FREE.

Click here => Microsoft will pay for your Technical Specialist exams for any student that registers & take the exam(s) before 30th June 2009.

---

Australian State Education Departments

What about the other products that aren’t S/W design & Development?

Most of the Australian Dept’s of Education have signed agreements with Microsoft to provide Server & Desktop tools to all the schools.
My perception is that this is broken. The Dept appears to make the s/w available to the schools who request it. The NSW Teachers that I’ve talked to don’t know to ask for the s/w. So they don’t get it & have to make do, when they could have great solutions. As a parent, this frustrates me. I don’t know how well this process is working in the other states.

 

Online Education for Students

• For Primary & High School check out Kid's Corner. More details in my blog post Teach your kids to program
• For Senior High & Tertiary Students (& really smart younger kids) Channel 8 is for you.

 

 

For Start-up Businesses – BizSpark Program

This has to be the sweetest deal I’ve seen … ever. Get Hundreds of Thousands of dollars worth of s/w. Get Access to people who can help sell your product, mentor you &/or assist with business advice. All just for signing up..

If you :-

• are starting or started your business in the past 3 years,
• actively engaged in development of a software-based product or service
• generate less than us$1Million

You might qualify to be included in the BizSpark program. BizSpark Overview Microsoft provides nearly all of our products & a lot of support (technical & business) to help new companies get over the challenges of those early years getting a product to market. How does it work?

To see if you qualify click Do I qualify?

For a list of s/w. click What s/w do I get? 
Note that in addition to the huge list of high end server products, including Team Suite versions of the dev tools, you also get an MSDN Premium subscription. This supplies you with just about every product Microsoft makes, excluding games & H/W.

To get started, click Web based Signup Agreement

For new IT Related businesses – Empower for ISV’s

Click here => Microsoft Empower for ISVs is an initiative for independent software vendors creating new software solutions using Microsoft® technology. Membership gives you access to essential development tools, resources, and support that will help you accelerate your product development and get your application to market faster.  This program costs ~ us$375 per year.

Do I qualify?

Requirements for Microsoft Empower for ISVs
To take advantage of the valuable Empower initiative, your software development company must commit to developing one packaged and resalable custom application that supports at least one of the following Microsoft technologies:

• Windows Vista® operating system
• 2007 Microsoft Office system
• Windows Server® 2008 or Windows Server 2003
• Microsoft SQL Server® 2008 or SQL Server 2005
• Microsoft Dynamics™ business management solutions
• Windows Mobile software

Does Your Software Development Company Qualify?
The great benefits of Microsoft Empower for ISVs are available to any micro, small, or medium independent software development company in an intense building phase.

What Do I get?

MSDN Premium Subscription with 5 Dev & Testing Licenses

The full range of Microsoft integrated server software; all Microsoft operating systems; Microsoft productivity applications including: Microsoft Office Professional 2007, Microsoft Office Project Professional 2007, Microsoft Office Visio® 2007 drawing and diagramming software, and Microsoft MapPoint® 2009 business mapping software.

Up to 5 internal-use software licenses for:

• Microsoft Visual Studio® 2008 Professional Edition
• Windows Vista® Business Operating System
• 2007 Microsoft Office System or Microsoft Office Professional 2007

One software license and up to 5 CALs for:

• Windows Server 2008 Standard or Enterprise
• Exchange Server 2007 Standard or Enterprise Edition
• SQL Server 2008 Standard or Enterprise Edition
• Office SharePoint Server 2007 Standard or Enterprise CAL

Technical Advisory Services
10 hrs of phone-based professional consulting. Select from a broad range of services, including:

• Software development advice
• Best-practice recommendations
• Code samples
• Limited technology-, architecture-, or application-
  design review

Innovate On
Training, code samples, logo certification, and go-to-market opportunities to help you complete your development process and market your solution effectively.

For Existing IT Businesses – Certified Partner Program

Who is it for?

The Microsoft Partner Program is designed for all partners who develop and market solutions based on Microsoft platforms, provide consulting or technical services on Microsoft systems, or recommend Microsoft technology purchases. Click here for details.

I will not repeat the web site here, but in summary.

There are 3 levels; Registered, Certified & Gold Certified.

Cost to join: Registered costs nothing, Certified & Gold Certified cost $3465 per site if you qualify.

This gives you access to heavily subsidised s/w. In the case of Gold Certified, Hundreds of Thousands of dollars in s/w you can use for internal staff, development, demos for your sales / consulting people etc.

 

This is not a complete list. But it is now 2am, I know I’ve missed something big but can’t recall what it is.

I hope this helps you to better tap into the vast array of free stuff. Thanks for reading this far.

Dave

SQL Server 2008: April 09 Updates – Handy links

Scanning the Microsoft sites it might not be obvious that April 09 was a huge month for new releases from the SQL Team. Given that it is sometimes difficult to find everything in one place I thought I’d make it easier for you by creating this index.

Beyond the links there is nothing in this post particularly insightful.

As mentioned in my prior news post SQL Server 2008 Service Pack 1 was released. In addition, lots of related stuff was updated.

Latest updates to the Interoperability & Extensions to SQL Server

 

7 April 09 Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007, April 2009

This is brilliant technology that makes Data Mining accessible to people who haven’t got a clue about Data Mining. Just need Office Excel 2007 & a copy of SQL 2008 Analysis Server, with the Data Mining Sessions parameter turned on & all your business users can mine the data they pulled into their Excel Spreadsheet. This delivers two add-ins for Microsoft Office Excel 2007 (Table Analysis Tools and Data Mining Client) and one add-in for Microsoft Office Visio 2007 (Data Mining Templates).

There is also a version that works for SQL 2005 & Office 2007. While not quite as full featured it is still very nifty. It is contained in the Feature Pack for SQL Server 2005 - February 2007 There is also a version for Office 2003 & SQL 2005, but I can’t find a link to it any more, so I assume we’ve pulled it.

7 April 09 Microsoft SQL Server 2008 Reporting Services Report Builder 2.0, April 2009

An intuitive report authoring environment for business and power users to create SQL Reporting Services Reports. This update now supports Click-Once Install.

7 April 09 Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies, April 2009

Display your reports natively inside SharePoint. Sure you can put SQL Reports into SharePoint without this add-in. But it provides much nicer integration, especially letting you control security using the SharePoint’s security on the page.

7 April 09 Microsoft SQL Server 2008 Upgrade Advisor, April 2009

When you install SQL 2008 on a machine which has previous editions of SQL installed, the SQL 08 setup program includes the Upgrade Advisor & will run this Upgrade check & warn you of any potential issues with your schema. Well…, it runs this if you are attempting to upgrade the existing instance. If you are just creating an additional SQL 2008 instance next to you previous versions there is nothing to check.

It is a good idea to download this first & scan your databases prior to attempting to setup over the top. At the same time you could use profiler to capture a representative sample of your applications queries & scan them too. This will give you a much more thorough check of your system & reduce the potential issues you may have upgrading your production systems.

Note: If you move your SQL 2000/2005 database to SQL 2008 & run it in compatibility mode. The Upgrade Advisor will not be able to scan it & you lose the ability to get any insight this tool may have provided you.

 

7 April 09 Microsoft SQL Server 2008 Feature Pack, April 2009

The Feature pack includes a large variety of other interoperability & other add-ins. Especially popular is the Microsoft SQL Server 2005 Driver for PHP This driver also works for SQL 2008. But note: Unlike the JDBC driver which has UNIX version, the PHP Driver only runs on Windows platforms.
Tip:  The feature pack points to the older JDBC Driver v1.2, use the JDBC v2.0 below.

Feature Pack for SQL Server 2005 - February 2007  perhaps you may find it handy, this is the equivalent for your SQL 2005 servers

 

3 April 09 Microsoft SQL Server JDBC Driver 2.0

a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in Java Platform, Enterprise Edition 5.

30 Jan 09 Microsoft SQL Server 2008 Books Online - Jan 2009

Always handy to be using the latest release of the docs, They constantly are adding more examples & better explanations.

I trust this saves you some time,

Dave