@@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
SELECT @@TRANCOUNT
Plan guide for associating query
sp_create_plan_guide creates a plan guide for associating query hints or actual query plans with queries in a database.
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.Customer AS c
ON h.CustomerID = c.CustomerID
INNER JOIN Sales.SalesTerritory AS t
ON c.TerritoryID = t.TerritoryID
WHERE t.CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT TOP 1 *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)';
sp_control_plan_guide drops, enables, or disables a plan guide.
EXEC sp_control_plan_guide N'DROP', N'Guide1'
Does my database contain edition-specific features?
sys.dm_db_persisted_sku_features is used to list all edition-specific features that are enabled in the current database.
SELECT * FROM sys.dm_db_persisted_sku_features
GO
sp_MsForEachDB
sp_MsForEachDB perform an action on all of the databases.
exec sp_MsForEachDB @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand
- @command1 - the command to be executed by sp_MsForEachDB.
- @replacechar - a character in the command string that will be replaced with the database name being processed (default '?').
- @command2 and @command3 are two additional optional commands that can be run for each database (@command2 will be run after @command1, @command3 will be run after @command2).
- @whereand - an additional constraints to help identify the rows in the sysobjects database that will be selected.
- @precommand - a command to be run prior to processing any database.
- @postcommand - a command to be run after all commands have been processed against all databases.
Examples:
declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 = 'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
set @cmd2 = 'if ''?'' <> ''tempdb'' backup database ? to disk=''c:\temp\?.bak'''
set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'
exec sp_MSforeachdb @command1=@cmd1,
@command2=@cmd2,
@command3=@cmd3
GO
EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'
sp_MSforeachtable
sp_MSforeachtable perform an action on all of the tables within a database.
exec sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand
- @command1 - the command to be executed by sp_MSforeachtable.
- @replacechar - a character in the command string that will be replaced with the table name being processed (default '?').
- @command2 and @command3 are two additional optional commands that can be run for each table (@command2 will be run after @command1, @command3 will be run after @command2).
- @whereand - an additional constraints to help identify the rows in the sysobjects table that will be selected.
- @precommand - a command to be run prior to processing any table
- @postcommand - a command to be run after all commands have been processed against all tables
Examples:
declare @cmd1 varchar(500)
set @cmd1 = 'exec sp_Help "?"'
exec sp_MSforeachtable @command1=@cmd1
GO
EXEC sp_MSforeachtable @command1="print '*'; DBCC DBREINDEX ('*', ' ', 80)" ,@replacechar='*'
GO
declare @cmd1 varchar(500)
set @cmd1 = 'exec sp_Help "?"'
exec sp_MSforeachtable @command1=@cmd1
GO
EXEC sp_MSforeachtable @command1="print '*'; DBCC DBREINDEX ('*', ' ', 80)" ,@replacechar='*'
GO
RANK OVER, DENSE_RANK OVER
--Returns the rank of a result set:
SELECT StamTableId, StamTableStr, colIntA,
RANK() OVER (ORDER BY colIntA) as Rank_over
FROM StamTable
--Returns the rank of rows of a result set, without any gaps in the ranking:
SELECT StamTableId, StamTableStr, colIntA,
DENSE_RANK() OVER (ORDER BY colIntA) as Rank_over
FROM StamTable
--Returns the rank of rows within the partition of a result set, without any gaps in the ranking:
SELECT StamTableId, StamTableStr, colIntA,
RANK() OVER (PARTITION BY StamTableStr ORDER BY StamTableStr, colIntA) as Rank_over
FROM StamTable
RANK():
DENSE_RANK():
Get the output of INSERT, DELETE, and UPDATE statements
DELETE
FROM StamTable2
OUTPUT deleted.*
WHERE StamTableId > 11
INSERT INTO StamTable2 (StamTableStr, StamTableStr2)
OUTPUT inserted.*
SELECT StamTableStr, StamTableStr FROM StamTable WHERE StamTableId > 11
UPDATE StamTable2
SET StamTableStr2 = StamTableStr2 + '_UPDATED'
OUTPUT deleted.*, inserted.*
WHERE StamTableId > 11
Note: This OUTPUT doesn't work when there are 'instead of' triggers.
Subscribe to:
Posts (Atom)