1.Compressed Storage of 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 nonclustered index.
- A whole indexed view.
2. FILESTREAM Storage
FILESTREAM in SQL Server 2008 enables SQL Server-based applications to
store
unstructured data, such as
documents and images, on the file
system.
VARBINARY(MAX)
Standard VARBINARY(MAX) data is limited to 2 GB.
3. Sparse Columns
Sparse columns are ordinary columns that have an
optimized storage for
null values. Sparse columns reduce the space requirements for null
values at the cost of more overhead to retrieve nonnull values. Consider
using sparse columns when the space saved is at least 20 percent to 40
percent.
CREATE TABLE dbo.T1
(
keycol INT NOT NULL PRIMARY KEY,
col1 VARCHAR(20) NOT NULL,
col2 INT SPARSE NULL,
col3 CHAR(10) SPARSE NULL,
col4 NUMERIC(12, 2) SPARSE NULL
);
4. IntelliSense for Query Editing
5.Query Editor Regions
6.Filtered Indexes
SQL Server 2008 introduces filtered indexes and statistics. You
can now create a
nonclustered index based on a predicate, and only the subset
of rows for which the predicate holds true are stored in the index B-Tree.
Similarly, you can manually create statistics based on a predicate.
CREATE NONCLUSTERED INDEX idx_currate_notnull
ON Sales.SalesOrderHeader(CurrencyRateID)
WHERE CurrencyRateID IS NOT NULL;
7. DDL Trigger Enhancements
In SQL Server 2008, the type of events on which you can now
create DDL triggers is enhanced to include stored procedures that perform
DDL-like operations. This gives you more complete coverage of DDL events that
you can capture with triggers.
Many stored procedures perform DDL-like operations. Before SQL
Server 2008, you could not capture their invocation with a trigger. Now
you can capture many new events that fire as a result of calls to such
procedures. For example, the stored procedure sp_rename now fires a
trigger created on the new RENAME event.
8.Grouping Sets
SQL Server 2008 introduces several extensions to the GROUP BY
clause that enable you to define multiple groupings in the same query.
SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS
qty
FROM dbo.Orders
GROUP BY GROUPING SETS (
( custid, empid, YEAR(orderdate) ),
The following statements are equivalent:
SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))
|
SELECT customer, NULL as year, SUM(sales)
FROM T
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T
GROUP BY year
|
9. MERGE Statement
The new MERGE statement is a standard statement that combines
INSERT, UPDATE, and DELETE actions as a single atomic operation based on
conditional logic.
MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
10. Table Types
SQL Server 2008 introduces table types and table-valued
parameters that help abbreviate your code and improve its performance.
You use the CREATE TYPE statement to create a new table type. For example, the following code defines a table type called OrderIDs in database:
CREATE TYPE dbo.OrderIDs AS TABLE ( pos INT NOT NULL PRIMARY KEY,
orderid INT NOT NULL UNIQUE );
DECLARE @T AS dbo.OrderIDs;
INSERT INTO @T(pos, orderid) VALUES(1, 51480),(2, 51973),(3, 51819);
SELECT pos, orderid FROM @T ORDER BY pos;
11. Large UDTs
In SQL Server 2005, user-defined types (UDTs) in the CLR were limited to 8,000 bytes. SQL Server 2008 lifts this limitation and now supports large UDTs. Similar to the built-in large object types that SQL Server supports, large UDTs can now reach up to 2 GB in size.
12. Date and Time Data Types
Before SQL Server 2008, date and time improvements were probably
at the top of the list of the most requested improvements for SQL Server—especially
the request for separate date and time data types, but also for general
enhanced support for temporal data. SQL Server 2008 introduces four new date
and time data types—including DATE, TIME, DATETIME2, and DATETIMEOFFSET—as well
as new functions that operate on the new types and enhancements to existing
functions.
13. CONVERT Function
The CONVERT function is enhanced to allow conversions between binary and character hexadecimal values.
14. Spatial: Two new spatial data types have been added--
GEOMETRY and GEOGRAPHY--which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.
15. HIERARCHYID
While hierarchical tree structures are commonly used in many applications, SQL Server has not made it easy to represent and store them in relational tables. In SQL Server 2008, the HIERARCHYID data type has been added to help resolve this problem. It is designed to store values that represent the position of nodes of a hierarchal tree structure.
For example, the HIERARCHYID data type makes it easier to express these types of relationships without requiring multiple parent/child tables and complex joins.
- Organizational structures
- A set of tasks that make up a larger projects (like a GANTT chart)
- File systems (folders and their sub-folders)
- A classification of language terms
- A bill of materials to assemble or build a product
- A graphical representation of links between web pages
16. Roll UP
It will do get the sum of group by columns in another row.
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM tblPopulation
GROUP BY Country,[State],City WITH ROLLUP
