Filtered index in Microsoft SQL Server 2008

Filtered index in SQL Server 2008 is an index WHERE clause. A filtered index is an optimized non-clustered index. It allows for defining the filter predicate with WHERE clause at the time of creating index. The rows from the filtered index are available in B-Tree will contain the rows which only satisfy the criteria of the filter while creating the index.

The advantages of Filtered indexes are :

1. Has only that row which satisfies the filter criteria defined. This results in reducing the storage space need for the index.
2. The filtered index statistics are accurate and more compact. The reason is they consider only the tuples / rows in the filtered index and it reduces the cost of overhead of updating the statistics.
3. The data modification impact is less by using filtered index. Because it is updated only at the time where the data of the index is impacted.
4. The cost of maintenance will be reduced, because only the subset of rows will be considered which rebuilding the index.

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. 

Order of Query Execution in Microsoft SQL Server

SQL query is excuted in following order.

The query goes to the shared pool that has information like parse tree and execution plan for the corresponding statement. Then validates the SQL statement and validates the source(table).

Acquire locks.

Checks all the privileges.

Execute the query.

Fetch the values for SELECT statement

Displays the fetched values.

To sum up, the sequence is:

SELECT
FROM
WHERE
GROUP BY
HAVING 

Cross Join and Full Outer Join in SQL Server

Cross Join

There is no join conditions specified in Cross Join. Results will be Cartesian product of two tables participating in join. Results in pairs of rows.

Full Outer Join

It is combination of both left and right outer joins. Results in every row from both of the tables , at least once. It assigns NULL for unmatched fields.

Establish Remote Connection to Microsoft SQL Server Database

Establish remote connection in database using following process.

• Use SQL Server Surface Area Configuration Tool for enabling the remote connection in database. 
• Click on Surface Area Configuration for Services and Connections.
• Click on SQLEXPRESS/Database Engine/RemoteConnections
• Select the radio button: Local and Remote Connections and select ‘Using TCP/IP only’ under Local and Remote Connections.
• Click on OK button / Apply button.

Linked Server in Microsoft SQL Server

Microsoft SQL Server supports the connection to different OLE DB on an ad hoc basis. This persistent connection is referred as Linked Server.

To use Linked Server for any OLE DB, follow these steps.

• Open SQL Server Management Studio in SQL Server 2005
• Expand Server Objects in Object Explorer.
• Right-click on Linked Servers. Click on New Linked Server.
• Select General page in the left pane and  
• Type any name for the linked server in the first text box
• Select the Other Data Source option.
• Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list. 
• Type the Excel as the name of the OLE DB data source.
• Type the full path and file name of the Excel file in Data Source box.
• Type the Excel version no. (7.0, 8.0 etc) in the Provider String. 
• Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
• To create a linked server click on OK.

Trace frag in Sqlserver

Temporary setting of specific server characteristics is done by trace tags. 

DBCC TRACEON is the command to set the trace flags. Once activated, trace flag will be in effect until the server is restarted. Trace frags are frequently used for diagnosing performance issues.

For example, the trace flag 3205 is used for disabling hard compression for tape drives, when an instance of SQL Server starts.

Buffer Cache and Log Cache in Sql Server

Buffer cache is a memory pool in which data pages are read. It performance of the buffer cache is indicated as follows: 95% indicates that pages that were found in the memory are 95% of time. Another 5% is needed for physical disk access. If the value falls below 90%, it is the indication of more physical memory requirement on the server.

Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log caches separately from the buffer cache.