concept step in category sql

appears as: steps, steps, step, step, The steps
SQL Server MVP Deep Dives

This is an excerpt from Manning's book SQL Server MVP Deep Dives.

Laying out the query like this doesn’t change the importance of knowing how to read a query when you follow the steps I described earlier, but it helps the less experienced people who need to read the queries you write. Bracketing the nested sections may help make the query even clearer, but I find that bracketing can sometimes make people confuse these nested JOINs with derived tables.

Let’s walk through an example that demonstrates the steps involved in separating tables and indexes into different filegroups, then backup the tables-only filegroup, and finish with restoration of the backup.

We’ll begin by reviewing SQL Server’s default table and index filegroup placements to describe default behaviors (by which most databases operate), then move through the steps required to separate indexes from tables before looking at backup and restore.

The @NCIXFGName parameter should be provided with the name of a new filegroup to rebuild nonclustered indexes into. At this stage, this filegroup does not exist, but we’ll create it in step 7. The name provided for this new filegroup must be different from the existing nonclustered index filegroup for this database (which is NCIX_FG in our example) because that filegroup’s descriptive metadata still exists in the system catalogs. For our example, we’ll create another filegroup named N’NCIX_FG2’.

EXEC usp_Manage_NCIX_FileGroup N'Script', N'NCIX_FG2'

Output from the execution of this procedure should contain a list of CREATE INDEX statements, one per nonclustered index in the database. Save this output to a script file (for example NCIXScripts.sql) for later use in step 7. Note that each CREATE INDEX statement will include the filegroup name NCIX_FG2. When we run this script later, nonclustered indexes will be re-created within this filegroup, so we’ll need to add it to the database (which we do in step 7).