Easily Access Remote Data Using SQL Server Views

ount yourself lucky if the data you need is always in the database you happen to be using. Most of us must retrieve data from all kinds of sources, and sometimes it isn't easy. The good news for SQL Server users is you can access remote data from another SQL Server database with a simple view. You don't need a convoluted stored procedure, user-defined function, or object library; a quick and easy view can get the job done if you know the right syntax.

Views, which have been around since SQL Server 2000, provide an easy and efficient solution for accessing data. You probably use them already to access data in the current database. Views allow you to do the following, which makes them a better solution than accessing the table itself:

• Limit who sees the data
• Determine which data the user sees
• Limit who can manipulate the data

Views are like tables, except that they don't store data. Instead, a view stores the instructions the server needs to retrieve data. The server executes the instructions to return the view data, but doesn't store the data itself. In addition, because views are evaluated on the server, you can limit the amount of data dragged across the network—always a good practice. (If you're moving up from a Microsoft Access database, a view is similar to a query.)

Views do have a few restrictions you'll need to consider when determining whether they are the right solution for a given task:

• A view can't include a COMPUTE or COMPUTE BY clause.
• A view can't include an ORDER BY clause unless there's a TOP clause in the SELECT list.
• A view can't include the INTO keyword.
• A view can't reference a temporary table or a table variable.

The good news is that even with these restrictions, views are still a viable tool most of the time.

Next Page