Tips and Tricks - SimonS Blog on SQL Server Stuff

Browse by Tags

All Tags » Tips and Tricks (RSS)
31 March 2008 10:13

SQL Server 2008 - HierarchyId - How do you move nodes/subtrees around

In a recent post SQL Server 2008 - HierarchyId whats the point? I talk about the scenarios that the new hieararchyId can be applied to. Here we expand on this further and also provide a solution for moving hierarchies around. The limitation is that you...
Posted by simonsabin | 2 comment(s)
Filed under: , , ,
17 March 2008 16:52

Set based splitting of delimited strings to columns

I recently presented on using CLR in SQL at DevWeek and commented on how TSQL Udfs just don't perform. A common scenario people in the audience were using TSQL Udfs for was splitting delimited strings. One guy asked me afterwards what the best way...
Posted by simonsabin | 3 comment(s)
Filed under: , ,
16 March 2008 21:21

SQL Server 2008 - Whats this abut 30,000 columns?

Its all about Sparse columns and they are a great feature of Katmai Imagine you have a products table, you will have attributes for some products that don't apply to others i.e. cup size applies to bras but not to socks. There are a number of ways...
Posted by simonsabin | 2 comment(s)
Filed under: , , ,
11 March 2008 11:51

SQL Server 2008 - Converting binary(hex) values to strings

One of the frequently asked questions in the newsgroups is how do you convert a string representation of a binary value to the equivalent binary value and the reverse. The latter is often required to be able to print out the binary representation. There...
Posted by simonsabin | with no comments
Filed under: , ,
04 March 2008 14:25

Replace invalid chracters with valid character

I am updating the SQLBits website to have the content from the weekend and I wanted to have the session titles in the URL. However many of the titles contain invalid characters (well maybe not invalid ones but ones that are escaped). So to avoid URLS...
Posted by simonsabin | 1 comment(s)
Filed under: ,
26 February 2008 13:35

String cleaning with TSQL

There have been some posts on cleaning text with TSQL, Remove all non-numeric characters from a string and extract-only-numbers-from-a-string Here is my take on it. The nice thing is that you just specify the valid characters in the like pattern to select...
Posted by simonsabin | 2 comment(s)
Filed under:
22 February 2008 00:58

SQL Reflector

In the user group meeting today I mentioned an AddIn for Management Studio that did reflector on the assemblies in the database. I was either confused as I don't seem to be able to find the management studio add in. What I did find was a plugin for Reflector...
Posted by simonsabin | with no comments
Filed under: ,
20 February 2008 13:06

SQL Server 2008 – iFTS Performance – DocidMap

The next post in the series on iFTS (Integrated Full Text Search) covers one of the major improvements in SQL Server 2008 for Full Text and is related to the Integrated part or iFTS. SQL Server 2005 introduced a huge performance improvement by moving...
Posted by simonsabin | 3 comment(s)
Filed under: , , ,
20 February 2008 12:11

SQL Server 2008 – iFTS Manageability – Loading thesaurus files

The next post in the series on iFTS (Integrated Full Text Search) covers a thesaurus files. A feature introduced in SQL 2005. The loading of thesaurus files is a bit of an uncontrolled beast in SQL Server 2005 (there weren’t supported or documented prior...
Posted by simonsabin | with no comments
Filed under: , , ,
20 February 2008 11:32

SQL Server 2008 – iFTS Transparency – Viewing the words in the index

This is the next in the series of posts about the new iFTS (Integrated Full Text Search) in SQL Server 2008. One of the huge frustrations with using Full Text prior to SQL Server 2008 was that you had no idea what content the iFilter has extracted from...
Posted by simonsabin | with no comments
Filed under: , , ,
20 February 2008 09:40

SQL Server 2008 – iFTS Transparency – dm_fts_parser

SQL Server 2008 – iFTS Transparency – dm_fts_parser In the next in this series of posts on Integrated Full Text Search (iFTS) in SQL Server 2008, we look at the new dmv dm_fts_parser. Wow thats a cool function name what does it do Simon? Well in my first...
Posted by simonsabin | with no comments
Filed under: , , ,
15 February 2008 10:56

Table Valued Methods on Type

I amd really getting into the use of UDTs as a way of packaging up SQL CLR code. I'm also looking into implementing an array type so I can do code like declare @s dbo . SQLArray set @s . AddKeyword ( 'sas' ) set @s . AddKeyword ( 'dog' ) Fo this to be...
Posted by simonsabin | with no comments
Filed under: , ,
19 January 2008 16:28

SQL Server 2008 TSQL - Table Valued Parameters

Table Valued Parameters have to be one of the coolest features in SQL Server 2008. With them you are able to pass a set of data around as a single variable. What this means is that if you are saving multiple records you can do very easily in one procedure...
Posted by simonsabin | 1 comment(s)
Filed under: ,
09 January 2008 23:21

SQL Server 2008 Spatial - How to create a circle?

How do you create a circle in a planar system. Ok so there are some funky algorithms for creating a set of points all the way round the circle. Well there is a much easier way. Create a point i.e. POINT (10 10) and the use the STBuffer(radius) method...
Posted by simonsabin | with no comments
Filed under: , ,
08 January 2008 22:41

SQL Server 2008 Spatial - Performance of database calls follow up

I recently blogged about the performance of passing spatial data to the database.( SQL Server 2008 Spatial - Performance of database calls? ) In it I found that the the performance radically dropped off as the number of points increases. Whilst this was...
Posted by simonsabin | with no comments
Filed under: , , ,
More Posts Next page »