Go ahead and expand the branch of our database and we'll go through these options.


The Database Branch
First we have the database diagrams. This will have SQL server automatically build a diagram of your table relationships within your database. I don't ever use this and most other people don't either. It may be helpful in explaining your database table relationships to someone that doesn't know much about databases.

The tables branch is for your tables. Right click on the branch heading and choose New Table. You've been through this on a previous tutorial already.


Creating a Table

The right side holds configuration information for the table as a whole. The (name) holds the actual name of the table. Most of the rest of the info you won't need to deal with. The top area holds your column selections. You have the name, the data type the column holds and whether or not that column accepts a null value. For a list of all the data types and what they do, check MSDN.

Beneath that are the column properties. As you click on the columns in the upper window you can change their properties down here. Here you can also set the column name and data type. Additionally, you can create computed columns, making columns that are automatically calculated off of other columns, for instance. Below that is a description you can use to describe the column.

There's also a full-text specification. What this does is index that column for full-text searches. This makes searches significantly faster but is typically only useful for text fields that hold large amounts of textual data. Next we have the Identity Specification. This lets you set a field as the identity for the table. An identity field means that this column must be unique for each row. It's a reliable method for addressing any single row in the table. You can only set identity on numeric-type columns like int, bigint and numeric. The identity increment is the number that the identity is incremented each time you insert a new row while the seed is the number it starts at. For instance, with an increment of 1 and a seed of 1, the first row you insert will be 1, then 2, 3, etc etc. If you set the increment to 3 and the seed to 5 your first row would be 5, then 8, 11, etc. I think you get the idea. If you ever want to completely reset your table, including deleting all rows and resetting the identity count, just truncate the table. The command is truncate tablename.

The Table Context Menu

Save your table. You'll see dbo.users saved on the left (assuming you named it users like I did). You can right-click on the users table to get a variety of options. Edit allows you to edit the table via a generated SQL query. Design takes you back to the view when you first created the table, allowing you to make changes or add new columns, for instance. Open table takes you to a view of all the rows in the table. You can add, edit or delete rows here. Script table as automatically generates different types of SQL statements for you.

View Dependencies lets you see any relationships you may have set up with other tables. You can use reports to load an rdl file for reporting purposes. I doubt you'll ever need that functionality.

The Table Tree

Expand the dbo.users table and you'll see several options below it. The columns folder lists all your columns and their data types. You can right click on them for various options. Below this is the keys. You can use this to make a foreign key. Don't worry about that yet.

Next is the constraints folder. This will list constraints you've set on the table. Once again, don't worry about that yet. Next we have indexes. This controls how the table is indexed for searches. Intelligently indexing a table will greatly improve the performance of a database. Lastly we have statistics which are used for statistical information.

Below the tables folder we have a few more options. Then we can get into SQL. Views are similar to a table. You can basically take an existing table (or tables) create a SQL statement and have that populate a view. This can be very handy if you need to do some complex SQL statements for various web apps. Rather than duplicate a complex sql statement, you can just put it into a view and it acts like a simple table.

Next we have the synonym. What this does is allow you to create an alias for a long, fully qualified name. If you're accessing data on another, linked SQL server this can be a very helpful and time-saving option. However, in most cases you won't ever need to worry about this.

Programmability

Programmability contains another group of options. Stored procedures are a way of programming SQL tasks. It can allow you to store a complex set of SQL operations and then call them. We'll cover stored procedures later. Functions are used similarly to stored procedures but with some limitations. Deciding which to use depends on the circumstances. Database triggers are used to trigger certain actions (like updating a table) when something happens. For instance, I could use database triggers so that every time a new user registers, I update another table with the current count of users. That example's a bit pointless but it shows what triggers can be used for.

Assemblies are used for SQL server programming. This is beyond my experience. Types lists the various data types and lets you define your own. Rules are used to set up rules on the SQL server. I've never used rules but if you're interested you can check TechNet. Last in the list we have Security. This allows you to adjust security options for your database.

And there you have it. With a basic understanding of the interface, let's start doing some SQL.