SQL Server Tutorial

What is SQL Server?

SQL Server is a Microsoft-developed and promoted relational database management system or RDBMS. SQL Server is designed on top of SQL, a popular programming language for communicating with relational databases, much like other RDBMS applications. SQL Server is related to Transact-SQL, or T-SQL, which incorporates a collection of proprietary programming constructs to Microsoft's SQL implementation. For over 20 years, SQL Server has been running exclusively in the Windows environment. Microsoft made it available on Linux in 2016. In October 2016, SQL Server 2017 was typically available, running on both Windows and Linux.

      Take your career to next level in SQL Server DBA with HKR. Enroll now to get SQL Server Certification Course Training!

Why SQL Server?

Unlike most other desktop applications, the SQL Server can function like a client-server database system. These systems operate either on a central server or on many central servers. This enables the data to be accessed concurrently from the network by several users. The database can be accessed by users through an application. Using the database, the data is saved and made available. In SQL Server, some helpful features allow CRM applications to deliver their functionality. Working professionals can be in various nations and access the data through the browser and update it.

Versions of SQL Server

There were approximately 19 distinct models of SQL Server. There were, indeed, inevitably some recognizable ones. They are listed below.

• The primary one emerged as a part of a project to port the Sybase SQL Server to OS/2 in 1989.
• With Server 4.2 for NT that was released in 1993, the SQL Server made an entry into Windows NT.
• In 1995, SQL Server 6.0 was launched and the Sybase cooperation ended.
• In 1998, SQL Server 7.0, during which source code was transformed from C to C++, was launched.
• SQL Server 2005, where the old Sybase code was withdrawn, which was unveiled in 2005.
• New features such as Availability Groups and Always On SQL Server Failover Cluster Instances have been incorporated in SQL Server 2012, providing options to enhance the availability of databases.
• New in-memory functionality for tables was introduced by SQL Server 2014 that made them fit completely into memory.
• SQL Server 2017, or version 14.0, codenamed vNext, was launched in 2017, adding support for Linux.
• SQL Server 2019, or version 15.0, codenamed Seattle, was launched in 2019, provided Always On availability groups ensuring effective resource management and improved database availability.

SQL Server Architecture

The SQL Server Architecture is built on the model of the client/server architecture where users share and access the data on the server through client applications. Understanding internal design or architecture is the best method of learning technology.

General Architecture: 

The General Architecture takes care of a few specifics, such as queries, protocols, servers, and relational engines. It also engages in splitting the queries into logical units and obtaining requests initiated by clients. It also engages operating with a query optimizer and the right plan of execution. The below diagram illustrates the overview of general architecture.

IMG

Memory Architecture: One of the memory architecture's main design objectives is to reduce reads and writes to the disk. It is a crucial part of the architecture of SQL Server. The below diagram illustrates the overview of memory architecture.

IMG

Data File Architecture: The most critical aspect of the Data File Architecture is the grouping of the database into database files used for allocation and administration purposes. A file does not become a member of more than one single group. The below diagram illustrates the overview of Data File Architecture.

IMG

Log File Architecture: 

The logical operation is carried out by the transaction log of the SQL Server. Here, the transaction log serves as a log record string. The processes are all logged. The Log File Architecture depends heavily on the SQL Server Architecture. The below diagram illustrates the overview of log file architecture.

IMG

Components of SQL Server

The latest SQL Server versions are launched every 18 months. Significant developments often do not make marketing headlines, but also deliver improvements in productivity and performance. In the consolidated updates, Microsoft began to incorporate product releases.

Database Engine: This section of the SQL Server builds and drives relational databases. The below diagram illustrates the overview of database engine architecture.

IMG

SQL Server Analysis Services: This section manages SQL Server data-analysis. They build Online Analytical Processing cubes which are highly developed programming objects that can organize data within the relational database. The below diagram illustrates the overview of SQL Server 2005 analysis services architecture.

IMG

SQL Server DBA Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

SQL Server Reporting Services: It is a SQL Server component that, despite the database operating system, offers reporting. The below diagram illustrates the overview of SQL Server reporting services architecture.

IMG

SQL Server Integration Services: The SQL Server will perform the Extract, Transform, and Load or ETL process of the SQL Server. The below diagram illustrates the overview of SQL Server Integration Services architecture.

IMG

SQL Key types

Keys function in the database tables as a blend of single or multiple fields. They are used as per the specifications to retrieve the records or data from the rows in a table. It is also feasible to use these keys to determine the relationship between tables. Keys categories include the following:

• Super Key: A set of one or more keys that would mark a record in the table uniquely.
• Candidate Key: A set of one or more fields that would be able to define a record uniquely. A table can contain several candidate keys, each of which can be a primary key.
• Primary Key: A set of one or more fields that would uniquely define a record that cannot recognize null or duplicate values in the database table.
• Alternate Key: A candidate key that does not serve as a primary key.
• Foreign Key: A field in a database table which, in another table, acts as a primary key and is used to establish a relationship between tables. The null and duplicate values will be considered.

In the RDBMS system, SQL allows users to access data. It assists you to describe the data and allows you to define the data in a database and to manipulate that relevant data. You can build and drop databases and tables with the aid of SQL commands in the DBMS. SQL provides you with the ability to use the function in the database, create a view, and stored procedure. On tables, procedures, and views, you may indeed set permissions. There are five types of SQL queries used extensively.

• Data Definition Language (DDL).
• Data Manipulation Language (DML).
• Data Control Language(DCL).
• Transaction Control Language(TCL).
• Data Query Language (DQL).

                    Top 30+ frequently asked SQL Server interview questions & answers for freshers & experienced professionals

What is DDL

The Data Definition Language helps you describe the structure or schema of the database. The following are the types of DDL commands which will be used in SQL:

1) CREATE

CREATE statements are used to determine the schema of the database structure:

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Examples:

Create database university;

Create table students;

Create view for_students;

2) DROP

Delete tables and databases from RDBMS by dropping commands.

Syntax:

DROP TABLE;

Examples:

Drop object_type object_name;

Drop database academy;

Drop table scholar;

3) ALTER
The Alters command enables you to manipulate the database structure.

Syntax:

To add a new column in the table.

ALTER TABLE table_name ADD column_name COLUMN-definition; 

To modify an existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);  

Example:

Alter table hkr99 add subject varchar;

4) TRUNCATE:
This command was used to erase all the table rows and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;  

Example:

TRUNCATE table scholars;

What is DML

The Data Manipulation Language (DML) lets you alter an instance of a database by inserting, modifying, and deleting its data. In a database, it is responsible for implementing all types of data modification. There are three fundamental constructs that allow users and database programs to fill in data and information:

In SQL, here are a few important DML commands:

• INSERT
• UPDATE
• DELETE

1) INSERT

It is an SQL query statement that is used to insert data into the row of a table.

Syntax:

INSERT INTO TABLE_NAME  (col1, col2, col3,.... col N)  

VALUES (value1, value2, value3, .... valueN);  

Or 

INSERT INTO TABLE_NAME    

VALUES (value1, value2, value3, .... valueN);  

Example:

INSERT INTO students (RollNo, FIrstName, LastName) VALUES ('60', 'Tom', Erichsen');

2) UPDATE

This command is used to update or alter the value of a table column.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

Example:

UPDATE students    SET FirstName = 'Peter', LastName= 'Thompson' WHERE StudID = 10;

3) DELETE

To delete one or more rows from a table, this command is used.

Syntax:

DELETE FROM table_name [WHERE condition];

Example:

DELETE FROM students WHERE FirstName = 'Peter';

What is DCL

DCL (Data Control Language) contains commands that are useful for giving "rights & permissions." such as GRANT and REVOKE. Other permissions govern the database system parameters.

Examples of DCL commands:

The following are the DCL Commands:

• Grant
• Revoke

1) Grant
This command is used to provide a database with user access privileges.

Syntax:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

Example:

GRANT SELECT ON Users TO'Tom'@'localhost;

2) Revoke
It is useful to back permissions from the user.

Syntax:

REVOKE privilege_nameON object_name FROM {user_name |PUBLIC |role_name}

Example:

REVOKE SELECT, UPDATE ON student FROM BCA, MCA;

What is TCL

Inside the database, the transaction control language or TCL commands deal with the transaction.

1) Commit

This command is used to save the database with all the transactions.

Syntax:

Commit;

Example:

DELETE FROM Students WHERE RollNo =25;  

COMMIT;

2) Rollback

You can undo transactions that have not already been saved to the database using the rollback command.

Syntax:

ROLLBACK;

Example:

DELETE FROM Students WHERE RollNo =25;

3) SAVEPOINT

You can set a savepoint inside a transaction with this command.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example:

SAVEPOINT RollNo;

What is DQL

To extract data from the database, the Data Query Language (DQL) is used. Only one command will be used by DQL:

SELECT:

This command assists you select an attribute based on the condition that the WHERE clause describes.

Syntax:

SELECT expressions FROM TABLES WHERE conditions; 

Example:

SELECT FirstName FROM Student WHERE RollNo > 15;

SQL Server instance

The SQL Server instance refers to the Database Engine instance. This is a copy of sqlservr.exe that can be executed on the operating system. Several system databases would be able to manage each of these instances. This means that multiple Database Engine instances can be run independently of the other instances on a computer.

You can install these instances at any time, and even after the SQL Server has been running for some time on the system. The instances allude primarily to the engine of the database and the supporting components. For client tools, they do not apply. The below diagram illustrates the overview of SQL Server 2014 instance architecture.

IMG

Subscribe to our youtube channel to get new updates..!

Subscribe

Benefits of Instances

The sky is the limit when it comes to different examples. The reasons can differ throughout various organizations. These are some of the benefits provided by SQL Server Instances.

• Functional or Load testing: Load testing corresponds to the loading of various codebases and the synchronization of load testing with the instances. The load testing must then be performed to restart all instances after they have been completed. Functional Testing allows numerous codebases to be evaluated with preallocated resources independently.
• Server Sharing: With SQL Server, a departmental solution can be considered. Several Server 1 instances can be used to share the server's resources equally. It is suitable for departmental payments.
• Server Consolidation: Multiple instances are integrated into one server here. Full SQL Server backups and restoration can be done for a single instance and retain the same features as the original server.
• Security: For third-party applications, multiple instances are very good. These require high SQL Server privileges in cases where you do not want to commit applications to the same instance.

Database Systems of Client/Server

Applications for the client-server are applications that allow the user to access data stored on the server. It does not matter to the computer system. Both of these may be workstations and may run on the same type of operating system. The servers would hold databases and the workstations would allow the users to access the database. By using some other database program, a client/server database application can be built.

• Server: The server is a component of the database-storing client/server framework. The server program, which is also part of the database management system that interprets the clients' commands, is also present. In the database, these commands are interpreted as operations.
• Client: The client's most significant task is to build a user interface. To the end-users, the computer itself is the client machine and the program is the user interface. Users will normally not be able to comprehend that the process requires a server.

Tasks of Database Management 

In this process, multiple database management activities are involved. They are as follows:

• Alteration of Configuration Settings for SQL Server: Most of the configuration settings can presently be changed through SQL Server Management Studio. The SP_CONFIGURE TSQL command also helps to modify the settings that are needed.
• Managing Information Reports for the Database Engine: Using SQL Server instances, proper management can be executed. In the framework, many generic ones are already incorporated.
• Databases Detachment and Attachment: Database detachment means that the SQL Server instance is completely removed. However, the files would be kept intact and on the file system, they would persist. When the database is connected again, the state of the database will stay intact.
• Database Objects Scripting:  Two levels of scripting features help the SQL Server task or action to be moved to a TSQL script. This scripting feature is a great way to automate the responsibilities or settings of redundant administration. Without even being an expert, strong scripts can be produced easily.
• The Database Restoration:  The most crucial task is the building backups and the recovery strategy. When making backups, it is crucial to better comprehend the recovery models associated with the database.

SQL Server Editions and Features

In many editions, SQL Server is available. Below are different editions with their characteristics:

Enterprise: It is the best edition and contains all the numerous SQL Server features available. It is intended for large organizations and administrators of businesses.

Standard: The characteristics available in this edition are lower than those of Business. When there is no need for advanced functionality, it finds use. These can be used by industries that do not require advanced features.

Workgroup: In the remote offices of large corporations, this edition discovers opportunities.

Web: This edition is used for web applications in particular.

Express: This is an entry-level edition. In addition to 1 GB of memory, only 1 CPU can be used and the maximum database capacity is 10 GB.

Developer: It is licensed to only a single user for testing, demo, and development, identical to the Enterprise edition. Without any need for reinstallation, the Developer edition can be upgraded to the Enterprise edition.

Datacenter: The recent major update for the new SQL Server 2008 R2 is the Datacenter Edition. In terms of memory, it has no limitations. it will provide assistance even for more than 25 instances.

Compact: This edition is free and it is available for the creation of mobile applications. 4 GB is the database's maximum allocated capacity.

Enterprise Evaluation: A completely functioning and free SQL Server instance is provided in the Evaluation Edition. It is perfect for studying and implementing ideas and will immediately expire after 6 months.

Business Intelligence: All of the features are incorporated in the Standard edition. PowerPivot and Power View are some advanced features that are also included. Many advanced online operations, indeed, are available.

Determining the version of SQL

If you'd like to know the version of your SQL, you have to implement the corresponding steps.

1. Press Windows Key + S to start. You're going to get the Search Box.
2. In the box, you should type "SQL Server Configuration Manager" and click Enter.
3. Then, in the top-left frame, highlight SQL Server Services.
4. You need to right-click on SQL Server (PROFXENGAGEMENT) and then click on Properties.
5. The Advanced tab should be clicked.
6. In the "Stock Keeping Unit Name" and "Version" columns, you can find the corresponding information. The column "Stock Keeping Unit Name" indicates the edition of SQL and you can also see the "Version" of the editions.

Using SQL Server

The following needs must be installed first in order to use SQL Server. Two types of installations exist:

1. Stand-alone.
2. Cluster-based.

Step 1: You need to download the Evaluation Edition from the official website of Microsoft. You will find some files depending on the bit-version selected:

ENUx86SQLFULL_x64_ENU_Core.box


ENUx86SQLFULL_x64_ENU_Install.exe

ENUx86SQLFULL_x64_ENU_Lang.box

(OR)

ENUx86SQLFULL_x86_ENU_Core.box


ENUx86SQLFULL_x86_ENU_Install.exe

ENUx86SQLFULL_x86_ENU_Lang.box

Step 2: You can choose "Install.exe" to retrieve the appropriate files from the directories you have downloaded.

Step 3: Then you can double-click on the program “SETUP”.

Step 4: On the left side of the SQL Server Installation Center screen, press Installation.

Step 5: The first preference you can find on the right-hand tab should be pressed.

Step 6:  On the following screen that is displayed, you should press "OK".

Step 7: Select the “Next” button.

Step 8: You need to select the product key and click on Next to verify it.

Step 9: You have to select the checkbox to approve the license option and then click on Next.

Step 10: You must select the option to install the SQL Server feature and select Next.

Step 11: You can select the Checkbox for Database Engine Services and then click Next.

Step 12: You should enter a named instance, provide an appropriate name, and then click the Next button.

Step 13: On the screen that is displayed, simply click on Next.

Step 14: You can select the names of the service accounts and the start-up types for the services listed and then proceed to the Collation tab.

Step 15: Make sure that the right collation type is selected and then press Next.

Step 16: You must ensure that the selection of the authentication mode and the administrators are checked, and then press Data Folders.

Step 17: You have to select the directory locations specified above and then click Next.

Step 19: Simply press the 'Next' button.

Step 20: Select the installs above and then press the Install button.

You'd be able to start using SQL Server this way.

How do I create a new SQL Server

The following steps must be implemented to build a new database on the SQL server:

Step 1: Just open the  Microsoft SQL Management Studio.

Step 2: You can connect to the database engine and use the credentials of the database administrator.

Step 3: This server node should be extended.

Step 4: You should right-click on the Databases and then select the new Database option.

Step 5: You need to enter a name and then press the OK button.

Conclusion

SQL Server is among the world's most familiar databases and can be used for multiple applications.  SQL Server helps you to simultaneously handle multiple users and ensure that the properties of the selected isolation level are observed by transactions. Locking safeguards information and internal resources that allow a single-user system to function as a multi-user system. It can be used for replication, managing users, roles, and optimization tasks other than maintaining databases and maintaining backups. Since today's virtual environment is built on data that is properly structured with the help of databases, SQL Server learning can give profitable opportunities in leading MNCs and secure a great future ahead.

Related Article:

1. KPI in Power BI
2. Looker vs Power BI
3. MSBI vs Power BI