I've waited far too long to communicate this disappointing news to everyone, so here it is…

I'm not going to be releasing the SQL Express SP2 Bootstrapper manifests that I had discussed in a long ago blog posting. I'll spare you the sob story of my efforts to create this; suffice it to say that some requirements arose that complicated an official release and priorities changed over time. If you work in the software industry you know how it goes. So that brings us to…

My feeble backup plan

Most of the complications (ok, all of them) that I ran into were related to packaging up the manifests so they could be downloaded and installed. (Seriously, it's a long story, so don't ask.) The manifests themselves are pretty straight forward and only required some minor tweaks of the existing RTM manifests. There is nothing to stop me from sharing those updates with you in this blog and giving instructions on how to use them.

DIY SQL Express Bootstrapper

Technically, the bootstrapper, or the VS Generic Bootstrapper as I like to call it, refers to the technology created by the Visual Studio team to allow any developer to deliver specific sets of functionality as prerequisites that can be checked for and automatically installed by both ClickOnce and MSI based installers built with VS. The framework for building your own prerequisite is documented on MSDN in the topic Adding Custom Prerequisites. A more end to end treatment of the technology was printed in MSDN magazine back in 2004 in the article Use
http://statisticsio.com/Feeds/SQLBlogfeeds/tabid/60/Default.aspx -8/23/2007 6:41:37 PM

SELECT @SQL = 'SELECT COUNT(*) AS RECCOUNT FROM TABLE'

sp_executesql @SQL

SELECT @SQL = 'SELECT COUNT(*) AS RECCOUNT FROM TABLE'

http://statisticsio.com/Feeds/SQLBlogfeeds/tabid/60/Default.aspx -12/21/2007 2:05:26 PM

Several questions have come up lately regarding whether SQL Server Express is an appropriate product to be used in a hosting environment.  Here are the answers:

·         User Instances (also known as RANU) available with SQL Server Express Edition, are NOT RECOMMENDED for hosting environments.

·         Shared hosting: SQL Server Express Edition is NOT RECOMMENDED for shared (multi-tenant) hosting a
http://statisticsio.com/Feeds/SQLBlogfeeds/tabid/60/Default.aspx -2/22/2008 7:49:00 PM

There is a new white paper available on managing SQL Server for MOSS environments. http://go.microsoft.com/fwlink/?LinkId=111531&clcid=0x409

Great tips on Index defragging and use of DBCC.

One thing that I'm not sure I entirely agree with is the adding a proc to the user database to support index defrag. I prefer to add administrative procs to the master database so all users databases can enjoy them.

Another common questions is "can I change my content database schema" by adding a view or proc. The answer is pretty much NO - more information is contained in this article.

http://support.microsoft.com/kb/841057/

SQL Links

ETW stands for “Event Tracing for Windows” and it is used by many Windows applications to provide debug trace functionality.  This “wide” availability is a key point of using ETW because it can help to track certain activities from end to end.  For example, you can literally track a request coming from IIS, passing through protocol layer, and then finally handled by database engine in single trace file.   Unfortunately, not many people know that SQL Server 2005 provides a full ETW functionality which can output most of SQL Trace events available for SQL Server Profiler.  In this article, I will explain how to use SQL ETW with examples.

First of all, how do I know if SQL ETW is really active on my machine?  The following shows output on my server which has SQL Server 2005 Standard Edition as 2^nd instance (named Yukon).

C:\>logman query providers
Provider                                 GUID
-------------------------------------------------------------------------------
YUKON Trace                              {130A3BE1-85CC-4135-8EA7
http://statisticsio.com/Feeds/SQLBlogfeeds/tabid/60/Default.aspx -11/12/2006 8:32:00 PM

SQL Server 2005 introduced four new functions, ROW_NUMBER, RANK, DENSE_RANK, and NTILE that are collectively referred to as ranking functions.  These functions differ from ordinary scalar functions in that the result that they produce for a given row depends on the other rows in the result set.  They also differ from aggregate functions in that they produce exactly one output row for each input row.  Unlike aggregates they do not collapse a set of rows into a single row.  In this post, I'll take a look at ROW_NUMBER - the simplest of all ranking functions.

I'll use the following simple data set for the examples in this post:

CREATE TABLE T (PK INT IDENTITY, A INT, B INT, C INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)

INSERT T VALUES (0, 1, 8)
INSERT T VALUES (0, 3, 6)
INSERT T VALUES (0, 5, 4)
INSERT T VALUES (0, 7, 2)
INSERT T VALUES (0, 9, 0)
INSERT T VALUES (1, 0, 9)
INSERT T VALUES (1, 2, 7)
INSERT T VALUES (1, 4, 5)
INSERT T VALUES (1, 6, 3)
INSERT T VALUES (1, 8, 1)

Let's begin with the following simple example:

SELECT *, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber FROM T

This query orders the rows in the table by column B and assigns sequential row numbers (much like an identity column) to these rows.  The result looks like so:

PK    A     B     C     RowNumber
----- ----- ----- ----- ----------
6     1     0     9     1
1     0     1     8     2
7     1     2     7     3
2     0     3     6     4
8     1     4     5     5
3     0     5     4     6
9     1     6     3     7
4     0     7     2     8
10    1     8     1     9
5     0     9     0     10

The plan for this query is fairly simple:

  |--Sequence Project(DEFINE:([Expr1003]=row_number))
       |--Compute Scalar(DEFINE:([Expr1005]=(1)))
  
http://statisticsio.com/Feeds/SQLBlogfeeds/tabid/60/Default.aspx -3/19/2008 6:28:00 PM

Bob Ward from PSS has a wonderful blog article that explains the details about the re-release of SQL Server 2005 SP2 and fixes posted later. This is a must read for anyone deploying SQL Server 2005 SP2 to understand the various hotfixes, GDRs and procedures. Please visit his link for more details.

SQL Server 2005 SP2 Re-release and post fixes

-- Umachandar