SQL a glance

Standard

SQL Table:

This SQL Object to store and retrieve the data in row and column. A table can create with multiple columns and with multiple data types. Column Name with in the table should be unique and should not have SQL syntax as the name. int, numeric, datetime, smalldatetime, char, varchar, nchar, nvarchar, bit, varchar(MAX), nvarchar(MAX), XML are some known data type. Varchar(MAX) and nvarcharr(MAX) are replacing large objects (LOB) such as image, Text, nText since SQL Server 2008 R2 release.

Table definition having feature allow or restrict the data in terms of data type, length, allow null value or not, unique value (Unique Constraints) or not, lookup value (Check Constraints) or not. Best practice one of the columns should be identified as primary key of the table.

Use Db_Name 

Create Table [Schema_Name.]Table_Name
(
<Column_definitions> …
)

GO

Note: Full version of syntax available in MSDN site

SQL TempTable:

This SQL temporary objects use with custom table definition to keep and track the data until the function or session alive. The definition and data will be removed from SQL memory once the function or session end.

SQL support local and global temp tables based on their visibility and life time. Local temp table name should start with one # character followed by meaningful name.  It will be visible only user one who created. It will be cleared from SQL memory once calling objects or user session end.

Use Db_Name

Create Table [Schema_Name.]#LocalTempTable_Name
(
<Column_definitions> …
)

GO

Note: Full version of syntax available in MSDN site

Global temp table name should start with two # characters followed by meaningful name. It can be accessible thru more than one object and all user sessions. It will be alive until lost user session alive.

USE DB_Name

Create Table [Schema_Name.]##GlobalTemp_Name
(
<Column_definitions> …
)

GO

Note: Full version of syntax available in MSDN site

SQL Procedure

This SQL object helps perform the business logic using set of statement to achieve the goal. Procedures are compiled objects, it helps to avoid bottle neck arise when use the plain SQL with on time compilation. It supports procedural language. Procedural language helps to check the condition, iteration, calling user or SQL function, assigning variables, to handle more than one dynamic query with parameter and complex calculation.

Use DB_Name

Create Procedure [Schema_Name.]Procedure_Name (<@Parameters_withDataTypeDefaultDirectionDefinition>) As
<Set_Of_Procedural_SQL_Statement>

GO

Note: Full version of syntax available in MSDN site

SQL Function

This SQL Object helps to perform piece of business logic using procedural and/or SQL statement vice versa to return single (scalar) or Table as a result. It can be reusable with more than one object.

Use DB_Name

Create Function [Schema_Name.]Function_Name
(<@Parameters_withDataTypeDefault>) Returns data_type As
Begin
<Set_Of_Procedural_SQL_Statement>
return <result>
End

GO

Note: Full version of syntax available in MSDN site

SQL View

This SQL object to keep the definition of data filter as custom query with one or more table to make virtual table. It allows viewing filtered data and/or data aggregation from multiple tables join together to make single virtual table either with in the database or across the multiple partition tables or tables across multiple databases.  SQL views are updatable. It helps to view certain rows or specific column by user. That means, restrict the user from unauthorized data.

Use DB_Name

Create View view_name
As <Select_Statement_WithCondition>

GO

Note: Full version of syntax available in MSDN site

SQL Triggers

This SQL object helps to automatically execute set of procedural statement as per the business logic when do some DML (INSERT, UPDATE, DELETE statement with table or view), DDL (Create, Alter, Drop statement with SQL Objects) and logon operations.

SQL Cursor

This SQL Object helps to return data set as well as allows to process the data set row by row with help of Fetch Next and While loop statement.

Syntax for Cursor declaration and manipulation

Use Db_name

Declare @variable1 datatype ………..
Declare Cursor Cursor_Name For <Select_Statement_to_return_data>
Open Cursor_Name
Fetch Next From Cursor_Name into <declared_variables_in_order>
While @@Fetch_status = 0
Begin
<Statement_To_Process_Business_Logic>
Fetch Next From Cursor_Name into <declared_variables_in_order>
End
Close Cursor_Name
Deallocate Cursor_Name

GO

Note: This is pseudo code; get more information in MSDN site

SQL Index

This SQL Objects help to improve the database performance (Speedup data retrieval from table or view) using one or more keys stored on-disk structure (B-Tree – binary search tree to store, sort, delete and insert data in fraction of time) or heap associated with table or view. This can be classified into two major categories such as Clustered Indexes and Non-Clustered Indexes.

A)     Clustered Index

One or more clustered keys combine together form clustered index. This indexes sort the data as B-Tree structure to improve the data retrieval. Clustered Index can create only one per table.

B)      Non-Clustered Index

One or more non-clustered (or with heap or with clustered index) keys combine together form non-clustered index. The data row may locate using pointer in heap memory or with clustered index in the table. These indexes are not guaranteed to sort order of the row, without clustered index in the table. More than one non-clustered index can create with in the table or view.

SQL Files

SQL Server has physical files (operating system files) to store the data and log information as separate. It has Primary data file with .mdf extension, secondary data file with .ndf extension and log file with .ldf extension. But SQL Server is not enforcing this file extension. Primary data file and secondary data file consists of pages with unique name with sequential number. Physical files name are not directly referred by Transact SQL Statement, it refers through logical files name.  When we do the backup the primary data file and corresponding log file compressed to save as backup file with .bak extension

SQL QUERIES

SQL Queries are SQL statement to join one or more table or view to retrieve the data, or perform specific operation such as insert or delete or update on the table or view.

Simple Query:

Select Column1, Column2 from Table1

Select Column1, Column3 from View1

Query Join:

Select Table1.SameCol, Table2.Column2 from Table1 INNER JOIN Table2  ON Table1.SameCol = Table2.SameCol

Select Table1.SameCol, Table2.Column2 from Table1 OUTER JOIN Table2 ON Table1.SameCol = Table2.SameCol

Select Table1.SameCol, Table2.Column2 from Table1 LEFT OUTER JOIN Table2 ON Table1.SameCol = Table2.SameCol

Select Table1.SameCol, Table2.Column2 from Table1 RIGHT OUTER JOIN Table2 ON Table1.SameCol = Table2.SameCol

Select Table1.SameCol, Table2.Column2 from Table1 CROSS JOIN Table2 ON Table1.SameCol = Table2.SameCol

Aggregate Function with filter condition:

Select Count(*), Column1 from Table1 group by Column1 having Count(*) Operator value

Query with Conditional Column Value (Case … When … Then …. Else…End)

Select Column1, ‘Meaningful_Description1’ = Case Column2 When Column2Value1 Then formula1 When Column2Value2 Then formula2 Else formula3 End, ‘Meaningful_Description2’ = Case Column3 When Column3Value1 Then Description1 When Column3Value1 Then Description2 Else Description3 End From Table1

Sub Query as Column:

Select filterColumn, Column2, (Select subColumn1 from Table2 Where subColumn2 = filterColumn) as derivedColumn from Table1

Sub Query as Table:

Select SubQryTable.Column1, MainTable.Column1 from MainTable inner join (Select Column1 from Table1 Where Column1 Operator Value) as SubQueryTable on MainTable.Column1 = SubQueryTable.Column1

Sub Query in where clause:

Select Column1, Column2 from MainTable Where Column1 in (Select Column1 from SubQueryTable Where Column3 Operator Value)

SubQuery in having clause:

Select Aggregate_Function1(Column1), Column2 from MainTable Group by Column2 having Aggregate_Function1(Column1) Operator (Select Aggregate_Function2(Column3) from SubQueryTable)

Isnull function – Replacing Null value with default value

Select isnull(Column1, defaultvalue) from Table1

SQL Functions – Date Function

GetDate() – Function return to current server date time

DatePart(datepart_abb, date_column) – Function to return the value of ‘datepart’ from ‘date_column’

DateAdd(datepart_abb, num_datepart, date_column) – Function to return the value of ‘datepart’ from after add ‘num_datepart’ with ‘date_column’   DateDiff(datepart_abb, start_date, end_date) – Function to return the value of ‘datepart’ after find the date difference between ‘start_date’ and ‘end_date’

SQL Functions – String Function

Ascii(char) – Function return to ascii value of the char (most left character)

PATINDEX(‘%pattern%’, expression) – Function to return the starting position of first occurrence of the ‘pattern’ in the expression assigned in the second parameter.

Space(Num_Of_Space) – Function to return the number of assigned values of spaces.

Replace(string_expression, ‘String_To_Find’, ‘Replacement_string’) – Function to return replaced string from first parameter assigned to find all the matching of second parameter assigned and replace with string value assigned in the third parameter.

Stuff(string_expression, start_position, length,  char_expression) – Function to return the value of string_expression assigned in the first parameter after delete number of characters  assigned in the third parameter from the starting position assigned in the second parameter from it  and then insert the string value assigned in the fourth parameter.

Left(expression,  num_of_charFromLeft) – Function to return the first parameter value only specified number of characters from the left.

Replicate(expression, num_of_time_repeate) – Function to return the replicated expression in number of time specified in the second parameter.

SubString(expression, startPosition, length) – Function to return the expression specified starting position to number of characters length.

Reverse(String_Expression) – Function to return the value assigned as string_expression in reverse order.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s