T-SQL TRANSACT STRUCTURED QUERY LANGUAGE

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

SP in Fundamental level:

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

Collation levels and default collation

Collation levels and default collation:

Collation can be set at 4 levels in SQL Server:
1.       Server
2.       Database
3.       Columns
4.       Expression

But collations are compared either at columns level or in expressions. So I am starting from lower to upper levels. The collations of upper two levels (server and database) are only helpful in providing default collation to columns and expressions.
Expression collation: All literals, variables and parameters and functions without any input parameters, by default get the collation of database.

Column collation: When we create a table we can specify a collation explicitly for all character data type (char, nchar, varchar, nvarchar, text, ntext) columns. If not specified then collation of database would be the default collation of a column. The collation of a column can be changed by using an ALTER TABLE statement similar to the following:

ALTER TABLE TestTab ALTER COLUMN CharCol CHAR(10) COLLATE Greek_CS_AI

Database collation: When we create a new database we can specify collation. If not specify then collation of model database is assigned as default collation. To know the collation of database use below statement:

SELECT DATABASEPROPERTYEX('testDB', 'Collation') SQLCollation

Database collation can be changed using the ALTER DATABASE statement as below.:

ALTER DATABASE myDB COLLATE Greek_CS_AI

When collation of database is changed it does not automatically change the collation of all columns of all tables. But the new collation would be the default collation for new columns created hence forth and for expressions.

Server collation: This is set during SQL Server installation. It’s the default collation for system databases. Because this is the collation of model database so this would be the default collation for all new databases that would be created on this server. The server collation can not be changed. To query the server collation use the below statement:

SELECT CONVERT (varchar, SERVERPROPERTY('collation'))