HttpCode

I came across an interesting problem the other day, I needed to do some string manipulation with SQL Server, normally dealing with this sort of stuff sucks, I wanted to split a string like  ‘item1, item1, item3’ .. so splitting on a given character. The first thing that popped to my mind was a Table Valued function, something like this:

-- Splits a column based on a specified delimiter
CREATE FUNCTION dbo.Split
(
       @List nvarchar(2000),
       @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

       Id int identity(1,1),
       Value nvarchar(2000)
)
AS
BEGIN

       While (Charindex(@SplitOn,@List)>0)
       Begin
               Insert Into @RtnValue (value)
               Select  Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
               Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
       End

       Insert Into @RtnValue (Value)
               Select Value = ltrim(rtrim(@List))

               Return
END

There are many examples of this type of function on web, my point isn’t the actual implementation, rather the way you can use it.

I can now run the query:

select Value from dbo.Split('item1, item2, item3',',')

But my problem was a little different from the trivial example above, I wanted to pass data into this function that was the result of my query, rather than just passing arguments in statically like above and using the function as a table, I really wanted to apply the function to the data from my table, on a row by row level.

After a little searching I found the CROSS APPLY SQL syntax, with this I can now do exactly what I want:

select SF.Value from TestTable TT
cross apply dbo.Split(TT.Value, ',') SF

The above query simple selects the data from the TestTable, then cross apply’s the Split function passing it the TestTable’s value column (which contains the data ‘Item1, Item2, Item3, Item4’, also note that I’m selecting the column from the Split function.

This Produces the desired results:

Given a table like:

And data like:

I thought this was pretty cool. SQL can be fun.

Recently I had to write a SQL Server Reporting Services Report that used a web service for the data source, the web service returned a horrible .NET DataSet object which I had no control over.

To use a web service from Reporting Services, the first step is to create a new Data Source of type: XML:

Then enter the URL to the web service in the connection string section.

The next step is to create a new DataSet for the report.

In the Query Designer enter the following:

<Query>
   <Method Name="<web service method name>" Namespace="<webservice namespace url>”>
   </Method>
   <ElementPath IgnoreNamespaces="True">
<web service method name>Response/<web service method name>Result/diffgram/NewDataSet/Table1   (replace Table1 with the dataset name if used)

    </ElementPath>

</Query>

If you need to pass parameters to the web service, this can be done by adding something like:

<Parameters>
       <Parameter Name="<parameter name>">
           <DefaultValue></DefaultValue>
       </Parameter>
   </Parameters>

But be sure that the case of the parameter name is exactly the same as the parameter in the report that you wish to pass in.

By default SQL Server will auto grow your databases by 1 MB for your data file and 10% for your log files. There have been numerous discussions about the usefulness of this default value, but at the end of the day you should really understand how this will effect your database.

In the world of SharePoint it’s common for users to upload multiple mega-byte files, which will be stored in the content database and would cause the database file to grow and could potentially cause performance issues.

Another SharePoint scenario that needs consideration is the SSP databases, the indexing of content performs a large number of write operations of the metadata associated with the crawled content. It might be wise to monitor the performance of these databases to determine if changing the auto growth rate would help.

If you came to the conclusion that you did want to change the default options for all new databases, then you can simply change the default values of the model database, you can do this by right clicking on the model database and selecting the button next to the file you wish to change:

Like all performance related tips, it’s best if you conduct some of your own analysis.

If you run your SQL Server on a non standard port you may have experienced issues when installing SharePoint on this server. The standard psconfig utility has a field for the server name, if you give it a comma and then the port your SQL box is running on it will fail. The psconfig wizard doesn't understand the port number format. This isn't just related to SharePoint, I had to use this same trick when I was doing a Great Plains install.

The trick is to create an Alias, first run 'cliconfg', you should be presented with the following UI:

Select the Alias tab, then Add - you should then see the above dialog. Now you just need to enter the server alias, I normally just make this the server name, but your circumstances may be different, then unselect the 'Dynamically determine port' checkbox so you can enter the port number that your SQL Server runs on.

Once the Alias is setup you can refer to the SQL Server by the server alias that you have setup here.

One of my favourite features of SQL Server 2008 is the Merge or 'upsert' Statement. Lets say we have an ETL process that prepares a data stage table, without the merge statement you might first delete all the records from the staging table and then insert them all again. If you have large data sets this can become quite time consuming and well frankly it doesn't seem very elegant. Another alternative is to use a cursor to iterate over each record and look for changes, you might also have data that has a timestamp which might help you write a set based operation, both of these approaches are less than ideal.

Well as of SQL 2008 you can use the Merge Statement:

Lets say you have a simple table like:

CREATE TABLE People
(
  ID       INT PRIMARY KEY,
  Name     Varchar(50) NOT NULL,
  Position Varchar(50) NOT NULL,
  Age      INT NOT NULL
);

Assuming our StagedPeople table is the same structure the merge statement would look like:

MERGE INTO People
USING StagedPeople
 ON People.ID = StagedPeople.ID
WHEN MATCHED THEN
-- if we find a match, then perform an update  
UPDATE SET
  Age = StagedPeople.Age,
  Name = StagedPeople.Name,
  Position = StagedPeople.Position
WHEN NOT MATCHED THEN
 -- if we don't find a match, then insert a new record
 INSERT (ID, Name, Position, Age)
 VALUES (StagedPeople.keycol, StagedPeople.Name, StagedPeople.Position, StagedPeople.Age)
WHEN SOURCE NOT MATCHED THEN
 -- we could delete the record, since it's not found in the Staged table
DELETE;
OUTPUT $Action

 

The $action variable returns a varchar(20) of the operation that has taken place,  i.e. 'INSERT', 'UPDATE', 'DELETE'

The operations and full reference can be found here.

It seems like a pretty fundamental operation, glad it only took till 2008 for us to have it :)

This week I picked up this simple tip for spatial enabled applications. Just say you have an existing database that has the latitude and longitude stored as separate columns such as a Latitude and Longitude as you would like to make these columns available so that spatial operations can be performed on them.

Create a view on the table with the Lat, Long as parameters to the Geography::Point function which creates a spatial point.

Your table might look like:

The following SQL will do the trick to convert the points to spatial column:

CREATE VIEW SpatialView
AS

-- Create a view over a table without a spatial column
SELECT Name, geography::Point(Latitude, Longitude, 4326) as Location FROM UnSpatialTable

So now you have a spatial column that you can include in your spatial queries such as, finding the distance between points:

DECLARE @location GEOGRAPHY

SELECT @Location = Location from SpatialView where name = 'test'


SELECT @Location.STDistance(Location) as DistanceToOther from SpatialView

Creating a buffer around a point:

SELECT Location.STBuffer(20) as BufferedLocation from SpatialView where Name = 'test'

This approach might be useful if you collecting data on a mobile device running the compact edition of SQL which doesn't have any spatial features. You can collect your data as lat,long values and then do the spatial manipulation via the view. Don't forget to think about spatial indexes!