Transaction Isolation Level in SQL Server

Transaction Isolation Level: -

Basically, a Transaction is a logical unit of work or t-sql statement, in which either all should be executed successfully or none of them.

With the help of setting the isolation level of a transaction, we can define, up to which extent a transaction can be inaccessible (or isolated) from the other transaction. Or we can say, Transaction Isolation Level controls the locking and row versioning behavior of a transaction.

There are 5 level of Transaction Isolation Level: -

1)    Read Uncommitted

2)    Read Committed

3)    Repeatable Read

4)    Snapshot

5)    Serializable

Read Uncommitted: -

·         Doesn’t maintain any isolation.

·         Result dirty reads, phantom reads and non-repeatable reads.

Read Committed: -

·         Allow transaction to read only committed data.

·         It protects dirty reads.

·         Not prevent against phantom reads and non-repeatable reads.

Repeatable Read: -

·         Cannot read uncommitted data of other transaction.

·         No other transactions can modify data that has been read by the current transaction until the current transaction completes.

·         Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads.

·         Protect against both dirty reads and non-repeatable reads.

·         Allows phantom reads.

Snapshot: -

·         It prevents dirty reads, phantom reads and non-repeatable reads.

·         It provides each transaction with a "snapshot" of the data it requests. The transaction may then access that snapshot for all future references, eliminating the need to return to the source table for potentially dirty data.

·         The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Serializable: -

·         It prevents dirty reads, phantom reads and non-repeatable reads.

·         Statements cannot read data that has been modified but not yet committed by other transactions.

·         No other transactions can modify data that has been read by the current transaction until the current transaction completes.

·         Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

If you need to change the isolation model in use by SQL Server, simply issue the command:

SET TRANSACTION ISOLATION LEVEL <level>

Dirty Reads/Non-repeatable Reads/Phantom Reads in SQL Server

Dirty Reads: - Occur when one transaction reads uncommitted data.

Non-repeatable Reads: - Occur when one transaction attempts to access the same data twice and a second transaction modifies the data between the first transaction's read attempts. This may cause the first transaction to read two different values for the same data, causing the original read to be non-repeatable.

Phantom Reads: - Occur when one transaction accesses a range of data more than once and a second transaction inserts or deletes rows that fall within that range between the first transaction's read attempts. This can cause "phantom" rows to appear or disappear from the first transaction's perspective.

COALESCE in SQL Server

COALESCE:-Returns the first nonnull expression among its arguments.

Syntax: -COALESCE ( expression [ ,...n ] ) 

Example: -SELECT COALESCE(NULL, NULL, NULL, GETDATE()) 

Extended Properties in SQL Server

Extended Properties: -

Basically, extended properties are used for general maintenance of instance of SQL Server. With the help of extended properties, we can create built-in database documentation for our database objects. Extended Properties allow us to add text as properties of objects in a database or of the database itself. Text can include descriptive or instructional content, purpose, input options, formatting rules etc. for a database object.

Extended properties enforce the consistency because extended properties are stored in the database, all applications reading the properties can evaluate the object in the same way.

Extended properties,

-         Has a user defined name and value.

-         Value of an extended property, can contain up to 7500 bytes of data.

-         Multiple extended properties can be added to a single object.

-         Extended properties can be defined for objects at any one of database level, schema level or object level. Here Database level treated as Level 0, Schema level treated as Level 1 and Object level treated as Level 2.

Exceptions for Extended Property: - Extended property cannot be defined on below objects:

-          Full text objects.

-          Object outside database scope such as HTTP end point.

-          Unnamed objects such as partition function parameters.

-          System defined objects such as system tables, catalog views and system stored procedures.

Below are the system procedures to interact with extended properties: -

1)    To add an extended property - sp_addextendedproperty (Transact-SQL)

2)    To update an extended property - sp_updateextendedproperty (Transact-SQL)

3)    To delete and extended property - sp_dropextendedproperty (Transact-SQL)

4)    To view an extended property –

fn_listextendedproperty (Transact-SQL) &

sys.extended_properties (Transact-SQL)

Example of Execute SQL task

We have gone through regarding Execute SQL task in my previous post. Here is the example of Execute SQL Task in SSIS with various type of result set having SQLSourceType = Direct input: -

First create the table

Now configure the connection manager.

Now Edit the Execute SQL Task

1)    When ResultSet = None & SQLSourceType = Direct input

Mention the SQL statement in SQLStatement.

Now click on OK.

Execute SQL task will appear without cross sign as below:-

Now right click à Execute Task àafter execution Execute SQL task will look below :-

Validate the data in table

2)     When ResultSet = Single Row & SQLSourceType = Direct input

Declare a variable to hold the result having the same data type.

Here we are getting the count of rows in table so get the variable of data type INT32.

Modify the SQL Statement as below :-

Now modify the mapping of Result Set tab to hold the result as below :-

Now execute “Execute SQL Task”, RIGHT CLICK à Execute Task

3)     When ResultSet = Full result set & SQLSourceType = Direct input

Declare a variable of data type Object as we are going to hold full result set.

In Result set tab, the result binding name (for Result Name) must be set to zero for full result set and XML results, as below: -

Then Right Click à Execute Task

Note:-

1)    When Result set type = Single row then data type of variable can be any type that is compatible with the type column in the result set.

2)    When Result set type = Full result set then data type of variable will be ‘object’.

3)    When Result set type = XML then data type can be either String or Object.

Execute SQL Task in SSIS

Execution Task:-

Execute SQL Task; help us to execute SQL statement from SSIS package. That SQL statement can be a stored procedure or can have single SQL statement as well as multiple SQL statements.

Configuration properties of Execute SQL Task are divided into 4 categories:-

1)    General

2)    Parameter Mapping

3)    Result Set

4)    Expression

To get the configuration properties, right click on execute SQL task and select EDIT.

1)    “GENERAL” tab have below options :-

·         Name: - here we can provide or edit the name of Execute SQL Task. Better to keep the name of Execute SQL Task, generic to functionality.

·         Description: - General details regarding functionality and what going to happen.

·         TimeOut: - Specify the maximum number of seconds the task will run before timing out. A value of 0 indicates an infinite time. The default is 0.

·         CodePage: - A code page, also known as a character set, is a set of 256 uppercase and lowercase letters, numbers, and symbols. The printable characters of the first 128 values are the same for all character set choices. The last 128 characters, sometimes referred to as extended characters, differ from set to set. The code page for SQL Server determines both the types of characters that SQL Server recognizes in your database, and the results that SQL Server returns for queries that involve character comparisons. Here we specify the code page to use when translating Unicode values in variables.

·         ResultSet: - Here we have to specify the result set type, expected by the SQL statement being run. We have four option to chose the type of result set: -

1)    None

2)    Single row

3)    Full result set

4)    XML

·         Connection Type: - Choose the type of connection manager to use to connect to the data source. Here, we have below options: -

1)    OLE DB

2)    EXCEL

3)    ODBC

4)    ADO

5)    ADO.NET

6)    SQLMOBILE

·         Connection: - Here, we have to specify the connection from the defined connection managers.

·         SQLSourceType: - Here we have to specify the source type of the SQL statement that the task runs. Below are the options: -

1)    Direct Input: - In this case, we have to specify the SQL Statement in SQLStatement box.

2)    File Connection: - In this case, we have to specify the file connection in FileConnection box.

3)    Variable: - In this case, we have to specify the source variable (which feed the query) to SourceVariable.

·         IsQueryStoredProcedure: - It confirms, the specified SQL statement is stored procedure or not. This property is editable only if the specify connection is ADO, otherwise the property is read only and default value will be, “False”.

·         BypassPrepare: - This option is available only for OLE DB connection and help us to specify that SQL statement prepared or not. “true” skips preparation, “false” prepares the SQL statement before running it.

Browse: - It helps us to locate a file that contains a SQL statement and copy the contents of the file as a SQL statement into the SQLStatement property

Build Query: - Here, graphical tool help us to create a SQL statement. This option is available only when the SQLSourceType option is set to Direct input.

Parse Query: - Help us to validate the syntax of SQL statement.

2)    “Parameter Mapping” tab have below options: - This dialog box help us to map variables to parameters in SQL statement. Here we have below option:-

·         Variable Name: - Here we have to mention the user or system defined variable.

·         Direction: - To mention the direction of above defined variable as Input/Output/ReturnValue.

·         Data Type: - Define the data type of parameter, to which the value of variable is going to pass.

·         Parameter Name: - Parameter name or position number (depend on connection type) to which the value of variable going to pass.

·         Parameter Size: S size of parameters that have variable length, such as strings and binary fields.

Add: - To add a parameter mapping

Remove: - To remove the parameter mapping from the list.

3)    “Result Set” tab have below options :- It help us to map the result of the SQL statement. Result Set option will be disabled if ResultSet on the General page is set to None. Here we have below option: -

·         Result Name: - If result set type is SINGLE ROW, then we have to specify either the name of a column returned by the query or the number that represents the position of column in the column list of columns returned by the query.

If the result set type is FULL RESULT SET or XML, then we use 0 as the result set name.

·         Variable Name: -Map the result set to a variable by selecting a variable.

ADD: - add a result set mapping.

Remove: - To remove the result set mapping.

To get better understanding of Execute SQL Task with example, you have to go here.

Schema in SQL Server

Schema introduced in SQL SERVER 2005, it is basically a container not more than that OR we can say, Schema is used to group objects (such as table, views and stored procedures) logically.

Schema is a collection of objects adhering to the ANSI SQL-92 standard.

All objects within a schema must be uniquely named and a schema must be uniquely named in the database catalog. There is difference between “user” and “schema”:-

·         User do not own objects where as Schema owns objects.

·         Schemas can be created and altered in a database, and users can be granted access to a schema.

·         A schema can be owned by any user, and schema ownership is transferable.

With the help of “CREATE SCHEMA” and “ALTER SCHEMA”, we can create and alter the schema. The default schema for a user can be defined by using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER commands. If no default schema is defined then SQL SERVER takes, ‘dbo’ as a default schema.   schema_id and principle_id of dbo is 1.

With the help of “DROP” command, we can Removes a schema from the database. Point should be noted, the schema that is being dropped must not contain any objects. If the schema contains objects, the DROP statement fails.

We can get the details of schema in sys.schemas object.

Basically there are two type of schema :- 1) built-in schema.   2) User defined schema.

One side note:- At the time of installation of SQL SERVER, we got few schemas by default. One of the schemas is named sys. (The sys schema contains a list of some of the objects that exist in your database system) and another is called dbo.

LEAD function in SQL SERVER 2012

As per Books online, Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.

SQL SERVER 2012 introduced LEAD function as an ANALYTICAL FUNCTION, with the help of that we can get simply the next Nth row value in an order. The order of the rows can be grouped by using the Partition By clause for powering the SQL Lead () function. 

In other words, we can say, this function will return columns values from subsequent rows in the same result set without the use of a self-join. With the help of this function, we can compare values in the current row with values in a following row.

SYNTAX:-

                                LEAD (scalar_expression [,offset] [,default])

                                 OVER ([partition_by_clause] order_by_clause)

NOTE: - The partition by clause is optional, the order by clause is required.

DESCRIPTION OF LEAD () CLAUSES:-

·         SCALAR_EXPRESSION:- is the value to be returned – this will normally be a column, but it can also be a subquery or any other expression that results in a single value.

·         OFFSET: - is the number of following (LEAD) rows from the current row from which to obtain a value, and if not specified it will default to 1.

·         DEFALUT: - is the value to use if the value at the offset is NULL, and if not specified it will default to NULL.

EXAMPLE WITHOUT “PARTITION BY” CLAUSE:-

DECLARE @tbl TABLE (id INT IDENTITY

, name VARCHAR (10)

                                                )

INSERT INTO @tbl (name) VALUES ('A')

INSERT INTO @tbl (name) VALUES ('B')

INSERT INTO @tbl (name) VALUES ('C')

SELECT * FROM @tbl

Result will be:-

id	name
1	A
2	B
3	C

·         HAVING ONLY SCALAR EXPRESSION AND ORDER BY CLAUSE

SELECT *

, LEAD( name ) OVER (ORDER BY id)

FROM @tbl

Result will be :-

id	name	LEAD EXAMPLE
1	A	B
2	B	C
3	C	NULL

·         HAVING OFFSET VALUE (WITHOUT DEFAULT) WITH SCALAR EXPRESSION AND ORDER BY CLAUSE

SELECT *

, LEAD ( name, 2 ) OVER (ORDER BY id)

FROM @tbl

Result will be :-

id	name	LEAD EXAMPLE
1	A	C
2	B	NULL
3	C	NULL

·         HAVING OFFSET VALUE HAVING DEFAULT WITH SCALAR EXPRESSION AND ORDER BY CLAUSE

SELECT *

, LEAD( name, 2, '[LEAD]' ) OVER (ORDER BY id)

FROM @tbl

Result will be:-

id	name	(No column name)
1	A	C
2	B	[LEAD]
3	C	[LEAD]

EXAMPLE WITHOUT “PARTITION BY” CLAUSE:-

DECLARE @tbl TABLE (id INT

, name VARCHAR (10)

                                                )

INSERT INTO @tbl VALUES (1, 'A')

INSERT INTO @tbl VALUES (1, 'B')

INSERT INTO @tbl VALUES (1, 'C')

INSERT INTO @tbl VALUES (2, 'P')

INSERT INTO @tbl VALUES (2, 'P')

INSERT INTO @tbl VALUES (2, 'Q')

INSERT INTO @tbl VALUES (2, 'R')

SELECT * FROM @tbl

Result will be:-

id	name
1	A
1	B
1	C
2	P
2	P
2	Q
2	R

·         HAVING ONLY SCALAR EXPRESSION AND ORDER BY CLAUSE

SELECT *

                , LEAD (name) OVER (PARTITION BY id ORDER BY id)

FROM @tbl

Result will be:-

id	name	LEAD EXAMPLE
1	A	B
1	B	C
1	C	NULL
2	P	P
2	P	Q
2	Q	R
2	R	NULL

·         HAVING OFFSET VALUE (WITHOUT DEFAULT) WITH SCALAR EXPRESSION AND ORDER BY CLAUSE

SELECT *

                , LEAD (name, 2) OVER (PARTITION BY id ORDER BY id)

FROM @tbl

Result will be:-

id	name	LEAD EXAMPLE
1	A	C
1	B	NULL
1	C	NULL
2	P	Q
2	P	R
2	Q	NULL
2	R	NULL

·         HAVING OFFSET VALUE HAVING DEFAULT WITH SCALAR EXPRESSION AND ORDER BY CLAUSE

SELECT *

                , LEAD (name, 2, '[LEAD]’) OVER (PARTITION BY id ORDER BY id)

FROM @tbl

                Result will be:-

id	name	LEAD EXAMPLE
1	A	C
1	B	[LEAD]
1	C	[LEAD]
2	P	Q
2	P	R
2	Q	[LEAD]
2	R	[LEAD]