SQL Basics for Beginners

What It Is, Why Use It and Associated Key Terms!

What is SQL Server?

SQL Server is a full-featured database management system designed by Microsoft for easy deployment in an enterprise environment. SQL is the standard language for RDBMS and SQL statements are used for communicating with a database, updating information and retrieving it. SQL Server is an entire management system where data is stored in the server and housed in a relational database. It is available in several editions, each with a different set of features and functionalities to meet a variety of user requirements including SMBs with limited needs and large enterprises with complex requirements. It supports ANSI SQL and runs on Transact-SQL. The new features added to the recent SQL version include row processing, transaction control, error handling, declared variables, XML data type support, database mirroring and improved security.

At Your Service

Data Storage

Data storage is a database that comprises of tables having typed columns and SQL Server supports a wide range of data types including decimal, float, integer, character strings and variable length character strings. It also supports binary storage for unstructured blobs of data and text for storing textual data. For rounding floats to the nearest integers, it supports both symmetric arithmetic rounding and round down, depending on the type of argument.

SQL Server also facilitates UDTs (user-defined composite types) and makes stats available in the form of virtual tables and dynamic management views. It can also contain other objects like indexes, stored processes, constraints, views and transaction logs. When the data exceeds 8KB and the row contains varbinary or varchar data, that data is migrated to a new page and is replaced with a pointer that leads to the data. All the rows remain in a single partition and the size of the partition is defined by the user.

SQL Server Buffer Management

To minimize disk I/O, SQL Server buffers pages in random access memory. All pages up to 8KB are buffered in-memory and the entire set of pages being buffered is called the cache. The available amount of memory determines the number of pages cached and the buffer cache is managed by the designated buffer manager. Any page that is being read from or written to gets copied in the cache. Succeeding reads or writes are then redirected to the in-memory copy instead of the on-disc version. The buffer manager updates the page on the disc if the in-memory cache is not referenced temporarily. Asynchronous I/O is used for writing pages back to the disc and this process is performed in the background thread so that other operations don’t need to wait until the ongoing I/O operations are accomplished. Every single page that is written has a checksum which is computed again when it is being read to verify that it has not been tampered or damaged in the process.

Concurrency and Locking

SQL Server enables multiple clients to utilize the same database simultaneously. This makes it much easier to control concurrent access to the sharded data while ensuring complete data integrity even when several clients simultaneously update the same data, modify it or try to read it when it is being changed by another client. SQL Server comes with two different control mechanisms: optimistic concurrency and pessimistic concurrency. When the pessimistic mode is being used, the concurrent access is controlled by the SQL Server using locks which are either shared or exclusive. As the name implies, an exclusive lock restricts the access to a single user as long as the lock is held. Shared locks are typically used when multiple users need access to the same data. These locks can be applied with different levels of granularity depending on the per-database basis.

The optimistic concurrency control mode facilitates the instant creation of new rows instead of overwriting the existing rows. Unlike locking, this mechanism does not block other requests when a specific row is being added or modified because all the additions and deletions are done on the older version of the rows and these older versions reside on a temporary database.

What is SQL Server Management Studio and Why Use It?

SQL Server Management Studio (SSMS) is a comprehensive environment that allows easy access to all the components of SQL Server and database for configuring, administering, and managing all its components. It comprises of a wide range of graphical tools with several script editors that can be used by administrators and developers irrespective of their skill levels. SSMS is a single environment with a combination of all the features and functionalities available in the previous versions of SQL Server, so developers and database administrators can make the most of the familiar features and rich scripting capabilities. This is a standalone tool that empowers the tooling team to be faster and efficient. It is a fantastic solution for enterprises managing several servers on different versions and is also compatible with Azure components like Azure SQL Data Warehouse and Azure SQL Database.