Cloned from: SQL Server



keywords:
Bookmark and Share



Front Back
RDBMS
Relational Database Management System
6 properties of RDBMS
1. Values are atomic
2. Column values are of the same kind
3. Each row is unique
4. The sequence of columns is insignificant
5. The sequence of rows is insignificant
6. Each column must have a unique name 1. Values are atomic
2. Column values are of the same kind
3. Each row is unique
4. The sequence of columns is insignificant
5. The sequence of rows is insignificant
6. Each column must have a unique name
Cursor
Objects used to manipulate data on a row-by-row basis
6 steps of a cursor
1.  Declare cursor
2.  Open cursor
3.  Fetch row from the cursor
4.  Process fetched row
5.  Close cursor
6.  Deallocate cursor
Trigger
  • Event-driven SQL procedure
  • Stored and managed by the DBMS
  • Mainly used to maintain referential integrity
System Tables
Some of them are:
Information_schema.check_constraints
Information_schema.column_domain_usage
Information_schema.column_priveleges
Information_schema.columns
Information_schema.tables
Information_schema.views
Information_schema.key_column_usage
LOB Data
Large Object Data
CLR
Common Language RunTime
OLTP
Online Transaction Processing
Subqueries
SELECT statement within another SELECT statement
DBCC
Database Consistency Checker

*Most usefull for performance & troubleshooting
Some DBCC commands
DBCC TRACEON
DBCC Page Command
DBCC CheckDB
DMO
Dynamic Management Object
DMV
Dynamic Management View
(Part of DMO)
DMF
Dynamic Management Function
(Part of DMO)
Examples of DMVs
sys.dm_exec_query_stats
sys.dm_exec_tran_locks (transactions)
sys.dm_db_index_usage_stats
Inner Join
Data that matches both tables
Types of Joins
Outer Join
Left Outer Join
Right Outer Join
Full Outer Join
Inner Join
Self Join
Cross Join

Self Join
A table that joins itself

*Can be used -> Common example, a companny has hierarchal reporting
Full Outer Join
All rows from both tables are returned
Left Outer Join
Returns each row that satisfies the join of the first input with the second input.  It also returns any rows from the first input that had no matching data.
Outer Join
Right Outer Join
Cross Join
Clustered Index
Physically orders rows in a table
*A table can have only one clustered index

ALTER TABLE <TableName>
ADD EmpIndex Int IDENTITY (1,1)
GO
Non-clustered Index
*Particularly handy when we want to return a single row from a table

CREATE UNIQUE NONCLUSTERED INDEX <IndexName>
ON <TableName> <(ColumnName)>
GO
Heap
Table with no indexes

(indid=0 on sysindex system table)
1NF
First Normal Form

*Eliminate repeating groups
*Eliminate duplicative columns from the same table
2NF
Second Normal Form

Eliminate redundant data
3NF
Eliminate columns not dependant on key
Index
Physical structure containing pointers to the data

*Helps locate rows more quickly & efficiently

*Used to speed up queries

*A mechanism for providing fast access to table rows & for enforcing constraints
CTE
Common Table Expression

*Temporary resultset that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE View statement

*Similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.  Unlike a derived table, a CTE can be self-referencing & can be referenced multiple times

ex:
WITH <CTE Name> (ColumnNames)
AS (Select * FROM <TableName>)
Collation
x of y cards