Extras

Collation

Collation specifies how data is sorted and compared in a database.

Index

• A database index is a data structure that provides a quick lookup of data in a column/columns of the table.

• It enhances the speed of operations accessing data from table at the cost of additional writes and memory to maintain the index data structure.

• The index creates a tree-based structure that the database system's query optimizer can use to quickly find rows in the table based on a specified column.

CREATE INDEX idx_name ON table_name(column_name);

Disadvantages of the Indexes.

• Every time data changes in the table, all the indexes need to be updated.

• Indexes need disk space. The more indexes you have, more disk space is used.

• Index decreases the performance on inserts, updates and deletes.

• It slows down the query performance.

Unique Index

• Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values.

• Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index.

Non-Unique Index

• Non-unique indexes, on the other hand, are not used to enforce constraints on the tables with which they are associated.

• Instead, non-unique indexes are used solely to improve query performance by maintaining a sorted order of data values that are used frequently.

Clustered Index	Non-Clustered Index
The Clustered Index focuses on physical structure.	The non-clustered index focuses on logical structure.
The clustered index is more efficient, i.e., faster.	The Non-Clustered index is less, i.e., slower.
In a clustered index, the index is stored with the main data.	In a non-clustered index, the index is stored in a different table.
There can only be one clustered index.	There can be several non-clustered indexes.
The Index key represents the records in the database table.	The Index Key represents the order of records within the index of the database table.

Clustered Index

• Clustered indexes are indexes whose order of the rows in the database corresponds to the order of the rows in the index.

• Clustering indexes can improve the performance of most query operations because they provide a linear-access path to data stored in the database.

Clustered index modifies the way records are stored in a database based on the indexed column.

• The columns that make up a clustered index should form a unique, identity, primary key, or any combination where values are increased for each new entry. As clustered indexes sort the records based on the value, using a column already ordered ascending, such as an identity column, is a good choice.

• A column whose value changes frequently should not be used for a clustered index. The reason is that each change of the column used for the clustered index requires the records to be reordered. This re-ordering can easily be avoided by using a column that is updated less frequently, or ideally, not updated at all.

• Likewise, columns that store large data, such as BLOB columns (text, nvarchar(max), image, etc.), and GUID columns are not ideal for clustered indexes. This is because sorting large values is highly inefficient, and in case of GUID and image columns, doesn't make much sense.

• Finally, a clustered index should not be built on a column already used in a unique index.

Non-Clustered Index

A non-clustered index creates a separate entity within the table which references the original table.

The only difference between clustered and non-clustered indexes is that the database manager attempts to keep the data in the database in the same order as the corresponding keys appear in the clustered index.

The downside to adding indexes to a table is that they affect the performance of writes. Moreover, improperly created indexes can even adversely affect SELECT queries! Any table configuration where performance suffers due to excessive, improper, or missing indexes is considered to be poor indexing.

Pagination

DECLARE @PageNumber AS INT
            DECLARE @RowsOfPage AS INT
        DECLARE @MaxTablePage  AS FLOAT 
        SET @PageNumber=1
        SET @RowsOfPage=4
        SELECT @MaxTablePage = COUNT(*) FROM SampleFruits
        SET @MaxTablePage = CEILING(@MaxTablePage/@RowsOfPage)
        WHILE @MaxTablePage >= @PageNumber
        BEGIN
         SELECT FruitName,Price FROM SampleFruits
        ORDER BY Price 
        OFFSET (@PageNumber-1)*@RowsOfPage ROWS
        FETCH NEXT @RowsOfPage ROWS ONLY
        SET @PageNumber = @PageNumber + 1
        END

Pagination in SQL ServerSQL Shack - articles about database auditing, server performance, data recovery, and more

UNION

• There must be the same number of expressions in both SELECT statements.

• The corresponding expressions must have the same data type in the SELECT statements.

• For example:

  Expression1 must be the same data type in both the first and second SELECT statements.

UNION removes duplicate rows while UNION ALL does not remove duplicate rows.

UNION only appends distinct values. More specifically, when you use UNION, the dataset is appended, and any rows in the appended table that are exactly identical to rows in the first table are dropped. If you’d like to append all the values from the second table, use UNION ALL. You’ll likely use UNION ALL far more often than UNION.

FAQs

• Can you explain the difference between a select and an update query in SQL?

• How do you use the WHERE clause in an SQL query to filter results?

• How do you use aggregate functions in an SQL query, such as AVG, SUM, and COUNT?

• Can you explain the difference between a primary key and a foreign key in a database table?

54KB

intro-to-sql-course-glossary.pdf

pdf