Introduction to SQL and SQL Server
SQL Server is a relational database management system (RDBMS) developed by Microsoft. Transact-SQL is an extension of the SQL language that adds new and more poweful characteristics to it, like the use of Triggers and stored procedures. Transact-SQL allows the application of procedural logic for the treatment of information and it’s capable of being embedded by applications developed in different programming languages.
SQL Server has some important features that make it an extremely powerfool tool and one of the most recommended options in the market, for the administration and management of relational databases.
- It allows the use of Triggers and stored procedures.
- It allows transactions.
- Security (low vulnerability and integrated regulatory compliance).
- Avaliability, stability and scalability.
- It allows working in client-server mode. Data is saved on the server and the client can only access the information.
- It allows the administration of information from other external data servers.
- It offers different versions and editions that adjust to multiple functionalities and bussiness logics to be implemented.
- It has a free version for developers,wich, even with some limitations compared to some other versions, continues to offer a poweful tool as a Database Management System.
SQL (Structured Query Language)
As mentioned earlier Transact-SQL expands the posibilities of SQL (Structured Query Language) language.
Broadly speaking, SQL is a standarized programming language that is used to administrate and retrieve large amounts of data. SQL can be divided into three groups or sublanguages DDL
, DML
y DCL
.
DDL (Data Definition Language)
DDL is the language used to define the structure of the tables and other objects in the database. DDL statements are used to create, update and delete the databases, schemas, tables, indexes and procedures, as well as to define the data types that can be used to fill each field (INTEGER
, VARCHAR
, DATE
) …
This languague will also be responsible for defining and aplying the relationships between different tables and defining the keys(Primary Key, Foreign Key,etc.) and the constraints applied to each field (null
, size ,etc.).
The DDL statements define the structures that will store the data, as well as the procedures and/or functions that allow them to be consulted.
DML (Data Manipulation Language)
DML is the language responsible for retrieving and manipulating data from relational databases, among its main statements we can find the insertion and manipulation of data (update and deletion) as well as the queries about them (SELECT
).
The DML statements work directly on the data stored in the fields of the database tables.
DCL (Data Control Language)
DCL is the language used for managing access to the database and its contents, mainly through the GRANT
statement (to grant permissions and access to users) and REVOKE
statement (to eliminate existing privileges).
DCL, allows you to create and delete roles, permissions and referencial integrity to control and manage the access to the database.
Microsoft SQL Server editions
There are several SQL Server editions, each one has its own characteristics that adjust to the needs of the different groups, organizations and users. Wich one to choose will depend on those needs.
-
Enterprise: edition that encompasses all the features that SQL Server can offer, especially oriented at large companies and organizations that depend on large services and have extensive server needs.
-
Standard: edition that allows the management and basic administration of databases for small and medium-sized companies and organizations, whose execution of applications both locally and in the cloud does not depend on high server resources.
-
Web: as the name suggests, this edition is geared towards web applications, so it’s used primarily by web hosting and VAP providers.
-
Express: free edition oriented to software creators and developers, ideal as a learning method to compile and use small server and desktop applications. It can be upgraded to other, more complete, SQL Sever editions in case of requirement of avanced features.
-
Developer: as its name implies, a free edition specially oriented to developers, wich integrates all the functionality of the Enterprise edition allowing the compilation of any SQL Server application, but limited to a local and development enviroment.