Write code to create a database, set location after the difference between creating a database with a visual interface

First, the creation of the database on the Internet to search the processing method, slightly improved a bit CREATE DATABASE Tttt_1
On PRIMARY
(  
NAME = test1,
filename= ' F:\test\test1.mdf ',--this path must exist before it can be built successfully
SIZE = ten,
MAXSIZE = UNLIMITED,- -Unlimited growth
filegrowth = 5
)  
LOG on
(  
name= ' Test1_dat ',
filename= ' F:\test\test1.ldf ',--this path must exist before it can be built successfully
SIZE =5MB,
MAXSIZE = 25MB,
filegrowth =5MB
)  
GOSecond, the database created with this method is different from the discovery created by the visual interface. Right-click New database to create a comparison between the two types of table scripts. difference and find out what these differences mean. 1) The difference between Enable_broker and set Disable_broker, and turn from http://www.cnblogs.com/wanghk/archive/2012/05/12/2497170.htmlSQL Server Service Broker for the current database is not enabled, so query notifications are not supported. If you want to use notifications, enable Service Broker for this database

The problem encountered last night also knows that the notifications service relies on the underlying Service broker. I thought it was only possible to execute the following script to enable Service Broker for the database.

ALTER DATABASE DBNAME set Enable_broker

However, after execution, the script has been in the execution state, do not agree, just in the busy other things do not see the results of the operation, the results of this morning a look, incredibly run has not ended. Although it is performed on a production database, the database is only 30G in appearance, but it does not have to be executed for an evening and is not finished, but to terminate execution, using

SELECT is_broker_enabled from sys.databases WHERE name = ' DBNAME '

View is_broker-enabled as 0, Service Broker is still not enabled

After Google, in a foreign forum found a solution:

ALTER DATABASE DatabaseName SET new_broker with ROLLBACK IMMEDIATE;
ALTER DATABASE Databasename SET enable_broker;

Executes the above 2 statements without any waiting, prompting the command to complete.

SELECT is_broker_enabled from sys.databases WHERE name = ' DBNAME '

View Is_broker-enabled to 1

The New_broker option, explained in SQL Server Books Online:

Each database contains a Service Broker identifier. The service_broker_guid column of the sys.databases catalog view displays the Service Broker identifier for each database in the instance. Service Broker routing uses Service Broker identifiers to guarantee that all messages for a session are passed to the same database. Therefore, the Service Broker identifier should be unique across all instances on the same network. Otherwise, the message may be misrepresented.

SQL Server generates a new Service Broker identifier for each new database. Because the identifier is new, SQL Server can safely activate Service Broker message delivery in the new database. Other databases on the network should not have the same Service Broker identifier.

New_broker. This option activates Service Broker message delivery and creates a new Service Broker identifier for the database. Because none of the existing sessions in the database use the new instance identifier, this option ends these sessions and returns an error.

ROLLBACK immediate will roll back the unfinished transaction immediately.

There are four options for managing identifiers and message delivery:

• Enable_broker. This option activates Service Broker message delivery and retains the existing Service Broker identifier for the database.

  Note
  Enable SQL SERVER&N in any database Bsp Service Broker requires a database lock. To enable Service Broker in the msdb database, first stop SQL Server agent. The Service Broker can then obtain the necessary locks.

• Disable_broker. This option disables Service Broker message delivery and retains the existing Service Broker identifier for the database.

• New_broker. This option activates Service Broker message delivery and creates a new Service Broker identifier for the database. Selecting this option ends all existing sessions in the database and returns an error for each session. This is because these sessions do not use the new identifiers. Any route referencing the old Service Broker identifier must be recreated with the new identifier.

• Error_broker_conversations. This option activates Service Broker message delivery and retains the existing Service Broker identifier for the database. Service Broker ends all sessions in the database and returns an error for each session. Typically, you would use this option if you have an open session between a database and another database, and you want to restore the database to a different point in time than the database that you are talking to. All sessions in the restored database must end with an error because these sessions are now out of sync with the other databases. The Service Broker identifier is reserved so that all routes referencing the identifier are still valid.

SQL Server does not allow two databases with the same Service Broker identifier to activate message delivery in the same instance of SQL Server, regardless of which of the specified options is selected. If you attach a database that has the same Service Broker identifier as an existing database, SQL Server deactivates Service Broker Message delivery in the attached database.

2) The database created by the handwriting script is not specified primary

PRIMARY

The first file specified in the <filespec> item in the primary filegroup becomes the primary file.    a database can have only one master file.    database files and filegroups. " > For more information, see Database Files and Filegroups.   

If you do not specify a PRIMARY, the first file listed in the CREATE DATABASE statement becomes the primary file.

IF not EXISTS (SELECT name from Sys.filegroups WHERE is_default=1 and name = N ' PRIMARY ') ALTER DATABASE [tttt1] MODIFY FIL Egroup [PRIMARY] DEFAULT
GO

3) manually created build script with trustworthy OFF but not this option automatically

By default, this setting is "OFF"

Write code to create a database, set location after the difference between creating a database with a visual interface