SQL Server Performance

Mining For 'Yukon' Nuggets

Discussion in 'SQL Server 2005 General DBA Questions' started by gaurav_bindlish, Jun 23, 2003.

  1. gaurav_bindlish New Member

  2. vbkenya New Member

    Juicy stuff... Compiled T-SQL ? CLR ?

    Is this the death of the Profiler and my dear old 'Show Execution Plan'?

    Makes more sense to call this new animal 'SQL Server.NET' rather than just 'YUKON'.

    Nathan H.O.
  3. gaurav_bindlish New Member

    Actually there was another thing that caught my attention. Usage of .NET compatible datatypes for column data type. So we have more flexibility now. But will it be able to store objects of classes?

  4. vbkenya New Member

    I can sniff the makings of an "Object Oriented Database" in the air.

    Allow me a few dreams.....

    -Data Types

    Image the declaring and storing (maybe)some of .NET Data types and working with the various conversion functions or the formatting capabilities inherent in these types. Think of Collections: ArrayList, Hashtable, etc. I just can't imagine how SQL Server will facilitate storage of some of these CLS data types.

    -Error handling

    Imagine Try...Catch...Finally in T-SQL (or will it be 'OO-SQL'?)


    Streams (Binary, text etc) and files might become a possibility opening up new ways of working with XML data and DTS.

    Nathan H.O.
  5. gaurav_bindlish New Member

    Well I think the Data type and the error handling cases are incorporated in Yukon. (Read the link posted) The third point may also become a possibility. Let's see...

  6. bradmcgehee New Member

    I found out today that once I get the beta of Yukon (very soon), I must sign a NDA, so I won't be able to talk about it in this forum. Shucks!

    Brad M. McGehee, MVP
  7. gaurav_bindlish New Member

    Too bad.... Very disappointed to hear this......

    Anyway, will wait till the final release....

  8. vbkenya New Member

    Truly sad, but with an NDA in force:

    - How are guys able to write articles and books about YUKON?
    - Or is there specific smallprint prohibiting stuff you can't talk about?
    - Or does the NDA apply only to some 'people'.

    Nathan H.O.
  9. gaurav_bindlish New Member

    NDA is applicable to all the beta testers. The person whose interview was published is writing book on Yukon so she can definately let out some of the details. I think after this year's PDC, things will become more clear about YUKON.

  10. bradmcgehee New Member

    The NDA I have had to sign doesn't let me discuss anything at all. It is an early beta. Later betas may be different.

    Brad M. McGehee, MVP
  11. gaurav_bindlish New Member

  12. gaurav_bindlish New Member

    This is how I beleive things will look like-

    Multiple active recordsets- Blessing for reporting guys who wanted to see order and order details but had to use a join and have to repeat order data just because the connection allowen one open recordset. Now they can have 2- one on ordersand one on order details.

    PAssing Array as paramter - I need not say about this. After TABLE datatype, and FUNCTIONS(), its a bonus.

    Splitting datetime - Save 4 bytes where you don't need to store time. Performance enhancement.

    Recursive queries - Ma turn out to be a solution to cursors.

    Exception Handling - Oracle guys have less to be proud of. Was present in Oracle. Never in SQL. Lot of manual work saved.

    Workbench tool - Enhanced programming experience, Makes life as easy as coding in visial basic.

    I'll keep the team posted if I find something....

  13. vbkenya New Member

    I guess the question still is:

    "Will the Yukon release of SQL Server qualify as a full-fledged, object-relational "universal database" that delivers most or all the features and performance of Oracle 9i or IBM DB2 UDB 8.1?"

    Roger Jennings

    Personally, I don't really care for now what anybody else (vendors) has to offer.....Yukon will still be offering TOO MUCH for me to handle/upgrade.

    Nathan H.O.
  14. preethi Member

    Yukon going to come with some wonderfull things. Specially structured error handling, custom data types etc.
    I am worried about another 3 things (Some are offered by ORACLE)
    1. Is there any way to create before triggers. Do it have the ficility to identify the old and the new data of the same row. (Or atleast row level firing)
    2. Bitmap indexes
    3. anyway to read transaction log ? (Specially to find the schema changes) Is there any way to alert the admin/ fire another trigger where certain schema changes occur?

    where can I get the details?

    G.R. Preethiviraj Kulasingham
  15. vbkenya New Member

    • Is there any way to create before triggers. Do it have the ficility to identify the old and the new data of the same row. (Or atleast row level firing)

    We currently have INSTEAD OF TRIGGERS. I suppose YUKON will keep this and maybe add to it.

    • Bitmap indexes

    Not explicity implemented in SQL Server 2000 and Maybe not in YUKON. They may still be implemented internally as is the case now.

    • anyway to read transaction log ? (Specially to find the schema changes) Is there any way to alert the admin/ fire another trigger where certain schema changes occur?

    You cannot currently read (easily and intelligibly) the T-LOG. There are third party tools for this sort of thing (Lumigent Log Explorer is one of them)

    • where can I get the details?

    You will just have to keep on receiving the "news flashes" like most other people not involved in the beta testing. There is no official reference at the moment (as far as I know). Wait and See.

    Nathan H.O.
  16. satya Moderator

    Specifically for the point 3 you can refer to this link http://www.lumigent.com/go/saj2/] from LUMIGENT for schema changes alert and as mentioned by Nathan.

    I believe there is no scope of consideration for reading Tlog using SQL Server (YUKON) like LOG EXPLORER does.

    Satya SKJ
  17. preethi Member

    Log Explorer is anyway a 3rd party tool. we may have to upgrade when we move to the next SQLServer. (The new storage stucture may change)
    Unless we have the log explorer as a tool with SQL Server we don't have full control over data.

    before triggers (row level) is very important because currently we have no way of tracking the changes if the primary key is changed. For example consider a table with key 1, 2,3,4 & 5 after an update statement all have changed to 2,4,,8,& 10 we don't know whether it happened by simply multiplying the key by 2 or any other complex formula (add 5 to all odd numbers) right now I am handling by having another key (identity property)

    G.R. Preethiviraj Kulasingham
  18. gaurav_bindlish New Member

    As far as changing the storage structure is concerned, as far as I have made out from the articles on the internet regarding Yukon compatibility with SQL Server 2000, maximum stress from SQL Server team is to have least problems for migration. So I really hope that data structure does not change.

    In any case with new relase for the software, lot of new functionality (for monitoring etc.) is added. So I guess even if SQL Server provided some inbuilt functionality of reading logs, that would also undergo a change.

    For the hadling of change in the primary key, you can have triggers on the table to mainatain audit trail of the activities on the table. I know this is a cumbersome task but if your data is important and you want to keep a track, this is one solution that can be implemented.

  19. gaurav_bindlish New Member

    The sitings on internet about Yukon- (Courtesy sswug.org)

    1. at www.microsoft-watch.com. According to David Campbell (Microsoft product unit manager for the SQL Server Engine):

    - Yukon characterized as a "fourth generation" database that is optimized to provide "autonomous computing." Microsoft executives have used the autonomous computing label to describe interactions between computers that don't trust each other;

    - Yukon won't be an XML database, but will rather integrate support for XML data types into the relational SQL Server database;

    - Yukon will be tightly integrated with Microsoft's Visual Studio .Net development suite, Campbell said. That way, database programmers will have access to Visual Studio's authoring, debugging, profiling and IntelliSense capabilities.

    2. at www.sqlmag.com.

    - Long-awaited Yukon code base will usher in a new era on Microsoft's software-development roadmap; the company will incorporate the software into other projects, including the Windows Future Storage (WinFS) file system in the next Windows release (code-named Longhorn) and the data store for a future Microsoft Exchange Server release (code-named Kodiak). Microsoft will also issue the next major release of Visual Studio .NET to coincide with Yukon; a minor release (code-named Everett) will ship in early 2003 to coincide with Windows .NET Server (Win.NET Server) 2003;

    - Microsoft will deliver Yukon beta 1 to only 1500 testers, although a future release will be more widely available. Alpha code is being used inhouse and with select close partners, the company said. A key advance in this release is the ability to code stored procedures in any Visual Studio .NET-compatible programming language, thanks to full compatibility with the Microsoft .NET Common Language Runtime (CLR). This feature will make SQL Server more accessible and useful for developers.

    3. at www.eweek.com. According to Gordon Mangione (vice president of SQL Server):

    - Microsoft is also working on making its XML features "deeper," he said. The company released an XML update this fall, focusing on making the common language runtime and the application frameworks of .NET run inside the database itself. That was a huge shift from the past, Mangione said. All further work on XML will continue to be done in-house—it would be too difficult for Microsoft to integrate with another vendor's native XML database, he said. XML databases alone are a successful niche product.

    And finally some official information about Yukon features, fall into 3 category: Enterprise Data Management, Developer Productivity, Business Intelligence:

    1. Enhanced high availability technologies, additional backup and restore capabilities, replication enhancements, and "secure by default" settings will help enterprises to provide users with secure, consistent access to enterprise applications;

    2. Enable organizations to easily store, manage, and analyze data through interoperability advancements, support for open standards, and native Extensible Markup Language (XML) and Web services capabilities;

    3. A new management tool, expanded self-tuning capabilities, and a powerful new programming model will provide flexible control for day-to-day database operations. Enhancements to SQL Profiler and other tools will also help database administrators tune their servers for optimum performance;

    4. Developers will be able to utilize one development tool for Transact-SQL, XML, and Multidimensional Expression (MDX). Integration with the Visual Studio® development environment will provide more efficient development and debugging of line of business and business intelligence applications. SQL Server “Yukon” will also include robust enhancements to the Transact-SQL language;

    5. With the common language runtime (CLR) embedded in the database engine, developers will be able to choose from a variety of familiar languages to develop database applications, including Transact-SQL, Microsoft Visual Basic .NET, Microsoft Visual C# .NET, and Microsoft Visual J# .NET. Additionally, CLR integration will provide developers with increased flexibility through the use of user-defined types and functions. The CLR will also provide opportunities to use third-party code for rapid database application development;

    6. Advancements such as native XML support, user-defined data types, and XQuery will enable organizations to seamlessly connect internal and external systems. SQL Server "Yukon" will support both relational and XML data natively, so enterprises can store, manage, and analyze data in the format that best suits their needs. Support for existing and emerging open standards such as Hypertext Transfer Protocol (HTTP), XML, Simple Object Access Protocol (SOAP), XQuery, and XML Schema Definition (XSD) will also facilitate communication across extended enterprise systems;

    7. Building on SQL Server 7.0 and SQL Server 2000, SQL Server "Yukon" will deliver an end-to-end business intelligence platform with integrated analytics including online analytical processing (OLAP); data mining; extract, transformation, and load (ETL) tools; data warehousing; and reporting functionality. This comprehensive, integrated approach will enable organizations to seamlessly build and deploy robust business intelligence applications while controlling costs;

    8. Advancements to existing business intelligence features, such as OLAP and data mining, and the introduction of a new reporting server will provide enterprises with the ability to transform information into better business decisions at all organizational levels, from the information worker to the CEO. Additionally, scalability and availability enhancements will help ensure that users have uninterrupted access to business intelligence applications and reports;

    9. An improved ETL tool will enable organizations to more easily integrate and analyze data from multiple heterogeneous information sources. By analyzing data across a wide array of operational systems, organizations will gain a competitive edge through a holistic understanding of their business.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  20. thomas New Member

  21. gaurav_bindlish New Member

    I am definately going to send some wishes to SQL Server team at [email protected]

    XML Column type will be intersting.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  22. satya Moderator

  23. satya Moderator

    Also lookout for November 2003 month's SQL Server magazine for loads of updated information on YUKON.

    Satya SKJ
  24. tkelley New Member

    Has anyone seen a final release date for Yukon? Doesn't it usually occur 3-6 months after Betas are released?

    T Kelley

  25. satya Moderator

    In general MS releases within 6 months of BETA release, but in the case of YUKON and upcoming O/s softwares MS is more keen to freshup the utilities in terms of usage, security etc.

    As of now there is no date set and will be release sometime next year.

    Satya SKJ
  26. Twan New Member

    MS are not even saying 2004 at this stage, so I'd suggest that it is likely to be either Q4 2004 or Q1 2005

  27. satya Moderator

    Once they'd failed to meet the deadline so keeping the cards close and I expect it will be in somewhere Q3 of 2004.

    Satya SKJ
  28. satya Moderator

Share This Page