Post-it notes: Chapter 14 –Implementing Objects

April 10, 2013

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

Views 

Intro

  • Objects are divided into programmability and storage
  • Many built-in data types
  • Can create user-defined data types
  • Create indexes on frequently searched columns
  • Constraints define rules for column
  • Unique constraints ensure uniqueness
  • Constraints can also define relationships between tables (foreign key contrainsts)
  • Views can be updated
  • Stored proc’s are compiled t-sql stmts
  • Functions, similar to sp’s – return a value – never update data
  • Triggers – actions that are executed whenever data changes
  • DML – Insert/update/delete/merge
  • DDL – create/alter/drop

Database Recovery Models

  • Simple Recovery model – log files re-used as soon as they become full
  • Full Recovery model – trans logs kept until backups
  • Bulk-logged model – like full, except does not log non-logged operations
  • Non-logged operations offer better performance
  • Truncate table/select into – are non-logged or minimally logged
  • Cannot use the above statements when using replication

Tables and Views

  • Table must have at least one column
  • Stores data in underlying mdf/ldf/ndf
  • Various data types, can create User defined (Create/Alter/Drop type)
  • Columns can have special attributes: Identity, Computed, timestamp or uniqueId
  • UniqueIdentifier column – GUID, used for replication, guaranteed unique
  • Constraints include
    • Check – compares column data with value. Cannot check against another table. Use trigger
    • Primary Key – enforce uniqueness of each row
    • Unique – same as primary. But can have multiple
    • Foreign key – enforce relationships between tables
    • Default – default value of column
  • DML Statements commit as a whole or not

Creating Indexes

  • See post it notes – chapter 7
  • I’ve read tons of articles on indexes and this explanation is by far the best: https://www.simple-talk.com/sql/database-administration/brads-sure-guide-to-indexes/
  • Enables sort and query performance
  • If table is not sorted, no index, known as a heap
  • Only 1 clustered index per table. Usually the primary key
  • Can have many non-clustered indexes. Usually create on frequently searched fields
  • Trade-off is write performance – indexes must be updated
  • CREATE INDEX statement. Use for Clustered and Non-clustered
  • Can choose index to be unique, or use filter clause
  • PAD_INDEX = ON|OFF
    • when ON, free space is allocated in each page of index. Amount, see FILLFACTOR
    • when OFF, free space for one row in each page
  • FILLFACTOR=%
    • Specifies percentage of free space to allocate. If you specify 80%, then 20% will be free. See chapter 7 for more details.
  • ONLINE=ON|OFF – SQL 2008 enterprise. Underlying table online and accessible while index rebuilt
  • If you specify a column to be PRIMARY KEY it will default to Clustered index. This can be overridden by specifying non-clustered
  • If you specify a column to be UNIQUE it will default to non-clustered index

Full Text Indexes

  • SQL 2005 and earlier – separate service [full text search]
  • SQL 2008 – part of DB service
  • Full text index can be stored on separate filegroup
  • Only 1 full text per table – based on single, unique column that does not include nulls
  • FREETEXT – used to search full text index
  • Char, varchar, nchar, nvarchar, text, ntext, xml, image, varbinary
  • image, varbinary – must specify type column (docx, pdf, xlsx)

Views

  • View types
    • Standard View – One or more tables. Can include join, where, top, order
    • Updateable view – single table only. Can have following triggers, [instead of update|merge|delete)
    • Index view – CREATE INDEX
    • Partitioned View – joins all partition data into 1 view (horizontal/vertical partitioning)
  • CREATE VIEW {columnnames} – allows you to rename columns shown in view
  • CREATE VIEW WITH ENCRYPTION – hide view definition also prevents  view from being replicated
  • CREATE VIEW SCHEMABINDING – bind with underlying table columns cannot be dropped/altered until the view is dropped. Only applies to columns used within the view

Creating Stored Procedures

  • Stored compiled t-sql
  • Can accept parameters (@ datatype) and return values (OUTPUT)
  • Can assign default parameter to use if none supplied
  • Can change data
  • WITH ENCRYPTION – encrypts the stored procedure definition
  • WITH RECOMPILE – instruct DB engine to never cache the execution plan for this SP
  • EXECUTE AS – allows to run sp as different user

Creating Functions

  • Similar to functions – cannot execute DML statements
  • Always return single value – or single table-valued expression
  • Used for re-using code
  • Functions can accept input parameters
  • Functions do not support output parameters
    • Scalar functions: return a single value
    • Single statement table-valued functions: single select return table expr
    • Multiple statement table-valued functions: return multiple table expr
    • Built-in functions – getdate(), avg()

Creating Triggers

  • Triggers are sp’s bound to table/view
  • Run when DML is executed
  • FOR UPDATE|FOR INSERT|FOR DELETE – execute immediately after insert/update/delete statement
  • INSTEAD OF UPDATE|INSERT|DELETE – execute before
  • Trigger can include complex t-sql, query tables/views, execute DML
  • Use sparingly, affect performance, difficult to maintain
  • Unlike the CHECK constraint, triggers can access other tables!
  • Trigger can access special tables INSERTED and DELETED
  • Virtual tables only exist while the trigger is executing
  • Query these tables to view changed records
  • Can define a trigger on a view

 

Thanks for reading.

Advertisements

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

%d bloggers like this: