These are some notes that I took while studying for my MCSD final exam on SQL server 2000.  There are some good things in here and some things that might not make sense until I organize them and put them into a nice little paper.

 

Prevents users accessing the view from editing the values in such a way that the data would not get selected by the view.

Allow data that is retrieved frequently to be retrieved faster and must use ansii nulls option on.  A clustered index must be created first.

UNION
Should be used to join two tables in a view from different disparate databases and order by must be specified outside of view.

COALESCE selects one or the other

To compile queries remotely use the OPENQUERY function, only to be used with linked servers.

 

OPENDATASOURCE in FROM can be used to access non-linked remote servers in T-SQL by passing all params when the function is called.

 

OPENROWSET is used just like OPENDATASOURCE to query data remotely.

 Efficient Transactions

• Keep transactions short
• Avoid user interaction within a transaction
• Open a transaction only to modify data
• Minimize the use of implicit transaction mode
• Minimize the amount of data targeted by a transaction
• Use lower transaction isolation levels (improve concurrency)
• When using cursors, specify optimistic concurrency control
• Design transactions to occur in the same order as the data

 Error handling

• check for errors after each statement but use RETURN to stop processing
• @@ERROR returns the last error, it returns 0 if an error does not occur
• BEGIN and END are control statements

Stored Procedures

• Input parameters
• use @paramatername datatype and use comma to separate.  Up to 2100 parameters can be used
• AS comes after parameter list
• Output paramaters

 Triggers
 Special type of stored procedures
  > Trigger Mechanics

• When INSERT, UPDATE and DELETE statements can fire triggers
• INSTEAD OF triggers fire instead of triggering statements and before contraints
• Only one INSTEAD of trigger can be created for each type of statement
• AFTER triggers fire after triggering statements
• Multiple AFTER triggers of the same type can be created on the same table
• Triggers are event-driven stored procedures;  they are called automatically when specified data modifications are attempted
• Triggers provide functionality that goes beyond that of constraints
• Instead of triggers are executed instead of
• After triggers are fired after

Tuning and Optimizing Data Access
 > Methods for optimizing query performance

• Application Design
• Minimize network usage (batches)
• only return required rows and columns SET NOCOUNT ON to eliminate the number of rows count
• Cursors require more round trips. Limit cursor usage
• Use stored procedures and avoid ad hoc queiries (send only params)
• Move logic to data
• Allow applications to cancel their queries (no reboots)
• Follow the recommended practices for minimizing deadlocks
• Keep queries and transactions as short as possible
• Always specify time-out periods

SQL Profiler

• Is a graphical SQL Server monitoring tool
• Capture information about events
• Save to file or table
• Replay trace events at normal speed or one at a time
• Trace information can be used to identify and address performance issues and other problems:
• Debugging stored procedures
• Identifying long-running queries
• Auditing user access to instances of SQL server
• Use filters to specify certain aspects to look for
• Create templates for your own custom traces

 Index Tuning Wizard

• Tune custom selects or profiler results
• Schedule and save the scripts for index tuning
• Can apply changes now or later
• Can view or Edit the tuning wizard script

Statistics

• Provide statistical information about the distribution of values in a column
• Are automatically created on all indexed columns at the time of index creation
• Can be created automatically or manually on non-indexed columns
• Are used by the query processor to determine optimal data access strategies for query execution
• Can be updated automatically or manually
• Updating statistics is important but we should disable automatic statistics during business hours
• (sp_updatestats

 > Most optimization happens at the client
 > Optimize at design time
 > Use stored procs in client apps
 > Use SQL profiler to debug and optimize stored procedures and batches used in application (locking, deadlocking, security violations)
 > Use the Index Tuning wizard to modify indexes in order to optimize a specified set of queries
 > Statistics on indexed columns are created automatically.Statistics need to be up-to-date

 

Using Indexes to Optimize Data Access

• Non clustered indexes are sorted in DESC order
• B Tree is the start of a subindex
• Each subrange is divided into two more subranges and 4 3rd level nodes.
• This occurs until only one pointer exists pointing to the location on the hard drive where that row resides
• In a clustered index the table index starts at the top and works its way down
• Built in the same manner as the B-Tree
• Clustered index are used as pointers to rows to a non clustered index

 > Fill factor

• Page splits cause index fragmentation
• Drop and recreate indexes
• Specify the fill factor to specify how much data the table will initially require

> Indexing strategies

• Indexes on tables with many rows are better than small tables
• Indexes on columns with unique values are more efficient than indexes on columns with many duplicate values
• A clustered unique index is automatically created on primary key in each table
• Too many indexes adversely affect the performance of INSERT, UPDATE and DELETE statements
• A large number of indexes may improve the performance of select statements
• A single composite index on multiple columns is more efficient than a separate index on each of those columns
• A clustered index should be as narrow as possible
• Clustered indexes are especially efficient on columns that have to be sorted or searched for ranges of values
• Always list columns in appropriate order when using composite indexes
• create indexes to find WHERE clause or JOIN condition data
• Create clustered indexes on columns frequently search for reange of values
• Create non-clustered indexes on columns frequently searched for exact matches
• Create composite covering indexes for columns all contained in the select (greatly improves performance)
• In composite indexes, list columns with higher cardinality before columns with lower cardinality
• When creating indexes, specify appropriate fill factors to minimize fragmentation
• Drop and rebuild indexes to eliminate existing fragmentation
• Periodically rebuild indexes and update statistics to improve performance

Designing database security
 > Providing Direct Access to Data

Logins

• Provides access to particular instance of SQL Server
• Standard logins can be used from within third-party operating systems
• Windows-based logins use integrated windows authentication

Configuring logins

• sp_addlogin [loginname], [password] to add logins SQL authentication (3rd party OSs)
• sp_grantlogin [domain\loginname] for granting a domain user account to SQL
• sp_grantlogin [domain\groupname] for granting a domain group

Server Roles

• Include logins
• Only use existing roles
• Roles specify tasks that the user can do

Database User Accounts

• Are specific to a particular database
• Are mapped to databases
• sp_grantdbaccess [loginname], [dbuseraccountname] in context connection of the db
• each database must have a user for the login that is trying to access it and must be mapped

sp_grantdbaccess

• the username does not have to be the same as the loginname
• to remove the linked access use sp_revokedbaccess [username]
• mapping windows mapped user accounts is different format than sql auth sp_grantdbaccess 'Demo\Anne'

Database Roles

• Are similar to windows user groups
• Are specific to a particular database
• can include user and group accounts
• Are fixed and cannot be deleted or changed
• public is similar to everyone role
• implicitly provide users with specific permissions
• sp_addrole [rolename]
• sp_addrolemember [rolemembername]
   

Managing Permissions

• Statement Permissions control statement level permissions
• Are assigned in a particular database
• Control the ability of users to create databases
• Database specific
• Assigned to roles and users of the DB
• (Grant, Deny, Revoke)
• GRANT CREATE TABLE TO [username]
• DENY CREATE VIEW TO [username]
• REVOKE CREATE TABLE TO [username]
• REVOKE [permissionname] FROM|TO [username] use FROM and TO interchangeably
• GRANT ALL TO [username] to grant all permissions in one step
• REVOKE ALL FROM [username]
• CREATE DATABASE permission is only controlled in the master database of SQL server
• Object permissions control ability to modify data in objects
• Can be assigned in a specific DB for specific objects
• (SELECT, INSERT, UPDATE, DELETE, EXEC, DRI (declare referential integrity))
• Can also be specified on individual columns
• Implied permissions are acquired through membership in fixed server or database roles and through object ownership
• May effective at the server, database or object levels
• May not be transferable
• (only admins or owners can delete or alter objects use dbddl for restricted authority for altering)

Effective permissions

• All non-conflicting permissions are combined
• Denied permissions override the corresponding permissions
• Last set of permissions overwrites earlier assigned permissions
• All permissions are assigned at column level but table level permissions overwrite each column level permissions

Roles permissions take precedence over user level permissions

Designing Security Solutions

• Application Roles (application security)
• Are specific to a particular database
• Can be used only by applications
• Program applications to use application roles rather than users
• Provide access from the security context
• sp_addapprole [rolename], [password]
• GRANT SELECT ON [tablename] to [rolename]
• sp_setapprole [rolename], [password] will set the role of the user to the specified role
• grant access to guest account for databases
• Microsoft excel uses application roles

Ownership Chains

• Owns a set of objects that form a chain of reference
• sp_addsrvrolemember [username], [rolename]
• sp_changeobjectowner [objectname], [username] basically drops the object, clears permissions, and adds the user as the owner
• if dbo and the current user are not the object owner then you must qualify the object name
• Using Views, Functions, and procedures as security
• You can query tables below views as long as the users are the same or the user access has access to both objects (the same rule applies to sprocs)
• Users can only perform activities in the application roles, views, and procedures.

Security Solutions

• Can not restrict access to rowsets but can restrict access to views and revoke underlying table access from public.

Security Solutions2

• Use table valued functions to  update data in underlying tables and restrict access to a specific rowset and the current user with USER_NAME in subselect