SQL Server 2008 is a noteworthy product release that offers many new cool features and key improvements over its past , and making it the most robust and comprehensive release of SQL Server to date.
Herewith I am listing out some of the key features would be helpful for the core deevlopers and for the people who are all expecting interview calls and want to brush up the things fast. Don’t expect to be so elaborate
1. Inline variable assignment
Instead of:
DECLARE @myVar int
SET @myVar = 5
you can do it in one line:
DECLARE @myVar int = 5
2. Like C# math syntax hereafter in our SQL code we can use like : SET @i += 5
3. SQL Server 2008 introduces automatic auditing
4. Offers Row-level and Page-level compression - Mostly takes place on the metadata. For instance, page compression will store common data for affected rows in a single place.
5. Filtered Indexes.
6. Introduced Table Value Parameters (TVP).
CREATE TYPE KhanType AS TABLE (Name varchar(20), Age int)
DECLARE @myKhan KhanType
INSERT @myKhan SELECT 'Khan, 35
INSERT @myKhan SELECT 'Thameem, 31
INSERT @myKhan SELECT 'gani’, 27
INSERT @myKhan SELECT 'Gulam, 42
exec sp_MySproc2 @myKhan
And the sproc would look like this:
CREATE PROCEDURE sp_MySproc2(@myKhan KhanType READONLY) ...
The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc.
7. Full support for Intellisense in the SQL Server Management Studio (SSMS).
8. Transparent Data Encryption -SQL Server 2008 enables the encryption of entire databases, data files, and log files, without the need for application changes. Some of the benefits of transparent data encryption include securing data from unauthorized users and encrypting backups
9. External Key Management -SQL Server 2008 provides a comprehensive solution for encryption and key management
10. FILESTREAM Data - The new SQL Server 2008 FILESTREAM data type allows large binary data like documents and images to be stored directly in an NTFS file system; the document or image remains an integral part of the database and maintains transactional consistency. FILESTREAM enables the storage of large binary data, traditionally managed by the database, to be stored outside the database as individual files that can be accessed using an NTFS streaming API. Using NTFS streaming APIs allows efficient performance of common file operations while providing all of the rich database services, including security and backup.
11. Geographical Information - SQL Server 2008 provides comprehensive spatial support to consume, extend, and use location information in spatially enabled applications.
• GEOGRAPHY data type
This feature enables you to store planar spatial data that conforms to industry spatial standards like Open Geospatial Consortium (OGC). This enables developers to implement “Flat Earth” solutions by storing polygons, points, and lines that are associated with projected planar surfaces, as well as naturally planar data such as interior spaces.
• GEOMETRY data type
This feature enables you to store geodetic spatial data and perform operations on it. Use latitude and longitude coordinates to define areas on the earth’s surface and associate geographical data with industry-standard ellipsoid such as WGS84, which is used in GPS solutions worldwide.
12. HIERARCHY ID - SQL Server 2008 enables database applications to model tree structures in a more efficient way than previously possible. HierarchyId is a new system type that can store values that represent nodes in a hierarchy tree. This new type features a flexible programming model. It is implemented as a CLR user-defined type (UDT) that exposes several efficient and useful built-in methods for creating and operating on hierarchy nodes.
13. Integrated Full-Text Search - Integrated Full-Text Search makes the transition between Full-Text Search and relational data seamless, while enabling the use of full-text indexes to perform high-speed text searches on large text columns.
14. Sparse Columns - This feature provides a highly efficient way of managing empty data in a database by enabling NULL data to consume no physical space. For example, sparse columns allow object models that typically contain numerous null values to be stored in a SQL Server 2008 database without experiencing large space costs. Sparse Columns also enable administrators to create tables with more than 1,024 columns.
15. Large User-Defined Types - SQL Server 2008 eliminates the 8,000-byte limit for user-defined types, enabling users to dramatically expand the size of their UDTs.
16. 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.
17. Allow multiple value inserts within a single INSERT statement. Say
INSERT INTO dbo.Departments
VALUES (1, 'Human Resources', 'Maryam'),(2, 'Sales', 'Biju'),
(3, 'Finance', 'Gilly'),(4, 'Purchasing', 'Barbera'),
(5, 'Manufacturing', 'Screwer');
18. MERGE 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.
|
| Author: AssanKhan Ismail 24 Oct 2008 | Member Level: Bronze Points : 0 |
fine one
|