Using SQL with the SharePoint Team Services Database

  • Article

SharePoint Team Services from Microsoft uses joins to pull information from different fields together. To account for NULL or missing values in the lookup fields (for example, documents without an author or documents with an author who has been deleted), SharePoint Team Services uses a LEFT JOIN when querying lists with lookup columns. An INNER JOIN does not return records unless the two tables do not have NULL matching values.

The following example demonstrates the use of a LEFT JOIN:

SELECT
      MyList.ID, MyList.Title,
      People.Name AS CreatedBy,
      People1.Name AS ModifiedBy
FROM
      MyList LEFT OUTER JOIN People
      ON MyList.CreatedBy = People.ID
      LEFT OUTER JOIN People AS People1
      ON MyList.ModifiedBy = People1.ID

Editing Data in Lists

SharePoint Team Services does not provide bulk edits and modifies data in a list one record at a time. This section describes the SQL syntax for inserting, updating, and deleting records.

Inserting Data

The command to add data to a table uses the following syntax:

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2, ...)

Inserting URLs

When inserting URLs, SharePoint Team Services must first check to see if the URL exists. If the URL already exists in the table, the ID of that URL is stored. If the URL does not exist, a new row is inserted into the URL table and the ID of the new URL is inserted into the list table.

Updating Data

The command to update data uses the following syntax:

UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE
primary_key_column = primary_key_value
AND
Version = current_version_value

The version line in the WHERE clause is used for concurrency checking. It checks to see if the record that is about to be updated has the same value as when editing the data was first begun. If someone else changes the record, the user is warned before they overwrite the changes. If the number of records affected by the update statement is zero, a concurrency error results.

Deleting Data

The command to delete data from a list uses the following syntax:

DELETE FROM table_name
WHERE
primary_key_column = primary_key_value
AND
Version = current_version_value

Literals

When passing literal values to and from the database, the following delimiters and formatting are used:

Data Types Delimiter Comments Examples
Numbers <none> Dot decimal separator

No thousands separator

 1

1.222

157832

1008.007
Text Values Single Quote Quotes are doubled up inside of strings 'foo'

'foo bar'

'Jim''s List'
Date/Time Values Single Quote Unambiguous date formatting is used: year-month-day hours-minutes-seconds '1999-10-31 18:30:00'

Modifying Lists

The following sections specify the SQL syntax for modifying lists.

Add List

Adding a list means making a new entry in the List of Lists table (see Inserting Data) and creating a new table for the list. Creating a table uses the following syntax:

CREATE TABLE table_name
      (
      ID int IDENTITY(1,1), PRIMARY KEY
      Title nvarchar NOT NULL
      MyFloatColumn float,
      MyCurrencyColumn float,
      MyBooleanField tinyint
      ...)

Delete List

Deleting a list means deleting an entry in the List of Lists table (see Deleting Data) and dropping the table containing the list data. Dropping a table uses the following syntax:

DROP TABLE table_name

Add Field

Adding a field to a list means adding field metadata to the List of Lists table (see Updating Data) and altering the list data table to add the column. Adding a field to the list data table uses the following syntax:

ALTER TABLE table_name
ADD COLUMN column_name data_type

Note: SQL Server only allows you to add columns at end of the table. The order of columns in the table should have no impact on the order of columns in the user interface. User interface column order is metadata that should be stored in the Views XML schema.

Delete Field

Deleting a field from a list means deleting the field metadata from the List of Lists table (see Updating Data) and altering the list data table to drop the column. Deleting a field uses the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name

Rename List/Field

Renaming a list or field means modifying the display name for the field or list in the FieldsTypes XML schema stored in the List of Lists table (see Updating Data). The list data table is not altered at all: The column names and table name remain the same.

See Also

Overview of the SharePoint Team Services Database

List Data Types