T-SQL - Page 1 : Download and Install & Creating Tables , Retrieving , and deleting

Creating a Database : 

Databases --> Right click -- Give a DB Name

Creating our first query

SELECT 1+100 
SELECT 1+100 as TOTAL

SELECT 1+100 
SELECT 8+10 as TOTAL

Batch  

GO tells this is end of the batch , 

Creating a Table 

There are two ways to create a Table 
-- first one by using a Graphical User Interface or GUI

-- 

Creating Table using T-SQL

Inserting into Tables using T-SQL

The caps lock for the table is the only acepting one for me whereas in the tutorial it is accepting caps and small

create table tblfirst

(FirstName varchar (200) )

INSERT INTO TBLFIRST VALUES ('Elephant')

The intellisense which the SQL server relies on hasn't been updated. or Ctl+Shift + R

Inserting values in any way

Retrieving Data 

select FirstName from tblfirst;

to show everything below, this time i dragged the table.

select * from [dbo].[tblfirst];  

select * from [tblfirst];  // dbo is optional we can take that away

Deleting 

delete from tblfirst   // This deletes contents and not the table

Truncate Table  // This two deletes columns  -- you cannot use Roll Back function.

n T-SQL (Transact-SQL), both DELETE and TRUNCATE are used to remove data from a table, but they have some key differences:

DELETE Statement:

Granularity:

DELETE is a row-wise operation. It removes individual rows from the table based on the specified condition.

Logged Operation:

DELETE is a fully logged operation. Each deleted row is logged in the transaction log, making it possible to roll back changes.

Trigger Execution:

If the table has associated triggers, they are fired for each row deleted.

Rollback:

You can use a ROLLBACK statement to undo the changes made by a DELETE statement within a transaction.

Example:

sql

Copy code

DELETE FROM YourTable

WHERE SomeCondition;

TRUNCATE Statement:

Granularity:

TRUNCATE is a table-level operation. It removes all rows from the table without specifying any condition.

Logged Operation:

TRUNCATE is minimally logged. It logs the deallocation of data pages but not the individual row deletions. This makes it faster than DELETE.

Trigger Execution:

TRUNCATE does not activate any DELETE triggers on the table.

Rollback:

You cannot use a ROLLBACK statement to undo the changes made by a TRUNCATE statement. Once committed, the data is gone.

Identity Columns and Constraints:

If the table has an identity column, TRUNCATE resets the identity to the seed value.

TRUNCATE may be blocked if foreign key constraints reference the table.

Example:

sql

Copy code

TRUNCATE TABLE YourTable;

Choosing Between DELETE and TRUNCATE:

Use DELETE when you need to conditionally delete specific rows, or when you want to log individual row deletions.

Use TRUNCATE when you want to quickly remove all rows from a table, and you don't need to log individual row deletions or execute triggers.

Keep in mind that TRUNCATE requires fewer system resources and is generally faster for large tables, but it comes with certain limitations compared to DELETE. Choose the appropriate statement based on your specific requirements.

Delete a complete table /

drop table tblfirst