Connecting to SQL Server using SSMS,Install Sql server 2019 Step by Step

Introduction

In this post, we'll go over step-by-step instructions for installing Microsoft SQL Server on Windows computers. We'll use Windows Server 2019 and SQL Server 2019 Developer edition for this tutorial.

Pre-requisites

Using a Windows computer with a 2 core and 4 GB of RAM

Download the 2019 Developer Edition of SQL Server.

Download the SQL Server installation files from the official website first.

First, install Microsoft SQL Server 2019 on your machine.

Let start with how to install SQL Server.

1. Download Microsoft SQL Server from the below url.

https://www.microsoft.com/en-au/sql-server/sql-server-downloads

2. After download click sqlexpress.exe.

Click on the Installation page and then on ‘New SQL Server stand-alone installation or add features to an existing installation’

Select Evaluation and click the Next Button.

    

Click the Next button.

Wait few min. then click next.

Select the features in the Feature Selection box as seen in the screenshot below. You can alter the installation location for a SQL Server instance, but I'll go with the default. Please select a feature and then click the Next button.

You can choose to install a default or named instance name. If a default instance is already present on the machine, we can only install the named instance. Provide an appropriate name for the named instance. For example, I specified SQL 2019

Note

Standard Instance

A client does not need to give the name of the instance when connecting to SQL Server when it is installed in the default instance. Only the server name needs to be known by the client. HARDIK-PC, as an illustration. 

Known instance 

The network name of the computer and the instance you choose during installation together identify a named instance. When connecting, the client must include both the server name and the instance name. HARDIK-PC/MSSQLSERVER, as an illustration.

In the next page, Server configuration, you can specify service account to use for SQL Server services along with the Collation

If we want to change the collation, click on customize and choose the required collation.

"SQL Server's correlations include sorting criteria, case and accent sensitivity, and other properties for your data. The code page and related characters that can be represented for character data types like char and varchar are determined by collations used with those data types. – Microsoft”

Specify Authentication modes (Windows or Mixed mode) and add users to have SQL Server administrators.

The Database Engine Configuration window will appear after that. Select Mixed Mode under Authentication Mode in the Server Configuration tab, and then enter a strong password. Your current Windows user should already be automatically inserted in the "Specify SQL Server administrators" section. Alternatively, select the Add Current User option.

Click on Data Directories and we can specify the data, log files, backup directories here.

Place database files and backup files in the locations you choose in the Data Directories tab. By default, it saves all of the files to the C drive, however it is not advisable to store database files on an OS drive because we risk losing our data if an OS-related problem arises. I therefore select my local machine's D drive.

Click on TempDB to configure the TempDB configurations. We will move with the default configuration suggested by the installer.

There are configurations for the temporary database file in the TempDB tab (s). There are certain recommended configuration settings for the locations, number, and size of temporary database files. The number of logical processors should ideally match the number of TempDB data files. I therefore have a lot of files to 2. Here is a fantastic post on TempDB best practises if you're interested in learning more.

 

We won't be using this feature, therefore keep the checkbox unchecked under the FILESTREAM tab. These huge files, photos, or documents can be stored directly on the file system using SQL Server's FILESTREAM feature. For additional details, click on Next button.

We get an overview of the SQL Server configurations from the Ready to install page. Review the information and click on next to start the installation process.

Once the installation is completed, launch SSMS 18.0 preview to connect with SQL Server.

Congratulations! We have successfully installed SQL Server 2019 Developer edition on Windows machine. Next, you can install SQL Server Management Studio to connect SQL Server and query SQL databases. Please follow below steps to install SQL Server Management Studio.

SSMS:-

SQL Server Management Studio is a client tool not the server by itself.

Read More

Creating altering and dropping a database

 Creating database From GUI:-

Open SQL Server Management and right-click of database and click new database options

Read More

Creating and working with table

The aim of this post is to create tblperson and tblgender tables and establish primary key and foreign key constraints.

Read More

Adding A default constraints

A column default can be specified using default constraints. The default constraint is used to insert a default value into a column. The default value will be added to all new record, if no other values is specified, including null.

Altering an existing column to add a default constraint

Syntax:-

Alter table table_name add constraint (constraints_name) default  (default_value) for (existing column name)

Query:-

Alter table tblemployee add constraint df_tblgender_gnderid default 3 for genderId

NOTE 

Tblemployee  = table name

Df_tblgender_gnderid = default constraint 

3= Default value

Read More

Cascading referential integrity constraint

Cascading referential integrity constraint allow to define the action MS sql server should taken when a user attempt to delete or update a key to which exiting foreign key points.

Read More

Adding a check Constraint

 Check Constraint:-  Check Constraint is used to limit the range of the values, Which can be entered into a column.

The general formula for check constraint in sql server 

Alter tabla (tablname) add constraint (constraint name) check (Boolean expression )

Read More

Identity Column in SQL Server

What is an Identity Column?

If a column is marked as an identity column, then the value for this column are automatically generated, when you inserted a new row in the table.

Create an Identity column 

Create a table tblemployee, this statement marks PersonId as an identity column with seed = 1 and Identity Increment = 1. Seed and Increment values are optional. If you do not specify the identity and seed they both default to 

Read More

How to get the last generated identity column value in SQL Server

Read More

Unique key constraint

 

We use the UNIQUE constraint to enforce the uniqueness of a column i.e. the column should not allow any duplicate values. We can add a unique constraint through the designer or using a query.

To add a unique constraint using MS SQL Server Management Studio Designer:

1. Right click on the table and select Design option.

2. Right click on the column and select Indexes/Keys option..

3. Click Add option

4. For Columns, select the column name you want to be unique.

5. For Type, choose Unique Key.

6. Click Close, Save the table.

To create the unique key using a query:
Alter Table Table_Name
Add Constraint Constraint_Name Unique(Column_Name)

Used to implement both primary keys and unique keys, a column's uniqueness. So, when do you choose each other? A table can have only one primary key. If you want to apply exclusivity to 2 or more columns, we use the unique key constraint

What is the difference between a primary key constraint and a specific key constraint? This question is mostly asked in interviews.

1. One table can have only one primary key, but more than one unique key.

2. The primary key does not allow taps, where the unique key allows a tap

Hitch up

1. Right click on the constraint and delete.

or

2. Using a Query

Alter table tblPerson

Drop Constant UQ_tblPerson_Email

Read More

Select statement in MS sql server

 

SELECT Column_List

FROM Table_Name

If you want to select all the data(column), you can also use (* ) For better performance use the column list, instead of using (*.)

SELECT * FROM Table_Name

To Select distinct rows use DISTINCT keyword

SELECT DISTINCT Column_List FROM Table_Name

Example: Select distinct city from tblPerson

Filtering rows with WHERE clause

SELECT Column_List FROM Table_Name  WHERE Filter_Condition

Example: Select Name, Email from tblemployee where City = 'London'

Note: Text values ​​must exist in single quotes, but are not required for numeric values.

Read More