Introduction to TSQL

I found a doc I wrote some times ago, when watching the mythical VTC trainer Mark Long, feel free to use this post if you are going to test someone on sql knowledge, just use use guide as the content are very basic, but  it would be enough for beginner.

first paper

T codd

different sql implementation

tsql = Microsoft

plsql = oracle

terminology

rdbms = relational database mng system

proc=

stored procedure

pk = primary key

fk = foreign key

bcp = bulk copy program // to copy data into sql

odbc e oldb = industry standard to programmatically attch a db and manipulate

oledb = faster version of odbc

sarg = searchable argument to speed up the where clause

dbcc = db check consistency

guid= global unique identifier // # globally unique number

blob = binary large object // img and ptr to outside the table

what is a database

tables have relationship by keys

database = collection of all objects to create, maintain and manipulate data

oltp = online transaction processing // built for online of transaction on db

● fast, efficient simultaneous updates

● maintain data integrity

● highly normalized

● related table

olap = online analytical processing

● forecast on data

● fast retrieval on historical data

● denormalized

● data marts

normalization

avoid duplication of

efficiency of data input

reads related data across multiple tables

of course the retrieval of data is less efficient

based on normal form rules

[ex]

inefficiency of flat data

suing the normalization and relation between tables

no data is repeated

pro

efficient input

data store only once

cons

less query execution

more complex queries

denormalization

combine data in fewer table

less efficient storage

fast data retrieval // put sum columns and precalculate

pro

fast efficient queries

pre-sum and make calculation

cons

less efficient data manipulation

not efficient data storage

data relationship

pk:

each table has a pk

it insures that each row is unique

can be single column or multiple columns

[ex]

// name of person

fk:

to link to tables

N> data in pk can’t be deleted until all the fk are deleted

design concerns

db design will affect the complexity of tsql queries

data meaning and context for queries // meaning with the business context

sql statements

the language elements are

dcl // data control language

ddl // data definition lang

dml // data manipulation lang

dcl // to change roles and membership –  grant / deny / revoke

ddl // to define db, tables, user data types, to define the structure of db – create/ alter / drop

dnl // to manipulate data in db

select / insert / update /delete

tsql elements

variables =

local var defined with @


DECLARE @LastName char(10)

 

operators

arithmetic // + – * %

assignment // =

bitwise // & | ^

comparison // = > >= !=

logical elements // ALL AND ANY BETWEEN EXIST IN LIKE NOT OR SOME

string concatenation // +

unary elements

order of operation

1. + – ~

2. * / %

3. + –

4. comparison

5. NOT

6. AND

7. ALL ANY BETWEEN

8. =

the order is important

transactions

a single unit of work

N> all actions are successful or none of them

modes:

Autocommit // each individual statement is a transaction

Explicit // each is explicitly started with the BEGIN TRANSACTION and ended with COMMIT or ROLLBACK

Implicit transaction // implicitly started when prior transaction completes, but explicitly completed

Functions

sql instruction that return a value

3 types

rowset // return a number of rows/ data

aggregate // return an aggregated value

scalar // operates on singular value

how

CREATE FUNCTION statement

SCHEMABUNDING // to ensure table consistency on definition functions

different types of function

determinist // always returns the same value given the same value

non-determinist

user defined function

stored procedures

group of tsql statements grouped by name for a single execution plan

to provide encapsulation of logic and action

to provide addition security // to give permission on execution

to simplify the mng of administration

to speed up queries // using the cache to faster subsequent running of the same sp

views

a predefined query that can be called

tables with data where I don’t want to publish content of everything, but just part of

a subset of tables contents

to use

● to limit table exposed to the user

● to multiple tables join

triggers

a form of stored procedure invoked automatically on actions

associated to table // a observer model

N> they cannot be called directly

part of transaction

isql/osql

command execution prompt utility

osql // use odbc for application interface

Null

null means UNKNOW value N> to test use IS NULL in where case

NULL values cannot use for PK,FK or similar

N> 2 NULLS are not equal

SET ANSI NULLS ON => it now recognizes NULL with =

N> with OFF the NULL is recognized as value and returned in query result

Creating tables
CREATE TABLE name
(column_name datatype NULL/NOT NULL,
...
)

Ex

create table test

(fname varchar(20) not null,

)

creating constraints

to enforce data integrity

they defines rules about what values allow in columns

the constraints can be defines in 2

● @ table creation

● @ altering the table later

Ex

create table tconstraint
(name varchar(20) not null,
age char(2) not null check (age>18))

Ex

create table tconstraint

(name varchar(20) not null,
age char(2) not null )
alter table tconstraint
add constraint checkage check (age>18)
the classes of constraint are

● NOT NULL // does not accept NULL

● CHECK // to limit values

● UNIQUE // to enforce uniqueness of values

● PK // to identify column or set of values uniq indentify a row

● FK // to identify relationship between tables

creating default

value to use in column when data is not specified

default can be

● constant

● built-in function

● mathematical expression

Ex

create table defex

(fname varchar(20) not null,
city varchar(20) not null,
state char(2) default ‘TN’)
insert into dexex
(fname, city)
values (‘mario’, ‘avellino’)
select * from defex

creating relationship

to create a relationship between 2 tables, to denormalize data

clip_image002

to add the constraint from Address to Customers tables we add

alter table Address

add constraint FK_Address foreign key (id)

references customers (id)

N> we alter the table the will reference the main table to point to the main table

clip_image004

SELECT

the basic select statement will give the data from a table/s

basic select

SELECT select_list
[INTO new_table ]
FROM table_source
[ WHERE search_condition]
[ GROUP BY ]
[ HAVING ]
[ ORDER BY ]

Ex

select au_fname + ‘-’ + au_lname

from authors

N> with select we define a pull statement made up of column and expression combining them

column aliasing

to rename columns name on the fly in the result set

Ex

select au_id AS ‘Author ID’, au_lname AS Last

for authors

N> usage of ‘’ to get long more complex names

N> AS it’s optional can be replace with a whitespace

select INTO

creates a new table (same struct) and put the result of the select into it

N>

○ to create staging table and combine results

○ cannot be use if the table exists already

 

ex

select au_fname, au_lname, phone

into selintoex

from authors

select from

to define the table from which I query data

N> not necessary in arith expression, constants … not addressing columns

using join

retrieve data from 2 or more tables based on relationship between them

N> this comes from the Normalization, as we split data among tables defining relationship to link the info of a logical row of the denormalized table

a join condition defines the way tables are related in a query by:

● specifying column for each table to be used for the join

● specifying a logical operator to compare the actual value contained in the columns

types of join

● Inner join

○ it matches rows based on the values in common columns

● outer join

○ left/right outer = results all the rows form the left/right table, regardless to the common values

● cross join = it returns all rows from the left table combined with all from the right on (Cartesian product)

aliasing

aliasing is used in joins to simplify the query.

N> the alias is defined in the from part and can be used in the query from the beginning, the engine will put all together

select where

it specifies the condition to restrict the rows returned, to avoid to get all the rows every time

N> select * from xyz is dangerous, especially in production conditions

Ex

select * from authors

where au_lname like ‘_a%’

N> usually you use an expression in the where clause

Looking for NULL

NULL means unknown

N> NULL != empty or zero

N> NULL != NULL // is unknown

using the IS NOT NULL retrieves value that are not null

Ex

select l_name, region

where regions IS NOT NULL

N>

avoid use of = as it depend on the setting of ANSI_NULL definition so use IS NULL for that

group by

to group the result set to avoid replication of redundant rows

N> useful to aggregate on at groups level

Ex

select stor_id, sum(qty)

from sales

group by stor_id

N> the grouping on the defined column means that the others columns in the select have to return only one row for each unique row of the column grouped, so we have to define an aggregation on the others column in the select not in the group by statement or to add them in the group by itself

having

defined to put where condition on the aggregated result after the grouping

ex

select stor_id, sum(qty) from sales

group by stor_id

having sum(qty) >10

N> you have to rewrite the aggregation func in the having

union

it combines more queries

rules:

● number of cols equals

● datatype must be compatible

ex

select a ,b from t1

union

select a,b, from t2

Insert

it adds rows to a table

INSET INTO table Name
column list
VALUES
value list

N> if the value list is fully specified is not necessary to put the column list

to insert results from a query table in a another table

ex

insert into newT

select * from oldT

where col1 like ‘a%’

update
UPDATE tableName
SET columnName = expressionSet
WHERE expressionWhere

to update a set of rows in the table

N> without the where condition you change all the row on tableName

advanced update

using a table to get a criteria for the update

delete

to delete rows in the table

. to delete a table itself you need a drop

DELETE from tableName
WHERE deleteExpression

N> use the where clause every time otherwise you’ll delete all the rows in the table

truncate

functionally the same with no HERE clause

N> it resets the seed value of IDENTITY columns

N> truncate will not fire triggers

Index

it’s as the index of in a book to address the data faster

pro

speed up the retrieval

enhance performance on joins/sorting /grouping

enforces uniqueness

cons

increase resource consumption on inserts/updates

maintenance

can be ineffective

types of indexes

● clustered = determines physical storage order
Ex the phone book is clustered on lastname/firstname
N> only one clustered index per table

● non clustered = separate object
N> it creates pointers to actual data

index guidelines for tsql

know the data on data

build indexes on columns used in WHERE clause

choose clustered index on the most used column and use less a s possible

N> clustered first then nonclustered

creating an index

CREATE typeOfIndex INDEX indexName
ON tableName (columnName)

delete an index

DROP INDEX tablename.indexName

N> use the profile or actual execution plan to check the performance changes on using index

composite index

it utilizes more than 1 column up to 16 columns

N> make sure WHERE references the 1st column in a composite index

View

a subset of rows of tables, as a predefined query on tables, creating a virtual table

N> we use them specially to limit the visibility of sensible data

create a view

CREATE VIEW viewName
AS
underlyingQuery

ex

create view storesOrderQty

as

select stor_name, ord_num , qty

from stores st join sales sa

on st.stor_id = sa.stor_id;

select * from storesOrderQty

adv

● focus the data for the user, filtering out data not needed

● hide database complexity

● hide db structure

● simplify permissions

● protect sensitive data

● data partitioning on separate db

N> we don’t apply security on tables, but on views

system tables

sql server stores information in system tables

they exist in master db

each user database has system tables as syscolumns

N> system tables should not be altered

we can query them using

● information schema views

○ system table independent view of the sql server meta data // for compatibility

Ex

check if a table exists

select * from information_schema.tables

N> check doc for the list of type of information_schema available

tsql var

variable are type specific

● loop counter

● control flow values

● hold data returned by sp

2 types of tsql var

local

@name

scope = from the point until the end of the batch / sp in which it’s declared

global

@@name

scope = always in any tsql action

declaring and assigning

DECLARE @varName dataType

N> must begin with @

datatype has to be designated at declaration

original value is NULL // not known

use SET to assign a var value

SET @varName = varValue

ex

declare @mario varchar(20), @notDefined int

set @mario =’Mario says hello’

print @mario

print @notDefined

N> to set you can use the select value return

ex

declare @x varchar(50)

set @x = (select ax from table where id = 1)

ex

select @ form table where ax = @x

N> @@rowcount = number of returned by last select

variables as obj names

using the var to refer table names tec

use EXEC

Ex

declare @x varchar(15)

set @x = ‘authors’

— select * from @x –it does not work

exec (‘select * from ‘ + @x )

system variables

they are global var

N> the functions automatically populates the system variables

stored procedure

group of tsql statement compiled into a single execution plan

adv

● provides encapsulation of logic

○ to hide the underlying implementation

● security

○ for usage

○ to avoid to send sql content over the network

● simplify mng/administration

● performance

five steps

● parse

○ to make sure to understand what to do

● permission

○ to execute on table

● optimize

○ the structure of call

● cache

○ save time for other executions

● execute

create a procedure

CREATE PROCEDURE procName
AS
{TSQLCode}

N> to execute it use execute or exec

using input variables

to send input parameter to the proc just define the input in the definition of the proc

ex

create proc authorlist

@contract int

as

select au_lname from authors where contract = @contract

output parameters

a proc can returns values to the calling proc

….
@outValue type OUTPUT

you define a var pas it to the proc and use the changed values

extended proc

they are dll that sql server can load dynamically

they appear as normal sp even are external

N> they extend the sql capabilities

ex

exec xm_cmdshell ‘dir c:\’

cursors

we ask to sql a set of data based on criteria

in vb/c# I use loop to retrieve data , this is called row process

cursors provide row by row processing

N> they put overload

functions

they are instructions that return a value

● scalar

○ operate on single value

● aggregate

○ operate on group of values

● rowset

○ used as table reference

CREATE FUNCTION statement

SCHEMABINDING

to avoid to drop table and invalidate some functions related

user defined functions

custom build functions

3 types

● scalar

● multi statement

● inline

ex scalar

CREATE FUNCTION fnNA

(@input varchar(15))

RETURNS varchar(15)

BEGIN

IF @input IS NULL

SET @input = ‘UnkN’

RETURN @input

END

select au_id, dbo.fnNA(address) from authors

ex multiline

CREATE FUNCTION fnAuthInfoExtra

(@colName varchar(12))

RETURNS @AuthInfo

table

(AuthorData varchar(40) primary key NOT NULL,

Extra varchar(60))

AS

BEGIN

IF @colName =’city’

INSERT @AuthInfo SELECT (au_fname + ‘ ‘ + au_lname) , city from authors

ELSE

IF @colName =’state’

INSERT @AuthInfo SELECT (au_fname + ‘ ‘ + au_lname) , state from authors

RETURN

END

select * from fnAuthInfoExtra(‘city’’)

select * from fnAuthInfoExtra(‘state’)

ex inline

similar to using function

triggers

it’s a form of sp invoked automatically when actions take place

they are associated to table

– cannot be called directly

cannot be circumvented

to use when

● when we have complex logic to perform

● to cascade data changes thorough related tables

● to reference another table

● enforce complex referential integrity

creating

CREATE TRIGGER triggerName
ON tableName
FOR [ACTION(INSERT, UPDATE, DELETE)]
sqlStatement

how does it work

N>while trigger is in execution a in memory table for insert and delete is created, to reference the data affected by the trigger action

nesting and recursive trigger

it’s nested when a trigger performs an action that initiate another trigger (indirect)

N> set RECURSIVE trigger option to enable a recursive call on the trigger itself (direct)

clip_image006

XML

in XML the data is always self-describing, just looking at the table, without knowing too much on the query that pulled the data

FOR XML

to produce xml form results

RAW

maps query result into XML element with a generic row element

AUTO

it returns query results in simple nested XML tree

N> using ELEMENTS columns are returned as sub-elements instead of attributes

EXPLICIT

to specify the shape of the XML tree result

ex

SELECT au_id, au_fname FROM authors FOR XML RAW

<row au_id=”409-56-7008″ au_fname=”Abraham” />

SELECT au_id, au_fname FROM authors FOR XML AUTO

<authors au_id=”409-56-7008″ au_fname=”Abraham” />

SELECT au_id, au_fname FROM authors FOR XML AUTO, ELEMENTS

<authors>

<au_id>409-56-7008</au_id>

<au_fname>Abraham</au_fname>

</authors>

Optimizing

avoid pulling too much data when is not needed

use the where clause properly

use filtered expressions

keep in mind design issue

utilizing indexes

you have one clustered index per table so use properly

the first column on the where use the indexed one

use index tuning wizard

execution plan / sql profiler

the execution gives you a visual representation of the query execution and the cost of each part

sql profiles catches all the info on the executed query against db

One thought on “Introduction to TSQL

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