To normalize a database, the primary goal is to avoid repetitive data. Examples of normalization. You must understand how to normalize data for this test.

SQL server has no Many-to-Many relationships. If you have many-to-many relationships, you need an intermediate table to hold relations. An example would be for Orders and Products. A customer can order many products, and a product can be ordered by many customers. Between Orders and Products, place an order details table to normalize.

For more information on normalization: http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2oby.asp

The Primary Key is a record identifier, like EmployeeID or ProductID. It can be any field or combination of fields that is unique to each record.

Primary key can be a combination of fields. An example would be LastName and FirstName for an Employees table, Use the LastName as the first item in the key declaration since that is the most specific identifier (more people have the same first name than last name).

UNIQUE constraints enforce the uniqueness of the values in a column (or set of columns) and ensure that no duplicate values are entered in specific columns. This is useful to ensure uniqueness in columns that do not participate in a PRIMARY KEY. The PRIMARY key already has Uniqueness enforced.

A FOREIGN KEY constraint references a primary key in another table. When a table has a FOREIGN KEY constraint, and entry has to be made in that feild, and the value of that entry must exist in the foreign table. A foreign key is what defines the relationship between two tables.

When creating the database, the default size is 1MB. Size can be specified as a number and a multiplier of KB, MB, GB, or TB. If only a number is given, the size will default to MegaBytes.

Ex: SIZE = 100 would create a 100MB database. SIZE = 2 GB would create a 2 GB database

To specify the initial size, calculate the data size of a single row, and multiply by the estimated number of rows for the table. Do this for each table. Remember to leave a little room for growth. Add 1MB for SQL Server Modeling.

Use the FILEGROWTH parameter when creating a database to specify the increment at which the database should grow each time it must expand. The FileGrowth property specifies the growth increment of the operating system file used to store table, index, or log data. The growth value can be specified in either a size (MB or KB) or as the percentage of the current database (default is 10%).

When calculating growth, remember growth is only records added. If current records change then the database does not grow.

Tables that are frequently joined together are frequently accessed at the same time. Place these on different filegroups on different arrays to distribute the work load for read/write operations. Non-clustered indexes do not need to be in the same filegroup as the tables they support, so these can also be separated. Only clustered indexes need to be in the same filegroup as their tables.

Tables and Indexes

A check constraint is a rule that specifies the values allowed in one or more columns of every row of a table. A check constraint is limited to checking values within a single row of a table.

A Clustered Index is part of the table itself. The table will be sorted by the clustered index column. There can be only one clustered index per table.

A non-clustered  index is stored separately from the table of data. In a non-clustered index, the data of the reference column is duplicated, and pointers are created to point to the rows of the actual data.

More Index Basics: http://www.awprofessional.com/articles/article.asp?p=28285

To speed searches, any column often referenced in a WHERE clause should be indexed.

If multiple columns in different tables are referenced in a WHERE clause, create an indexed view of the tables.

To create a clustered index on a view, the view must be created with the SCHEMABINDING option. SCHEMABINDING binds the view to the schema of the underlying base tables.

A non-clustered  index is analogous to an index in a textbook. The data is stored in one place, the index in another, with pointers to the storage location of the data.

The FILLFACTOR property is used to specify the percentage that each page of an index should be filled during index creation. The remaining space is used for future growth without fragmentation.

When you set the IDENTITY property for the column, it then becomes the unique key for the table.

Example: CREATE TABLE Employees (EmployeeID INT IDENTITY (2, 1) PRIMARY KEY NOT NULL)

* The EmployeeID column will now start with the first ID being 2 and increase by 1 for each additional employee added.

When altering a table or importing large amounts of data, use the ALTER TABLE statement with the NOCHECK CONSTRAINT to temporarily turn off checks or avoid foreign key constraints.

A user defined function can be used to store a calculation as a row; like for a tax table.

Views

A view that is schema-bound must be un-bound before it can be deleted.

The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view. Ref: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp

A client application queries a view exactly the same as it queries a table.

SQL Server uses a publisher-subscriber metaphor for its data replication feature. The publisher makes information (data) available. A distributor forwards any changes to the appropriate subscribers. The subscribers receive the information.

Replication Architecture: http://msdn.microsoft.com/library/en-us/architec/8_ar_ra_6u05.asp

A Snapshot copies an entire view of data to another computer. The destination database view is overwritten with the new version.

A Snapshot file cannot be compressed in the default snapshot folder, you must make a snapshot copy in another folder to compress it.

For a SQL Replication overview: http://www.microsoft.com/sql/evaluation/features/replication.asp

• Design a partitioning strategy.
• Design and create constraints and views.
• Resolve replication conflicts.

The BulkInsertTask object, based on the Transact-SQL BULK INSERT statement, provides the fastest method for copying large amounts of data from a text file to SQL Server. BulkInsertTask should be used for copying operations, and in situations where performance is the most important consideration. It is not used in conjunction with transformations during data import operations.

To use the bulk insert object on a text file:

1. Use Data Transformation Services and Microsoft Visual Basic Scripting edition to create a Package object.
2. Create a connection object for the text file.
3. Add a BulkInsertTask object to the Package object.
4. Use the Execute method of the package object to load the data.

DTS can be used to import data from text files, XML, or other databases into a SQL Database.

To import data from a text file into two data tables, create two DTS tasks.

OPENXML

OPENXML provides a rowset view over an XML document, similar to a table view, only accessing XML documents.

OPENXML( DocumentHandle, RowPattern, Flags) [WITH (SchemaDeclaration | TableName)]

- DocumentHandle is the document handle of the internal representation of an XML document.
- RowPattern Is the XPath pattern used to identify the nodes
- Flags Indicates the mapping that should be used between the XML data and the relational rowset

Ref: http://msdn.microsoft.com/library/en-us/tsqlref/ts_oa-oz_5c89.asp

To write queries against an XML document by using OPENXML, you must first call the sp_xml_preparedocument system stored procedure, which parses the XML document and returns a handle to the parsed document that is ready for consumption.

To output data in the XML format, extract the data using a SELECT statement with a FOR XML clause.

Linked Servers

To connect two servers: On one, execute the sp_addlinkedserver system stored procedure, linking the two servers. On the other, execute the sp_addlinkedsrvlogin system stored procedure, which will finish the mapping of the linked server.

Methods include linked servers, OPENQUERY, OPENROWSET

Commands to know:

Data Control Language (DCL) statements

GRANT Creates an entry in the security system that allows a user to work with data or execute certain Transact-SQL statements.
DENY Denies a permission to an object, and prevents any other permissions from being inherited. Deny overrides all other permissions.
REVOKE Removes a previously granted or denied permission.

Data Definition Language (DDL) statements :
CREATE object_type object_name. - Used to create a new object
ALTER object_type object_name. - Used to modify an existing object
DROP object_type object_name. - Used to remove an object
* Objects can be Databases, Tables, Stored Procedures, Views, etc...

Data Manipulation Language (DML) statements:
SELECT - Used to retrieve data from a database
INSERT - Used to add new records to a database
UPDATE - Used to modify existing records in a database
DELETE - Used to remove records from a database

Statement Level language elements enable you to control the flow of logic in a script:
BEGIN…END Blocks These elements enclose a series of Transact-SQL
statements so that SQL Server treats them as a unit.
IF…ELSE Blocks These elements specify that SQL Server should execute the
first alternative if a certain condition is true. Otherwise, SQL Server should
execute the second alternative.
WHILE Constructs These elements execute a statement repeatedly as long as
the specified condition is true. BREAK and CONTINUE statements control the
operation of the statements inside a WHILE loop.

To access a specific database, use SELECT * FROM Server.DBName.DBO.TableName

To output data in the XML format, extract the data using a SELECT statement with a FOR XML clause.

To delete all the rows in a table, TRUNCATE TABLE is faster than DELETE since DELETE removes rows one at a time and records each deleted row in the transaction log. TRUNCATE TABLE simply removes all entries from a table.

Use JOINs to retrieve data from two or more tables based on logical relationships. Joins can be categorized as: INNER JOIN, OUTER JOIN, or CROSS JOIN. A FULL OUTER JOIN accepts NULL values in the joining columns.

The HAVING clause specifies a condition for a search. HAVING must be used with the SELECT statement, and is usually used in a GROUP BY clause to organize the results. The conditions in the HAVING clause are applied to the grouping, and only the groups that meet the conditions are returned.

The @@ROWCOUNT variable returns the number of rows affected by the last statement. The @@ROWCOUNT is reset for every new statement that executes.

For a tutorial on T-SQL, please visit: http://www.w3schools.com/sql/default.asp
 

Optimistic locking with explicit transaction is the best method for positioned updates.

Cursors can be used to jump through data. For example to return every other row from a cursor, use FETCH RELATIVE 2 to jump two rows for the next set of data, use FETCH RELATIVE 5 to jump five rows, etc...

Increase performance by setting the CURSOR THRESHOLD to Zero.
The command for this is EXEC sp_configure 'cursor threshold', 0
Ref: http://msdn.microsoft.com/library/en-us/adminsql/ad_config_7xda.asp (Cursor Threshold Option)

When possible, you can reduce load on the server by using Client API cursor in the client application to move through data. This allows an entire result set to be sent to the client, and the client can scroll through the data without having to contact the server for each row.

A range of table-level locking hints can be specified using the SELECT, INSERT, UPDATE, and DELETE statements. These locking hints override the current transaction isolation level for the session.

More on Locking Hints: http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_1hf7.asp 

When two transactions may result in a deadlock, use DEADLOCK_PRIORITY: Set to LOW for transaction that should lose.
More on locking priority: http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_8ynt.asp

When confronted with a deadlock event, use SQL Profiler to capture the deadlock event

To avoid locking, have different update statements that access the same tables access those tables in the same order.

For Example: Transaction A updates customer information, then order information. Transaction B updates orders then customer information. A deadlock happens when Transaction A has locked customers and is waiting on orders, at the same time, Transaction B has locked orders and is waiting on customers.

If we change the order of each transaction so that both Transaction A and B update customer information then orders, there is no way for the two to deadlock.

Use the WITH ENCRYPTION clause to encrypt the text of a stored procedure. The stored procedure's definition cannot be viewed by other users once the WITH ENCRYPTION clause is applied to that procedure, since the procedure definition is stored in an encrypted form.

Use stored procedures for repetitive tasks, or to hide procedure function from users.

A parameterized query is a stored procedure that permits flexible row/column selection using a parameter in the WHERE clause of a SQL statement.

A stored procedure parameters can have a default value. When the parameter is not specified by the calling application, the default is used. If a value is supplied for the parameter, the supplied value is used.

54. When you specify the WITH RECOMPILE option in a stored procedure definition, it tells SQL Server to recompile this stored procedure each time it is executed. SQL will not cache a plan for the stored procedure. Because the stored procedure must be recompiled each time it is executed, it will execute more slowly. This option should only be used when stored procedures take parameters whose values differ widely between execution, resulting in different execution plans to be created each time.

The RAISEERROR statement can be used to generate an error to be sent back to the calling application. Add the WITH LOG option to log the self-generated error

Some system Stored Procedures to know:

xp_sendmail is the function used in SQL 2000 to send email messages to a defined recipient

The xp_logevent extended stored procedure logs a user-defined message in the Microsoft SQL Server log file and in the Microsoft Windows 2000/NT Event Viewer.

The sp_recompile function Causes stored procedures and triggers to be recompiled the next time they are run.

Triggers are a special class of stored procedure that execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. Triggers are powerful tools that can be used to enforce business rules automatically when data is modified. Triggers can also extend the integrity checking logic of SQL Server constraints, defaults, and rules, although constraints and defaults should be used instead whenever they provide all the needed functionality.

Triggers can be set as FOR, AFTER, or INSTEAD OF, and are set to work with DELETE, INSERT, and / or UPDATE statements.

A trigger can be used to validate data as it is added or updated to a database, and to generate custom errors.

Triggers can supply validation where other SQL validations will not work. For example: Checking if a customer is listed as active before inserting an order.

A FOR UPDATE trigger can be used to check the data values supplied in UPDATE statements.

A FOR INSERT trigger can be used to check the data values supplied in INSERT statements.

An AFTER trigger is often used to remove old entries from a table after a new entry is made.

INSTEAD OF INSERT triggers can be used to intercept an INSERT command, and modify values before inserting the record. This is useful for maintaining the integrity of the primary key.

INSTEAD OF triggers are most useful for extending the types of updates a view can support and can provide the logic to modify multiple base tables through a view.

For detail on trigger types: http://msdn.microsoft.com/library/en-us/tsqlref/ts_aa-az_9036.asp

Use the sp_lock and sp_who system stored procedures to find locked resources and to identify processes that are holding locks.

Use non-clustered  index to combine columns for a common index to speed searches of items, for example when a application looks up orders by referencing City and Date columns.

If you have a performance issue with a query, the very first step you should take before analyzing it is to update the statistics on all the tables in your database.

The Index Tuning Wizard helps to identify tables and columns where indexes can be added to increase the overall efficiency of a database.

When running a command to access an object not owned by you, qualify the name of the object with the owner value. For Example, to drop a trigger owned by Admin2, use DROP TRIGGER Server.DBName.Admin2.TriggerName

When the owner of an object is a user, the owner is required to grant the proper permissions to each user of the objects.

Permissions can be assigned to the Database, table, column, or even row level.

To limit data exposed in tables with sensitive information, create a view of the table with only necessary data, and grant access to the view. 

Example: You are not the database owner, but you create a view. You need to give others permissions to the view. You must make the owner of the database the owner of the view before granting the permissions to the view.

When an Internet application needs access a database, consider replacing the SQL commands with stored procedures. This restricts direct access to tables.

To set up an application role you must:
O   Create the application role.
O   Assign a password to the application role.
O   Configure the application to use the application role to access a SQL Server database.
O   Assign appropriate permissions to the application role in the SQL Server database.

17. Grant application roles for custom applications where users only need to access data through the application.

• SELECT TOP 20 WITH TIES
• 62. COALESCE
• 62. CAST
• Positioned update