Tue 26 Apr, 2022
┡ T-SQL Language
┃┡ Date& time
┃┡ Functions
┃┃┡ Metadata
┃┃┃┡ SCOPE_IDENTITY( )
┃┃┃┃╰ Returns the last identity value inserted into an identity column in the same scope.
┃┃┃┃A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two
┃┃┃┃statements are in the same stored procedure, function, or batch, they are in the same scope.
┃┃┃┃SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they
┃┃┃┃return values that are inserted into identity columns.
┃┃┃┃SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
┃┃┃┃GO
┃┃┡ Ranking
┃┃┃┡ ROW_NUMBER( )
┃┃┃┃╰ Numbers the rows of a results set.
┃┃┃┃Temporary sequential row ID calculated when the query is run.
┃┃┃┃To add a row number column in front of each row, add a column with the ROW_NUMBER
┃┃┃┃function, in this case named 'Row#'. You must move the ORDER BY clause up to the OVER
┃┃┃┃clause.
┃┃┃┃SELECT
┃┃┃┃ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
┃┃┃┃name, recovery_model_desc
┃┃┃┃FROM sys.databases
┃┃┃┃WHERE database_id < 5;
┃┃┃┃
┃┡ Language elements
┃┃┡ Control-of-Flow
┃┃┡ Expressions
┃┃┃┡ CASE
┃┃┃┃╰ Evaluates a list of conditions and returns one of multiple possible result expressions.
┃┃┃┃The CASE expression has two formats:
┃┃┃┃The simple CASE expression compares an expression to a set of simple expressions to
┃┃┃┃determine the result.
┃┃┃┃The searched CASE expression evaluates a set of Boolean expressions to determine the
┃┃┃┃result.
┃┃┃┃CASE can be used in any statement or clause that allows a valid expression.
┃┃┃┃For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and
┃┃┃┃SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
┃┃┃┃CASE
┃┃┃┃WHEN when_expression THEN result_expression [ ...n ]
┃┃┃┃[ ELSE else_result_expression ]
┃┃┃┃END
┃┃┃┃
┃┃┃┡ COALESCE
┃┃┃┃╰ Evaluates the arguments in order and returns the current value of the first expression
┃┃┃┃that initially doesn't evaluate to NULL. For example, SELECT COALESCE(NULL, NULL,
┃┃┃┃'third_value', 'fourth_value'); returns the third value because the third value is the first
┃┃┃┃value that isn't null.
┃┃┃┃SELECT Name, Class, Color, ProductNumber,
┃┃┃┃COALESCE ( Class, Color, ProductNumber ) AS FirstNotNull
┃┃┃┃FROM Production.Product;
┃┃┃┃
┃┡ Numeric
┃┡ Statements
┃┃┡ Set
┃┃┃┡ NOCOUNT
┃┃┃┃╰ Stops the message that shows the count of the number of rows affected by a Transact-SQL
┃┃┃┃statement or stored procedure from being returned as part of the result set.
┃┃┃┃When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count
┃┃┃┃is returned.
┃┃┃┃The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
┃┃┃┃SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each
┃┃┃┃statement in a stored procedure. For stored procedures that contain several statements that
┃┃┃┃do not return much actual data, or for procedures that contain Transact-SQL loops, setting
┃┃┃┃SET NOCOUNT to ON can provide a significant performance boost, because network traffic
┃┃┃┃is greatly reduced.
┃┃┃┃USE AdventureWorks2012;
┃┃┃┃GO
┃┃┃┃SET NOCOUNT OFF;
┃┃┃┃GO
blog.jcaston.uk // 2025