Explain Compression - row-level and page-level compression in 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 and 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.
What is Service Broker?
Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable.
Describe Entity Data Services in sql server 2008, i.e. Line Of Business (LOB) framework and Entity Query Language (eSQL)
SQL Server 2008 allows objects to be created for high level business like Customers, Parts, Inventory. Instead of returning individual rows and tables, these entities could be used. The E-R model will now match with the SQL model.
What do you mean by Table Sample?
TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.
What is filtered indexes in sql server 2008? Explain its benefits and provide an example
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 benefits of Filtered indexes are:
Has only that row which satisfies the filter criteria defined. This results in reducing the storage space need for the index.
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.
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.
The cost of maintenance will be reduced, because only the subset of rows will be considered which rebuilding the index.
Ex: CREATE NONCLUSTERED INDEX FI_Employee_DOJ ON Employee(DOJ)
WHERE DOJ IS NOT NULL
In the above example the NOT NULL is the filtered criteria for the index. Employee is the table and DOJ is the column name