Use Transact-SQL to create database objects in SQLServer

The data warehouse of AdventureWorksCycles contains several very large tables. To improve data storage, you have decided to partition several tables across file groups. In this way, you can control the basic disk storage. Prerequisites before starting this experiment, you must: Use Transact-SQL to create a database object in the MicrosoftSQLServer Database

The data warehouse of Adventure Works Cycles contains several very large tables. To improve data storage, you have decided to partition several tables across file groups. In this way, you can control the basic disk storage. Prerequisites before starting this experiment, you must: Use Transact-SQL to create a database object in the Microsoft SQL Server database

The data warehouse of Adventure Works Cycles contains several very large tables. To improve data storage, you have decided to partition several tables across file groups. In this way, you can control the basic disk storage.

Prerequisites

Before starting this experiment, you must:

Use Transact-SQL in Microsoft®SQL Server®Experience in creating objects.

Lab settings

Lab scenario

The data warehouse of Adventure Works Cycles contains several very large tables. To improve data storage, you have decided to partition several tables across file groups. In this way, you can control the basic disk storage.

When you partition several tables across file groups, you want to add features and improve the performance of your solution. You decide to allocate data based on the order date, instead of arbitrarily splitting data across file groups. Because you only modify the file group that contains the latest data, this will increase the speed of backup and restoration. In addition, you can regularly archive file groups to move historical data to another table so that the size of the active table remains manageable.

　　1. Create a partitioned table

In this exercise, you will create a partitioned data warehouse fact data table. Large tables often need to store data across several disk volumes. SQL Server tables cannot be placed in specific files. However, file groups can be placed in files, and tables can be allocated to file groups. In this way, you can control the storage of data in tables that are very large in SQL Server. In addition, it is very useful to define which data is stored in which file group if the table contains multiple file groups. The partition function provides this function by splitting tables horizontally based on the values in a specific column.

Note: You can copy the scripts used in this exercise in the Partition Processing. ssmssln Solution in the C: \ SQLHOLS \ Partitioning \ Solution \ Partition Processing folder.

Create an SQL Server script Project

1. Start from the Microsoft SQL Server 2008 Program Group in the "Start"> "All Programs" menu.

SQL Server Management Studio.

2. In the connection dialog box, verify the following settings and click Connect:

Server Type: Database Engine

Server Name: (local)

Authentication: Windows Authentication

3. On the File menu, point to new and click Project.

4. Make sure that the SQL Server script is selected and enter the following settings:

Name: Partition Processing

Location: C: \ SQLHOLs \ Partitioning \ Starter

Solution name: Partition Processing

5. Make sure that the directory for creating the solution is selected, and click OK.

6. In Solution Explorer, right-click the connection and click Create connection.

7. In the connect to server dialog box, verify the following settings and click OK:

Server Name: (local)

Authentication: Windows Authentication

Create File groups and files

1. In Solution Explorer, right-click the connection added in the previous step, and then click Create query.

2. Right-click SQLQuery1. SQL and click rename.

3. Type Files and Filegroups. SQL, and then press Enter.

4. type the following code (each FILENAME parameter should take one line ).

　 USE [ Master ]

　　 ALTER DATABASE [ AdventureWorksDW ] ADD FILEGROUP [ Fg2001 ]

　　 GO

　　 ALTER DATABASE [ AdventureWorksDW ] ADD FILEGROUP [ Fg2002 ]

　　 GO

　　 ALTER DATABASE [ AdventureWorksDW ] ADD FILEGROUP [ Fg2003 ]

　　 GO

　　 ALTER DATABASE [ AdventureWorksDW ] ADD FILEGROUP [ Fg2004 ]

　　 GO

　　 ALTER DATABASE [ AdventureWorksDW ] ADD FILE

(NAME = N ' AdventureWorksDW_Data2001 ' ,

FILENAME = N ' C: \ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ DATA \ AdventureWorksDW_Data2001.ndf ' ,

SIZE = 2048KB, FILEGROWTH = 1024KB) TO FILEGROUP [ Fg2001 ]

　　 GO

　　 ALTER DATABASE [ AdventureWorksDW ] ADD FILE

(NAME = N ' AdventureWorksDW_Data2002 ' ,

FILENAME = N ' C: \ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ DATA \ AdventureWorksDW_Data2002.ndf ' ,

SIZE = 2048KB, FILEGROWTH = 1024KB) TO FILEGROUP [ Fg2002 ]

　　 GO

　　 ALTER DATABASE [ AdventureWorksDW ] ADD FILE

(NAME = N ' AdventureWorksDW_Data2003 ' ,

FILENAME = N ' C: \ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ DATA \ AdventureWorksDW_Data2003.ndf ' ,

SIZE = 2048KB, FILEGROWTH = 1024KB) TO FILEGROUP [ Fg2003 ]

　　 GO

　　 ALTER DATABASE [ AdventureWorksDW ] ADD FILE

(NAME = N ' AdventureWorksDW_Data2004 ' ,

FILENAME = N ' C: \ Program Files \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ DATA \ AdventureWorksDW_Data2004.ndf ' ,

SIZE = 2048KB, FILEGROWTH = 1024KB) TO FILEGROUP [ Fg2004 ]

　　 GO