SQL Server Data Types and Ranges

Quick reference for SQL Server data types

Applies to: SQL Server 2000, SQL Server 2005, SQL Server 2008

Saving disk space is not the only benefit of properly selected data types for SQL Server columns. In addition to disk space benefits, the right data types reduce backup times, improve SQL Server performance, and enhance the execution of queries and joins. Here’s a comprehensive list of all SQL Server datatypes , ranges, and limitations:

Exact numerics

Type From To
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 –1
numeric -10^38 +1 10^38 –1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647

numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate numerics

Type From To
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

 

datetime and smalldatetime

Type From To
datetime (3.33 milliseconds accuracy) Jan 1, 1753 Dec 31, 9999
smalldatetime (1 minute accuracy) Jan 1, 1900 Jun 6, 2079
date (1 day accuracy. Introduced in SQL Server 2008) Jan 1, 0001 Dec 31, 9999
datetimeoffset (100 nanoseconds accuracy. Introduced in SQL Server 2008) Jan 1, 0001 Dec 31, 9999
datetime2 (100 nanoseconds accuracy. Introduced in SQL Server 2008) Jan 1, 0001 Dec 31, 9999
time (100 nanoseconds accuracy. Introduced in SQL Server 2008) 00:00:00.0000000 23:59:59.9999999

 

Character Strings

Type Description
char Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar Variable-length non-Unicode data with a maximum of 8,000 characters.
varchar(max) Variable-length non-Unicode data with a maximum length of 231 characters (Introduced in SQL Server 2005).
text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

 

Unicode Character Strings

Type Description
nchar Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar Variable-length Unicode data with a maximum length of 4,000 characters.
nvarchar(max) Variable-length Unicode data with a maximum length of 230 characters (Introduced in SQL Server 2005).
ntext Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

 

Binary Strings

Type Description
binary Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary Variable-length binary data with a maximum length of 8,000 bytes.
varbinary(max) Variable-length binary data with a maximum length of 231 bytes (Introduced in SQL Server 2005).
image Variable-length binary data with a maximum length of 2,147,483,647 bytes.

 

Other Data Types

  • sql_variant: Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
  • timestamp: Stores a database-wide unique number that gets updated every time a row gets updated.
  • uniqueidentifier: Stores a globally unique identifier (GUID).
  • xml: Stores XML data. You can store xml instances in a column or a variable (Introduced in SQL Server 2005).
  • cursor: A reference to a cursor.
  • table: Stores a result set for later processing.
  • hierarchyid: A variable length, system data type used to represent position in a hierarchy (Introduced in SQL Server 2008).

Understanding SQL Server Backup Types

Applies to: SQL Server 2000, SQL Server 2005

Database backups are at the core of any SQL Server disaster recovery planning for any production system. Backups may be used to provide a means of recovery to a point-in-time when the database was last operational. Microsoft® SQL Server™ provides several types of backups that may be combined to formulate a customized disaster recovery plan depending on the nature of the data and the recovery requirements. It is highly recommended that all SQL Server databases be backed up periodically.

SQL Server backup media

A database may be backed up to disk or to tape. The examples in this article assume a disk backup directly into a disk file (as opposed to a disk backup device). Any database can be backed up to a random disk file at any time. The file may either be initialized (using WITH INIT) or appended with the new backup.

Types of backups in SQL Server

SQL Server provides several different kinds of backups including CompleteDifferentialTransaction Log, andFile(s) and Filegroup(s) backup. A combination of these backups may be used to formulate a robust disaster recovery strategy. The following paragraphs explain each SQL Server backup type

Complete database backup

A complete database backup creates a stand-alone image of the entire database. A complete database backup is self-dependent and may be restored to either the same or a new database on the same or a different server. This provides plenty of flexibility at the time when this backup has to be restored. A complete backup may be restored without the need for any other kind of backup. It may also be performed for databases in any recovery model. Restoring a complete database backup typically would be considered a starting point for a disaster recovery situation where the entire database is lost or damaged. It is recommended that a complete database backup be performed at regular intervals for all production databases. It is also recommended that a complete backup should be performed for system databases if there are any changes performed to the SQL Server operating environment such as creating or removing databases, configuring security, creating and modifying DTS/SSIS packages or scheduled jobs, adding and removing linked servers, etc.

Backup syntax

BACKUP DATABASE Northwind
TO DISK = ‘c:backupsnorthwind.bak’
WITH INIT

Restore syntax (Same database)

RESTORE DATABASE Northwind
FROM DISK = ‘c:backupsnorthwind.bak’

Restore syntax (New database and/or server)

RESTORE DATABASE Northwind_new
FROM DISK = ‘c:backupsnorthwind.bak’
WITH MOVE ‘northwind’ TO ‘c:new_locationNorthwind_new.mdf’
MOVE ‘northwind_log’ TO ‘c:new_locationNorthwind_new_log.ldf’

Differential database backup

A differential backup backs up only modified extents since the last complete backup. An extent is a group of 8 data pages each consisting of 8 KB (64 KB in total). By definition, differential backups are cumulative. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup. Differential backups may be considered as an alternative for databases that are large and are modified infrequently. These would include data warehouse type of databases. Differential backups have several limitations including the following:

  • They do not provide point-in-time restore capabilities
  • They may only be restored after a complete database backup is restored
  • They may not be performed on the master database

Backup syntax

BACKUP DATABASE Northwind
TO DISK = ‘c:backupsnorthwind_diff.bak’
WITH INIT, DIFFERENTIAL

Restore syntax (Same database – Note that a complete database backup is restored first using WITH NORECOVERY)

RESTORE DATABASE Northwind
FROM DISK = ‘c:backupsnorthwind.bkp’
WITH NORECOVERY

RESTORE DATABASE Northwind
FROM DISK = ‘c:northwind_diff.bkp’
WITH RECOVERY

Transaction log backup

An SQL Server database consists of two components: data file(s) and transaction log file(s). A transaction log captures the modifications made to the database. A simple transaction may place several records in the transaction log. Each of these records is known as a log record and is assigned a unique identification number known as the log sequence number (LSN). Log records that belong to the same transaction are linked together through the LSN. If SQL Server service shuts down unexpectedly, upon restart the recovery process examines the entries in the transaction log and if there are transactions that have not been rolled forward completely, the recovery process rolls back the changes performed as part of these incomplete transactions. This operation is extremely important as it forms the basis of transactional recovery. Entries in the transaction log are also used if transactional replication is configured for the specific database.

A transaction log backup backs up all transactions since either the previous transaction log backup, or the complete database backup if there have been no transaction log backups performed for the database in the past. This backup may then be used to apply the backed-up changes, in case disaster recovery is required. Transaction log backups may only be applied to a database in an unrecovered state. A database may be in an unrecovered state if it is being restored from a set of backups as part of a disaster recovery procedure, or if it is configured as a standby database on a warm backup server. A transaction log backup also truncates the inactive portion of the transaction log, unless the database is configured as a Publisher in transactional replication and there are transactions pending propagation to Subscribers.

Each transaction log backup contains a First and Last log sequence number (LSN). Consecutive transaction log backups should have sequential LSNs for the boundary log records. These LSN values may be examined using the RESTORE HEADERONLY command. If LastLSN from the previously restored transaction log backup does not match the FirstLSN from the backup that is currently being restored, the restore operation fails with the following error: “This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log”. If the above message is generated while restoring a particular transaction log backup, which is part of a set of transaction log backups that are to be restored, any attempts to restore further transaction log backups will fail with this message. There could be several reasons for consecutive transaction log backups being out of sequence. Some of the most common reasons noted from support experience have been:

  • The database recovery model has been changed to Simple and back to either Full or Bulk-Logged. Switching the recovery mode to Simple causes the transaction log to be truncated
  • Another transaction log backup was performed between the previous successfully restored backup and the one generating this message
  • The transaction log was manually truncated between the two backups
  • The database was in Bulk-Logged recovery model and non-logged operations were performed
  • Transaction log backups are not allowed for databases in Simple Recovery model. While in Simple Recovery model, a database’s transaction log is truncated every time a CHECKPOINT is invoked for the database

Transaction log backups provide the possibility of performing a point-in-time restore or point-of-failure restore. You can also perform a restore to a named transaction with transaction log backups.

Backup syntax

BACKUP LOG Northwind
TO DISK = ‘c:backupsnorthwind_log_1.bak’
WITH INIT

Restore syntax (Same database – Note that a complete database backup is restored first using WITH NORECOVERY, then a sequence of transaction log backups)

RESTORE DATABASE Northwind
FROM DISK = ‘c:backupsnorthwind.bkp’
WITH NORECOVERY

RESTORE LOG Northwind
FROM DISK = ‘c:northwind_log_1.bkp’
WITH RECOVERY

File(s) and Filegroup(s) backup

Use BACKUP to back up database files and filegroups instead of the full database when time constraints make a full database backup impractical. To back up a file instead of the full database, put procedures in place to ensure that all files in the database are backed up regularly. Also, separate transaction log backups must be performed. After restoring a file backup, apply the transaction log to roll the file contents forward to make it consistent with the rest of the database.

SQL Server Blocking and Deadlocks

The more blocking happens on SQL Server the less concurrency the system. A certain amount of short blocks are unavoidable but too many blocks for longer periods of time can degrade performance and increase the chances of SQL Server deadlocks.

SQL Server blocking

SQL Server blocking occurs when one connection (user process or application process) places a lock on a table (or a number of rows) and a second connection attempts to read or modify the data under the lock. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

The more blocking happens on the server the less concurrency the system achieves. A certain amount of blocking is unavoidable but too many blocks for longer periods of time can degrade the performance of SQL Server.

SQL Server deadlocks

The combination of two blocked connections where the first is blocking the second and the second is blocking the first is called a deadlock. Since deadlocks are not naturally resolved with time, SQL Server automatically kills one of the connection (Deadlock victim) once it detects a deadlock. This allows the other connection to continue with its transaction.

Although deadlocks can be caused by two short blocks (Fraction of a second), it is often the long blocks that increase the chances of a deadlock to happen.

Blocks escalating to deadlocks

The following diagram shows the sequence of events leading to a deadlock. Consider two applications (A1, A2) accessing two different table (T1, T2):

Event 1: A1 places a lock on T1 inside its transaction and continues to execute other statements

Event 2: A2 places a lock on T2 inside its transaction and continues to execute other statements

Event 3: A1 attempts to place a lock on T2 (Needs to access T2 before it can finish the transaction) but has to wait for A2 to release its lock

At this point, a block is created since A2 is blocking A1

Event 4: While A1 is waiting, A2 attempts to place a lock on T1 (Needs to access T1 before it can finish its own transaction)

A deadlock is created since two connections have blocked one another. SQL Server automatically resolves the deadlock by choosing one of the connections as a deadlock victim and killing it.

SQL Server Performance Counters

The most important performance counters to watch when tuning SQL Server performance are CPU usage, memory usage, cache hit ratios, and disk I/O. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to disk I/O and cached disk access is worthwhile.

SQL Server CPU utilization

Measuring the amount of time the CPU is busy running non-idle SQL Server threads is an important aspect of SQL Server performance monitoring and tuning. A continually high CPU usage rate may indicate the need for a CPU upgrade or the addition of multiple processors. Alternatively, a high CPU usage rate may indicate a poorly tuned application or SQL code. Optimizing the application can lower CPU utilization. A consistent state of 80 to 90 percent may indicate the need for a CPU upgrade or the addition of more processors. When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor’s time may not seem unusual. If this causes the performance of other applications to suffer, try changing the timing of the workload.

SQL Server memory utilization

By default, SQL Server changes its memory requirements dynamically based on available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and then uses the memory available. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system. However, the option to dynamically use memory can be overridden using the min server memory, max server memory, and working set size server configuration options.

The more memory your server has the more cached disk reads and writes it can perform. Lack of system memory can cause high non-cached disk reads and writes. Adding memory to your server can help lower physical disk access.

SQL Server Cache Hit Ratios

SQL Server cache hit ratios indicate the percentage of data pages read from the SQL Server cache (memory) vs. those read from the physical disk. The SQL Server cache consists of the buffer cache and the procedure cache.

The Buffer cache is the memory pool used to store SQL Server data. The buffer cache hit ratio indicates the percentage of data pages read from the buffer cache vs. physical disk. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to cached disk I/O is crucial when monitoring the performance of SQL Server. A value of 90% cache hit ratio indicates pages were retrieved from memory 90% of the time. The other 10% were read from disk. A consistent value below 90% indicates that more physical memory is needed on the server.

The Procedure cache is the memory pool used to store SQL Server execution plans. The procedure cache hit ratio indicates the percentage of execution plan pages read from the procedure cache vs. disk.

SQL Server disk I/O

SQL Server disk I/O is frequently the cause of bottlenecks in most systems. The I/O subsystem includes disks, disk controller cards, and the system bus. If disk I/O is consistently high, consider:

  • Move some database files to an additional disk or server.
  • Use a faster disk drive or a redundant array of inexpensive disks (RAID) device.
  • Add additional disks to a RAID array, if one already is being used.
  • Tune your application or database to reduce disk access operations. Consider index coverage, better indexes, and/or normalization.

Microsoft SQL Server uses Microsoft Windows I/O calls to perform disk reads and writes. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. Applications and systems that are I/O-bound may keep the disk constantly active.

Different disk controllers and drivers use different amounts of CPU time to perform disk I/O. Efficient controllers and drivers use less time, leaving more processing time available for user applications and increasing overall throughput.

SQL Server Performance Tips

SQL Server performance tuning can consume a considerable amount of time and effort. The following list is a quick guideline that you should keep in mind when designing and developing SQL Server database applications:

User Defined Functions (UDF)

Refrain from using user defined functions (UDF) in a select statement that may potentially return many records. UDFs are executed as many times as there are rows in a returned result. A query that returns 100,000 rows calls the UDF 100,000 times.

SQL Server table indexes

Create SQL statements that utilize defined table indexes. Using indexes minimizes the amount of table scan which in most cases will be much slower than an index scan.

Multiple disks

The single best performance increase on a SQL Server computer comes from spreading I/O among multiple drives. Adding memory is a close second. Having many smaller drives is better than having one large drive for SQL Server machines. Even though the seek time is faster in larger drives, you will still get a tremendous performance improvement by spreading files, tables, and logs among more than one drive.

Disk controllers

Different disk controllers and drivers use different amounts of CPU time to perform disk I/O. Efficient controllers and drivers use less time, leaving more processing time available for user applications and increasing overall throughput.

SQL Server foreign keys

Ensure that all your tables are linked with foreign keys. foreign keys enhance the performance of queries with joins. Database tables inside each application are naturally related. Islands of tables are rarely needed if your application’s business logic is well defined.

SQL Server primary keys

Ensure that every table has a primary key. if you can’t find a natural set of columns to serve as a primary key, create a new column and make it a primary key on the table.

Processor (CPU)

When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor’s time can be used. If this causes the performance of other applications to suffer, try changing the workload of the queries with aggregates.

Understanding VARCHAR(MAX) in SQL Server 2005 & 2008

In SQL Server 2000 and SQL Server 7, a row cannot exceed 8 KB in size. VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk.

To store more data in a single column, you needed to use the TEXT, NTEXT, or IMAGE data types (BLOBs) which are stored in a collection of 8 KB data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure. BLOBs are hard to work with and manipulate. They cannot be used as variables in a procedure or a function and they cannot be used inside string functions such as REPLACE, CHARINDEX or SUBSTRING. In most cases, you have to use READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate BLOBs.

To solve this problem, Microsoft introduced the VARCHAR(MAX),  NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions.

Microsoft recommend using MAX data types instead of BLOBs in SQL Server 2005. In fact, BLOBs are being deprecated in future releases of SQL Server.

For more information on SQL Server data types and ranges, visit http://www.teratrax.com/sql-server-data-types-ranges/

sp_spaceused: Determine SQL Server table size

Have you ever wondered which tables take the most disk space in your database? Using the sp_spaceused stored procedure you can determine the amount of disk space (data and index) used by any given table in the current database.

Example 1 (SQL Server table)

Run the following SQL statement from the SQL Server Management Studio. Replace the names in bold with your own:

USE db1
GO
EXEC sp_spaceused N’dbo.orders
GO

Results

  • name: Table name for which space usage information was requested
  • rows: Number of rows existing in the table
  • reserved: Total amount of reserved space for table data and indexes
  • data: Amount of space used by table data
  • index_size: Amount of space used by table indexes
  • unused: Total amount of space reserved for table but no yet used

Example 2 (SQL Server database)

You can also run sp_spaceused without any parameters to display information about the whole database. Replace the names in bold with your own:

USE db1
GO
EXEC sp_spaceused
GO

Results

First Recordset:

  • database_name: Name of the current database
  • database_size: Size of the current database in megabytes. database_size includes both data and log files
  • unallocated space: Space in the database that has not been reserved for database objects

Second Recordset:

  • reserved: Total amount of space allocated by objects in the database
  • data: Total amount of space used by data
  • index_size: Total amount of space used by indexes
  • unused: Total amount of space reserved for objects in the database, but not yet used

Notes

Permission to execute sp_spaceused is granted to the public role. Only members of the db_owner fixed database role can specify the @updateusage parameter.

database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When table name is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

sp_configure: Limit SQL Server memory usage

Use the sp_configure system stored procedure with the max server memory option to limit the amount of memory in the buffer pool used by an instance of SQL Server or MSDE. This will prevent SQL Server from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. You cannot set max server memory to a value less than 4 MB. 16 MB or more is recommended especially if you are using replication. max server memory is an advanced option. You need to enable advanced options before you can use it.

Run the following SQL statements to limit the amount of server memory used to 64 MB:

 

Enable advanced options:

USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

 

Set the maximum amount of memory to 64 MB:

USE master
EXEC sp_configure 'max server memory (MB)', 64
RECONFIGURE WITH OVERRIDE

 

Display the newly set configuration:

USE master
EXEC sp_configure 'max server memory (MB)'

 

Set ‘show advanced options’ back to default:

USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Advantages of SQL Server 2005 64-bit

64-bit processors are expected to dominate the markets by the end of 2007. It is clear that the industry is moving towards the 64-bit platform fast, especially for server hardware. The move towards the 64-bit platform has been driven by lower hardware prices, especially after the introduction of x64 architecture by both Intel (EM64T) and AMD (AMD64). Before x64, the only 64-bit architecture available for SQL Server was the IA-64 architecture from Intel. The IA-64 architecture is very scalable but since it is not based on the 32-bit x86 processor, it cannot run native 32-bit applications.

The following table illustrates the various processors available in the market from Intel and AMD. Note that SQL Server 2005 (64-bit) is available for both x64 and the IA-64 Itanium:

Intel AMD
x64 Xeon with EM64T Athlon 64
Pentium 4 with EM64T Opteron
IA-64 (Intel only) Itanium N/A
Itanium 2

Advantages of 64-bit computing

64-bit computing has many advantages over the 32-bit architecture. The following is a list of 64-bit advantages:

Large memory addressing: The 64-bit architecture offers a larger directly-addressable memory space. SQL Server 2005 (64-bit) is not bound by the 4 GB memory limit of 32-bit systems. Therefore, more memory is available for performing complex queries and supporting essential database operations. This greater processing capacity reduces the penalties of I/O latency by utilizing more memory than traditional 32-bit systems.

Enhanced parallelism: The 64-bit architecture provides advanced parallelism and threading. Improvements in parallel processing and bus architectures enable 64-bit platforms to support larger numbers of processors (up to 64) while providing close to linear scalability with each additional processor. With a larger number of processors, SQL Server can support more processes, applications, and users in a single system.

Same application code: You can migrate to SQL Server 2005 (64-bit) and achieve new levels of application scalability without having to change any application code. Migrating current databases to a 64-bit environment can be achieved by performing a simple database backup from the 32-bit server and restoring the backup on a 64-bit server. You can also use detach (sp_detach_db) and attach (sp_attach_db) to move a database to a 64-bit server. Microsoft invested a lot of effort to make sure that database file structures remain compatible between the 32-bit editions and the 64-bit editions of SQL Server. Applications that access 32-bit servers can connect to and access 64-bit servers without changing application code. This gives users a great deal of flexibility in determining the appropriate rate of 64-bit adoption for their organizations. It is also possible to migrate data back to the 32-bit version for SQL Server 2005 databases. However, downgrading to SQL Server 7.0 from SQL Server 2005 (64-bit) is not supported.

Reduced TCO: Total cost of ownership (TCO) can be reduced with a 64-bit system through consolidation, reduced licensing fees, and higher transaction throughput. By leveraging the tremendous scalability and memory addressability of the 64-bit architecture, SQL Server 2005 (64-bit) can host increasingly large and complex database and analysis applications, and support server consolidation on large-scale SMP systems with many processors. Applications using SQL Server 2005 (64-bit) databases can benefit from massive in-memory data caching as well as larger data structures for multiple parallel workloads, concurrent user connections, plan cache, sort space, and lock memory.

Server consolidation: The additional processor and memory headroom available within the 64-bit environment enable significant scale-up and server consolidation opportunities. A large number of consolidated databases can fit into a single SQL instance that has sufficient memory to support the data structures (connections, locks, hash, and so on) needed for multiple coexisting applications. The same benefits also apply to consolidation of multiple instances onto a single physical server.

Improved bus architecture and cache: The improved bus architecture enhances performance by moving more data between cache and processors in shorter periods. A larger on-die cache allows for faster completion of user requests and more efficient use of processor time.

Memory addressing with 64-bit vs. AWE

Inherently, A 32-bit system can manage a maximum of 4 GB of memory. This limits the addressable memory space for Windows 2000 and Windows 2003 systems to 4 GB. With 2 GB reserved for the operating system by default, only 2 GB of memory remains for SQL Server. To allow a 32-bit system to address memory beyond the 4 GB limit, a set of memory management extensions to the Microsoft Win32 API called Address Windowing Extensions (AWE) is used. Using AWE, applications can acquire physical memory as non-paged memory, and then dynamically map views of the non-paged memory to the 32-bit address space. By using AWE, SQL Server Enterprise Edition can address up to 32 GB of physical memory on Windows Server 2003 Enterprise Edition and up to 64 GB of memory on Windows Server 2003 Datacenter Edition.

Although AWE provides a way to use more memory, it imposes overhead and adds initialization time leading to weaker performance as opposed to 64-bit systems. Also, the additional memory addressability with AWE is available only to the SQL Server’s data buffers. It is not available to other memory consuming database operations such as caching query plans, sorting, indexing, joins, or for storing user connection information. It is also not available on other engines such as Analysis Services. In contrast, SQL Server 2005 (64-bit) makes memory available to all database processes and operations. Using the 64-bit version on either IA64 or x64 hardware, a SQL Server instance can address up to 1 terabyte of memory; the current maximum amount of physical memory supported by Windows Server 2003 SP1. This memory is available to all components of SQL Server, and to all operations within the database engine.

OPENROWSET: Import data into SQL Server tables

The OPENROWSET feature in SQL Server and MSDE provides a fast and easy way to open an OLE DB compatible data source, such as an Excel sheet, directly from your SQL script. Coupled with the “SELECT * INTO” command, the OPENROWSET feature can import data from an Excel sheet into a table in SQL Server or MSDE.

Run the following command. This example uses the OLE DB Provider for Microsoft Excel to access sheet1 in the Excel file c:book1.xls.

 

SELECT *
INTO db1.dbo.table1
FROM OPENROWSET(‘MSDASQL’,
‘Driver={Microsoft Excel Driver (*.xls)};DBQ=c:book1.xls‘,
‘SELECT * FROM [sheet1$]’)

 

table1 will be created in the db1 database. The content of this table will be imported from the sheet1 worksheet in your c:book1.xls Excel file.