Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the meaning of creating partitioned tables in data warehouse in SQLServer database". In daily operation, I believe that many people have doubts about what it means to create partitioned tables in data warehouse in SQLServer database. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what does SQLServer database mean to create partitioned tables in data warehouse?" Next, please follow the editor to study!

What does SQLServer database mean by creating partitioned tables in a data warehouse

The creation process is as follows:

1. Create a new ServerSecurity/Database/' > SQLServer script project

(1) start ServerSecurity/Database/' > SQLServerManagementStudio from the MicrosoftServerSecurity/Database/' > SQLServer2008 program group in the start-> all programs menu.

(2) in the Connect to Server dialog box, verify that the following settings are correct and click Connect:

Server type: database engine

Server name: (local)

Authentication: Windows authentication

(3) on the File menu, point to New, and then click Project.

(4) make sure the ServerSecurity/Database/' > SQLServer script is selected, and then enter the following settings:

Name: PartitionProcessing

Location: CRV Server SecurityUniverse SQLHOLsPartitioningStarter

Solution name: PartitionProcessing

(5) make sure the directory where the solution was created is selected, and then click OK.

(6) in solution Explorer, right-click the connection, and then click New connection.

(7) in the Connect to Server dialog box, verify that the following settings are correct and click OK:

Server name: (local)

Authentication: Windows authentication

two。 Create filegroups and files

(1) in solution Explorer, right-click the connection you added in the steps in the last article, and then click New query.

Right-click ServerSecurity/Database/' > SQLQuery1.sql, and then click rename.

(3) Type FilesandFilegroups.sql, then press Enter.

(4) Type the following code (each FILENAME parameter should have a single line).

User [master] ALTERDATABASE [AdventureWorksDW] ADDFILEGROUP [fg2001] GOALTERDATABASE [AdventureWorksDW] ADDFILEGROUP [fg2002] GOALTERDATABASE [AdventureWorksDW] ADDFILEGROUP [fg2003] GOALTERDATABASE [AdventureWorks DW] ADDFILEGROUP [AdventureWorksDW] ADDFILE (NAME=N'AdventureWorksDW_Data2001',FILENAME=N'C:ProgramFilesMicrosoftServerSecurity/Database/' > SQLServerMSServerSecurity/Database/' > SQL10.MSServerSecurity/Database/' > SQLSERVERMSServerSecurity/Database/' > SQLDATAAdventureWorksDW_Data2001.ndf',SIZE=2048KB,FILEGROWTH=1024KB) TOFILEGROUP [AdventureWorksDW] (NAME=N'AdventureWorksDW_Data2002') FILENAME=N'C:ProgramFilesMicrosoftServerSecurity/Database/' > SQLServerMSServerSecurity/Database/' > SQL10.MSServerSecurity/Database/' > SQLSERVERMSServerSecurity/Database/' > SQLDATAAdventureWorksDW_Data2002.ndf',SIZE=2048KB,FILEGROWTH=1024KB) TOFILEGROUP [fg2002] GOALTERDATABASE [AdventureWorksDW] ADDFILE (NAME=N'AdventureWorksDW_Data2003',FILENAME=N'C:ProgramFilesMicrosoftServerSecurity/Database/' > SQLServerMSServerSecurity/Database/' > SQL10.MSServerSecurity/Database/' > SQLSERVERMSServerSecurity/Database/' > SQLDATAAdventureWorksDW_Data2003.ndf',SIZE=2048KB,FILEGROWTH=1024KB) TOFILEGROUP [fg2003] GOALTERDATABASE [AdventureWorksDW] ADDFILE (NAME=N'AdventureWorksDW_Data2004' FILENAME=N'C:ProgramFilesMicrosoftServerSecurity/Database/' > SQLServerMSServerSecurity/Database/' > SQL10.MSServerSecurity/Database/' > SQLSERVERMSServerSecurity/Database/' > SQLDATAAdventureWorksDW_Data2004.ndf',SIZE=2048KB,FILEGROWTH=1024KB) TOFILEGROUP [FG2004] GO

(5) Click execute.

What does SQLServer database mean by creating partitioned tables in a data warehouse

3. Create a partition function

(1) in solution Explorer, right-click the connection, and then click New query.

Right-click ServerSecurity/Database/' > SQLQuery1.sql, and then click rename.

(3) Type CreatePartitionFunction.sql, then press Enter.

(4) Type the following code.

USEAdventureWorksDWCREATEPARTITIONFUNCTIONpf_OrderDateKey (int) ASRANGERIGHTFORVALUES (185550) GO

(5) Click execute.

Note: the partition function provides the boundary between the two filegroups. In this case, the value is the key corresponding to January 1.

4. Create a partition scheme

(1) in solution Explorer, right-click the connection, and then click New query.

Right-click ServerSecurity/Database/' > SQLQuery1.sql, and then click rename.

(3) Type CreatePartitionScheme.sql, then press Enter.

(4) Type the following code. Click execute.

USEAdventureWorksDWCREATEPARTITIONSCHEMEps_OrderDateKeyASPARTITIONpf_OrderDateKeyTO (fg2001,fg2002,fg2003,fg2004) GO

Note: although only two boundaries are listed in the partition function, there are four filegroups listed in the partition function. The fourth filegroup is provided as the next filegroup for future filegroup splitting.

At this point, the study on "what is the meaning of creating partitioned tables in data warehouse in SQLServer database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.