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 filtered indexes - there are so many cool new features available in sql server 2008 that many a DBA are excited. as an index-tuning man i've been intrigued by filtered indexes which essentially allow you to slap a WHERE clause on an index making it small and focused on a strategic subset of the tables data. you know, the sort of thing that would have required an indexed view in the past. in order to test them out i created a simple table in a scratch database and filled it with data using DataDude. CREATE TABLE [dbo].[Sales]( [Sale_ID] [int] IDENTITY NOT NULL, [Complete] [bit] NOT NULL, [Location] [int] NOT NULL, CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED ( [Sale_ID] ASC ) the table was filled with 10000 rows with [Complete] = 1 and 100 rows 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...
Podcasts - These days there are so many useful sources of information for software developers: websites, user groups, message boards, second life events, conferences, not to mention good old print mediums such as magazines and books. In the last few years another interesting means of content delivery has come into play with the proliferation of portable mp3 players... podcasts. I'd like to share a few that I've found useful not only for keeping me up to date on the latest and greatest software development news but to pass the time during my 60 miles of driving every day. Keep in mind to play these you only need podcatching software, not necessarily an iPod or other portable mp3 player. .Net Rocks! - This is a great podcast hosted by Richard Campbell and Carl Franklin...
SSRS scripting with RS.EXE - At work we've recently deployed SQL Server Reporting Services (SSRS) and it's been quite interesting. In general I love the product but of course as with everything there are situations where you have to do more than the management tools provide and you have to write some scripts. SSRS provides an interesting scripting facility, RS.EXE, which allows you to run VB .Net code against the management web service of SSRS. I encountered a situation where I had to add a user to the policy as the "Browser" role for all folders in my SSRS environment. Because I didn't want to inherit parent item security to all objects I had no clean way of accomplishing that other than programmatically. I wrote up a quick script and this task will be easy as...
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...
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...
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...
