Interview

SQL

4 years ago
Add Comment
by shohal
2 Views
Written by shohal

SQL

Advantages and disadvantages of indexes

Indexes are used by queries to find data quickly. In this part, we will learn about the different queries that can benefit from indexes.

Create Employees table
CREATE TABLE [tblEmployee]
(
[Id] int Primary Key,
[FirstName] nvarchar(50),
[LastName] nvarchar(50),
[Salary] int,
[Gender] nvarchar(10),
[City] nvarchar(50)
)

Insert sample data:
Insert into tblEmployee Values(1,’Mike’, ‘Sandoz’,4500,’Male’,’New York’)
Insert into tblEmployee Values(2,’Sara’, ‘Menco’,6500,’Female’,’London’)
Insert into tblEmployee Values(3,’John’, ‘Barber’,2500,’Male’,’Sydney’)
Insert into tblEmployee Values(4,’Pam’, ‘Grove’,3500,’Female’,’Toronto’)
Insert into tblEmployee Values(5,’James’, ‘Mirch’,7500,’Male’,’London’)

Data from tblEmployee table

Create a Non-Clustered Index on Salary Column
Create NonClustered Index IX_tblEmployee_Salary
On tblEmployee (Salary Asc)

NonClustered Index

The following select query benefits from the index on the Salary column, because the salaries are sorted in ascending order in the index. From the index, it’s easy to identify the records where salary is between 4000 and 8000, and using the row address the corresponding records from the table can be fetched quickly.
Select * from tblEmployee where Salary > 4000 and Salary < 8000

Not only, the SELECT statement, even the following DELETE and UPDATE statements can also benefit from the index. To update or delete a row, SQL server needs to first find that row, and the index can help in searching and finding that specific row quickly.
Delete from tblEmployee where Salary = 2500
Update tblEmployee Set Salary = 9000 where Salary = 7500

Indexes can also help queries, that ask for sorted results. Since the Salaries are already sorted, the database engine, simply scans the index from the first entry to the last entry and retrieve the rows in sorted order. This avoids, sorting of rows during query execution, which can significantly imrpove the processing time.
Select * from tblEmployee order by Salary

The index on the Salary column, can also help the query below, by scanning the index in reverse order.
Select * from tblEmployee order by Salary Desc

GROUP BY queries can also benefit from indexes. To group the Employees with the same salary, the query engine, can use the index on Salary column, to retrieve the already sorted salaries. Since matching salaries are present in consecutive index entries, it is to count the total number of Employees  at each Salary quickly. 
Select Salary, COUNT(Salary) as Total
from tblEmployee
Group By Salary

Diadvantages of Indexes:
Additional Disk Space: Clustered Index does not, require any additional storage. Every Non-Clustered index requires additional space as it is stored separately from the table.The amount of space required will depend on the size of the table, and the number and types of columns used in the index.

Insert Update and Delete statements can become slow: When DML (Data Manipulation Language) statements (INSERT, UPDATE, DELETE) modifies data in a table, the data in all the indexes also needs to be updated. Indexes can help, to search and locate the rows, that we want to delete, but too many indexes to update can actually hurt the performance of data modifications.

What is a covering query?
If all the columns that you have requested in the SELECT clause of query, are present in the index, then there is no need to lookup in the table again. The requested columns data can simply be returned from the index.

A clustered index, always covers a query, since it contains all of the data in a table. A composite index is an index on two or more columns. Both clustered and nonclustered indexes can be composite indexes. To a certain extent, a composite index, can cover a query.

——————————-***********************—————————-

Sql vs tsql vs plsql

In this video we will understand the difference between SQL, T-SQL and PL/SQL.

SQL stands for Structured Query Language. So, SQL is a language, specifically, it’s a language for relational Databases.

What is a relational database and why do we use it


In simple terms a relational database is a collection of tables to store data. The tables are usually related to each other by primary and foreign key constraints, hence the term Relational Database Management System, in short RDBMS.

For example, let’s say we want to store our organisation Employees data. We create a database (EmployeesDB – I named it EmployeesDB, but you can give it any meaningful name you want) and in this database we create 3 tables.

  • Departments – To store the list of all departments
  • Gender – To store different genders (Male and Female for example)
  • Employees – To store the list of all employees

To create the database itself, tables, relationships and to insert, update, delete and even select data we use SQL – Structured Query Language. So, in simple terms, SQL is a database language, we use it on a Database to create database objects like tables, views, functions etc. We also use it to insert, update, delete and select data.

What is T-SQL and PL/SQL and how is it different SQL

Well you can think of SQL as a standard database language. It was initially developed by IBM and later ANSI (American National Standards Institute) made it a standard. So, SQL is an ANSI standard and based on it different database vendors like Microsoft, Oracle and many other organisations developed their own database query language.


Standards are always good, because they allow us to write similar queries across different relational database management systems. Different vendors like Microsoft and Oracle for example, support most of the features of the ANSI SQL standard, however, these database vendors also include their own non-standard features that extend the standard SQL language.

So the database that is developed by Microsoft is called Microsoft SQL Server or MS SQL Server for short. The language that Microsoft developed to query SQL Server database is called Transact-SQL or T-SQL for short.


Similarly Oracle corporation developed a database management system called Oracle and the language that we use to query oracle database is PL/SQL. By the way, PL stands for Procedural Language.

So, you can think of SQL as a subset of T-SQL and PL/SQL. A word of caution here, both T-SQL and PL/SQL does not implement 100% of the feature set of standard SQL, but majority of the standard features are implemented. You can see that from the diagram below. Although, not entirely true, you can still think SQL is almost a subset of T-SQL and PL/SQL. This means if you know T-SQL or PL/SQL, then you already know the standard SQL.

The standard SQL is same across all database vendors. This means if you know the standard SQL, then you know how to do most of the basic things on most of the database management systems like SQL Server, Oracle, MySQL, PostgreSQL etc. If you ware wondering what is MySQL and PostgreSQL, well, just like SQL Server and Oracle, they are also relational database management systems.

Summary

  • SQL is the standard database language
  • Based on this standard SQL, database vendors like Microsoft, Oracle and many other organizations developed their own database query languages
  • TSQL is a proprietary procedural language for working with Microsoft SQL Server database
  • Similarly, PL/SQL is a proprietary procedural language for working with Oracle database
  • T-SQL and PL/SQL are an extension to standard SQL. 
  • This means they have more features and functions than the standard SQL. 
  • For example, features such as local variables are added. Similarly many, many built-in functions are added for processing strings, numbers, dates and other types of data.
  • They also added the capability to write stored procedures.

In short these procedural languages like T-SQL and PL/SQL for example, helps us in writing queries easier, quicker and more efficiently.

If you want to learn SQL and T-SQL, please check out our SQL Server tutorial for beginners course. We have covered everything you need, from the basics to advanced SQL concepts.

SQL Scripts

Create Database EmployeesDB
Go
Use EmployeesDB
Go 
Create table Departments
(
       Id int primary key identity,
       [Name] nvarchar(50)
)
Go 
Create table Gender
(
       Id int primary key identity,
       Gender nvarchar(20)
)
Go 
Create table Employees
(
       Id int primary key identity,
       [Name] nvarchar(50),
       DeptId int foreign key references Departments(Id),
       GenderId int foreign key references Gender(Id)
)
Go 
Insert into Departments values ('IT')
Insert into Departments values ('HR')
Go 
Insert into Gender values ('Male')
Insert into Gender values ('Female')
Go 
Insert into Employees ([Name], DeptId, GenderId) values ('Mark', 1, 1)
Insert into Employees ([Name], DeptId, GenderId) values ('Mary', 1, 2)
Insert into Employees ([Name], DeptId, GenderId) values ('John', 2, 1)
Insert into Employees ([Name], DeptId, GenderId) values ('Sara', 2, 2)
Insert into Employees ([Name], DeptId, GenderId) values ('Steve', 2, 1)
Go
Select * from Departments
Select * from Gender
Select * from Employees 
Select Employees.Name as [Name], Departments.Name as Department, Gender.Gender as Gender
from Employees
join Departments on Employees.DeptId = Departments.Id
join Gender on Employees.GenderId = Gender.Id

———————***************************——————–

What is a schema name in SQL Server?

What is a schema in SQL Server. A schema is a collection of database objects including tables, views, triggers, stored procedures, indexes, etc. A schema is associated with a username which is known as the schema owner, who is the owner of the logically related database objects.

How do I find schema name?

select owner, table_name from all_tables. Edit: I think above sql is correct solution in all cases because schema is owner of all db objects. So either i get schema or owner both are same.

What is schema in SQL example?

A schema is a collection of database objects like tables, triggers, stored procedures, etc. A schema is connected with a user which is known as the schema owner. Database may have one or more schema. SQL Server have some built-in schema, for example: dbo, guest, sys, and INFORMATION_SCHEMA.

What is the schema name in database?

A schema is a collection of database objects (as far as this hour is concerned—tables) associated with one particular database username. This username is called the schema owner, or the owner of the related group of objects. You may have one or multiple schemas in a database.

Why schema is used in SQL?

A SQL schema is a useful database concept. It helps us to create a logical grouping of objects such as tables, stored procedures, and functions.

What schema means?

1 : a diagrammatic presentation broadly : a structured framework or plan : outline. 2 : a mental codification of experience that includes a particular organized way of perceiving cognitively and responding to a complex situation or set of stimuli.

How do you set a schema?

The SET SCHEMA statement sets the default schema for a connection’s session to the designated schema. The default schema is used as the target schema for all statements issued from the connection that do not explicitly specify a schema name. The target schema must exist for the SET SCHEMA statement to succeed.

Why do we need schema in database?

As part of a data dictionary, a database schema indicates how the entities that make up the database relate to one another, including tables, views, stored procedures, and more. Typically, a database designer creates a database schema to help programmers whose software will interact with the database.

For More : https://www.youtube.com/watch?v=9EkQNQ5IbPA&list=PLX2qHGPZlD6xm99MbNlyHKXy3lPG0HVTq&index=5

You may also like

About the author

shohal

View all posts

Leave a Comment