Changing Database Collation

The existing Database connection can be changed using the ALTER Database command provided in SQL Server. To change the collation settings

• Restrict the Database access to Single User Mode
• Change the Database collation using the following syntax.
  
  ALTER DATABASE dbname COLLATE [Replace Actual Collation]
  GO
  

This will only change the collation of the Database and not the collation of the Database objects (Tables) if any exists already. We need to explicitly change the collation of the character (VARCHAR / NVARCHAR) columns for each table. To change the collation settings for columns

• Take the backup of all the Indexes and constraints of the tables with Character columns in the Database
• Drop all the Indexes and constraints (Primary Key, Foreign Key, Defaults etc)
• Change the Collation setting for the table columns. The below script can be used to generate the script that identifies the character columns and replace it with the new collation in the Database
  
  
  SELECT 'ALTER TABLE ' + SYSOBJECTS.Name + ' ALTER COLUMN ' + SYSCOLUMNS.Name + ' ' +
  SYSTYPES.name + '(' + RTRIM(CONVERT(CHAR,SYSCOLUMNS.length)) + ') ' + ' COLLATE [Replace Actual Collation]' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' End +
  CHAR(13) + ' GO'
  FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
  WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
  AND SYSOBJECTS.TYPE = 'U'
  AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
  AND SYSCOLUMNS.COLLATION IS NOT NULL
  GO

SQL Server Locking Modes

SQL Server supports acquires different locking modes depends on the type of operation we perform on the Data. The list of locking modes acquired by SQL Sever for each type of operation is given below.

Shared Lock

• Acquired for reading data.
• Other transactions can acquire shared lock on the same resources.
• No other transaction can modify data.

Exclusive Lock

• Acquired to modify data (INSERT, DELETE and MODIFY).
• No other transaction can modify or read data.

Update Lock

• Acquired to execute a data modification operation but first needs to search the table.
• No other transaction can acquire an update lock or an exclusive lock.

Schema Lock

• Modification lock - Acquired for DDL queries.
• Stability lock – Acquired when compiling queries.

Bulk Update Lock

• Acquired for performing bulk copy of data

SQL Server Locking Tips

SQL Server uses locking to support concurrency of data in Multi user environment .The locking can be applied to the Database resources such ROWS, INDEX, PAGE, EXTENT, TABLE or the Database itself. SQL Server automatically escalates row, key, or page locks to table locks as appropriate to protects system resources and increases efficiency. Locking at smaller granularity increases concurrency but create high overhead on the Database on the other hand Locking at larger granularity reduces overhead but expense in terms of concurrency.The best practice is to avoid lock escalation .The following tips can be useful to minimize locking

• Keep all Transact-SQL transactions as short as possible.
• Avoid interleaving reads and database changes in same transaction.
• Do all the conditional logic and variable assignment outside of a Transaction.
• Encapsulate all transactions within stored procedures.
• Avoid Transact-SQL statements inside the transaction that affect large numbers of rows at once.
• Although WHILE nesting transactions is perfectly legal avoid using inside the transactions.
• For lookup tables consider altering the default lock level for the table (Use SP_INDEXOPTION).
• Do not create temporary tables from within a stored procedure that is invoked by the INSERT INTO #temp EXECUTE statement.