This command will display the current optimization statistics for a table or view. Before you can show the statistics, you first need to create them. The following snippet illustrates how this is done. Provide a statistics name, the table (e.g., READING), and the column (e.g., VALUE) for which you want to capture statistics:
CREATE STATISTICS ReadingValue ON READING ([VALUE]) WITH SAMPLE 5 PERCENT
Once complete, execute the following command with a reference to the table and statistics name:
DBCC SHOW_STATISTICS (“READING”, “ReadingValue”)
The result is illustrated in Figure 2.25. A discussion of the interpretations, meaning, and actions to be taken from this report are outside the scope of this book.
FIGURE 2.25 Output of running the SHOW_STATISTICS command
DBCC SHOWRESULTCACHESPACEUSED
Like the PDW_SHOWSPACEUSED command, this command shows the storage consumed by the data stored in cache. This command is not supported when running on an Azure Synapse serverless SQL pool.
Data Definition Language
Data Definition Language (DDL) SQL commands like CREATE, ALTER, TRUNCATE, and RENAME are considered part of the Data Definition Language (DDL). There are lots of SQL commands, so grouping them into different categories is helpful when learning about managing databases. DDL commands are used to define, configure, and remove data structures, like tables, views, schema, indexes, and the database itself. You typically do not run DDL commands to work directly with data. Instead, use these commands perform required actions at the table level or higher. DDL commands have great impact, especially when considering the power of the DROP command.
DROP TABLE READING
DROP DATABASE brainjammer
Remember from Exercise 2.1 where you created an Azure SQL database. If you execute those DROP commands, the referenced table and database will be deleted. Unlike when you are working in Windows, you are not asked to confirm if you really want to delete the database. If you execute the command, the database is deleted without question. There are many reasons to perform backups of your database; this is one such example. Here are a few other DDL SQL commands that may be useful.
DESCRIBE
The command is not supported on either Azure SQL or Azure Synapse Analytics SQL pool. It is supported only when using a Spark pool. You use this command to show the details of a table. Execute the DESCRIBE command on a Spark pool in Azure Synapse Analytics using the following SparkSQL syntax:
%%sql
DESCRIBE TABLENAME
The alternative to DESCRIBE on Azure SQL or a SQL pool is the following:
exec sp_columns TABLENAME
EXPLAIN
This command is useful for discovering the query plan of a SQL statement run on Azure Synapse Analytics. This command is not supported by the serverless SQL pool, but only on dedicated SQL pools. This is similar to an explain plan, which I mentioned earlier, and will be covered more in Part IV of this book. Here is an example:
EXPLAIN WITH_RECOMMENDATIONS
SELECT SESSION.SESSION_DATETIME, SCENARIO.SCENARIO
FROM SESSION
INNER JOIN SCENARIO ON SESSION.SCENARIO_ID = SCENARIO.SCENARIO_ID
The result is an XML‐formatted report explaining how the query would execute. The details can provide some insights into bottlenecks and location of latency.
