Managed Stored Procedures and User Defined Functions - Part I (Monday, December 06, 2004) By now you might have heard that SQL Server 2005 hosts the .NET 2.0 runtime. This enables writing stored procedures, functions and triggers using any of the managed languages, such as C# and VB .NET. This article is based on my first experience with SQLCLR integration, where I converted some of the documented and un-documented extended stored procedures to managed procedures and functions. A Quick Introduction to SQL Server Management Objects (SMO) (Thursday, July 29, 2004) Microsoft SQL Server 2000 supports a COM-based object model, called SQL-DMO, which can be used to programmatically manage the SQL Servers. SQL Server 2005 replaces SQL-DMO with two .NET based object libraries: SQL Server Management Objects (SMO) & Replication Management Objects (RMO). In this short tutorial, I'll show you how you can use SMO in your .NET applications. Essential XQuery - The XML Query Language (Monday, February 02, 2004) XQuery or XML Query Language is a W3C specification designed to provide a flexible and standardized way of searching through (semi-structured) data that is either physically stored as XML or virtualized as XML (such as XML Views over relational data). To give you an analogy, SQL is used to query relational data, XQuery is a standard language for querying XML data. This article is an introduction to XQuery language (based on November 2003 working draft).

Chapter 7 - Your First Script Component (Wednesday, July 06, 2005) The Integration Services feature of Microsoft® SQL Server 2005 brings together data from diverse sources in a high-performance data integration platform, to enable users to work across multiple applications that may not have been designed to cooperate. In The Rational Guide To Scripting SQL Server 2005 Integration Services Beta Preview, Donald Farmer, Microsoft's Group Program Manager for SQL Server Integration Services, clearly explains how to build practical and useful scripts for this exciting new SQL Server 2005 feature. Mastering the Script Component can be your key to a vast range of data integration functionality. Script components are versatile. They are easy to add to your Data Flow, quite simple to program, and give excellent performance. Add to that the power of visual debugging and you can see why they are such an exciting feature of SSIS. Chapter 2 - Conversations (Wednesday, July 06, 2005) Conversations are one of the fundamental concepts of Service Broker. There are two types of conversations (monologs and dialogs), but only dialogs are included in SQL Server 2005. A dialog is a reliable, two-way, ordered, persistent exchange of messages between two endpoints. The fundamental communications primitive for most messaging systems is a message. For Service Broker, the messaging primitive is the dialog. Chapter 3 - SQL Server 2005 Express Tools (Wednesday, July 06, 2005) SQL Server 2005 Express includes useful tools for managing services, networking protocols, configurations, and more. The SQL Server Configuration Manager tool contains a SQL Server 2005 Services node (which lists services for your server), a SQL Server 2005 Network Configuration node (which lists networking configurations), and a SQL Native Client Configuration node (which lists configurations for SQL Native Client connections). Another tool is Express Manager, or XM, which lets you graphically manage the objects in a SQL Server Express 2005 instance, such as databases, tables, views, stored procedures, and more. The SQLCMD tool allows you to use the command line to issue commands against a SQL Server 2005 instance. It also allows a dedicated administrative connection, so that your commands are not affected by slow server performance.

NEWSEQUENTIALID() (Monday, October 17, 2005) SQL Server 2005 introduces a new T-SQL function called NEWSEQUENTIALID() that is similar to NEWID() function to generate GUIDs, except NEWSEQUENTIALID() function generates the GUID values sequentially. Try the following in Management Studio: USE [tempdb]; GO CREATE TABLE dbo.tblTest (c1 uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(), c2 uniqueidentifier NOT NULL DEFAULT NEWID()); GO DECLARE @Counter INT SET @Counter = 100 WHILE @Counter > 0 BEGIN INSERT INTO dbo.tblTest DEFAULT VALUES; SET @Counter = @Counter - 1 END GO SELECT * FROM dbo.tblTest; GO DROP TABLE dbo.tblTest; GO You will notice that the c1 column has sequential GUID values whereas the c2 column has random GUID values. NEWSEQUENTIALID() Benefits: You can use NEWSEQUENTIALID() to generate GUIDs to reduce page contention at the leaf level of indexes. Since the values are generated sequentially (instead of random values), NEWSEQUENTIALID() makes the column more suitable for indexing. Sequential values lead to less page splits, thus less fragmentation and clustered index performance should improve as they'll be inserted ascending and in sort order. When used with a ROWGUIDCOL column in Merge Replication, NEWSEQUENTIALID() can provide increased performance when making and tracking changes. NEWSEQUENTIALID() Limitations: NEWSEQUENTIALID() can only be used with DEFAULT constraints on table columns of type uniqueidentifier. It cannot be referenced in queries. It is possible to guess the value of the next generated GUID, and therefore access data associated with that GUID. If privacy is a concern, do not use this function.

Conferences

Training Classes

Webcasts

User Group Meetings

Online Chats

 View All Events

SQL Server 2005: Users will upgrade … eventually  (Wednesday, October 19, 2005) Next SQL Server Stop: Katmai  (Thursday, October 13, 2005) Windows-Based Databases Earn Trust in World's Largest Deployments  (Thursday, October 13, 2005) When Applications And Databases Collide  (Thursday, October 13, 2005) In a Big Year for BI, Microsoft Ups The Price/Performance Ante  (Sunday, October 02, 2005)

SQLXML The Web releases model used by Microsoft to enable XML support for SQL Server 2000 is called as SQLXML. This included annotated XSD schemas, XML bulk load, Web services (SOAP) support, updategrams, and so on. Click here for more information. XMLA XMLA specification is an open industry-standard Web service interface designed specifically for online analytical processing (OLAP) and data-mining functions. Analysis Services 2005 natively supports XMLA. Click here for more information on XMLA.

 View Complete Glossary