Tag Archive for: SQL Server 2005

Shredding XML in SQL Server 2005

/9 Comments/in , /by

Using XML data has many applications. In databases in particular it can be used for passing parameters from client applications, exchange data between SQL modules, or storing details in XML columns. SQL Server 2005 offers new capabilities and native support for XML. Below is one example of utilizing some of the new XML methods in XML to shred XML data to table format.

-- Declare XML variable
DECLARE @data XML;
 
-- Element-centered XML
SET @data = 
N'<data>
    <customer>
      <id>1</id>
      <name>Allied Industries</name>
    </customer>
    <customer>
      <id>2</id>
      <name>Trades International</name>
    </customer>
  </data>';
 
-- Using the query() method
SELECT T.customer.query('id').value('.', 'INT') AS customer_id,
       T.customer.query('name').value('.', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer);
 
-- Using the value() method
SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,
       T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer);
 
-- Select only customer which id equals 2 using the exist() method
SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,
       T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer)
WHERE T.customer.exist('id/text()[. = "2"]') = 1;
 
-- Attribute-centered XML
SET @data = 
N'<data>
    <customer id="1" name="Allied Industries"/>
    <customer id="2" name="Trades International"/>
  </data>';
 
-- Using the value() method
SELECT T.customer.value('@id', 'INT') AS customer_id,
       T.customer.value('@name', 'VARCHAR(20)') AS customer_name
FROM @data.nodes('data/customer') AS T(customer);
 
 
-- Results
customer_id customer_name
----------- --------------------
1           Allied Industries
2           Trades International

Testing the processing for element-centered XML versus attribute-centered XML does show that attribute-centered XML is processed faster.

Resources:

XML Support in Microsoft SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms345117.aspx

SQL Server 2005 Service Pack 2

/5 Comments/in /by

The long awaited SQL Server 2005 SP2 is released! This is important for a few reasons, not the least of which that Vista requires SP2 to run SQL Server.

It is an all inclusive service pack, which means it includes everything from service pack 1. The installation requires 1.9 GB of disk space available to download and install SP2. Upon installation it does take approximately 1 GB of disk space on the system drive.

The following table shows the version numbers that correspond to versions of SQL Server 2005:

Version Number  SQL Server 2005 Version

--------------  ---------------------------------
9.00.1399       SQL Server 2005 (initial version)
9.00.2047       SQL Server 2005 SP1
9.00.3042       SQL Server 2005 SP2
9.00.3050       SQL Server 2005 SP2a
9.00.3152       SQL Server 2005 SP2 with KB933097

The SQL Server 2005 version can be verified in SQL Server Management Studio Object Explorer when connected to the instance, or by executing the following query against a Database Engine instance:

SELECT SERVERPROPERTY('ProductVersion');

To avoid reboot after the installation it is recommended to stop the Windows Management Instrumentation and SQL Server FullText Search services.

Everyone has its preferences when it comes to new features. Here is the list of my top 10 features in this service pack:

1. VARDECIMAL – This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values in the column do not require many digits of precision, you can potentially save a large amount of the disk space that is needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.

2. Maintenance Plans – Maintenance plans are now supported by the SQL Server Database Services installation. Before SP2, you were required to install SQL Server 2005 Integration Services (SSIS) to run maintenance plans on a server-only installation.

3. Custom Reports – Users can now integrate their own management reports into Management Studio.

4. Linked servers – The New Linked Server dialog box now includes a Test Connection button. The Linked Servers node in Object Explorer now displays system catalogs and user database objects. As soon as the linked server is created, you can expand its node to show tables and views on the remote server.

5. Script generation – You can control how objects are scripted from the Object Explorer using the new Tools Options script dialog box. This dialog box also controls the defaults for the generate Script Wizard. Also, in the Generate Script Wizard you can now specify that the scripted objects include a DROP statement before the CREATE statement. Scripting of objects into separate files is now possible.

6. Maintenance Plan Schedules and Multi-Server – The Maintenance Plan Designer supports multiple subplans for a maintenance plan. Each subplan can have an independent schedule for maintenance tasks. Also, maintenance plans now support multi-server environments, logging to remote servers, and multiple schedules.

7. Reporting Services Integration with SharePoint – You can integrate a report server instance with Windows SharePoint Services 3.0 or Microsoft Office 2007 SharePoint Server to store, secure, access, and manage report server items from a SharePoint site. Integration features are provided jointly through SP2 and a special Reporting Services Add-in that you download and install on an instance of the SharePoint technology you are using. The new Report Viewer Web Part is included in the Reporting Services Add-in that you install on a SharePoint technology instance.

8. Graphical Show Plans – The spacing between nodes is reduced to display more plan information.

9. SQL Server Management Studio Connections – Fewer connection prompts when you open T-SQL scripts. Management Studio determines the connection of the current context.

10. Copy Database Wizard – The new wizard can discover many of the issues with dependent objects to improve reliability. A verbose logging file is created during operation and can be examined to diagnose problems with the operation. Improved reliability for online database transfers using SMO method: Stored procedures, Jobs, Packages, Messages, Endpoints, LOGINs; Permissions (explicit GRANT/DENY).

Additional resources:

How to obtain the latest service pack for SQL Server 2005
http://support.microsoft.com/kb/913089

SQL Server 2005 Service Pack 2 Download
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

SQL Server 2005 Express with SP2 Download
http://msdn.microsoft.com/vstudio/express/sql/download/

What’s New in SQL Server 2005 SP2
http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/WhatsNewSQL2005SP2.htm

Readme Files for SQL Server 2005 SP2 and SQL Server 2005 SP2 Express Editions and Tools
http://www.microsoft.com/downloads/details.aspx?FamilyID=eb05d099-8a66-45f6-84ce-4888760d2af8&DisplayLang=en

Readme for Microsoft SQL Server 2005 Service Pack 2
http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm

Press Release: Microsoft Releases SQL Server 2005 Service Pack 2
http://www.microsoft.com/presspass/press/2007/feb07/02-19SQLSP2PR.mspx

A list of the bugs that are fixed in SQL Server 2005 Service Pack 2
http://support.microsoft.com/default.aspx?scid=kb;en-us;921896

JumpstartTV Video by Brian Knight: Overview and Installing SQL Server 2005 SP2
http://www.jumpstarttv.com/Media.aspx?vid=70

Feature Pack for Microsoft SQL Server 2005 – February 2007
http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

SQL Server 2005 Samples and Sample Databases (February 2007)
http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

SQL Server 2005 Books Online
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Reporting Services Add-in for Microsoft SharePoint
http://www.microsoft.com/downloads/details.aspx?FamilyId=1e53f882-0c16-4847-b331-132274ae8c84&DisplayLang=en

Data Mining Add-ins for Microsoft Office 2007
http://www.microsoft.com/downloads/details.aspx?FamilyId=7c76e8df-8674-4c3b-a99b-55b17f3c4c51&DisplayLang=en

Microsoft SQL Server 2005 Service Pack 2 issue: Cleanup tasks run at different intervals than intended
http://support.microsoft.com/kb/933508

Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2
http://support.microsoft.com/kb/933097/

64-bit Architectures for SQL Server 2005

/0 Comments/in /by

Seems there is a lot of confusion about the different 64 bit architectures and which are supported for SQL Server 2005 to run on. And the different vendor acronyms to describe the architectures just add to the confusion.

Here is a brief note trying to sort this out.

There are only two 64 bit architectures supported by SQL Server (and actually driven by the supported 64-bit architectures for Windows). The separation is based on the underlying processor architecture.

The first one is IA-64 (or Itanium) and it is based on the EPIC (Explicitly Parallel Instruction Computing) instruction set. This is Intel’s first true 64-bit chip.

The second 64 bit architecture is x64 (or x86-64) which is essentially the x86 architecture with extensions to include 64 bit functionality. All Intel x64 and AMD64 processors fall in this category. It was first introduced with AMD’s Athlon 64.

There is no 32 bit equivalent for IA-64 as it was designed to be 64 bit from the beginning.

It is more correct to say x86-32 for the 32-bit equivalent, as x86 is the generic name for the architecture which started with Intel’s 8086 processor which was 16-bit. The x86-32 was introduced with the Intel 386 processor.

More details on 64-bit computing with SQL Server 2005 are available here:
http://www.microsoft.com/sql/editions/64bit/overview.mspx