What's new in SQL Server 2008 - Some More

Filestream Storage

As the name may suggest, Filestream enables the storage of unstructured data such as documents and videos.  This feature integrates with the NTFS files system by using the nvarchar(max) data type to store data on the file system.  This feature is great for when you’re currently storing documents inside of your SQL Server system that are larger than 1 MB and fast read access is important.

Sparse Columns

Sparse columns are very similar to other types of database columns, except that they are specialized and optimized for null values.  If you have a table that holds a lot of NULL values, first consider your table design.  If your design is solid or cannot be altered, then your table may be a candidate to take advantage of sparse columns.  The rule from Microsoft is to consider using Sparse columns when 20-40 percent of the values in a column will be NULL.

New Date & Time Data Types

Prior to SQL Server 2008, there was no way to native data type to store time related data.  Time data was only available as part of the datetime (or smalldatetime) data types.  With SQL Server 2008, TIME is a separate data type, able to hold time values such as ‘23:59:59.9999999′.  Along with the new TIME data type, there are additional data types such as datetime2, which holds additional nanosecond data and the datetimeoffset data type, which can hold datetime data that is timezone aware.

Spatial Data Types

Two new data types are included in SQL Server 2008; geometry and geography.  These data types hold information regarding the physical location and shape of geometric objects, which allow for applications to be built that are geographically aware.

Dependency Reporting

Reporting dependencies has never been consistent or reliable in SQL Server.  The difficultly is that when objects are added, modified or dropped, special actions must be taken by the database engine to ensure that the dependency chain is correct.  In 2008, new dynamic management views are included keep track of these dependencies so that reliable reporting can be done one these objects.

Advanced Auditing Features

SQL Server 2008 includes several new features that facilitate the auditing of data.  These features include a new Auditing feature that allows you to easily create customized audits of database engine events.  Change Data Capture, while not auditing specific, makes data changes typically used for loading a data warehouse easily available in an easily used format.  SQL Server 2008 also includes a Data Collector feature that allows the DBA to gather and compare data that is gathered from several different sources.

Policy Based Administration

This is one of my favorite features of SQL Server 2008.  This feature allows a database administrator (DBA) to easily and uniformly set policies across servers that ensure system rules are met.  These policies can be rules such as simplifying administration tasks, preventing unauthorized system changes, making sure code compliance is met, ensuring best practices are met.

TSQL Intelli-sense

SQL Server Management Studio 2008 includes Intelli-sense which is a familiar feature in other Visual Studio products.  This feature presents a popup box when typing SQL statements to give options to choose columns from tables that are involved in your queries.

Central Management Servers

This feature allows the database administrator (DBA) to administer multiple SQL Servers easily and efficiently.  These servers allow for multi-server queries and for policy based management policies to be executed against multiple servers at the same time.

Compound Operators

This form of syntactic sugar is present in many other programming languages such as C++ or C#.  Consider the following TSQL statement.

SET @x = @x + 1

Using compound operators, the above statement can be rewritten in the following manner:

SET @x += 1

This statement, while not groundbreaking, takes TSQL a step forward in the evolution of programming languages.

Transparent Encryption

As data becomes more and more sensitive, the ability to keep the data secret becomes more important.  SQL Server 2008 includes the ability to encrypt data stored in the database transparently, which keeps you from having to alter applications to take advantage of the feature.  This feature prevents unauthorized users from reviewing the data even if they have the backup files.

Comments

Post a Comment

Popular posts from this blog

Google to FTS Syntax Cheat Sheet

Image
OPERATOR EXAMPLE DESCRIPTION   nut Searches for inflectional forms of the word nut crank arm crank AND arm Searches for documents containing inflectional forms of the words crank AND arm crank and ann. The keyword AND is optional. tire OR air Searches for documents containing inflectional forms of the words tire or air, “reflector bracket” Performs a phrase search for the phrase "reflector bracket". hardware -bracket Searches for documents containing inflectional forms of the word hardware but not the word bracket. +clamp Searches for the word darn') without generating inflectional forms. ~seat Searches for thesaurus forms of the word seat Assemb* Searches for words that begin with the prefix assemb . <washer nut> Searches for documents that contain the words washer in close proximity to the word nut
Read more

@@rowcount

@@rowcount is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error you need to save it in a local variable if you want to use the value later, since @@rowcount is set after each statement. Since with SET you can only assign variable at a time, you must use SELECT if you need to save both @@error and @@rowcount into local variables: SELECT @err = @@error, @rowc = @@rowcount (For this reason, I prefer to always use SELECT for variable assignment, despite Microsoft's recommendations to use SET.) In T-SQL it is not an error if, for instance, an UPDATE statement did not affect any rows. But it can of course indicate an error in your application, as it could be an error if a SELECT returns more that one row. For these situations, you can check @@rowcount and raise an error and set a return value, if @@rowcount is not the expected value.
Read more

Sql Index

Image
Indexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
Read more