SQL Server – All in One (Developers + DBA Guide)

SQL Server 6 comments

Microsoft SQL Server is a relational database management system that Microsoft has created. It is a database server, which is a software product whose principal role is to store and retrieve data as required by other software applications, which may run on the same computer or on a networked computer. This article collects all of the posts about SQL Server and organizes them in a logical way for novices to learn from basic to advanced topics.

You will not only have a good understanding of SQL Server after completing this course, but you will also be skilled in it.

As new posts are published, the topic links will be updated.

CHAPTER I – The Beginning
1.1. Overview

1.2. Architectures
—–1.2.1      Client-Server Architecture
—–1.2.2      Multi-Tier Architecture

1.3. DBMS & Data Models
—–1.3.1      The Journey from FMS to DBMS
—–1.3.2      Data Models
 —–1.3.3      Advantages & Limitations of Data Models

1.4. RDBMS & SQL
—–1.4.1      Basic Rules – Part-1 & Part-2
—–1.4.2      RDBMS vs SQL vs SQL Products
—–1.4.3      Normalization
—–1.4.4      Denormalization
—–1.4.5      SQL vs SQL Products

1.5. Life Cycles
—–1.5.1      Software & Database Development Life Cycles
—–1.5.2      Life Cycle Phases
—–1.5.3      Project Analysis & Requirements

CHAPTER II – MS SQL Server

2.1. Instances, Services, Installation & Configuration
—–2.1.1      Instances & Types & AWS Instances Types
—–2.1.2      Installation of Microsoft SQL Server
—–2.1.3      Configuring SQL Server
—–2.1.4      SQL Server Instances Services
—–2.1.5      Connecting through CLI
—–2.1.6      Connecting through GUI
—–2.1.7      Microsoft SQL Server Versions & Identifiers
—–2.1.8      Microsoft SQL Server Editions

2.2. Exploring SQL & SQL Server Objects
—–2.2.1      Object Explorer – Objects
—–2.2.2      Types of System Databases in SQL

2.3. Working with SQL Sub Languages
—–2.3.1      Overview
—–2.3.2      Sub-Languages

2.4  Data Definition Language
—–2.4.1      Creating Objects
—–2.4.2      Altering Objects
—————–a)  Altering Database
—————–b)  Altering Tables
—————–c)  Altering Stored Procedures and Functions
—–2.4.3      Deleting Objects
—–2.4.4      Renaming Objects
—–2.4.5      Truncating Tables

2.5  Data Manipulation Language
—–2.5.1      Overview
—–2.5.2      Data Types
—–2.5.3      Insert Statements
—–2.5.4      Insert Ignore Statements.
—–2.5.5      Update and Delete the data.

2.6  Data Query Language
—–2.6.1      Retrieving the data from tables
—–2.6.2      Retrieving the specific columns data from tables
—–2.6.3      Retrieving data using clauses and operators

2.7  Data Control Language
—–2.7.1      Overview
—–2.7.2      Permissions and Access Control

2.8  Transact Control Language
—–2.8.1      Commit,
—–2.8.2      Rollback
—–2.8.3      Savepoint.

CHAPTER III – Querying MS SQL Server

3.1  Filtering, Sorting and Grouping the data
—–3.1.1      Operators
—–3.1.2      Clauses
—–3.1.3      Conditions
—–3.1.4      Sorting the data
—–3.1.5      Grouping the data
—–3.1.6      Having By

3.2  Regularly Used Expressions
—–3.2.1      String and Arithmetic Expressions
—–3.2.2      Usage of Top
—–3.2.3      Usage of Distinct
—–3.2.4      Handling Null
—–3.2.5      Simple Case Expression
—–3.2.6      Search Case Expression

3.3  Aliasing & Computed Columns
—–3.3.1      Aliases
—–3.3.2      Physical Computed Columns
—–3.3.3      Logical Computed Columns
—–3.3.4      Naming Conventions

3.4  Sub-Queries
—–3.4.1      Sub-Queries
—–3.4.2      Correlated Sub Queries
—–3.4.3      Nested Queries

3.5 Built-in Functions
—–3.5.1      Aggregate Functions:

—–3.5.2      Analytical Functions:

—–3.5.3     Configuration Functions:

—–3.5.4     Conversion Functions:

aaa3.5.5     Data Type Functions:

——3.5.6     Date Time Functions:

aaa3.5.7     JSON Functions:

aaa3.5.8     Mathematical Functions: 

aaa3.5.9     Logical Functions:

aaa3.5.10    Ranking Functions:

aaa3.5.11   Security Functions:

      • CERTENCODED
      • PWDCOMPARE
      • CERTPRIVATEKEY
      • PWDENCRYPT
      • CURRENT_USER
      • SCHEMA_ID
      • DATABASE_PRINCIPAL_ID
      • SCHEMA_NAME
      • sys.fn_builtin_permissions
      • SESSION_USER
      • sys.fn_get_audit_file
      • SUSER_ID
      • sys.fn_my_permissions
      • SUSER_SID
      • HAS_PERMS_BY_NAME
      • SUSER_SNAME
      • IS_MEMBER
      • SYSTEM_USER
      • IS_ROLEMEMBER
      • SUSER_NAME
      • IS_SRVROLEMEMBER
      • USER_ID
      • LOGINPROPERTY
      • USER_NAME
      • ORIGINAL_LOGIN
      • PERMISSIONS

aaa3.5.12    String Functions:

aaa3.5.13    System Functions:

      • $PARTITION
      • ERROR_PROCEDURE
      • @@ERROR
      • ERROR_SEVERITY
      • @@IDENTITY
      • ERROR_STATE
      • @@PACK_RECEIVED
      • FORMATMESSAGE
      • @@ROWCOUNT
      • GET_FILESTREAM_TRANSACTION_CONTEXT
      • @@TRANCOUNT
      • GETANSINULL
      • BINARY_CHECKSUM
      • HOST_ID
      • CHECKSUM
      • HOST_NAME
      • COMPRESS
      • ISNULL
      • CONNECTIONPROPERTY
      • ISNUMERIC
      • CONTEXT_INFO
      • MIN_ACTIVE_ROWVERSION
      • CURRENT_REQUEST_ID
      • NEWID
      • CURRENT_TRANSACTION_ID
      • NEWSEQUENTIALID
      • DECOMPRESS
      • ROWCOUNT_BIG
      • ERROR_LINE
      • SESSION_CONTEXT
      • ERROR_MESSAGE
      • SESSION_ID
      • ERROR_NUMBER
      • XACT_STATE
      • SERVERPROPERTY

aaa3.5.14     Window Functions:

3.6   JOINS
aaa3.6.1      Inner Joins
aaa3.6.2      Left Outer Joins
aaa3.6.3      Right Outer Joins
aaa3.6.4      Full Outer Joins
aaa3.6.5      Self Joins
aaa3.6.6      Cross Joins
aaa3.6.7      Merge Joins
aaa3.6.8      SQL-92 vs SQL-89 Joins

3.7   Data Conversion
aaa3.7.1      Overview
aaa3.7.2      CAST
aaa3.7.3      CONVERT
aaa3.7.4      PARSE
aaa3.7.5      TRY_CAST
aaa3.7.6      TRY_PARSE
aaa3.7.7      TRY_CONVERT

3.8   XML Path
aaa3.8.1     XML in SQL Server
aaa3.8.2     XML in SQL Server – 2

CHAPTER IV – Enforcing Business Rules

4.1  Integrity Constraints
aaa4.1.1      Integrity & Types
aaa4.1.2      Entity Integrity
aaa4.1.3      Entity Integrity Constraint – Primary Key
aaa4.1.4      Unique Key
aaa4.1.5      Not Null
aaa4.1.6      Domain Integrity
aaa4.1.7      Check Constraint
aaa4.1.8      Default Constraint
aaa4.1.9      Referential Integrity
aaa4.1.10    Foreign Key

4.2  Conditional Statements
aaa4.2.1      IIF Statements
aaa4.2.2      ISNULL and COALESCE Statements
aaa4.2.3      CHOOSE function
aaa4.3.4      REPLACE function
aaa4.3.5      REPLICATE function
aaa4.3.6      IIF Statements

CHAPTER V – Performance Tuning

5.1  Overview
5.2  Indexing & Types
aaa5.2.1      Clustered – Non-clustered Index
aaa5.2.2      Creating Indexes
aaa5.2.3      Dropping Indexes
aaa5.2.4      Enabling & Disabling Indexes
aaa5.2.5      Rebuild and Reorganize Indexes
aaa5.2.6      Performance Tuning Best Practices

CHAPTER VI – Views

6.1  Introduction & Advantages of Views
aaa6.1.1  Creating a View in SQL Server
aaa6.1.2  Altering View in SQL Server
aaa6.1.3  Removing a View in SQL Server
aaa6.1.4  Fetch all the Views
aaa6.1.4  Indexed Views
aaa6.1.5  SQL Server Catalogue Views

CHAPTER VII – Synonyms

7.1  Overview
aaa7.1.1  Creating Synonyms
aaa7.1.2  Dropping Synonyms
aaa7.1.3  Referencing Synonyms in Dynamic SQL

CHAPTER VIII – Sequences
8.1  Overview
aaa8.1.1  Creating Sequences & Usage
aaa8.1.2  Using Sequence in Table
aaa8.1.3  Dropping Sequences
aaa8.1.4  Next Value For
aaa8.1.5  Modifying Sequence
aaa8.1.6  Find Last Used Sequence

CHAPTER IX – TSQL (TRANSACT SQL)

9.1     Overview
aaa9.1.1     Temporary Objects
aaa9.1.2     Temporary Tables
aaa9.1.3     Table Variables
aaa9.1.4     Common Table Expressions
aaa9.1.5     Dynamic SQL,  Dynamic SQL Part-1,  Dynamic SQL Part-2
aaa9.1.6     Isolation & Isolation Levels
aaa9.1.7     Phantom Reads
aaa9.1.8     Dirty Reads
9.10     Stored Procedures
aaa9.10.1   Usage & Benefits
aaa9.10.2   CREATE OR ALTER 
aaa9.10.3   SET NOCOUNT ON 
aaa9.10.4   Creating, Dropping, Altering the Stored Procedures
aaa9.10.5   Create or Alter
aaa9.10.6   Input & Output Parameters
aaa9.10.7   Call Stored Procedure Inside Another Stored Procedure
aaa9.10.8   Last modified date of a stored procedure
aaa9.10.9   Insert through Stored Procedures
9.11      Error Handling & Try/Catch & RAISERROR
9.12      User-Defined Functions
aaa9.12.1    Scalar Functions
aaa9.12.2    Table-Valued Functions
9.13      Triggers
aaa9.13.1    Instead of Delete Triggers
aaa9.13.2    DML Triggers & Example
aaa9.13.3    Enabling & Disabling Triggers
9.14      Cursors & Examples
9.15      Development & Advanced Features
aaa9.15.1     Pivot Table
aaa9.15.2     Looping Statements
aaa9.15.3     Common Table Expression
aaa9.15.4     Ranking Functions Using BLOB data type
aaa9.15.5     Using XML data type
aaa9.15.6     User-Defined Data Types (UDTs)
aaa9.15.7     JSON in SQL Server
aaa9.15.8     Dynamic Data Masking
aaa9.15.9     Latest Enhancements

CHAPTER X – Data Grouping & Partitioning

10.1      Overview
aaa10.1.1    Files & File Groups
aaa10.1.2    Partitioning
aaa10.1.3    Partitioning on existing tables.

CHAPTER XI – Controlling & Securing the Data

11.1      Overview
aaa11.1.1    Creating Users & Roles
aaa11.1.2    Granting & Revoking of Roles & privileges
aaa11.1.3    Managing using Management Studio

CHAPTER XII – Encryption

12.1      Overview
aaa12.1.1    Encryption
aaa12.1.2    Encryption by Certificate
aaa12.1.3    Encryption of Database
aaa12.1.4    Encryption by Semantic Key
aaa12.1.5    Encryption by Passphrase

CHAPTER XIII – Import & Export the data

13.1      Overview
aaa13.1.1    Importing & exporting data from and to flat files
aaa13.1.2    Importing & exporting data from and to flat Microsoft Excel
aaa13.1.3    BCP & Bulk-Import
aaa13.1.4    Reading the content of a file using OPENROWSET
aaa13.1.5    Importing & exporting data from and to other relational databases

CHAPTER XIV – Backup & Restoration

14.1      Overview
aaa14.1.1    Backup Methods
aaa14.1.2    Generating SQL Script from Backup
aaa14.1.3    Executing SQL Script
aaa14.1.4    Generating Change Script
aaa14.1.5    Taking database Backup
aaa14.1.6    Restoring database using backup
aaa14.1.7    Restoring a database with RESTORE option.
aaa14.1.8    Attaching and detaching of database
aaa14.1.9    Bring database from suspect mode

CHAPTER XV – Automation, Scheduling and Monitoring

15.1      Overview
aaa15.1.1     Executing SQL Script through SQL Agent
aaa15.1.2     Creating Jobs
aaa15.1.3     Creating Schedules
aaa15.1.4     Alerts
aaa15.1.5     Operators
aaa15.1.6     Configuring DB Mail
aaa15.1.7     Enabling & Disabling Jobs
aaa15.1.8     Monitoring Jobs – Job Completion Status through a Query
aaa15.1.9     Monitoring Activities / Sessions
aaa15.1.10   End the sessions
aaa15.1.11   Monitoring Performance

CHAPTER XVI – Working with DBMail

16.1      Overview
aaa16.1.1      DBMail
aaa16.1.2      Send a mail using DBMail
aaa16.1.3      Send Query Results to a file
aaa16.1.4      Send mail in HTML format

CHAPTER XVII – Disaster Recovery

17.1      Overview
aaa17.1.1    Disaster Recovery
aaa17.1.2    Recovery Models
aaa17.1.3    Log Shipping
aaa17.1.4    High Availability
aaa17.1.5    AlwaysON
aaa17.1.6    Database Snapshots

CHAPTER XVIII –Administration & Maintenance

18.1      Overview
18.2      System Databases
18.3      TempDB
18.4      DBCC Maintenance Commands
18.5      DBCC Validation Statements
18.6      DBCC Informational Statements
18.7      Trace Commands
18.8      Dynamic Management Views
18.9      Get the list of tables of a database
18.10    Get the list of stored procedures of a database
18.11    Get the list of databases of the instance
18.12    Search the specific column in a database
18.13    Get the number of rows from tables
18.14    Get the size of each table from a database
18.15    Get the list of the files from a database
18.16    Get the list of the filegroups from a database
18.17    Get the schemas, tables, columns and indexes
18.18    Get the information about the scheduled jobs
18.18    Get the information about the scheduled jobs during a specific time
18.19    Get the information about the backup and restore operations
18.20    Get the restore date-time
18.21    Setting up an alert on Missed Scheduled job
18.22    Disk space usage  & Tables Disk Space Consumption
18.23    Get the Edition, Version information about the instance
18.24    Identify the active node in the cluster.
18.25    Certificate-based Server Logins
18.26    Server Principals Login Create and Modified Date
18.27    Status of the SQL Server Agent
18.28    Find the specific column across all the tables.
18.29    SQL Server Error Log – Search
18.30    Get Stored Procedure Last Modified Date
18.31    Get Table Row Count and Used Size
18.32    Reserved Keywords as Columns
18.33    Altering Default values
18.34    Altering Identity values
18.35    Can a permanent table and a Temporary table can have the same name?
18.36    XP_CMDSHELL
18.37    Find third highest salary 
18.38    Split Alpha-Numeric String into columns
18.39    Date Format in SQL Server
18.40    Multiple Table’s Row Count in SQL Server
18.41    Sys.Objects TYPE Abbreviations
18.42    Sys Procedures 
18.43    List of Indexes with Index Type
18.44    List of indexes of a specific table

CHAPTER XIX – Report Queries Examples
19.1        Insert data from Linked Server
19.2        Hourly Report
19.3        Monthly Report
19.4        Daily Report
19.5        Working with Duplicate Records
19.6        Insert Data through stored procedure
19.7        Adding multiple columns with a default value.
19.8        Multiple ways to find a missing serial number.
19.9        Best usage of NTILE function.
19.10      Handling JSON and Unstructured Data in SQL Server
19.11      JSON in SQL Server
19.12      Hijri Date in SQL Server
19.13      Import from Excel – Named Sheet
19.14      Data Sampling – TOP, LIMIT, ROWNUM and DENSE_RANK
19.15      Creating a table and a view based on a select statement.
19.16      Generate multiple CSV files from SQL
19.20      Convert delimited data into Columns.
19.21      Searching the columns that has multiple lines.
19.22      Searching Unicode Strings
19.23      INSERT 100,000 random rows
19.24      Auto Update the default date

CHAPTER XX – Data Samples
20.1       Oracle’s EMP and DEPT tables and data.
20.2       SQL Sample Project
20.3       SQL Assignment-1
20.4       Complete TSQL with Examples
20.5       SQL Server Sample Schema, Tables – For Practice
20.6       Problem Scenarios – For Practice
20.7       Answers to Problem Scenarios – For Practice
20.8       SQL Server Training

CHAPTER XXI – Interview Questions
21.1      Interview Questions-1
21.2      Interview Questions-2
21.3      Interview Questions-3
21.4      Interview Questions-4
21.5      Interview Questions-5
21.6      Interview Questions-6
21.7      Interview Questions-7
21.8      Interview Questions-8