snaps & snippets

XPath ID for People Avoiding Pubs

2011-06-01T11:46:00.001+02:00

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

2011-05-09T15:29:00.013+02:00

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

2011-04-05T00:09:00.001+02:00

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?

2010-03-04T21:50:00.002+01:00

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?

2010-01-03T19:38:00.002+01:00

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

2009-11-20T21:17:00.001+01:00

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?

2009-04-10T18:54:00.002+02:00

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

2009-04-09T10:06:00.002+02:00

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

2009-03-08T20:33:00.003+01:00

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)

2009-02-03T12:36:00.001+01:00

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

2008-12-31T21:17:00.002+01:00

What do you think? Have a good one! ML

Sparse Columns and Filtered Indexes

2008-12-21T18:13:00.004+01:00

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

2008-12-21T18:09:00.002+01:00

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)

2008-10-01T09:50:00.004+02:00

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

2008-08-26T11:53:00.001+02:00

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

2008-08-08T18:56:00.004+02:00

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

2008-06-13T16:08:00.002+02:00

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

2008-05-07T16:08:00.005+02:00

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

2008-04-01T16:19:00.004+02:00

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

2008-03-10T18:46:00.004+01:00

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

2008-02-24T16:26:00.001+01:00

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(*)?

2008-01-11T17:36:00.001+01:00

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

2007-12-31T19:41:00.000+01:00

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

Microsoft Office 2007 iFilters and SQL Server

2007-12-21T17:45:00.001+01:00

Ever since the problem with the missing iFilters for Office 2007 documents first arose, the official response by the vendor was to install Office 2007 (or at least Word 2007) on the server. I believe we all agree that this simply isn't good practice. First of all, why would anyone need an end-user application such as Microsoft Word on a server? Obviously Word would never even have to be

SQL 2005 Books Online – September 2007 Update

2007-11-30T21:49:00.000+01:00

Once again it's time to update the local copy of SQL Server 2005 Books Online. It's the end of November and the September 2007 Update has just been made available for download. The files are available through Windows Update, but in case you've missed them (or want full control over the installation) you can also find them here: SQL Server 2005 Books Online (September 2007). The

SQL Server 2005 Analysis Services – Column Truncation "Inconvenience"

2007-09-14T13:31:00.000+02:00

I've been using SQL Server Analysis Services for large data analysis (analyzing texts) recently, and I've encountered an intriguing problem, the investigation of which has led me to some unexpected discoveries (and a workaround). To reproduce the problem start SQL Server Business Intelligence Studio, then follow these 6 simple steps: Create a new SQL Server Analysis project; Add a

An Unusual Data Rescue

2007-09-07T18:55:00.000+02:00

Take a look at the picture below. Somewhere in that lovely mess, on one shred of paper – or more? – there is an eleven digit number that I need. How lovely! Luckily for me, I've managed to find all the pieces. And it only took about 20 minutes. :) Which is still a long time compared to about three seconds that it originally took to cause the problem... Lessons learned:

Six Months

2007-07-20T14:49:00.000+02:00

»Become a better developer in six months.« Define 'better'. :) I've been tagged by Denis 'The SQL Menace' Gobo to share my views on how I would go about becoming a better developer in the next six months. (Self-)education is the first thing that comes to mind, and the majority of the tag-attack "victims" agree that reading and learning (and writing about it) directly lead to being better at

SQL Server 2005 XML Methods, Part Four, The Existential Quandary

2007-07-06T15:08:00.000+02:00

We've seen SQL Server 2005 XML data retrieval methods in previous posts. In particular we've seen how they perform compared to T-SQL retrieval methods, and – quite frankly – the results were somewhat disappointing. There is an XML retrieval method that we haven't discussed yet. There are two reasons why not: It's not really a retrieval method as much as it is a way of testing whether

SQL Server 2005 Best Practices Analyzer

2007-07-04T12:56:00.001+02:00

SQL Server 2005 Best Practices Analyzer has been released on July 2nd, and is available at this site: SQL Server 2005 Best Practices Analyzer (July 2007). ML

2007 Microsoft MVP Award

2007-07-04T01:05:00.000+02:00

I am extremely pleased to tell you, dear reader(s), that on July 1st I have been presented with the 2007 Microsoft MVP award in the »Windows Server System – SQL Server« category. I would like to take this opportunity to thank everybody at the MVP Program for considering my participation in the communities to be worthy of this award. I would certainly like to thank Dejan Sarka for introducing me

SQL 2005 Books Online – May 2007

2007-06-18T11:23:00.000+02:00

In case you've missed it, the May 2007 update of SQL Server 2005 Books Online has been made available for download on June 12th. The 135 MB setup file can be downloaded from this site: SQL Server 2005 Books Online (May 2007). Of course, the online edition has also been updated accordingly. Also, Windows Live Search now supports SQL Server 2005 Books Online Scoped Search,

Presentation Slides and Enabling Database File Encryption

2007-05-18T17:02:00.000+02:00

The PowerPoint slideshow of the presentation I held at this year's NT Conference is ready for download. The presentation is in Slovene, but I'm willing to translate it to English (or German or Latin – if you give me some time ;) – leave a comment in this blog if you're interested. The presentation provides an overview of cryptographic capabilities of SQL Server 2005, focusing on the Database

SQL Server 2005 Cryptography, Symmetric key performance

2007-05-11T20:05:00.000+02:00

As mentioned before, cryptography is a new functionality in SQL Server 2005. Most aspects of data encryption, digital signing of modules, and the use of cryptographic objects for authentication and authorization are covered in Books Online. Additionally, here are the links to a couple of blogs dedicated to cryptography in SQL Server 2005 (and SQL Server security in general) that I found very

SQL Server 2005 Service Pack 2a

2007-03-07T20:54:00.000+01:00

Although SP2 for SQL Server has been released more than a week ago I haven't yet had the time to upgrade. Which could also be interpreted as good fortune as yesterday SP2a has been released bringing a fix to the »original« SP2. You can read more about it in this KB article. In brief: if you use History Cleanup tasks or Maintenance Cleanup tasks in your SQL Server 2005 maintenance plans (or IS

SQL Server 2005 Service Pack 2

2007-02-19T18:54:00.000+01:00

After the December 2006 CTP of SQL Server 2005 SP2 the second Service Pack has RTM'ed today and is ready for download at the official site. The official site contains links to individual downloads, here are a few essential short cuts: Microsoft SQL Server 2005 Service Pack 2; Microsoft SQL Server 2005 Express Edition SP2 (registration is encouraged, not necessary); SQL Server

SQL Server 2005 XML Methods, Part Three, The XPath Expression

2007-02-14T13:41:00.000+01:00

We've discussed the basics of XML in SQL, we've seen a few examples of XQueries and their performance; I guess we're ready to go a bit deeper. In this article we'll look more closely at XPath expressions and see how they behave in SQL Server 2005 XML Queries. More XPaths than one lead to First of all there are several ways of formulating a valid XPath expression – for a

XQuery 1.0 becomes a W3C Recommendation

2007-01-24T12:54:00.000+01:00

On January 23rd 2007 the World Wide Web Consortium announced that XQuery 1.0, XPath 2.0 and XSLT 2.0 are now official W3C Recommendations, meaning that the R&D activities regarding the current versions have come to a conclusion and that all three standards are now ready for implementation. On the other hand this also means that the work on the next versions has started – I believe the XML Query

SQL Server 2005 XML Methods, Part Two, Retrieval Comparison

2007-01-09T00:13:00.000+01:00

After a brief introduction to XML retrieval methods in SQL Server 2005 we can examine the behaviour of a few practical examples. The implementation of XML as a native data type could not have been considered complete without XML indexes. Especially when considering data retrieval – indexing may just as well be the deciding element of the entire implementation. There are four types of XML

A post not dedicated to SQL

2006-12-24T14:22:00.000+01:00

Here's to new beginnings! May peace and pleasure fill your holidays, and continue into a fantastic 2007! ML

Windows Vista and SQL Server 2005

2006-12-20T12:49:00.000+01:00

There have been several discussions in the communities (forums, newsgroups, blogs) on the fact that Vista, the next version of the Microsoft Windows operating system, does not (or will not) support SQL Server 2005. And when I say "discussions", I really should say "complaints". Yes, SQL Server 2005 and SQL Server 2005 Express are not supported on Vista. That's a fact. Here's another fact:

XQuery 1.0 Becomes a Proposed Recommendation

2006-11-27T18:53:00.000+01:00

On November 22nd the World Wide Web Consortium announced that the XQuery 1.0, XPath 2.0 and XSLT 2.0 became Proposed Recommendations. In other words, it's safe to assume that in the early 2007 we should expect the work on current versions of these three XML management standards to end with proper W3C Recommendations. Obviously, the R&D activities of the XML Query Working Group will not seize,

SQL Server 2005 XML Methods, Part One, Basic Retrieval

2006-11-24T20:59:00.000+01:00

As mentioned in one of the previous posts XML became a native data type with SQL Server 2005. Opinions on its usefulness in a relational database are divided – "fragmented", even. ;) The purpose of this series of posts is to shed some light on how the XML data type could be utilized efficiently, and what are the caveats that should be avoided. In this first part specifically, we'll take a look at

NOLOCK no good

2006-11-10T21:07:00.000+01:00

Query hints should be used with caution, and a special care should be given to locking hints; in Books Online this subject is fully covered, and several SQL professionals out there have added their own views and experience, which is also very evident online. I've also used some examples of how query hints work when discussing optimistic concurrency (and some other places) – purely for

The expense of down-converting unicode values

2006-10-26T18:37:00.000+02:00

Using functions on columns in query conditions is bad practice and should be avoided – I've mentioned that fact before. As it turns out, sometimes the database engine itself chooses to transform column data in order to test a condition, causing a negative performance impact. A serious one as well. As Marco Russo describes in his post on a problem one of his users has encountered, the database

EXISTS or NOT EXISTS... that is the question here

2006-10-03T12:18:00.000+02:00

Whether 'tis nobler to count the rows in tables And stall the traffic with outrageous pride Or to merely stop as early as a single row Fullfills the expectations; To exist: to be found. Has Hamlet ever designed a data driven solution? How to check whether one or more rows corresponding to a given set of conditions exist? It should be common knowledge by

SQL 2005 and tempdb

2006-09-28T01:26:00.000+02:00

With the expansion of the row versioning framework in SQL Server 2005 tempdb becomes an even more important part of the platform than it used to be. Snapshot transaction isolation as well as database snapshots make big use of this "temporary" storage facility and keeping track of its usage is the key to the continuous efficiency of data management solutions based on the SQL Server platform. As

Date[Time] constructor SQL-style

2006-09-21T13:42:00.000+02:00

Querying temporal data in SQL Server has been discussed on many occasions, many tips and how-to's can be found online, best practices regarding date/time data that have evolved over time have been discussed in blogs, articles and books. As the eternal quest in developing practical, simple and efficient solutions regarding temporal data in SQL continues I've decided to chip in and show you a

Snapshot transaction isolation – optimistic concurrency made easy?

2006-09-08T14:40:00.000+02:00

In a previous post we've looked at an example of how the principles of optimistic concurrency should be corrected in order to prevent possible harmful consequences that might come from uninhibited optimism. We've seen how to utilise a rowversion column to track data modifications at row level, and we've reached into the core of how to leverage data modification activities in a multi-tiered

Optimistic concurrency (with essential corrections)

2006-09-01T12:35:00.000+02:00

Optimistic concurrency rests on somewhat wobbly foundations – the presumption that no user will ever be blocked by another user. Which is usually true for single-user solutions while multi-user solutions require a slight correction to this logic. Put another way: with optimistic concurrency we assume that a particular row will only be modified by one user at any given time, and being fully

[snap] Cryptography limitations

2006-08-28T14:20:00.000+02:00

As mentioned before SQL Server 2005 brings several security improvements – one of them being the support for cryptography. The purpose of the built-in cryptiographic functions is to enhance server and/or database security and to provide additional methods in data security (by allowing the encryption of values in columns and variables). The SQL Server encryption hierarchy is well documented in

XPath/XQuery functions in SQL Server 2005

2006-08-18T13:59:00.005+02:00

One of the key improvements in SQL Server 2005 is the implementation of XML as a native data type. At first glance this may not seem like such a big task (after all, it's just one more data type, right, and it's been a standard for years) but after considering the amount of additional features involved, the task of implementing it seems far from trivial: XML Schema - a proper data type

Certificate dependency

2006-08-14T11:15:00.000+02:00

Knowing whether a certificate exists and/or is in use by a dependent object is good, knowing how it's used is slightly better, but knowing exactly which objects are using a certificate is what is really useful. This post is a follow-up to a previous post concerning the use of certificates, so without further ado... ...here's the function: create function dbo.fnList_Certificate_Dependencies ( @

Is a certificate in use?

2006-08-10T11:24:00.000+02:00

SQL 2005 brought several important security improvements in regard to previous versions, one of which is the Certificate. It has three purposes in SQL 2005: Authentication – subjects (persons, devices, services) are granted access to the server (e.g. through HTTP endpoints, as database principals) after a valid certification path has been verified; Module signatures – procedures, functions,

Full synchronisation

2006-08-08T13:30:00.000+02:00

In contrast to Unidirectional synchronisation (discussed in a previous post) also better described as source-to-target data propagation, the purpose of Full synchronisation is to consolidate data at both locations – following the meaning of the word 'synchronisation' more closely: resulting in current historical events (e.g. business transactions) at both locations. From Greek: syn- (together [

SQL Server 2005 Books Online – July 2006 Update

2006-08-08T13:22:00.000+02:00

In case you missed it - like I have (I've been offline for a while) - the Books Online collection was updated July 20th. The files can be downloaded from this location. The online version has also been updated. ML

Column Dependencies and Consequences

2006-07-17T11:37:00.000+02:00

There's one task that every database administrator and every database designer hates. Database schema modifications. Executing appropriate DDL scripts usually does the trick, but is the ALTER script for the modified object enough? What about dependencies? In a relational database pretty much every object is in some way related to one or more other objects - either depending on other objects or

Aggregate concatenation in SQL 2005

2006-07-12T15:00:00.000+02:00

In a previous post I've discussed aggregate concatenation and the issues related to that particular solution. SQL Server 2005 provides new possibilities of implementing safer and more efficient methods. The most obvious one would seem to be in designing a user-defined CLR function, since .Net 2.0 seems like a pretty efficient place to manipulate strings (in respect to T-SQL). There is, however,

Unidirectional synchronisation

2006-07-10T11:13:00.000+02:00

In the newsgroups a frequent question is on how to synchronise data between two locations without the use of replication. Once again, the set-based aproach proves to be the most transparent, the most efficient and the most easy to uderstand. In this example we'll look at unidirectional synchronisation, which as a matter of facts isn't as much about synchronisation as much as it is about the

Filling the gaps

2006-06-30T11:01:00.000+02:00

Recently, in a newsgroup discussion, once again the subject of 'filling the gaps' came up. Imagine an object that can exist in several different states, each of these can last a certain period of time. In order to preserve space and to avoid redundancy we only trace the changes of the object's state. For reporting purposes, however, we require contiguity - the question is no longer when has the

Warnings once, errors today

2006-05-31T12:56:00.000+02:00

In the previous post we took a look at some warnings in SQL 2005 that are considered as errors in SQL 2000. There are also a few opposite cases. Not serious breaking changes, but something to consider.Error 21 (SQL 2005)Severity 20Error 21 (SQL 2000)Severity 10Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator. (Same message text in SQL

Errors once, warnings now

2006-05-30T12:55:00.000+02:00

SQL 2005 brings many breaking changes that need to be adressed when preparing migrations from previous versions. Here is a brief look at some changes in error messages from SQL 2000 to SQL 2005. These aren't (or at least shouldn't be) vital to the majority of migration cases; still, this post (and the next one) provides an overview, that may prove to be useful - if only for a moment or two.Error

Service pack 1 is talking to me! Or is it...?

2006-04-27T09:56:00.000+02:00

Have you ever seen Lassie (the original or one of the movies)? Remember how she's always trying to speak? Trying to warn the kids of imminent danger? With an almost intelligent look on her face, barking excitedly and pointing with her front leg at the culprit wherever he or she might be? I must say I feel like one of the kids in the movie these days. Each time I start may brand new SQL Server

Microsoft SQL Server 2005 Service Pack 1

2006-04-20T10:05:00.000+02:00

It's been a whole day now, since SQL Server 2005 SP-1 has been made available for download. The build number is 2047 (v. 9.00.2047). Read the list of changes and fixes before attempting an upgrade. I've installed the CTP when it was first made available, so I guess I'm looking at a uninstall/reinstall weekend. :) Oh, yes - SQL Server 2005 Express and Express with Advanced Services have also been

Does the DDL Trigger exist?

2006-04-14T18:19:00.000+02:00

Here's a simple little function that checks whether a DDL Trigger with the specified name already exists. If the trigger exists 1 is returned, else the function returns 0. For most SQL objects I normally use the object_id() system function, but the latter only returns a valid value for objects in the sys.objects system catalog view, while triggers (both DML and DDL triggers), XML Schema

[snap] Unicode values in varbinary(max) columns

2006-03-29T15:29:00.000+02:00

When storing Unicode data in varbinary columns the binary stream must begin with the appropriate marker (0xFFFE), otherwise the iFilter will not be able to properly parse the content when building a full-text index. I've learned this the hard way. Well, it didn't actually hurt, but it took a day or two to get cleared up. The interesting bit is the fact that when Unicode data is cast to XML

Microsoft SQL Server 2005 SP1 CTP

2006-03-17T10:03:00.000+01:00

Microsoft SQL Server 2005 Service Pack 1 Community Technology Preview has been announced yesterday and is now publicly available for download. Of course there's also SQL Server 2005 Books Online SP1 CTP, but more importantly SQL Server Express with Advanced Services CTP! SQL Server Express with Advanced Services CTP includes: SQL Server Management Studio Express (SSMSE); support for full-text

More fun with strings

2006-02-16T23:46:00.000+01:00

This time the fun is in counting how many times one string occurs inside another. Of course this has been attempted many times - kudos to all who've attempted it before me! My function, however, includes an extra parameter to support case-sensitive comparisons. Here it is: create function dbo.fnCount_StringInText ( @text nvarchar(4000) ,@string nvarchar(4000) ,@caseSensitive bit = null

Fun with strings

2006-02-10T14:49:00.000+01:00

Let's imagine there's a string of alpha-numeric and numeric characters and we need to delimit the string in order to separate both types of characters, e.g. to prepare values for normalization. The following scalar function (minus a few bugs) inserts a parameterised delimiter (a single space as the default) between individual series of apha-numeric and numeric data: create function

XmlSchema usage

2006-01-16T16:11:00.000+01:00

This post is basically a follow-up to the fnGet_XmlSchema_IsUsed function I posted recently. It lists SQL objects and columns (parameters) where a specific XML Schema is used. If null is specified as the parameter, the function returns a list of all XML Schema Collections and their usage. create function dbo.fnList_XmlSchema_Usage ( @XmlSchemaName sysname = null ) returns @XmlSchemaUsage

Is XmlSchema in use?

2006-01-16T13:26:00.000+01:00

SQL 2005 brings XML as a native data type, and what good is XML if it's not typed? The following function helps identify XML Schemas that exist in the current database. It also helps in identifying XML Schema Collections that are in use in table columns and/or in procedures as typed XML parameters. The XmlSchemaName parameter should contain a qualified XML Schema Collection name (i.e. .

IFilter error - byDesign or byBug?

2005-12-01T00:54:00.000+01:00

Full-text indexing of binary columns in MS SQL 2005 is – to say the least – somewhat overly vulnerable. The root of possible problems is the TYPE COLUMN, specifying the type of the binary value (file) that one tries to build a full-text index (FTI) for. The TYPE column is used by the full-text indexing service (FTS) to select an appropriate IFilter to parse the binary value and create a proper

Index properties with a kick

2005-09-14T21:43:00.000+02:00

The following function is an expansion to the Microsoft SQL Server's built-in function INDEXPROPERTY. It returns a table containing table names, index names, property names and property values, so it can be used for analysis and documentation purposes. create function dbo.fnList_IndexProperty ( @tableName sysname = null ,@indexName nvarchar(128) = null ,@propertyName varchar(128) = null )

When binary values come as characters

2005-08-02T22:55:00.000+02:00

When importing data into SQL through ODBC you might stumble upon a slight hiccup - binary data type is not one of the Microsoft Jet or ODBC data types. The following procedure treats your binary values as character data, but returns them in the correct data type. create proc dbo.CharToBin ( @CharacterValue varchar(8000) ,@BinaryValue varbinary(8000) = null output ) as declare @sql nvarchar

Defragment all indexes in the current database

2005-07-21T20:55:00.000+02:00

Use this script to defragment every index on every table in the current database. declare @workFlow table ( WorkID int identity (1, 1) ,TableName sysname ,IndexName sysname ,unique(TableName, IndexName) ) declare @startID int declare @endID int declare @curID int declare @curTableName sysname declare @curIndexName sysname set nocount on insert @workFlow ( TableName ,

OPENXML custom UDF (inline table function)

2005-07-19T00:37:00.000+02:00

Ever had the need to manipulate XML programmatically in SQL? How about this generic function? Of course it needs some alterations to suit your specific needs more appropriately... It recieves one parameter - the document handle of an internal representation of an XML document (see example below). create function dbo.fnReturn_XML_asUniversalTable ( @xmlObjectHandler int ) returns table as

Climbing trees is for monkeys

2005-07-13T17:19:00.000+02:00

So, here's a monkey. A 'monkey' function, that is. The tree we'll be climbing is a self-referenced table: create table dbo.TreeOfValues ( NodeID int primary key ,Value nvarchar(64) ,ChildOfNodeID int foreign key references dbo.TreeOfValues (NodeID) ) go insert dbo.TreeOfValues ( NodeID ,Value ,ChildOfNodeID ) select 1 as NodeID ,'father1' as Value ,null as

Return related values as array

2005-07-11T12:07:00.000+02:00

Silly it may be but sometimes de-normalized relationships make more sense to the end-user. The following function presents a way to list related records (in the Northwind database) in a flat table, where in this case the names of products in an order can be listed in a single column as comma-delimited values. Also known as: aggregate concatenation. create function dbo.fnGet_ProductName_asArray (

Test for existence of databases on the local and/or a linked server

2005-05-04T16:07:00.000+02:00

Working with many databases on many servers can be a drag sometimes. In special situations - especially when using dynamic queries - it is imperative to test for existence of linked db's programmatically. I use this function: create function dbo.fnGet_dbExists ( @dbName varchar(8000) ) returns bit as begin declare @result bit declare @serverName varchar(128) declare @serverExists bit

Fail-safe conversion to integer

2005-03-03T23:40:00.000+01:00

The following function provides a simple solution to string-to-integer conversion. It converts strings of data type nvarchar(4000) to numbers of data type integer. By utilizing pattern-matching numeric characters are parsed from the input string from left to right until the pattern is matched. For instance: string "338/I" is converted to number 338. Before actual conversion, the part of the

Functions Overview

2005-02-03T13:25:00.000+01:00

I use this query in MS Excel to view the parameters in each of the user-defined functions in a specific database. It also provides me with an overview into the design of return tables for table functions. select sysobjects.[name] as FuncName ,ParamName = case when syscolumns.[name] = '' then 'RETURNS'

Procedures Overview

2005-02-03T13:00:00.000+01:00

I use this query in MS Excel to view the parameters in each of the procedures in a specific database. select sysobjects.[name] as ProcName ,syscolumns.[name] as ParamName ,systypes.[name] as Datatype ,syscolumns.length as Length ,Special = case syscolumns.isoutparam when 1 then '

The First One

2005-02-03T11:41:00.000+01:00

I started this blog to store and to share little bits and pieces of T-SQL code. They are but snippets and scripts which have brought efficiency into my work with SQL. I sincerely hope they do the same for you. ML