Introduction

This blog aims beginners on how easily they can learn SQL Server step by step. We are trying to cover most of the topics in a simple way with lots of examples and necessary explanations. Hope you will enjoy this series and feel free to share the feedback in comment section.

Audience

This tutorial is designed for all those readers who want to learn the fundamentals of SQL Server and put it into practice. For beginners, it helps a step by step or stairway to learn SQL Server an d for advanced and experienced, it helps to refresh the points with examples.

Prerequisites

As this is designed for beginners, there are only two prerequisites are expected.

1. Interest to learn SQL Server

2. If you have experience in other programming language, sometimes you may need to unlearn few things and learn new things to better understanding the concepts of database management.

It is good to have SQL Server installed on your computer, as it might assist you in executing the examples yourself and get to know how it works.

Table of contents

Microsoft SQL Server – A bit of History & Introduction

A bit of History & Introduction

SQL Server – System Databases

1. Master
2. Msdb
3. Model
4. TEMPDB

SQL Server – Constraints

Constraints in SQL server defines the rules and restrictions to a column or multiple columns that enforces the integrity and reliability of the data in the specified column(s). Constraints can be defined at column level or table level in SQL Server. It can be specified while creating a table or even later time. However, once the constraints are specifying for an existing column, it will validate the rule defined by the constraints on the existing data. There are different types of constraints as below.

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

SQL Server – String Functions

• QUOTENAME
• String_split
• String_AGG
• LEN & DATALENGTH
• LEFT & RIGHT
• CHARINDEX
• SOUNDEX & DIFFERENCE
• STR
• UPPER & LOWER
• REPLACE, REPLICATE & REVERSE
• CONCAT_WS
• CAST & CONVERT

SQL Server – Global Variables

Global variables have values set by the database server.  Users cannot create global variables, and cannot update the values of global variables directly. There are two type global variables – connection specific & server specific. eg: @@identity holds connection-specific information, Otherwise, variables such as @@connections, have values that are common to all connections. Let us quickly see some of global variables as below.

• @@CONNECTIONS & @@MAX_CONNECTIONS
• @@ERROR & @@TOTAL_ERRORS
• @@SERVERNAME, @@SERVICENAME & @@REMSERVER

SQL Server – Date Time Functions

• CURRENT_TIMESTAMP
• CURRENT_TIMESTAMP, GETDATE(), GATUTCDATE() & SYSDATETIME()
• CALENDAR Table in SQL Server

SQL Server – Window Functions

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

SQL Server – Operators

• ANY, ALL & SOME

SQL Server – System Functions

• PARSENAME
• UPDATE
• ISNULL & COALESCE
• NULLIF
• IIF

SQL Server – Number Functions

• COUNT() & COUNT_BIG()

SQL Server – System Procedures

• sp_procoption
• xp_servicecontrol
• sp_settriggerorder
• sp_databases

SQL Server – Other Topics

• Computed Columns
• Stairway to Partitioning
• XACT_ABORT – Exception Handling
• Change Data Capture (CDC)

SQL Azure

• Azure Data Studio

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!