SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Yukon Demo at PASS
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/22/2002 :  17:15:34  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

USA
200 Posts

Posted - 11/23/2002 :  18:53:05  Show Profile  Reply with Quote
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!"
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 11/23/2002 :  18:55:18  Show Profile  Visit robvolk's Homepage  Reply with Quote
They already have WHILE loops in T-SQL. What more do you need?

Go to Top of Page

Lavos
Posting Yak Master

USA
200 Posts

Posted - 11/24/2002 :  11:12:32  Show Profile  Reply with Quote
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!"
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 11/24/2002 :  11:40:44  Show Profile  Reply with Quote
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!


Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 11/24/2002 :  11:49:46  Show Profile  Reply with Quote
What's quicksilver?

Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 11/24/2002 :  11:56:28  Show Profile  Reply with Quote
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
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 11/24/2002 :  20:16:14  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 11/26/2002 :  15:20:07  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
I really would like some regular expressions :-p ... like maybe a major enhancement to the LIKE pattern support ;)

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 11/26/2002 :  15:38:14  Show Profile  Visit robvolk's Homepage  Reply with 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)

Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 11/26/2002 :  15:41:00  Show Profile  Reply with Quote
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

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 11/26/2002 :  15:48:59  Show Profile  Visit robvolk's Homepage  Reply with Quote
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!)

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 11/26/2002 :  16:10:46  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

Henderson
Starting Member

USA
5 Posts

Posted - 11/26/2002 :  17:32:13  Show Profile  Visit Henderson's Homepage  Reply with Quote
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
Go to Top of Page

jasper_smith
SQL Server MVP & SQLTeam MVY

United Kingdom
846 Posts

Posted - 12/06/2002 :  18:22:12  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
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 http://sqldev.net/events.htm#Developer%20Days,%20the%20Hague%20-%20the%20Netherlands%20 November%2013%202002

All the other presentations are well worth downloading as well





HTH
Jasper Smith
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/12/2002 :  02:23:11  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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.
Go to Top of Page

GreatInca
Posting Yak Master

USA
102 Posts

Posted - 12/12/2002 :  18:47:33  Show Profile  Visit GreatInca's Homepage  Send GreatInca an AOL message  Send GreatInca a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

GreatInca
Posting Yak Master

USA
102 Posts

Posted - 12/12/2002 :  19:09:41  Show Profile  Visit GreatInca's Homepage  Send GreatInca an AOL message  Send GreatInca a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 12/12/2002 :  19:27:59  Show Profile  Visit Merkin's Homepage  Reply with Quote
I think you are directing your requests in the wrong place. Maybe you need to contact Microsoft.

Damian
Go to Top of Page

GreatInca
Posting Yak Master

USA
102 Posts

Posted - 12/12/2002 :  19:34:42  Show Profile  Visit GreatInca's Homepage  Send GreatInca an AOL message  Send GreatInca a Yahoo! Message  Reply with Quote
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
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 12/12/2002 :  20:46:41  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000