1. Intelligence - It has been previously possible in SQL Server
2000 and 2005 with use of 3rd party add-ins like SQL Prompt. But now in SQL 2008 / 2008 R2 Built-in
intelligence is available. IntelliSense is the general term for a number of
features: List Members, Parameter Info, Quick Info, and Complete Word.
2. 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.
3. Inline variable assignment - Microsoft SQL Server 2008 enables you to
initialize variables inline as part of the variable declaration statement
instead of using separate DECLARE and SET statements. [DECLARE @myVar int = 5]
4. Compound Assignment Operators - You
can use compound operators (+=, -=, *=, /=, %=) wherever assignment is normally
allowed – e.g., in the SET clause of an UPDATE statement or in a SET statement
that assigns values to variables. [SET @i += 5]
5. 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. Besides
being performed as an atomic operation, the MERGE statement is more efficient
than applying those actions individually.
6. Sparse Columns -
Sparse columns are columns that are optimized for the storage of NULLs. To
define a column as sparse, specify the SPARSE attribute as part of the column
definition. Sparse columns consume no storage for NULLs, even with fixed-length
types; however, when a column is marked as sparse, storage of non-NULL values
becomes more expensive than usual. Therefore, you should define a column as
sparse only when it will store a large percentage of NULLs.
7. Enhanced CONVERT Function - The
CONVERT function is enhanced in SQL Server 2008 to support new conversion
options between character strings and binary data types. You determine the
conversion option to use by specifying a style number as the third argument.
Style 0 is the default behavior that was supported in previous SQL Server
versions—this style translates character strings to the binary representation
of the ASCII codes for the characters and vice versa.
8. New Date and Time Data Types:
Before SQL Server 2008, date and time improvements were top of the list of the
most requested improvements for SQL Server—especially the request for separate
date and time data types. SQL Server 2008 introduces four new date and time
data types—including DATE, TIME, DATETIME2, and DATETIMEOFFSET.
9. New and Enhanced Functions - To support the new date and
time data types, SQL Server 2008 introduce new functions: SYSDATETIME,
SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and TODATETIMEOFFSET.
10. HIERARCHYID Data Type - The
new HIERARCHYID data type in SQL Server can be useful for storing and
manipulating hierarchies. This type is internally stored as a VARBINARY value
that represents the position of the current node in the hierarchy (both in
terms of parent-child position and position among siblings). You can perform
manipulations on the type by using either Transact-SQL or client APIs to invoke
methods exposed by the type.
11. Spatial Data Type - Two types of
spatial data are defined for SQL Server 2008: geometry and geography. Geometry data is based on Euclidian
geometry and is used to store points, lines, curves, and polygons.
12. FILESTREAM - FILESTREAM is a new
feature in the SQL Server 2008 release. It allows structured data to be stored
in the database and associated unstructured (i.e., BLOB) data to be stored
directly in the NTFS file system. You can then access the BLOB data through the
high-performance Win32® streaming APIs, rather than having to pay the
performance penalty of accessing BLOB data through SQL Server.
13. New Grouping Sets - SQL
Server 2008 introduces several extensions to the GROUP BY clauses that enable
you to define multiple groupings in the same query. These extensions are: the
GROUPING SETS, CUBE, and ROLLUP sub-clauses of the GROUP BY clause and the
GROUPING_ID function.
14. Output Clause -
Returns information from, or expressions based on, each row affected by an
INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to
the processing application for use in such things as confirmation messages,
archiving, and other such application requirements. The results can also be
inserted into a table or table variable.
15. Table Types Parameters -
Table types allow easy reuse of table definition by table variables. Table
types enable you to save a table definition in the database and use it later to
define table variables and parameters to stored procedures and functions.
Because table types let you reuse a table definition, they ensure consistency &
reduce chances for errors.
16. Table-Valued Parameters -
Table-valued parameters enable you to pass a parameter of a table type to
stored procedures and functions. In SQL Server 2008, you simply pass the stored
procedure a table-valued parameter. There is no risk of SQL Injection, and
there is opportunity for efficient reuse of execution plans.
17. 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.
18. LINQ -
Microsoft SQL Server 2008 LINQ is an exciting evolution in data programming
that enables developers to build queries over data directly from within any
.NET-based programming language. LINQ to Entities, LINQ to Dataset, LINQ to
SQL, and LINQ to XML are key implementations of this powerful technology.
19. Large UDT – Last
in my list, 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. If the UDT value does
not exceed 8,000 bytes, the database system treats it as an inline value as in
SQL Server 2005. If it exceeds 8,000 bytes, the system treats it as a large
object and report its size as “unlimited.”
20. Processing
of delimited strings - This is awesome currently stored proc needs to parse the string
(Comma separated value in single string) into a usable form. In 2008, Microsoft
introduced Table Value Parameters (TVP). The advantage here is that you can
treat Table Type as a regular table, use it in joins. Say goodbye to all those string parsing
routines.
Note:
All the above points (in RED color) are directly related to the database
developers.
21. Plan freezing: SQL
Server 2008 enables greater query performance stability and predictability by
providing new functionality to lock down query plans, enabling organizations to
promote stable query plans across hardware server replacements, server
upgrades, and production deployments.
22. Plug-in model for SSMS – SSMS 2005 also had a plug-in model, but it was
not published, so few developers that braved that environment were flying
blind. With 2008, plug-in model will be published and thousand add-ins will
bloom.
23. Object
Dependencies - SQL Server 2008 delivers several objects that provide reliable
discovery of object dependencies, replacing the unreliable older sys.sql_dependencies view and the sp_depends stored procedure. The new
objects provide information about dependencies that appear in static code,
including both schema-bound and non-schema-bound objects as well as
cross-database and even cross-server dependencies.
24. Resource governor - Allows the DBA to specify how much resource (e.g. CPU/RAM) each user
is entitled to. At the very least, it'll
prevent people, with less SQL knowledge from shooting off a query with a
Cartesian product and bringing down the box.
25. Auditing - Change
data capture is a new mechanism in SQL Server 2008 that enables you to easily
track data changes in a table. The changes are read by a capture process from
the transaction log and recorded in change tables. Those change tables mirror
the columns of the source table and also contain metadata information that can
be used to deduce the changes that took place.
26. Compression - Modern
organizations have to manage ever-increasing amounts of data, which drives up
storage costs and can lead to poorly performing applications. Use the built-in
Data Compression and Backup Compression features of Microsoft® SQL Server® 2008
R2 to reduce data storage costs and help to ensure optimal performance for your
mission-critical applications.
27. Change
Data Capture - With Change Data Capture, changes are captured and placed in
change tables. It captures complete content of changes, maintains cross-table
consistency, and even works across schema changes. This enables organizations
to integrate the latest information into the data warehouse.
28. Integrated
Full Text Search - Integrated Full Text Search makes the transition between Text
Search and relational data seamless, while enabling users to use the Text
Indexes to perform high-speed text searches on large text columns.
29.
Transparent
Data Encryption - Enable encryption of an entire database, data files, or log
files, without the need for application changes. Benefits of this include:
Search encrypted data using range and
fuzzy searches, search secure data from unauthorized users, and data
encryption without any required changes in existing applications.
30. Policy-Based
Management: Policy-Based Management is a policy-based system for managing
one or more instances of SQL Server 2008. Use this with SQL Server Management
Studio to create policies that manage entities on the server, such as the
instance of SQL Server, databases, and other SQL Server objects.
No comments:
Post a Comment