DB Dev 70-433 Implementing Tables & Views Code Crackin #23

Hey Now Everybody,

This is a review of the first objective on the Microsoft exam 70-433 ‘Implementing Tables & View’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

Objective: Implementing Tables & Views

1. Implementing Tables & Views (14 percent)
1.1 Create & Alter Tables 3.1
1.2 Create & Alter Views 5.4
1.3 Create & Alter Indexes 6.2
1.4 Create & modify Constraints 3.2
1.5 Implement Data Types C3.1 & 8.1
1.6 Implement partitioning solutions c6.2

During this series I plan on using the ASP.NET membership database for many of the examples. The reason for choosing this db is since it’s a public database & used in real world apps more so than the sample db’s such as northwind or AdventureWorks.

1. Implementing Tables & Views (14 percent)
1.1 Create & Alter Tables 3.1

Creating tables is not just defining columns. We have to choose data types correctly & implement data integrity.
Data types & how they behave is an important skill so we can use them correctly.
Data integrity is important to protect your data.

1.2 Create & Alter Views c5.4

A view is a select statement stored in the db
Views return a single result & cannot use temp tables
We can update data though a view
If a view doesn’t meet requirements for data alters, we can create an INSTEAD OF trigger to process the data modification instead.
A portioned view can be created by using a UNION ALL on two tables
Distributed partitioned views use linked servers to combine multiple tables across servers.
We can create a unique, clustered index on a view to improved performance.

1.3 Create & Alter Indexes 6.2

Indexes typically help read performance but hurt writing performance
Indexed views can increase performance.
It’s important to determine which columns to put the index key.
Analyze which indexes are being used so we can drop ones that aren’t will save storage space.

1.4 Create & modify Constraints c3.2

Implement constraints to verify data integrity
Implement constraints to support the optimizer

1.5 Implement Data Types C3.1 & 8.1
Attribute that specifies the type of data.

1.6 Implement partitioning solutions c6.2

CODE examples in a test database named dbTomato

Let’s check out a script for creating a table such as the simple aspnet_users

Use dbTomato 
CREATE TABLE [dbo].[aspnet_Users]
(
[ApplicationId] [uniqueidentifier] NOT NULL
, [UserId] [uniqueidentifier] NOT NULL
, [UserName] [nvarchar](256) NOT NULL
, [LoweredUserName] [nvarchar](256) NOT NULL
, [MobileAlias] [nvarchar](16) NULL
, [IsAnonymous] [bit] NOT NULL
, [LastActivityDate] [datetime] NOT NULL
)

Create two new data types Code example:

Create two new data types Code example:

 01 
 
Use dbTomato
02 CREATE TYPE dbName.NAME FROM NVARCHAR(60);
03  
04 CREATE TYPE dbName.CURRENCYVALUE FROM DECIMAL(12,5);
  
Create View 
  
create view v_aspnet_Users_anonymous 
05 as  
06 SELECT UserId, UserName, IsAnonymous 
07 FROM dbo.aspnet_Users
08 WHERE (IsAnonymous = 1)
09 GO
  

Clustered indexes & non-clustered indexes differ.
10 ALTER TABLE dbTomato.aspnet_User
11 ADD CONSTRAINT PKUserID
12 PRIMARY KEY NONCLUSTERED (UserID);

Code to Modify existing table this example alters the aspnet_Users table by changing the datatype to navarchar(17) & allow not null:

ALTER TABLE aspnet_Users
13 ALTER COLUMN MobileAlias nvarchar(17) NOT NULL;
14 Constraints, a real constraint from the aspnet_Users table is below:
15 ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (0) FOR [IsAnonymous]
16

---------------------------------------

GO

Question Concepts Examples

1.1 When we have a field that we want to be unique such as two rows not having the same value we can right click in the design view of the table on the column & add a key with the Is Unique property set to True.

1.2 Adding a foreign key constraint

1.3 Alter table to use less space

1.3.1 Changing the data type of the Volume column from bigint to int has the potential to truncate data, but the conversion is valid

1.3.2 Nothing in the table declaration prevents it from being compressed; therefore, REBUILD WITH (DATA_COMPRESSION = ROW) is a valid alteration.

1.4 Storing videos Microsoft SQL Server 2008 introduces the FILESTREAM storage attribute for binary data stored in a varbinary(max) column, which stores binary data on the local file system rather than in the database file. This optimizes read performance for large binary objects, which makes it the best option for the application being developed.

1.5 Minimize execution time --: Indexing the monthlySalary column of the view minimizes the execution time of the GROUP BY clause of the statement in question. To create an index on a view, it must also include a unique clustered index.

1.6 Prevent users from coping a view The WITH ENCRYPTION option of the CREATE VIEW command encrypts the view definition in sys.syscomments so that it cannot be viewed by anyone, even the view's owner

1.7 A row is deleted from a table w/ an identity column . to reinsert the data we : SET IDENTITY_INSERT Products ON; allows explicit values to be entered into an identity column for the remainder of the current session or until it is turned off again.

1.8 The correct syntax for creating this alias data type is CREATE TYPE salary FROM decimal (8). The data type should be based on the decimal type rather than the float type because the values included do not exceed 10 million and a decimal column with a precision of 8 uses less storage space than does a float column

1.9 Decrease time it takes statement to execute: CREATE INDEX expertise_index ON Contractors (expertise) WHERE lastUpdated > '20080101'; is the best choice because it limits the index to only the rows relevant to the statement in question, which minimizes both the size of the index and the time to search the index.

1.10 Grant persmissions The view grants users the access they need while denying them access to any other portions of the database. It also provides flexibility for the users to work with the data as they see fit.

1.11 Add column to same table on multiple servers we create a server group

1.12 Improve perf by partitioning table

1.13 Reduce storage: Setting FILLFACTOR to 70 leaves 30 percent of the space on each leaf-level page empty, allowing for future growth and reducing page splits.

1.14 Xml file data -- > create a table

1.15 Improve select speed Add the PERSISTED option to the Profitability column. The PERSISTED option increases performance when a calculated column is retrieved at the expense of performance when the column is inserted or altered. This is achieved by performing the calculation when the data is entered and storing the result in the database. When the Profitability column is not PERSISTED, the nested CASE statements must be parsed each time the column is queried. Because retrieving calculated data is faster than calculating both CASE statements, making the Profitability column PERSISTED results in the largest performance gain.

1.16 Insert row with view & where clause

1.17 Transfer data from partition

1.18 Altering table not success due to WITH SCHEMABINDING When a view is created with the WITH SCHEMABINDING option, none of the rows used in the view can be altered without dropping or altering the view first.

Next up the other objectives in the exam!

That is all there will be more,

As always all comments welcome,

Catto