GEOG 868
Spatial Database Management

Looking Under the Hood of a SQL Server Geodatabase

PrintPrint

Looking Under the Hood of a SQL Server Geodatabase

A. Feature class storage in SQL Server

Let's take a look at how the data we've been working with in ArcGIS Pro is stored in SQL Server.

  1. If you need to, log in to your enterprise geodatabase instance using Windows Remote Desktop.
  2. If you are still in the remote connection used for the earlier parts of the lesson, close ArcGIS Pro and open SQL Server Management Studio.
  3. Connect to the localhost server, and browse inside the egdb database (Databases > egdb).
  4. Expand the Tables folder, and note that all of the tables we've worked with in the last couple of lessons are found here (along with many others, some of which we'll discuss momentarily). 
  5. Open the jb.CITIES table (right-click > Select Top 1000 Rows). Under the Results tab, you should see all of the attribute data along with a SHAPE column. Keep in mind that the values in the shape column are in Microsoft geometry format. You could work with your data using raw SQL much like we did with PostGIS by taking advantage of SQL Server's spatial data tools [https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-sql-server]. 
  6. Close the CITIES table.
    But in the Object Explorer pane, keep the list of Tables expanded.

B. Repository tables

ArcSDE relies on a number of tables behind the scenes. Many of these so-called repository tables are owned by the dbo superuser.

  1. Looking at the Tables listing, you should see a few "GDB_*" tables and many "SDE_*" tables. It's not important for you to know the purpose of all of these tables, but there are a few that are worth discussing.

    The SDE_layers table stores information on all of the geodatabase's feature classes, which can be displayed as layers.
  2. Open the dbo.SDE_layers table. The first column (layer_id) stores a unique ID for each feature class.
  3. Close the SDE_layers table.

    You may notice a number of "i" tables. These tables help ArcGIS keep track of the next unique identifier available in each table. There is one "i" table for each feature class. The relationship between the "i" tables and their associated feature classes can be found in SDE_table_registry.
  4. Open the SDE_table_registry table. The registration_id value is the linkage between the feature class and its "i" table. You might open up one of the "i" tables and note that the base_id indicates the next ID to be used in the event a new feature is added.
  5. Close the SDE_table_registry table.

    Finally, it's worth pointing out that the "GDB_*" tables are where geodatabase behavior information is stored. For example, you can see the relationship between feature classes and their parent feature datasets in these tables.
  6. Open the GDB_items table. Locate the row with a Name value of egdb.CENSUS.usa_L7, and make note of its UUID value. On my instance, its UUID is "{D7034507-7188-467F-BFB1-41F1F3FE2F3D}", but it will be different on your instance.
  7. Now, open the GDB_itemrelationships table. You should see that same value appear three times in the OriginID column.
    The DestID values for those rows correspond to the UUID values of the egdb.CENSUS.us_cities, egdb.CENSUS.states and egdb.CENSUS.counties feature classes found in the GDB_items table.

It's not really important that you remember much about these repository tables. However, hopefully, you now have a bit of an appreciation for what's going on behind the scenes and will see the tables as a bit less of a mystery.

Always remember to Stop your Instance when you finish or when you take a long break.