Author: Latheesh NK

Cloud & SQL Server

Amazon RDS for SQL Server versus SQL Server On Amazon EC2

Its an era of Cloud computing and anywhere you go, people talk about cloud computing and its usages, migration and benefits etc.So, I thought of taking an initiative on understanding how SQL Server is versed with Cloud. I would like to admit the fact that it was a “walk away” for me initially, but its quite interesting and fascinated knowing more about. Not just because its success stories,but also I loved the challenges while assessing different scenarios SQL Server with Cloud computing and its support.

This blog post concentrates on AWS and SQL Server.

Let us quickly understand what is AWS first?

AWS is a cloud computing platform provided by Amazon. It includes Infrastructure as a Service (IaaS), Platform as a service (PaaS) and Software As a Service (SaaS). Its a “pay-as-you-go” computing model that means you can scale your depending on your needs and pay for the service. Again, I do not want to go in detail about AWS since lots of resources available in internet and its just a google search away.

AWS offers a great flexibility to run SQL server with two major options:

1. Amazon Elastic Compute Cloud (EC2)
EC2 is a version of VM in Amazon cloud platform. It provides us a complete control for settings/configuration etc.
2. Amazon Relational Database Service (RDS)
RDS is a fully managed service without much control to the user. Service would take care of maintenance and manageability of service instances. Amazon RDS supports various relational database engines like MySQL, PostgresSQL, MS SQL, Oracle , MongoDB, Maria DB and Amazon Aurora etc.
Amazon Relational Database Service provides many more managed services compared to EC2 as shown in the below picture. Hence, its is more of business drivers that the customer can spend more effort on their data rather than maintenance and manageability of their database or instance.

Features supported by RDS & EC2

Now, its very important for SQL Server Professionals that to understand the supporting features for Amazon RDS and Amazon EC2. A quick reference table is prepared as below (as on 25th Apr 2020). We need to understand this reference may or may not needs changes as Amazon increases its capabilities in future and we may need to compare with the latest at the time.

SQL Server Features Amazon RDS for SQL Server SQL Server on Amazon EC2
Versions Supported SQL 2012/2014/2016/2017 ALL
Editions Supported Express/Wb/Standard/Enterprise
High Availability AWS Managed Self Managed
Encryption TDE , Encrypted Storage
Authentication Windows & SQL
Backups Managed Customized
Maintenance Automatic Self Managed
Monitoring & Management Amazon CloudWatch
CDC Available

The above are the major comparison chart, more detailed information, please refer See Also section.

Assessment & Planning

Since we had seen some of the differences between Amazon RDS & Amazon EC2 services, it is time for us to evaluate which one would be best suited for our application if there is a migration plan exists. We all should be aware the fact that it is NOT definitely a LIFT and SHIFT technique, but needs lots of understanding of your system and careful evaluation of certain things. This is the most time taken phase. I would like to list out few parameters to help in this decision making as below. Please note, it is a very wide topic and we should do a careful evaluation depending on your application/project workloads and other characteristics. I would also suggest to have an evaluation done by an Amazon professional for smooth transition.

1. Conduct an inventory of your SQL Server instances
2. Conduct an inventory of your SQL Server databases
3. Know your current Licensing option
One of the main reasons for cloud migration is cost saving. It can be in terms of Infrastructure cost or human resource cost or maintenance cost etc. License Cost can also be reviewed here as AWS comes with License option by default, so it would be important to verify the current licensing option with your organization and Microsoft that can be further evaluated. “Amazon RDS for SQL Server supports the “License Included” licensing model. You do not need separately purchased Microsoft SQL Server licenses. “License Included” pricing is inclusive of software, underlying hardware resources, and Amazon RDS management capabilities.” Please refer for more details.
4. Understand your HA/DR solutions
5. Performance benchmarks if any /Capacity Planning
(Resource utilization like CPU/Memory/IOPS etc)
6. Data Retention Policy if any
This assessment helps us to reduce the Disk cost and utilization. Say, If we have a database with 10 years of data and as per business rules, we just need ONLY last 5 years data , then it is good to purge or archive the older data before we migrate to AWS. This would reduce the space usage and enables an easy migration from On-Premise to Cloud. This is one of the area where Micorsoft Azure has flexibility using Stretch database feature; Amazon RDS does not support Stretch Databases yet (as on published date of this blog post).
7. Various Migration options

We will cover lot more interesting things in upcoming posts, until then stay tuned, stay safe!

See Also:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DBA Utilities, SCRIPT GALLERY

SQL Server Inventory Queries – SQL Server Databases

Here is a T-SQL script used for inventory purpose on your SQL Server Databases.

Tested in: SQL Server 2016/SQL Server 2017

Script:

drop table if exists #SQLDatabaseInventory;
create table #SQLDatabaseInventory(
  ServerName varchar(128) default @@servername
, database_id bigint
, DBName varchar(128) default db_name()
, DBOwner varchar(128)
, CreateDate datetime2
, RecoveryModel varchar(12) 
, StateDesc varchar(60)
, CompatibilityLevel int
, DataFileSizeMB int
, LogFileSizeMB int
, DataUsageMB int
, IndexUsageMB int
, SizeMB decimal(17,2)
, Collation varchar(60)
, UserCount int
, RoleCount int
, TableCount int
, SPCount int
, UDFCount int
, ViewCount int
, DMLTriggerCount int
, IsCaseSensitive bit
, IsTrustWorthy bit
, LastFullBackupDate datetime2
, LastDiffBackupDate datetime2
, LastLogBackupDate datetime2
, IsTracked sql_variant);

insert into #SQLDatabaseInventory(database_id,DBName, DBOwner, CreateDate, RecoveryModel, StateDesc, CompatibilityLevel, IsCaseSensitive
, IsTrustWorthy, Collation, LastFullBackupDate, LastDiffBackupDate, LastLogBackupDate, IsTracked)
select database_id,name, suser_sname(owner_sid), create_date, recovery_model_desc, state_desc,compatibility_level
, IsCaseSensitive=CAST(CHARINDEX(N'_CS_', collation_name) AS bit), is_trustworthy_on, Collation_Name
, t.LastFullBackup, t.LastDiffBackup, t.LastLogBackup
, Case when is_cdc_enabled = 1 Then 'CDC' else '' End 
from master.sys.databases db
outer apply( SELECT
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
FROM msdb.dbo.backupset b 
where b.database_name = db.name
) t;

EXEC master.dbo.sp_msforeachdb'use [?]
update t set SizeMB=(select sum(size)/128. from dbo.sysfiles)
, DataUsageMB=x.DataUsageMB, IndexUsageMB=x.IndexUsageMB
   , DataFileSizeMB = u.DBSize, LogFileSizeMB = u.LogSize 
   , TableCount=y.TC, UDFCount=y.UC, SPCount = y.SC, ViewCount=y.VC
   , DMLTriggerCount=y.DC
   , UserCount = z.UC, RoleCount = z.RC
from #SQLDatabaseInventory t
   outer apply (
   SELECT SUM(case when df.type in (0,2,4) then df.size else 0 end)/128
   , SUM(case when df.type in (1,3) then df.size else 0 end)/128 
   FROM sys.database_files df 
   ) u(DBSize, LogSize)
   outer apply(select  DataUsageMB=sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END)/128,
IndexUsageMB=(sum(a.used_pages)-sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type < > 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END
    ))/128
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
   ) x
   outer apply 
   ( select SC=Sum(case Type when ''P'' then 1 else 0 end)
    , DC=Sum(case Type when ''TR'' then 1 else 0 end)
    , TC=Sum(case Type when ''U'' then 1 end)
    , UC= sum(case when Type in (''TF'', ''IF'', ''FN'') then 1 else 0 end)
    , VC=Sum(case Type when ''V'' then 1 else 0 end)
    from sys.objects where object_id > 1024
    and type in (''U'',''P'',''TR'',''V'',''TF'',''IF'',''FN'')
   ) y
   outer apply 
   ( select UC = sum(case when [Type] in (''G'',''S'',''U'') then 1 else 0 end)
      , RC = sum(case when Type = ''R'' then 1 else 0 end)
      from sys.database_principals
      where principal_id > 4
   ) z where t.DBName=db_name();
'
SELECT A.*,
Case when is_parameterization_forced = 1 then 'ON' ELSE 'OFF' END is_parameterization_forced,
Case when is_query_store_on = 1 Then 'ON' ELSE 'OFF' End is_query_store_on,
Case When is_broker_enabled = 1 Then 'ON' ELSE 'OFF' End is_broker_enabled,log_reuse_wait_desc,
Case when is_read_only = 1 Then 'READ-ONLY' Else 'ALL' End is_read_only,
Case When is_auto_close_on = 1 Then 'Enabled' Else 'Disabled' End is_auto_close_on,
Case When is_auto_shrink_on = 1 Then 'Enabled' Else 'Disabled' End is_auto_shrink_on,
Case When is_auto_update_stats_on  = 1 Then 'Enabled' Else 'Disabed' End is_auto_update_stats_on
FROM #SQLDatabaseInventory A
inner join sys.databases B
On B.database_id = A.database_id

I would really love to hear from you what else you would like to see to this list?

See Also:

https://sqlzealots.com/2020/04/26/sql-server-inventory-queries-sql-server-instances/

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

 

DBA Utilities, SCRIPT GALLERY

SQL Server Inventory Queries – SQL Server Instances
Here is a T-SQL script used for inventory purpose on your SQL Server Instances.

Tested in: SQL Server 2016/SQL Server 2017

Script:

drop table if exists #t;
 
create table #t(
  ServerName varchar(128) default @@servername,
  ServerEdition sql_variant default serverproperty('edition'),
  ServerVersion sql_variant default @@version,
  ProductVersion sql_variant default serverproperty('productversion'),
  ProductLevel sql_variant default serverproperty('ProductLevel'),
  ServerCollation sql_variant default serverproperty('collation'),
  MachineName sql_variant default serverproperty('ComputerNamePhysicalNetBIOS'),
  DefaultDataPath sql_variant default serverproperty('InstanceDefaultDataPath'),
  DefaultLogPath sql_variant default serverproperty('InstanceDefaultLogPath'),
  IsClustered sql_variant default serverproperty('IsClustered'),
  IsFullTextInstalled sql_variant default serverproperty('IsFullTextInstalled'),
  IsIntegratedSecurityOnly sql_variant default CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
							WHEN 1 THEN 'Windows Authentication'   
							WHEN 0 THEN 'Windows and SQL Server Authentication'   
							END,
  cpu_count int,
  hyperthread_ratio int,
  Physical_Memory_GB Bigint,
  max_workers_count Bigint,
  sqlserver_start_time datetime,
  cores_per_socket bigint,
  numa_node_count bigint,
  --Configuration Details
  [Ad Hoc Distributed Queries] sql_variant,[xp_cmdshell] sql_variant,
  [optimize for ad hoc workloads] sql_variant,[backup compression default] sql_variant,
  [blofcked process threshold (s)] sql_variant,[default trace enabled] sql_variant,
  [clr enabled] sql_variant,[max server memory (MB)]sql_variant,
  [min server memory (MB)] sql_variant,[max degree of parallelism] sql_variant,
  [cost threshold for parallelism] sql_variant,[max text repl size (B)] sql_variant,
  [show advanced options] sql_variant
);

insert into #t(cpu_count,hyperthread_ratio,Physical_Memory_GB,max_workers_count,sqlserver_start_time,cores_per_socket,numa_node_count,
[Ad Hoc Distributed Queries],[xp_cmdshell],[optimize for ad hoc workloads],[backup compression default]
    ,[blofcked process threshold (s)],[default trace enabled],[clr enabled],[max server memory (MB)],[min server memory (MB)]
    ,[max degree of parallelism],[cost threshold for parallelism],[max text repl size (B)],[show advanced options])
 
select cpu_count,hyperthread_ratio,((Physical_Memory_kb/1024.0)/1024.0),max_workers_count,
sqlserver_start_time,cores_per_socket,numa_node_count,
B.*
from sys.dm_os_sys_info A
outer apply ( Select Max([Ad Hoc Distributed Queries]) [Ad Hoc Distributed Queries],
        Max([xp_cmdshell]) [xp_cmdshell] ,
        Max([optimize for ad hoc workloads]) [optimize for ad hoc workloads],
        Max([backup compression default]) [backup compression default],
        Max([blofcked process threshold (s)]) [blocked process threshold (s)],
        Max([default trace enabled]) [default trace enabled],
        Max([clr enabled]) [clr enabled],
        Max([max server memory (MB)]) [max server memory (MB)],
        Max([min server memory (MB)]) [min server memory (MB)],
        Max([max degree of parallelism]) [max degree of parallelism],
        Max([cost threshold for parallelism])[cost threshold for parallelism],
        Max([max text repl size (B)]) [max text repl size (B)],
        Max([show advanced options]) [show advanced options]
 From sys.configurations 
 PIVOT (MAX(Value_in_use) for Name in([Ad Hoc Distributed Queries],[xp_cmdshell],[optimize for ad hoc workloads],[backup compression default]
    ,[blofcked process threshold (s)],[default trace enabled],[clr enabled],[max server memory (MB)],[min server memory (MB)]
    ,[max degree of parallelism],[cost threshold for parallelism],[max text repl size (B)],[show advanced options])) as PVT) B
 
Select * From #t

I would really love to hear from you what else you would like to see to this list?

See Also:

https://sqlzealots.com/2020/04/26/sql-server-inventory-queries-sql-server-databases/

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SSMS

How to enable Side by Side Query window in SQL Server Management Studio


Side by Side Query window is a very useful feature in SSMS. When we are comparing queries or its data, then this feature becomes very handy.

Let us quickly see how do we enable this feature with very simple steps as below:

Select Window Menu in SSMS – > Select “New Horizontal Tab Group” OR “New Vertical Tab Group” as you wish to see the query windows either horizontally or vertically!

You can always revert the change as below:
Select Window Menu in SSMS – > Select “Move to Previous Tab Group”

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Table Partitioning

Stairway to SQL Server Table Partitioning – Whats is Table Partitioning in SQL Server
There are two types of partitioning in SQL Server – Vertical & Horizontal. Today, we are quickly going to see these two types in SQL Server and its different use cases. One of the easiest way to explain Vertical partitioning is a partition at Column level otherwise Horizontal Partitioning is at Row level.

Vertical Partitioning

Vertical Partitioning is a process of splitting a really big table into multiple smaller tables for various reasons. Performance improvement is the major benefit of vertical partitioning. If your table is having large number of columns and lots of Large Object columns or very lengthy textual information, then partitioning the table into multiple smaller tables would really help to boost the performance especially if not all of your columns are frequently accessed. A simple pictorial representation as below: Lets quickly see how vertical partitioning helps in performance. (Note that this is a very simple example for readers to understand the impact of query execution). Few guys will have different arguments, please post those questions in comment, we will discuss in upcoming blog post. But for now, I strictly wanted to limit the scope of this post ONLY to the context of vertical partitioning.

Script:

Drop table if exists Employee_Tab1
Create Table Employee_Tab1
(EmployeeID int,FirstName varchar(10),
MiddleName varchar(10)
,LastName varchar(10)
,DOB Date
)DECLARE @i int
SET @i = 1
BEGIN TRAN
WHILE @i<>100000
BEGIN
INSERT INTO Employee_Tab1 
VALUES(@i,'a' ,'b','c', getdate())
SET @i=@i+1
END
COMMIT TRAN
GO

Set statistics io on
Select EmployeeID,DOB From Employee_Tab1
where dob = cast(getdate() as date)
Set statistics io off
 
Drop table if exists Employee
Create Table Employee
(EmployeeID int,FirstName varchar(10),
MiddleName varchar(10),
LastName varchar(10),
DOB Date,Address1 nvarchar(max),
Address2 nvarchar(max),
Address3 nvarchar(max))
DECLARE @i int
SET @i = 1
BEGIN TRAN
WHILE @i<>100000
BEGIN
INSERT INTO Employee 
VALUES(@i,'a' ,'b','c',
getdate(),Replicate('a',8000),
Replicate('a',8000),Replicate('a',8000))
SET @i=@i+1
END
COMMIT TRAN
GO
Set statistics io on
Select EmployeeID,DOB From Employee
where dob = cast(getdate() as date)
Set statistics io off

So if you look at the code, we tried to create two tables Employee and Emploee_Tab1 and we inserted almost 100000 records to these tables. You can observe the difference between these two tables – Employee table contains 3 large text columns for address where as the Employee_Tab1 does not have those columns.When we query these tables (note that we are not referring the address columns), we can observe a huge number of logical reads happens with Employee table where as the table employee_tab1 which does not have address columns has to do only less logical reads(532 compared to 2000). This indicates that SQL has to get more data from disk to cache for second query (using employee table) than first query (using vertically partitioned table Employee_Tab1). You may observe the behavior in the below side by side query window screenshot clearly.

Points to ponder:

Use Vertical partitioning

1. If your table is having more columns

2. If your table has lots of BLOB or wide text columns those are not frequently used

3. If your table is a highly transaction table

4. with extreme care if you are restructuring your table since it may have big impact like many objects may need to change.

Horizontal Partitioning

As mentioned earlier, horizontal partitioning is a partition process that carries out at row level. That means, if you a table with (m/b)illions of data, you can partition the table horizontally. This would enable better manageability/maintainability etc. Horizontal partitioning may also have performance benefits, however, we should know how to implement this technique efficiently.

Let us quickly see a pictorial representation of the Horizontal partitioning as below.

Horizontal Partitioning requires understanding of few important terminologies.

1. Partition Key Column

 Partition Key column determines on what basis the table has to be partitioned. In our example, the partition key column is AuditDate. It is important to be noted that the choice of partition key column is very important for performance aspects. We will cover this in details in next blog posts.

2. Partition Function

 Just partition key column does not partition the table. We need a partition logic to be implemented on key column to partition the table. This logic can be implemented through a function called partition function. We can implement the function using RANGE Right and RANGE Left. Simply illustration of Range as below.
    1. Range left means that the actual boundary value belongs to its left partition, it is the last value in the left partition.

    2. Range right means that the actual boundary value belongs to its right partition, it is the first value in the right partition.

Usually, for an year column partition, the last day of the year (31st Dec year) is the range left and first day of the year (1st Jan year) is the range right. If range has not been specified properly, then there will be an overlapping of two years data in the same partition that can cause lots of confusion and difficulties, in fact it may even defeat the purpose of partition altogether.

3. Partition Scheme

The partition scheme maps the logical partitions to physical filegroups. It is possible to map each partition to separate filegroup or all partitions to one filegroup. There are many benefits with a proper mapping of partition and filegroup that we can cover in next blog posts.

Points to ponder:

Use Horizontal partitioning

1. If you know your table and data in detail (I am not joking – 🙂 )

2. If your table has lots of data and you have a definite partition requirement

3. If your table needs an archival of data or efficient movement of data

We will cover more on this topic in coming days, until then stay tuned!

Refer Also:

Stairway to SQL Server Table Partitioning – How do we partition an existing table

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SCRIPT GALLERY

Understanding PIVOT operator in SQL Server with Examples
Today, we will quickly see PIVOT operator in SQL Server and why do we need this and its various forms of usages. To understand better, let us think of a transformation of data as below and see how do we approach further.

First off, let us create a CustomerPurchaseTable as below. Please note if you are not heard of DROP IF EXISTS yet, please refer here. 

Drop Table if exists  CustomerPurchaseTable

create Table CustomerPurchaseTable( CustomerID int, FruitName varchar(100), Quantity int)
Insert into CustomerPurchaseTable Values
(100,'Apple',10),(100,'Grapes', 5),(101,'Orange',10),(102,'Pineapple',5),(102,'Grapes',10)

Select * From CustomerPurchaseTable

I remember when I started my career I was using SQL Server 7.0 and SQL 2000. It was not so easy task to achieve transformations as of today, particularly, the pivoting had achieved using Select ..CASE statements as below. 

Select CustomerID, Sum(Case When FruitName = 'Apple' Then Quantity Else NULL End )'Apple',
				   Sum(Case when FruitName = 'Grapes' Then Quantity Else NULL End ) 'Grapes',
				   Sum(Case when FruitName = 'Orange' Then Quantity Else NULL End ) 'Orange'
From CustomerPurchaseTable
Group by CustomerID

Post SQL Server 2005 version release, there was a new operator introduced “PIVOT” which does the work with ease. I would say, its a fancy form of old way.But, at the same time, I would agree that its a saver at times, we would see that later part of this blog post.

Here is the syntax of PIVOT:

Syntax:
SELECT first_column AS ,
[pivot_value1], [pivot_value2], ... [pivot_value_n]
FROM 
() AS 
PIVOT 
(
 aggregate_function()
 FOR 
 IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
) AS ;

Select * From CustomerPurchaseTable
PIVOT (Sum(Quantity) For FruitName in ([Apple],[Grapes],[Orange]))as PivotTable

Now, let us look at a case how it would work for a non-fixed type of fruits in the above example? Then, this should be a dynamic transformation that might required. With old approach(prior to SQL Server 2005), this is a complicated one and hard to achieve with Dynamic Cross-Tabs/Pivot Tables. With the introduction of PIVOT that becomes handy. That does not mean that we do not need dynamic query, but it extremely handy to build dynamic list of columns and assign the value to the dynamic Query to produce the transformation. The generation of column list with quotename is much easier than generating the entire sql construct of CASE…WHEN as in the old way. 

DECLARE 
    @columns NVARCHAR(MAX) = '', @sql     NVARCHAR(MAX) = '';

SELECT @columns += QUOTENAME(FruitName) + ','
FROM (Select Distinct FruitName From CustomerPurchaseTable) A
ORDER BY FruitName ASc

SET @columns = LEFT(@columns, LEN(@columns) - 1);
PRINT @columns;

Set @SQL = 'Select * From CustomerPurchaseTable
PIVOT (Sum(Quantity) For FruitName in (' + @columns + '))as PivotTable'

Exec sp_executeSQL @SQL

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Errors and Exceptions

Error Message in SQL Server: Conflicting locking hints are specified for table “tablename”. This may be caused by a conflicting hint specified for a view.
Recently, One of my colleague has reported an issue as below from production environment.
Msg 4138, Level 16, State 1, Line 19 Conflicting locking hints are specified for table “tablename”. This may be caused by a conflicting hint specified for a view.
Error message is very clear and we can easily understand there are some incompatible hints applied in the same query caused a conflict. Let me quickly reproduce the issue for all of us to understand better with a simple example. 
-- Sample tables and data population
Drop Table if exists T1,T2
GO
create Table T1(Col1 int )
create Table T2(Col1 int )
GO
Insert into T1 Values(1)
Insert into T2 Values(1)
GO
Select * From T1
Select * From T2
The above code will create two tables – T1 and T2. I do not want to get complicated with more columns and its a simple and easy replication with a single column Col1. Now, Let us create a view using both of the tables. Please note, while creating the view we have given WITH (NOLOCK) on one of table called T2 as below. 
--Sample view 
Drop view if exists vw_test
GO
Create OR ALTER View vw_test 
as 
Select * From T1 
     where exists(Select 1 From T2 with (nolock) where t1.Col1 = t2.col1)
If you notice the VIEW, as mentioned above, NOLOCK has given to avoid any locking scenario deliberately. We are not going to cover whether NOLOCK is good or bad in this blog post, but this is a common bad practice that almost all people do, even experience people would do it at times. A view is a generalized form that anyone can use it as they like later. So, in this case, let us see some of the usages of the view to explain the behavior. 
-- SELECT with intended lock
Select * From vw_test A with (UPDLOCK,HOLDLOCK)
This is an example of intended lock on view. While executing the SELECT statement, we will end up with error message “Conflicting locking hints are specified for table “T2″. This may be caused by a conflicting hint specified for a view.”

Another example: 
--Rowlock will conflicts with NOLOCK
Update A Set A.Col1  = A.Col1 + 2 
From vw_test A with (ROWLOCK)
This is another example of ROW lock on view for an UPDATE query. While executing the UPDATE statement, we will end up with error message “Conflicting locking hints are specified for table “T2″. This may be caused by a conflicting hint specified for a view.”

So, precisely, there are many combinations that NOLOCK could end up with this similar exception.

Few are: ROWLOCK/TABLELOCK/UPDLOCK/SERIALIZABLE etc.

How to resolve this issue?

Two ways you can avoid issue as below:

1. Change the view definition to remove NOLOCK 

Create OR ALTER View vw_test 
as 
Select * From T1 
     where exists(Select 1 From T2 where t1.Col1 = t2.col1)
--Rowlock will conflicts with NOLOCK
Update A Set A.Col1  = A.Col1 + 2 
From vw_test (TABLOCKX) A

2. Change the query that uses view to remove the conflict lock hints 

--updlock will conflicts with NOLOCK
Update A Set A.Col1  = A.Col1 + 2 
From vw_test  A

Summary:

This is a simple error message and does not really require any extra thoughts to be put in to understand the error message or conflict scenario. This is not just for NOLOCK, any incompatible hints would cause this issue , however, I would like to take an important point that many of us are using NOLOCK as a free tool without knowing its implications. With this example( its just an another example), I would like to reiterate DO NOT use NOLOCK as a free tool, use it with atmost careful evaluation and understanding!

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!