T-SQL

TRANSACT STRUCTURED QUERY LANGUAGE

            This is a 4^th generated intermediate language between user and SQL Server. Whenever user wants to interact with SQL Server, he has to interact with SQL Server through T-SQL. It includes the following Sub Languages

1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)

Things to Observe:

1. While writing the Queries using T-SQL in SQL Server Management Studio we need not to follow any particular case. Because T-SQL is case insensitive language.
2. After writing the Query, we need to select that query using either mouse or keyboard.
3. Now Press F5 ( Execute Key).
4. Then the results are displayed in a separate window called Result window or Result Pane.
5. Use Ctrl+R to Hide/Show the Result window or Result Pane.
6. Use F8 for Object Explorer

Note:

1. SQL SERVER can handle nearly 32767 Databases
2. Each Database can handle nearly 2 billion Database Objects.
3. Each Table can handle nearly 1024 columns
4. Each Table can handle nearly 1 million Rows.

SP in Fundamental level in SQL Server

SP_RENAMEDB: Here SP stands for Stored Procedure. This stored procedure is used to change the name of the existing database.

Syntax: SP_RENAMEDB ‘OLD DATABASENAME’, ‘NEW DATABASENAME’

            E.g.: SP_RENAMEDB  ‘NRSTT’, ‘NRSTTS’

The above statement renames (changes the database name) NRSTT to NRSTTS

SP_RENAME: This stored procedure is used for changing the name of the table and for changing the name of the column

i. Syntax to change the name of the table

SP_RENAME ‘OLD TABLENAME’, ‘NEW TABLENAME’

E.g.      SP_RENAME ‘EMP’, ‘EMPLOY’

The above stored procedure changes the name of EMP table to EMPLOY

ii. Syntax to change the name of the column

SP_RENAME ‘TABLE.OLDCOLUMN NAME’, ‘NEW COLUMNNAME’

E.g.      SP_RENAME ‘STUDENT.ADR’, ‘ADDRESS’

The above stored procedure changes the name of ADR column to ADDRESS in STUDENT table.

SP_HELP: This stored procedure is used to display the description of a specific table.

Syntax: SP_HELP TABLENAME

E.g.: SP_HELP EMP

The above stored procedure displays the description of EMP table

SP_DATADASES: This Stored procedure displays the list of databases available in SQL Server.

Syntax: SP_DATABASES

SP_TABLES: This stored procedure displays the list of tables available in the current database.

Syntax: SP_TABLES

SP_HELPDB: This stored procedure is used to display the description of  master and log data file information of a specific database

Syntax: SP_HELPDB Database-Name

Ex: SP_HELPDB SAMPLE

SP_SPACEUSED: This stored procedure is used to find the memory status of the current database

Syntax: SP_SPACEUSED

How to Install SQL Server on Linux CentOS 7 - SQL Server on Linux

# Let's check if mssql-server repository already exists?

sudo ls /etc/yum.repos.d | grep mssql-server.repo

# If it does, we need to check contents of this repository and remove that, otherwise we will face issues during the install

sudo cat/etc/yum.repos.d/mssql-server.repo

sudo rm -rf /etc/yum.repos.d/mssql-server.repo

# Configure and Download SQL Server Repository

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config...

# Install SQL Server using below commands

sudo yum install -y mssql-server

# Configure SQL Server and Setup SA password

sudo /opt/mssql/bin/mssql-conf setup

# Restart SQL Server Services

systemctl restart mssql-server

# Configure repository and install Sqlcmd and other Tools to internally connect to newly installed SQL Server

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config...

sudo yum install -y mssql-tools unixODBC-devel

#Set Environment Tools to your Path Environment


# Connect to SQL Server using sqlcmd tool

sqlcmd -S localhost -U SA -P Pass@123


# Connect to SQL Server Remotely using SQL Server Authentication

SSMS - Error and Resolution

# Enable Firewall port 1433


sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload



# Enable and start SQL Server Agent services
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true


install sql server on centos 7
install sql server  2017 on CentOS 7
install sql server management studio on linux