Implement partitioning solutions

May include but is not limited to: partitioned tables and indexes (constraints, partition functions, partition schemes, MERGE, SPLIT, SWITCH); distributed partitioned views (constraints, linked servers).

Partitioning means that you store table data in different file groups, instead of using only one file group. By default, SQL Server treats every table as if it were partitioned, but having only one partition.

Partitioning is implemented horizontally, so that you store collections of rows in different file groups, not columns. You should consider partitioning a table when it is excepted to contain lots of data, which is being used in different ways. MSDN comes up with the example of a sales table, with the current month used primarily for inserts, and the previous ones for selects. This is a good candidate for partitioning solutions, because of the different operations performed on the different parts of the table.

To create a partitioned table, you need to do the following:

  • Create a partition function
  • Create a partition scheme
  • Create the table using the scheme

Continue reading “Implement partitioning solutions”

Create and modify constrains

May include but is not limited to: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling, NOCHECK, SET IDENTITY_INSERT

Some of us (including me) with the ASP.NET WebForms background may think that validating data before it can be sent to the database eliminates the need of further refinements. However, we don’t have the power of ensuring that our database can be only accessed from our application, and some extra layers of validation certainly won’t hurt for anyone.

SQL Server 2008 allows a set of validation mechanisms, such as ensuring that two rows can’t hold the same values, identities, and cascading referential support. We can also define custom validation logic with the help of the CHECK keyword. But let’s start from the beginning.

SQL Server stores relational data. To be able to query this data efficiently, it’s a good idea to create a key for each column in a table. Primary keys are the tool that’s designed for this. A primary key creates a set of unique identifiers on a given column, thus preventing the same data to be inserted, and even creates clustered indexes.

There are three ways to define a primary key: in the CREATE TABLE clause, you can set one on the column level, on the table level, or you can use an ALTER TABLE clause to add one later. I think the only one worth mentioning is adding a constraint later:
Continue reading “Create and modify constrains”

Create and alter indexes

There are two types of indexes in SQL Server: clustered and nonclustered. The difference is that a clustered index is the actual table, which you can imagine as containing all columns of the table. A nonclustered one, in contrast contains only the columns referenced In its definition.

To take a further step, let’s discuss the question of covered and noncovered indexes. While clustered and nonclustered refers to the table itself, the question whether or not an index is covered is based on the queries. An index is considered to be covered when all columns referenced by a query is contained in it. Let’s assume we have the following query:

SELECT Column1, Column2 FROM Table

In this case, to cover this query, we need a nonclustered index with columns Column1 and Column2. To do this, you’d call:

CREATE NONCLUSTERED INDEX MyIndex ON Table (Column1, Column2);
Continue reading “Create and alter indexes”

Create and alter views

This objective may include but is not limited to: WITH ENCRYPTION, WITH SCHEMABINDING, WITH CHECK OPTION, manage permissions (GRANT, DENY, REVOKE)

A view is a select command stored in your database. Further simplifying, it’s a pointer to a select command. Nothing else but the command is stored, and executed when you query the view.

What is the point of it? You might want to show only a set of columns instead of all of them, or you have a complex select command on a few tables, and you would like to call it easily.

To create a view, just use the following syntax:

CREATE VIEW ViewName
AS
Select statement

To alter it, use the ALTER VIEW statement. There are a few options you can specify, preceded by a WITH clause: SCHEMABINDING, ENCRYPTION and CHECK OPTION.

  • WITH SCHEMABINDING: there are times when you want to change a column name in a table. This, however, would break your view, because the SELECT command is evaluated only in its creation time, when you create the view. To overcome this, create your views with the WITH SCHEMABINDING clause. This won’t let you change your column name, when a view references on it.
  • WITH ENCRYPTION: encryption simply obfuscates the SELECT statement. It’s not really an encryption method, and can be easily reverse-engineered, so it’s generally discouraged.
  • WITH CHECK OPTION: when you update data through the view, WITH CHECK OPTION validates the data regarding the columns in the SELECT statement. This makes sure that the modified data is visible in the view. Note that if you update data in the underlying table directly, CHECK OPTION will do you no good.

Create and alter tables

This objective may include but is not limited to: computed and persisted columns, schemas, scripts to deploy changes to multiple environments, for example, dev, test, production; manage permissions (GRANT, DENY, REVOKE)

Tables are the basis of SQL Server data. A table is built from columns and rows, each value is found in the intersection of the two. SQL Server offers the fully blown DDL, Data Definition Language dedicated to build tables.

Creating a table is pretty straightforward:

CREATE TABLE database.schema.name

( columnName colunmType constraints NULL/NOT NULL, secondColumnName secondColumnType secondConstraints…);

Now a column name must conform to some rules, e.g. it must be unique, it cannot contain reserved keywords of SQL Server, and some special characters, but anybody with some kind of programming background knows these rules immediately.

There’s the possibility to create computed columns, which have some restrictions, but I think these are pretty easy to understand, for example, you cannot insert or update values into/of them. To create a computed column, use the following syntax:

ComputedColumn AS (column1 * column2),

Note that you don’t have to specify a type for these columns. Computed columns are cool, but they are virtual. This means that every time you reference on them, they get calculated, every one of them. You can imagine that this could be a drawback real soon. To override this default behavior, just use the PERSISTED keyword in the CREATE/ALTER table clause to persist your computed columns in the database.

Suppose you have a table, but want to change it in some way, for example, add a column. In this case, you should use the ALTER TABLE clause, which works exactly the same as CREATE TABLE.

Some words on schemas. Schemas provide a way to group your related tables, stored procedures, and various database objects. Also, they lets you control access on the items contained in your schema in one place, so get used to work with them. To create a schema, you use the intuitively named CREATE SCHEMA clause, along with a schema name.