In this blog, I will explain the SQL Transaction. A transaction is a logical unit of work. Each transaction begins with a specific task and ends with all tasks in a group successfully complete. If any tasks fail, it means that the transaction fails. All steps must be committed (transaction Success) or rolled back (transaction failure). A transaction begins to initiate the execution of the SQL statement. A transaction must be committed or rolled back. It is separate operations succeed is transaction succeed and committed to the database. If any separate operation fails means transaction failure and must be undone rolled back. The following are the properties of a transaction.
 
1. Atomicity
2. Consistency
3. Isolation
4. Durability

Transaction Process

• BEGIN TRANSACTION - Starts the transaction
• ROLLBACK - If an error occurred, reverts the existing transaction changes
• COMMIT - No error occurred, then it saves all transaction states
• SAVEPOINT - Rollback particular named transaction

Example
    BEGIN TRANSACTION T1 
    UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1 
    COMMIT; 
     
    BEGIN TRANSACTION T1 
    UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1 
    ROLLBACK; 
     
    SAVEPOINT T1 
    UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1 
    ROLLBACK T1; 

HostForLIFE.eu SQL Server Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

A sequence object in MS-SQL Server is designated to define and get only integer values, such as int, bigint, smallint, tinyint. However, if we want to generate sequence value(s) that are alpha-numeric, then we can define a Stored Procedure that can combine to generate an alpha-numeric combination of sequence values. This blog gives a complete idea of how this can be implemented.

CREATE DATABASE sampdb1

use sampdb1

--First Create a sequence object s3 which will generate numbers from 1 to 5 and cycles

CREATE SEQUENCE s3

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 5

CYCLE

--Ensuring that the sequence object is defined properly and generating defined values.

SELECT NEXT VALUE FOR s3

--Defining a Stored Procedure that generates a Custom-sequence of values. This stored procedure is going to just get the generated alpha-numeric combination of the sequence.

CREATE PROCEDURE genSeqVals AS

Begin

DECLARE @n as int, @msg varchar(4)

SELECT @n=NEXT VALUE FOR s3

PRINT 'A'+cast(@n as varchar(2))

End

--Execute the following code and check.

Exec genSeqVals

--Defining another Stored Procedure that generates a Custom-sequence of values. This stored procedure returns the alpha-numeric combination of the sequence value that is generated using an OUTPUT parameter.

CREATE PROCEDURE getSeqVals(@res varchar(4) OUTPUT) AS

Begin

DECLARE @n as int, @msg varchar(4)

SELECT @n=NEXT VALUE FOR s3

SET @res = 'A'+cast(@n as varchar(2))

End

--Execute the following code and check.

DECLARE @seqnum varchar(4)

EXECUTE getSeqVals @seqnum output

print @seqnum

This is one way in which sequence objects can be custom-implemented as per the requirement. I hope that the above lines of code have given you deeper insight into T-SQL for custom implementation. Happy coding!

In this article, I am going to tell about the dropping of indexes in View in SQL Server. Views are virtual tables in which data from one or more tables gives the result set as our SQL table does with rows and columns. A View doesn’t store the data permanently in the database and at the time of execution, only its result set gets determined. When a View contains a large amount of rows and has complex logic in it then we can create an index on a View to improve the query performance. A View consists of a Unique Clustered Index and it is stored in the database as the clustered index does. Now, let’s run a few scenarios to check when Clustered index which is created on a View gets dropped automatically.

First, I will create a table on which I will run those scenarios.
CREATE TABLE[dbo]. [Customer] 
    ( 
        [CustomerID][int] IDENTITY(1, 1) NOT NULL, [CustomerName][varchar](50) NOT NULL, [CEO][varchar](40) NULL, [Phone][varchar](20) NOT NULL PRIMARY KEY CLUSTERED( 
            [CustomerID] ASC)) 
GO 

Now, I will create a View which will use this Customer table.
-- Create view 
Create VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName, CEO 
  from dbo.Customer 
  GO 

Here in the definition of View, I have used WITH SCHEMABINDING which is necessary for creating an index on a View. This option simulates that we cannot delete any of the base table used in the View and in order to make any changes, first, we need to drop or alter the View.

Also, all the table references in a View should have two part naming convention (schemaname.tablename) as we have in vw_Customer view (dbo.Customer).

Now, I will create an index on our View.

Scenario 1
Create index IX_CustomerID  
 ON vw_customer (CustomerID); 
 GO 

Error returned: Cannot create index on view 'vw_customer'. It does not have a unique clustered index. On Views, the first index must be a unique clustered index, so this will throw the error.

So, the first index on View should be UNIQUE CLUSTERD INDEX, else it will throw an error.

Scenario 2
Create unique clustered index IX_CustomerID  
 ON vw_customer (CustomerID) 
 GO 

Now, our Indexed View is created having a clustered index on it. Now, I want to alter my View and add one more column GETDATE() as CurrentDate  in the View definition and alter the View.

Now, alter the View after Scenario 2.

Scenario 3
ALTER  VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName , GETDATE() as CurrentDate 
  from dbo.Customer 
GO 

Now, as I have altered the View, I want to create another index on column CustomerName which will be a NonClustered index.

Scenario 4
Create index IX_CustomerName  
 ON vw_customer (CustomerName); 
GO 

Again, I get an error: Cannot create index on view 'vw_customer'. It does not have a unique clustered index.

As we have already created Unique Clustered Index on View, still it gives an error. The interesting thing is that after updating the View, the index that was created in Scenario 2 is dropped and the code will throw the same error as in Scenario 1.

As mentioned here,
"ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view."
Hope you will like this post.