Imagine you need to create a database on a customer’s machine, but you don’t know how they have their file system laid out. You can take advantage of the fact that SQL Server keeps track (at the instance level) of the default location for SQL Data files and SQL Log files. This allows you to simply use CREATE DATABASE databasename to create a new database at the default location (with all of the default properties).

In SQL Server 2008, you can specify database default locations when you install SQL Server, and you can change it later by going to Server Properties, Database Settings, and changing the “Database default locations”. This will only affect new databases that are created after the change is made. If someone overrides the default location when they are creating the database, either in the SSMS GUI, or in T-SQL, their change will override the instance level default location settings.

If you decide to just create a database at the default location, there is a pretty good chance that it will be on the C: drive of the database server (for both the data file and the log file), which would not be a good thing. It would be a good idea to detach the database and move the files somewhere more appropriate, and then reattach the database.

At any rate, I was playing around with some of this today, and I thought it might be interesting to other people. If you just CREATE DATABASE, you will have several database properties that you should consider changing, depending on your situation. For example, you probably want to make the data file and log file initial sizes a little larger than their default settings. You also probably want to change their filegrowth settings to be a little larger than their default settings.

My script adds a file group to the database, and then adds a file to that new filegroup. This file group will be used for full text indexes, and ideally, it should be on a different LUN than your base relational tables, but since we don’t know how the filesystem is laid out, we’ll just create it in the same directory as the primary data file. You can fix that when you detach, move and reattach the database. I also explicitly set some database properties to what I want, rather than relying on the default properties.

I also use the SERVERPROPERTY function to figure out whether I have SQL Server 2008 Enterprise Edition on the instance where I am creating the database. If I do, I enable Page data compression on the clustered index of the table that I just created. Finally, I create a full text catalog, and then create a full text index that uses the dedicated FTFILEGROUP file group. Hopefully, you found some useful techniques in this script.

-- One way to create a database 
-- and some objects programmatically
-- Glenn Berry 
-- March 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry
USE [master];
GO
-- Create database with default properties 
-- at default location for SQL Server instance
CREATE DATABASE TestDB;
GO
-- Change data file initial size and file growth values
ALTER DATABASE TestDB MODIFY FILE 
(NAME = N'TestDB', SIZE = 500MB , FILEGROWTH = 500MB);
GO
-- Change log file initial size and file growth values
ALTER DATABASE TestDB MODIFY FILE 
(NAME = N'TestDB_log', SIZE = 500MB , FILEGROWTH = 500MB);
GO
-- Add FTFILEGROUP
ALTER DATABASE TestDB ADD FILEGROUP [FTFILEGROUP];
GO
-- Set some database properties
ALTER DATABASE TestDB SET AUTO_UPDATE_STATISTICS_ASYNC ON; 
GO
ALTER DATABASE TestDB SET RECOVERY SIMPLE WITH NO_WAIT;
GO
-- Switch to new database
USE TestDB;
GO
-- Retrieve path to data file
DECLARE @PhysicalName nvarchar(260);
SET @PhysicalName =(SELECT physical_name 
                    FROM sys.database_files
                    WHERE file_id = 1);
-- Change filename to something different (with ndf file extension)
DECLARE @NewPhysicalName nvarchar(260);
SET @NewPhysicalName = (SELECT REPLACE(@PhysicalName,'TestDB.mdf','TestDB_FT.ndf'));
-- Build dynamic SQL for ALTER DATABASE ADD FILE command                  
DECLARE @NewSQLCommand nvarchar(1000);
SET @NewSQLCommand = 'ALTER DATABASE [TestDB] ADD FILE (NAME = N''TestDB_FT'', FILENAME = N'''
SET @NewSQLCommand = @NewSQLCommand + @NewPhysicalName 
SET @NewSQLCommand = @NewSQLCommand + '''' + ', SIZE = 500MB , FILEGROWTH = 500MB) TO FILEGROUP [FTFILEGROUP];'
-- Run command to create add file in FTFILEGROUP
EXEC sp_executesql @NewSQLCommand;
-- Create a new table
CREATE TABLE [dbo].[TestTable](
    [TestID] [int] IDENTITY(1,1) NOT NULL,
    [TestTextColumn] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [TestID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
-- Check for SQL Server 2008 and Enterprise Edition
IF LEFT(CONVERT(CHAR(2),SERVERPROPERTY('ProductVersion')), 2) = '10' 
   AND SERVERPROPERTY('EngineEdition') = 3
      BEGIN
            -- Use Page Compression on the clustered index if 
            -- we have SQL Server 2008 Enterprise Edition or greater
            ALTER TABLE [dbo].[TestTable] REBUILD PARTITION = ALL
            WITH (DATA_COMPRESSION = PAGE);
      END
      
      
-- Create full text catalog as default
CREATE FULLTEXT CATALOG [FTCatalog]WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
AUTHORIZATION [dbo];
GO
-- Create FT Index on TestTextColumn column of dbo.TestTable table
CREATE FULLTEXT INDEX ON [dbo].[TestTable]([TestTextColumn] 
LANGUAGE [English])
KEY INDEX [PK_TestTable] ON ([FTCatalog], FILEGROUP [FTFILEGROUP])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM);