HttpCode

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!