May 2006 Archives

Day/Time: Wednesday, June 14 3:45 PM - 5:00 PM Room: 157 ABC
Speaker(s): William Vaughn
SQL Express is the version of SQL Server 2005 designed to replace MSDE. In some scenarios, SQL Express is a viable replacement for the JET/Access database engine - it's safer and more reliable. Because SQL Express does not behave like JET in most respects, it can be a challenge for JET developers to understand how to control SQL Express and leverage its power from their applications. This talk discusses the differences in SQL Express, MSDE, JET and the other SQL Server Editions - including the just-announced SQL Server Everywhere edition. See how SQL Server Everywhere (which builds on the proven codebase of SQL Mobile) fills a need for a lightweight DBMS where SQL Express might be overkill. The session includes an overview of the new SQL Server Management Studio Express toolset and code used to manage SQL Express (or any instance of SQL Server) including discovery, starting, stopping and pausing instances. We also demonstrate how Visual Studio leverages SQL Server Express functionality into the IDE and permits developers to create, configure and deploy SQL Server Express databases with their applications.
Track(s): Database Development and Administration
Session Type(s): Breakout Session
Session Level(s): 200

Yet another question was posted in the public ADO.NET newsgroup and I decided to use this forum to answer this fairly common question: Are Stored Procedures (SPs) faster than ad-hoc queries? One misconception is that SPs are compiled into some binary-like code and simply loaded into memory on each invocation--kinda like a DLL. They aren't. In order to take advantage of the current data "statistics" (how the information is distributed in the tables being referenced), each query is recompiled (to some extent) at runtime.

No, not every statement is recompiled on each use. The mechanism works like this (somewhat simplified): When you execute a query (parameterized or not) it's syntax checked, compiled, placed into the common RAM cache and executed. The compiled query sits in the RAM cache until space is needed for something else. When you execute the same query (or one that's pretty close--perhaps with a different parameter value) there is no need to recompile the query--the cached query is simply rexecuted with the new values.

A stored procedure works the same way but the server can recognize it more quickly and knows if it has its query plan in cache--it does not have to be checked for correct syntax--that was done when it was first created. So, yes, there is a little performance benefit whenrunning a SP when compared to an ad-hoc query. While there is the additional (minor) benefit of the number of bytes sent to the system to execute the SP is far lower (just the string that has the name and parameter values)--this benefit is not that great.

As I have talked about many times here, in newsgroups and in my sessions, books and articles, IMHO JET/Access is not suitable in a business application. I've detailed the problems that include its physical IO over the network (which makes it prone to data loss), its inability to be backed up while any application is still connected, its need to be periodically compressed, it's lack of scalability (which means you have to start over when the customer outgrows it), it's (very) limited capacity and the number of users it can support. It should never be used as a web host unless you don't mind terrible performance--that's because it's a "client" engine--not a service designed to support parallel operations. One of the most serious problems is its inability to encrypt data in place and its fragile security system. This makes it "officially" unsuitable to manage HIPA-compliant data. The fact that you access JET over a very problematic MDAC stack is and always has been another problem. Each time the stack is changed, your application might or might not work. Jet is also accessed over a COM-based OLE DB interface--in unmanaged code.

Microsoft’s Orcas development team asked for some feedback on Visual Studio in a recent SDR. Since my opinions are not covered by the NDA, I’m free to share these ideas with the public. I also submitted a brief summary of this list to my readers at Processor Magazine where I encouraged them to provide additional suggestions and feedback.

The next release of Visual Studio .NET (that Microsoft has dubbed “Orcas”) is due out sometime in late 2007. Many folks tell me that they’re getting more and more push-back on this runaway train given all of the changes that keep rolling out of Microsoft’s new and improved (AKA “you should do things our way”) department. The suggestions shown here are those that help developers work with existing architectures—making them more productive by leveraging their existing skill sets and training.

I strongly encourage all veterans to visit the announcement page issued by the Veteran’s Administration that explains how a VA data analyst lost 26.5 million veteran records to a common thief. What the article does not explain is why the data was not encrypted. Today, it's common practice to ensure that data is encrypted on any system that can be stolen (a laptop or an external or removable drive).

Jonathan asked me to post this... I plan to go.

Hi All, we are putting together a gathering of ADO.NET, and data connectivity lumaries for an informal gathering during TechEd 2006. This will be an opportunity for everyone who lives and breaths data connectivity, ADO.NET and who is looking to get a first hand feel of everything on the horizon for ADO.NET, LINQ and .NET and meet some of the key players involved.
The date ? Meet up is on June 13, 2006
The time ? Between 4pm and 6.30pm
The where? Seaport Hotel in Boston.
If you think you are the kind of person who would be interested in this, either comment below, or shoot me an email! Of course there will be food and drink for all, so sign up fast as space is limited!
-Jonathan Bruce .NET & XQuery Program Manager
DataDirect Technologies
Jonathan Bruce [jonathan.m.bruce@gmail.com]







Using Project-based Databases






Proliferating Project-based Databases


Ok, so you heard that it’s possible to use Visual Studio to
create a new SQL Server database and add it to your project. As a matter of
fact, you can add any number of SQL Server databases to your project and Visual
Studio will be happy to comply. How happy you are with debug performance might
be another matter but I’m getting ahead of myself. This practice seems to be
important enough for Microsoft to include the functionality in the IDE so if
this is something you do or plan to use as an easy database deployment
technique read on—especially if you’re targeting Smart Client/Windows Forms
applications. I understand how this can be appealing. All you have to do is add
the database file to your project and Visual Studio automatically includes it
in your deployment binaries.

Ok, so you heard that it’s possible to use Visual Studio to create a new SQL Server database and add it to your project. As a matter of fact, you can add any number of SQL Server databases to your project and Visual Studio will be happy to comply. How happy you are with debug performance might be another matter but I’m getting ahead of myself. This practice seems to be important enough for Microsoft to include the functionality in the IDE so if
this is something you do or plan to use as an easy database deployment technique read on—especially if you’re targeting Smart Client/Windows Forms applications. I understand how this can be appealing. All you have to do is add the database file to your project and Visual Studio automatically includes it in your deployment binaries.



The latest edition in the Hitchhiker’s Guide series is finally done—at least my part. The book is in edit (and has been for awhile). This means we can expect to see it on shelves in about four months (hopefully less). Clearly, this is not a good way to get current information into your hands. As it is, the book is over 800 (Microsoft Office Word) pages. That means it’ll push 900 pages on the shelf if we keep all of the content. Yes, it will also include a DVD and a slew of examples, sample databases and possibly some Camtasia™ videos (if I find the time). I plan to post the outline here before long.

I’ll be speaking at TechEd 2006 (US) this year in Boston. Sure, I’ll be doing a session—it’s on SQL Server Express Edition that’ll make it clear where it fits and what it replaces. And of course, I’ll be helping those addicted to JET find ways to kick the habit and move to better alternatives—without a 12-step program. I’ll also be talking (briefly) about the newly rebranded SQL Server Everywhere edition. You once knew this edition as SQL Server Mobile and SQL Server CE before that.

I spent the last couple of days in the Visual Basic.NET SDR up on the Microsoft campus. It was an enlightening experience and while much of what we we were told is NDA stuff, I learned quite a bit about Microsoft’s approach to making VB.NET better. They are working hard to bring as many VB6 developers over to .NET as possible. Hopefully, this means VB.NET but I don’t think it makes a snit of difference whether they use VB.NET or C#. Sure, I think VB.NET has (and will continue to have) a lot more appeal to VB6 developers (and their managers) especially having seen some of the new features being planned for the “Orcas” release. However, if developers pay the price to transition to .NET, most of their battles will be won.

Not without a careful loook at the code... It does sound like @@Identity is being referenced (it should not be).
We are using SCOPE_IDENTITY and we are still getting these unexpected results. The scenario is as follows: Insert into Table A (which has an identity column) which fires a trigger adding a row to Table B which has an identity column. On subsequent inserts or updates the identity from Table B is used instead of the identity from Table A. This is happening in an application written in VB 6 using SQL Server 2000. Any suggestions?
Only low-level formats (rewriting the sectors) will completely wipe things out, as far as I know. Any other type of reformat is recoverable.
Neat. I'm looking forward to your book.

I read your last version that you handed to me at the MVP summit last year. You know so much, that it is impossible to stuff it in 800 pages. Hopefully I'll be as smart as you one day.

I was working on my daughter's (George's) laptop the other night and made two mistakes. First, I was working on hardware after 8PM--this is a mortal sin as far as I am concerned. Next, I thought her drive had two partitions. It had one. I formatted both partitions. I shouldn't have. Thankfully, I discovered a program “Recover My Files” which after a considerable length of time (over 24 hours) was able to “unformat” the NTFS partitions. I, frankly was very grateful but sorta shocked as I assumed (incorrectly) that dropping a partition, creating a new partition and doing a full format would wipe the data. It does not. Each and every file on the drive was recovered (at least it looks like they were).

(Update: No, the Recover My Files program did not actually recover the data--at least it has not done so at this point. While it does see the files, when saved the files are corrupted.)

Another little issue: IBM (whose service is highly rated by Consumer Reports) sent George a “Used but Serviceable” replacement hard drive. It lasted about 18 hours before it failed. They're sending another. Perhaps it will last long enough to build a backup... This assumes that I won't be so tired that I delete the backup before getting a chance to install it. I wonder what data the replacement drive contains? Hummm, should I peek?

bv

 

I've made a decision to stop adding new content to my new book “Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)”. I finally came to the realization that I can't keep up with Microsoft and their constant churn. No, this does not mean I won't be publishing the book--hardly. It means that I'm going to trim back to 800 or so pages and publish the new stuff as EBook. This more closely follows the pattern used by Microsoft. That is, ship a product and then ship updates for the next couple of years (before it's made obsolete). ;)

To this end, the book's manuscript is now in the hands of the editors and reviewers. I hope to get it on the shelves this summer. The new support web site is up www.hitchhikerguides.net but not really ready for comments quite yet. Don't be surprised if the colors and layout change from minute to minute--I'm still tuning.

Let me end on this final note. This book would have been a lot harder without the help of Peter Blackburn. While he did not write a lot of content this time, he has been there during the entire process (over two years) keeping me on track, keeping my systems functional and keeping my eye on the ball when it comes to security issues. Thanks bud.

 

Pages

Powered by Movable Type 4.21-en

About this Archive

This page is an archive of entries from May 2006 listed from newest to oldest.

April 2006 is the previous archive.

June 2006 is the next archive.

Find recent content on the main index or look in the archives to find all content.