|
July 2006
July 31, 2006
Strange error in SQL Server 2005 Management Studio
I think I have seen this behavior, but simply ignored it. Here is a post from Plip, who did not just ignore it, but actually blogged about it.
Posted by Douglas Reilly at 04:07 PM Permalink
|
July 28, 2006
Red Gate Discusses SQL Prompt
Red Gate recently acquired a program named PromptSQL and renamed it SQL Prompt and released a version 2 for free. It turns out that the code underlying SQL Prompt was not quite as robust as the Red Gate folks expected, and so since they had promised a version 2 out, they decided to release it for free, and then rebuild the code from the scratch and release a commercial version 3 that would require payment.
Turns out, this process kicked up a lot of dirt in the community.
Here is Simon Galbraith's response to the community.
In the interests of full disclosure, I have done a fair amount of writing work for Simple-Talk.com, the content Web site operated by Red Gate. That said, I do believe Simon's response to the issues brought up by community members is entirely reasonable.
What do you think?
Posted by Douglas Reilly at 02:12 PM Permalink
|
Statistics in SQL Server
SQL Server stores all sorts of statistics about how data is distributed within your database. Statistics are used by the query optimizer to choose the most efficient way to answer your questions.
Sometimes when you have a query that performs badly, the cause can be that your statistics are out of date or in some other way munged. Mike Gunderloy wrote an article on the subject.
Especially cool here is the discussion of an undocumented option that lets you increase the apparent number of rows and pages in your tables. Why might you wnat to do this? SQL Server's query analyzer works differently on small tables and large tables. Tricking SQL Server into thinking you have a huge table can allow you to see how SQL Server will handle a particular query.
It is interesting to see how SQL Server does things. For example, years ago, I had a query that had worked correctly for ages and it suddenly failed, returning data in an incorrect order. It turns out that my query had no explicit ORDER BY clause, and I was depending upon the way that SQL Server returns rows by default (the Primary Key was being used, even though I had not specifically requested it). When the table reached a certain size, it no longer returned rows in that order, and my application broke. This sort of failure is particularly annoying ("I did not change anything, and now my application is broken. Did anyone install any SQL Server updates?"). Of course, once I realized the problem, the solution was trivial, doing what I should have done in the first place!
Posted by Douglas Reilly at 10:18 AM Permalink
|
July 27, 2006
SQL Server Integration Service Web Site.
One of the biggest changes in SQL Server 2005 is the change from DTS to SQL Server Integration Services (SSIS).
There is now an entire site devoted to SQL Server Integration Services, here.
Posted by Douglas Reilly at 11:12 AM Permalink
|
July 25, 2006
New SQL Server 2005 Books Online
The latest SQL Server Books On-line is now available from Microsoft.
Come and get it!
Posted by Douglas Reilly at 10:39 AM Permalink
|
July 20, 2006
Live from Redmond!
Micorosft is having the product teams presnt talks thay are calling "Live from Redmond".
There are lots of talks, I have highlighted a couple that are of particular interest to database folks:
16-Aug 9:00 AM Pacific time
Smart Client: Offline Data Synchronization and Caching for Smart Clients
Steve Lasker
Click here to Register
A summary of this talk follows:
"One of the advantages of Smart Client development is supporting cached or fully disconnected operations. Built on the proven codebase of SQL Mobile, SQL Server Everywhere is a lightweight, in-proc database solution they can be easily deployed without struggling with the tradeoffs between JET, XML and SQL Express. Once you have a real database you need to think about different sync technologies. Steve covers the various architectures and sync options available today with SQL Server Everywhere including Merge Replication, Remote Data Access. We also cover the pros and cons of DataSet and SQL Server Query Notifications, as well as the future of sync for building Occasionally Connected Applications. Empower your users with Windows Client Applications that work where the user is, not where the network may be."
30-Aug 9:00 AM Pacific time
Visual Studio: Developing Local and Mobile Data Solutions with SQL Server Everywhere
Steve Lasker
Click here
A summary of this talk follows:
"Many developers assume wireless will be everywhere, enabling applications to communicate with centrally located servers. Any developer who's actually tried deploying broadly connected applications knows this is simply a dream of an alternate universe that only frustrates users with reality. Users want their applications to just work, and their data to be available, even if the network isn't. In order to build an app that can work offline you need some key components such as a real database. SQL Server Express is most definitely a real database, but many find it too capable for the local and mobile data needs. With the explosion of data everywhere, this session explores how you can leverage SQL Server Everywhere, the recently announced personal data store built on the proven SQL Mobile product. Learn how you can start programming against SQL Server Everywhere today. We cover deployment options including ClickOnce, designer support within Visual Studio, and the ADO.NET programming including updatable cursors using the SqlCeResultSet. Empower your users with Windows Client Applications that work where the user is, not where the network may be."
Hope to "see" you there!
Posted by Douglas Reilly at 09:23 AM Permalink
|
July 19, 2006
New Provider for .NET 2.0 for Oracle
Via my friend WallyM, a post about a new .NET provider for Oracle.
There are a couple of links in there that point to an Oracle magazine page with links to relavent articles, and a link to Plip's post on using Oracle Express Edition in .NET.
Posted by Douglas Reilly at 11:08 AM Permalink
|
July 15, 2006
MSDE will not run on Vista...
I am not entirely sure what I think about this news that MSDE, the free version of Microsoft SQL Server, will not run on Vista.
While there are many reasons to migrate to SQL Express 2005, I can see there being completely valid reasons why someone might not want to. I have no applications that depend upon MSDE in my current lifetime, but I have encouraged others to use it in appropriate circumstances. I certainly can imagine an application that I would want to run on Vista that was tied to MSDE that might be difficult to migrate to SQL Express 2005.
Personally, I imagine this may be another decision that will later be recinded, like the change that allowed SQL Mobile to become SQL Everywhere and thus run on regular PC's, even after Microsoft swore it would work only with Tablet based PC's.
Posted by Douglas Reilly at 12:49 PM Permalink
|
July 14, 2006
SQL Prittifier
Red Gate has created a new program to pretty up SQL code so that it will display in a pretty way in an HTMP page. It is available as a Web application, here.
Here is an article describing the product, and it's limits.
The program works pretty well. The resulting HTML is a little verbose, but when you need to print some SQL code on a Web page, it is very useful. For instance, this code has been prettified:
ALTER PROCEDURE dbo.spQueryUserIsInRole
@EMail NVARCHAR(128),
@RoleName NVARCHAR(128)
AS
SET NOCOUNT ON
DECLARE @UserID INT
DECLARE @RoleID INT
DECLARE @Ret INT
SET @Ret=0
SELECT TOP 1 @UserID=UserID
FROM [User]
WHERE Email=@EMail
SELECT @RoleID=RoleID FROM Role WHERE RoleName=@RoleName
IF EXISTS(SELECT * FROM UserRoleLink WHERE UserID=@UserID AND RoleID=@RoleID)
BEGIN
SET @Ret=1
END
SELECT @Ret AS Ret
RETURN @Ret
Posted by Douglas Reilly at 08:17 AM Permalink
|
July 13, 2006
SQL Server Full Text Search
SQL Server Full Text Search is one of those features that many people like, in theory.
In practice, it is often lots more complicated. Hilary Cotter has a two part set of articles on the subject, here and here.
Hilary is one of the "Database Geek of the Week's" that I have interviewed over the last year or so.
Posted by Douglas Reilly at 10:10 AM Permalink
|
July 12, 2006
CTP 4 of the Database Professional Edition of Visual Studio is available...
Here.
No functionality changes from the last version, just stability and a new project UI.
Posted by Douglas Reilly at 10:53 AM Permalink
|
July 11, 2006
Technical Reviews...
Jim Holmes Blogs about how difficult it can be to get the technical review of your book back from the reviewers.
I recall my first experience with having a technical book get a tech review. I was horified. In the end, it was still my book when I got done, but it was a lot better for the extra set of eyes looking at it.
That said, it can be a bit hard to take. After 5 books, I am a little more used to the process, but sometimes it still hurts. Good luck Jim!
Posted by Douglas Reilly at 10:57 AM Permalink
|
July 06, 2006
Database Refactoring...
One of the latest buzzwords in the software development world is "Refactoring." I saw a book on the topic at the Tech Ed Bookstore.
Scott Ambler writes an introduction to database refactoring here that appears to be excerpted from his database refactoring book.
Change is unavoidable, and so having the tools in place and the understanding of the process of refactoring will help you manage it.
Posted by Douglas Reilly at 05:32 PM Permalink
|
July 05, 2006
Multiple Instances or Single Instance?
SQL Server DBA's and Developers need to decide how to partition databases when running multiple databases on a single server.
This TechNet article has guidance on whether multiple instances or a single database makes the most sense. I have never been a fan of multiple instances. One problem (less of a problem these days I expect) is that some clients will not have the underlying drivers to allow them to work with multiple instances. The second problem is the general confusion over what is in which instance of the database.
Posted by Douglas Reilly at 10:07 AM Permalink
|
Review: A Developer’s Guide to SQL Server 2005
I am a book person. I love books as a learning tool. As I elaborate here, I think books are still very useful, even in the age of the Internet.
I am fortunate enough to have a huge stack of books in my inbox, a large number of them SQL Server related. One of those that I have enjoyed reading is A Developer’s Guide to SQL Server by Bob Beauchemin and Dan Sullivan.
A Developer’s Guide to SQL Server is a big book, at about 960 pages of content. SQL Server 2005 is a huge step forward from SQL Server 2000, the version we have used and generally loved for many years now. For such a big change, having access to a big book such as this, covering primarily what is new in SQL Server 2005, is a great way to get started.
The biggest single change for SQL Server 2005 is the ability to write stored procedures, functions and triggers in any .NET language. A Developer’s Guide to SQL Server starts out with a good explanation of how SQL Server 2005 hosts the .net runtime. At first glance, this seems like it might not be the first thing you need to know about SQL Server 2005, but for anyone who expects to use .NET languages to write code to run on SQL Server really does need to know a little about how the magic works. That SQL Server is now serving as a runtime host has an impact on both security and reliability. For me, reading the chapter on how SQL Server acts as a runtime host allowed me to tie together a lot of what I knew in abstract terms (such as the fact that .NET code is stored inside the database) to concrete implications (.NET provides a large number of ways to modify hosting behavior, and loading code from a database rather than from the file system shows SQL Server using alternate code loading.
Once you know what might be more than you wanted to know about how SQL Server acts as a runtime host, the following chapters go into great detail on exactly how you can integrate your .NET code with SQL Server, as well as how to use user defined data types in SQL Server. The use of user-defined data types strikes me as one of the areas that can be difficult to fully understand and manage, especially in the context of keeping the data types in synch between SQL Server and other assemblies that might interact with SQL Server and these .NET user defined types. The book does a good job covering how you should (and just as importantly, when you should) use user defined types.
Security is a major issue for database developers these days. SQL Server acting as a runtime host has significant security implications, and the world at large is demanding that database developers create more secure databases. As a partial answer, SQL Server ships with many optional features turned off by default. Security is covered in a chapter that covers all the changes in SQL Server, from enabling features to using encryption. After reading the chapter covering security, I do not consider myself a SQL Server security expert, but I do know where to look when I need to understand and use some of the many new security features.
There are chapters covering SQL Engine enhancements as well as T-SQL enhancements. In light of the new ability to write code using .NET languages, the T-SQL new features have often been overlooked. While using the .NET languages can often be useful, a careful understanding of the T-SQL enhancements can help you make better use of T-SQL for what it does best, set operations. From error handling to ranking functions to common table expressions, the book does a good job of bringing you up to speed.
There are a number of chapters covering use of XML within SQL Server. This is an area that I, honestly, have not used extensively. If the occasion arises that I need to use XML, I will refer to this book.
Following chapters cover SQL Server Service Broker, SQL Server as a platform for Web services and using SQL Server from a client application, with emphasis placed on new features, such as retrieving user defined types.
There are appendices that act as a quick tutorial on .NET, SQL Server Management Studio and using Visual Studio to access SQL Server.
Overall, if you can afford only a single book on SQL Server 2005, I cannot think of a better book to invest in
Posted by Douglas Reilly at 09:29 AM Permalink
|
July 01, 2006
LINQ to Amazon Web Service Implementation
LINQ, Language Integrated Query, is a way to access data inside the language syntax, rather than calling out to a database using a separate syntax. AMazon Web Services are used to allow folks to develop applications that integrate with information from Amazon.
Fabrice posts information on a LINQ implementation that allows you to access Amazon Web Service results. Very cool.
Posted by Douglas Reilly at 02:41 PM Permalink
|
|
