TCL Commands: TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database. COMMIT – Saves work done in transactions. ROLLBACK – Restores database to original state since the last COMMIT command in transactions. SAVE TRANSACTION – Sets a savepoint within a transaction. Commit Command: Commit is […]
DCL Commands: DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. GRANT – Gives user’s access privileges to database. REVOKE – Withdraws user’s access privileges to database given with the GRANT command Grant Command: […]
DML Commands: DML is abbreviation of Data Manipulation Language. It is used to insert, update and delete data in a database. INSERT – Inserts data into a table UPDATE – Updates existing data into a table DELETE – Deletes all records from a table Insert Command The SQL Server Insert into statement is used to add new […]
DDL (Data Definition Language ) Commands: DDL Commands are used to create or destroy the objects in SQL Server. Create Alter Drop Create Command To create a table to use fallowing syntax: Syntax: Create table <table-name> (column-1 datatype, column-2 datatype, column-3 datatype, ——————— ——————— Column-n datatype, primary key(one or more columns)) Example: Create table […]
Fill Factor: The fill factor is “how much percentage of space is filled by data on a page”. By default fill factor value is –>0 which is equal to–>100% The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of […]
Fragmentation: The empty spaces was found in a page, then that empty page is called “fragmentation”. The empty spaces were formed when huge modifications/deletions happen on a index (internally in a table). Fragmentation will impact performance of queries, because those empty spaces occupy variant space for no reason and are not utilized.
Filtered indexes were introduced in SQL Server 2008. Filtered indexes are non clustered indexes that have the addition of a WHERE clause. Creating filter index: CREATE NONCLUSTERED INDEX filter_index1 ON mydb.dbo.emp(sal) WHERE sal > 5000