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.

ALTER TABLE SWITCH PARTITION FAILURE

Problem

The SWITCH TO Partition fails when we perform a swith from non-partitioned table to partitioned table even though the same constraint exists on the partitioned column for both the tables

Solution

The solution to the problem can be explained using the below example

/* Partition Function */
CREATE PARTITION FUNCTION [IdentiferPFN](int) AS RANGE LEFT FOR VALUES (100, 200, 300, 400)

/* Partition Scheme */
CREATE PARTITION SCHEME [IdentiferPFN] AS PARTITION [IdentiferPFN] TO ([FILEGROUP1])GO

/* Partition Table */
CREATE TABLE [dbo].[TestPartition]
( [PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL, [EmployeeID] [int] NULL)
ON [IdentiferPFN]([EmployeeID])
GO
ALTER TABLE [dbo].[TestPartition] WITH CHECK ADD CONSTRAINT [CChk] CHECK (([employeeid]>=(0)))
GO
ALTER TABLE [dbo].[TestPartition] WITH CHECK ADD CONSTRAINT [CChk1] CHECK (([employeeid]<=(100)))
GO

/* Non Partition Table */

CREATE TABLE [dbo].[TestPartitionSwitch]
( [PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL, [EmployeeID] [INT] NULL)
ON [FILEGROUP1]
GO
ALTER TABLE [dbo].[TestPartitionSwitch] WITH CHECK ADD CONSTRAINT [Chk] CHECK (([employeeid]>=(0)))
GO
ALTER TABLE [dbo].[TestPartitionSwitch] WITH CHECK ADD CONSTRAINT [Chk1] CHECK (([EMPLOYEEID]<=(100)))
GO

/* Swith the data from Partition table to non Parition table */
ALTER TABLE TestPartitionSwitchSWITCH TO TestPartition PARTITION 1

/* Swith the data back to Partition table */
ALTER TABLE TestPartitionSWITCH PARTITION 1 TO TestPartitionSwitch

This switch back statement will throw an error saying

"ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'PARTITIONTEST.dbo.TestPartitionSwitch' allows values that are not allowed by check constraints or partition function on target table'PARTITIONTEST.dbo.TestPartition'."

This problem can be solved by modifying the constriaint to include the NOT NULL check on the partition column "EmployeeID" as below.

ALTER TABLE [dbo].[TestPartitionSwitch] WITH CHECK ADD CONSTRAINT [Chk] CHECK (([employeeid]>=(0) AND [employeeid] IS NOT NULL))
GO
ALTER TABLE [dbo].[TestPartitionSwitch] WITH CHECK ADD CONSTRAINT [Chk1] CHECK (([EMPLOYEEID]<=(100) AND [employeeid] IS NOT NULL))
GO

A NULL in partition column would satisfy the check constraint but fall outside of the target partition.Changing the nullablility of the columns or asserting not null in the constraint resolves the issue thats the trcik.

Easy Steps for Upgrading to SQL Server 2005

Detach the Database from SQL Server 2000 server and attach to SQL Server 2005 server/Back Up and restore.

Once the Database is restored perform the following steps to make sure the Upgrade process is done perfect.

Update statistics - To help optimize query performance, update statistics on all databases following upgrade. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server 2005 databases.
Update usage counters - In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, we recommend that you run DBCC UPDATEUSAGE on all databases following upgrade.
Change Database Compatibility – Change the Database compatibility level to SQL SERVER 2005 (90)
Configure your new SQL Server installation - To reduce the attackable surface area of a system, SQL Server 2005 selectively installs and activates key services and features. Change the surface area configuration as required. For more information on how to activate SQL Server 2005 features, see SQL Server Surface Area Configuration.

The following check list should be verified before Installing / Upgrading to SQL Server 2005

• Review Hardware and Software Requirements for Installing SQL Server 2005.
• Review Check Parameters for the System Configuration Checker.
• Review Security Considerations for a SQL Server Installation.
• Review Using Upgrade Advisor to Prepare for Upgrades.
• Review SQL Server 2005 Database Engine Backward Compatibility.
• Back up all SQL Server database files from the instance to be upgraded, so you can completely restore them, if necessary.
• Run the appropriate Database Console Commands (DBCC) on databases to be upgraded to ensure they are in a consistent state.
• Estimate the disk space required to upgrade SQL Server components, as well as user databases. For disk space required by SQL Server 2005 components, see Hardware and Software Requirements for Installing SQL Server 2005.
• Ensure that existing SQL Server system databases - master, model, msdb, and tempdb - are configured to autogrow, and ensure that they have adequate hard disk space.
• Ensure that all database servers have logon information in the master database. This is important for restoring a database, as system logon information resides in master.
• Disable all startup stored procedures, as the upgrade process will stop and start services on the SQL Server instance being upgraded. Stored procedures processed at startup time may block the upgrade process.
• Stop Replication and make sure that the replication log is empty.
• Quit all applications, including all services with SQL Server dependencies. Upgrade may fail if local applications are connected to the instance being upgraded.

SQL Server Index Creation Guide Line

Index creation guide

Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.

Only add indexes if you know that they will be used by the queries run against the table

As a rule of thumb, every table should have at least a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases--such as an identity column, or some other column where the value is increasing--and is unique. In many cases, the primary key is the ideal column for a clustered index

Static tables (those tables that change very little, or not at all) can be more heavily indexed that dynamic tables (those that are subject to many INSERTS, UPDATES, or DELETES) without negative effect.

If you will be creating an index to speed the retrieval of a single record, you may want to consider making it a non-clustered index, and saving the clustering index (you can only have one) for a more complex query

Don't over index your OLTP tables, as every index you add increases the time it takes to perform INSERTS, UPDATES, and DELETES

Don't add the same index twice on a table.

Whether an index on a foreign key has either high or low selectivity, an index on a foreign key can be used by the Query Optimizer to perform a merge join on the tables in question. A merge join occurs when a row from each table is taken and then they are compared to see if they match the specified join criteria. If the tables being joined have the appropriate indexes (no matter the selectivity), a merge join can be performed, which is generally much faster than a join to a table with a foreign key that does not have an index.

If you have two or more tables that are frequently joined together, then the columns used for the joins should have an appropriate index.

When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each row is unique, and once the needed record is found, SQL Server doesn't have to look any further.

Ways boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:

• Of the search criterions in the WHERE clause, at least one of them should be based on a highly selective column that has an index.
• If at least one of the search criterions in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause.
  If none of the column in the WHERE clause are selective enough to use an index on their own, consider creating a covering index for this query.
• The Query Optimizer will always perform a table scan or a clustered index scan on a table if the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed (or does not have a useful index). Because of this, if you use many queries with OR clauses, you will want to ensure that each referenced column in the WHERE clause has an index.
• The Query Optimizer converts the Transact-SQL IN clause to the OR operator when parsing your code. Because of this, keep in mind that if the referenced column in your query doesn't include an index, then the Query Optimizer will perform a table scan or clustered index scan on the table.
• Create composite indexes with the most restrictive column first.
  

Don’t add the index under the following cases

• If it is not used by the query optimizer. Use Query Analyzer's "Show Execution Plan" option to see if your queries against a particular table use an index or not. If the table is small, most likely indexes will not be used.
• If the column values exhibit low selectivity, often less than 90%-95% for non-clustered indexes.
• If the column(s) to be indexed are very wide.
• If the column(s) are defined as TEXT, NTEXT, or IMAGE data types.
• If the table is rarely queried.
  

SQL Server Fill Factor Recommendations

SQL Server Query performance can be improved by creating indexes with the appropriate Fill Factor values. The following steps should be done

Step 1:

The tables should be classified into one of the following categories
• Low Update tables
• High Update tables
• Everything In-Between

Step 2:

Modify the Index creation scripts to hold the fill factor values as given below

Low Update Tables (100-1 read to write ratio): 100% fillfactor
High Update Tables (where writes exceed reads): 50%-70% fillfactor
Everything In-Between: 80%-90% fillfactor.