Skip to main content

sql-server

SELECT statement clauses​

SELECT statements are used to fetch data from a database

Alias Need to set a variable when two tables have the same column name

SELECT * FROM [table]

the star / asterick will retrieve all the rows and columns

SELECT * FROM [table] ORDER BY [column]
  • perform specific tasks

Parameter​

  • A parameter is a list of columns, data types, or values that are passed to a clause as an argument

  • WHERE

  • UNION

  • JOIN

    • INNER JOIN
    • OUTER JOIN
    • CROSS JOIN

  • BETWEEN

  • ORDER BY

  • NOT (WHERE NOT)

  • EXCEPT

  • INTERSECT

Where Clause​

  • the more complex the query becomes, the larger the WHERE clause becomes
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN

By Word

SELECT * FROM rhymes.words WHERE word IN ('test', 'handle')

By Syllable Length

SELECT * FROM rhymes.words WHERE syllables IN (3,4,5)
  • IS
  • IS NOT
  • filter by criteria, comparison, conditions

Comparison Operator​

  • '>' greater than
  • '<' less than
  • '=' equal
  • '<>' or '!=' not equal

AND Criteria Condition

OR Criteria Condition

Clauses​

ORDER BY / GROUP BY Clause​

  • sorting the result set
  • ASC (default) or DESC for descending
  • ASC
  • DESC

Constrain Result Set

SELECT DISTINCT r.word, FROM rhyme r;

NOT DISTINCT​

subqueries NOT EXIST​

Set functions​

  • COUNT
SELECT COUNT(*) FROM `rhymes`

returns 21 million

  • MIN / MAX
  • AVG
  • SUM

Concurent / Simultaneous Transactions

Referential Integrity

  • Primary Key Constraint
  • Foreign Key Constraint
  • Unique Constraint
  • Indexes
  • Triggers

Data Access Layer (DAL)

  • The DAL returns back a _____Entity collection and also takes in a _____Entity object to add to the database. Please note the data access layer is responsible to convert the output to the _____Entity type object.

Non-Uniform passing

Entity Classes / Uniform passing

  • The middle tier inherits from the _____Entity class and adds behavior to the entity class. This class will have all the business rules for the entity.

Hybrid (DTO & Entity)

SQL Server Architecture​

● Protocol Layer ○ SQL Server network interface ○ read operation- lookup with select statement query wrapped in a tabular data stream (TDS) packet ○ SNI- sql network interface picks up TDS ○ protocol-tcp/ip, namepipes, isdn, vdi, ipx/spx novell ● put TDS packet in tcp/ip envelope, send to relational engine ● Relational Engine ○ port 1433- default SQL Server tcp/ip port ○ Cmd parser- cut into language elements to check for syntax ○ buffer pool has 2 main areas ■ data cache ■ plan cache- used for ad hoc stored procedures, execution plans, etc ■ 10+ caches (check DMOS clerks) ○ Query Tree --> optimizer ■ search for optimal plan not best plan. in less than 1 sec, can change trace settings ○ phase 0 ■ analyze select ■ transaction processing plan ○ quick plans ■ heuristics- narrow/refines query to statement ● OLE DB ○ access methods ■ logical operations- inner join ■ physical operations- anti join ■ index ■ cardinality ● what indexes do you have that take the shortest path to the data ● primary key with no clustered/non-clustered index? ○ buffer management ● SQLOS & Schedulers ○ SPID Thread ● SQL Database >> DataSource >> Form View/Grid View/List View ■ manipulation & formatting

● Table Hints-manually make sure a transaction can’t be modified by other transactions until the first transaction has committed to ensure db consistency. there are different levels of isolation needed ○ NOLOCK (or READUNCOMMITTED) is applicable to SELECT statements. NOLOCK makes sure no shared locks are issued against the table that would prevent other transactions from modifying the data. could potentially read ‘dirty’ aka uncommitted data making processing inconsistent and problematic ○ READPAST- less common than NOLOCK, this hint has the db engine not consider any locked rows or data pages when returning results. there are no dirty reads and blocking does not occur when issuing queries ● DISTINCT- specifies to eliminate duplicate rows from the result set

Transaction Log​

  • Written to sequentially

Log file Placement​

  • on a separate LUN for manageability purposes
  • dedicated disks if high throughput is needed
  • RAID 1 or RAID 10 provides redundancy