Exploring Transact SQL (T-SQL) and its Function Types

Transact SQL (T-SQL) serves as the dedicated query language for Microsoft SQL Server, facilitating a range of operations from data retrieval to manipulation. Let’s delve into its intricacies and the types of functions it offers.

T-SQL: An Overview T-SQL stands as a procedural language tailored for Microsoft SQL Server. It extends SQL’s capabilities by introducing features like declared variables, transaction control, error handling, and row processing. While its syntax differs from PL-SQL, it delivers comparable functionality and results.

Features of T-SQL T-SQL introduces several distinctive features:

  • BULK INSERT: This statement enables the seamless importing of files into the database table or view, with customizable formatting options.
  • Support Functions: T-SQL offers a plethora of functions for efficient string and data processing.

Types of Functions in T-SQL T-SQL encompasses four primary types of functions:

  • Aggregate Functions: These functions operate on a set of values and return a single value. Examples include SUM, AVG, MIN, and MAX.

In Transact SQL (T-SQL), understanding function types and data types is crucial for effective database management. Let’s dive into the various function types and data structures available in T-SQL:

Function Types in T-SQL:

  1. Ranking Function: This function assigns a ranking value to each row within a specified partition.
  2. Rowset Functions: These functions return an object that can be used as a reference to a table within an SQL statement.
  3. Scalar Functions: Operating on a single value, scalar functions return a single value as output.

Data Types in T-SQL:

  • String Data Types:
  • char(n): Fixed-length non-Unicode characters (Max size: 8,000 characters)
  • varchar(n): Variable-width character string (Max size: 8,000 characters)
  • varchar(max): Variable-width character string (Max size: 1,073,741,824 characters)
  • text: Variable-width character string (Max size: 2GB of text data)
  • nchar: Fixed-width Unicode string (Max size: 4,000 characters)
  • Numeric Data Types:
  • bit: Integer representing 0, 1, or NULL
  • tinyint: Whole numbers from 0 to 255 (1 byte)
  • smallint: Whole numbers between -32,768 and 32,767 (2 bytes)
  • int: Whole numbers between -9,223,372,036,854,775,808 and 9,223,372,854,775,807 (4 bytes)
  • real: Floating precision number data from -3.40E + 38 to 3.40E + 38 (4 bytes)
  • Date and Time Data Types:
  • datetime: Date and time from January 1, 1753, to December 31, 9999 with 3.33 milliseconds accuracy (8 bytes)
  • datetime2: Date and time from January 1, 0001, to December 31, 9999 with 100 nanoseconds accuracy (6-8 bytes)
  • date: Date only from January 1, 0001, to December 31, 9999 (3 bytes)
  • time: Time only with 100 nanoseconds accuracy (3-5 bytes)
  • timestamp: Stores a unique number updated upon row creation or change.
  • In this blog, we’ll explore how to create tables, insert records, and select data using Transact-SQL (T-SQL), a powerful language for managing Microsoft SQL Server databases.

Creating a Table in T-SQL

  • The CREATE TABLE statement is used to create a new table in the database. Here’s the syntax:

CREATE TABLE table_name

(

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  …

);

Example:

CREATE TABLE employees

(

  employee_id INT NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50),

  city VARCHAR(50)

);

Inserting Records in T-SQL

The INSERT INTO statement is used to insert new records into a table. Here’s the syntax:

INSERT INTO table

(column1, column2, … )

VALUES

(expression1, expression2, … ),

(expression1, expression2, … ),

…;

Example:

INSERT INTO employees

(employee_id, last_name, first_name)

VALUES

(01, ‘Sam’, ‘Johnny’);

Selecting Records in T-SQL

The SELECT statement is used to retrieve records from a table. Here’s the syntax:

SELECT column1, column2, …

FROM table_name;

Example:

SELECT employee_id, first_name

FROM employees;

To select all columns, use:

SELECT * FROM table_name;

Example:

SELECT * FROM employees;

Conclusion

While SQL serves as the base programming language, T-SQL extends its functionality for use with Microsoft SQL Server. Your choice of database depends on project requirements.

Interested in mastering SQL? Enroll with IgnisysIT for SQL course for comprehensive training. From structuring databases to writing efficient SQL statements and managing scalable growth, this course has it all!

Have questions? Drop them in the comments, and our experts will be happy to assist you!