SQL Server 2008 -- Automatic Recovery of Data Pages

SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.

SQL Server 2008 -- Enhanced Database Mirroring

SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.

SQL Server 2008 -- Auditing

Create and manage auditing via DDL, while simplifying compliance by providing more comprehensive data auditing. This enables organizations to answer common questions, such as, "What data was retrieved?"

SQL Server 2008 -- Partitioned Table Parallelism

Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.

SQL Server 2008 -- Backup Compression

Keeping disk-based backups online is expensive and time-consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.

SQL Server 2008 -- Performance Data Collection

Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.

SQL Server 2008 -- Data Compression

Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.

SQL Server 2008 -- Predictable Query Performance

Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

SQL Server 2008 -- Log Stream Compression

Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.

SQL Server 2008 -- Change Data Capture

Change Data Capture makes database auditing easier to create and maintain. While DML auditing has been available via triggers in SQL Server for a few versions, and DDL auditing has been available since SQL Server 2005, the solutions had to be custom written by a developer with reasonably intimate details of the database's underpinnings. With Change Data Capture, system stored procedures are used to mark which types of objects you want to audit, and the stored procedures take care of how the auditing occurs.

While setting up these auditing procedures is typically a one-time deal, business requirements change over time; it is usually easier to use system stored procedures to handle changes, plus it's less error prone than changing your custom auditing code. Unfortunately, when you use Change Data Capture, you will lose the auditing flexibility that you currently have with your own auditing solution.

SQL Server 2008 -- Resource Governor

SQL Server 2008 provides Resource Governor, a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use.

CREATE RESOURCE POOL bigPool; GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO

SQL Server 2008 -- 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.

--**********SPARSE Columns********** USE [tempdb] GO --Declaring SPARSE columns CREATE TABLE SparseTest (ID       int IDENTITY(1,1),  LastName varchar(50) SPARSE NULL,  Salary   decimal(9,2) NULL) GO ALTER TABLE SparseTest  ALTER COLUMN Salary decimal(9,2) SPARSE GO

MSDN：http://technet.microsoft.com/en-us/library/cc280604.aspx

SQL Server 2008 -- File Stream (4)

● Summary

●  When to use

1.       Objects that are being stored are, on average, larger than 1 MB

2.       Fast read access is important.

3.       You are developing applications that use a middle tier for application logic.

4.       For smaller objects, storing varbinary(max) BLOBs in the database often provides better streaming performance.

●  Storage

1.       When a table contains a FILESTREAM column, each row must have a unique row ID.

2.       FILESTREAM data containers cannot be nested.

3.       When you are using failover clustering, the FILESTREAM filegroups must be on shared disk resources.

4.       FILESTREAM filegroups can be on compressed volumes.

● Security

1.       FILESTREAM data is secured just like other data is secured: by granting permissions at the table or column levels.

2.       Encryption is not supported on FILESTREAM data.

3.       When the database is closed, the physical data container is fully available and subject to Windows security check.

4.       Recommend that you secure directories that contain FILESTREAM data so that the files cannot be accidentally altered or deleted.

● Management

1.       FILESTREAM is implemented as a varbinary(max) column and integrated directly into the Database Engine, most SQL Server management tools and functions work without modification for FILESTREAM data.

2.       All backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database.

3.       If you do not want to back up FILESTREAM data with relational data, you can use a partial backup to exclude FILESTREAM filegroups.

SQL Server 2008 -- File Stream (3)

●To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem

1.Objects smaller than 256K are best stored in a database.

2.Objects larger than 1M are best stored in the filesystem.

3.Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors. The notion of “storage age” or number of object overwrites as way of normalizing wall clock time. Storage age allows our results or similar such results to be applied across a number of read:write ratios and object replacement rates.

4.Reference http://research.microsoft.com/apps/pubs/default.aspx?id=64525

5.MSDN Article：FILESTREAM Storage in SQL Server 2008

6.MSDN Magazine：Programming with FileStreams in SQL Server 2008

7.RAVI S.MANIAM'S BLOG：FILESTREAM DATA TYPE : SQL SERVER 2008

SQL Server 2008 -- File Stream (2)

• SQL Server 2008 BLOB storage compare

*：FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

SQL Server 2008 -- File Stream (1)

●FILESTREAM is disabled by default in SQL Server 2008, and you must first enable it before the feature can be used.

●Two step to enable FILESTREAM

1.Enabling FILESTREAM for the Machine

2.Enabling FILESTREAM for the Server Instance

EXEC sp_configure filestream_access_level, n RECONFIGURE

Note: The value 0 disables the FILESTREAM feature completely. Setting the access level to 1 enables FILESTREAM for T-SQL access only, and setting it to 2 enables FILESTREAM for full access (which includes local or remote file I/O streaming access as enabled for the machine in the first step). To support our sample .NET applications that will demonstrate file I/O streaming access using OpenSqlFilestream, you'll need to select level 2 (full access).

SQL Server 2008 -- Filtered Indexes

1.Filtered indexes provide a way to create more targeted indexes that require less storage and can support more efficient queries.

2.Filtered indexes are particularly well suited for indexing non-NULL values of sparse columns.

--**********Filtered Indexes********** --Creating and Testing a Filtered Index --on the Production.Product Table CREATE NONCLUSTERED INDEX IX_Product_Size ON Production.Product(Size, SizeUnitMeasureCode) WHERE Size IS NOT NULL; GO SELECT ProductID, Size, SizeUnitMeasureCode FROM Production.Product WHERE Size = 'L'; GO

SQL Server 2008 -- The hierarchyid Data Type (2)

●The benefit of the hierarchyid Data Type

1.Can invoke methods to arbitrarily(任意的) insert, modify, and delete nodes at any point within the structure very efficiently

2.Can reparent(重新定義父階) entire subtrees in a single update.

3.Faster than running recursive queries on a traditional self-joining table using CTEs.

4.The hierarchyid type is implemented as a SQL common language runtime (CLR) user-defined type (UDT). This means that hierarchyid is hosted internally by the CLR and not natively by SQL Server.

SQL Server 2008 -- The hierarchyid Data Type (1)

1.Hierarchical data is not relational. It is organized as a tree structure of parent and child elements rather than as flat lists of rows in related tables.

2.The most common hierarchical data format in today's world is XML. But XML is the wrong choice when you'd prefer to keep your data stored relationally as normal rows with standard SQL Server data-typed columns and all you really want is hierarchical linking capabilities between the rows. Since your data is then encapsulated within a single XML document.

3.The self-joining table approach and CTEs recursive queries can fulfill the hierarchy structure, but it requires you to handle much of the maintenance of the tree structure yourself.

SQL Server 2008 -- Table-Valued Parameter (3)

●TVP Limitations

1.TVPs are read-only after they are initially populated and passed; they cannot be used to return data.

2.The READONLY keyword must be applied to TVPs in the signatures of your stored procedures, or they will not compile.

3.Cannot update the column values in the rows of a TVP, and you cannot insert or delete rows. If you must modify the data in a TVP, one workaround is to insert the data from the TVP into a temporary table or into a table variable to which you can then apply changes.

4.There is no ALTER TABLE...AS TYPE statement that supports changing the schema of a TVP table type. Instead, you must first drop all stored procedures that reference the type before dropping the type, re-creating it with a new schema, and then re-creating the stored procedures.

5.Indexing is limited as well, with support only for PRIMARY KEY and UNIQUE constraints. Also, statistics on TVPs are not maintained by SQL Server.

SQL Server 2008 -- Table-Valued Parameter (2)

 1.CTE and table variable VS. Table-Valued Parameter VS. Temp Table

SQL Server 2008 -- Table-Valued Parameter (1)

1.The terms table-valued parameter (TVP) and user-defined table type are used interchangeably.

2.A TVP is based on a new user-defined table type in SQL Server 2008 that describes the schema for a set of rows that can be passed to stored procedures or user-defined functions (UDFs).

--**********Table-Valued Parameters********** --CREATE User-Defined Table Types CREATE TYPE CustomerUdt AS TABLE   (Id int, CustomerName nvarchar(50), PostalCode nvarchar(50)) --DROP User-Defined Table Types DROP TYPE CustomerUdt --Using TVPs for Bulk Inserts and Updates CREATE TYPE LocationUdt AS TABLE(LocationName varchar(50), CostRate int) GO --TVP參數一定要加上READONLY關鍵字，否則會有下列錯誤訊息 --The table-valued parameter "@TVP" must be declared with the READONLY option. CREATE PROCEDURE uspInsertProductionLocation(@TVP LocationUdt READONLY) AS     INSERT INTO [Production].[Location] ([Name],         [CostRate], [Availability], [ModifiedDate])     SELECT *, 0, GETDATE() FROM @TVP GO DECLARE @LocationTVP AS LocationUdt INSERT INTO @LocationTVP SELECT [Name], 0.00 FROM [Person].[StateProvince] EXEC uspInsertProductionLocation @LocationTVP /* --DROP User-Defined Table Types --要先移除PROCEDURE，否則會有錯誤，錯誤訊息如下 --Cannot drop type 'LocationUdt' because it is being referenced     by object 'uspInsertProductionLocation'. --There may be other objects that reference this type. DROP PROCEDURE uspInsertProductionLocation DROP TYPE LocationUdt */

SQL Server 2008 -- Grouping Sets

1.Extends the capabilities of the GROUP BY clause for summarizing and analyzing the data.

2.Grouping Sets returns just the top-level rollup rows for each grouping level and does not include the actual group level summary information that was returned by our earlier versions of the query.

USE [tempdb] IF (object_id(N'tempdb..Inventory') IS NOT NULL)     DROP TABLE Inventory; CREATE TABLE Inventory(     Store varchar(2),     Item varchar(20),     Color varchar(10),     Quantity decimal); --INSERT DATA INSERT INTO Inventory VALUES('NY', 'Table', 'Blue', 124) INSERT INTO Inventory VALUES('NJ', 'Table', 'Blue', 100) INSERT INTO Inventory VALUES('NY', 'Table', 'Red', 29) INSERT INTO Inventory VALUES('NJ', 'Table', 'Red', 56) INSERT INTO Inventory VALUES('PA', 'Table', 'Red', 138) INSERT INTO Inventory VALUES('NY', 'Table', 'Green', 229) INSERT INTO Inventory VALUES('PA', 'Table', 'Green', 304) INSERT INTO Inventory VALUES('NY', 'Chair', 'Blue', 101) INSERT INTO Inventory VALUES('NJ', 'Chair', 'Blue', 22) INSERT INTO Inventory VALUES('NY', 'Chair', 'Red', 21) INSERT INTO Inventory VALUES('NJ', 'Chair', 'Red', 10) INSERT INTO Inventory VALUES('PA', 'Chair', 'Red', 136) INSERT INTO Inventory VALUES('NJ', 'Sofa', 'Green', 2) --BASIC GROUP BY SELECT Item, Color, SUM(Quantity) AS TotalQty, COUNT(Store) AS Stores FROM Inventory GROUP BY Item, Color ORDER BY Item, Color --Rolling Up by Level SELECT Item, Color, SUM(Quantity) AS TotalQty, COUNT(Store) AS Stores FROM Inventory GROUP BY Item, Color WITH ROLLUP --上面那一句也可換成下面這種表示方式 --GROUP BY ROLLUP(Item, Color) ORDER BY Item DESC, Color DESC --Rolling Up All Level Combinations SELECT Item, Color, SUM(Quantity) AS TotalQty, COUNT(Store) AS Stores FROM Inventory GROUP BY Item, Color WITH CUBE --上面那一句也可換成下面這種表示方式 --GROUP BY CUBE(Item, Color) ORDER BY Item DESC, Color DESC --Returning Just the Top Level(GROUPING SETS operator, new in SQL Server 2008) SELECT Store, Item, Color, SUM(Quantity) AS TotalQty FROM Inventory GROUP BY GROUPING SETS (Store, Item, Color) ORDER BY Store, Item, Color --Handling NULL Values INSERT INTO Inventory VALUES('NY', 'Lamp', NULL, 36) INSERT INTO Inventory VALUES('NJ', 'Lamp', NULL, 8) GO SELECT Store, Item, Color, SUM(Quantity) AS TotalQty FROM Inventory GROUP BY GROUPING SETS(Store), CUBE(Item, Color) ORDER BY Store DESC, Item DESC, Color DESC GO SELECT     CASE WHEN GROUPING(Store) = 1 THEN '(all)'     ELSE ISNULL(Store, '(n/a)') END AS Store,     CASE WHEN GROUPING(Item) = 1  THEN '(all)'     ELSE ISNULL(Item,  '(n/a)') END AS Item,     CASE WHEN GROUPING(Color) = 1 THEN '(all)'     ELSE ISNULL(Color, '(n/a)') END AS Color,     SUM(Quantity) AS TotalQty FROM Inventory GROUP BY GROUPING SETS(Store), CUBE(Item, Color) ORDER BY Store DESC, Item DESC, Color DESC GO --刪除TABLE DROP TABLE Inventory;

SQL Server 2008 -- New Date and Time Data Types

●The date, time, datetime2, and datetimeoffset types are four new data types in SQL Server 2008 for storing dates and times, which you should now begin using for new database development in lieu(替代) of the traditional datetime and smalldatetime data types.

1.Separation of Dates and Times： Date (3 Bytes) 、Time

2.More Portable Dates and Times：datetime2  can store dates from 1/1/0001 to 12/31/9999

3.Time Zone Awareness：datetimeoffset

4.New and Changed Functions

5.Date and Time Accuracy, Storage, and Format