SQL is nonprocedural, meaning that the database decides how to execute the SQL statement. The extensions discussed in this hour were procedural.
Normalization is the process used to ensure that a database is properly designed according to relational database principles.
Stored procedures are used to centralize the logic or business rules to abstract the security management and to improve performance.
When we want to process logical operations at the server side instead of the client side, we can use stored procedures.
Trigger is like a dynamic stored procedure. A trigger is a group of T-Sql statements that is executed automatically when specified events occur.
Simply to say a view is a stored sql select statement.
The most common use of views is the simplification of data access.
An index is a collection of data and reference information used to locate records more quickly in a database table. MS SQL Server supports two primary index types. One is clustered and another one is nonclustered.
They can be used to increase database performance and that they can equally decrease database performance when used improperly.
Primary key and unique keys are used to enforce uniqueness of the column. Primary key creates a clustered index on the column by default. But unique key creates a non-clustered index by default. Another important difference is the primary key does not allow NULLs, but unique key allows one NULL only.
Partitioned indexes are indexes like a partitioned tables stored on partitions instead of directly stored on filegroups. Partitioned indexes are usually used with partitioned tables. If we create an index on a table that is partitioned and do not specify otherwise, the index will automatically be partitioned to match the underlying table.
The Data Definition Language (DDL) is the part of SQL used to create, change, or destroy the basic elements of a relational database. Basic elements include tables, views, schemas, catalogs and clusters .
Spatial indexes are special indexes used for geometric or geographic data. To use a spatial index, we should have a spatial data. Spatial data is data that references geometric or geographic information. Spatial data columns are uses the geometry or geography data types.