SQL Server 2008 can have up to 2 billion tables per database. A table that has a defined column set, can have up to 30,000 columns with a maximum of 1024 non-sparse + computed columns. Tables that do not have column sets are limited to 1024 columns. The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. This restriction is relaxed for tables with varchar, nvarchar, varbinary, or sql_variant columns that cause the total defined table width to exceed 8,060 bytes. The lengths of each one of these columns must still fall within the limit of 8,000 bytes, but their combined widths may exceed the 8,060 byte limit in a table.

Creating compressed tables and indexes.

SQL Server 2008 supports both row and page compression for both tables and indexes. Data compression can be configured for the following database objects:

• A whole table that is stored as a heap.
• A whole table that is stored as a clustered index.
• A whole non clustered index.
• A whole indexed view.
• For partitioned tables and indexes, the compression option can be configured for each partition, and the various partitions of an object do not have to have the same compression setting.

The compression setting of a table is not automatically applied to its non clustered indexes. Each index must be set individually. Compression is not available for system tables. Tables and indexes can be compressed when they are created by using the DATA_COMPRESSION  value set to None / Row / Page in  CREATE TABLE and CREATE INDEX statements.

Example:

Create a table with data compression

CREATE TABLE T1 (c1 int, c2 nvarchar(200) ) WITH (DATA_COMPRESSION = ROW);

Sparse Column

SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).

Disadvantages of SPARSE column are:

SPARSE column does not have IDENTITY or ROWGUIDCOL property. SPARSE column cannot be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes. SPARSE column cannot have default value or rule or computed column. Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column cannot be part of clustered index key. Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes. A table operation which involves SPARSE column takes performance hit over regular column.

Create a table that has sparse column

CREATE TABLE T1 (c1 int PRIMARY KEY,C2 varchar(50) SPARSE NULL ) ;

Creating a table that has a FILESTREAM column ( new in 2008)

CREATE TABLE dbo.EmployeePhoto

    (

    EmployeeId int NOT NULL PRIMARY KEY,

    ,Photo varbinary(max) FILESTREAM NULL

    ,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL

        UNIQUE DEFAULT NEWID()

    )

Creating a table with an xml column typed to an XML schema collection

CREATE TABLE HumanResources.EmployeeResumes

   (LName nvarchar(25), FName nvarchar(25),

    Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );

Table Partitions

When a database table grows in size to the hundreds of gigabytes or more, it can become more difficult to load new data, remove old data, and maintain indexes. Just the sheer size of the table causes such operations to take much longer. Even the data that must be loaded or removed can be very sizable, making INSERT and DELETE operations on the table impractical. The Microsoft SQL Server 2008 database software provides table partitioning to make such operations more manageable.

Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table. In addition, the SQL Server optimizer can direct properly filtered queries to appropriate partitions rather than the entire table.

Creating a partitioned table

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES (1, 100, 1000) ;

GO

CREATE PARTITION SCHEME myRangePS1

AS PARTITION myRangePF1

TO (test1fg, test2fg, test3fg, test4fg) ;

GO

CREATE TABLE PartitionTable (col1 int, col2 char(10))

ON myRangePS1 (col1) ;

GO

DataTypes

Date and Time Data Types

SQL Server 2008 introduces four new date and time data types. These types enable applications to have separate types for date or time, increased year range, increased fractional second precision and time-zone offset support. For more information, see Using Date and Time Data.

hierarchyid Data Type

SQL Server 2008 introduces a new system-provided data type to encapsulate hierarchical relationships. Use hierarchyid as a data type to create tables with a hierarchical structure or to reference the hierarchical structure of data in another location. Use hierarchical methods to query and perform work with hierarchical data by using Transact-SQL.Examples where the hierarchyid type makes it easier to store and query hierarchical data include the following:

An organizational structure, A file system, A set of tasks in a project, A taxonomy of language terms, A graph of links between Web pages.

The related SqlHierarchyId CLR data type is available for client applications. For more information, see Using hierarchyid Data Types (Database Engine).

CREATE TABLE Organization

   (

    EmployeeID hierarchyid,

    OrgLevel as EmployeeID.GetLevel(),

    EmployeeName nvarchar(50) NOT NULL

   ) ;

GO

Spatial Data Types

SQL Server 2008 introduces two spatial data types: geometry and geography. The geometry data type supports planar, or Euclidean (flat-earth), data. The geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. These new data types support the storage and manipulation of spatial data objects such as linestrings, points, and polygons. For more information, see Working with Spatial Data (Database Engine).

User-Defined Table Type

The Database Engine introduces a new user-defined table type that supports representing table structures for use as parameters in stored procedures and functions, or in a batch or the body of a stored procedure or function. You can create unique constraints and primary keys on user-defined table types. To create a user-defined table type, use the CREATE TYPE statement.

CREATE TYPE LocationTableType AS TABLE

    ( LocationName VARCHAR(50)

    , CostRate INT )

GO

User Defined Types (UDT)

The maximum size of a UDT has been increased to 2147483647 bytes.

Full Text Search

SQL Server 2008 Enterprise full-text search introduces a new full-text architecture. Full-text search is now a fully integrated feature of a database. As a result, full-text search has been significantly enhanced in the following areas

• Full-text catalogs are integrated into the database, rather than being stored in the file system, so moving a database now automatically moves the full-text catalogs.
• SQL Server 2005 noise words have been replaced by stopwords. Configuration of stopwords is managed by a new set of stoplist objects. These objects are associated with the database and moved around with it. This maintains the stopword configuration intact during manageability operations such as backup and restore, detach and attach, and copying a database with the Copy Database Wizard.
• Query and indexing performance have improved dramatically in some specific scenarios as a result of integration with other important query components such as the query optimizer.

Transact SQL

Compatibility Level

Sets certain database behaviors to be compatible with the specified version of SQL Server. New in SQL Server 2008.

ALTER DATABASE database_name

SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

COMPATIBILITY_LEVEL { 80 | 90 | 100 }

Is the version of SQL Server with which the database is to be made compatible. The value must be one of the following:

80 = SQL Server 2000

90 = SQL Server 2005

100 = SQL Server 2008

Compound Operators

Operators that perform an operation and set a variable to the result, for example SET @x += 2, are available.

CONVERT Function

The CONVERT function is enhanced to allow conversions between binary and character hexadecimal values.

Syntax for CAST:

CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

data_type is the target data type. This includes xml, bigint, and sql_variant

Date and Time Functionality

SQL Server 2008 includes support for the ISO week-date system.

Syntax : DATEPART (datepart , date ).

Datepart is part of a date( like year, quarter, day, week , weekday etc). return type is an integer.

GROUPING SETS

The GROUPING SETS, ROLLUP, and CUBE operators are added to the GROUP BY clause. There is a new function, GROUPING_ID(), that returns more grouping-level information than the existing GROUPING() function. The non-ISO compliant WITH ROLLUP, WITH CUBE, and ALL syntax is deprecated. Rollup and Cube operators are normally used in OLAP. They generate reports with totals and subtotals. CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.  (These are available from SQL 2005).

MERGE Statement

This new Transact-SQL statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table. The syntax allows you to join a data source with a target table or view, and then perform multiple actions based on the results of that join.

SQL Dependency Reporting

SQL Server 2008 introduces a new catalog view and system functions to provide consistent and reliable SQL dependency reporting. You can use sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, and sys.dm_sql_referenced_entities to report on cross-server, cross-database, and database SQL dependencies for both schema-bound and non-schema-bound objects.

Transact-SQL Row Constructors

Transact-SQL is enhanced to allow multiple value inserts within a single INSERT statement.

Example: INSERT INTO Production.UnitMeasure

VALUES (N’FT2′, N’Square Feet ‘, ’20080923′), (N’Y', N’Yards’, ’20080923′), (N’Y3′, N’Cubic Yards’, ’20080923′);