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.

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

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.

BULK INSERT Data into a SQL Server Table from a CSV file

You want to import your sales orders from a comma delimited file into a SQL Server or MSDE table called orders. The file looks like this:

345,John Doe, 400
346,Mark Smith,450
347,Robert,400

 

Run the following SQL command from the SQL window in Teratrax Database Manager. Replace lower case text with your own:

BULK INSERT [orders]
FROM ‘c:orders.csv
WITH (FIELDTERMINATOR = ‘,’)

 

Result (order table):

OrderID Name Price
345 John Doe 400
346 Mark Smith 450
347 Robert 400

 

 

 

 

Make sure the field terminator (‘,’ in this example) is not part of any actual value. The field Name for instance cannot be ‘Doe, John’ since the ‘,’ would be considered as a field terminator.

The table has to be similar in format to the file (same number of fields and same data type.)

Download MSDE 2000

Unlike SQL Server, MSDE did not ship with its own Enterprise Manager tool. Developers and users of MSDE applications lack the tools necessary for database design, management, and administration.

Downloading MSDE

Download MSDE 2000 from Microsoft

 

Database size

MSDE databases are limited to 2 gigabytes (GB) of data. This limit is per database, and not per server, so a single MSDE server can support multiple MSDE databases, each containing up to the 2 GB limit. If you anticipate that your database is currently or will grow beyond 2 GB, consider upgrading to Microsoft SQL Server™ 2000 Standard Edition for a more scalable database platform on which to build.

Multiple users

MSDE is tuned for desktop and shared solutions where there are fewer than five concurrent workloads hitting the database at any one time (only five concurrent batch workloads or 25 concurrent connections for websites.)  If your solution needs to support more than this number of concurrent batches, you may need to migrate to SQL Server or SQL Server Enterprise editions for optimal performance at this higher level of scalability.

MSDE Replication

MSDE can participate in most types of replication with either a centralized SQL Server or another MSDE. Specifically, MSDE can participate fully in snapshot and merge replication, but only as a subscriber in transactional replication (MSDE cannot be a publisher in transactional replication.) Also note that a per-set Client-Access License (CAL), or processor license is required if MSDE is participating in any type of interaction with a SQL Server, including replication. A CAL or processor license is not required if MSDE is replicating with another MSDE.

MSDE Benefits

MSDE 2000 is a redistributable version of SQL Server 2000. It is a database engine provided by Microsoft that is based on the core SQL Server technology and supports single- and dual-processor desktop computers. In other words, MSDE 2000 is a scaled down copy of SQL Server. MSDE 2000 was introduced to provide application developers a database engine that is more powerful than the Jet engine and at the same time expandable to SQL Server. It is ideal for client applications requiring an embedded database and websites serving up to 25 concurrent users.

The common technology base shared between SQL Server and MSDE 2000 enables developers to build applications that can scale seamlessly from desktop solutions to multiprocessor enterprise clusters.

As an alternative to the Jet engine, MSDE 2000 provides a cost-effective option for developers who need a database server that can be easily distributed and installed. Because it is fully compatible with SQL Server, developers can easily target both SQL Server and MSDE database objects with the same application code base. This provides a seamless upgrade path for MSDE database instances to SQL Server if an application grows beyond the storage and scalability that a MSDE can provide.

Multi-server, multi-transaction operations

Multiple-transaction processes across servers cannot include any machines running MSDE. These advanced operations cannot involve any MSDE databases. MSDE databases cannot request services from a SQL Server without a per seat SQL Server CAL (Client Access License) or processor license.

MSDE and under Windows 95, 98 and Me

MSDE cannot use integrated security features of Microsoft Windows NT® when operating on a Microsoft Windows® 95, Windows 98 or Windows Me platform. Also, MSDE cannot be started remotely, and some third-party network protocols are not supported.

Compare SQL Server 2005 Editions

With the release of SQL Server 2005, Microsoft has introduced the Express edition and the Workgroup edition to better meet the needs of each customer. The new line of SQL Server 2005 editions includes Express, Workgroup, Standard, and Enterprise Edition. The four new editions will offer a range of features including high availability, scalability, and business intelligence tools. The following table lists the various features in each edition:

Express Workgroup Standard Enterprise
Number of CPUs 1 2 4 No Limit
RAM 1 GB 3 GB No Limit No Limit
Database Size 4 GB No Limit No Limit No Limit
Merge Replication
Transactional Replication
Report Server
Management Studio
Report Builder
Backup Log-shipping
Full Text Search
SQL Agent Job Scheduling
Database Mirroring
Failover Clustering
Notification Services
Integration Services
Web Services (HTTP Endpoints)
Analysis Services
Data Mining
Database Tuning Advisor
Advanced Performance Tuning

 

Read more about SQL Server 2005 editions and features…

 

Microsoft SQL Server Cursors

SQL Server cursors are database objects used to manipulate data in a set on a row-by-row basis. You can fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language. Before you can use a cursor, you need to declare it.

DECLARE CURSOR

The DECLARE CURSOR command defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.

SQL-92 Syntax

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,…n ] ] } ]

Transact-SQL extended syntax

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,…n ] ] ]

SQL-92 arguments

cursor_name is the name of the Transact-SQL server cursor defined. cursor_name must conform to the rules for identifiers.

INSENSITIVE defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. When SQL-92 syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.

SCROLL specifies that all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available. If SCROLL is not specified in an SQL-92 DECLARE CURSOR, NEXT is the only fetch option supported. SCROLL cannot be specified if FAST_FORWARD is also specified.

select_statement is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.

Microsoft® SQL Server™ implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type.

READ ONLY prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

UPDATE [OF column_name [,…n]] defines updatable columns within the cursor. If OF column_name [,…n] is specified, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated.

Transact-SQL extended arguments

cursor_name is the name of the Transact-SQL server cursor defined. cursor_name must conform to the rules for identifiers.

LOCAL specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

GLOBAL specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.

Note If neither GLOBAL or LOCAL is specified, the default is controlled by the setting of the default to local cursor database option. In SQL Server version 7.0, this option defaults to FALSE to match earlier versions of SQL Server, in which all cursors were global. The default of this option may change in future versions of SQL Server.

FORWARD_ONLY specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.

STATIC defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

KEYSET specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.

DYNAMIC defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.

FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified the other cannot be specified.

READ_ONLY prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.

SCROLL_LOCKS specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft® SQL Server™ locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.

OPTIMISTIC specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

TYPE_WARNING specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another.

select_statement is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within select_statement of a cursor declaration.

SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type.

FOR UPDATE [OF column_name [,…n]] defines updatable columns within the cursor. If OF column_name [,…n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

Remarks

DECLARE CURSOR defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. The OPEN statement populates the result set, and FETCH returns a row from the result set. The CLOSE statement releases the current result set associated with the cursor. The DEALLOCATE statement releases the resources used by the cursor.

The first form of the DECLARE CURSOR statement uses the SQL-92 syntax for declaring cursor behaviors. The second form of DECLARE CURSOR uses Transact-SQL extensions that allow you to define cursors using the same cursor types used in the database API cursor functions of ODBC, ADO, and DB-Library.

You cannot mix the two forms. If you specify the SCROLL or INSENSITIVE keywords before the CURSOR keyword, you cannot use any keywords between the CURSOR and FOR select_statement keywords. If you specify any keywords between the CURSOR and FOR select_statement keywords, you cannot specify SCROLL or INSENSITIVE before the CURSOR keyword.

If a DECLARE CURSOR using Transact-SQL syntax does not specify READ_ONLY, OPTIMISTIC, or SCROLL_LOCKS, the default is as follows:

If the SELECT statement does not support updates (insufficient permissions, accessing remote tables that do not support updates, and so on), the cursor is READ_ONLY.

STATIC and FAST_FORWARD cursors default to READ_ONLY.

DYNAMIC and KEYSET cursors default to OPTIMISTIC.

Cursor names can be referenced only by other Transact-SQL statements. They cannot be referenced by database API functions. For example, after declaring a cursor, the cursor name cannot be referenced from OLE DB, ODBC, ADO, or DB-Library functions or methods. The cursor rows cannot be fetched using the fetch functions or methods of the APIs; the rows can be fetched only by Transact-SQL FETCH statements.

After a cursor has been declared, these system stored procedures can be used to determine the characteristics of the cursor.

System stored procedure description

sp_cursor_list: Returns a list of cursors currently visible on the connection and their attributes.
sp_describe_cursor: Describes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.
sp_describe_cursor_columns: Describes the attributes of the columns in the cursor result set.
sp_describe_cursor_tables: Describes the base tables accessed by the cursor.

Variables may be used as part of the select_statement that declares a cursor. Cursor variable values do not change after a cursor is declared. In SQL Server version 6.5 and earlier, variable values are refreshed every time a cursor is reopened.

Permissions

DECLARE CURSOR permissions default to any user that has SELECT permissions on the views, tables, and columns used in the cursor.

 

 

Sources include the Microsoft website