Microsoft & .NETVisual BasicQuick Start Guide to SQL Server 7 -- Part 3

Quick Start Guide to SQL Server 7 — Part 3

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

A hearty welcome to the third part of our quick start SQL Server 7 guide!

Fire up that printer and get ready to roll off another dozen pages of database development info. It’s pure step-by-step food for hungry minds—and available nowhere else on the Web.

If you missed the first two instalments, grab part one here and part two here.

This week, we’re covering views, rules, defaults, user-defined types—and more!

Join us in two weeks for the final instalment, in which we’ll be checking out transactions, triggers and stored procedures.

But enough patter it’s feeding time!

Views are essentially the same as Microsoft Access queries.

You tell the database what information you want to retrieve, then give that query (‘view’) a name.

The data from this view can then be retrieved via a piece of code such as that in the ‘Accessing from VB’ section. Instead of doing something like this in Visual Basic code:

objRS.Open "Select * from tblCustomers", objConn

You simply alter the SQL Statement to the name of the view, like this:

objRS.Open "MyView", objConn

This means you’re not simply storing all your SQL statements within your application, but rather in one central depository, your database.

Also, you can grant permission for certain users to access your view, but not the underlying table. That enables you to stop unauthorised users gaining access to privileged information, such as the salary field in an employee table.

To create a View:

  • View your Database in Enterprise Manager (as before)
  • Click ‘Views’
    • The existing views are used by SQL Server to maintain your database. Do not remove them.
  • Right-click ‘Views’, then select ‘New View’
    • Depending on which version of SQL Server you are using, the screen you see may vary.
  • Type your SQL statement in the box currently containing the skeletal "SELECT FROM"
    • Alternatively, you may use the Access-like tools on the same screen to build your view
    • To test your SQL statement, hit the exclamation button on the toolbar
  • Hit ALT-F4 to close the design view window
  • Respond to any prompts to change, saving with an appropriate name

To edit a View:

  • View your Database in Enterprise Manager (as before)
  • Click ‘Views’
  • To re-enter the design view, right-click and select ‘Design View’
    • Alternatively, double-click to edit the raw SQL statement, without all the visual frills
  • Press ALT-F4 when finished, responding to any prompts

To grant View access to a user:

  • Follow all the steps for granting Table access to a user (as before), but using your view as opposed to a table

Sometimes you need to delete or perhaps rename an object, such as a view or table. This is a very simple process.

To delete an Object:

  • Locate the Object within Enterprise Manager
  • Right-click on the Object
  • Select Delete
  • Check the items to be deleted in the prompt, then hit ‘Drop All’ when ready

To rename an Object:

  • Locate the Object within Enterprise Manager
  • Single-click on the Object
  • Press F2
  • Type in a new name
  • Press Enter
  • If prompted that the rename may cause problems, decide what to do

Defaults are one of the simplest concepts within SQL Server. They’re just default values set to one side, which you can tie to particular fields.

You can add defaults whilst designing your tables. However the default method demonstrated here allows you to store the default value for multiple fields in one single location, allowing for easier maintenance.

To create a Default:

  • View your Database in Enterprise Manager (as before)
  • Right-click the grey ‘Defaults’ icon, and select ‘New Default’
  • Type in a Name for your Default
  • Specify a Default Value
    • If you’re specifying a default text value, enclose it in quotation marks
    • Dates and numbers do not require surrounding quotation marks
  • Click OK to save

To bind a Default to a Field:

  • Create your Default (as before)
    • You can’t bind a default until you’ve first saved and closed it. So ensure you save and close it, then open again at the next step
  • Open your Default by double-clicking on it in Enterprise Manager
  • To bind a Default to a field, click ‘Bind Columns’, select your table, add the individual fields, then click OK
  • To bind a Default to a particular user defined type (we’ll explain these on the next page), click ‘Bind UDTs’, then check the ‘Bind’ box for all those to be affected
    • Checking the ‘Future Only’ box means existing columns using the UDT won’t inherit the new default
  • Click OK to save

To edit a Default:

  • Open your Default, remove all dependencies of the Default, then close it by clicking OK
    • In this instance, dependencies are items that relies on this default to work, such as a bound column
    • To view dependencies, right-click on your default, select ‘All Tasks’, ‘Display Dependencies’
    • To remove dependencies, open your Default, then click on both the ‘Bind Columns’ and ‘Bind UDTs’ buttons, removing all items that rely on the default
  • Open your Default again and edit as required
  • Click OK
  • Open your Default yet again and rebind the columns as before, closing and saving when finished

User Defined Data Types are simply customised versions of the basic data types.

As an example, you might create a particular User Defined Data Type (UDT) to hold a postal code. In England, that would probably be defined as a char (string) of seven characters long that doesn’t allow nulls.

When designing your tables, you’ll find any UDTs you created among all the regulars.

The main purpose of UDTs is to allow for easier, centralised maintenance of data integrity.

After all, if the postal code system or something else slightly more volatile changes, you only have to update your one UDT, not a myriad of random tables.

Another magical advantage is that you can bind defaults (as per the last section) and rules (as per the next section) to specific UDTs.

To create a UDT:

  • View your Database in Enterprise Manager (as before)
  • Right-click the ‘User Defined Data Types’ icon, and select ‘New User Defined Data Type’
  • Type in a Name for your UDT
  • Select a data type, length (if applicable) and check for whether or not you wish to allow null values
    • None of this information can be changed after you have created the UDT
    • Also, it would appear that whether or not you check to allow null values or not, this option is ignored meaning you must implement this functionality at table level.
  • Optionally, specify a Rule and Default
    • The Rule and Default options can be altered after the UDT has been created
  • Click OK to save

To implement a UDT:

  • Start creating your table (as before)
  • On the field you want to use the UDT, select it from the list of available data types
  • Continue the table creation process as normal

Rules are wonderful little widgets that allow you to dictate what sort of information can go into a particular field. They’re what Microsoft Access calls a Validation Rule.

However in Access, you put the rules behind each individual field. In SQL Server, you create a rule, then tell the system which columns that rule applies to.

Here’s an example of a rule: @value = ‘P’

This tells SQL Server the field @value must equal ‘P’. If the rule is evaluated as True (eg, the field @value is equal to ‘P’), the field value is accepted. If the rule is evaluated as False (eg, the field @value is not ‘P’), the field value is rejected.

Here’s another example: @value >= #7000 AND @value < #25000

This allows any money amount greater than or equal to #7,000 and less than #25,000.

And one more example: @value IN (‘1389’, ‘0736’, ‘0877’)

This allows a field value of 1389, 0736 or 0877 anything else gets thrown out the window.

Note that existing table data isn’t checked by added rules, only updates and new additions.

To create a Rule:

  • View your Database in Enterprise Manager (as before)
  • Right-click the yellow ‘Rules’ icon, and select ‘New Rule’
  • Type in a Name for your Rule
  • Specify the Rule text
  • Click OK to save

To bind a Rule:

  • Create your Rule (as before)
  • Open your Rule by double-clicking on it in Enterprise Manager
  • To bind a rule to a field, click ‘Bind Columns’, select your table, add the individual fields, then click OK
  • To bind a rule to a particular user defined type (as per the last section), click ‘Bind UDTs’, then check the ‘Bind’ box for all those to be affected
    • Checking the ‘Future Only’ box means existing columns using the UDT won’t inherit the new rule
  • Click OK to save

To edit a Rule:

  • Open your Rule, remove all dependencies of the Rule, then close it by clicking OK
    • In this instance, dependencies are items that rely on this rule to work, such as a bound column
    • To view dependencies, right-click on your rule, select ‘All Tasks’, ‘Display Dependencies’
    • To remove dependencies, open your Rule, then click on both the ‘Bind Columns’ and ‘Bind UDTs’ buttons, removing all columns that rely on the rule
  • Open your Rule again and edit as required
  • Click OK
  • Open your Rule yet again and rebind the columns as before, closing and saving when finished

Check us out in a fortnight for the fourth and final part in our print-and-collect series. We’ll be covering; transactions, triggers and stored procedures, as well as even more Visual Basic ADO code.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories