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

Group by in MS sql server

Group By In SQL Server we have got many aggregate functions. Examples

1. Count()

2. Sum() 3. avg() 4. Min() 5. Max()

The GROUP BY statement groups rows that have the same values into summary rows,. It is often used in conjunction with one or more aggregate functions.

I want an MS SQL query, which gives total salaries paid by City. show below

Query for display total salaries by city: We are getting apply SUM() aggregate function on Salary column, and grouping by city column.

Select City, SUM(Salary) as TotalSalary from tblEmployee Group by City

Note: If you leave, the group by clause and try to execute the query, then you get an error - Column 'tblEmployee. City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Read More

Joins in SQL server

Joins in SQL servers are used to query for retrieve data from 2 or more related tables. A Join clause is used to combine rows from one , two or more tables, based on a related column.

In SQL, a join operation combines rows from two or more tables based on a related column between them. The result of a join is a new table that contains only the rows where the join condition is met. There are several types of joins in SQL, including:

There are three types of JOINS.

1. CROSS JOIN

2. INNER JOIN 

3. OUTER JOIN 

Outer Joins are  3 types

1. Left Join or Left Outer Join

2. Right Join or Right Outer Join

3. Full Join or Full Outer Join

• INNER JOIN: Returns only the rows where there is a match in both tables.

• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values will be returned for the right table's columns.

• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values will be returned for the left table's columns.

• FULL OUTER JOIN: Returns all rows from both tables, and for the non-matching rows, NULL values will be returned for the columns of the table that does not have a matching row.

The join condition is specified in the ON clause of the join statement, and it determines which rows from the two tables are combined.

Read More

Advanced or intelligent joins in sql server,

Considers Employees (tblEmployee) and Departments (tblDepartment) tables

Employee Table (tblEmployee)


Departments Table (tblDepartment)


How to retrieve only the non-matching rows from the left table. The output should be as  below:


Query:
SELECT       Name, Gender, Salary, DepartmentName
FROM           tblEmployee E
LEFT JOIN   tblDepartment D
ON                 E.DepartmentId = D.Id
WHERE        D.Id IS NULL



How to retrieve only non-matching rows from the right table


Query:
SELECT         Name, Gender, Salary, DepartmentName
FROM             tblEmployee E
RIGHT JOIN    tblDepartment D
ON                   E.DepartmentId = D.Id
WHERE          E.DepartmentId IS NULL



How to retrieve only non-matching rows from both the left and right table. Matching rows should be eliminated.


Query:
SELECT         Name, Gender, Salary, DepartmentName
FROM              tblEmployee E
FULL JOIN      tblDepartment D
ON                   E.DepartmentId = D.Id
WHERE          E.DepartmentId IS NULL
OR                   D.Id IS NULL

Read More

Self join in sql server

Before post, we have seen joining 2 different tables - tblEmployees and tblDepartments. Have you ever thought of a need to join a table by itself? Consider tblEmployees table shown below.

Write a query that gives the following result.



Self Join Query:
A MANAGER is also an EMPLOYEE. Both the EMPLOYEE and MANAGER rows are present in the same table. Here we are joining tblEmployee with itself using different alias names, E for Employee and M for Manager. We are using LEFT JOIN, to get the rows with ManagerId NULL. You can see in the output TODD's record is also retrieved, but the MANAGER is NULL. If you replace LEFT JOIN with INNER JOIN, you will not get TODD's record.
Select E.Name as Employee, M.Name as Manager
from tblEmployee E
Left Join tblEmployee M
On E.ManagerId = M.EmployeeId


In short, joining a table with itself is called SELF JOIN. SELF JOIN is not a different type of JOIN. It can be classified under any type of JOIN - INNER, OUTER or CROSS Joins. The above query is, LEFT OUTER SELF Join.

Inner Self Join tblEmployee table:
Select E.Name as Employee, M.Name as Manager
from tblEmployee E
Inner Join tblEmployee M
On E.ManagerId = M.EmployeeId

Cross Self Join tblEmployee table:
Select E.Name as Employee, M.Name as Manager
from tblEmployee
Cross Join tblEmployee

Read More

Different ways to replace NULL in sql server

 

Consider the Employees table below. 

Next POst, we have learned to write a LEFT OUTER SELF JOIN query, which produced the following output.



In the output, the MANAGER column, for Todd's rows is NULL. I want to replace the NULL value, with 'No Manager'

Replacing NULL value using ISNULL() function: We are passing 2 parameters to IsNull() function. If M.Name returns NULL, then the 'No Manager' string is used as the replacement value.
SELECT E.Name as Employee, ISNULL(M.Name,'No Manager') as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

Replacing NULL value using CASE Statement:
SELECT E.Name as Employee, CASE WHEN M.Name IS NULL THEN 'No Manager' 
ELSE M.Name END as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

Replacing NULL value using COALESCE() function: COALESCE() function, returns the first NON NULL value.
SELECT E.Name as Employee, COALESCE(M.Name, 'No Manager') as Manager
FROM tblEmployee E
LEFT JOIN tblEmployee M
ON E.ManagerID = M.EmployeeID

We will discuss COALESCE() function in detail, in the next postEmail ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

Read More

Stored procedures in MS Sql server

Stored Procedures:- A stored Procedures is a group of a T-SQL statement. If you have a situation,  where you write the same query over and over again, You can save that specific query as a Stored Procedure and call it just by its name.

Syntax:- Create procedure procedures name 

As begin

SQL statement 

End

Execute a Stored Procedures:- 

exec procedure name

Stored Procedures Example:- 

The following SQL statement creates a stored procedure named "SelectAllEmployee" that selects all records from the "tblemployee" table

Create proc SelectAllEmployee 

as

Begin

Select * from tblemployee

End

Emp_Name Emp_ID Address

Lalita     2         Noida

Ankit     1         Noida

Ankita     3        Delhi

Anku     4         US

XYZ     5         UK

Stored Procedure With Multiple Parameters:-

Multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

The following SQL statement creates a stored procedure that selects Tblemployee from a particular address with a particular PostalCode from the "tblemployee" table:

Example

CREATE PROC SelectAllEmployee @address nvarchar(30), @Emp_Id nvarchar(10)

AS

SELECT * FROM tblemployeeWHERE address = @address AND Emp_Id = @Emp_Id 

GO;

Execute the stored procedure above as follows:

Example

EXEC SelectAllEmployee @address = 'London', @Emp_Id  = '01';

To view the stored procedure in text:

sp_helptext stored_proc name

To change the stored procedure use,

alter procedure proc_name 

as

begin

SQL statement 

end

Drop Procedure 

Drop procedure proc_name

To Encrypt the text of the  Store Procedure 

CREATE PROC SelectAllEmployee @address nvarchar(30), @Emp_Id nvarchar(10)

with encryption 

AS

begin

SELECT * FROM tblemployeeWHERE address = @address AND Emp_Id = @Emp_Id 

GO;

Read More