Filtered Indexes?

Filtered index:

Filtered indexes for SQL Server were introduced in SQL Server 2008. Put simply, filtered indexes are nonclustered indexes that have the addition of a WHERE clause. Although the WHERE clause in a filtered index allows only simple predicates, it provides notable improvements over a traditional nonclustered index. This allows the index to target specific data values – only records with certain values will be added to the index – resulting in a much smaller, more accurate, and more efficient index.
The basic syntax for creating a filtered index is:

CREATE NONCLUSTERED INDEX <index name> ON <table> (<columns>) WHERE <criteria>; GO

Advantages of using filtered indexes

Reduced index maintenance costs. Insert, update, delete, and merge operations are not as expensive, since a filtered index is smaller and does not require as much time for reorganization or rebuilding.
Reduced storage cost. The smaller size of a filtered index results in a lower overall index storage requirement.
More accurate statistics. Filtered index statistics cover only the rows the meet the WHERE criteria, so in general, they are more accurate than full-table statistics.
Optimized query performance. Because filtered indexes are smaller and have more accurate statistics, queries and execution plans are more efficient.

Limitations

• Statistics may not get updated often enough, depending on how often filtered column data is changed. Because of how SQL Server decides when to update statistics (when about 20% of a column’s data has been modified), statistics could become quite out of date. The solution to this issue is to set up a job to run UPDATE STATISTICS more frequently.
• Filtered indexes cannot be created on views. However, a filtered index on the base table of a view will still optimize a view’s query.
• XML indexes and full-text indexes cannot be filtered. Only nonclustered indexes are able to take advantage of the WHERE clause.
• The WHERE clause of a filtered index will accept simple predicates only.

Can we create non clustered index on a primary key column?

Yes we can create non clustered index on a primary key column!

According to the default behaviour of sql server, when we create a primary key column in a table then a clustered index will be automatically created on that particular column.

This default behaviour can be changed. And we can tell sql server not to create any index on primary key column. i.e, we can ask sql server to create primary key column without any index and some other column with clustered index.
So, this is one method in which we can create non clustered index on a primary key column. This is shown in the below way.

CREATE TABLE dbo.Table_1 (
Id int NOT NULL IDENTITY (1, 1) PRIMARY KEY NONCLUSTERED,SomeOtherUniqueColumn int NOT NULL UNIQUE CLUSTERED ) ON [PRIMARY]

Database View's

A view is a virtual table which can be created over a single or multiple tables.

Eg: CREATE VIEW view_name1 AS (SELECT col1, col2 FROM table_name WHERE col2>25)
OR
CREATE VIEW view_name2 AS (SELECT t1.col1, t1.col3, t2.col1, t2.col4 FROM table_name1 LEFT OUTER JOIN table_name2 ON t1.col2=t2.col2 )

Internals:

1. There will not be any physical existence for a view.
2. Actually only the select query which is used in creating a view will be saved under the view name (“view_name” in the above example).
3. View is a separate database object like other's (table, procedure, function, login, trigger, cursor, sequence and etc.,).
4. So, a view can be queried like table.

   Eg: SELECT colx, coly FROM view_name1

5. The above query will be thought of the below:

   Eg: SELECT colx, coly FROM (SELECT col1, col2 FROM table_name WHERE col2>25)-> “This query is taken from the creation statement of view 'view_name1' “.

Are the Views Up-datable?

1. As we already know that there is no physical existence of view and we have already seen that, data is retrieved from original underlying table when the view is queried.
2. So, if we update a view then the underlying table should be updated. And the update operation should get executed without any problems.
3. Hold on..!
4. Yes, view are up-datable. But only when they are created on a single table as in 'view_name1'.
5. Hmm. Why so ???
6. See the creation statement of 'view_name2'. This view is created on joining two tables. In this example in particular, all the column values of the table 'table_name2' will be displayed as NULL's in the result set except the values which satisfy the join condition (ON t1.col2=t2.col2). But the original table 'table_name2' will not be having NULL values in those positions of the result set.
7. For this reason views which are created from multiple tables cannot be updated.

Usage:

1. Views are created to grant limited access to the users on the database tables. A security measure.
2. It will be very efficient to retrieve the actual desired data rather than the entire table. This can be done by creating a view on the desired data and then retrieving that data from the view. A performance measure.

Rules of GroupBy Clause

Group functions are the built-in SQL functions that act on groups of rows and return one value for the entire group.

Rules of GroupBy clause

The name may be a Column reference. Such a Column is called a grouping Column: its values will be grouped for the final result.

* The group by clause should contain all the columns in the select list expect those used along with the group functions.

Example:
Let us take the below query,
SELECT location, dept, SUM (salary) 
FROM employee 
GROUP BY location, dept;

In the above query, if the columns 'location' and 'dept' are not specified next to GroupBy clause, then it results an error.

* It is upto us to include aggregated columns next to GroupBy. Meaning, in the above mentioned example it is upto us to include column 'salary' next to the GroupBy clause.

* We cannot include a column next to GroupBy, if that particular column is not selected.

Handling the completely filled Transaction Log problem

Is your Transaction Log full ? And are you not able to execute transactions because of error saying "Transaction Log is full" ?

Problem Explanation:

In SQL Server, we have three recovery models.

1. Simple Recovery Model
2. Full Recovery Model
3. Bulk-Logged Recovery Model.

An SQL Server database has two files by default namely,

• Data file “.mdf”
• Log file “.ldf”

This database should be managed under one of the earlier mentioned recovery models.

NOTE: Taking Transaction Log Backup is the only way to truncate(empty) the log file.

In Simple Recovery model,
SQL Server automatically truncates the log file in regular intervals.
That is the reason why we are not allowed to take a transaction log backup in this mode of recovery (And there is no need to take transaction log backup also in this mode).
So, coming to the main point, log file will never get fulled in this mode of recovery.

In Full Recovery model,
Burden of maintaining and controlling the transaction log is completely left on the DBA.
So, If we dont take regular log backups of the database, then log file will get filled up until the threshold (Refer the NOTE mentioned earlier).

Listed below are some other regular scenarios when transaction log file of SQL Server database grows:

• If there are no scheduled log backups or log backups are not working.
• If there are active transactions in the log file
• If there are long running queries
• In database mirroring if mirror server fails
• In replication if distributor fails
• If there is no disk space to grow the Transaction Log file
• If bulk data was imported without dividing into multiple batches or transactions.

Problem Solution:

When the transaction log becomes full, SQL Server Database Engine issues a 9002 error.

Follow the below mentioned steps to take a transaction log backup.

1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2. Expand Databases, and select a desired database.

3. Right-click the database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.

4. In the Database list box, verify the database name. You can optionally select a different database from the list.

5. Verify that the recovery model is either FULL or BULK_LOGGED.

6. In the Backup type list box, select Transaction Log.

7. Optionally, you can select Copy Only Backup to create a copy-only backup. Leave this here.

8. Either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

9. Optionally, in the Description text box, enter a description of the backup set.

10. Choose the type of backup destination by clicking Disk or Tape. To select the paths of up to 64 disk or tape drives containing a single media set, click Add. The selected paths are displayed in the Backup to list box.

11. To remove a backup destination, select it and click Remove. To view the contents of a backup destination, select it and click Contents.

12. To view or select the advanced options, click Options in the Select a page pane.

13. Select an Overwrite Media option, by clicking one of the following:

• Back up to the existing media set
  • For this option, click either Append to the existing backup set or Overwrite all existing backup sets.
  • Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.
  • Optionally, enter a name in the Media set name text box. If no name is specified, a media set with a blank name is created. If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.
  • If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.

• Back up to a new media set, and erase all existing backup sets
  • For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

14. In the Reliability section, optionally, check:

• Verify backup when finished.
• Perform checksum before writing to media, and, optionally, Continue on checksum error.

15. In the Transaction log section:

• For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.
• To back up the tail of the log (that is, the active log), check Back up the tail of the log, and leave database in the restoring state.

16. If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. Clicking this option activates the Rewind the tape before unloading option.

17. SQL Server 2008 Enterprise and later supports backup compression. By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

After taking transaction log backups, in-active log will be truncated and empty space is created for the upcoming transactions. We can come out of the problem.

In order to prevent the occurrence of this problem in future, log backups should be scheduled at regular intervals according to the server traffic when we have opted for the Full Recovery mode.

Note: The process detailed above will only empty the log file. It will not reduce the log file size.The side reduction of database files is achieved by shrinking them. Shrinking is not a suggested operation. It is always good to maintain adequate log size by taking regular backups. 

Database Snapshots

A Snapshot is a read only copy of the source database as it existed when we created snapshot. 

The following list are some of the properties of database snapshots:

• Snapshot database is completely dependent of the source database.
• If a source database corrupts, the snapshot will also be corrupted.
• We can even restore the entire source database from the snapshot to the time of snapshot creation with below syntax

RESTORE DATABASE db_name
FROM DATABASE_SNAPSHOT = 'snapshotdb_name'

We can run the below query to list all the database snapshots created in a particular database instance.

SELECT * FROM sys.databases 

•  In the result of the above query, the value of 'source_db_id' column will be NULL for all the databases. Because these are original databases.
• So, if the column 'source_db_id' has some value other than null, it indicates that that is a database snapshot. 

We can directly list the database snapshots with the below query.

SELECT * FROM sys.databases 
WHERE source_database_id IS NOT NULL 

 Concept of Database Snapshots

• When a database snapshot is created, an empty 'sparse file' will be created with exactly the number of     pages that the source database consists.
• So, essentially a snapshot starts up empty.
• The database snapshot preserves each page of the source database when the time it (snapshot) was created.
• If few pages are modified in the source database, then those pages ( just before writing the modifications to the disk ) will be copied to the snapshot ( into empty space of those particular pages in the previously created sparse file ).

You can visualize the before mentioned concepts in the below figure:

Not able to understand? Try seeing the below figure:

Got it..!

You can also visualize the above concept in the below animation.

• Snapshot size on disk increases with the updates or modifications made on the source database.
• Source database and the snapshot database will be accessing the same data files.
• That is the reason why, we can run the snapshot on a different host ( source database should be present in this host up and running or available always ) so that the work load can be shared between the source and snapshot database as long as the read-only operations as concern.
• Database snapshots not only give the point in time view of the source database, it also gives point in time transactionally consistent view of the source database.

i.e, if there are any active transactions at the time of snapshot creation, then they must be rolled back.

If a page is copied into the snapshot because of a transaction, then if this transaction rolls back, then the page that is added into the snapshot will not be removed ( One-way operation ). 

This page copying is done synchronously which affects the performance.  

•  Source database and the snapshot database should be located in the same instance. 

Now when a query is submitted on the snapshot database and if the required pages are not present in it, then those pages are brought from source database to serve the query.

No locks taken on reads from the source database when copying the page to snapshots.

"SELECT" Statement

I will take you through two steps in which an SELECT statement will get processed...

Optimizing a Select

1. A SELECT statement is non-procedural.
2. A database server needs to optimize a SELECT statement because it is non-procedural (we cannot know   the exact number of steps that the server would undergo to execute the query).
3. Query optimizer does this optimizing query.
4. Tables, views definitions, indexes and statistics are input to the query optimizer and execution plan is its      output.

A query execution plan is a definition of following:

1. Sequence in which the source tables are accessed.

• Usually accessed in any way (top to bottom or bottom to top) and it does not matter.

2. The methods used to extract data from each table.

• Table scan
• Index scan
• Index seek

SQL Server Query optimizer:

1. SQL server query optimizer is cost-based.
2. It selects a plan which has reasonable cost and returns results faster.
3. Optimizer relies on statistics which helps in estimating the costs of the resources required to process a query.

Processing a SELECT statement

1. Parser scans the SELECT statement and breaks it into logical units such as keywords, expressions,    operators and identifiers.

2. A query tree or a sequence tree is built describing the logical steps needed to transform the source data  into result set format.

3. The optimizer analyzes different ways the source tables can be accessed.

4. Then the query tree is updated with the exact series of the steps which gives fast results while consuming  less resources.

5. The final optimized version of query tree is the execution plan.

6. The relational engine starts executing the execution plan upon getting the data from the storage engine.

7. The relational engine itself processes the data returned from the storage engine into the result set format and  sends to client.

Thanks for reading...!
Comments and issues are accepted with great respect.