I always look through the SQL Server 2005 samples with each new incantation, and always seem to find things in there that are new and interesting. In the SP2 version, the samples included a new sample database, "AdventureWorks Light". Also known as AdventureWorksLT.

Because folks that teach SQL Server do have a bunch of canned queries for exposition and know exactly how those queries are supposed to behave, sample databases really never disappear. Pubs was the sample database inherited from Sybase, although Sybase itself moved on to "Pubs2" last time I looked. The problem with Pubs was that it really didn't have much data at all. I always point out that, of the 8 publishers in Pubs, only 3 have actually published books. Northwind was inherited from Microsoft Access, being the original Access sample database. It had more data, but not a lot.

In SQL Server 2005, no sample databases are installed with the product by default, which is a good thing. But the "AdventureWorks series" is the new sample database, including an OLTP, data warehouse, and analysis databases, as well as case-senstive and case-insensitive collation versions. It has a reasonable amount of data and its design is said to reflect current thoughts on best practices. It is built around multiple departments in a putative organization, including Sales, HR, and Manufacturing. This allows it to include a employee/manager hierarchy and a bill-of-materials table.

But the Adventure Works database is a nice 3NF database, with lots of tables (~70) and it sometimes difficult to use for exposition, because of the long multipart object names and lack of familiarity. AdventureWorks Light is a bit more approachable, weighing in at about 3MB, but still over available as an MSI (no simple create script). It contains:

10 tables and 3 views
500 or so rows in each table
All in single object schema SalesLT
No stored procs, but 1 scalar and 2 table-valued functions
A single XML schema collection

It even includes a version of Sara Tahir's uspPrinterror and uspLogerror procedures for error handling, but interestingly uspRethrowError is left out.

We'll see if this new sample database is simple enough for widespread pedagogical use. At least it may get folks more use to using (memorizing) the "AdventureWorks family" table and column names.

PS: The SQL Server Samples now have their own Codeplex project at http://codeplex.com/SQLServerSamples

I just returned from Microsoft Developer and ITPro days in Ghent, Belgium. The hospitality was great, and the event itself drew some big crowds of top notch developers and IT pros. Ghent was an amazing city, with medieval architecture sharing the city with modern buildings. Besides the four talks on SQL Server 2005 and ADO.NET that I did for the main conference, Wednesday, I gave a special talk on Event Notifications for the Belgian SQL Server user group. On the previous, the user group and I went out for ribs and beer. I sampled quite a few different excellent Belgian beers, there is quite a variety!

Thanks to all for a really good time. The conference demo scripts are posted up on the SQLskills website. 

For folks that have been asking...my latest whitepaper "SQL Server 2005 Security Best Practices - Operational and Administrative Tasks" was posted on the Technet website this week. It also covers the nuances of security when using SQL Server SP2 and Vista. Enjoy!

Next week (28-29 Mar) I'll be doing some talks on SQL Server 2005 and ADO.NET vNext at DevDays Belgium in Ghent. On 28 Mar, I'll also be doing a special additional talk on SQL Server 2005 Event Notifications (including a cross-database notification demo) for the Belgian SQL Server user group at the event. If you're a registered member of the user group, I'll see you there. You can also sign up to be a member of the user group at the user group website. 

Thanks to the user group for inviting me.

Since SQL Server 2005 was introduced with .NET programming support, folks have been trying to push the boundaries of what can be used in SQLCLR or at least trying to determine where those boundaries are. Here's two things that, as far as I know, can't be done in SQLCLR.

1. Use dynamically generated code. The canonical example of this is dynamic serialization assemblies generated when you use "Add Web Reference" in the generated web service proxy code. The way around this is to use the sgen utility. But lately, it's been brought up that dynamic programming languages such as Iron Python always generate dynamic code. SQLCLR forbids using this, even in UNSAFE assemblies. No dynamically generated languages.

2. Use the SMO libraries. A combination of SMO not supporting partially trusted callers and using a special type of connection result in SMO being unusable even in UNSAFE assemblies. The obvious workaround is to use SQL DDL, but SMO encompasses more than DDL, for example, configuring service settings via WMI. If you really want to use SMO, it would be possible to call out to a web service or better yet, a Service Broker-based service that uses external activiation, does the SMO calls and returns the script and/or results.

One thing that I thought was particularly interesting in the Orcas Mar CTP was support for stored procedures. This support exists in LINQ to SQL and EDM ObjectServices; I thought I'd start with ObjectServices. There almost no documentation on this topic at this point, about half a page with an incomplete mapping schema example. That's to be expected at this point, though.

In Mar CTP, you can specify stored procs for insert/update/delete, but not for EDM queries yet. You need to change the SSDL (store schema definition language) and MDL (mapping definition language) files. With the correct mapping filechanges, AcceptChanges just calls the sprocs automatically. I started with a VS-generated set of mapping files and used XSD-based intellisence and error messages with line numbers to guide me along. Although you may have gotten rid of the XSD/XML errors or compile-time errors, your mapping files can fail to agree with one another. This occurs as a runtime error when you "new-up" an instance of your model class. Try-catch is your friend here.

SSDL changes were easier (just add the Function and Parameter elements for the proc) with one gotcha. My SSDL Namespace attribute was called "people"; if you specify "dbo.myproc" as the store name, EDM looks for [people].[dbo.myproc] at execution time. That's invalid in SQL Server. Specifying "myproc" as the store  name (or letting it default to the value of the "Name"attribute) causes the runtime to look for [people].[myproc]. The name of my sproc was actually [dbo].[myproc]; I had to change to SSDL Namespace attribute to "dbo" to make it work. Doing so made me change the MSL to match, but had no global ill effects.

The MSL was a little more interesting. I needed to put an "EntityTypeMapping" and "TableMappingFragment" in between my "EntitySetMapping" and "ScalarProperties" elements. Then I could add my ModificationFunctionMapping and Insert/Update/DeleteFunction elements as children of EntityTypeMapping. Because the XML schemas require elements be defined in order, I almost thought this feature was masked out in this CTP. Although I could see it in the schemas. It was there, of course, you just have to hit the intellisense in exactly the correct place in the document to show the element you're looking for.

OF COURSE, this is going to sound like complete gibberish without an example. I started with the easiest possible example: one table, few columns, primary key (its required) but not even an identity column. I also have two projects, one with the "vanilla" tool-generated CSDL/SSDL/MSL so I could refer back if I had problems. I did have problems. Coding three files of XML by hand, even with XSD-base intellisense, is right up there with [insert your least favorite chore here].

Three hours later, thanks to perseverance and SQLProfiler, I had a functioning prototype. It's posted here. I'm trying to decide which undoc'd part of this interesting set of mappings, models, and query lanaguages to try next. Or what tool to create to make this less of a chore. Enjoy!

SQL Server SP2 was released last Monday. The links were posted on many blogs, so I won't repost any but the main one here. SP2 is a "major" service pack because there are a few "must have" features in addition to some rather useful improvements and quite a few bug fixes. The ones I'd consider must haves are:

1. Support for running SQL Server 2005 on the Windows Vista operating system. Vista will inform you of the SP2 requirement when you install SQL Server 2005. You install the "base" SQL Server 2005 first and apply SP2 immediately afterward, except with SQL Server 2005 Express. Express ships an SP2-specific product.
2. Support for business intelligence features of Office 2007 in Analysis Services. Unless you have SP2 installed, these features will be greyed-out in Office 2007.

There are some brand new features too like Sharepoint Server 3.0/SQL Server Reporting Services integration, the vardecimal storage format, and multi-schedule/multi-server Database Maintanance Plans.

But with that many changes, there are bound to be some incompatibilities. Be careful with these.
1. If you enable vardecimal storage on a database, you can't restore or attach an SP2-post vardecimal format database backup on an SP1 instance.
2. You can't use SP2 Analysis Services backups and data folders on pre-SP2 instances of Analysis Services
3. Analysis Services databases with linked measure groups can have problems on SP2. The most likely ones will be databases migrated from AS2000. See the SP2 Readme file for specifics.
4. You can't use Database Maintanance Plans from an SP2 instance will an SP2 instance, because the SP2 maintanance plans can have multiple schedules.

So be sure and be aware of these to have a successful upgrade.

Next month I'm looking forward to speaking at DevDays in Ghent Belgium on SQL Server 2005 and on ADO.NET vNext. The conference is being held on 27-29 Mar, but most (all) of my talks will be on Thursday, the 29th. On Wednesday at noon, I'll be doing a book signing at the A/W booth. If you're around at DevDays drop by and say hi. Besides ADO.NET vNext, I'm talking about SQL query tuning, making an application run faster using Service Broker, and SQL Server SP2 and SQL Server on Vista.

See you there!

My SSIS whitepaper, which was available in draft form on the SSIS wiki, is live on the Microsoft web site today. For anyone who is confused by all of the whitepaper announcements lately, here is a list of titles and direct links.

This one is about SSIS:

Connectivity and SQL Server 2005 Integration Services
http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc

And these two (from last week) are about scaleout technologies and solutions. That is Service Broker, Scalable Shared Database, Peer-to-Peer Replication, Query Notifications, Distributed Partitioned Views, and Data Dependent Routing.

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/ImplementingScaleOut.doc

Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/InternalsTroubleshootingScaleOut.doc

Enjoy!

Peter DeBetta recently tagged me in what is called "blog tag". When I described it to Mary, she named it "blog chain letter" and said I should ignore it. But it got me thinking. Although I run my mouth often and have some fairly weird stories, here's 5 things you may not know about me. But then again, you might...

1. I attended the first home game of the New York Mets baseball team on a Friday at the Polo Grounds in 1962. My dad took me when I was 8. It was, as Casey Stengel would say, amazing, when they shocked everyone by winning it all in 1969. I'm waiting for the Seattle Mariners to do the same today.

2. I have a degree in Chemistry, rather than Computer Science. In fact, I dropped the only CS course I took in college, because I didn't have enough time in the day for labs. The labs consisted of waiting in (a fairly long) line to punch lab assignment programs onto cards, with a limit of 15 cards at a time. I was taking six other courses that semester...

3. I worked on the first commercial implementation of Kerberos that I'm aware of. In 1992, I joined a startup called Open Computing Security Group (OCSG). We put out Kerberos implementations for almost every variant of Unix and for IBM mainframe, as well as clients for Macintosh and Windows 3.1. Really. The company is now known as Cybersafe.  My first port was NeXT OS 1.0 (which is just BSD 4.4 Unix over a Mach kernal + GUI). Our biggest competitor at the time was Cygnus support, who compiled, packaged, and sold support for the open Project Athena code. I never forgot their slogan "We make free software affordable". IBM had (in addition to RACF) a competing security system called CryptoKnight.

4. In 30 years in this business, I've only carried an on-call pager or cell phone for a total of 2 months. Not that I was never "on call", I WAS on call for YEARS. In the pre-pager days you just had to tell the operations folks where to find you at all times.

5. I wrote the first (that I'm aware of) class on ASP (ASP classic, not ASP.NET) in the alpha2 or beta1 days of it. It was for an internal Microsoft gig. Just to provide some time reference, the day before I taught this class, I attended a pre-release seminar on Active Directory Version 1.0. Teaching across the hall during the class was a guy named Don Box, who I'd never heard of, teaching COM.

I'll do everyone I know I favor, and not "tag" anyone. Cheers.

The companion whitepaper to my "Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005" whitepaper (see yesterday's post) is available. This whitepaper is called "Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005", with as much about internals as I could cram in 50 pages. Again, I don't have the "main" link; the direct link is here. Enjoy.

I've been working on some whitepapers on scaleout technologies in SQL Server 2005. The first whitepaper is now available; I don't have the main link, but here is the direct link to the doc file on the Microsoft download site. The whitepaper is about the implementation steps when using scaleout technologies like Service Broker, Scalable Shared Database, Query Notiifcations, and Peer-to-Peer Replication and how to choose which technology or combination of technologies is the best fit.

It will be followed by a companion whitepaper about internals and troubleshooting of these same scaleout technologies. I'll let you know when that one's available. Hope you find them useful.

Michael Rys (and who would know better/sooner about this) just announced on his blog that XQuery 1.0 and associated specs (including XPath 2.0 and XSLT 2.0) are now official W3C recommendations. Congratulations to the working group on this. The specification process is also underway for a standard XQuery Update Facility and XQuery/XPath Full-Text query facility. The specs are available on the W3C website.

I'll have to revisit/reread the specs, now that they are finalized. The one that always seem to cause the most controversy in classes that I've taught has been the XQuery 1.0/XPath 2.0 Data Model, which is based on sequences, rather than being a document object model, Infoset, or Post Schema Validation Infoset, or a description of the serialized form of XML (Extensible Markup Language (XML) spec). The inclusion of both nodes and atomic values is usually a bit disconcerting, and I get comments like "that's not XML" when people see it. Although SQL Server 2005's XML data type doesn't exactly follow the XQuery 1.0/XPath 2.0 Data Model, rumor has it that the next version of the ISO/ANSI SQL spec (SQL2007?) may have some something to say about this, as well as something to say about XQuery in general. Right now, the SQL2003 spec doesn't specify a query language.

It will also be interesting to see what the SQL Server folks do with regards to updates to support the new specs in the next release, and support of a larger portion of the language constructs. Also, it would be interesting to see support for sequences in other XML specs and/or client and middle-tier APIs. So that "other folks" can read the results...

The SQL Server 2005 Integration Services team has posted a draft of my SSIS Connectivity Whitepaper on their connectivity wiki at http://ssis.wik.is (note the change in the address of the wiki site). It addresses the details of using OLE DB, ADO.NET, and others with SSIS and the database/data source of your choice. Check it out.

If you've had success (or issues) with using SSIS and any type of data, I'd like to hear about it.

Enjoy...

Thought I'd answer one last question or two from the last time I did the talk on SQL Server 2005 plan guides. If you're asking at this point "what is a plan guide", reference the SQL Server 2005 BOL or this blog entry.

Q. Can you use a plan guide on an encrypted procedure? I want to put a hint on encrypted vendor-supplied code.
A. No, per books online you cannot create an OBJECT plan guide on any object that is itself encrypted or references an encrypted object. You get error message 10512 if you try.

Q. Can you create a plan guide on the SQL statement (rather than an OBJECT plan guide) on a SQL statement that I know exists as part of an encrypted procedure and have the guide be used?
A. You can create a SQL-type plan guide on a statement in a procedure, but it won't be used in any case; you need to create an OBJECT-type plan guide for it to be usable inside a procedure. You can confirm its "non-use" by looking at the showplan output from an un-encrypted procedure with a SQL-type plan guide; you can't see an XML showplan for statements in an encrypted procedure.

Q. Can I have more than one plan guide? For example to ENABLE/DISABLE different guides at different times of the day? Can I have two guides enabled at the same time?
A. You can't create more than one plan guide on a single statement/module-or-object combination, whether they are DISABLEd or not. You get error message 10502 if you try. You'd have to DROP the first plan guide before you can create the second one.

Here's another blog posting to answer a question from over a month ago.

With separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA named after the user and assign that SCHEMA as the user's default schema. Question was, "does sp_dropuser do the right thing and drop the schema named after the user?"

Short answer is "yes, it does". Pretty easy to prove. Actually, sp_adduser can eventually call sp_grantdbaccess and sp_dropuser eventually can call sp_revokedbaccess, so...

create login bob with password = 'A^#DNEfdfhkWD#*iubdwc )000ks1'
go

use adventureworks
go

sp_grantdbaccess 'bob'
go

select * from sys.schemas  -- bob schema is there
go

sp_revokedbaccess 'bob'
go

select * from sys.schemas  -- bob schema is gone
go

The more intriguing thing is when you check this by using the OBJECT_DEFINITION system function:

-- creates schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_grantdbaccess'))

-- drops schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_revokedbaccess'))

Sure enough, there is nicely commented code in sp_revokedbaccess to drop a schema. BUT rather than using the DROP SCHEMA DDL statement, it looks like this:

EXEC %%Owner(Name = @name_in_db).DropSchema(OwnerType = 1)

Hmmm....

It's been over a month since I've blogged, confirmed by the previous blog entry when "it's sunny in Barcelona". Thought I'd blog about a question that someone wrote to ask me a few weeks ago.

What are the limitations/requirements for using a SQLCLR function in an indexed view (aka materialized view)?

Some of the requirements came from the books online, but can be verified with a simple sample, but there were a few surprises.

1. The VIEW must be created WITH SCHEMABINDING. This is a "normal" requirement of an indexed view, but usually when creating VIEWs WITH SCHEMABINDING that reference UDFs, the UDF must be defined WITH SCHEMABINDING as well. SQLCLR UDFs can't be explicitly be defined WITH SCHEMABINDING, but they can be used in views defined as WITH SCHEMABINDING.
2. The VIEW can't use a SQLCLR derived column as part of the index key unless the derived column is declared as PERSISTED in the base table.
3. The SQLCLR function must be declared Deterministic and Precise, and do no data access using SqlCommand, etc. DataAccess = None, SystemDataAccess = None is the default in SQLCLR functions anyway. And no external access is allowed.
4. An indexed view cannot contain a SQLCLR user-defined aggregate (UDA) function.

To demonstrate, I wrote two functions AddOne in SQLCLR, TSQLAddOne in TSQL. You can use AddOne in an indexed view. You can use AddOne as a key column in an indexed view if its defined as persisted in the base table. Note that the TSQLAddOne can be used as an index only if its defined WITH SCHEMABINDING.

This is fairly similar to the restrictions for TSQL functions and indexed views, except that you can access data in TSQL function and use it as the key, only if the TSQL function is defined with schemabinding. SQLCLR functions can't be defined with schemabinding, because there is no way to figure out which tables, etc, it's accessing. So because you can declare a TSQL function with schemabinding, you can probably make it part of the key if you access data. But, as when using a SQLCLR function in a VIEW WITH SCHEMABINDING, this shouldn't be an issue when your SQLCLR function does no data access. Hmmm...

Check out "table3 and view3". It uses SQLCLR function in a persisted computed column, and then as the KEY in an indexed view derived from the table.

I'm at ITForum in Barcelona, day 2. I've got the 17:00 talk and its on Troubleshooting Service Broker. It's warm and sunny outside, especially for November, and I'll need to think up something extrordinary to keep people inside and at the talk. How about having Service Broker make the sun shine inside programmatically..you can do anything with a database, right? Unfortunately, the .NET assembly System.Sunshine.dll is not on the "approved assembly" list for SQLCLR. Must not have done that reliability testing that SQLCLR folks require.

For everyone that was at my Key Management talk yesterday and said they couldn't live without the demo scripts I was using, the scripts will be, along with the Broker Troubleshooting and the XML for DBAs scripts, on the SQLskills website when the conference ends. Look under the "Past Events". By next week, this will be a "past event" and the sunshine a distant memory. Sigh.

I'm at ITForum in Barcelona this week. I'll be speaking on three topics that are:

1. Completely or almost completely new in SQL Server 2005
2. Take some DBAs a bit out of their comfort zone

If you've been avoiding these features until now, its time to take the plunge and let these features help you, rather than avoid them and do without the beneifts they provide. The topics are:

1. XML for DBAs - Showplan, DDL triggers and lots of things other things YOU use are XML.
2. Key Management - this is useful even if you don't do data encryption. Linked servers and SQL Agent use encryption keys.
3. Service Broker troubleshooting - Event Notifications and Database Mail (among others) use Service Broker.

See you there.

Last week at TechEd I was showing off Query Notifications. When I showed using the preprovisioned queue (overloads on SqlDependency.Start and SqlDependency constructor), a delegate asked about using the same queue with more than one subscriber. He repeated the question when I showed the low-level SqlNotificationRequest.

I did some tests over the weekend and the answer is no. You need a separate queue for each subscriber, otherwise the SqlDependency listener gets "confused". If you use the dynamically created queues like most people do (SqlDependency.Start with only a connection string), you get a new queue and service for each subscriber. So you won't notice this. I thought I'd have better luck with the SqlNotificationRequest because I'm reading the queue myself, but without going to the system tables (and perhaps introducing race conditions) its not possible there either.

Queue are cheap to create, so there is really no reason not to create a separate pre-provisioned queue and service for each notification subscriber. But, at least for the time being, its a requirement.