ULTIMATE T-SQL

STEP UP YOUR GAME WITH “ULTIMATE T-SQL”. ERROR HANDLING, BACKUPS, SUBQUERIES; YOU NAME IT.

Original Posted By myself on 1/11/2016 at http://rchannel.azurewebsites.net/ChannelR/Details/6

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')

output ----> Output parameters behave just like input parameters, however these output parameter needs to be declared with the OUTPUT keyword when used in a stored procedure.

Set @pInfo = (select Name from tblPersons where Name = Tom) ----> Store the result of the query in an output variable.

The query to be stored in the output variable needs to be enclosed between brakets ( )

2. Execute the query.

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

1. Declare a variable

Declare @pMyID int = spGetID

Declare ----> the Declare keyword is used followed by the variable name and its data type when declaring a variable.

The @ sign matters too when declaring variables in T-SQL. :)

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

The select keyword is simply followed by the @ sign and the variable name to select the variable’s content.

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

rank() over (Order by ID) ----> The rank() keyword must be followed by the over keyword and an aggregate function to order to function.

In this case the order by aggregate function is used.

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

Pagging requires the order by clause

offset 0 ----> Rows to be skipped = 0

rows fetch first 10 rows only ----> Divide the records into first 10 per page

You can also use the next keyword e.g: rows fetch next 10 rows only if you want each next 10 records- lets say in page 2.

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)

Noticed that the subquery is written within brackets ( )

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

Noticed that the ‘##’ characters needs to be specified whenever your using a global temporary table


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'

if 1 > 0 ----> the if keyword is followed by a value to be evaluted followed by a condition

else if 1 > 2 ----> executes only when the if statement evaluates to false

select '1 is greater' ----> this statement will only execute if the if statement evaluates to true.

else select '1 Is less' ----> Default statement to be executed in case the if and the else if statement evaluate to false.

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

WHILE (SELECT AVG(salary) FROM tblEmployee) > 3000 ----> The while keyword is followed by a condition (while average salary is less than 3000)
 
The BEGIN and END keywords are utilized to start and terminate the statement to occur in case the average salary is less than 3000.

The BREAK keyword ensures that the statement is ran only once if the average salary is less than 3000.

Careful! If the average salary is indeed less than 3000 and the break keyword is not specified then your computer would crash by running into an infinite while loop.

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

BEGIN TRY EXECUTE sp_Example END TRY ----> T-SQL statements are inside in the TRY block.

BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage END CATCH ----> In case an error is found in that TRY block, T-SQL statements that are inside the CATCH block are executed.

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

use SomethingDB ----> We need to specify the name of the database to be used.

@Devtype = N'Disk' ----> Devtype stands for device type. In this case we set it to N'Disk' since the backup is being stored on a hard disk drive.

@LogicalName ----> The logical name is used by SQL server to reference the backup file. The logical name must be unique within your database.

@physicalName =N'C:\SomethingDB.bak' ----> Note that the file path for the backup may vary depending on how you setup storage on your computer.

Exec Master.dbo.sp_AddumpDevice ----> The master database name is specified since it contains the store procedure sp_AddumpDevice which we are using.

Noticed that the N keyword is required in the query.

2. Execute the query.