XACT_ABORT statement to rolls back the implicit or explicit transactions.

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

SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. 

XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The setting of SET XACT_ABORT is set at execute or run time and not at parse time.

Remember: Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

The following code example causes a foreign key violation error in a transaction that has other Transact-SQL statements. In the first set of statements, the error is generated, but the other statements execute successfully and the transaction is successfully committed. In the second set of statements, SET XACT_ABORT is set to ON. This causes the statement error to terminate the batch and the transaction is rolled back.

IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
GO
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
-- SELECT shows only keys 1 and 3 added.
-- Key 2 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.
-- Key 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
SELECT *
    FROM t2;
GO

ALTER table script to Create new column within existing columns..

One of my client asked me to send me an alter table script that add a new column in table. I sent him alter table statement as follows

ALTER TABLE CacheTree ADD IsXML bit default(0);

He said I know this but my problem is to add news column on a specific position like 3rd column in table necessary for maintaining Standards but he did not have SSMS access? He added there is a way when you make a change in SSMS you can generate ALTER TABLE script through SSMS without saving that column and then you can use that statement to create your own column and table. He needs that script from me.

I have idea in mind that we might achieve this by using 

• Drop keys from the existing table
• Create new table with required column and insert data into newly created temp table.
• drop the existing table
• Rename the new table and create keys.

But i don't know how SSMS generate script for this. Now I have found the way and wants to share it with all who wants to know.

Enable Alter Changes through SSMS

You can enable check box using SSMS that enables option to generate Alter table script. Through 2008 R2 SSMS I have done following changes.

Here is the check box "Warn about table effected".

After that try to add a new column in CacheTree table on 3rd place.

From the screen shot you can see I have added "IsXML" column and then before saving that change I have gone to 

"Form Designer -- > Generate Change Script"

and I got following script from SSMS.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_CacheTree
(
SearchId int NOT NULL,
UserCriteria xml NULL,
IsXML bit NULL,
CampaignSearch xml NULL,
CampaignType int NULL
)  ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_CacheTree SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.CacheTree)
EXEC('INSERT INTO dbo.Tmp_CacheTree (SearchId, UserCriteria, CampaignSearch, CampaignType)
SELECT SearchId, UserCriteria, CampaignSearch, CampaignType FROM dbo.CacheTree WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.CacheTree
GO
EXECUTE sp_rename N'dbo.Tmp_CacheTree', N'CacheTree', 'OBJECT' 
GO
ALTER TABLE dbo.CacheTree ADD CONSTRAINT
PK_SearchUserCampaigns PRIMARY KEY CLUSTERED 
(
SearchId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


GO
COMMIT

Now look at the script It did same what I was thinking but I got regenerated script from SSMS.

Selecting XML Structure from relational DB

We have started our discussion to select XML from beging to implementation XML in Sql server. http://shamas-saeed.blogspot.com/2012/05/schema-xml-memoir-definition-to.html

In previous version we have seen what is the major difference between typed vs untyped XML. http://shamas-saeed.blogspot.com/2012/06/typed-xml-vs-untyped-xml.html

Now we have understanding of XML and its schema. Our next step is to see how can we prepare XML structure from tables. As we have seen that xml used to implement for complex structures like student information history, patient history in hospital management, logical comparison of properties in Property based database and CV detail of employees on any CV site. To understand our flow I have selected a structure shown in following diagram

From the attached diagram we can define above structure as Advertiser create campaigns with some specific criteria like CTR,GEN etc. User also set its search criteria that includes parameter values against campaign types. This data used once searched for future search optimization. final result stores in Campaign search. 

Now I want to change its structure as there is an expensive data in user criteria table against each user and each criteria type. Then against each criteria type there are multiple campaign search results. I have changed its structure as follows

I have created a new table as CacheTree and create CampaignSearch XML column to store search results and UserCriteria table to store User search criteria that campare results with campaigns table for optimization.

To implement this My XML will be like this

========================= User Criteria XML ==================================

<User CampaignType="1">
  <Level Id="1" Enabled="true">
    <Criteria Type="CTR" TypeId="1" FunctionId="1">
      <Value>%(59)%</Value>
    </Criteria>
  </Level>
  <Level Id="2" Enabled="true" />  
</User>
=========================  User Criteria XML  ===================================


and
========================= Campaign Search XML ==================================

<Campaigns>
  <C CId="754" AId="9" />
  <C CId="757" AId="9" />
</Campaigns>
=========================  Campaign Search XML  ===================================

In above structure CID is CampaignID and AId is AdvertiserID. there is no need to store UserID as when any user apply any search format we compare that format with User criteria XML and if any match found we will return its Campaign Search XML instead of applying search queries on Campaign table.

Next: I have created XML structure but now i need to create its schema and I don't know how to create that. In our next post we will see how easily we can generate schema of any structure we have selected.

Windows Azure Lunch event (complete cloud environment)

After successfully lunch SQL Azure services now Microsoft launch Windows Azure. A complete and comprehensive platform for developing and deploying applications. It is now integrated for Windows, Mac, Linux OS developers as well. Supporting more then one Db like SQL SERVER, MySQL, Mongo DB etc.

For detail look at the Scott Guthrie (CVP Windows Azure, Application plateform) presentation at Microsoft platform.

http://www.meetwindowsazure.com/Conversations

Windows Azure provides a Microsoft Windows Server-based computing environment for applications and persistent storage for both structured and unstructured data, as well as asynchronous messaging. Windows Azure also provides a range of services that helps you connect users and on-premises applications to cloud-hosted applications, manage authentication, use inter-service messaging, and implement data management and related features such as caching.

SQL Azure is essentially SQL Server provided as a service in the cloud.

Major features are:

Compute Environment

• Windows Azure Compute (Web and Worker Roles).
• Virtual Machine (VM role). 

Data Management

• Windows Azure Storage.
• SQL Azure Database.
• Data Synchronization.
• Caching. 

Networking Services

• Content Delivery Network (CDN). 
• Virtual Network Connect.
• Virtual Network Traffic Manager. 
• Access Control (ACS).
• Service Bus.

Other Services

• Business Intelligence Reporting. 
• Marketplace. 

Index fragmentation, rebuild and reorganize through TSQL

We need to look into index fragmentation of our database indexes on regular intervals. Once find fragmented then we need to rebuild and reorganize at some point.

to do all three steps. we need to search on many places to find how to find fragmentation and rebuild or reorganize query. I have gathered all 3 steps here

Step 1: Find fragmentation percent of your running indexes.

========================= Code segment  starts ==================================

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() and avg_fragmentation_in_percent > 0
ORDER BY ps.OBJECT_ID
========================= Code segment ends ===================================

screen shot shows how much an index has been fragmented.

Step2: If indexes are over 10 percent to 40 percent you need to reorganize these indexes. Keep in mind it will effect performance during re-Organization.

========================= Code segment  starts ==================================


ALTER INDEX ALL ON Blog.dbo.Hashfunction REORGANIZE
GO
========================= Code segment ends ===================================

Step3: If indexes are over 40 percent fragmented then you need to rebuild these indexes. Keep in mind it will effect performance during re-Organization. Query used to rebuild all indexes on mentioned table. You can mention indexes name for one.

========================= Code segment  starts ==================================


ALTER INDEX ALL ON Blog.dbo.Hashfunction REBUILD
GO
========================= Code segment ends ===================================

Typed XML vs UnTyped XML

XML stands for EXtensible Markup Language. XML tags are not predefined. You must define your own tags. It is used so much in web designing and managing structure and data. XML introduced in SQL server as well to manage extensive data with your specific structure as you can define relational tables in database. From SQL SERVER 2005 xml introduced as data type both for column and variable.

Typed XML vs UnTyped XML

When we define an xml as a variable, parameter then it is called as UnTyped but when we assign some defined structure with it, it becomes typed. Msdn says “You can optionally associate a collection of XML schemas with a variable, parameter, or column of xml type. In this case, the xml data type instance is called typed. Otherwise, the XML instance is called untyped.”. Untyped xml can verify the opening and closing structure of XML basics like tagging etc but cannot verify structure.

 Typed xml when assigned to a column, SQL Server validates the instance. Schemas provide information about the types of attributes and elements in the xml data type instance. The type information provides more precise operational semantics to the values. For example, decimal arithmetic operations can be performed on a decimal value, but not on a string value. Because of this, typed XML storage can be made significantly more compact than untyped XML.

To create an Untyped xml you only needs to declare an xml variable and assign an xml to it. as shows in following code and screen shot.

You can also create Untyped XML using output of any query using For XML as shown in following screen

To create typed XML you needs to declare XML schema collection first and then assign it to a column of XML data type. Then you can insert data according to assigned schema structure. XML data type stores upto 2GB of xml data. When Schema against XML or Typed XML will require more space to store data and implement Xml Indexes, discussed later. 

There are some general limitation applied to XML discussed below

The stored representation of xml data type instances cannot exceed 2 GB.

·         It cannot be used as a subtype of a sql_variant instance.

·         It does not support casting or converting to either text or ntext. Use varchar(max) or nvarchar(max) instead.

·         It cannot be compared or sorted. This means an xml data type cannot be used in a GROUP BY statement.

·         It cannot be used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE, and DATALENGTH.