Ultimate T-SQL

Table of content

 

---

Prerequisites

A good knowledge of outrageous T-SQL is required. You can learn more about outrageous T-SQL using the following link: Outrageous_T_SQL

A SQL Server Management Studio 2012 or SQL Server Management 2014 is required. 
 

---

A. Output parameter stored procedure

1. Type:

Create proc spGetName
@pInfo varchar(50) output
as
Set @pInfo = (select Name from tblPersons where Name = 'Tom')
 
2. Execute the query.

Storing the results of the output parameter store procedure into a variable

1. Declare a variable

Declare @pMyID int = spGetID 
 
2. Execute the store procedure with the previously created output variable as its parameter. 

Exec T @pMyID output 

3. Display the information stored in the variable

select @pMyID 
 
Result: 1
 

---

B. Rank

The Rank function provides the rank of each row from a table.

1. To get the rank of rows based on their ID column, type:

select *,
rank() over (Order by ID) as 'Rank'
From tblPersons
 

2. Execute the query.

Result: 

 

---

C. Pagging

Lets say you have a list of 5000 records but you want to divide these into pages so that your user do not have to deal with these 5000 in one single page. 1. Type:

select * from tblPersons
order by id
offset 0 
rows fetch first 10 rows only
 
2. Execute the query.
 

---

D. Subquery

 

• You guessed it right; a subquery is a query within a query connected using the where clause. 

 

• The subquery is utilize as a to further restrict the information to be retrieved from table using a query.

1. Type:

select Name from tblPersons
where tblPersons.ID = (select top 1 id from tblPersons as ID)
 
2. Execute the query.

Result: Tom
 

---

E. Global Temporary Table

 

• A global table can be access through any database on the server.

 

• A temporary table is deleted when your server restarts.

1. Type:

Create table ##tblTemp
(ID int,
Name varchar(50));

#tblTemp ----> The '#' character is used to define a temporary table.
##tblTemp ----> 2 '#' characters are used to define a global temporary table.

2. Execute the query.

Using the global temporary table

select * from ##tblTemp
   

---

F. Table Variables

Variable tables are an alternative to temporary tables but the table variables cannot be used as input or output parameter.

1. Type:

declare @varTbl table
(ID int,
Name varchar(50))

declare @varTbl table ----> the table keyword is used as the data type of the variable to make it behave like a table

2. Execute the query.

Selecting information from a table variable

1. Under your previous query type:

select * from @varTbl

2. Execute the query.
 

---

G. Truncate Table

Unlike the delete table keywords, the truncate table clears the table fields without deleting the table itself. A very simple, yet very useful keyword. :)

1. Type:

truncate table tblCars

2. Execute the query.
 

---

H. If / Else statements

Similarly to case statements if/else statements are used to evaluate specified data using conditions.

1. Type

if 1 > 0
select '1 is greater than 0'

else if 1 > 2
select '1 is greater than 2'

else 
select '1 is less' 
 
2. Execute query.

Result: 1 is greater than 0
 

---

I. While loop

The while loop allow us to set a SQL statement those execution would be repeated until the specified condition evaluates to true. The SQL  statements' executions in a WHILE loop is controlled using the BREAK keyword inside the while loop.

1. Type:
 
WHILE (SELECT AVG(salary) FROM tblEmployee) > 3000
BEGIN
   SELECT 'The average employee salary is greater than $3000'
   BREAK
END
 
2. Execute the query.
 
Result: Nothing happens since the average salary is greater than 3000
 

---

J. Try and Catch

The Try and Catch keywords are used for error handling.

First create a store procedure which would produce an object resolution error.

1. Type

CREATE PROCEDURE sp_Example
AS
    SELECT * FROM NonexistentTable;
GO

2. Execute the query

Then execute the store procedure in the try block.

1. Type

BEGIN TRY
    EXECUTE sp_Example
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage
END CATCH
 
2. Execute the query

Result: 
 

---

K. Compressed Table

Table compression is fairly straight forward. However feel free to submit any potential query you have regarding this post in the comment section.

1. Type:

Create table tblName
(
id int,
name varchar(50)
)
with(Data_Compression = Row)

2. Execute the query.
 

---

L. Set Database permissions

Set database to Read only:

1. Type:

Alter database SomethingDB
set Read_Only

2. Execute the query.

Set database to Read and Write

1. Type:

Alter database SomethingDB
set Read_Write

2. Execute the query.
 

---

M. Backup Databse

It is extreamely important to regular backup your databases to keep them safer.

1. Type:

Exec Master.dbo.sp_AddumpDevice
@Devtype = N'Disk'
,@LogicalName = N'SomethingDB_Backup_Dev'
,@physicalName =N'C:\SomethingDB.bak'
use SomethingDB
Go
 
2. Execute the query.
 

---

N. Next Step

Feel free to apply all concepts introduced in this course using SQL Server Management Studio.
 

---