How to reduce index size and free up space in a SQL database.

First, run this script to figure out the worst offenders:

create table #t
(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

declare @id nvarchar(128)
declare c cursor for
select name from sysobjects where xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id
end

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

Continue reading “How to reduce index size and free up space in a SQL database.”

Microsoft Exam 70-487 Study Notes

Estimate of Number of Exam Questions per Module

Module Qs 1:Overview of Service and Cloud Technologies 1
2: Querying and Manipulating Data Using Entity Framework 4
3: Creating and Consuming ASP.NET Web API Services 3
4: Extending and Securing ASP.NET Web API Services 3
5: Creating WCF Services 7
6: Hosting Services 3
7: Windows Azure Service Bus 1
8: Deploying Services 4
9: Windows Azure Storage 2
10: Monitoring and Diagnostics 1
12: Scaling Services 1
A: Designing and Extending WCF Services 4
B: Implementing Security in WCF Services 2
C: “Classic” XML and ADO.NET 4
D: LINQ 2

Total questions in exam 42 Continue reading “Microsoft Exam 70-487 Study Notes”

Encrypting Databases with Transparent Data Encryption

Quantum-Encryption-640x353 Transparent Data Encryption (TDE) enables you to encrypt an entire database.
TDE protects the database against unauthorized third parties gaining access to the hard disks or backups on which the database is stored. TDE encrypts the database by using a Database Encryption Key (DEK) that is stored in the database boot record.
The DEK is in turn protected by the database master key, which is in turn protected by the service master key. You can use BitLocker Drive Encryption, a full-volume encryption method supported by Windows Server 2008 and Windows Server 2008 R2, although this will not ensure that database backups are encrypted.

NOTE TDE AND TEMPDB
If any database on the instance uses TDE, the tempdb system database will also be encrypted.
To use TDE to encrypt a database, you must perform the following steps:

1. Create the master encryption key.

2. Create the certificate protected by the master key.

3. Create a DEK and protect it by using the certificate.

4. Encrypt the database.

The first step in deploying TDE involves creating a master encryption key. You do this by using the CREATE MASTER KEY ENCRYPTION BY PASSWORD statement. For example, you can accomplish that by using the following query:

USE master;
GO CREATE
MASTER KEY ENCRYPTION BY PASSWORD = ”;
GO

After you have created the master encryption key, the next step involves creating the certificate that will be used to encrypt the database. You can accomplish this by using the CREATE CERTIFICATE statement. For example, to create a certificate named ServerCertificate that uses the subject name Server Certificate, use the following query:

CREATE CERTIFICATE ServerCertificate WITH SUBJECT = ‘Server Certificate’;
GO

When the master key and certificate are in place, you can create the DEK for the specific database. You do this by using the CREATE DATABASE ENCRYPTION KEY statement. For example, the following query creates a DEK for the AdventureWorks2012 database:

USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;
GO

After all the appropriate keys and certificates are in place, you can encrypt the database by using the ALTER DATABASE statement. For example, to encrypt the AdventureWorks2012 database, use the following query:

ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

When using TDE, you should create a backup of the server certificate in the master database. If you lose the database server without backing this up, you cannot access data in a database protected by TDE. You can use the BACKUP CERTIFICATE statement to cre- ate a backup of the certificate and private key, both of which are required for certificate recovery. The private key password does not have to be the same as the database master key password. For example, the following code, when run from the master system database, creates a backup of the ServerCertificate certificate to a file called ServerCertExport and a PrivateKeyFile private key:

BACKUP CERTIFICATE ServerCertificate
TO FILE = ‘ServerCertExport’
WITH PRIVATE KEY (
FILE = ‘PrivateKeyFile’,
ENCRYPTION BY PASSWORD = ” );
GO

SQL Server will write these backup files to the MSSQLDATA directory of the instance.

Find missing indexes for SQL Server 2012

The following example generates a listing of missing indexes that could be beneficial for T-SQL batches that have been submitted to the query optimizer since the last time the SQL Server service was restarted:

T-SQL

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [Index_Useful] ,
igs.last_user_seek ,
id.statement AS [Statement] ,
id.equality_columns ,
id.inequality_columns ,
id.included_columns ,
igs.unique_compiles ,
igs.user_seeks ,
igs.avg_total_user_cost ,
igs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS igs
INNER JOIN sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
ORDER BY [Index_Useful] DESC;

Query Execution Results
Query Execution Results
Missing Index Details - Full listing showing the desirability status, columns to be used and user seeks
Missing Index Details – Full listing showing the desirability status, columns to be used and user seeks

As you can see, the most required index missing in the database above is one on the status table.
We can create the missing index using the following command:

CREATE INDEX IX_ERPStatus ON tblERPStatus (SystemID, ERPDocumentNumber)

Displaying Table Data with Classic ASP

This page demonstrates the capabilities how to display a table from a SQL statement.

 

<html><head>
<TITLE>dbsimple.asp</TITLE>
</head>
<body bgcolor=”#FFFFFF”>
<%
 this code opens the database
myDSN=”DSN=Student;uid=student;pwd=magic”
set conntemp=server.createobject(“adodb.connection”)
conntemp.open myDSN

 this code retrieves the data
mySQL=”select * from publishers where state=NY
set rstemp=conntemp.execute(mySQL)

 this code detects if data is empty
If  rstemp.eof then
response.write “No records matched<br>”
response.write mySQL & “<br>So cannot make table…”
connection.close
set connection=nothing
response.end
end if
%>
<table border=1>
<%
 This code puts fieldnames into column headings
response.write “<tr>”
for each whatever in rstemp.fields
response.write “<td><B>” & whatever.name & “</B></TD>”
next
response.write “</tr>”

 Now lets grab all the records
DO  UNTIL rstemp.eof
 put fields into variables
pubid=rstemp(“pubid”)
name=rstemp(“name”)
company_name=rstemp(“company name”)
address=rstemp(“address”)
city=rstemp(“city”)
state=rstemp(“state”)
zip=rstemp(“zip”)
telephone=rstemp(“telephone”)
fax=rstemp(“fax”)
comments=rstemp(“comments”)

 write the fields to browser
cellstart=”<td align=””top””>”
response.write “<tr>”
response.write cellstart & pubid & “</td>”
response.write cellstart & name & “</td>”
response.write cellstart & company_name & “</td>”
response.write cellstart & address & “</td>”
response.write cellstart & city & “</td>”
response.write cellstart & state & “</td>”
response.write cellstart & zip & “</td>”
response.write cellstart & telephone & “</td>”
response.write cellstart & fax & “</td>”
response.write cellstart & comments & “</td>”

response.write “</tr>”
rstemp.movenext
LOOP
%>
</table>

<%
 Now close and dispose of resources
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
%>

</body></html>

Database Fundamentals in SQL Server 2008


This course is aimed toward those students who are new to using Microsoft SQL Server.
This course is also useful to those students who are studying for exams:

•70-432: Microsoft® SQL Server® 2008, Database Administration

•70-433: Microsoft® SQL Server® 2008, Database Development

Lesson Introduction

A database is a repository of data that can be stored, manipulated, and retrieved. There are many database models. SQL Server specializes in the implementation of a relational database model.

The purpose of this lesson is to explore the fundamental components of a relational database system. Tables and relationships form the basis of SQL Server. The organization of these tables are known as normalization, which makes working with SQL Server efficient, regardless of whether you are writing queries or creating a database from the outset.

Finally, you will be introduced to additional objects that are implemented within a SQL Server database to support data.

Types of Databases

A relational database is a collection of tables that are related together by a common field through a relationship. The purpose of a relational database is to break down data into business entities such as customers, orders, and employees and reflect each entity as a table. This helps reduce data redundancy and improves performance.

Within a database, two models of database can be created.

Online Transactional Processing (OLTP) Online Analytical Processing (OLAP)
The tables are organized in a way that the database is more optimized for systems that perform data modifications such as inserts, updates and deletes.

Typically, deals with detailed information.

Users can still read data from these systems. However, read performance may be affected because the structure of these tables is designed to reduce the amount of redundant and repetitive data OLTP is commonly implemented in many relational database systems.

Data is up to date and is immediately available after the transaction that modified the data is complete.

Each transaction typically deals with small amounts of data.

In this model, data is stored is in multidimension views of aggregated and detailed data. These can be stored as tables in a SQL Server database, other storage models also exist.

Typically, deals with summary information.

The tables here are optimized for reading data. Although it is possible to modify data, the performance against OLAP may be impaired for write operations.

OLAP databases are managed by SQL Server Analysis services and is becoming popular with the need to analyze data becoming more of a business imperative.

OLAP represents historical data over time.

Each transaction typically deals with large amounts of data.

What Are Tables?

Tables are objects that are used to store data. A table consists of columns and rows.

Columns have a data type such as an integer or time data type associated to a specific column to control the type of data that can be entered.

Rows are used to hold specific information about a business entity such as a customer or an order.
Typically, each row consists of a unique identifier associated to a particular row such as a customerID or as in the graphic, the DepartmentID column to assign a unique attribute to a record.

A primary key constraint is typically associated with the ID column to ensure that the entity data is unique.

What Are Relationships?

Relationships are used to join tables together through common fields between two tables.

Primary keys can be used to enforce uniqueness of records within a single table. You can also use the primary key to enforce data integrity between two tables. This is known as referential integrity.
In a typical two table relationship, one of the tables will be joined to the other table by a primary key column. The table that has the primary key is known as the primary table, and all records in the primary key column are unique. The secondary table on which the column is joined from the primary table typically has repeating values. The column that holds the repeating values is referred to as a foreign key column. This creates a relationship known as one to many and is the most common relationship type used.

By default, the impact of this relationship on joined columns is:

 

  • A value cannot be placed in the secondary table unless it exists in the primary table.
  • A record cannot be removed from the primary table if it has related records existing in the secondary table.
  • A record cannot be updated from the primary table if it has related records existing in the secondary table.

This ensures that data integrity is retained and that accidental deletions and updates cannot occur. This default behavior can be modified. Additional relationships that can occur in a relational database management system include the following.

  • One-to-One. There is one record in the secondary table for each record in the primary table.
  • Many-to-Many. There are many records in the secondary table for many records in the primary table.

What Is Normalization?

Normalization is the process of organizing data and tables within the SQL Server database. This is implemented by creating tables and setting relationships between the table to remove repetitive and redundant data.
Redundant data occupies unnecessary space in the database. Moreover, a change in information in one area requires a change in all areas where the same data exists because data is repeated in several locations. This can be particularly problematic if the data changes frequently.
There are many degrees of normalization. For use in commercial databases, it is typical to only implement one of three levels of normalization. These typically set rules for how data should be broken down.

These are referred to as:

 

  • First Normal Form (1st NF)
  • Second Normal Form (2nd NF)
  • Third Normal Form (3rd NF)

Normalization is a useful guide in breaking down data into a relational format. However, there can be slight variations when it is implemented. Ultimately, remember the purpose of normalization.

  • To prevent waste of space
  • To make data consistent
  • To ease long-term maintenance
  • To ease data updates and modifications

Additional SQL Server Objects


Tables are the fundamental storage object of data within a SQL Server 2008 database. There are also additional objects that can be created in SQL Server, which are used to support, manage, and protect the data that is stored within the tables. The following is an overview of each object and the purpose that it serves within SQL Server.

SQL Server Object Description
Constraints Constraints are objects that are associated with single or multiple columns within a table. Constraints ensure that data that is inserted or updated meets the requirements that the constraint places on the data.

The type of constraint objects that can be associated to a table are:

Primary key

Unique constraints

Foreign key

Default constraint

Check constraint

XML schemas

Indexes Indexes are objects within SQL Server that provide pointers to the data. They act as a bridge between queries and the data.Indexes can speed up data retrieval from a database if the appropriate indexes are created.

Without indexes, SQL Server scans an entire table to find data that will be required in a query.

Moreover, like an index in a book, an index in a database requires disk space.

Stored Procedures Stored procedures are SQL Server objects that encapsulate T-SQL logic within a named object.Stored procedures can hold T-SQL statements for repetitive or complex T-SQL queries and tasks without the need to rewrite the T-SQL statement.

The main advantage of stored procedures is that they are precompiled.

This means that the first run of a stored procedure has to work out the best way to retrieve data for a query or a task before it is executed.

Subsequent execution of the same stored procedure will take advantage of the previous execution plan that was previously worked out.

This can substantially improve the performance of queries and modifications that are conducted through stored procedures.

Stored procedure objects can also be secured, so access to the data can be controlled.

Views Views are objects in SQL Server that are analogous to virtual tables. Views consist of rows and columns like a table, and they can be secured to control access to the data like stored procedures.Views can pull data from any number of tables into a single virtual table. This provides the following benefits:

Focuses the data for the user.

Simplifies the management of permissions.

Hides the complexity of the underlying tables.

Provides shared application logic for .NET applications.

In certain situations, views can be used to improve performance.

Functions Functions are objects within SQL Server that can be used to manipulate or change data.SQL Server provides a wide range of system-supplied functions that can be used against data in the database, such as:

CONVERT – To covert data from one data type to another

GETDATE() – To retrieve today’s date

However, SQL Server provides database professionals with the ability to create user-defined functions if the SQL Server system functions do not provide the required functionality.

Data Manipulation Language (DML) Triggers Like stored procedures, triggers are SQL Server objects that encapsulate T-SQL logic within a named object.However, triggers are event-driven. A trigger is an object that is associated to a table or a view and dependant on the type of trigger defined will only fire when one of the following T-SQL statements are issued:

Insert

Update

Delete