Article: What's new in SQL Server Yukon

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: What's new in SQL Server Yukon

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-28 : 18:01:01

Yukon is Microsoft's codename for the next version of SQL Server, due to be released in 2004. Tim Anderson talks to Euan Garden, Microsoft's Product Unit Manager for SQL Server Tools.

Article Link.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-29 : 03:10:16

Wow... I just hope this company can afford the upgrade... The improvements sound great. And I like that they continue to improve T-SQL, with better error handling etc.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2003-08-06 : 14:06:34

some good info....but I am desperate to find out some of the replication features. we are replicating from sql2000 to several msde clients...across the internet...and it was a pain (and still is) getting this to work. does anyone know of any enahancements for replication?

thanks,
dw

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 14:39:24

What the hell is he talking about?

quote:

In a lot of places in SQL 2000 you have to use magic stored procedures to do things.

Magic Stored procedures?

What I want to know is if they'll be releasing the Miracle extensions...

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 14:43:56

And then there's this..

quote:

I can CREATE PROC AS EXTERNAL as opposed to CREATE PROC AS with T-SQL in it. And in the EXTERNAL command we point at a class and a method name inside an assembly. There's a T-SQL stored procedure header, it uses T-SQL types, we've got nullable types, and we use all of that inside the stored procedure.

That's all DB2 had for years...it's like they're going the wrong way...what's the benefit of moving away from the server?

[EDIT]
EXTERNAL stored Procedures in DB2 was painful

it wasn't as nice and simple like CREATE PROC mySPROC..

No..JCL, register to the catalog, write a COBOL (REXX, PL1, ect) Program..)

Painful
[/edit]

Brett

8-)

SELECT POST=NewId()

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-06 : 17:37:44

Hi, Brett

I can see you like DB2

. Me too.

But, should be pointed that SQL Server already allows you to write specialized procs, using any language able to create DLLs, using xps. So, in some situations you're already out of the server.

regards,

Sérgio Monteiro
Trust in no Oracle

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-06 : 19:44:15

You're not moving away from the server. Instead, you're setting up the assembly as PART OF SQL Server, then calling the code from it. Also, it's a .Net assembly, so the code runs in a managed environment and is a lot more reliable than a completely separate (external) process.

There's a difference in what MS means by EXTERNAL than what IBM means by EXTERNAL.

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-06 : 20:27:18

The EXTERNAL procedure will be largelly lke the extended procedure of SQL 200 days. BUT, as Rob poited out, they will execute in managed and safe environment, while extended proc can get you crazy.

Just now I have a XP I wrote to comunicate SQL Server with IBM TSM, without TDP, that is driving me to an abyss. I hope in Yukon it will be easier, as long as the framework fo SQL Server and VC are the same.

regards,

Sérgio Monteiro
Trust in no Oracle

chadmat
The Chadinator

1974 Posts

Posted - 2003-08-06 : 21:05:19

quote:
as long as the framework fo SQL Server and VC are the same

It is extremely integrated with Whidbey. When you build a method for use as a stored proc, it will automatically generate all of the T-SQL stub/EXTERNAL stuff for you, so it is just like writing a method in VS.Net.

http://www.clrsoft.com

Software built for the Common Language Runtime.

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 08:54:40

Hey, thanks for the responses....

I've a lot to learn about this stuff...

I still want to know what "magical stored procedures" are all about...

Any good sites for .NET out there?

Brett

8-)

SELECT POST=NewId()

euan_garden
Microsoft SQL Server Product Team

34 Posts

Posted - 2003-08-21 : 21:36:47

What I mean is the undocumented SP's, XP's and DBCC calls that we use in the UI, and that other tools vendors use, to allow you to manage the server.

-Euan

quote:
Originally posted by X002548

What the hell is he talking about?

quote:

In a lot of places in SQL 2000 you have to use magic stored procedures to do things.

Magic Stored procedures?

What I want to know is if they'll be releasing the Miracle extensions...

Brett

8-)

SELECT POST=NewId()

euan_garden
Microsoft SQL Server Product Team

34 Posts

Posted - 2003-08-21 : 21:38:10

As others have said this refers to how to access a SQL Server Procedure thats running as a CLR object. I was demonstrating that all we have done is extending the existing Create Proc syntax a little, so that devs/dbas are not having to learn something totally new.

-Euan

quote:
Originally posted by X002548

And then there's this..

quote:

I can CREATE PROC AS EXTERNAL as opposed to CREATE PROC AS with T-SQL in it. And in the EXTERNAL command we point at a class and a method name inside an assembly. There's a T-SQL stored procedure header, it uses T-SQL types, we've got nullable types, and we use all of that inside the stored procedure.

That's all DB2 had for years...it's like they're going the wrong way...what's the benefit of moving away from the server?

[EDIT]
EXTERNAL stored Procedures in DB2 was painful

it wasn't as nice and simple like CREATE PROC mySPROC..

No..JCL, register to the catalog, write a COBOL (REXX, PL1, ect) Program..)

Painful
[/edit]

Brett

8-)

SELECT POST=NewId()

euan_garden
Microsoft SQL Server Product Team

34 Posts

Posted - 2003-08-21 : 21:40:48

Correct, here is an example of a very simple but somewhat contrived proc in VB.Net

imports System

Public Class CSQLMaths
Public Shared Sub Add(ByVal In1 as Integer, ByVal In2 as Integer, ByRef Answer as Integer)
Answer = In1 + In2
End Sub
End Class

And here is how I load it and call it in SQL Server:

use sqlclrdemos
go

create assembly sqlmaths
from '\\komodo-demo3\c$\yukon\demos\sqlclr\live\sqlmaths.dll'
go

create procedure usp_mathsadd(@In1 int, @In2 int, @Answer int output) as
external name [sqlmaths]:[CSQLMaths]::[Add]
go

declare @Answer int
set @Answer = -999999
exec usp_mathsadd 3,2, @Answer output
print 'Answer is ' + cast(@Answer as char(6))

Whidbey makes this very easy.

-Euan

quote:
Originally posted by chadmat

quote:
as long as the framework fo SQL Server and VC are the same

It is extremely integrated with Whidbey. When you build a method for use as a stored proc, it will automatically generate all of the T-SQL stub/EXTERNAL stuff for you, so it is just like writing a method in VS.Net.

http://www.clrsoft.com

Software built for the Common Language Runtime.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-21 : 22:21:20

Thanks for demo Euan..

But that just makes me want to throw up!!!!!!!

I only have 4 requirements for Yukon..

1) Error handling in TSQL
2) A Boolean datatype!!!
3) Complete User Defined Data Type support..eg. CREATE TYPE Point (Int x, int y)
4) Database & relation constraints..

If they implement 3 & 4 we can do away with the 90% of middle tier code!
If they do all 4 then SQL Server would almost put every other DB vendor out of business....

DavidM

"SQL-3 is an abomination.."

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-08-21 : 23:16:33

I totally agree David

Just think about all the lucrative consulting work there will be going in and fixing up companies performance NIGHTMARES after they let their VB developers near the DB

I can't wait!

Damian

euan_garden
Microsoft SQL Server Product Team

34 Posts

Posted - 2003-08-25 : 21:31:15

Why throw up?

You will get 1 in Yukon. 3 you get through the SQLCLR DataType extensions,w hcih also means you get 2 if you are willing to write the code.

I think I know what you mean my 4 but how about explaining a little?

-Euan

quote:
Originally posted by byrmol

Thanks for demo Euan..

But that just makes me want to throw up!!!!!!!

I only have 4 requirements for Yukon..

1) Error handling in TSQL
2) A Boolean datatype!!!
3) Complete User Defined Data Type support..eg. CREATE TYPE Point (Int x, int y)
4) Database & relation constraints..

If they implement 3 & 4 we can do away with the 90% of middle tier code!
If they do all 4 then SQL Server would almost put every other DB vendor out of business....

DavidM

"SQL-3 is an abomination.."

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-25 : 23:40:24

I am very glad to here that Error handling made the list.

The fundamental problem I have with this CLR stuff, is that I feel it is a cop out for not being able (willing?) to make the product more relational.

As you highlighted, a User Defined Type has to be created outside the DBMS! Although I am glad they have at least given us that option (first thing I do will will be to make a better DateTime type), it would have given them huge browny points in the DBA world for this functionality to be at its core and would have finally killed of the OO-DBMS madness once and for all.

The boolean data type, the only data type required by a relational DBMS, appears never likely to make it into SQL Server until they remove the "SQL" from its name. ie. NULL's just cause so much pain....

When I started looking at RDBMS implementation, back in 1991, I was fortunate enough to be shown the ropes by a mathamatician and a PhD computer scientist. We had a copy of DB2 (I don't even think it was called DB2 back then) and Oracle and my "homework" was to pick out all the relational flaws in them. Besides NULL's and booleans the next thing I picked out was a total lack of Cardinality support, in both RI and tuple count.

Even today and I cannot declare a relationship to be anything other than one-to-one, one-to-many or many-to-many (Before anybody starts I know it I left out the "none" parts..). You have to use triggers (Or in SQL2K, a UDF) if you want say a one-to-three. This is a very sad state of affairs.

This also applies to how many rows are in a table. As a consequence, they have left out both relation contraints and database constraints. As an example is it is very hard to express a simple business rule such as "Only suppliers in London can supply to more than 10 red parts". Today this constraint is implemented in the middle-tier or a stored proc but it should be part of the database definition ie. It should be declarative and not procedural in nature.

The first DBMS vendor to implement, at its core, UDT's and database and relation constraints will crush all in its wake. I for one will welcome our new overlords!**

Whenever this happens the DBA will be the most prized asset in any business.

**Apologies to the The Simpsons

DavidM

"SQL-3 is an abomination.."

Lavos
Posting Yak Master

200 Posts

Posted - 2003-08-28 : 20:31:37

[qoute]

The boolean data type, the only data type required by a relational DBMS, appears never likely to make it into SQL Server until they remove the "SQL" from its name. ie. NULL's just cause so much pain....

[/qoute]

I know I might regret this, but aren't there a couple more data types than boolean required by a RDBMS? I realize that it's not exactly what you mean, but SQL Server still has bit which I guess could be considered boolean from the machine's point of view :)

I'm not sure what you mean by the null's line either. True, they can be a pain, but are you saying to remove them? Or expand them to include the NA and UNKNOWN null types?

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-28 : 22:33:59

Lavos,

The relational model has 2-valued logic at its core. As you know a Boolean data type is the only required data type for this logic to work and hence so does the relational model. I can construct integers, strings, dates etc.. from the Boolean data type, which is exactly what computers do now.

Because of NULL's in the SQL standard, SQL Server (and every other SQL implementation) works off 3-valued logic. So the BIT data type in SQL Server is close but still far away from a true Boolean type. Nearly every programming language has a boolean type.. Try setting a boolean to null in C, VB, C#, Java, etc.. and see what happens...

Haven't you noticed that you always have to have an equality operator to test for the BIT type.


Declare @Bit BIT
SET @Bit = 1

IF (@Bit = 1)
   PRINT 'Bit is 1'

--If SQL didn't have nulls... 
SET @Bit = true
IF (@Bit)
   PRINT 'Bit is true'

--This also applied to the WHERE clause..

Better minds than mine have tried to come up with ways around the missing information problem... If I remeber correctly, Date (& Darwen?) propose a special default value ? that can be defined for each data type. The important point is that it is value and not the 8th wonder of the world...

? == ? -> True! vs NULL == NULL -> Maybe!

A good DEFAULT is "mostly" not that hard and might even require people to think a bit more about the domain for each column...

DavidM

"SQL-3 is an abomination.."

Lavos
Posting Yak Master

200 Posts

Posted - 2003-09-02 : 21:11:34

I'd be careful with asking me to set a boolean to null in my language of choice, there's probably more than a few where it'd be perfectly acceptable. (Even then, I could fake it in Java, C++, or C# if I play with the type of the variable that I'm using to hold the value. Plus, most languages just don't have the same concept of NULL as SQL and therefore have just two state booleans built in.)

I'm still not quite sure why having a boolean data type would be a panacea, in the same way I don't see why removing NULL would make a RDBMS better. AFAIK, Codd had several different breeds of NULL in his idea of a relational database. Can they be a pain to deal with if used improperly? Sure, but so is a 15 page trigger that someone thought should have a few cursors in it :O

I have noticed the equality operator, but I give it the same consideration as the change in semantics going from a C++/C style if statement to C#, and consider it minor. IIRC, since true and false are not reserved words, you could always create some system user defined functions called TRUE() and FALSE() to compare the bit to.

Regardless, I'll admit my knowledge on set and relational theories are a little weak, but I'm just not seeing having a boolean data type rating high on a list of things to have. (don't get me wrong, I think it'd be a nice feature, but only nice in the same way as having unsigned types or more precise bit vector handling. Of course, I'm also the one who got called unimaginitive for hoping for a do {} while() looping construct.)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-09-02 : 21:37:27

A boolean has only 2 states.
Don't get confused between values and variables...

How do use use NULL's properly?
The latest thinking seems to think that Codd got NULLs wrong.

If you think the difference between 2-valued logic and 3-valued logic is minor, then so be it...

DavidM

"SQL-3 is an abomination.."

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources