Microsoft SQL Server facts for kids

Microsoft SQL Server is a special computer program made by Microsoft. It helps store and organize lots of information. Think of it like a super-smart digital filing cabinet for businesses and websites.

This program is a type of "database server". Its main job is to keep data safe and give it back when other programs ask for it. These other programs can be on the same computer or on a different one across the internet. Microsoft makes many versions of SQL Server. Each version is for different needs, from small apps to huge websites used by many people at once.

What is Microsoft SQL Server?

Microsoft SQL Server is a relational database management system. This means it stores data in tables that are linked together. It uses a language called SQL (Structured Query Language) to manage and find information.

The Story of SQL Server

The first version of Microsoft SQL Server came out in 1989. It was called SQL Server 1.0 and worked on an older computer system called OS/2. Its name tells you what it does: it's a server program that answers questions (or "queries") using the SQL language.

Key Moments in SQL Server History

• In 1989, Microsoft worked with other companies to bring SQL Server to OS/2.
• SQL Server 4.2 came out in 1993 for Windows NT. This was a big step.
• In 1995, SQL Server 6.0 was released. Microsoft started developing it on its own.
• SQL Server 7.0, released in 1998, was a major rewrite. The code was changed from C to C++.
• SQL Server 2005, released in 2005, finished updating the original code.
• SQL Server 2012, released in 2012, added new ways to store data in memory faster.
• SQL Server 2017, released in 2017, could now run on Linux computers.
• SQL Server 2019, released in 2019, added features for big data and smart databases.
• The latest version, SQL Server 2022, was released in 2022.

Current Supported Versions

As of 2024, Microsoft still supports these versions:

• SQL Server 2014
• SQL Server 2016
• SQL Server 2017
• SQL Server 2019
• SQL Server 2022

From SQL Server 2016 onwards, the program needs a 64-bit processor. It also needs a processor speed of at least 1.4 GHz. A speed of 2.0 GHz or faster is recommended for best performance.

Different Versions of SQL Server

Microsoft offers SQL Server in many different versions. Each version has different features and is made for different users.

Main Versions

• Enterprise: This is the most complete version. It has the main database features and extra tools. It can handle huge databases and lots of memory.
• Standard: This version includes the main database features. It has fewer advanced options than the Enterprise version. For example, it doesn't have some high-availability features.
• Web: This version is made for web hosting companies. It's a cheaper option for running websites.
• Business Intelligence: This version helps businesses understand their data better. It includes tools for analyzing information and creating reports.
• Express: This is a smaller, free version of SQL Server. It's good for learning or for small applications. It has some limits, like using only one processor and a maximum database size of 10 GB.

Special Versions

• Azure: This is the cloud-based version of SQL Server. It runs on Microsoft Azure, which means you use it over the internet without installing it on your own computer.
• Compact (SQL CE): This is a tiny database program. It's designed to be built into other applications. It's very small but has fewer features than the other versions.
• Developer: This version has all the features of the Enterprise Edition. However, it's only for testing and developing new programs, not for running live systems. Microsoft made this version free in 2016.
• LocalDB: This is a simple version of SQL Server for app developers. It's easy to set up and use as a small, built-in database.

How SQL Server Works

SQL Server uses a special language called Tabular Data Stream (TDS) to talk to other programs. This language helps send data between the server and a client program. TDS can work over different network connections like TCP/IP.

Storing Information in SQL Server

Data in SQL Server is kept in "databases." A database is a collection of tables, like spreadsheets. Each table has columns that hold different types of data, such as numbers, text, or dates.

SQL Server can store many types of data. It also lets users create their own custom data types. A SQL Server database can hold a huge number of objects and can be spread across many files.

How Data is Organized

The storage space in a database is divided into small blocks called "pages." Each page is 8 KB in size. A page is the basic unit for reading and writing data. SQL Server also groups pages into "extents," which are 8 pages long.

A single row of data in a table cannot be larger than 8 KB. If a row has very large text or binary data, that data is stored on separate pages. The main row then just has a pointer to where the large data is located.

Managing Memory (Buffer Management)

SQL Server keeps copies of frequently used pages in your computer's RAM (memory). This is called the "buffer cache." It helps the server work faster because it doesn't have to read from the disk every time. The "Buffer Manager" handles this cache. When you read or write data, it first goes into the buffer cache. The data is only written to the disk later, to save time.

Handling Multiple Users (Concurrency and Locking)

Many people can use the same SQL Server database at the same time. To make sure data stays correct, SQL Server has ways to control who can access what.

One way is using "locks." If someone is changing data, SQL Server puts an "exclusive lock" on it. This means no one else can change or even read that data until the first person is done. If someone is just reading data, SQL Server uses a "shared lock." Many people can read data with a shared lock, but no one can change it.

SQL Server also tries to prevent "deadlocks." A deadlock happens when two users are waiting for each other to finish. SQL Server can detect this and will stop one of the users to fix the problem.

Another way is "optimistic concurrency control." Instead of locking, when data is changed, SQL Server creates a new version of that data. Other users can still work with the older version while the new one is being created.

Getting and Using Data

The main way to get data from SQL Server is by asking a "query." You use a special version of SQL called T-SQL to write these queries. T-SQL is a language that tells the server what data you want.

How Queries Work

When SQL Server gets a query, it figures out the best way to find the data. This is called creating a "query plan." There might be many ways to get the same data, but SQL Server chooses the fastest one. This process is called "query optimization."

Once a query plan is made, SQL Server saves it for a while. If the same query is asked again, it can use the saved plan, which makes things faster.

Stored Procedures

SQL Server also lets you create "stored procedures." These are like saved T-SQL queries that live on the server. You can give them information (input parameters) and they can give you back results (output parameters). Using stored procedures can make things faster and reduce network traffic.

T-SQL Language

T-SQL (Transact-SQL) is Microsoft's special version of the SQL language. It has extra commands for managing SQL Server, like setting up databases, adding data, and checking how the server is doing. Client programs send T-SQL commands to the server to get things done.

SQL Server Native Client

SQL Server Native Client is a special library that helps programs connect to SQL Server. It makes sure programs can use all the features of SQL Server, like handling different data types and secure connections.

SQL CLR

SQL Server 2005 introduced a feature called SQL CLR. This lets people write stored procedures and other database code using programming languages like C# and VB.NET. This means developers can use familiar tools to build powerful database features.

Extra Services for SQL Server

SQL Server also comes with many extra services. These are not needed for the basic database to work, but they add useful features.

Machine Learning Services

These services let you do machine learning and data analysis right inside SQL Server. This means you don't have to move your data to another computer. You can use languages like R and Python to build smart models.

Service Broker

Service Broker helps different parts of SQL Server applications talk to each other. It sends messages reliably between them, making sure tasks are completed correctly.

Replication Services

SQL Server Replication Services help copy and sync database information. This means you can have the same data on different servers. There are three main types:

• Transaction replication: Changes are sent almost instantly to other databases.
• Merge replication: Changes from different places are tracked and synced together.
• Snapshot replication: A full copy of the database is sent at one time.

Reporting Services

SQL Server Reporting Services (SSRS) helps create reports from your database data. You can design reports using tools like Microsoft Visual Studio. These reports can then be saved in many formats, like PDF or Excel.

Integration Services

SQL Server Integration Services (SSIS) helps move and change data. It's used for things like bringing data from different sources, cleaning it up, and putting it into a data warehouse.

Full Text Search Service

The SQL Server Full Text Search service architecture

This service helps you search for words inside large blocks of text stored in your database. It's much smarter than a simple search. It can find words even if they are spelled slightly differently or are in a different tense. It also ranks how good the search results are.

SQLCMD

SQLCMD is a command-line tool that comes with SQL Server. It lets you type SQL commands directly into a command window. You can also use it to run scripts (lists of commands) to manage your databases.

Visual Studio

Microsoft Visual Studio is a popular programming tool. It works well with SQL Server. You can use it to design database structures and write code for SQL Server.

SQL Server Management Studio

SQL Server Management Studio (SSMS) is a graphical tool for managing SQL Server. It helps you set up, control, and take care of all parts of your SQL Server. You can use it to see how your database is performing and make changes easily.

Azure Data Studio

Azure Data Studio is another tool for working with SQL Server. It works on Windows, Mac, and Linux computers. You can use it to write queries, export results, and do basic checks on your server.

Images for kids

• 

  The SQL Server Full Text Search service architecture

See also

In Spanish: Microsoft SQL Server para niños

• Comparison of relational database management systems
• List of relational database management systems