SQL Server 2005 and 2000 on same machine

The installation of SQL Server 2005 and 2000 on the same machine may lead to the problem that the SQL Server 2000 server will not be visible to the client machines. This problem can be resolved using the SQL Server Browser Service which comes along with the SQL Server 2005 installation

What is Browser Service?

SQL Server Browser runs as a Windows service on the server. SQL Server Browser listens for incoming requests for SQL Server resources and provides information about SQL Server instances that are installed on the computer. SQL Server Browser contributes to three actions:

• Browsing a list of available servers
• Connecting to the correct server instance
• Connecting to Dedicated Administrator Connection (DAC) endpoints

For each instance of the Database Engine, the SQL Server Browser service (sqlbrowser) provides the instance name and the version number. SQL Server Browser is installed with SQL Server 2005 and provides assistance for previous versions of SQL Server that are running on that computer, starting with SQL Server 7.0.
In SQL Server 2000, the identification of the server connection endpoints was performed by the SQL Server service. SQL Server 2005 replaces that function with the SQL Server Browser service. If you install SQL Server on a computer that is also running SQL Server 2000 or MSDE, they must be upgraded to SP3 or later. Versions earlier than SP3 do not properly share port 1434 and might not make your SQL Server instances available to requesting client applications. Although you can change the startup order so that the SQL Server Browser service starts before SQL Server 2000 or MSDE, the recommended resolution is to update all older versions of SQL Server to the latest service pack.

Trouble shooting using Browser Service

When an instance of SQL Server 2000 is installed on the computer, if SQL Server Browser is not running, the SQL Server 2000 listener service will start. If SQL Server Browser starts after the listener service, it waits five seconds for SQL Server 2000 to give up port 1434. If that does not occur, SQL Server Browser will not start.
To resolve this problem with versions of SQL Server 2000 earlier than SP3, stop SQL Server 2000, start SQL Server Browser, and then restart SQL Server 2000. The SQL Server 2000 listener service will continue to try to start on port 1434, so the SQL Server 2000 instance should be upgraded to SP3 as soon as possible.
when your application is accessing SQL Server across a network, if you stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and write your client application code to always use that port number.

Paging with MS SQL Server 2005

Paging can be easily achieved in MS SQL Server 2005 by using the ROW_NUMBER function and CTE (Common Table Expressions). The ROW_NUMBER function provides the ability to issue a query and just return a subset of the result set and the CTE allows us to define the temporary result set with clause. The below example shows how to retrieve first ten Patients from a result set using the CTE and ROW_NUMBER function.

USE Patient_DB;
GO
WITH PatientDetail
AS
(
SELECT
Surname,
Forename,
Age,
ROW_NUMBER() OVER (order by Surname) AS 'RowNumber'
FROM Patient
)
SELECT * FROM
PatientDetail
WHERE
RowNumber between 1 and 10;
GO

The OVER clause is used to determine the partitioning and ordering of the intermediary result set before the ROW_NUMBER function is applied. The SELECT statement can be parameterized to retrieve data for the specified Range as given below.

USE Patient_DB;
GO
WITH PatientDetail
AS
(
SELECT
Surname,
Forename,
Age,
ROW_NUMBER() OVER (order by Surname) AS 'RowNumber'
FROM Patient
)

SELECT * FROM
PatientDetail
WHERE RowNumber between
@RowNumberFrom and @RowNumberTo;
GO

World’s Fastest Optical chip

IBM has announced Monday an optical chipset that could allow you to download a complete high-definition movie in one second, rather than the 30 minutes or more that today's fastest chips allow.
Measured in another way, the chipset can transmit the equivalent of four million simultaneous telephone conversations. The company said that the optical transceiver can move data at up to eight times more quickly than the fastest existing optical chips -- up to 160 Gbps.
The optical transceiver chipset moves information as light signals, not as electrical signals, and IBM said it could be available by 2010.
the new optical chipset, only one-fifteenth the size of a dime, can be manufactured with high-volume techniques and so could result in low-cost products. They could be integrated into printed circuit boards for PCs or set-top boxes.
More details at
http://www.sci-tech-today.com/story.xhtml?story_id=12200CQ3ZX4E

SQL Server 2005 Service Pack 2 Features

Microsoft has recently released the service pack 2 for SQL Server 2005.The service pack includes the following key enhancements

• Data Mining Add-ins for the 2007 Microsoft Office system enable data mining functionality from SQL Server Analysis Services (SSAS) to be used directly within Excel® 2007 and Visio® 2007.
• SQL Server Reporting Services (SSRS) compatibility with Microsoft Office Share Point® Server 2007 provides integration with the Report Center in Share Point, enabling the seamless consumption and management of SSRS reports within Share Point.
• SQL Server Analysis Services improvements for Excel 2007 and Excel Services relate to performance and functionality.
• Data compression (varDecimal) is an important feature for data warehousing scenarios, requiring less disk storage of decimal data and increasing overall performance.
• Manageability enhancements, based on customer feedback, provide management capabilities for database administrators such as improvements in database maintenance plans, enhanced management reports and a new copy database wizard.
• Management reports added to SQL Server Express Edition enable customers to get insights into the performance of their Express Edition and SQL Server Compact Edition databases.
• Interoperability improvements including Oracle support in the Report Builder feature enable customers to use its functionality on top of Oracle data sources. Customers also have access to SQL Server Reporting Services to build reports on top of Hyperion’s Essbase cubes.

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.