Useful Transact-SQL

Last updated Apr 18, 2023

1

SET STATISTICS IO ON

Causes SQL Server to display information about the amount of physical and logical IO activity generated by Transact-SQL statements. Physical IO is related to accessing data pages on disk and logical IO is related to accessing data pages in memory (data cache).

1

SET STATISTICS TIME ON

Displays the number of milliseconds required to parse, compile, and execute each statement.

1

sys.partitions

Contains a row for each partition of all the tables and most types of indexes in the database. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

1

sys.destination_data_spaces 

Contains a row for each data space destination of a partition scheme.

1

sys.filegroups

Contains a row for each data space that is a filegroup.

1

sys.dm_db_database_page_allocations

System function can report page allocation information for a database, table, index, and partition. IMPORTANT NOTE: The sys.dm_db_database_page_allocations system function is not documented and is subject to change. Compatibility is not guaranteed.

1

sys.database_files

Contains a row per file of a database as stored in the database itself. This is a per-database view.

1

DBCC TRACEON(3604);

Enables the specified trace flags. Trace flag 3604 is a diagnostic trace flag that causes SQL Server to send trace information to the client instead of writing it to the error log. Configures a trace flag to redirect the output of DBCC commands to the results window.

1

DBCC PAGE('ExampleDB', 3, 8, 1)

The DBCC PAGE command is a diagnostic tool that can be used to examine the physical structure of the data stored in a SQL Server database. DBCC PAGE('ExampleDB', 3, 8, 1), it retrieves information about the data page with a page ID of 8 in the file with a file ID of 3 in the database named ‘ExampleDB’. The last is a print option that can be changed from 0 to 3 to provide more detailed information. Each Slot corresponds to a row

1

EXEC sp_helpindex 'Person.Person';

Lists any indexes on a table, including indexes created by defining unique or primary key constraints defined by a create table or alter table statement (or view).

1
2
3

SELECT INDEXPROPERTY(OBJECT_ID('Person.Person'),  
							'PK_Person_BusinessEntityID',  
							'IsClustered');

Returns the named index or statistics property value of a specified table identification number, index or statistics name, and property name. Returns NULL for XML indexes.

1

DBCC SHOW_STATISTICS ('Person.Person', 'PK_Person_BusinessEntityID');

The command takes two parameters: the name of the table or indexed view for which statistics are to be displayed, and the name of the statistics object to be displayed. If the statistics object name is not specified, the command displays information for all statistics objects associated with the specified table or indexed view. It’s important to note that the statistics information displayed by DBCC SHOW_STATISTICS is based on a sample of the data in the table or indexed view, and may not reflect the entire dataset.

1

sys.foreign_keys

Contains a row per object that is a FOREIGN KEY constraint, with sys.object.type = F.