Row Level and Page Level Data Compression in Microsoft SQL Server 2008

Data compression is a feature which is used to reduce disk storage space and increase the performance of the query by reducing the I/O operations.

SQL Server 2008 supports two types of compression :

Row-level compression 

Page-level compression.

A row-level and page-level compression takes place on the metadata. Page level compression results in persisting certain common data that affects rows in a single location. 

The compression takes place into number of bits. For example, the length of ‘varchar’ will be stored in 3 bits.

Automatic Auditing in Microsoft SQL Server 2008

Automatic auditing is a true auditing solution for enterprise customers. STL Trace can be used for satisfying several auditing needs. SQL Server Auditing feature offers a number of advantages that attracts and help DBAs with much more easily achievable goals. These include meeting regulatory compliance requirements. These are :

1. Provision for centralized storage of audit logs and integration with system center.
2. Better performance that is noticeable.
3. Permits free-grained auditing in which an audit can be targeted for some specific actions by a principle against a particular object.
4. Allows the objects of audit to be treated as first class database objects, which mean Transact-SQL DDL statements can create these objects.
5. The database object is controlled by database engine’s permission model and enforcement control.
6. A new level permission is featured in SQL Audit – ALTER ANY SERVER AUDIT- which allows a principle to CREATE, ALTER and DROP an Audit Specification object.
7. A database level permission – ALTER ANY DATABASE AUDIT – is introduced to allow a principle to CREATE, ALTER and DROP a Database specification object.

Compound Operators in Microsoft Sql Server 2008

Compound operators feature is enhanced in SQL Server 2008. They are like compound operators in languages like C, C++.

Compound operators are a combination of operator with another operator.

The compound assignment operators are : 

arithmetic operators - +=,-=,*=, /=, %=, and
bitwise operators -&=, ^=,|=

For example

Declare @number int
Set @number = 10
--using Compound assignment operator
Set @number+=200
Select @number as MyResult
Go

Inline variable Assignment Process in Microsoft Sql Server 2008

Inline variable assignment in SQL Server 2008 enables to – declare, and assign the variables in a ‘single line’.

For example :

In Traditional Way :

DECLARE @myVar int
SET @myVar = 5

need to declare the variable and assigning it to the variable to split into 2 

lines and use 2 statements - DECLARE and SET.

In SQL Server 2008, as the name suggests ‘inline’, both declaration and assignment can be given in a single line:

DECLARE @myVar int = 5

How Microsoft Sql Server store pdf files

There are two ways to store pdf files in Microsoft sql Server.

1. To store pdf file in Microsoft Sql Server, create a column as type ‘blob’ in a table. Read the content of the file and save in ‘blob’ type column in a table.
2. Store pdf files in a folder and establish the pointer to link them in the database.