Microsoft® SQL Server® 2012 Bible

814

Part V: Enterprise Data Management

and uses the with option to specify procedure options: in this case, recompile (to indi-

cate that the database engine doesn’t need to cache a plan for this procedure and to

compile the procedure at run time).

Fixing the Script

Because you selected to script for a Stand-alone instance and not for SQL Database, the

script includes some syntax and statements that aren’t supported in SQL Database. Figure

31-7 shows some of the errors you see if you try to run the script as generated.

FIGURE 31-7

Script execution errors.

Another problem is that SQL Database doesn’t support heap tables. A heap table is one with-

out a clustered index. SQL Database currently supports only clustered tables.

You need to make some changes for your script to run under SQL Database. Follow these

steps:

1. Delete all instances of SET ANSI_NULLS ON.


2. Delete all instances of ON [PRIMARY].


3. Delete all instance of PAD_INDEX = OFF as well as ALLOW_ROW_LOCKS = ON and
   ALLOW_PAGE_LOCKS = ON.


4. In the Users table, modify the rowguid column, changing
   DEFAULT NEWSEQUENTIALID() to NULL.


5. In the stored procedure, remove the encryption clause.


6. Add a clustered index to any heap tables.
   Following is a quick explanation of the reason for the changes you previously made:

■ (^) ON [PRIMARY] isn’t needed because, as you learned in Chapter 1, “The World of SQL
Server,” and Chapter 2, “Data Architecture,” SQL Database hides all hardware-spe-
cifi c access and information. There is no concept of primary or fi le groups because
disk space is handled by Microsoft, so this option isn’t required.
■ (^) According to SQL Server Books Online (BOL) you can remove the entire WITH
clause that contains the table options. However, the only table options you need
c31.indd 814c31.indd 814 7/31/2012 10:00:26 AM7/31/2012 10:00:26 AM
http://www.it-ebooks.info