How to Download & Install SQL Server Tutorial? – Practical Guide For FREE

Last updated on 19th Jul 2020, Blog, Tutorials

SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet). Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

SQL Server Architecture

MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a request. The SQL Server accepts, processes and replies to the request with processed data. Let’s discuss in detail the entire architecture shown below:

As the below Diagram depicts there are three major components in SQL Server Architecture:

• Protocol Layer
• Relational Engine
• Storage Engine

SQL Server Indexes

Indexes are special data structures associated with tables or views that help speed up the query. SQL Server provides two types of indexes: clustered index and non-clustered index.

In this section, you will learn everything you need to know about indexes to come up with a good index strategy and optimize your queries.

Clustered indexes 
• introduction to clustered indexes and learn how to create clustered indexes for tables.
Create nonclustered indexes 
• learn how to create non-clustered indexes using the CREATE INDEX statement.
Rename indexes 
• replace the current index name with the new name using sp_rename stored procedure and SQL Server Management Studio.
Disable indexes 
• show you how to disable indexes of a table to make the indexes ineffective.
Enable indexes 
• learn various statements to enable one or all indexes on a table.
Unique indexes 
• enforce the uniqueness of values in one or more columns.
Drop indexes 
• describe how to drop indexes from one or more tables.
Indexes with included columns 
• describe how to add non-key columns to a non clustered index to improve the speed of queries.
Filtered indexes 
•  learn how to create an index on a portion of rows in a table.
Indexes on computed columns
• walk you through how to simulate function-based indexes using the indexes on computed columns.
SQL Server User-defined Functions
• In this section, you will learn about SQL Server user-defined functions including scalar-valued functions which return a single value and table-valued function which return rows of data.
• The SQL Server user-defined functions help you simplify your development by encapsulating complex business logic and make them available for reuse in every query.

User-defined scalar functions 
• cover the user-defined scalar functions that allow you to encapsulate complex formula or business logic and reuse them in every query.
Table variables 
• learn how to use table variables as a return value of user-defined functions.
Table-valued functions
• introduce you to inline table-valued function and multi-statement table-valued function to develop user-defined functions that return data of table types.
Removing user-defined functions 
• learn how to drop one or more existing user-defined functions from the database.

How to download SQL Server Setup

Step1): GotoURL: https://www.microsoft.com/en-in/sql-server/sql-server-downloads

Microsoft provides two specialized free editions to work on MS SQL server:

1.   Developer 

It has all feature which MS SQL server offers but we cannot use it in production. From the learning perspective, is it an ideal candidate to start.

2.   Express

This is also a free version but with the limited set of features with no business intelligence applications.

We will select the Developer edition for installation.

Step 2) Click on “Download now”

We will get set up as ‘SQLServer2017-SSEI-Dev.exe’.

How to Install SQL Server

Once the download completes, you double-click the file SQLServer2017-SSEI-Dev.exe to launch the installer.

1. The installer asks you to select the installation type, choose the Custom installation type allows you to step through the SQL Server installation wizard and select the features that you want to install.

2. Specify the folder for storing the installation files that the installer will download, then click the Install button.

3. The installer starts downloading the install package for a while.

4. Once the download completes, open the folder that stores the install package and double-click the SETUP.exe file.

5. The following window displays; select the installation option on the left.

6. Click the first link to launch a wizard to install SQL Server 2017.

7. Specify the edition that you want to install, select Developer edition and click the Next button.

8. Select the “I accept the license terms.” and click the Next button.

9.  Check the “Use Microsoft Update to check for updates (recommended)” to get the security and other important updates for the SQL Server and click the Next button.

10. The installation checks for the prerequisites before installation. If no error found, click the Next button.

11. Select the features that you want to install. For now, you just need the Database Engine Services, just check the checkbox and click the Next button to continue

12. Specify the name and install ID for the instance of the SQL Server and click the Next button.

13.  Specify the service account and collation configuration. Just use the default configuration and click the Next button.

14. Specify the database engine security mode. First, choose Mixed Mode. Next, enter the password for the SQL Server system administrator (sa) account. Then, re-enter the same password to confirm it. After that, click the Add Current User button. Finally, click the Next button.

15. Verify the SQL Server 2017 features to be installed:

16. The installer starts the installation process

17. Once it completes, the following window displays. Click the OK button.

18. Click the Close button to complete the installation

Congratulation!  you have successfully installed SQL Server Developer Edition.

Conclusion

In addition to one or more data files, every database in a SQL Server instance has one or more log files that keep track of changes to that database. (Remember that database snapshots don’t have log files because no changes are ever made directly to a snapshot.) SQL Server uses the transaction log to guarantee consistency of your data, at both a logical and a physical level. Also, administrators can back up the transaction log to make restoring a database more efficient. Administrators or database owners can also set a database’s recovery model to determine the level of detail stored in the transaction log.