Get documents about "Bob Beauchemin on SQL Server 2005
Document Sample
http://www.dotnetrocks.com
Carl Franklin and Richard Campbell
interview experts to bring you
insights into .NET technology and the
state of software development. More
than just a dry interview show, we
have fun! Original Music! Prizes!
Check out what you've been missing!
Text Transcript of Show # 172
(Transcription services provided by PWOP Productions)
Bob Beauchemin on SQL Server 2005
April 14, 2006
Our Sponsors
http://www.devexpress.com/
http://www.telerik.com/radcontrols
http://www.code-magazine.com
http://www.code-magzine.com/
Bob Beauchemin on SQL Server 2005
April 14, 2006
Geoff Maciolek: The opinions and viewpoints Richard Campbell: How about some email?
expressed in .NET Rocks! are not necessarily
those of its sponsors, or of Microsoft Corporation, Carl Franklin: Some email would be great. And
its partners or employees. .NET Rocks! is a all our email is around dnrTV this week. First
production of Franklins.Net, which is solely one…
responsible for its content. Franklins.Net –
‘Training Developers to Work Smarter’. Richard Campbell: Really?
(Music) Carl Franklin: Yeah. First one, “Hi! Carl and
Richard, firstly thanks for the great show;
Geoff Maciolek: Hey Rock heads! Pick up your definitely the best tech podcast I listen to, but I
assembly by the bootstraps and listen up; its time will cut to the chase. A while ago, I believe you
for another stellar episode of .NET Rocks! – ‘The did a DNR on SQL Server 2005 and OLAP with
Internet Audio Talk Show for .NET Developers’ the whole cube thing. Now, I have always had an
with Carl Franklin and Richard Campbell. This is interest in this, but alas, have never had the
Geoff Maciolek, here to announce Show # 172 opportunity to investigate. I actually now have a
with guest Bob Beauchemin, recorded live, copy of the Developer Edition of SQL Server
Friday, April 14th 2006. 2005 and I am frantically trying to figure this out,
alas, without much luck.” He loves that word,
.NET Rocks! is brought to you by Franklins.Net– alas. “You notice, that’s just one “l” away from
‘Training Developers to Work Smarter’ and now something really bad. I can't seem to get it to
offering a whole suite of onsite and remote deploy. I am wondering whether I should be
classes in .NET 2.0 Technologies, online at running on Win 2003 or something; XP Pro
www.franklins.net and by telerik r.a.d. controls – currently. Anyway, I thought it would be an
‘The most comprehensive suite of components excellent dnrTV episode just to run through the
for Windows Forms and ASP.NET web basics of setting up a Cube in pulling out some
applications’ online at www.telerik.com. wondrous statistics. And definitely an episode on
how to get cool stuff working would be brilliant.
Support is also provided by Developer Express, Thanks Carl and keep up the good work.”- Dan
‘Crafting first class tools, frameworks and Swain, London, UK.
controls for the .NET developer’ improve your
experience online at www.devexpress.com and Richard Campbell: Cool. That was Andrew
by CoDe Magazine, the leading independent Brust back in December; we did that show on
magazine for .NET developers, online at Business Intelligence and OLAP. Maybe we
www.code-magazine.com. And now, the man should bring him back and get him to do a dnrTV.
who will never again buy a coffee maker
endorsed by the prince of podcasting, Carl Carl Franklin: I am thinking that will be great.
Franklin. And the next email is not so bullish but he has
some good constructive criticism. “Hi there! Very
Carl Franklin: Thank you, thank you very much good programming, excellent job. However, the
and you are listening to Carl and Richard again TDD dnrTV is really-really bad. Please ask
on .NET Rocks! What the heck episode is this, someone more mature to do this unless of course
Richard? you have an agenda against TDD. It’s simply not
fair to present Test-Driven Development in such
Richard Campbell: 172. a way. This guy is definitely smart; however, he is
also too green. Frankly, as an introduction for the
Carl Franklin: I can always count on you for the masses, how can you test things so close to the
episode number man. It’s nice to be able to show UI? Also Mach stuff is really not for mass
up to work, fall into the booth and do my job. How consumption -- for an introduction; Mach i s
are you? needed only in 10% of things. Test-Driven
Development is simple; it’s simply just doing unit
Richard Campbell: Yeah. We are all feeling testing for business logic. For .NET all practical
organized today; got everything together, got the purposes it is NUnit or derivatives, nothing more.
new sound booth going, its cool. However, this guy makes it unnecessarily difficult
and confusing. Now I know why Rocky has the
Carl Franklin: Yeah. I am in a new sound booth idea to not confuse testing with TDD; certainly do
at PWOP Studios. We just put three of them in not confuse TDD with some kid’s TDD.” Ooh!
and it’s kind of nice, it’s nice in here. I feel real Strong words. “I feel you should invite really
now. Got a nice big window to look out, got a mature and moderate TDD guys to do another
microphone, got a fan, got connectivity; what else one; otherwise it is really not fair and will mislead
do you need? people.” Okay. Well, that’s good constructive
criticism.
Transcription by PWOP Productions, http://www.pwop.com Page 2 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
Bob Beauchemin: Oh yeah, that was the one
We got another email, same day I think, “Carl; I day -- was the training that was supposed to go
just finished watching Jean Paul Boodhoo’s till about 6 or 8 and ended up going till 10 and the
presentation on Test-Driven Development and I people still wanted more.
thought I would respond to your request for
feedback. I am an avid listener of dnrTV and I Richard Campbell: Yeah, it was really a weeks
want to let you know how much I appreciate your worth of content crammed into a day.
efforts. dnrTV is just awesome. I've learnt so
much about .NET through watching dnrTV; there Bob Beauchemin: Yeah, that’s the way I tend to
is simply no substitute for watching a do this.
programmer actually code something out in real
time and listening to the comments. You do a (Laughter)
great job as an interviewer too. Anyway, it sure
would be fun to sit around with a crew and watch Carl Franklin: Nothing wrong with that; as long
DNR, but I watch alone, so I can concentrate and as some people can keep up. So, you have been
rewind for key points etc. DNR has become my in the business a long time.
secret to success. Keep up the good work. So,
there you have it; a little of this, a little of that, Bob Beauchemin: Yeah, I am getting old, yeah -
people like dnrTV overall, I think it’s flying. - geezer.
Richard Campbell: Well, I am excited that the Carl Franklin: As long as Richard or longer?
Test-Driven Development topic generated so
much interest. Because I think it’s a significant Richard Campbell: We are all getting old; I
issue. mentioned that before the show started. We were
looking over the bio that -- first time I laid hands
Carl Franklin: Yeah, me too. And of course, if on a micro computer was 1977 as well, but I am
you have any comments for us send them along a bit younger than Bob; I started earlier.
with a check for $100,000 to .NET Rocks! at
franklins.NET and we’ll be glad to check them Carl Franklin: Okay.
out. We always want to hear from you, good or
bad. So, our guest today is Bob Beauchemin Bob Beauchemin: Yeah, that’s true, yeah.
and he is a Director of Developer Skills for SQL
skills. He is a database-centric application Carl Franklin: So SQL Server 2005 seems to be
practitioner and architect, instructor, course your thing.
author, writer et cetera and has worked with
computers since 1977. He's been an Application Bob Beauchemin: Oh! Right now, it’s pretty
Developer in DBA with relational databases like much all I am doing; I used to do other things like
SQL Server™, Oracle, Sybase, and DB2, as well teach .NET, teach ASP.NET, ADO.NET but now
a s n o n -relational databases including IMS/DB, it just seems to be all of SQL Server all the time.
IDMS, and others. Over the past two years he's
been teaching his SQL Server 2005 course to Richard Campbell: Didn’t -- you used to be with
premier customers and Microsoft personnel DevelopMentor?
worldwide through the SQL Server 2005 Ascend
program. He's provided SQL Server 2005 training Bob Beauchemin: Yes I was, I was with
to over 500 developers through this program. His DevelopMentor for about 6 or 7 years and I
latest book is “Programmers guide to SQL server started out with them by teaching MTS and OLE
2005.” Welcome Bob! DB Provider writing; and I sort of, talked them
into having a SQL Server course one day and it
Bob Beauchemin: Thanks. sort of took off from there. So at this point, this is
pretty much all I do.
Carl Franklin: Glad you could be on this show.
Carl Franklin: Bob if you had just one argument
Bob Beauchemin: Good-- good you can have to make for moving from SQL server 2000 to
me. 2005, what would it be?
Richard Campbell: I remember that training Bob Beauchemin: Just one? I only get one?
Bob, back at the Evangelism Airlift in, when was
it, August of 2004? Carl Franklin: Just one.
Carl Franklin: Long time ago. Bob Beauchemin: If I had one argument?
Transcription by PWOP Productions, http://www.pwop.com Page 3 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
Carl Franklin: We’ll go into others but let say the do either one -- and you can choose which one is
killer thing. best for you.
Bob Beauchemin: Okay. Well, there’s quite a Richard Campbell: And the difference is, I
few killer things in 2005. mean, for those who aren't up on this concept,
locking means, I have only ever got one copy of
Richard Campbell: Just one. the data, it never changes, and it’s protected from
changing while something is going on. It may not
Bob Beauchemin: But the one that people are be something as dramatic as an index, it may just
kind of surprised at because it’s sort of snuck in b e t h a t -- I’m in the middle of an update, so
there, is Snapshot Isolation, the ability to have nobody else can update this.
isolation that works by means of versioning rather
than by means of locking. And you can have Bob Beauchemin: Right. And nobody else can
versioning or locking or both if you want, and I see it either. Nobody else can see the real data
actually use that to have a bunch of new features doing an update.
that work because of that like, online index
rebuild for example. So, that’s the one that Carl Franklin: And this is the entire table we are
people always forget when they talk about the talking about.
neat, glitzy ones like .NET or XML support or
Service Broker, those things -- they forget about Bob Beauchemin: Oh! Well, row by row. SQL
the basics. Server has done row locking for quite a while
now.
Richard Campbell: Well, and you think -- it’s
such a huge issue you are touching against Carl Franklin: That’s right; we used to have
there… You need to replace indexes every s o page locking than row locking. Yeah
often. There is no two ways around it; and it used
to be that you basically lock the entire table while Bob Beauchemin: Yeah. Row locking -- or page
that’s happening. In a 24/7 system that’s not locking was a while ago, they can …
acceptable.
Carl Franklin: Long time ago.
Carl Franklin: And this is stuff that just happens
by default, the locking, or is this stuff you have to Bob Beauchemin: They do page locking, but the
do manually? default is row lock.
Bob Beauchemin: Oh! No. Well, the locking Richard Campbell: And up until now, SQL
happens by default. But to change things from Server has only offered locking in all of their
locking to versioning you actually have to make a isolation modes.
conscious decision to do that and actually set
things in the database to be able to make it Bob Beauchemin: Correct. All of the transaction
happen. It wouldn’t do to have things just change isolation levels use locking until this point.
over automatically.
Richard Campbell: Snapshot is the exception.
Carl Franklin: Right. Now we finally have versioning.
Bob Beauchemin: Because it could break all the Bob Beauchemin: Right. Well, you have two
existing applications you have. other different versioning isolation levels that
correspond to what you would see in a versioning
Carl Franklin: So, the idea is to give the you database; Oracle being the most famous of the
more control over what gets locked and when, versioning databases.
right?
Carl Franklin: Okay. All right good.
Bob Beauchemin: Sure. Exactly. And this is a
debate that’s been going on since the beginning Richard Campbell: So, maybe describe how
of the relational database; is it better to have versioning works in SQL Server, like what's
isolation using locking or is it better to have actually going on when I switch to a snapshot
isolation using versioning? isolation.
Carl Franklin: Yeah. Carl Franklin: Great question.
Bob Beauchemin: And so now, SQL Server Bob Beauchemin: Oh! Okay. So, when you
doesn’t have to worry about that debate; they can change to a snapshot isolation mode, basically
what happens is, SQL Server starts saving old
Transcription by PWOP Productions, http://www.pwop.com Page 4 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
versions of data in Tempdb. And when you read,
you read as of the last committed transaction or Carl Franklin: Now, it’s interesting that you have
the time you started the transaction. So, there is picked out as your number one reason -- that’s a
two actually different isolation modes in pretty huge reason, and I know its hard to pick
versioning. These are usually called in versioning one. So, go ahead; tell me as many things about
databases, read committed and serializable. But SQL Server 2005 as you really like.
SQL Server uses those terms for something else.
So, SQL Server calls those statement level Bob Beauchemin: Well, the glitzy ones that
snapshot and transaction level snapshot. But the every body has heard of, and in fact the ones I
idea is, you are always seeing data as of a thought we are going to talk about today before
certain point in time, but it still is consistent. And we started on that one was SQL CLR, the ability
you don’t have to take locks to make that to run CLR inside of SQL Server and XML. The
happen. The drawback is of course, you’re ability to have an XML data type and do XML
saving a whole bunch of extra stuff in Tempdb. queries and web services and all sorts of XML
type stuff in SQL Server; but there is also the
Carl Franklin: Yeah. Right. Service Broker, which is a service oriented
queuing system that lives in SQL Server.
Richard Campbell: And if you let that go on too
long, it could get pretty serious, but if you’re Carl Franklin: Right.
letting it go on too long, you are probably doing
something bad. Bob Beauchemin: That’s sort of, a sneaky one
too. People haven't really heard of that as much
Bob Beauchemin: Well, or you are having a big as the SQL CLR or the XML. There is also a ton
Data Warehouse type job that runs for 4 hours of security enhancements in SQL Server
and you want to make sure it’s consistent as of including Data Encryption and certificate based
midnight. I mean, that was the Oracle DBA’s Encryption -- things like that. So, there is quite a
biggest -- you know, nightmare was to be woken bit of everything in this version of SQL Server,
up by snapshot to old or -- snapshot is filled up which is why it took 5 years to come out.
and now that probably would be the SQL Server
versioning process’s biggest nightmare to see Carl Franklin: Right. (Laughter)
Tempdb fill up. In either…
Bob Beauchemin: People wonder why it took 5
Carl Franklin: Right. years to come out, and when you see the list of
features in it, it’s just amazing; and I am not even
Bob Beauchemin: of that case, it’s pretty much - talking about the Analysis Services stuff…
- the database is going to stop.
Carl Franklin: Right
Richard Campbell: Yeah, when TEMP DB fills
up, it’s better to just take no more transactions. Bob Beauchemin: …or the complete re-write of
DTS into integration services or the re-write --
Bob Beauchemin: (laughter) Yeah. you know, the enhancements to Reporting
Services that are in this release. So, I am just
Richard Campbell: But you are in a painful spot talking about the Database Engine itself.
at that point. And you know we have been bitten -
- beginners with SQL Server get caught by Carl Franklin: So, let’s talk about SQL CLR. We
Tempdb quite often because they forget or aren't haven't talked about that with anyone on this
aware that Tempdb is used by all sorts of things show in quite a while; and we talked about it
inside of SQL Server. You run a great big more before SQL came out, and after. So, you
aggregate join, it’s using Tempdb as it’s have obviously done a lot of work with this. Tell
scratchpad and if you haven't given enough us what's cool about this. And we …
room, the query is going to fail.
Bob Beauchemin: Okay
Bob Beauchemin: Yeah absolutely. And people
say its worse to have it in Tempdb than to have it Carl Franklin: …will talk about the pitfalls later.
where Oracle has it, which is a rollback segment. But tell us what's cool about it.
They call it rollback segment or rollback
database, but in Oracle if the rollback segment Bob Beauchemin: Okay. Well, what's cool about
fills up things stop too; so, same premise. it mostly is, it runs in process. Most databases
will have the ability to call either .NET code or
Richard Campbell: Same problem. JAVA code but that c o d e usually runs out of
process; and the problem with that is, if you’re
Bob Beauchemin: Yeah.
Transcription by PWOP Productions, http://www.pwop.com Page 5 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
going to run the code out of process, you might Bob Beauchemin: So, there is some of each;
as well have the code run in the web server… and the one that sort of, causes the most
contention is the discussion over whether or not
Carl Franklin: Yeah. you should run stuff that’s considered business
logic inside of a database…
Bob Beauchemin: …as run inside -- or pretend
it runs inside the database. So SQL Server is sort Carl Franklin: Yeah.
of, unique in having that code run inside the
database. That does mean that they had to do a Bob Beauchemin: And I think that one is
lot of extra work to make sure that the code particularly funny. In 1997 I taught MTS to a
doesn’t hurt the database and in fact they re- whole bunch of people around the world; and
architected the whole interaction between the when I taught MTS to people, I was trying to tell
database and the underlying operating system; them to move stuff out of the database and into
it’s called SQL OS. So, one of the reasons, but the middle tier.
not the only one, for SQL OS was to integrate all
of the .NET resources with SQL’s resources. So, Carl Franklin: Right
its not like you are running 2 things that compete
with each other, they are 2 things that work in Bob Beauchemin: And at that point in time, I
conjunction with each other. was told in no uncertain terms by a lot of people
that that logic actually belonged inside the
Richard Campbell: Yeah, that had to be a huge database and stored procedures and it was a
issue for them. really silly idea to move it into the middle tier. So,
I took that and went on and things sort of,
Carl Franklin: They have to retrofit these two evolved a little bit, and now it seems like people -
things together. - in fact, these same people, some of them are
telling me that this stuff has no business being in
Richard Campbell: Yeah. I think it’s quite a stored procedure; this stuff should be in the
amazing that they got the Framework into SQL middle tier. So, the thing about MTS and COM
Server. was that you could run stuff in MTS but you
couldn’t really run COM components inside of
Bob Beauchemin: Yeah that’s true; and in fact, SQL Server. So, if you wanted to move stuff out
it will be interesting to see how they proceed with to the middle tier that was your only choice.
it going forward to see if, for example, things like
the Windows Communication Foundation and the Carl Franklin: Right.
Windows Workflow foundation could conceivably
run either partially or in conjunction with… Bob Beauchemin: You obviously can't run
Transact-SQL in an application server
Carl Franklin: Right someplace. So, you sort of had to make a choice
of where the code ran, one way or the other. The
Bob Beauchemin: SQL Server nice thing about CLR is, you can fight with me
either way and have your way.
Richard Campbell: Yeah. And there is got to
have to be something that doesn’t work coming Carl Franklin: Sure.
down the pike; its going to have to be. I mean
how can they possibly -- I know it’s their job to Bob Beauchemin: If you really want to run that
think that far out, but I mean, there is a lot of code in the middle tier, you take the CLR code,
potential combinations of technologies that could you tweak it a little bit, you move it out to the
go to work there. middle tier. If you want to run the code in the
database, you tweak it a little bit and move it into
Bob Beauchemin: Oh! sure. the database.
Carl Franklin: What kind of code would you like Carl Franklin: It’s that “tweak it a little bit” that
to run inside SQL Server? raises eyebrows though, right? I mean, how
much tweaking do you have to do?
Bob Beauchemin: Well, there’s code that you
should run in SQL Server, and there is code that Bob Beauchemin: No. The “tweak it a little bit”
you could run in SQL Server, and there is code consists of the fact that they made some changes
that you really shouldn’t run in SQL Server… after Beta 2 so that when you access data inside
the database, you access it with the SQL Client
Carl Franklin: (Laughter) That’s good. Provider, but using a special connection string;
and the special connection string just means the
SQL -- or it means to the provider, I’m inside the
Transcription by PWOP Productions, http://www.pwop.com Page 6 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
database. And therefore, you don’t really change Bob Beauchemin: For example, if you got a
much of your code to run inside or outside the service pack against the framework, the SQL
database. There’re things that are different of Server wouldn’t necessarily use it. However, right
course, you wouldn’t -- for example, from a client, before the shipping date or a little bit before, they
like push back a row set to someone else... decided that really what would happen is, SQL
Server would load the latest version of the
Carl Franklin: Right. framework that you had on your machine.
Therefore , any framework changes have to be
Bob Beauchemin: Whereas, you would do that if tested against SQL Server, just like they have to
you are running a stored procedure. be tested against something else.
Richard Campbell: And you obviously wouldn’t Carl Franklin: Right.
pop up a Windows Form in a…
Bob Beauchemin: If you were to put it in the
Bob Beauchemin: Yeah. Absolutely. framework down there, SQL Server immediately
gets it.
Richard Campbell: stored procedure
Richard Campbell: And its totally automatic; you
Bob Beauchemin: Inside of a -- and you don’t have to do anything. You patch your
wouldn’t do that really in a middle tier though, framework on that SQL Server machine and SQL
either. Server has got it.
Carl Franklin: Right. But all the code that you Bob Beauchemin: Right. SQL Server is going to
would be able to run in a middle tier is completely look at the latest version of the framework.
-- is it .NET 2.0 essentially?
Richard Campbell: That’s pretty cool.
Bob Beauchemin: Yeah. It’s .NET 2.0 -- has to
be .NET 2.0 to run in SQL Server in the first Bob Beauchemin: Yes that is, but it is kind of
place. scary because then you are doing framework
changes and conceivably, SQL changes in
Carl Franklin: Okay. So there isn't going to be conjunction with each other they’d better work.
any compatibility issues; in other words.
Carl Franklin: What are some of the…
Bob Beauchemin: Well, there will be a
compatibility issue when the next version of .NET Richard Campbell: Yeah.
comes out
Carl Franklin: …things that you have done in the
Carl Franklin: Oh! sure. But I mean between say middle tier in an extended store procedure that
ASP.NET -- if you are running ASP.NET on the you make lots of sense for them to be there?
same box for example, you’re not going to have
any… Bob Beauchemin: Well. So, here is the
canonical example. When I actually had to do a
Bob Beauchemin: Oh! no. That’s the nice thing talk on what should run the middle tier -- or what
about any of the virtual type architectures is that should run in CLR and what should run in
you can have multiple versions of the same thing Transact-SQL. The folks on the SQL team gave
run at the same time. me this algorithm that they had been working on -
- this process that they had been working on
Carl Franklin: Sure. called the Wagner-Whitin algorithm I think it’s
Whitin-Wagner, somebody wrote me that said it
Richard Campbell: Are we going to be able to was Whitin-Wagner, and I’ve got it backwards.
update the Framework inside of SQL Server the
same way we updated it in Windows? Carl Franklin: Is that Greg Whitin?
Carl Franklin: Ooh! Good question. Bob Beauchemin: The Whitin-Wagner
algorithm; and the Whitin-Wagner algorithm does
Bob Beauchemin: Basically, the original thought production scheduling for manufacturing resource
was that they were going to only have SQL planning systems; and basically, it does a ton of
Server be running with the versions of the data access. It takes all of the information about
framework that it was tested with and it was…. the previous quarters for example, sales
information and puts that together with
Carl Franklin: Right. information about how long things -- how long the
shelf life of things are, and figures out how much
Transcription by PWOP Productions, http://www.pwop.com Page 7 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
to produce for the next quarter. So, it’s a Richard Campbell: I think the big thing that
projection type algorithm that requires a lot of happens when you are down to that kind of time
data, but it also uses multi-dimensional arrays. is, now you can be experimental with those
So that’s something that is difficult to do in projections rather than just running it once.
Transact-SQL.
Bob Beauchemin: Right. And the thing that's
Bob Beauchemin: So they gave me this code nice too is, if you do get a different machine
that ran either on the middle tier, and they configuration, you don't have to run it one place
actually had a client side version of the code or or another; you can't run Transact-SQL in the
DLL or as a CLR proc or as a Transact-SQL middle tier, but you can run CLR in the middle
procedure; and that code runs on the middle tier tier. And you can't usually run COM code on
depending on how far away the middle tier is to Sequel Server but now you can run CLR code in
SQL Server, in about the same amount of time SQL Server and its safe. And this was an
that it runs in SQL server -- and that amount of application that ran completely in safe mode; it
time is about 10 seconds in their test. However, wasn't in any of this unsafe stuff, or we have to
written in Transact-SQL, that stuff runs on my go out to the file system or any thing like that. So,
machine at least, someplace between two and that was the example that they gave me as sort
three hours. of, what turned my head around as far as what
could run, and what should run, and what
Richard Campbell: Wow! shouldn't run.
Bob Beauchemin: Yeah. And this is code that Carl Franklin: Right.
does data access. So, usually people think of
using the CLR and I voice -- said to people that Bob Beauchemin: And you can choose in this
use the CLR with code that doesn't do any data case; you don't have to say one thing or another.
access -- or accesses things outside SQL Server
-- stuff you’d usually do in extended stored Carl Franklin: Yeah.
procedures. But this is a real stored procedure
that you do NAnt that. Now, I thought this was This portion of .NET rocks! is brought by
some kind of a contrived example that they Developer Express - crafting first class tools,
brought up to give me, but my wife used to work frameworks and controls for the .NET Developer.
on manufacturing resource planning type Improve your experience online at
systems, the DBA; and so, I asked her, had she www.devexpress.com.
ever heard of this algorithm, and she said, oh
yeah, we do production scheduling all the time. (Music)
And I said, where do you do it? And she said,
well, we draw out all the data and we push it Richard Campbell: And the bottom line here is,
across the network into a middle tier machine, that we are dealing with a chunk of math,
and we do it there. And I said, about how long complicated enough, that it doesn't make sense
does that take? And she said, well, the whole to try and write it in Tran SQL, but so data
algorithm only takes about an hour and a half to dependent that trying to take it away from
run, but it takes an hour and a half to push the Transact-SQL makes it too slow to use.
data each way.
Carl Franklin: It sounds like the financial
Carl Franklin: Oh, wow! services sector could really use this kind of stuff.
Richard Campbell: Right, of course. Bob Beauchemin: Yeah. So what I tell people
when they say -- first of all, the people that say
Carl Franklin: Especially if you are dealing with that, oh, now we have CLR, I am going to rewrite
a lot of data. Yeah? all my stuff in CLR -- those people are scary,
right?
Bob Beauchemin: Now, that may take
processing power away from things, as you are Richard Campbell: (Laughs)
doing it in SQL Server but to be able to do that in
9 seconds rather than four and half hours or 9 Bob Beauchemin: The first question -- the first
seconds rather than three hours and 9 seconds is statement out of my mouth when I hear that is,
a big deal. why? Why would you ever write something over
in .NET code when it works perfectly fine in
Carl Franklin: That's a huge deal. Transact-SQL? And then there were the other set
of people that said, “You know, this CLR stuff is
Bob Beauchemin: Yeah. nice, and I am happy you have been teaching it
to us for a day but I am really glad to get back to
Transcription by PWOP Productions, http://www.pwop.com Page 8 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
something real again. I would never use this to keep from giving people access to the
stuff” -- and do nothing. I have got actually mail underlying tables -- if you write those in CLR, you
from somebody who had told me that at one point have to give people access to the underlying
in time, about two or three weeks later that said, tables.
"Dear Bob, I am the person that told you that
CLR was completely useless. We run a lab and Richard Campbell: Oh that's interesting.
we just decided, just for the hell of it, to rewrite
one of our stored procedures in .NET code to see Bob Beauchemin: Yeah. Or you have to use the
if it ran any faster; and we wrote the one -- we new “EXECUTE AS” to have it execute as the
took the one with the most calculations in it, and owner. And that has its own set of repercussions
the moment the most calculations ran four times that you have to think about. So, its not for
faster in CLR, we just wanted to tell you that this everything.
happened because we were the folks that told
you it was useless". Now, I wouldn't rewrite Richard Campbell: I had not thought about the
everything in CLR; that's a silly thing to do. fact that “EXECUTE AS” would be the solution to
Transact-SQL is meant for most of the stuff that it that Privileges problem in the CLR; I mean, it
is used for; but the idea is to start by taking the makes sense to me that because you don't know
most formula-centric mathematic-centric thing what the CLR could do; it could do anything. For
that you have and see if that’s worthwhile to write safety's sake, Microsoft has deprived the CLR of
in CLR. And if isn't worthwhile, if the performance that blanket privilege that stored procedures
gain isn't worth it, then you are done; there is no normally have, to do whatever they want. All you
going any further. need is, rights to the stored procedure, and all
other rights are implicit.
Richard Campbell: And I guess the other caveat
on that is, it should be slow enough that you care Bob Beauchemin: Well, the ownership chains
about its speed. work that way. The ownership chains don't check
ownership if the person who owns the stored
Bob Beauchemin: Sure, for something that you procedure and the person who owns the table
run a million times a second. are the same person.
Richard Campbell: Right. We would like to. Richard Campbell: Right.
Bob Beauchemin: So take that, see if it runs in Bob Beauchemin: But the rest of it, I mean you
CLR, if it doesn't, you are done. I mean, you don't wouldn't for example use a stored procedure to
have to do everything or nothing; and then, you access things outside of the database.
can go from there to find the sweet spot where its
not really worth to have to look and convert Richard Campbell: You certainly would use the
everything or transact SQL does this better. CLR for that?
Transact SQL does most data access stuff much
better than CLR. Bob Beauchemin: Oh yeah, that's always been
the province of extended stored procedure; and I
Carl Franklin: Yeah. was fairly surprised about halfway between Beta
2 and the Release of SQL Server, when I went
Bob Beauchemin: And the place where I would into the books online and saw, extended stored
start, which would be the user defined scale or procedures are deprecated, they might not be
user defined functions that are just mathematics, supported in future versions of SQL Server; use
there is no reason to write those in transact SQL the CLR instead. That's the thing that people
when you can write them in a compiled language. usually think of when they think of the CLR -- is a
safer extended stored procedure; but that's not
Carl Franklin: Yeah, they may even already the only thing it could be used for.
exist in some library somewhere, but that -- now
you can call them. Richard Campbell: You mentioned “EXECUTE
AS” has some ramifications around it; I think
Bob Beauchemin: Now, having said that, “EXECUTE AS” is a fascinating potential tool.
because I said that I got my reputation of being What are the problems that come out of that?
one of the most CLR-centric bigots around, but I
am not saying that you write stuff in CLR all the Bob Beauchemin: Oh, there a r e p e o p l e who
time, it's just a silly thing. For example, when you would think that “EXECUTE AS” is just the work
write data access code in CLR code it turns out of a devil. (Laughter). Well, I know one in
that the data access code behaves, for security particular that may or may not ever listen to this,
reasons, exactly the same way Dynamic SQL but yeah, he has written quite a bit on the pitfalls
would behave. So, if you wrote stored procedures of “EXECUTE AS” and it’s funny because the
Transcription by PWOP Productions, http://www.pwop.com Page 9 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
idea of an ownership chain, where if the person Server 2005 features up into features that you
who owns a stored procedure, and the table are can ignore and features that you can't ignore. For
the same, they don't check ownership, is a SQL example, one of the features that you can't ignore
Server and Sybase specific thing. The idea of is the fact that SQL Server 2005 does statement
executing a procedure as the caller of the stored level recompilation now -- not procedure level
procedure, and when you actually have to check recompilation; or that the optimizer works a little
ownership checking against the caller, is differently in this version of SQL Server; but stuff
something that's foreign to say, Oracle and DB2. like XML, CLR, in fact any of the new Transact-
Those guys execute things as the owner of the SQL routines as well. You can ignore those if you
stored procedure all the time; the problem that don't need them; they’re there as enhancements
people have with “EXECUTE AS” is, you are for you, you don't have to -- you are not forced to
really giving away your identity. A lot of auditors use them. But you can't ignore the fact, that does
are auditing products -- audit things by actually statement level recompilation now. That's a
auditing the SUSER__NAME() and that's going to difference that, you know, you are not going to be
reflect the name of the person who the procedure able to turn off around with a switch.
is running as. So, the SQL Server folks actually
put in a different way to audit; but you have to Carl Franklin: W h at are the ramifications of
know to audit that way, which is to audit by Login statement level compilation?
ID. So, people that say, oh, you are giving away
your identity; well, you are really not, but you are Bob Beauchemin: Statement level recompilation
if you look at it the old way. is a good thing because when SQL Server had to
recompile things before they always recompiled
Richard Campbell: Right. at the procedure level. So, for example if you had
a temporary table you could conceivably do a lot
Bob Beauchemin: And “EXECUTE AS” does of recompilations and every time SQL Server
have some interesting repercussions when you recompiled things, in other words, thought up
try and use that with respect to for example, file new query plans for things, it would think of new
privileges if you try and do an “EXECUTE AS” query plans for every statement in the stored
with a file it may not come up the way you want procedure. So, if you had 50 statements, it would
either. So “EXECUTE AS” is a way to get around recompile all 50. In this version of SQL Server,
that but I wouldn't you know necessarily say it’s they recompile on a statement basis and so what
the, you know, panacea for everything either. that means is that people that did all kinds of
tricks like having seven nested levels of stored
Richard Campbell: But the idea here is that the procedures so that each one could contain one
CLR needs to have certain privileges to work on statement -- you don't have to do that anymore.
the database; and so, rather than grant them all
to the users or to the application, give them Carl Franklin: I see.
actual table of access you want to have an ability
inside of that CLR call and say, I am going to Bob Beauchemin: So, it’s nice for that reason
temporarily escalate my rights so that I now have but it also changed the fact that people that did a
the rights to the tables to do the work I need to do lot of tricks now don't have to do them.
and then I will deescalate them later.
Carl Franklin: Bob, are there any issues around
Bob Beauchemin: Yeah, and that phrase that COM Interop and calling COM Objects from SQL
you used, ”temporarily escalates your rights” CLR?
waves a whole bunch of red flags at the auditors
and security consultants and PBA's around the Bob Beauchemin: Well, calling COM objects is
world; they hear that and they say, how do I turn not necessarily a good idea because the CLR
this off? has completely integrated itself into SQL Server;
so all the memory allocations the CLR does are
(Laughter) tracked by SQL Server; all the threads the CLR
uses are SQL Server threads and so forth. COM
Richard Campbell: Leave the CLR off. is completely foreign to SQL Server; and so,
when you are doing that, you are running two
Bob Beauchemin: Yeah, well that's the big thing things that run in the same process, sort of
that I used to hear all the time with CLR, and against each other.
actually sometimes with XML is, this is a nice
feature, it’s not for me, how do I turn this off? And Carl Franklin: Yeah.
now I can tell those people, well it is off by
default; you don't have to turn the CLR off if you Bob Beauchemin: SQL Server keeps no track of
want it off -- its not there, it’s a feature that you COM stuff; and conceivably, you could leak
can use if you want to. I always divide the SQL
Transcription by PWOP Productions, http://www.pwop.com Page 10 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
memory with COM or do other nasty things with Richard Campbell: Isn’t it sort of MSMQ’ish?
COM that you couldn't ever do with CLR.
Bob Beauchemin: Yeah, well it does some of
Richard Campbell: So, all of the protection that the things that MSMQ does; in fact, its funny I
has been put into the CLR to stop these things just did a talk about Service Broker today and
from happening will be circumvented if you somebody asked about what was the difference
started making COM calls through the Interop between Service Broker and MSMQ. So. MSMQ
layer with SQL CLR. has three modes that it can operate in; express
delivery mode, where the messages are kept in
Carl Franklin: That said, you must come across memory or the mode where the messages are
people who have no choice but to do that. not transactional but they are stored on disc or
transactional messages. Service Broker only
Bob Beauchemin: Yeah, that's true; but if they does transactional messages That's all it does;
do that, the better thing for them to do is but what it does, it does really well. So, it’s
conceivably to try and convert the stuff that they transactional messages are a lot faster than
are calling to CLR and use the safe libraries. And MSMQ’s transactional messages. And, if you
when do that, then they are going to get much want to use messaging in conjunction with a
better safety and much better integration with the database, which a lot of people do -- I would say
SQL Server. quite a lot of people do. If you are using MSMQ,
you are using a distributed transaction; whereas,
Carl Franklin: Good. So, short answer, don't do if you are using Service Broker, you are using
it, if you are going to do it. Think about it. local transaction.
Bob Beauchemin: Yes. That would be another Richard Campbell: Why pay the price of
thing that’s on the deprecation with this SPOA distributed transactions if you don't actually need
Create, although I am not sure if it is yet or not -- to?
the ability to create COM objects from SQL
Server directly. Bob Beauchemin: Right, but if you don’t need
reliable messaging, if its perfectly fine to drop a
Carl Franklin: What about calling web services? message here and there, then that's not as
outrageous as it seems. I worked on an MSMQ
Bob Beauchemin: You can call web services. project where they could do that -- then MSMQ is
perfectly fine, it runs faster in that mode than
Carl Franklin: For that matter, doing anything Service Broker would, and is a really good
asynchronously has got to be on the “don't do” choice.
list I would imagine?
Carl Franklin: What if you need to use
Bob Beauchemin: Well, SQL Server didn’t used something that is already asynchronous such as
to allow you to do things asynchronously and a web service or a remote call or something like
people would do crazy things like have their own that that's going to provide a call back? You know
thread pool that ran inside a SQL Server against a stored procedure is something that runs and
other -- you know, against SQL Server’s thread returns a result; it’s sort of, static and
pool. You don't have to do that any more either. If synchronous in nature. Without using Service
you want asynchronous execution, there is a built Broker, is there a way to use the asynchronous
place for it called Service Broker and you can model, the call back model? In other words, can
program Service Broker as a messaging system. you have these objects sitting out there in
You can program Service Broker with .NET code, memory, just handling call backs?
you can program it with Transact-SQL code, you
can program it however you like. In fact I sort of Bob Beauchemin: Well, it’s not a good idea to
like to think of Service Broker as a SQL Server have a whole bunch of objects sitting out in
specific but better version of web services -- memory in SQL Server because those things
more robust version of web services, better is a take up memory.
value judgment.
Carl Franklin: Sure.
Carl Franklin: Well, the Service Broker isn't just
web services; as you said, it's for doing anything. Bob Beauchemin: And they are taking up
I would say it’s a replacement for the memory that could be used by, for example data
asynchronous model in the CLR. buffer or something like that. But if you do that,
what we are going to do is just conceivably at the
Bob Beauchemin: Sure.... time that you are waiting, give up control to the
rest of SQL Server, which is the good thing to do
anyway. But SQL Server expressly forbids you
Transcription by PWOP Productions, http://www.pwop.com Page 11 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
from starting up your own threads except in -- deadlocks other than just data row locks. In
sorry, except in unsafe mode. So, it's not 2000, the only one they talked about was data
something that you would do, but to call row locks. In 2005, they talked about memory
something as a long running thing like web deadlocks and thread deadlocks and Parallelism
service may not be the best thing to do either for deadlocks and even deadlocks due to this feature
scalability reasons; but you can do it if you called “MARS.” So, we went from having one
absolutely feel the requirement to. type of deadlock that we could have; maybe two,
the Parallelism deadlocks are always considered
Richard Campbell: I can't see much reason why a sort of a bug to five or six different kinds of
you wouldn't use Service Broker for that. deadlocks we can have. If you are going to do
this stuff, you sort of have to be real careful about
Bob Beauchemin: Well, Service Broker only it and pick your spots.
talks between SQL servers.
Richard Campbell: It seems to me, we are
Carl Franklin: Well, I am saying in the situation walking towards SQL Server as an application
Richard, where you have a middle tier with an server with Service Broker and the CLR and all
synchronous model that is already working; or this increased functionality. We could put
you have -- maybe not middle tier object, but everything there now.
some library that you have written some DLL.
You’ve got an asynchronous message, you’ve Bob Beauchemin: You said the magic “A” word;
got call backs, they need to be, you need to have right?
a place to plop those things.
Carl Franklin: Yeah.
Bob Beauchemin: SQL Server wouldn't really be
a good idea for that because you would be using Bob Beauchemin: In the first part of t h e
up a whole bunch of threads that could be used ‘Ascend’ program, I was specifically told -- you
for other things. If you were actually going to do know, they didn't tell me what to say but I was
that with more than one person at a time, SQL specifically guided away from using the ‘A’ word,
Server has a limited -- it tries to limit the number because it would sort of, scare off DBA's. The
of threads that are sort of, running around and... DBA's have been used to dealing with something
that -- it's only purpose in life is to serve data and
Carl Franklin: Right. now you’re going to make it an Application Server
and complicate everything and maybe all those
Bob Beauchemin: …that would sort of block stuff doesn’t belong to stored procedures, all of a
threads that SQL Server could use for something sudden they are big middle tier fans, right?
else. Because they don't want to try and manage all
that stuff at the same time, it complicates their job
Carl Franklin: So I guess it's like a common by orders of magnitude.
sense thing, if you find you are bumping up
against the limits of what the SQL CLR was Carl Franklin: Right.
meant to do, maybe it doesn't belong there.
Bob Beauchemin: But conceivably, you could
(Laughing) use SQL Server without any data even in it as a
substrate for building that kind of application -- as
Bob Beauchemin: Sure. For example there are a cheap application server basically -- put an
people that have started to use CLR to produce instance of SQL Express out there and have it
big Datasets in the SQL Server’s memory. And run Service Broker to run messages back and
they find themselves running out of memory real forth -- something like that.
quick and SQL server will just end your
transaction if you are using CLR, if it can’t get the Richard Campbell: And you’ve got a data store,
memory allocation it needs. you’ve got some language to work with, if you
need to write some complicated code there’s a
Carl Franklin: Yeah I wouldn't want to do that. CLR. You have all these mechanisms available
to you. I mean it seems almost very BizTalk like
Bob Beauchemin: Yeah; also because the Lock actually.
Manager is cognizant now of memory and
cognizant of threads, it's conceivable that you Bob Beauchemin: Well, BizTalk is an
could get a deadlock that happens because both application more than a substrate for building
people are waiting for, for example, memory applications. All the stuff in SQL Server really is a
allocation. I was fairly shocked to look into the substrate for building applications rather than the
books online and, you know, have it listed in this applications themselves, just like a database is.
time’s books online, a whole bunch of reasons for
Transcription by PWOP Productions, http://www.pwop.com Page 12 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
Carl Franklin: Right Bob Beauchemin: So, its not a be all and end all
of everything, but what it does, it does completely
Bob Beauchemin: I mean, nobody would build well. In fact, one of the nice things that they did
an application that just used a database and with Service Broker was they re-architected the
nothing else, right? I have classes that people get mail system inside SQL Server. It used to be, if
upset because they have both data access and you went to send a mail message in a stored
database in them and I say, ‘do your users procedure and the mail server was down, you’d
usually access your applications by using Query wait until the mail server came back up to finish
Analyzer; is that their API to the application? How that stored procedure. What they did was, they
many people do that?’ rewrote the mail system. It’s called ‘Database
Mail’ rather than the old system they used to --
(Laughing) SQL Mail. Yeah; Database Mail rather than SQL
Mail. And Database Mail uses Service Broker.
Bob Beauchemin: So data access API’s are one So, not only can you send mail and just sort of
of the things that you have to think of when you fire and forget, and Service Broker will take care
think of any database application. But Service of it for you, but you can actually make that mail
Broker is not BizTalk either. Service Broker is message a part of your transaction.
sort of, more of a -- closer to a BizTalk channel
than BizTalk and Service Broker… Carl Franklin: How easy is it to set up Service
Broker and to use it, I mean what are we looking
Richard Campbell: Right. at code wise?
Bob Beauchemin:…is not trying to be Indigo Bob Beauchemin: Well that’s the interesting
either. Service Broker -- they are working on -- thing. I just did a TechNet webcast today on
apparently, a Service Broker to Indigo channel setting up Service Broker and the operational
apparently was a Service Broker channel for considerations behind Service Broker. And
Indigo. It was actually shown I guess at one of Service Broker security especially, is kind of
the PDC’s or something; but it’s not meant to do difficult to set up -- it’s non-trivial to set up. But
all of the things that Indigo does, nor is it meant security aside, you’re setting up messages,
to do all the things that BizTalk does. You could message types and contracts and you don’t even
conceivably build it, but why build it from scratch have to have message types and contracts, they
when you can buy it? have a default one for those. But you are
basically setting up services in queues. It’s when
Richard Campbell: Right. Well, it makes sense you use it to communicate either outside of your
now; Service Broker is this once -- it’s a little bit Database or outside of your instance, that you
of MSMQ and a little bit of BizTalk and a little bit have to be the most worried about security; and
of Indigo. This one channel that is good at in that case, setting up the security is non-trivial
transactional messaging -- and that’s all. but there are people in the Service Broker team,
and people even outside the Service Broker team
Bob Beauchemin: Yes. that are working on making that easier. For
example, there is a man named Jesus Rodriguez
Richard Campbell: Because that’s what it’s for that works for a company in Florida, that actually
put out something to be able to deploy Service
Bob Beauchemin: Yes, exactly. And you can I Broker apps, and then reconstruct them in other
suppose try and shoehorn it into everything else machines using UDDI, which I thought was pretty
but why bother when you have all those other interesting
products to use as well?
Richard Campbell: That is cool.
Richard Campbell: Yeah. So if you want to go
outside of that one skill you want to use, what are Bob Beauchemin: Is good as long as they are
the other tools? not using it for anything else.
Bob Beauchemin: Sure. And Service Broker Carl Franklin: That’s what I was going to say.
only talks to SQL Server too. So if I had for
example, an application that needed to use SQL Bob Beauchemin: Well, he put out a whole
Server and Oracle for example, there was no way bunch of those tools; he also put out a Service
that I could communicate with Service Broker Broker channel into BizTalk, so you could route
without some kind of intermediate layer in there. messages directly into BizTalk. I believe he’s a
BizTalk MVP -- or put out a Service Broker task
Carl Franklin: Exactly for SQL Server Integration Services; and so, lot
of interesting little components that he put out.
Transcription by PWOP Productions, http://www.pwop.com Page 13 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
Carl Franklin: So it doesn’t take very much code be notified when those things happen. And what
is what you’re saying? we are going to try and do is give bigger
discounts or first shot to the people that have
Bob Beauchemin: Well, it doesn’t take a whole actually signed up and we promise not to spam
lot to set up a simple Service Broker application. you too much...
It takes a little more to set up a complex Service
Broker application. But another thing that Service (Laughing)
Broker is used for, is to notify people of events,
database administrators specifically. So I think Bob Beauchemin: I really hate signing up to
that database administrators are already getting places that send a lot of spam, but if -- you know
more into that or they will, whether they want to just a few notifications about things that are going
or not. on, that’s okay, if I don’t want those, I can delete
them.
Carl Franklin: Yeah
Carl Franklin: Yeah also, I mean I do that right,
Bob Beauchemin: You can, for example, have at franklins.net but I always feel you got to give
Service Broker catch events that are usually them some content too. You got to give them
caught by SQL Trace or SQL Profiler. You can something; something that you’re thinking about,
have Service Broker catch DDL events; so one of something you’re working on, something that will
the nice things there, for the database save them some time.
administrator too.
Bob Beauchemin: Yeah. And that’s what our
Carl Franklin: Very good. This portion of .NET blogs are about too. I’ve had blog on SQL Server
Rocks! is brought to you by ‘Developer Express’, 2005 since I started teaching it, which was -- or
crafting first class tools, frameworks and controls since I started working with it which was 2003.
for the .NET developer. Improve your experience Now some of the things I couldn’t say about it,
online at www.devexpress.com. now I can; but they have always been little hints
and tricks and things that people didn’t know
(Music) about SQL 2005. Like, I sort of got the
impression some of those things that I said were
Carl Franklin: Bob, where are you located? things that astounded you guys too, that oh, I
didn’t know SQL Server did that, or I’d heard of
Bob Beauchemin: Oh! I’m in Portland, Oregon. this, I wondered what it did.
I’m finally home now for a while. The last 2 years
have sort of been this blur of people and places Carl Franklin: It doesn’t take much to astound
and really nice places everywhere but teaching me in SQL Server.
SQL Server and I’m sort of, a little bit more home
these days. I’ve been working with SQL skills (Laughing)
since about September and since then I sort of --
my travel workload and my travel load has sort Bob Beauchemin: Really!
of, gone down quite a bit. In fact, we are just
starting in SQL skills something that I think is Carl Franklin: Richard, I’m not so sure.
pretty exciting, which is that Kimberly and I will be
doing events together. We will be doing the Bob Beauchemin: I remember you being a
events where she’ll have a bunch of people in a pretty good database programmer.
room and do some topics and I’ll have a bunch of
people in a room and do other topics. And then Richard Campbell: Yeah I was, I just hadn’t
we’ll swap off when we get to topics that are done it in quite a while so…
relevant to the other group of people. So I’m
looking forward to those, those are coming up. Bob Beauchemin: Yeah. Well, like anything
else, but yeah I remember when you were doing
Carl Franklin: That sounds great. that
Bob Beauchemin: And we also have that book Richard Campbell: Yeah. I had my day.
coming out soon so that will be fun to see how
well that does too. (Laughing)
Carl Franklin: And we can find these at Carl Franklin: Richard on the other hand,
SQLskills.com? dabbles in it everyday.
Bob Beauchemin: Y e a h . www.sqlskills.com Richard Campbell: Well, Steve Forte and I have
website, and in fact you can go up and sign up, to been collaborating on a session that came out of
Transcription by PWOP Productions, http://www.pwop.com Page 14 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
a discussion in a hot tub and I can’t remember Carl Franklin: And you can always drop into the
what country we were in. SQL CLR and use the System.Xml namespace,
I’m sure, right?
Carl Franklin: Now that’s just sad; now come on.
You were in a hot tub in some country I can’t Bob Beauchemin: Yeah, conceivably you could
remember; maybe it was Sri Lanka, maybe it was do that too. That’s one of the namespaces or
Kuala Lumpur, I had a Martini. that’s one of the DLL’s that has gone through the
reliability testing; so, all of those things unless
Richard Campbell: Yeah, well you know this is you go out of process, are safe to do.
what happens to you….
Carl Franklin: So when would you use what?
Carl Franklin: Just shut up man.
Bob Beauchemin: Okay. Well, some people like
Richard Campbell: …you work it all the time. XSLT. They are not big fans of XQuery, they are
But we got into the discussion about CLR and big fans of XSLT.
whether it was a good idea or not and one of the
things that Steve ran across that got him excited Carl Franklin: Yeah.
was the ability to pull a query plan in XML.
Bob Beauchemin: And SQL Server doesn’t
Bob Beauchemin: That’s a totally different have any built in capability to do XSLT in
feature, but yep you can do that. That’s Transact-SQL. But if you want to do XSLT, it’s
something that really should be used sparingly pretty easy to write a five line program that calls
though like any kind of query hint but the idea in, and call that program as a stored procedure
that if you are one of a million people, that and do transforms right within the databases if
service pack one comes out and may make your that’s what you want to do.
query run slower, right they’ll make like 99% of
the queries run faster and you knew what the old Richard Campbell: Would you be going out to
query plan was and you go, “How do I get that the CLR to do that, right?
query plan back?” There’s something called
planned forcing where you can actually get out Bob Beauchemin: Right. You would go to the
the plan in XML format and use that plan as a CLR to do that. Another thing that we did was,
query hint. So, the nice thing about having it in that the Schema support, the XML Schema
XML format too is that if you know XML and it support in SQL Server doesn’t support every
doesn’t have to be XML on SQL Server, you can Schema production that there is; and so, what we
analyze it offline. were able to do, or what a cohort of mine, Dan
Sullivan was able to do at one point, when we
Richard Campbell: Right. Of course now SQL wanted the office Schemas into SQL Server, was
Server has great DML handlers, so it is fairly to write a stored procedure that just took in a
easy to write an XQuery to pull the important bits bunch of Schemas, ordered the Schemas
of data out of the query plan, like its cost, correctly and did little tweaks to the Schemas
expected number of rows, even the type of query like, take off the lax validation which SQL Server
that the query plan thinks it is. does to support the Schemas and actually
change the Schemas a little bit, but enough to get
Carl Franklin: Well, let’s talk some more about them into SQL server -- and he did that as part of
the XML features in 2005. I know that it was on a stored procedure; that we just read these files
your list of things to talk about. What specifically off the file system.
has been peaking your interest in the XML world?
Carl Franklin: Cool!
Bob Beauchemin: Well, there’s XML for
Developers, and there’s XML for DBA’s. The Bob Beauchemin: So, yeah, you can mix or
XML for Developers is based upon the fact that match those things. Now there is also XML for
this version of SQL Server has a built in XML DBA’s. The best XML for DBA, or the best XML
data type that acts, or that goes by most of the for analysis of database things feature that I have
standards in the ANSI standard SQL 2003 -- I’ll ever seen, is the fact that you can send the query
try that again -- It goes by most of the ANSI SQL plans now to a friend. So, you used to have the
2003 standard for XML data type. It also has a graphic query plan in SQL Server 2000, and the
built in XQuery language as well. So you can graphic query plan was really cool until you start
process your languages in XQuery; and since to talk to somebody over the phone. Then they’d
XQuery is a functional superset of XPath, that say, send me that. And you’d be sending like,
means if you know XPath but not XQuery, you screen shots and finally you just give up and
can use XPath also. send them the non-graphic query plan, and they
have to learn to read that. Now, in this version of
Transcription by PWOP Productions, http://www.pwop.com Page 15 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
SQL Server, because the query plans are XML, Bob Beauchemin: When you’re spitting out XML
the Query Plan Show stuff is just basically I think from a proc, you have two choices now. It used to
an XSLT transform. And you can save those be that you could only spit out XML on a stream;
query plans out in a format with a .SQLplan suffix because SQL Server really didn’t have any XML
and send it to a friend in mail. The friend double data type. So, you sent out this thing -- in fact
clicks on SQLplan and it comes up right in SQL you’ve probably seen it in Query Analyzer where
Server management studio with exactly the same you do the select for XML type deal and you get
pop up -- you know, properties and all of that stuff back this nice GUID Column ID.
that the original query plan had on your desk.
That’s probably the coolest thing and that’s Carl Franklin: Right.
facilitated by the fact that they save those query
plans out of XML. Bob Beauchemin: And what that GUID actually
was, was a signal to the client side API’s that
Carl Franklin: Then what about performance of hey, this isn’t any SQL row set here; this is a
XML? stream. It sort of looked like a row set in Query
Analyzer because that was the only way that
Bob Beauchemin: The performance of XML Query Analyzer could show it; but it really was a
depends on what you use it for; so, if you are stream. Since this version of SQL Server
using XML to replace relational, that’s probably supports XML data type, you can create instead
not a good idea. But if you’re using XML to be of your stream, a one-column one-row, row set if
able to for example, look at semi-structured data, you want to that has XML; or you could have a
or using XML for sparse properties in data, that’s multiple row, row set that has XML in every
not a bad idea. And so, it really depends on what column. Also, the four XML calls in SQL Server
you are doing with it just like everything else. I 2005 have been greatly enhanced. I mean, the
always hate to wimp out and have an answer number of enhancements are just too many to
like, “Well, it depends” say; and the biggest enhancement is the addition
of a new for XML dialect called “Select for XML
Carl Franklin: Yeah, well it does. Path” that let’s you put out a combination of
elements and attributes using namespaces, using
Bob Beauchemin: But it really does. an XPath syntax -- XPath like syntax within the
column names. So, that’s a pretty interesting
Richard Campbell: There’s also structures you feature, if you’ve ever used XML EXPLICIT --
could put together in XML that just don’t make XML EXPLICIT sort of takes hours to describe
sense in SQL Server, I mean this and I remember one of the XML books actually
multidimensional data that you can’t write to a had like, three or four chapters on XML
table easily, but XML will handle it. EXPLICIT. 95% of the queries can be done now
really simply with XML Path. So, really good
Bob Beauchemin: Sure, or things with sparse support for sending out XML to the outside world.
properties; like I have a million properties on And if you really want to get off the edge with
each element and they are all different. In SQL this, SQL Server supports XML Web Services
Server, if you designed it that way as pure directly. You can expose anything you want as a
relational which you could do, you would have web service and actually consume it with SOAP
99% of the rows having null values in that calls.
column; whereas if you have it in XML, you could
conceivably put in those sparse properties and Carl Franklin: Right.
search for them with Xquery -- and by the way,
the XQuery engine in SQL Server really uses the Bob Beauchemin: That makes most people
relational engine. If you look at a query plan that nervous because they think about, outside the
has SQL and XQuery in it, it just is a regular SQL firewall. I’m not saying you should put your SQL
Query Plan. They had to add I think, five new Server outside the firewall. But if you, for
physical operations just to be able to support example, have an IBM Mainframe that has a
XQuery, but otherwise, it’s a SQL Query Plan, Legacy Application that runs on it, that can
that that thing is executing consume web services and it’s inside your
firewall, it’s just as secure to call SQL Server
Carl Franklin: Now what if you are spinning out Web Service inside the firewall as it is to call to
XML from a stored proc; in other words, instead port 1433.
of writing data driven ASP.NET pages that turn
into an XML document, what about just making Carl Franklin: T h at’s something I’d never quite
something -- you know, making a stored understood; everybody would say, if you’re inside
procedure you can call directly from a browser. the firewall use Remoting, if you’re outside the
firewall use Web Services. Turns out Web
Services are not only more, easier to do inside or
Transcription by PWOP Productions, http://www.pwop.com Page 16 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
outside the Firewall, they’re actually more robust first because who’s going to be most resistant to
too. using XML -- all the DBA’s. And who’s going to
be the ones that use DDL Triggers all the time?
Bob Beauchemin: It all depends on the protocol Well, the DBA’s -- and actually row function for
you’re using and the amount of retries you want the DBA’s that basically took the XML apart and
to do too. made a row set out of it -- if that’s what they
really want. But the idea is that, that comes out in
Carl Franklin: And the type of application -- are XML format, the query plans come out in XML
you turning a lot of data? Web Services are just format, the Data Tuning Analyzer uses XML
damn easy. format. So, pretty much everything the DBA’s are
going to use is XML format at this point. Now,
Bob Beauchemin: Sure. And they made the that means that it’s really good for DBA’s, or it’s
Web Service so robust inside SQL Server and really worthwhile for DBA’s to learn XML, even if
actually put so many features in it, it emulates they don’t like it at first just because it’s a nice
Sessions if you want i t to do that. You could format, that everything seems to be coming out in
conceivably write and an ADO.NET Data these days, and it will make their job easier.
Provider that just talked to SQL Server using
HTTP -- if you really like HTTP as a transport. Carl Franklin: Is it easy to integrate Schemas?
Carl Franklin: Yeah if you really want to blow Bob Beauchemin: SQL Server has built in
your data. support for Schemas.
Bob Beauchemin: Yeah. Carl Franklin: Okay.
Carl Franklin: Sure. Bob Beauchemin: So you can put collections of
Schemas in. That was another interesting story;
Bob Beauchemin: So, any of those things -- and they started off by putting in one Schema. You
I’m just probably touching the surface of the XML could put in one Schema at a time and then type
support in SQL Server. But I started off by a column using one Schema. But if you have
thinking that XML support was just mostly for ever seen a WSDL document, there is no way
developers -- and like I said, I’ve had DBA’s that that you could type a WSDL document with one
say, “How do you turn that off?” But just trying to and only one Schema. So what they did was
enumerate the DBA tools that are now exposed introduced the concept of Schema Collections,
in XML, the big one that I always felt was sort of which are basically just SQL Objects like tables
a nefarious plot was, this version of SQL Server or views are, but they actually contain collections
has DDL Triggers and in a regular Trigger you of Schemas; and then you can use a Schema
can get the inserted and deleted tables to figure collection to type any document that you want to.
out what happens. In a DDL Trigger, what do you
send, to tell people what’s happened? So what Carl Franklin: Very nice.
they do is, they send a piece of…
Bob Beauchemin: Yeah.
Richard Campbell: You’re adding a column
right? I mean, that kind of trigger -- you’re Carl Franklin: So Bob, when is your book
dropping a table, that’s DDL. How do you send coming out?
that?
Bob Beauchemin: It’s due to come out around
Carl Franklin: Can you define DDL for our non- May 1 st. It was originally due to come out May
DDL, OLE listeners? 15th, and actually we thought it would come out a
little sooner than that when we first handed in the
Bob Beauchemin: Oh sure. It’s Data Definition book. I am doing this book with a guy named Dan
Language. So, Create Table would be DDL, or Sullivan, that I used to work with at
Create View would be DDL. DevelopMentor. And Dan’s been a really good
person to have as a co-author as well. But what
Carl Franklin: Okay. we did was, we were worried about the page
count. We had told Addison last week, we were
Bob Beauchemin: And so, when you want to tell going to produce about 600-page book. And so,
people what has happened in a DDL Trigger, we were concerned about being able to make the
there’s no standard real format that you can send page count. So, we just sort of, went off into lots
with columns and rows all the time. So what they of depth about every topic we could think of to
did, was they sent -- they made a system defined cover. And one of the reasons why the book is
function called event data; and event data returns going to be late is, because I believe it’s
XML. Now, I thought that was a nefarious plot at
Transcription by PWOP Productions, http://www.pwop.com Page 17 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
someplace now between 1150 and 1200 pages
long. Carl Franklin: Do you get it out, like Starbucks or
Seattle’s Best or anyplace?
(Laughing)
Bob Beauchemin: Oh no, we actually buy stuff
Bob Beauchemin: Yeah. That wasn’t where we and make it at home and my wife makes it way
started, but at that time we really couldn’t figure too strong. Which is probably why I am talking
out how to cut it back in a nice way either. this fast right now.
Carl Franklin: That’s not a book. Carl Franklin: Do you like, have a French Press
and roast your own beans, are you that nuts?
Richard Campbell: The SQL Odyssey.
Bob Beauchemin: No, we are not that much into
Bob Beauchemin: Yeah. So that’s a lot of it. We have like, a Mr. Coffee and we just -- you
material, so the only thing that I can think of that know, I do it up every morning.
the people are going to have a problem with is
figuring out how to carry it around, maybe they (Laughing)
will actually put it out on -- or have a CD based
version of it sometimes, so it’ll be easier to carry. Bob Beauchemin: But I will come in with this
huge cup of coffee and that’s sort of, one of the
Carl Franklin: Put it in the database. things that I do miss while I’m on the road is to
have really nice cup of coffee every morning.
Bob Beauchemin: Yeah really, put it in the
database and you can do searches on it. That Carl Franklin: Yeah.
actually wouldn’t be a bad idea.
Bob Beauchemin: I usually have the coffee
Carl Franklin: Search by page. (Laughter) wherever I am.
Bob Beauchemin: Yeah. I think that’s been Carl Franklin: You notice I didn’t ask him
done with somebody’s book once; I forget whose Richard, do you drink coffee? The guy wrote a
book that was. 1200 page freaking book, he obviously drinks
coffee, right?
Carl Franklin: All right, so we will have a words
table -- no, I am just kidding. (Laughing)
(Laughing) Bob Beauchemin: Well, Dan and I wrote it right?
Dan did write the half. I think he wrote just a little
Bob Beauchemin: Sure. Put it in there so you less than half but I guess that’s the idea.
can search it with full text search right?
Carl Franklin: I didn’t mean to take credit away
Carl Franklin: I am sensitive to over from Dan.
normalization, so I live through normalization
table -- table normalization hell. Bob Beauchemin: Yeah. Book writing is kind of
interesting and I am not sure I’ll be doing a lot
Bob Beauchemin: Yeah. Well, I mean one of my more of it either because it’s a lot of work. The
problems with books has always been that you reason why I wrote this book mainly was because
can’t search on them. And it would be really nice I could then get one, early access to the bids and
to have the thing in a searchable format. But two, the ability to write a class on it. So, that was
anyway, it’s coming out about May 1st and it will the nice thing, is that I got to write the class.
about 1150 pages.
Carl Franklin: Speaking of classes, when is your
Carl Franklin: Good God, man. next class?
Bob Beauchemin: So, people thought they read Bob Beauchemin: My next class is actually
everything in our last book, the first book at SQL going to be two weeks from now in India. I will be
Server 2005, we sort of, went overboard in this India for two weeks.
book.
Carl Franklin: Do you have public classes or are
Carl Franklin: Where do you get your coffee? you mostly doing on sites on demand?
Bob Beauchemin: Actually, I start with a couple
of big cups of coffee every morning.
Transcription by PWOP Productions, http://www.pwop.com Page 18 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
Bob Beauchemin: We mostly do private classes Carl Franklin: Well Bob, any last minute words
but we are going to start to offer public classes of advice or wisdom or any thing else you want to
real soon. promote maybe?
Carl Franklin: Okay. Bob Beauchemin: No I don’t have any. I am not
really great at gratuitous self-promotion and that
Bob Beauchemin: Y o u can look upon the was probably about as much as gratuitous self-
sqlskills website. When we finally get things promotion promotion as I could do.
together, we might have a little bit of website
reorganization too. We’re going to have some (Laughing)
public classes that we offer. But we’ve normally
offered this things called Immersion Events, Bob Beauchemin: And as far as words of
which are these trainings -- immersion is a pretty wisdom, I don’t really consider myself all that
good name for it too where you’re sort of wise either, right.
immersed in the SQL Server for as long as you
can stand it. And that’s the Event that we were Carl Franklin: Oh well, you’d be surprised.
going to start to do together, is the Immersion
Events. So, we are going to have Immersion Bob Beauchemin: People are not going to follow
Events and probably some public courses too. anything I do; all I do is provide information. Like I
used to say when I showed up at conferences to
Carl Franklin: Okay. Hey, maybe you could do talks, I am here to be the technical
consider doing a class in New London? entertainment.
Bob Beauchemin: Yeah. That would be cool. (Laughing)
New London. Where?
Bob Beauchemin: So, just sit back and listen to
Carl Franklin: Connecticut. East Coast. something technical and ask any question you
want no matter how off the wall it seems.
Bob Beauchemin: Oh! Really? That’s where I
am from. Carl Franklin: Well, you have the true mark of a
good technologist which is, you don’t let your ego
Carl Franklin: You are from Connecticut? get in the way of your work, which is great.
Bob Beauchemin: I am not from New London Bob Beauchemin: Well, I try not to.
but I am from Connecticut.
Carl Franklin: And he says, what ego? All right
Carl Franklin: Where in Connecticut? Bob, I am glad you were on this show. Richard,
thanks for asking the majority of the questions
Bob Beauchemin: Well, I originally was born in under SQL topics.
Norwalk but I have lived most of my life around
the Hartford area. Bob Beauchemin: Oh! Thanks for having me on
this show was really fun.
Carl Franklin: Oh no kidding?
Carl Franklin: Excellent. And we will talk to you
Bob Beauchemin: Yeah. Before I started -- it listeners, next week on .NET Rocks!.
was before working, and then I went to work in
Boston for a while and then moved out to Seattle (Music)
and then moved out to Portland. I have lived in
Portland, since about 1981. The first half of my Geoff Maciolek: .NET Rocks! can be found
life was spent in Connecticut. o n l i n e a t www.dotnetrocks.com and at
msdn.Microsoft.com/dotnetrocks. .NET Rocks! is
Carl Franklin: Excellent. edited each week by Geoff Maciolek, that’s me,
and Carl Franklin, who is also Executive
Bob Beauchemin: So, that’s where you’re Producer. All music heard on .NET Rocks!
located, I take it. including ToyBoy, the theme song, is created and
produced by Carl Franklin and Franklin Brothers
Carl Franklin: Yeah, that’s what we are. Band. Carl never sleeps. .NET Rocks! is
produced for franklins.net by PWOP productions
Bob Beauchemin: Oh! Interesting. - providing professional audio and podcasting
services online at www.pwop.com. PWOP - it’s
time to get your impact back.
Transcription by PWOP Productions, http://www.pwop.com Page 19 of 20
Bob Beauchemin on SQL Server 2005
April 14, 2006
(Music)
Transcription by PWOP Productions, http://www.pwop.com Page 20 of 20
Related docs
