CTE Concatenation - In the past concatenating string values across rows in T-SQL is the kind of thing that typically involved recursive UDFs, temp tables, or some such. Another option was introduced with CTEs back in SQL 2005 which can be recursive. For an example I'm essentially going to combine a table of words into sentences. Consider this structure: declare @words table ( ID int identity, Word_Text varchar(1024), Sentence int ) With this data: insert into @words select 'i', 1 insert into @words select 'am', 1 insert into @words select 'concatenated', 1 insert into @words select 'you', 2 insert into @words select 'are', 2 insert into @words select 'not', 2 So the idea is that we should get two resultant sentences, "i am concatenated" and "you are not". So POW, here it is: ;with...
SQL 2008 and powershell - with the 2008 releases from microsoft powershell is really making its way into the mainstream. sql server 2008 ships with standard powershell as well as an add-in that provides an interesting degree of integration that will allow DBAs to write scripts that are far more generalized than T-SQL scripts. getting ready to play with it is pretty strait forward. all you have to do is click the "Start PowerShell" context menu option which is relatively ubiquitous in SSMS 2008's object explorer, located on the "Tables" folder within a database for example. essentially the schematic structure of database is layed out into a virtual filesystem with database objects represented as files and directories. for example every table is a directory with subdirectories for each column and each column having files for the...
Object Oriented Databases with db4o - One of the most notable initiatives in the business software development industry in the last few years is the simplification of data access. The core complication lies in the impedance mismatch between relational storage and object oriented code higher in the stack. Far too much plumbing is required to map object properties to columns in result sets, deal with data type variations and handle other annoyances. The most popular response to the problem has been to leave relational storage in place and introduce ORM (Object Relational Mapping) frameworks to automate the conversion between relational and object oriented structures. This approach is popular in business environments because having a relational database under the hood offers many advantages such as ease of reporting and integration. Object oriented databases, however, offer an interesting alternative....
Quick Notes on ScriptTransformers in Solr DataImportHandlers - One thing that's impressed me with Solr is the flexibility of the Data Import Handlers (DIHs). When I was new to Solr there were several times I thought for sure I'd have to write my own extension of DataImportHandler. Every time that's happened I've been wrong. A transformer or something handled my needs. Sometimes it's wonderful to be wrong! Especially when it means less code I have to write myself! One of the aspects of DIH's that provide such great flexibility is transformers like RegexTransformer and TemplateTransformer. In this post, however, I'm going to *quickly* cover the ScriptTransformer wich allows you to employ your own custom JavaScript code in the processing of imports. Prerequisites Obviously you'll need a functional Solr instance. Also, ScriptTransformers require Java 6 due to JavaScript support. I'll...
SQL 2008 FILESTREAM - this post was written during the early release candidates and the actual implementation has changed slightly. your mileage may vary with this post. over the years people have attempted to put large amounts of non relational data in SQL. images strike me as the most popular case. generally people either stored the data directly in SQL or stored it on the filesystem with the path in SQL. neither of these were terrific options. the former was difficult to manage and frequently resulted in poor performance while the later tended to become inconsistent very quickly because SQL had no way of understanding how the filesytem was modified behind its back. enter SQL 2008's FILESTREAM storage which allows you to store LOB fields directly on the filesystem but still function as columns in...
SQL 2008's table valued parameters - One of my favorite new features available in SQL 2008 is table valued parameters. For years SQL developers have been dying for this abilty and have been throwing some miserable duct tape at the problem. Before sql 2005 it was common to see strings being passed into stored procedures and then parsed into temporary tables. This approach suffered several major disadvantages including being limited to 8000 bytes, requiring T-SQL parsing logic and little guarantee you would receive the parameter structured exactly as you desired. SQL 2005 not only lifted the 8000 byte limitation by introducing (n)varchar(max) but also added extensive XML support which allowed structured data to be passed in as XML and later queried as if it were relational data. While very useful I was always a little bothered by...
Using Entity Framework with Oracle - I've long thought that the Entity Framework hasn't gotten a fair shake. Sure, there's some room for improvement but for an out-of-the-box ORM that ships with the framework it's not too bad. One thing has always troubled me, though. I was never sure what kind of traction it had with data sources other than SQL Server. Recently in a project circumstances conspired in such a fashion that it would be pretty darn handy to use EF to access data in Oracle. Turns out it that despite being a little rough around the edges it's very feasible. The Provider The first step is to download and install an Entity Framework provider for Oracle. There are several options out there but the Oracle Data Provider for Entity Framework on CodePlex seems to be...
My Tool List - One of the more popular thing's Scott Hanselman has done is maintain a tool List, essentially a list of applications and utilities that he's found useful. There are plenty of absolute gems in his list and rather than present my own I'd be very comfortable just pointing you to his... But what fun would that be? None! I also think there are a few more that I can add that are relevant to my personal experience. The list is rather short and there's a bit of overlap with Scott's but if all goes as planned I'll expand it over time as old useful tools come to mind or I discover new ones. Cloud iAWSManager - This is an iPhone app that lets you manage many of the functions of Amazon Web...
Employing Solr/Lucene with SQL Server for Full-Text Searching - I've been fiddling with Lucene a good bit of late and have been quite impressed. It's more than just a "blazing fast" full-text indexing system, especially when implemented via Solr. With Solr it becomes an incredibly scalable, full-featured and extensible search engine platform. I had always assumed that the Lucene stack wasn't for me. For the most part I store my data either in SQL Server or MySQL, both of which have perfectly adequate full-text search capability. It turns out that I could have saved myself a few headaches and saved my employer some money by adopting Solr and not writing my own faceting, caching, etc. Naturally, Lucene/Solr isn't for everyone. If you just have a few hundred-thousand rows of text that you want to perform some basic searches on under...
Solrnet, a Solr Client Library for .Net - One of the strength's of Solr is it's ease of consumption by other platforms due to its REST API and response writers which include XML, JSON, native Ruby and native Python code. If you're trying to consume a Solr service from .Net you could easily use a WebClient and parse the results with .Net's System.Xml namespace and perhaps even build an object wrapper on top of it. Luckily that work's already been done with the solrnet library. In this post I'll outline the fundamentals of solrnet usage. Prerequisites This article assumes you have a .Net development environment such as Visual Studio and a functional Solr install in servlet container. I'll also assume that you understand how to configure Solr's schema. If that's not the case please consult the official Solr wiki....
SQL 2008's MERGE statement - One of the coolest new features of SQL Server 2008 (and there are many) is the MERGE statement. This construct has existed in oracle and DB2 for a while now but it's new to SQL Server. Effectively it allows you to merge two tables together, combining UPDATE, INSERTS and DELETEs into one nifty package. For the purposes of demonstration I'm going to provide a simple example that merges a table variable into a real table by updating rows with matching primary keys with the table variable's values. It will also insert into the real table where there's no match. Consider the following table: CREATE TABLE [dbo].[Things]( [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Name] [varchar](16) NOT NULL ) Go With the following content ID Name ----------- ---------------- 1 computer 2 box...
