Author : HASSAN MD TAREQ

Do you know? T-SQL is Turing complete

Terminology

RDBMS

  • RDBMS = Relational Database Management System
  • RDBMS manages multiple relational databases
  • System => it’s a complete solution for data (storing, manipulating, querying etc.)

Scheme

The term “schema” refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases). The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database.These integrity constraints ensure compatibility between parts of the schema.

The schema in a relational database refers to the tables, fields and also the existing relationship between the fields and tables

A database schema is a collection of metadata that describes the relations in a database. A schema can be simply described as the “layout” of a database or the blueprint that outlines the way data is organized into tables.

Database schema == interface

Collation

Collation determines how your data is sorted and compared. It’s very often important with regards to internationalization, e.g. how do you sort japanese kanji?

If you google collation and sql server you’ll find plenty of articles discussing it.

T-SQL

  • Transact-SQL (T-SQL) is Microsoft’s and Sybase’s proprietary extension to SQL
  • T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables.
  • T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements. These additional features make Transact-SQL Turing complete.

Transact-SQL is central to using Microsoft SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

GO

  • GO is not a T-SQL keyword, it’s a batch separator used by SSMS to break the entire script up into batches
  • To reduce memory overhead of executing entire script at once GO is used to divide an script into batches
  • Go means, whatever SQL statements are written before it and after any earlier GO, will go to SQL server for processing

You can change batch seperator keyword from GO to anything in SSMS (i.e. FOO) Batch seperator in SSMS

Square Brackets - []

Setup database

  • Download and install SQL Server developer edition: https://www.microsoft.com/en-us/sql-server/sql-server-downloads
  • Download and install SSMS: https://aka.ms/ssmsfullsetup (SSMS => SQL Server Management Studio)
  • Download sample database (.bak file)
  • Put .bak file in .../Backup folder
    • C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER_DEV\MSSQL\Backup
    • MSSQL15.MSSQLSERVER_DEV => different for you depending on your SQL Server name
  • Connect SQL Server to SSMS
    • Make sure SQL Server developer edition is running
      • Task bar > Search: “SQL Server”
      • Open: SQL Server Configuration Manager > SQL Server Services > SQL Server (SQLSERVERDEV)
      • if not running: SQL Server (SQLSERVERDEV) > Right click > Start
    • Start SSMS => SSMS should automatically detect the running SQL Server
    • Use windows authentication

Now restore sample database (i.e. AdventureWorks2017.bak) using SSMS

Step-1

Restoring sample database using ssms Step 1

Step-2

Restoring sample database using ssms Step 2

Step-3

Restoring sample database using ssms Step 3

Step-4

Restoring sample database using ssms Step 4

Step-5

Restoring sample database using ssms Step 5

Step-6

Restoring sample database using ssms Step 6