10 things sql server

Upload: dvsachin1748

Post on 07-Apr-2018

215 views

Category:

Documents


0 download

TRANSCRIPT

  • 8/6/2019 10 Things SQL Server

    1/5

    Page 1

    Copyright 2005 CNET Networks, Inc. All rights reserved.

    For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

    Version 1.0August 8, 2005

    10 things you should know aboutMicrosoft's SQL Server 2005

    By Scott Robinson

    TakeawayThere's plenty of hype about the new SQL Server 2005. Here's a list what's important about the pending release,and what you can plan on using SQL Server for in the near future.

    Table of Contents

    SQL SERVER............................................................................................................................................................................2

    1. THERE IS NOW AN XML DATA TYPE....................................................................................................................................2

    2. DISTRIBUTED MANAGEMENT OBJECTS (DMO) BECOMES SQLSERVER MANAGEMENT OBJECTS (SMO) ....................2T

    3. COMMON TABLE EXPRESSSIONS (CTES)RECURSIVE QUERIES ....................................................................................2

    4. THE SERVICE BROKER MAKES SQLSERVER TRAFFIC ASYNCHRONOUS..........................................................................3

    5. CREATE .NET TRIGGERS ...................................................................................................................................................3

    6. SQLSERVER 2005 CONFIGURATION IS DYNAMIC .............................................................................................................3

    7. DEFINE YOUR OWN DATA TYPES.........................................................................................................................................3

    8. MANY ACTIVE RESULT SETS, ONE CONNECTION ................................................................................................................3

    9. WAITFOR...RECEIVE ...................................................................................................................................................4

    10. DTS IS NOW INTEGRATION SERVICES .............................................................................................................................4

    ADDITIONAL RESOURCES..................................................................................................................................................5

    Version history...................................................................................................................................................................5Tell us what you think.......................................................................................................................................................5

  • 8/6/2019 10 Things SQL Server

    2/5

    10 things you should know about Microsoft's SQL Server 2005

    SQL ServerThe fanfare from TechEd 2005 this past June was vintage Microsoft: lots of happy noise about miraculous productswith tongue-in-cheek delivery dates, as many of us suppressed a yawn. But let's give credit where it's due; we'vewaited a long time for a new SQL Server, and the features Microsoft is delivering with its upgraded databasetechnology are what we've asked for and then some. Here's a rundown on the features we'll find most useful.

    1. There is now an XML data typeIf there's any feature of SQL Server 2005 to jump up and down about, it's the new native XML data type. Why?Apart from the giant leap forward of an already Web-friendly agenda, the new type offers us design options thatare atypical of Microsoft, which generally likes to do our designing for us.

    The new XML data type:

    can be used in a table column

    can be used in a stored procedure, as a parameter or as a variable

    can store untyped data

    can check against a schema to see if data stored in a column typed as XML matches that associated

    schema (if there's no schema, the data is considered untyped)

    And the mapping between XML data and relational data is bidirectional.

    2. Distributed Management Objects (DMO) becomes SQL ServerManagement Objects (SMO)SQL Server Management Objects (SMO) is a .NET Framework-based management framework that lets you createcustom applications for server management. SMO (like DMO before it) allows you to handle columns, tables,databases, and servers as objects, programmaticallyand SMO supports SQL Server 2005's new features, likeService Broker.

    SMOs are optimized, not instantiating objects fully (with all the properties retrieved) until the object is explicitlyreference. You can also batch SQL commands, and create scripts to create objects.

    Your custom server management apps can be used to manage SQL Server 7 in SQL Server 2000 systems aswell.

    3. Common Table Expresssions (CTEs)recursive queriesA common table expression (CTE) enables queries to be recursive. A CTE can be self-referential, with an upperlimit on the incursions. You can use the CTE as a part of a WITH, in a SELECT, UPDATE, INSERT or DELETEcommand.

    Page 2Copyright 2005 CNET Networks, Inc. All rights reserved.

    For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

    http://techrepublic.com.com/html/blogs/teched2005.htmlhttp://techrepublic.com.com/1200-10880-5737262.htmlhttp://techrepublic.com.com/5100-3513-5805291.htmlhttp://techrepublic.com.com/5100-9592_11-5796703.htmlhttp://techrepublic.com.com/5100-3513_11-5782941.htmlhttp://techrepublic.com.com/5100-3513_11-5782941.htmlhttp://techrepublic.com.com/5100-9592_11-5796703.htmlhttp://techrepublic.com.com/5100-3513-5805291.htmlhttp://techrepublic.com.com/1200-10880-5737262.htmlhttp://techrepublic.com.com/html/blogs/teched2005.html
  • 8/6/2019 10 Things SQL Server

    3/5

    10 things you should know about Microsoft's SQL Server 2005

    4. The Service Broker makes SQL Server traffic asynchronousThere's a front-end queuing system, and it changes everything. You can now manage SQL Server traffic byrendering it asynchronous with the new Service Broker feature. It enhances scalability by enabling your system tohandle more traffic logically that it can handle physically. The Service Broker can be accessed via SQLcommands and allows transactions to include queued events.

    Those who know me well would never accuse me of being a Microsoft disciple, but this feature impresses me inno small measure and I'm pleased to call attention to it. Adding easily-configured asynchronicity to the data layerof an enterprise system is a boon to developers and opens up huge possibilities for Web apps. The economy withwhich those apps can now scale can't be overstated. Service Broker alone is a reason to consider upgrading toSQL Server 2005.

    5. Create .NET triggersSQL Server 2005 is .NET-integrated to a promising degree (it has distressed us for some time that Microsoft'scommitment to .NET is as hedged as it is), and one useful consequence of this integration is the ability to createuser-defined triggers (UDTs) through Visual Studio 2005.

    The Trigger option can be pulled from the template list in Visual Studio, generating a file for the code to be

    triggered. The mechanism tying this code to SQL is a SqlPipe. It's deployed in your Build | Deploy. You can workit in the other direction (i.e., from CLR) by referencing the Trigger object in a T-SQL CREATE TRIGGERcommand.

    6. SQL Server 2005 configuration is dynamicIf you're running SQL Server 2005 on Windows Server 2003, its configuration is fully dynamicyou can changeconfiguration values on-the-fly without restarting the server, and get immediate response (the same is true forAddress Windowing Extensions).

    7. Define your own data types

    The user-defined type, enabled by the integration of SQL Server 2005 and the .NET CLR, is a consolidation ofprevious practices, allowing you to create application- or environment-specific types. You can extend moregeneral types into variations that only except values you defineno more triggering or constraints. Validation isbuilt into the field.

    8. Many active result sets, one connectionThis is another feature not just to make note of, but to get excited about. MARS (Multiple Active Result Sets)enables you to execute multiple queries yielding multiple results, over a single connection. An application canmove between open result sets as needed. The performance and scalability benefits are obvious.

    This new trick is courtesy of the new ADO.NET, in tandem with SQL Server 2005's ability to accommodatemultiple active commands. Since MARS is part SQL Server 2005 and part ADO.NET 2.0, it is only available ifyou're using both.

    Page 3Copyright 2005 CNET Networks, Inc. All rights reserved.

    For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

    http://techrepublic.com.com/5100-22_11-5087336.htmlhttp://techrepublic.com.com/5100-22_11-5161213.htmlhttp://techrepublic.com.com/5100-22_11-5161213.htmlhttp://techrepublic.com.com/5100-22_11-5087336.html
  • 8/6/2019 10 Things SQL Server

    4/5

    10 things you should know about Microsoft's SQL Server 2005

    9. WAITFOR ... RECEIVEIn previous versions of SQL, WAITFOR was static. We fed it some wait-time value, and that was what it could do.Now WAITFOR is dynamic; tell it to wait for a RECEIVE statement's results, whenever that might be delivered.

    Beyond the usual this-is-cool, we can appreciate this feature because of the manner in which it accommodates

    the new Service Broker (see #2). Since Service Broker makes database query ability asynchronous via queuing(and therefore extremely dynamic), and a particular database query may sit in a queue for an undeterminedperiod, the new dynamic WAITFOR his ideal for responding to RECEIVE results that will emerge at the discretionof Service Broker.

    10. DTS is now Integration ServicesThere's a new architecture underlying data transformation. The very popular and widely used DTS is nowIntegration Services, and consists of a Data Transformation Pipeline and a Data Transformation Runtime.

    The pipeline connects data source to data target by means of data adapters, with transformations between them.It's a conventional structure, but implemented in such a way as to enable considerable complexity: for instance,you can do one-to-many mappings, and create columns with output derived from a transform.

    The Data Transformation Runtime gives you components for organizing data loading and transformationprocesses into production-oriented operations, within which you can manage connections and manipulatevariables. It's basically a run-time object framework that can be bundled into managed .NET apps.

    DTP and DTR components are used to create Integration Services packages, similar in principle to the familiarDTS packages but with much greater levels of configurability and control, particularly in the area of workflow.

    Scott Robinson is an IT management consultant to the U.S. manufacturing and brokerage industries. He hasmanaged design teams sponsored by the Department of Defense and the Department of Energy, and has workedwith academic research groups. He is vice president of development at Quantumetrics, Inc.

    Page 4Copyright 2005 CNET Networks, Inc. All rights reserved.

    For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

    http://techrepublic.com.com/5100-3513_11-5765005.htmlhttp://techrepublic.com.com/5100-3513_11-5765005.html
  • 8/6/2019 10 Things SQL Server

    5/5

    10 things you should know about Microsoft's SQL Server 2005

    Additional resources Subscribe to TechRepublic'sDownloads RSS Feed

    Sign up for TechRepublic's Downloads Weekly Update newsletter

    Sign up for TechRepublic's SQL Server newsletter

    Check out all of TechRepublic's free newsletters

    Follow the evolution of an SQL query and automate your database reporting

    Achieve better SQL Server performance with Profiler and Query Analyzer

    SQL Building Blocks and Server Settings

    Version historyVersion: 1.0

    Published: August 8, 2005

    Tell us what you think

    TechRepublic downloads are designed to help you get your job done as painlessly and effectively as possible.Because we're continually looking for ways to improve the usefulness of these tools, we need your feedback.

    Please take a minute to drop us a line and tell us how well this download worked for you and offer yoursuggestions for improvement.

    Thanks!

    The TechRepublic Downloads Team

    Page 5Copyright 2005 CNET Networks, Inc. All rights reserved.

    For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

    http://techrepublic.com.com/5155-22-0.xmlhttp://techrepublic.com.com/5155-22-0.xmlhttp://nl.com.com/MiniFormHandler?brand=techrepublic&list_id=e072http://nl.com.com/MiniFormHandler?brand=techrepublic&list_id=e046http://nl.com.com/acct_mgmt.jsp?brand=techrepublic&return_to=http://techrepublic.com.com/http://techrepublic.com.com/5138-9592-5783491.htmlhttp://techrepublic.com.com/5138-9592-5657128.htmlhttp://techrepublic.com.com/5138-6240-5634109.htmlmailto:[email protected]?subject=Download_Feedback&body=10_things_you_should_know_about_Microsoft%27s_SQL_Server_2005mailto:[email protected]?subject=Download_Feedback&body=10_things_you_should_know_about_Microsoft%27s_SQL_Server_2005http://techrepublic.com.com/5138-6240-5634109.htmlhttp://techrepublic.com.com/5138-9592-5657128.htmlhttp://techrepublic.com.com/5138-9592-5783491.htmlhttp://nl.com.com/acct_mgmt.jsp?brand=techrepublic&return_to=http://techrepublic.com.com/http://nl.com.com/MiniFormHandler?brand=techrepublic&list_id=e046http://nl.com.com/MiniFormHandler?brand=techrepublic&list_id=e072http://techrepublic.com.com/5155-22-0.xmlhttp://techrepublic.com.com/5155-22-0.xml