What is SQL Server? Introduction, History, Types, Versions

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is primarily designed and developed to compete with MySQL and Oracle database. SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact-SQL). T-SQL is a Microsoft [ [ ...]

Database Systems: An Overview

A database system is an overall collection of different database software components and databases containing the following parts: Database application programs Client components Database server(s) Databases A database application program is special-purpose software that is designed and implemented by users or by third-party software companies. In contrast, client components are general-purpose database software designed and [ [ ...]

Relational Database Systems: An Introduction

1. Relational Database Systems The component of Microsoft SQL Server called the Database Engine is a relational database system. In contrast to earlier database systems (network and hierarchical), relational database systems are based upon the relational data model, which has a strong mathematical background. The central concept of the relational data model is a relation—that [ [ ...]

SQL Server Editions and Management Tools

As you plan your installation, you need to know which SQL Server editions exist so that you can choose the most appropriate one. This section describes your options and then introduces the various management tools that you can use in all SQL Server editions. 1. SQL Server Editions Microsoft supports several editions of SQL Server, [ [ ...]

Planning Phase for SQL Server: General Recommendations

During the installation process, you have to make many choices. As a general guideline, it is best to familiarize yourself with the effects of each option before installing your system. At the beginning, you should answer the following questions: Which operating system will be used? Which SQL Server components should be installed? Where will the [ [ ...]

Installation of SQL Server on Windows or Ubuntu

The following sections describe installation of SQL Server on two different operating systems: Windows and Ubuntu. 1. Planning the Installation on Windows To start the SQL Server installation, mount the ISO image of the SQL Server software. After that, click the Setup.exe file. SQL Server contains a tool called Installation Center (see Figure 2-2). This [ [ ...]

SQL Server Management Studio

The Database Engine provides various tools that are used for different purposes, such as system installation, configuration, auditing, and performance tuning. (All these tools will be discussed in subsequent chapters of this book.) The administrator’s primary tool for interacting with the system on Windows platforms is SQL Server Management Studio. Both administrators and end users [ [ ...]

Using SQL Server Management Studio with the Database Engine

SQL Server Management Studio has two main purposes: Administration of the database servers Management of database objects The following sections describe these functions of SQL Server Management Studio. 1. Administering Database Servers The administrative tasks that you can perform by using SQL Server Management Studio are, among others, the following: Register servers Connect to a [ [ ...]

Authoring Activities Using SQL Server Management Studio

SQL Server Management Studio gives you a complete authoring environment for all types of queries. You can create, save, load, and edit queries. SSMS allows you to work on queries without being connected to a particular server. This tool also gives you the option of developing your queries with different projects. The authoring capabilities are [ [ ...]

Azure Data Studio for SQL Server

Prior to SQL Server 2017, the Database Engine was supported by only one platform—Windows. In SQL Server 2017, Microsoft added support for other operating systems, namely Linux platforms. Therefore, since SQL Server 2017, the database system can also be used with the Linux operating system and its derivatives. Support for Linux requires another front-end tool, [ [ ...]

SQL Server Management Studio vs. Azure Data Studio

The following two lists give general suggestions to help you decide whether to use SSMS or Azure Data Studio. Use SQL Server Management Studio if you: Spend most of your time on database administration tasks Perform sophisticated administrative tasks Perform security management, including user management, vulnerability assessment, and configuration of security features Need to make [ [ ...]

SQL Server Components: SQL’s Basic Objects

The language of the Database Engine, Transact-SQL, has the same basic features as other common programming languages: Literal values (also called constants) Identifiers Delimiters Comments Reserved keywords The following sections describe these features. 1. Literal Values A literal value is, for example, an alphanumerical, hexadecimal, or numeric constant. A string constant contains one or more [ [ ...]

SQL Server Components:Data Types

All the data values of a column must be of the same data type. (The only exception specifies the values of the SQL_VARIANT data type.) Transact-SQL uses different data types, which can be categorized as follows: Numeric data types Character data types Temporal (date and/or time) data types Miscellaneous data types The following sections describe [ [ ...]

SQL Server Components:Transact-SQL Functions

Transact-SQL functions can be either aggregate functions or scalar functions. The following sections describe these function types. 1. Aggregate Functions Aggregate functions are applied to a group of data values from a column. Aggregate functions always return a single value. Transact-SQL supports several groups of aggregate functions: Convenient aggregate functions Statistical aggregate functions User-defined aggregate [ [ ...]

SQL Server Components:Scalar Operators

Scalar operators are used for operations with scalar values. Transact-SQL supports numeric and Boolean operators as well as concatenation. There are unary and binary arithmetic operators. Unary operators are + and – (as signs). Binary arithmetic operators are +, -, *, /, and %. (The first four binary operators have their respective mathematical meanings, whereas [ [ ...]

SQL Server Components:NULL Values

A NULL value is a special value that may be assigned to a column. This value normally is used when information in a column is unknown or not applicable. For example, in the case of an unknown home telephone number for a company’s employee, it is recommended that the NULL value be assigned to the [ [ ...]

Creating Database Objects with SQL Server

The organization of a database involves many different objects. All objects of a database are either physical or logical. The physical objects are related to the organization of the data on the physical device (disk). The Database Engine’s physical objects are files and filegroups. Logical objects represent a user’s view of a database. Databases, tables, [ [ ...]

Modifying Database Objects with SQL Server

The Transact-SQL language supports changing the structure of the following database objects, among others: Database Table Stored procedure View Schema Trigger The following two sections describe, in turn, how you can alter a database and a table. The modification of the structure of each of the last four database objects is described in Chapters 8, [ [ ...]

Removing Database Objects with SQL Server

All Transact-SQL statements that are used to remove a database object have the following general form: DROP object_type object_name Each CREATE object statement has the corresponding DROP object statement. The statement DROP DATABASE databasel {,  …} removes one or more databases. This means that all traces of the database are removed from your database system. [ [ ...]

SELECT Statement: Its Clauses and Functions in SQL Server

The Transact-SQL language has one basic statement for retrieving information from a database: the SELECT statement. With this statement, it is possible to query information from one or more tables of a database (or even from multiple databases). The result of a SELECT statement is another table, also known as a result set. The simplest [ [ ...]