FAQ : @@Servername and SQLServername result does not match

OR
Why @@Servername and Serverproperty(‘Servername’) is giving different name.
OR
Why my default sql server instance name does not match the machine name?


If you rename the Physical Machine (OS) which was already installed with an SQL Server default instance the ServerProperty() function will show you the lastest name and the @@Servername function will show you the old name. Because SQL Server Setup sets this @@Servername variable to the computer name during installation. To synchronize the result of @@Servername with the new name, you need to drop the old servername and add the new server name using sp_DropServer and sp_AddServer

Steps are as follows

--Check Servername
select @@Servername,Serverproperty('Servername')

-- (a) Drop old server name
Exec Sp_dropServer ‘OldServername’

-- (b) –Add new servername
Exec Sp_addServer ‘NewServerName’, 'Local'

-- (c) See the changes
Exec sp_Helpserver

-- (d) Check Servername
select @@Servername,Serverproperty('Servername')

Refer :
http://msdn.microsoft.com/en-us/library/aa197071(SQL.80).aspx

SQL Server Compatibility Level

Compatibility level of a database specifies SQL Server version compatibility and can be set to SQL Server 7.0(70), SQL Server 2000 (80) or SQL Server 2005 (90). When set to other than SQL Server 2005 (90), the compatibility makes the database behavior compatible with that version of SQL Server. By default it is set to 90. other options are given for primarily to address Upgradation problem, if any. The compatibility level can not be set using ALTER DATABASE Command. You should use sp_dbcmptlevel system stroed procedure to change compatibility.

Syntax : sp_dbcmptlevel datbasename , [70/80/90]

New Features of SQL Server 2005

These are the few new fetures of sql server 2005. There are many but, we can consider these are the key features

. SQL Server Management Studio (SSMS)
. SQL Server Configuration Manager
. Common language runtime (CLR)/.NET Framework integration
. Dynamic management views (DMVs)
. System catalog views
. SQL Server Management Objects (SMO)
. Dedicated administrator connection (DAC)
. SQLCMD
. Database Mail
. Online index and restore operations
. Native encryption
. Database mirroring
. Database snapshots
. Service Broker
. SQL Server Integration Services
. Table and index partitioning
. Snapshot isolation
. Business Intelligence (BI) Development
. Query Notification
. Multiple active result sets
. New SQL Server data types
. SQL Server Management Studio (SSMS)
. SQL Server Configuration Manager
. Common language runtime (CLR)/.NET Framework integration
. Dynamic management views (DMVs)
. System catalog views
. SQL Server Management Objects (SMO)
. Dedicated administrator connection (DAC)
. SQLCMD
. Database Mail
. Online index and restore operations
. Native encryption
. Database mirroring
. Database snapshots
. Service Broker
. SQL Server Integration Services
. Table and index partitioning
. Snapshot isolation
. Business Intelligence (BI) Development
. Query Notification
. Multiple active result sets
. New SQL Server data types

SQL Server 2005 - Database Engine Enhancements

Several new database-specific enhancements have been added to SQL Server 2005. These changes are focused primarily on the database storage engine. The following are some of the most important enhancements:

. Instant file initialization—New or expanded database files are made available much faster now because the initialization of the file with binary zeros is deferred until data is written to the files.

. Partial availability—In the event of database file corruption, the database can still be brought online if the primary filegroup is available.

. Database file movement—You can now use the ALTER DATABASE command to move a database file. The physical file must be moved manually. This feature was available in SQL Server 2000, but it only worked on tempdb.In addition, many new table-oriented enhancements are available with SQL Server 2005. This includes features that define how the data in the tables will be stored in the database.

The following are two of the key enhancements:
. Large rows—SQL Server 2005 now allows for the storage of rows that are greater than 8060 bytes. The 8060-byte limitation that existed with SQL Server 2000 has been relaxed by allowing the storage of certain data types (such as varchar and nvarchar) on a row overflow data page.

. Stored computed columns—Computed columns that were calculated on-the-fly inprior versions can now be stored in the table structure. You accomplish this by specifying the PERSISTED keyword as part of the computed column definition.

SQL Server 2005 - T-SQL Enhancements

SQL Server 2005 provides many enhancements to the T-SQL language that allow you to improve the performance of your code and extend your error-management capabilities. These enhancements include improved error handling, new recursive query capabilities, and support for new SQL Server database engine capabilities. Some of the T-SQL enhancements are as follows:

. Ranking functions—SQL Server 2005 introduces four new ranking functions:
ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These new functions allow you to efficiently analyze data and provide ranking values to result rows of a query.

. Common table expressions—A common table expression (CTE) is a temporary named result set that can be referred to within a query, similarly to a temporary table. CTEs can be thought of as an improved version of derived tables that more closely resemble a non-persistent type of view. You can also use CTEs to develop recursive queries that you can use to expand a hierarchy.

. PIVOT/UNPIVOT operator—The PIVOT operator allows you to generate crosstab reports for open-schema and other scenarios in which you rotate rows into columns, possibly calculating aggregations along the way and presenting the data in a useful form. The UNPIVOT operator allows you to normalize pre-pivoted data.

. APPLY—The APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression.

. TOP enhancements—In SQL Server 2005, the TOP operator has been enhanced, and it now allows you to specify a numeric expression to return the number or percentage of rows to be affected by your query; you can optionally use variables and subqueries. You can also now use the TOP option in DELETE, UPDATE, and INSERT queries.

. DML with results—SQL Server 2005 introduces a new OUTPUT clause that allows you to return data from a modification statement (INSERT, UPDATE, or DELETE) to the processing application or into a table or table variable.

. Exception handling for transactions—Earlier versions of SQL Server required you to include error-handling code after every statement that you suspected could potentially generate an error. SQL Server 2005 addresses this by introducing a simple but powerful exception-handling mechanism in the form of a TRY...CATCH T-SQL construct.