snaps & snippets

XML Query Composition in Practice

noreply@blogger.com (Matija Lah) — Thu, 29 Mar 2012 18:05:22 PDT

XML composition using XML Query is not what you might call a popular subject, not even a frequently discussed one; well, at least as far as SQL Server is concerned. In this blog, I have discussed XML retrieval on numerous occasions, I have also touched SQL Server XML performance characteristics, but XML composition has so far been stuck on the back burner. Until now, that is. The subject of

SQL Server 2012 RTM

noreply@blogger.com (Matija Lah) — Fri, 23 Mar 2012 12:12:50 PDT

Earlier this month SQL Server 2012 RTM was announced, and the evaluation is now available for download: Microsoft SQL Server 2012 Evaluation. The build number for the RTM is 11.0.2100.60. Related downloads are also available: Books Online for SQL Server 2012 – product documentation is available online, and can also be installed locally. Books Online are not installed as

XPath ID for People Avoiding Pubs

noreply@blogger.com (Matija Lah) — Wed, 01 Jun 2011 02:47:41 PDT

I discussed the ID XPath function in a recent post. I composed the data samples used in that post from the data available in the pubs sample database. Since this particular database may not be a very popular commodity these days, I've also prepared a "pubs-free" alternative – using the same data as before, but with fewer obstacles. The sample consists of two scripts showcasing the examples

SQL Server: The XPath ID Function

noreply@blogger.com (Matija Lah) — Wed, 01 Jun 2011 02:57:47 PDT

XML provides a simple and efficient way of storing relational data, especially for the purposes of transporting it from one RDBMS to another. Besides the "natural" technique, where the structure of the XML document is used to represent the relationships, XML Schema also provides three special data types that can be used to define the relationships inside an XML document. I've already discussed

SQL Server Integration Services, Execute SQL with Complex Parameters

noreply@blogger.com (Matija Lah) — Mon, 04 Apr 2011 15:11:09 PDT

If you're familiar with SQL Server Integration Services (SSIS) then you've probably, at one time or another, run into problems with the integration bit – however weird that may sound. I can understand the fact that SSIS is supposed to be generic and universal and platform independent, and therefore not favor a particular DBMS – not even the one that it's a part of. Nonetheless, for years now I

Cannot open user default database? Login failed?

noreply@blogger.com (Matija Lah) — Thu, 04 Mar 2010 12:52:27 PST

Assigning a default database to every server principal (login) is good practice, no doubt. Just think of the last time someone in your organization created a user object in master by mistake. Generally, the most appropriate database to set as the default for a user is the database they will most likely access when performing their work, and for most cases that would be a user database (rather

What Permissions Does a Principal Have?

noreply@blogger.com (Matija Lah) — Sun, 03 Jan 2010 11:37:07 PST

SQL Server Management Studio provides several ways of checking which permissions have been granted (or denied) to whom and on what objects. This is achievable on different levels within the securables hierarchy: Server-level permissions, per principal: on the Permissions page of the Server Properties window (accessible through the View menu when the instance node is selected in the Object

SQL Server MVP Deep Dives

noreply@blogger.com (Matija Lah) — Fri, 20 Nov 2009 12:18:53 PST

Earlier this month, at the PASS 2009 Summit, a very special event took place – the SQL Server MVP Deep Dives book launch. As the title suggests, this is no ordinary SQL Server book, for several reasons: It was written by 53 MVPs; 100 percent of authors' royalties have been donated to support War Child International, a network of independent organizations, working across

[snap] Pending File Rename Operations?

noreply@blogger.com (Matija Lah) — Fri, 10 Apr 2009 09:57:12 PDT

I will never understand why some software/hardware vendors fail (forget?) to clean up the Windows registry after they've... well, fondled with it for reasons known only to them, such as during software installation or updates. When installing SQL Server – the release or a service pack – on a machine where software from one of the aforementioned vendors has recently been installed or updated,

SQL Server 2008 Service Pack 1

noreply@blogger.com (Matija Lah) — Thu, 09 Apr 2009 01:07:06 PDT

SQL Server 2008 Service Pack 1 has been released on April 7th 2009, bringing a few improvements to the setup process – on top of several important fixes. Be sure to review the release notes before attempting installation. The files can be downloaded from the following site: SQL Server 2008 Service Pack 1. The list of bugs fixed in SP1: KB 968369. ML

SQL Server 2008 XPath Functions

noreply@blogger.com (Matija Lah) — Fri, 10 Apr 2009 10:31:39 PDT

The 2008 version of SQL Server has not introduced as many changes to its XPath implementation as one might have expected; nonetheless, two additional XPath functions have been added. In fact, I'm very sure many of us have missed both of them ever since SQL Server 2005: lower-case() – returns the text of the target node in lower case; and upper-case() – returns the text of the

SQL Server 2008 Books Online Update (January 2009)

noreply@blogger.com (Matija Lah) — Tue, 03 Feb 2009 03:37:25 PST

The January 2009 update of SQL Server 2008 Books Online has been released last Friday. The installation file is available for download: Microsoft SQL Server 2008 Books Online (January 2009). An update of the sample databases is scheduled for spring, and by the looks of things that's still very far away. ;) Anyway, the samples are available at CodePlex: SQL Server 2008

2008 May Not Be the Last for Planet Earth

noreply@blogger.com (Matija Lah) — Wed, 31 Dec 2008 12:50:27 PST

What do you think? Have a good one! ML

Sparse Columns and Filtered Indexes

noreply@blogger.com (Matija Lah) — Sun, 21 Dec 2008 09:15:59 PST

At the December meeting of SLODUG, the Slovenian Developers User Group, I've presented two new functionalities introduced with SQL Server 2008: Sparse Columns – a new (or, at least, an improved) way of storing data in nillable columns so that non-existent data takes up virtually no space in the database. Well, to be honest, storage usage *is* affected (as the maximum size of

Some Old News

noreply@blogger.com (Matija Lah) — Sun, 21 Dec 2008 09:12:26 PST

This may be old news, but I haven't said anything about this so far, so this may not be old news to everyone. ;) SQL Server 2005 Service Pack 3 SQL Server 2005 SP3 has been released on December 15th 2008. The files (elevating the full version number to 9.00.4035) are available for download: Microsoft SQL Server 2005 Service Pack 3; Microsoft SQL Server 2005 Express Edition

SQL Server 2008 Books Online Update (August 2008)

noreply@blogger.com (Matija Lah) — Wed, 01 Oct 2008 00:53:28 PDT

An update to SQL Server 2008 Books Online has just been published yesterday: Microsoft SQL Server 2008 Books Online (August 2008). ML

SQL Server 2008 Express with Advanced Services

noreply@blogger.com (Matija Lah) — Tue, 26 Aug 2008 02:54:27 PDT

The advanced edition of SQL Server 2008 Express is now available for download. As was the case with version 2005 when this edition was first introduced, the Advanced Services include (in addition to the Database Engine, of course): SQL Server Management Studio Basic (formerly: Express); Full-text Search; and SQL Server Reporting Services (limited to local reporting). You

SQL Server 2008 RTM

noreply@blogger.com (Matija Lah) — Sat, 09 Aug 2008 05:23:57 PDT

I'm sure you know by now that SQL Server 2008 has RTMed about two days ago and is already available for download to MSDN/TechNet subscribers. There are additional free downloads that you might also be interested in: Microsoft SQL Server 2008 Evaluation Edition; Microsoft SQL Server 2008 Upgrade Advisor; Microsoft SQL Server 2008 Feature Pack, August 2008; Microsoft SQL

SQL Server 2008 (RC0), FILESTREAM changes

noreply@blogger.com (Matija Lah) — Fri, 13 Jun 2008 07:10:44 PDT

At this year's Microsoft NT Conference in Portorož fellow MVP Andrej Tozon and I have presented a workshop on designing solutions with Visual Studio 2008 and SQL Server 2008 using the Windows Presentation Foundation and a few new SQL Server 2008 features. Andrej has blogged about this last month and has made all the workshop files available on his site. You might also already know that SQL

SQL Server 2008 Scoped Search

noreply@blogger.com (Matija Lah) — Wed, 07 May 2008 07:13:58 PDT

This may be old news, but it's also the kind of "old news" that's actually becoming more and more relevant these days. ;) More than a month ago the Microsoft SQL Server documentation team have announced a new Live Search Macro: The SQL Server 2008 Books Online Scoped Search macro. Those of you who've found the 'old' one useful, must have been anticipating the 'new' one.

SQL Server: XQuery/XPath, Predicate Functions

noreply@blogger.com (Matija Lah) — Wed, 02 Sep 2009 08:13:13 PDT

Previously we have looked at the supported XQuery/XPath retrieval functions implemented in SQL Server 2005 with a few examples of use, and we have mentioned the fact that some functions are restricted to use in XPath predicates. Today we will see which XQuery/XPath functions are subject to this restriction. Any attempt at using these restricted functions to retrieve data from the XML would

SQL Server: XQuery/XPath, Retrieval Functions

noreply@blogger.com (Matija Lah) — Tue, 17 May 2011 03:29:55 PDT

Even after all this time (since the release of SQL Server 2005 which introduced the XML data type) I still notice users having trouble with XQuery/XPath functions when processing (or rather trying to process) XML data in SQL Server, that's why I've decided to dedicate a few more posts to the subject. The ulterior motive here is me learning and testing what (I think) I know. ;) I've introduced

SQL Server XQuery, Accessing Attributes

noreply@blogger.com (Matija Lah) — Wed, 02 Sep 2009 08:14:14 PDT

While browsing this blog's usage logs recently, I've noticed quite a few referrals from various search engines of users trying to troubleshoot problems they may have encountered when accessing XML attributes in a SQL Server XQuery. Although the problem has been discussed in the past, and has even been documented in Books Online, some users out there still aren't familiar with a SQL Server 2005

What's wrong with COUNT(*)?

noreply@blogger.com (Matija Lah) — Mon, 25 Feb 2008 03:36:12 PST

Counting – what's it all about? I'm sure you've heard about a trick that one should use when aggregating data using the COUNT system function in Microsoft SQL Server; specifically, how the performance of the function can be improved using a neat trick. But can we go beyond neat...? The tests have been done on a SQL Server 2005 (SP2) instance; you're all kindly invited to also run them on an

Not Just Any Old 2008

noreply@blogger.com (Matija Lah) — Mon, 31 Dec 2007 10:53:48 PST

In this uncertain world of ours one thing remains pretty certain. 2008 is definitely coming. The year, that is. ;) Have a good one! ML