Article: Yukon Demo at PASS - SQL Server Forums

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: Yukon Demo at PASS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-22 : 17:15:34

I've spent the last three days at the PASS conference and thought I'd pass along some of the information on Yukon. Unlike my earlier wild guesses this stuff has a much better chance of being true. This article is mostly just what I saw with little commentary. I'll write something up later with some more detailed thoughts.

Article Link.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-11-23 : 18:53:05

Did they mention any changes to TSQL? For instance, I'd love to see a DO ...... WHILE(expression) construct added.

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-23 : 18:55:18

They already have WHILE loops in T-SQL. What more do you need?

Lavos
Posting Yak Master

200 Posts

Posted - 2002-11-24 : 11:12:32

Let me use code examples


-- Insert some code here has to be done at least once, and maybe more
INSERT INTO Blah (woo)
SELECT Something FROM Yeah WHERE ComplexWhere = 1

WHILE (EXISTS(SELECT SomeCondition FROM Errr WHERE SomeOtherCondition = 1))
BEGIN

-- Now put the same code in the body of the loop.
INSERT INTO Blah (woo)
SELECT Something FROM Yeah WHERE ComplexWhere = 1

END

This has the same statements twice, which requires more maintenance, isn't as obvious when reading, etc. etc.

Compare to:


DO
BEGIN

INSERT INTO Blah (woo)
SELECT Something FROM Yeah WHERE ComplexWhere = 1

END 
WHILE (EXISTS(SELECT SomeCondition FROM Errr WHERE SomeOtherCondition = 1))

I know which I'd rather write and maintain, but it's not like it can't be done with a standard while loop, which is probably why it isn't on the high priority list of things to do.

I've really only had to do it when working with recursive tree structures, and with the new query options Graz talked about those might be handled in a much better way. Of course, there are still whole classes of problems out there that would be better solved with a DO-WHILE.

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-24 : 11:40:44

Display some adaptability*:


WHILE 1=1
BEGIN
  INSERT INTO Blah (woo)
  SELECT Something FROM Yeah WHERE ComplexWhere = 1
  IF (NOT EXISTS(SELECT * FROM Errr WHERE SomeOtherCondition = 1)) BREAK
END

* only another 10 months until Quicksilver's out!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-24 : 11:49:46

What's quicksilver?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-24 : 11:56:28

Follow-up (maybe) to Neal Stephenson's Cryptonomicon. It's not relevant, just that Doug Shaftoe (son of Bobby Shaftoe, who does indeed go to sea, albeit without silver buckles) is wont to say this ("Display some adaptability") on occasion.

Edited by - Arnold Fribble on 11/24/2002 12:07:08

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-11-24 : 20:16:14

Graz,

I was hoping you'd write this up. Thanks.

As for varchar(max), my interpretation of what was said was that it would work like any other varchar in terms of your code, but if you ended up putting > 8000 characters in, behind the scenes they would manage it as a text field for you. If that's an accurate interpretation, then it means a lot of the restrictions to text fields go away.

Two other things: They showed a CREATE XML INDEX statement, which I think is just a special index for XML datatype fields. And they mentioned row-level security, but I didn't get any details on that.

------------------------------------------------------
The more you know, the more you know you don't know.

Edited by - AjarnMark on 11/24/2002 20:18:59

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-11-26 : 15:20:07

I really would like some regular expressions :-p ... like maybe a major enhancement to the LIKE pattern support ;)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-26 : 15:38:14

You'll be able to use reg.exp. in a .Net assembly and register the assembly for use in SQL statements. The nice thing is that since the regular Yukon code will be in CLR, and your own custom .Net code will also be in CLR, your own code will perform as well as native SQL Server code (assuming the algorithm is as efficient as possible)

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-11-26 : 15:41:00

quote:

You'll be able to use reg.exp. in a .Net assembly and register the assembly for use in SQL statements. The nice thing is that since the regular Yukon code will be in CLR, and your own custom .Net code will also be in CLR, your own code will perform as well as native SQL Server code (assuming the algorithm is as efficient as possible)

Does that mean that T-SQL will become IL (intermediate language I think) before running?

Justin

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-26 : 15:48:59

I don't know for sure, but I don't see why not. I imagine SQL Server is written in C/C++, it seems natural that it could be rewritten in C#. It was also designed from the beginning to hook into native Windows APIs so that it could work on any WinNT platform (not an issue right now since it's no longer supported on Alpha), but since MS's entire philosophy is to make .Net as universal as possible, it seems kinda backwards to have non-IL code running SQL Server that also supports IL functionality internally.

The links at the bottom of the article have some more insight on it; it's still a WAG on my part, but it seems logical to me (SHUT UP Femenella!)

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-11-26 : 16:10:46

I asked about using a regular expression in a WHERE clause and didn't really get an answer I liked. They were really curious about what I planned to use it for, etc. They never did give me a clear answer one way or another though. If you want it, send an email to sqlwish@microsoft.com

quote:
Does that mean that T-SQL will become IL (intermediate language I think) before running?

As I understand it NO. T-SQL is still the default/main language for SQL Server. The CLR languages are only callable from within extended stored procedures. To use a CLR language you first have to compile it to a DLL and then register it as an extended stored procedure. It (the C#/VB.NET routine) accesses SQL Server through something that looks and acts like a recordset except that it's ALL inside the SQL Server process space.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Henderson
Starting Member

5 Posts

Posted - 2002-11-26 : 17:32:13

quote:

I really would like some regular expressions :-p ... like maybe a major enhancement to the LIKE pattern support ;)

You can currently use regular expressions with COM objects. Not the libraries that come with .NET, but using the VBScript.RegExp class with sp_OACreate/sp_OA*. It's a bit of a PITA to read in T-SQL, but the performance was a lot better than I expected, and I didn't have to write and manage a dll. I've only used this for tasks that are more administrative, but which end users perform rarely from a UI, not in code that gets executed frequently.

But yeah, regular expressions in the LIKE clause could be a real joy - but it might be something that would get abused, and we'd end up with poorly performing sql queries and lazy designs out there.

If anybody needs the ugly sample code, please request.

quote:

You'll be able to use reg.exp. in a .Net assembly and register the assembly for use in SQL statements. The nice thing is that since the regular Yukon code will be in CLR, and your own custom .Net code will also be in CLR, your own code will perform as well as native SQL Server code (assuming the algorithm is as efficient as possible)

Also "regular yukon code" i assume to be T-SQL.. or are we talking about compiled dll's?

I really get the impression that T-SQL won't be hosted on the CLR, even with a sp, but that your .NET CLR assemblies are hosted inside the SQL Server process, plus we get a .NET framework for SQL Server with this integration.

Edited by - Henderson on 11/26/2002 17:37:07

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-12-06 : 18:22:12

For one of the better looks at Yukon's CLR integration have a look at the "SQL Server: Today and Tomorrow" presentation from Gert Drapers available here [url]http://sqldev.net/events.htm#Developer%20Days,%20the%20Hague%20-%20the%20Netherlands%20(November%2013%202002)[/url]

All the other presentations are well worth downloading as well

HTH
Jasper Smith

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-12-12 : 02:23:11

I just got back from my local SQL Server Users Group meeting. We have a little advantage in that our meetings are held at Microsoft and we often get speakers from Microsoft. Well, as usual, tonight's speaker was very tight-lipped about Yukon, but based on things that were not said, I am making a guess that we will see some additional security options for SQL Server (as opposed to Windows Authentication) passwords. Again, this is purely my speculation, but my hunch is that Yukon will introduce features such as password expiration, history-checking on new passwords, force strong passwords, etc. These are all features that you get with Windows Authentication (at the Windows level, of course) but we have not had with SQL passwords. I think they're coming.

------------------------------------------------------
The more you know, the more you know you don't know.

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-12-12 : 18:47:33

I want ARRAYS. I also what an array data type for parameters that will covert comma separated lists into arrays (or just a single-column table variable). I would love to be able to put these variables in the IN () clauses. Lot less dynamic sql in sps or SQL in code.

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-12-12 : 19:09:41

Here's my wish list:

Will Yukon fix the bug when ADO always time out (runs fast in Query Analyzer) when queries use an indexed a column that uses a complex user defined function (like metaphone)?

Also the ODBC/OLEDB driver does not deal with (strange errors w OLEDB driver; strange / screwed up results w/ ODBC driver) using OPENROWSET to call an sp that calls another sp with lots of dynamic sql (joining a table to a crosstabed result set created by a universal crosstabber sp).

And The Query Optimizer grossly underestimates row counts and frequently ignores join hints when using table variables or temp tables with no statistics. Doesn't hurt much with simple queries with low final and working row counts, but it hits hard with reporting queries with lots of rows. Will Yukon fix that?

Also for statistics to be seen on a temp table, the processes the create the statistics and the process that needs them has to be in separatly run batches or separate stored procedures. It is inconvienient.

I would love Yukon to include hints that help guide the optimizer with estimating row counts in specific areas of the query (scans/seeks/joins) as opposed to dictating join types or what indexes to use. Particularly useful with statistic-less table variables and temp tables or with large sequentially (date) inserted tracking/log tables that don't get their statistics updated very fequently and cost too much to keep them frequently updated (mostly hitting in email send system log is being used for resuming recent interrupted mailings - rowcount way underestimated).

I would love to have VarChar varibles and result columns (don't need it in data columns) that can go over 8000 bytes. Would love it to be able to sort more than 8000 char rows also (ROBUST PLAN doesn't fix or is extremely slow; crosstabbed reports).

Would like to parameterize TOP n and possible future rowcount hints (don't care if sp has to recompile every run). Also parameterizing IN () clauses with single-column table variables comma separated list or array variables (1D array or a table var that allows just 1 col) would be very nice.

It would be cool to be able to use semi joins in the from clause (currenly have to use EXISTS(). Just return a 1/0 bit column called 'exists' from the semi-joinee. That would alow hinting and ordering the semi joins.

I want to be able to return values out of executed dynamic sql (like OUTPUT parameters in SPs).

Edited by - GreatInca on 12/12/2002 19:30:28

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-12-12 : 19:27:59

I think you are directing your requests in the wrong place. Maybe you need to contact Microsoft.

Damian

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-12-12 : 19:34:42

I would but microsoft tend to only care for people who pays them thousands of dollars for tech support contracts. I'm very happy with my new server that I stayed up until 4am last night to move the database too. 8 36Gig SCSI drives in raid 0+1, 4 P3 Xeons, 4 gigs ram and it takes up 4 Us on the rack. I think the thousands of dollars is better spent on my new server than with fighting with microsoft.

Edited by - GreatInca on 12/12/2002 19:36:09

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-12 : 20:46:41

quote:

I want to be able to return values out of executed dynamic sql (like OUTPUT parameters in SPs).

sp_executesql allows you to have INPUT AND OUTPUT Parameters.

EXEC sp_executeSQL @sqlstr, N'@paramin int, @paramout int', @paramin = @varin, @paramout = @varout out

Edited by - ValterBorges on 12/12/2002 20:51:21

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources