Skip to main content

Posts

Showing posts with the label table

What is Covering Index in SQL

A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional look-up to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation. covering index is a non-clustered index which stores additional columns at leaf level to avoid bookmark lookup to heap/clustered index.this is typically created using INCLUDE clause. Let say we have a table tab1 and we have columns starts from column 1 to 10. and we have clustered index on column1 and non clustered index on column 2 . for example you written a query select * from tab1 where column 2=200. this query first will go and look for the matching data in non clustered index and because of our query want to retrieve all the columns it will perform key look up , not helathy. this time i had one more non clustered index on column3 and column4 ,composite.in this case our query

Clustered index and non clustered index in SQL

Clustered index which reordered the table and saved separately as index table(data pages stored as B-tree) there can be one clustered index per db & Non clustered index is typically a index(data pages stored as heap) A table can have only one clustered index with many non clustered indexes. The leaf nodes of the cluster index stores data pages and non clustered index stores key values(where as table includes clustered index) or rid's(table contains non cluster index only).

Create,Delete database,table,table data,index in sql by query analyzer

Delete database,table,table data,index in sql by query analyzer 1. To delete all data from table . TRUNCATE TABLE tablename 2. Delete database through query analyzer DROP DATABASE databasename 3. Delete table in sql DROP TABLE tablename 4. Delete index from table in sql. DROP INDEX tablename.indexname To create database in sql you can use following command in sql query analyzer. Create Database in SQL CREATE DATABASE databaseName; Second way is you can right click by mouse and select create new database then set name of database in sql prompt.