When, Why and how to use XACT_ABORT

SET XACT_ABORT allows SQL Server to automatically rolls back the complete transaction when a T-SQL statement raises a runtime error. 

SET XACT_ABORT is used in SQL Server to control the behavior of a batch of Transact-SQL statements. When set to ON, it tells SQL Server to roll back the entire transaction if any statement in the batch raises an error, rather than just rolling back the individual statement that raised the error. This can be useful if you want to ensure that no partial changes are made to the database if an error occurs.  

When to use SET XACT_ABORT: SET XACT_ABORT should be used when there is a risk of partial changes being made to the database if an error occurs. This could be the case in complex transactions with multiple statements that need to either succeed or fail as a group.  

Why to use SET XACT_ABORT: SET XACT_ABORT is used to ensure that all statements in a transaction succeed or fail as a group. This helps to ensure that your database is consistent and not left in an inconsistent state if an error occurs.  

How to use SET XACT_ABORT: SET XACT_ABORT is used to control the behavior of a batch of Transact-SQL statements. 

 To use SET XACT_ABORT, you must use it at the beginning of the batch, before any other statements: 

 SET XACT_ABORT ON   

BEGIN TRANSACTION  

 -- Transact-SQL statements go here   

COMMIT TRANSACTION 

Example: 

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: XACT_ABORT example  

SET XACT_ABORT ON;  

BEGIN TRANSACTION;  

INSERT INTO [dbo].[Test] (Name, Value) VALUES ('Test1', '1');  

INSERT INTO [dbo].[Test] (Name, Value) VALUES ('Test2', '2'); 

INSERT INTO [dbo].[Test] (Name, Value) VALUES ('Test3', '3');  

COMMIT TRANSACTION;

 

Different versions of Microsoft SQL Server; release, code name and release date.

 Version history of Microsoft SQL Server

Server Name	Release Name	Code Name	Release Date
Microsoft SQL Server 2019 (RTM)	SQL Server 2019 (15.x)	Big Data Clusters	November 4, 2019
Microsoft SQL Server 2017 (RTM)	SQL Server 2017 (14.x)	Machine Learning Services	October 2, 2017
Microsoft SQL Server 2016 (RTM)	SQL Server 2016 (13.x)	Stretch Database Release	June 1, 2016
Microsoft SQL Server 2014 (RTM)	SQL Server 2014 (12.x)	Hekaton	April 1, 2014
Microsoft SQL Server 2012 (RTM)	SQL Server 2012 (11.x)	Denali	March 6, 2012
Microsoft SQL Server 2008 R2 (RTM)	SQL Server 2008 R2 (10.5)	Kilimanjaro	April 21, 2010
SQL Server 2008	SQL Server 2008(10.0)	Katmai	August 6, 2008
Microsoft SQL Server 2005	SQL Server 2005(9.x)	Yukon	7-Nov-05
Microsoft SQL Server 2000	Microsoft SQL Server 2000(8.00.194)	Shiloh	7-Aug-00
Microsoft SQL Server 7.0	SQL Server 7.0(7.00.623)	Sphinx	1/24/1998
Microsoft SQL Server 6.5	SQL Server 6.5(6.50.201)	Hydra	15-Dec-95
Microsoft SQL Server 6.0	SQL Server 6.0(6.00.048)	SQL95	12/15/1996
Microsoft SQL Server 4.2	SQL Server 4.21a	NT-SQL	7/7/1995
Microsoft SQL Server 4.0	SQL Server 4.2B (16-bit)	Asterix	22-May-93
Microsoft SQL Server 1.1	SQL Server 1.1 (16-bit)	Pietro	7-May-89
Microsoft SQL Server 1.0	SQL Server 1.1 (16-bit)	Filipi	Mar-89

Change Column DataType

Datatype of column may require modification with the new change to the application. Column used in multiple tables could be dynamically changed in one execution using Alter Table command.

ALTER TABLE schemaName.tableName
ALTER COLUMN columnName DataType [NULL|NOT NULL]

The following example increases the size of a varchar\nvarchar column.

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com  
Use DBName
GO
SELECT 'ALTER TABLE [' +sc.name+'].['+object_name(col.object_id)

         +'] ALTER COLUMN ['+ col.name+'] '

+ CASE DATA_TYPE WHEN 'varchar' THEN DATA_TYPE +'(255) NULL ' 

                                  WHEN 'nvarchar' THEN DATA_TYPE +'(175) NULL '

   ELSE ''  END

,col.*

FROM sys.columns col

INNER JOIN sys.tables tb ON col.[object_id]=tb.[object_id]

INNER JOIN sys.schemas sc ON sc.[schema_id]=tb.[schema_id]

INNER JOIN INFORMATION_SCHEMA.COLUMNS incol ON incol.TABLE_NAME=tb.name

                                            AND incol.TABLE_SCHEMA=sc.name

                                            AND incol.COLUMN_NAME=col.name



WHERE DATA_TYPE in ('varchar','nvarchar') AND col.name ='CaseDetail'

AND CHARACTER_MAXIMUM_LENGTH<255

Note: 

1. Reducing the precision or scale of a column may cause data truncation. 
2. Column contain data, its new size should be equal to or greater than the old size. 
3. Read modified column cannot be any of on Link

Predicate effect in ON and WHERE clause

There is always a question, Is using predicate in ON clause gives the same result using predicate in Where clause of a query?
Actually it depends on the order in which the conditions of a query are evaluated logically in SQL Server; but if the query is an inner join, then there is no difference between using predicate as part of ON clause or Where. They might generate different result when query is with OUTER joins because the predicates in ON clause applied to the table before the join, whereas WHERE clause is applied to the result of the join.

SQL Server Logical Query Execution Order:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

ON clause predicate with Outer Join:

In case of OUTER JOIN query will follow sequence

SELECT  *
FROM    dbo.Employee emp
        LEFT JOIN dbo.EmployeeStatus es ON emp.Status_ID = es.Status_ID
                                           AND emp.Status_ID <> 2

1.       Table Employee scanning ( Table at left side of the Outer clause )
2.       Table EmployeeStatus scanning with predicate Status_ID<>2

Result Set:

WHERE clause predicate with Outer Join:

In Case of WHERE clause query will follow the sequence

SELECT  *
FROM    dbo.Employee emp
        LEFT JOIN dbo.EmployeeStatus es ON emp.Status_ID = es.Status_ID
WHERE   emp.Status_ID <> 2

1.       Table Employee scanning
2.       Table EmployeeStatus scanning
3.       Result set filtering based on predicate Status_ID<>2

Result Set :

Reference Link:
http://msdn.microsoft.com/en-us/library/ms177634.aspx

SQL Server Backup Types and Restore

Thin Black Lines ( Aasim Abdullah & Amna Aasif )

Every SQL Server database consists of a Master Data File (an OS file with the “.MDF” extension) to store user data. Log Files, or OS files with the “.LDF” extension, are also a necessary part of every database.  Log files record changes to data from requests by SQL queries.  Extra data files, or OS files with the “.NDF” extension can be added to a database.

Row data files are logically grouped into one or many File-Groups

Inside every row data file, user data is kept within boundary of 8K data pages which are further grouped as Extents.

It is hard to imagine not backing up the production databases periodically. This facilitates recovery from accidental data deletion or complete database corruption.  IN addition to recovery, database backups are also necessary when we need to move a database between different instances and need to archive unused data.

SQL Server allows four different types of backups, Full, Differential, Transaction Log, and File/File group backups.

Full Backup

A full database backup copies the entire database from all MDF, LDF, & NDF OS files into one OS file, typically given a “.BAK” file extension.

When a Full Database backup operation starts, the operation will first lock the database; effectively make the row data file/s read-only, without making the database inaccessible for ongoing & concurrent user transactions. It does so by placing a marker in the transaction log indicating what transactions pre-existed and will be copied to the BAK file.  After the transaction log marker has been set the lock is released and the backup will start copying database pages into the BAK OS file.

The backup operation copies only the pages that contain data while unused empty pages are skipped.

There is the possibility that users will modify data in the database while the backup is in progress. Once the data page copying process is complete, the backup operation will again lock the database; place the end marker in transaction log, release the lock and start copying appended changes from the transaction log between two markers.

This “log tail end” backup means every change which is from the initial marker to the second marker should be part of full backup.  In nearly all scenarios the BAK file is up-to-date to the moment the backup operation is complete. 

Full Database Backups are more disk I/O intensive which is why full backups take longer to complete. In between two full backups, data is secured by backing up only the changes since the most recent fill backup. That is possible through Differential or Transaction Backups.

Differential Backup

The first 9 pages of every data file are in the same sequence.  One of the 9 is the DCM (Differential Change Map).  DCM is a special header page which contain information about extents being modified after the last full backup operation.

Differential backups starts with scanning of DCM page/s. Each bit in a DCM page represents a single extent in the database file. Extents with bit value 0 mean none of its data page is changed since last full backup and are skipped during copy process.

Once extent copying process is done, just like in a full backup process, the differential backup process also backs up the transactional log from most recent full backup marker to bring the database to a stable state at the time the differential operation is complete.

Differential backup only contains data that has changed since the most recent Full backup.  So the operation takes up less space and I/O time compared to a full backup.

Therefore, for a large database we can add a full database backup with subsequent differential database backups to our backup plan.

Transaction Log Backup

The Transactional log contains serial records for every change made to a database data rows. Once query data changes are committed or roll backed, the data is considered part of inactive transactions in log file.

When a transaction log backup process starts, the process starts copying inactive transactions records created since the most recent backup operation of the transaction log e.g. the most recent Full, Differential, or Transaction log backup.

All transaction data considered inactive data in the transaction log are copied to the transaction log backup OS file.  The transaction log backup operation finishes by marking inactive data space as available and ready for reuse.

As Transaction log file is sequential so creating a transaction log backup is far more less time and I/O intensive as compared to full or differential backups.

Critical production databases are backed up with combination of full/differential and transactional backups.

File\File Group Backup

Larger database that consist of multiple files and file groups can be partially backed up, by creating File or File Group backups. It is recommended to also take transaction log or differential file\file-group backups at relatively close in time to File/File Group backups so recovery of a database will be founded on a consistent state backup files.

Configuration Manager

·         Configuration manager is GUI to manage/configure all

§  Sql Server Services,

§  Server Network Protocols,

§  Client Network Configurations

§  And also to change the Connectivity options.

·         Using SQL Server Configuration Manager you can set Service's (such as SQL Server Integration Services, SQL Server Full Text Search Service, SQL Server Analysis Services, SQL Server Reporting Services, SQL Server Database Engine Services, SQL Server Agent Service, SQL Server Browser Services etc)

                              §  State à   Start OR Stop

§  Start Mode à   Manual OR Automatic

·         Using SQL Server Configuration Manger, allows  you to Create or Remove Server Alias.

·         Using SQL Server Configuration Manger you can also force Protocol Encryption and Protocol’s

§  State à Enabled OR Disabled

(Shared Memory, Named Pipes, TCP\IP and VIA)

·         Using SQL Server Configuration Manager Tool you can change SQL Server Service Account or change the Password for any of the SQL Server Services.

·         Using SQL Server Configuration Manager, changes in Account settings also configure associated additional changes such as Windows Registory settings and account can read the SQL Server settings immediately once changed where as other Tools (SMO and WMI) can not change associated settings which may effect the proper start of service.

·         Using SQL Server Configuration Manager Tool  changes come into effect immediately by avoiding downtime

  

Reference Links

SQL Server Configuration Manger: http://technet.microsoft.com/en-us/library/ms174212.aspx

Protocol Encryption: http://technet.microsoft.com/en-us/library/ms191192.aspx

                               http://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

Account Permissions: http://msdn.microsoft.com/en-us/library/ms143504.aspx

What is SQL Server?

SQL Server is a relational database management system developed by Microsoft. That can respond to queries from other applications OR client machines.