03 - Transact SQL - Cursors

Hi,

 

Here we'll cover below –

 

-          Transact-SQL, an extension to the SQL database programming language, is a powerful language offering many features.

-          Transact-SQL provides the SQL Server developer with several useful functions, conditional processing methods, advanced transaction control, exception and error handling, scrollable cursors, and much more.

 

Here we'll cover below –

 

-          Transact- SQL Cursors

-          Extended Properties - Incomplete

-          Create a Script that Creates a Script

-          sp_executesql

-          String Concatenation

 

 

 

Transact-SQL Cursors

 

-          SQL Server is a set-processing product, meaning that operations made against the database operate optimally when performed against a set of results, rather than a single row.

-          Transact-SQL cursors allow you to process and work with individual rows from a result set

 

-          Transact-SQL cursors can impact server performance negatively by consuming resources, such as memory, excessively.

-          Cursors should only be used if a set-processing alternative cannot be used in their place.

-          Used wisely, cursors can be quite useful; on the other hand, using cursors indiscriminately can have a severe impact on performance.

 

-          The lifecycle of a Transact-SQL cursor is as follows:

 

 

o         A cursor is defined in a SQL statement that returns a valid result set. This result set is defined as either updateable or read-only.

o         The cursor is then populated, after which the rows can be fetched one row at a time, or as a block.

o         The rows can be fetched forward or backward within the result set (scrolling).

o         Depending on the cursor type, the data can be modified during the scrolling (forward or backward behavior) or read and used with other operations.

o         After the cursor has been used, it should be closed and de-allocated from memory.

 

 

 

Declare a Cursor	DECLARE <cursor_name> CURSOR <LOCAL_or_GLOBAL> <FORWARD_ONLY_or_SCROLL> <STATIC_or_KEYSET_or_DYNAMIC_or_FAST_FORWARD> <READ_ONLY or SCROLL_LOCKS or OPTIMISTIC> <TYPE_WARNING> FOR <select_statement> <FOR UPDATE> <OF column_name_N…>	DECLARE spid_cursor CURSOR FORWARD_ONLY FOR SELECT spid FROM master.dbo.sysprocesses
Opening a Cursor	OPEN <optional_GLOBAL> <cursor_name_or_cursor_variable_name>	OPEN spid_cursor
Fetching a Cursor	FETCH <NEXT_or_PRIOR_or_FIRST_or_LAST_or_ABSOLUTE(n)_or_RELATIVE(n)> FROM <optional_GLOBAL> <cursor_name_or_cursor_variable_name> INTO <@variable_1>, <@variable_N…>	DECLARE @spid smallint FETCH NEXT FROM spid_cursor INTO @spid PRINT 'Spid #: ' + STR(@spid) EXEC ('DBCC INPUTBUFFER (' + @spid + ')')   DECLARE @spid smallint IF @@cursor_rows > 0 BEGIN WHILE @@fetch_status = 0 BEGIN FETCH NEXT FROM spid_cursor INTO @spid PRINT 'Spid #: ' + STR(@spid) EXEC ('DBCC INPUTBUFFER (' + @spid + ')') END END
Closing a Cursor	CLOSE <optional_GLOBAL> <cursor_name_or_cursor_variable_name>	CLOSE spid_cursor

 

 

Extended Properties

 

-          This is about attaching properties to database objects, and extended it to allow multiple user-defined extended properties to be attached to various objects within the database.

-          Extended properties allow you to add meta data about the database and database objects.

-          The extended property is made up of a name/value pair, where the value is a SQL_VARIANT data type, with up to 7500 bytes of data.

-          Extended properties are not allowed on system objects.

 

-          RoleModel -  sp_addextendedproperty stored procedure.

 

sp_addextendedproperty

<property_name>,

<property_value>,

<level_0_object_type>,

<level_0_object_name>,

<level_1_object_type>,

<level_1_object_name>,

<level_2_object_type>,

<level_2_object_name>

 

 

Create a Script that Creates a Script

 

-          Used to create a Transact-SQL script that has to be run against several objects within a database, or databases within a SQL Server instance.

-          One beneficial time-saving technique involves using a SELECT query to write that script for you.

 

 

Script 1

SELECT 'ALTER DATABASE ' + name + ' SET RECOVERY FULL' FROM master.dbo.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')     Output –   ALTER DATABASE BookRepository SET RECOVERY FULL ALTER DATABASE Northwind SET RECOVERY FULL ALTER DATABASE pubs SET RECOVERY FULL

 

 

 

Use sp_executesql

 

-          Unlike stored procedures, regular ad hoc SQL batches and statements usually cause SQL Server to generate a new execution plan each time they are run.

-          The only time an execution plan will be reused for an ad hoc query is if there is an exact match to a previously executed query.

 

-          The sp_executesql stored procedure allows you to create and use a reusable execution plan, where the only items that change are the query parameters.

-          However, The sp_executesql is not a magic bullet for all types of queries, so make sure to test all scenarios.

 

           

sp_executesql N'<@SQL_Statement_or_Batch>', N'<@Parameter_Name_1> <Datatype_1>', N'<@Parameter_Name_N> <Datatype_N>'	EXEC sp_executesql     N'Select * From MyTable Where name = @name',   N'@name varchar(20)',   @name = 'John'

 

String Concatenation

 

-          String concatenation is the process of attaching two or more characters, binaries, or columns into one expression using the plus sign (+).

-          Concatenation with a NULL value will return a NULL value (and not a concatenated value), unless SET CONCAT_NULL_YIELDS_NULL OFF is configured.

 

PRINT 'Fun ' + 'with' + ' concatenation!'

 

 

 

Thanks & Regards,

Arun Manglick || Tech Lead