Datasheet

database. For example, it has always bugged me that Transact-SQL does not contain a Trim function to
truncate both leading and trailing spaces from a string of characters. Transact-SQL offers an
RTRIM func-
tion that truncates trailing spaces and an
LTRIM function that removes leading spaces. The code to suc-
cessfully implement a traditional trim operation thus becomes the following:
LTRIM(RTRIM(‘character string’))
To reduce my irritation level and the number of characters I needed to type to successfully trim a charac-
ter string, I created my own
TRIM function in the Model database with the following code:
USE Model
GO
CREATE FUNCTION dbo.Trim (@String varchar(MAX))
RETURNS varchar(MAX)
AS
BEGIN
SELECT @String = LTRIM(RTRIM(@String))
RETURN @String
END
After creating this function in the Model database, it will be propagated to all databases created after
adding it to the
Model database and can be utilized with the following simplified code:
dbo.TRIM(‘character string’)
I know it’s only a saving of two characters, but those two characters are open and close parenthesis char-
acters, which are often the source of annoying syntax errors. By reducing the nested functions, the over-
all complexity of the function call is also reduced.
Almost any database object can be added to the
Model database so that they are available in subse-
quently created databases. This includes database users, roles, tables, stored procedures, functions, and
assemblies.
The MSDB Database
I mostly think of the MSDB database as the SQL Server Agent’s database. That’s because the SQL Server
Agent uses the
MSDB database extensively for the storage of automated job definitions, job schedules,
operator definitions, and alert definitions. The SQL Server Agent is described in greater detail in Chapter 8,
but for now, just know that the Agent is responsible for almost all automated and scheduled operations.
The SQL Server Agent is not the only service that makes extensive use of the
MSDB database. Service
Broker, Database Mail, and Reporting Services also use the
MSDB database for the storage of scheduling
information. In addition to automation and scheduling information, SQL Server Integration Services
(SSIS) can also utilize the
MSDB database for the storage of SSIS packages.
The TempDB Database
The TempDB database is used by SQL Server to store data— yes, you guessed it, temporarily. The TempDB
database is used extensively during SQL Server operations, so careful planning and evaluation of its size
and placement are critical to ensure efficient SQL Server database operations.
15
Introducing SQL Server 2005
04_047046 ch01.qxp 10/18/06 12:18 AM Page 15