HOME BUILDER
JAMES HEIN
This week I have been doing a lot of database work with Microsoft's SQL Server Express Edition.
My first comment is that if you are going to do any serious work using a database, get the full version.
That being said, SQL Server Express is free and for many people this will be perfect. One thing I have noticed is that "(local)" does not work as a database name when using Express, so for any referencing you must use your computer name e.g. MY-COM\SQLSEXPRESS otherwise it can't find it. Not sure why this is, or what MS is trying to do here, but the first time your code cannot see the database, remember this.
Make sure that you have a lot of spare space on your database drive. Every time you do something the log file will grow bigger, often to may times the size of your database. Here is how you can manage this.
From within Server Management Studio Express, right click on your database (or one of them) Select Task, Shrink, Files and look at the dialogue box. Select Log and look at the Currently Allocated and Available Free space boxes. Sometimes these can get very large. Start by releasing the unused space (default), and click OK. Just dong this occasionally can really free up some disk space.
If like me one of your databases is loaded then left alone, you can also shrink the size of the database if it has too much wasted space. Depending on the size of the database and the power of your computer, some of these operations can take quite a while.
If you have a backup of your database, and yes you should do this often, then it can be quicker sometimes to delete the database and restore it, especially if you are developing. When things go live you have to take greater care with this.
When you are working with larger databases, with lots of records then adding indexes is your friend. Let's say you want to load up a table with records and your "Where" clause looks something like:
Where C.Country - Code = D.Main - Country
And (D.Special - code="FRED" or D.Special - code = "MARK")
Now imagine that table D has six million records. If you have not set up an index on Special - Code and Main - Country then the poor old SQL Server engine is going to be working very hard to find all of your matches. If you add the index then the whole process will got a lot faster. From within Server Management Studio Express expand a table and right click on the Indexes folder. Add New Index and in the dialog box give it a name, click on the add button, pick your field and then OK, OK again and you're done. If you want more details or info click on help.
To get you started, a table can have only one clustered index, typically the primary key, and defines the order in which the record is stored in the database. So if you are regularly scanning the table in a particular order then this would also be the field to make clustered.
The best way to start in databases is to download SQL Server Express from the Microsoft site, make sure you get everything, and start playing around. Create a couple of tables, get the SQL to build NorthWind or some other database.
Then try some basic SQL. Right click on a database and select New Query. In this window, play with some SQL. Next, try accessing the data from a Visual Studio Express application or using some other language you are familiar with. This is also free to download but you will need to pick Windows Forms or ASP.Net versions depending on what you want to try first. I use these products on a daily basis and for the most part they work quite well.
Email: jclhein@gmail.com.
Prev
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Next
