Mastering SQL Server: Essential Tips for Beginners

In this article, We will cover all the topics of SQL Server. This will work as SQL Server notes for beginners.

SQL Server is a relational database management framework created by Microsoft. It is profoundly adaptable and runs on Windows-based machines. Since the arrival of the SQL Server’s first version, the SQL server has continued to advance.

While its main capability is its relational database management framework, the SQL server has become undeniably more than that. These include advanced security, integrated reporting and analytical capabilities.

SQL Server database information is a must for individuals working on Microsoft technologies like ASP.NET, Dot NET Core and VB.NET.

This article will cover all those points of SQL Server that a beginner should need. Also, these points are very important for interviews as well.

What is a relational database?

A relational database is a database model based on relational data proposed by E.F. Codd. RDBMS (Relational Database Management System) is used to maintain relational databases.

SQL Server is one of the most famous RDBMS. Let’s explore the important points of SQL Server.

What are the Constraints in SQL Server?

It is a rule or limit on the data type of a table.

List of Constraints –

• Not null
• Unique
• check
• Default
• Index

Unique Key and Primary Key

A unique Key is used to uniquely identify a record in a table. Multiple values can be there per table. Null values are allowed in Unique Keys.

The primary Key uniquely identifies each record in a table. One table can contain only one primary key.

Q. What is Data Integrity ?
Ans: Data Integrity defines, the accuracy of data, Consistency of Data

Indexing in SQL Server

An index is one of the significant ways to make the performance of the database high. It makes the questioning system quick by giving easy access to records in tables.

There are 2 types of Indexing in the SQL server.

• Clustered Index –  It is used for easy retrieval of data from the database and it is Faster. Only one cluster index can have in a single table.
• Non-clustered index – It is slower than a clustered index. A single table can have multiple non-clustered indexes. 

Normalization and De-Normalization

Denormalization – It increases the performance of the entire Database as it introduces redundancy into a table. We can do this by removing joins. This combines data from multiple tables into a single table.

Normalization – Normalization is a process of organizing data to avoid duplication and data redundancy.

Form of Normalization in SQL Server

1NF – Each table cell would have a single value. It means all the records must be unique

2NF – Database should be in 1 NF and should also have single column primary key

3NF – If the Database is in 2NF and there is no transition dependency exists.

BCNF – BCNF is a stronger form of 3NF.

4NF – A database is in 4NF if it is in BCNF form and it should not have a multi-valued dependency.

5NF – A Database is in 5NF if it is in 4NF and does not contain join dependency.

Advantages of Normalization in SQL Server

• Better DB organization
• More number of tables with fewer columns
• Quickly find information
• Easy modification – We can modify the data easily

Disadvantage of normalization is that a bad normaliz form may lead to the worst database design.

ACID Property

Let’s see the ACID property of DBMS.

A (Automaticity) – It means a part of the transaction fails then complete transactions should be failed and DB remains unchanged

C (Consistency) – It means data should meet all the validation rules

I (Isolation) – Multiple transactions happen independently without interfering with other transactions

D (Durability) – It says that if a transaction is successful then the records will be stored permanently in the storage space.

What is Trigger?

A trigger is a special kind of stored procedure defined to execute automatically in place or after data modifications.

Q. Are Null values the same as zero or blank space?

Answer – No. A null value basically means the value is unavailable or unknown. Zero is a number while blank space is a character.

Joins in SQL Server

The join clause permits us to fetch data from at least two related tables into a significant outcome set. We can join the table with a SELECT statement and a join condition.

It shows how SQL Server can utilize data from one table to select records from another table. As a general rule, tables are connected with one another using foreign key constraints.

Types of Joins

This is one of the most asked questions in SQL Server interviews.

There are the following types of Joins in SQL Server.

Inner Join

This JOIN returns all rows from more than one table that fulfil the predetermined join condition. It is the basic and most well-known type of join and expects as a default join.

Syntax of Inner Join

SELECT column1,column2, column 3......column n    
FROM Table1    
INNER JOIN Table2 ON condition1    
INNER JOIN Table3 ON condition2  

SELF Join

A table is joined to itself with the help of SELF JOIN. It implies that each table column is joined with itself and with each and every other table line. This can be considered a JOIN of two duplicates of similar tables. We can do this with the assistance of table name aliases.

CROSS Join

The SQL CROSS JOIN delivers an outcome set which is the number of rows in the main table multiplied by the number of records in the subsequent table. This sort of result is called a Cartesian product.

Syntax of CROSS Join in SQL server

SELECT * 
FROM Table1 
CROSS JOIN Table2;

LEFT Join or LEFT OUTER Join

The LEFT OUTER JOIN gets every one of the records from the left table and matching records from the right table. It will return NULL while no matching record is seen as in the right side table. This is called LEFT join as well.

Syntax to write LEFT Join

SELECT column1, column2, .... column n  
FROM Table1    
LEFT JOIN Table2    
ON Table1.column = Table2.column;  

RIGHT OUTER Join

This is also called RIGHT Join. The RIGHT OUTER JOIN gets every one of the records from the right table and matching records from the left table. It will return NULL while no matching record is seen as in the left side table.

SELECT column1, column2, ... column n    
FROM Table1    
RIGHT JOIN Table2    
ON Table1.column = Table2.column;    

FULL OUTER Join

This is also called FULL Join. The FULL OUTER JOIN in SQL Server returns an outcome that incorporates all records from the two tables. The segments of the right-hand table return NULL while no matching records are found in the left-hand table. Furthermore, assuming no matching records are found in the right-hand table, the left-hand table section brings NULL.

Syntax to write FULL OUTER Join

SELECT column1, column2.... column n   
FROM Table1    
FULL JOIN Table2    
ON Table1.column = Table2.column;    

Subquery – It is a query inside another query. Sub-query is always executed first because it has to pass the value to the main query

Group Function – Available group functions in SQL Server are AVG, COUNT, MAX, MIN, and SUM. The group function works on a set of rules and returns one result per group.

Relationship in SQL Server

One-to-One – Single record in one table will relate to a single record in the second table.

one to many – Single record in the first table will have a relation to many records in the second table.

Many-to-One – Many records in table A are related to one record in table B.

Self-referential – in a single table

Operators in SQL Server

Between operator – It gives two ranges to get the values.

in operator – It gives a set of values to fetch the result.

What is Drop Command?

Drop command removes the table and it cannot be rolled back.

Truncate in SQL server

It removes all rows from the table and it cannot be rolled back.

What is the Recursive CTE in SQL Server?

A recursive Common Table Expression (CTE) is a CTE that references itself. The CTE executes over and again and returns subsets of data until it returns the total data set.
A recursive CTE is valuable in querying the hierarchical type of data, for example, an organization chart.

Syntax to write CTE expression in SQL server.

;WITH CTE1 (column1, column2.... column n)
AS
(
  -- Write query
)
 
SELECT *
FROM   CTE1

What is a Clause in SQL Server?

A clause limits the results by applying it to the query. Some of the clauses are – where having

Where – It can be applied with a select update and delete statements

Having – It can be applied with a select statement only. It is usually used with a group-by clause.

Constraints can be applied to column level and table label. 

What is a View?

A View is a virtual table where you can combine data from multiple tables and put it in a view.

Why do use view?

It will help to make complex queries easier.

What is a stored procedure in SQL Server?

Stored Procedure advantages it is faster it provides better security disadvantages it utilizes memory in the database server.

Functions in SQL Server

A Function in SQL Server is a piece of code where we pass the parameters and it returns some value.

Types of Functions in SQL Server

There are mainly 2 types of functions in SQL Server

• User Defined Function
• System Defined Function

User-Defined Function in SQL Server

SQL Server supports 2 types of user-defined functions and those are –

• Table Valued Function – This function returns a table.
• Scalar Function – This type of function accepts n number of parameters and returns a scalar value or single value. Here n denotes any numeric value.

What is Collation in SQL Server?

A set of rules that determine how data can be sorted and compared is called Collation.

Q. What is a local variable?

Answer – Local Variables can be used and exist only inside a function.

Q. What is a Global Variable in SQL Server?

Answer – A global variable can be accessed throughout the program.

Q. What is DataWarehouse?

Answer – Data warehouse is a central repository where data can be assembled from multiple sources of information.

Summary

This article covers the topics that must know for freshers or beginners. A beginner can use this article as a SQL Server notes to prepare for interview.