Queries & Views
By Richard Rost

19 days ago

Lesson 11: Creating Queries & Views in SQL Server

S M L XL FS | Slo Reg Fast 2x | Bookmark

In lesson 11, we build our first queries and views in SQL Server using the graphical query designer, noting key differences from Access in sorting, filtering, and saving queries. I demonstrate how to construct, execute, and modify queries, discuss the importance of using fields intentionally, and explain the distinction between saving queries locally and as views on the server. We walk through saving a view, connecting it in Access, and how server-side filtering improves performance by reducing network traffic. We'll discuss ORDER BY in views and how server-side processing changes how data is managed and displayed in Access.

Navigation

Prev: Lesson 10
Next: Lesson 12

Keywords

SQL Server for Access, queries and views in SQL Server, graphical query designer, SSMS query editor, sorting and filtering in SQL Server, SQL view creation, saving queries as files, linking Access to SQL Server views, TOP keyword SQL Server, avoiding SELE

Comments for Queries & Views

Age	Subject	From
15 days	Great as Always	Tim Riley
18 days	Excessive Kindness	Joe Holland

Subscribe to Queries & Views
Get notifications when this page is updated

Intro

Transcript

In lesson 11, we're going to build our first queries and views in SQL Server. You'll see how the graphical query designer works, how sorting and filtering behave a little differently from Access, and how to save that logic on the server as a view. Then we'll bring that view back into Access so you can see how the server does the heavy lifting and only sends you the data you actually need.

Let's build our first query in SSMS. I am happy now that I can actually connect across the network from my regular PC instead of having to remote desktop into the server.

Connect to your database. We have our table all set right here. Let's create a query.

Now, if you're coming from Access, which I know most of you are, a lot of this is going to feel familiar, but some of it is going to be a little bit different. Come right up here and click on New Query. This window pops up.

Now I am zoomed in a little bit. You can just hold the control key down if you have a scroll wheel and just scroll in and out. I have zoomed in because my eyesight isn't that great and also so you can see it a little better.

Just like Access, SSMS does have a graphical query editor. So we can right-click and go to Design Query in Editor. This window pops up, and the table looks just like Access. There is our CustomerT. Hit Add.

Just like Access, close this and here is our table with all the fields. Now, instead of clicking and dragging them like we usually do, we are going to just check these boxes. So let's check CustomerID, FirstName, LastName, pick something else - let's pick CustomerSince and CreditLimit.

You can see down here it's building the SQL for you as you click on these fields. Let me make this a little bit bigger so we can see everything in here. Here is the field list down here.

Now, it's backwards from Access, in that here the fields go down whereas in Access they go across, which changes our AND and our OR logic. In Access, I always teach you in the query classes, it's AND across, OR down. Here it's AND down and OR across. We will talk more about this when we get deeper into queries.

Just like in Access, we've got an alias column. If you want to call FirstName FN, it writes it down here: FirstName AS FN. We have to do that in Access a little bit differently. To alias in Access, we type in FN: FirstName. So it's a little bit different, but once you get used to the differences, it's not that hard.

Output right here is the same as the Show boxes. You can include fields in here if you want to add sorting or filtering based on those fields, but you don't want to see them in the output, you can turn them off there. That is just like the Show box in Access.

Sort Type: Here we have Ascending and Descending; the tail-end of a donkey joke still applies. Those of you who have taken my Access Beginner 1 class know what that's about. Then we have Sort Order over here, which is a little more confusing.

Now, in Access, we sort left to right. If you want something to sort first, you move it left in the query designer. Here, let's say you want to go LastName, FirstName. What you do is you set LastName ascending and set FirstName ascending, and look what happens: you get a 1 and a 2 over here now. This is where you can pick the sort order, so this will be LastName and then FirstName. You want to flip these, just change that to a 1 and that becomes a 2. So it's not the order that they appear in; it's what the sort order number says.

Filters are where conditions. If you want FirstName to be "Richard," just type in Richard and then it turns it into that. It's equals N and then in single quotes 'Richard.' We are going to have a whole separate lesson on all the differences between SQL, this stuff, between Access and SQL Server. The N just means it's an NVARCHAR. Remember that when we were doing our data types, that's what the N means. You don't technically need it, but leave it there if it creates it for you.

Here we have that, and you could put your OR going across this time. So if you want Bob, you put Bob in there, then Joe, put Joe in there, and you can see those are all OR conditions. Versus AND: let's say you want the LastName to be Rost. That becomes an AND condition. So if you want to be Richard Rost, you do it like that. See how that works? It's just like Access but backwards.

We're going to get rid of these. So this is very familiar, a little bit different, but very familiar. I am going to hit OK and that puts the SQL back here.

Now, I always like to come in here and clean this up. It puts way too many spaces and stuff in here. I like to clean this stuff up. That's just me being nerdy. Sometimes I do this to make it more visible.

Now you want to execute this. Hit the Execute button right there. Here are your results right down here.

Here is something that throws a lot of people the first time they see it - this little red X right here. It says Error, "Click to navigate the error list." Click that and then there are no errors - there's nothing down here. What's all that about? Well, if you see the red X, but your query says "Query executed successfully," then don't panic. The red X is not an SQL error from the database engine. It's coming from the SSMS editor itself, usually from IntelliSense or the query designer. It says "Query executed successfully," so we're good.

Actual SQL Server errors show up in the Messages tab and stop your query from running. If the results grid appears and you see "Executed Successfully," then the database engine is perfectly happy. Sometimes SSMS gets out of sync and throws background editor warnings that do not actually apply to your query. When you click the red X and the error list shows nothing, it's because there are no real builder syntax errors. This is one of those things that we are just not going to worry about right now.

We will talk more about this in future classes, but if you see this little thing here, just ignore it.

Here's another thing that throws beginners. This used to throw me, too. If you right-click in here, let's say you want to go back and edit this in the designer. If you right-click in here and go back to Design Query in Editor, it looks like you lost everything. But you didn't. Close this, cancel this. You have to select the text in here. Then you can right-click on it and go Design Query in Editor and you get it all back.

Why is that? That's because in here, you can actually have multiple statements. You could come down here and say SELECT CustomerID FROM CustomerT. Now you have a second SQL statement in here.

Now when you execute this, you get two query results. Look at that. That's kind of nifty. By selecting this, it's telling it this is what I want to edit in the query editor. As your queries get more complex, you can have a whole bunch of stuff up here. You can have it execute multiple things in a row. You can have multiple action queries up here. Put your delete query and your append query and all that together.

You can comment stuff out in here too. Sometimes what I will do is, if I'm playing with a variation to this, you can comment stuff out. Just select it and hit this little comment button here. It puts two hyphens in front; that's the SQL version of a comment. Then you can make adjustments to this one without losing this guy and go back and forth. We will talk a lot more about this. There's a lot more you can do with this query editor than the one in Access, even if you're using that new Matico editor, which I don't use yet because it's still got some bugs in it.

One thing that I do want to stress from playing around with this myself is if you are working with data coming across the wire and you are testing, make sure you use the TOP keyword a lot. I always put TOP 10 in here while I'm experimenting and getting the query just right. If you just say, "give me all the records," especially if you have the star in here, and especially if you're playing around with "give me all of the fields too," and you have a lot of stuff, it could take a while.

When I'm messing around with my SQL Server on my website and I forget the TOP 10, it takes a minute for all those records and all those fields to pull down. So get in the habit of putting TOP 10 in here, at least until you're happy with it. Then you can remove that. That's just my design tips and tricks.

We are going to talk a lot more about the different SQL commands in future lessons. Also, I use the star a lot when I'm looking to see, "OK, I want this field and that field," and then I'll come in here and refine it, but avoid the star, especially when you are pulling big blocks of records over the network, especially over the Internet. When you have lots of long text fields, some of the database fields on my website hold long text and there are lots of them in the table, so you want to avoid that star if you can. I always say be intentional about the fields that you retrieve. Don't just retrieve everything.

This is OK when you're designing, but later on, go through and whittle it down.

Here is the next big thing when I talk about queries. Queries in Access are saved in the database. If you go to save this right now, if I hit Control S to save this, it's going to save it as an SQL file locally. This is my C drive. So again, it's a nomenclature change. It's a name shift for you. A query is something you save locally that you can execute whenever you want to. I am going to just call this "Ricks First Query."

Now, in the future, let's say I close this. If I want to run that query again, it's not saved in the database; it's saved locally. If I want to open that guy up, I can go to File and then Recent Files - there it is, or you can go to Open then File and open up Ricks Query, and there it is and it will load back up. Then you can play with that again, but it's a local file saved on your drive that you can use to query the server.

If you want to save this on the server, that's what a view is. We save it as a view.

Select your SQL - it's the easiest way to do it. Copy it. Come over here to Views. Right-click, New View.

Now here's the Add Table again. If you want to build the view from scratch here using the graphical designer, just like we did with the query, you can do that, too. I'm going to hit Close because I've already created the query. I am just going to paste the SQL in there, then just click off of that, and look at that - it copies it in.

It made some changes - obviously it replaced the star with all the fields, but that's OK. We can edit that. Then you can right-click and go to Execute SQL and see your results down here. That's pretty cool.

Now, here is the coolest thing: we can save this now. Hit Save. Let's call this CustomerQ. I know it's a view and I should be using V, but I don't. My Access habits die hard, so to me, I store them as Q on the server because when I come back into Access and I want to work with queries, I remember that this is not a table, it's a view. So I use Qs for my views. Q view, it rhymes. I've been doing it for decades, so you are doing it too. If you want to use V, use V. Hit OK.

You might see this warning pop up because I have a TOP 10 here and I also have an ORDER BY clause. We're going to spend a whole lesson on this later on. This is very important. We're not going to dive deep into it just yet, but SQL Server is telling you that just because you put ORDER BY in the view definition, it doesn't guarantee that the results will come out sorted when you view that query later.

In other words, the view itself doesn't promise order. TOP works together with ORDER BY to determine which rows are selected, but once the view is saved, SQL Server doesn't guarantee the final output order unless you also use ORDER BY in the outer query that selects from this view.

The key idea for now is if you care about the order of your results, always use ORDER BY in the final SELECT statement. Again, we are going to dedicate a full lesson to this later. For now, just understand that this warning is legitimate. Ordering inside a view is not something you should rely on for presentation, for example.

We are generally going to use these views for selecting the records that we want, and then in Access, we are going to build a query off of that, and that's where we will do our ordering. So just keep that in mind. We are almost never going to use ORDER BY in a view. I almost never do.

Hit OK. It's going to do its thing. Now we have a CustomerQ view.

Let's make this query a little more meaningful. Let's get rid of the ORDER BY since that's not really going to be important in here. Let's make it so we just have the fields we want. Let's do CustomerID, FirstName, LastName. Let's get rid of Email, Notes, FamilySize. Let's keep the CreditLimit and IsActive.

Let's see what we have now. Right-click, Execute. Let's get rid of the TOP 10 now since I'm happy with the results. Let's see what this yields. Execute again.

Let's say I want only people with credit limits of $1,000 or more. Find CreditLimit, and we're going to put in the filter here: greater than or equal to 1,000. This little icon here just means the query has changed, so the data that you're looking at isn't up to date. Right-click, Execute or Control R.

We have 25 records now.

Now, here's the beautiful thing about views. Save the view, Control S, it's saved. Now we can query that view, CustomerQ. So now I can say SELECT * FROM CustomerQ instead of CustomerT.

We will talk about this squiggly stuff in just a minute. Execute. There we go. We have just queried the view. This is where we would do the ORDER BY in the final query. This could be in Access. In fact, let's do that. Let's head over to Access now.

Back in Access, we still have our customer table here. Now, we can attach to views on the server, and they'll get treated like tables over here. That's another reason why I make sure that their names end in Q, so I know that it's a view and not the table. Most of the time, but not always, my views are not editable. That's a big deal.

Go to External Data, New Data Source, From Database, SQL Server, Link, and hit OK. We have our Kirk DSN all set up and ready to go. Hit OK. There's our CustomerQ. Hit OK. What fields do you want from CustomerQ? Pick them all. Hit OK. There's our CustomerQ. There it is, and this just happens to be editable, so we're good.

A lot of times, my views are really complex, and so there comes a point where it is just like in Access - if your query is too complex, you cannot edit it.

Here's the beautiful thing, and this is the big paradigm shift that you have to get used to when working with Access and SQL Server. In Access, we are used to pulling all the data down to the local machine and then filtering it there. The tables live in the back end. Access grabs a giant chunk of records and your PC does the work of sorting, filtering, finding what you need. This works fine for small to medium databases, but it doesn't scale well.

Even me, in my little office here, just myself, my customer table has got 50,000 - 60,000 records in it. Each of those customers might have dozens or, hopefully, more orders. If I want to do a big report like that, it is pulling all that data across the network wire, which is fine in my office, but I would never try that big of a data set over an internet connection. It would take hours.

With SQL Server, the work moves to the server. If you create a view on the server that shows only active customers, or only customers from Florida, or only customers with a credit limit over $1,000, that filtering happens before the data ever leaves the server. When you click that link in Access, when you open this view in Access, Access only receives that tiny finished result set. The server only returns the 50 records out of 150,000, and then Access displays those. That's far less network traffic, far faster forms, far faster reports, far less work for the local machine.

As we move forward, this becomes even more important. When you open a form that is bound to SQL Server data, the server can just send the specific record or records you ask for, sometimes even just one record at a time. Your PC is no longer churning through massive data sets just to display a single customer or just that customer's orders. The server sends you just what you need.

So, start thinking of SQL Server as the engine that does the heavy lifting. You design views, your filters, all the logic, that's going to be on the server. Access becomes the front end that simply displays the results.

This shift in thinking is fundamental to building fast, scalable, professional database applications. This is my Captain Kirk speech at the end of the episode: that's why we're aboard her. So, this is important. If you get nothing else out of this entire beginner level 1 course, this is it. This is the mindset you have to start adopting. Later on, we're going to learn how to make dynamic pass-through queries, where you can create a query on the fly that says, "give me just this information." It sends that query up to the server. There is all kinds of cool stuff you can do.

While I'm looking at it, I want to talk about these squiggly lines. This IntelliSense "Invalid object name CustomerQ." What are you talking about? It's right there. I'm looking right at it. IntelliSense can be helpful, but it's annoying. If you hit Control Shift R, it refreshes sometimes. There, it's still not going away. Oh, there it goes. It takes a second sometimes.

When you make changes in here, it's looking at the back end to see what's going on. Remember, the SSMS interface is different from the actual database engine. Sometimes it takes a second to update. Honestly, half the time I disable IntelliSense right there and it does not bother you with those squiggles. If you like it, leave it on. I am going to leave it on for now. We might disable it later on, but for now, it's OK. Just don't panic if you see that there. It's constantly trying to tell you what's in the back end.

Then you get these little pop-ups here. Just like Access, the VBA editor has IntelliSense in it, too. Just like I mentioned in a previous video, you don't necessarily need the dbo in here. It's assumed. It's kind of like you don't need "Me." everywhere in Access VBA. Also, it's not case sensitive. You could put LastName in there and that's just fine. It'll work, and these aren't case sensitive either. I try to keep everything in the right cases, but you never know what sneaks in.

Now that I'm done with this query, I am going to save that query and then close this. Remember, that query is saved locally on my hard drive. That's a difference between queries and views.

I am going to warn you now: you are going to hear me throughout the course accidentally call views "queries" and vice versa. I almost never use real queries - the file, open, run, this thing. I almost never use saved queries on my hard drive. So if I say "query," I most likely mean "view." It's just my Microsoft Access brain kicking through. You will know what I mean; I'm just warning you now.

Do not go into the System Views. You don't need any of this stuff for now; that's all more advanced stuff.

Today, you learned how to build queries in SQL Server, turn them into views, and let the server handle the heavy lifting before the data ever gets to Access.

Post a comment down below. Let me know how you liked today's video and what you'd like to see in future lessons. That's going to do it for lesson 11, folks. Hope you learned something. Live long and prosper, my friends. I'll see you in lesson 12.

Quiz

Q1. What is the primary difference between how sorting works in Access and SQL Server's graphical query editor?
A. In Access, fields are sorted top to bottom; in SQL Server, they are sorted left to right.
B. In Access, you use numbers for sort order; in SQL Server, you move fields to the left.
C. In Access, you sort left to right by field position; in SQL Server, you set a sort order number.
D. Sorting is identical in both systems.

Q2. How does the logic differ for AND and OR conditions between Access and SQL Server's query designer?
A. Both use AND across and OR down.
B. Access uses AND across, OR down; SQL Server uses AND down, OR across.
C. Access and SQL Server both use OR across and AND down.
D. Access uses OR across, AND down; SQL Server uses AND across, OR down.

Q3. What does the 'N' in 'N'Richard'' mean when creating a filter in SQL Server?
A. It means the value is numeric.
B. It signifies a null value.
C. It indicates the value is an NVARCHAR (Unicode string).
D. It means the value is not equal.

Q4. When saving a query in SQL Server Management Studio (SSMS), what happens by default?
A. The query is saved directly in the database as a stored procedure.
B. The query is saved as a local .sql file on your machine.
C. The query is automatically converted to a view.
D. The query is overwritten in Access.

Q5. What is required to save your query logic so it stays on the server and can be used later by others or by Access?
A. Save the query as a CSV file.
B. Create a view in SQL Server.
C. Export the query to Excel.
D. Use the "Save As Table" option.

Q6. Why is it recommended to use the TOP keyword while experimenting with queries in SQL Server?
A. To ensure all fields are included in the output.
B. To retrieve only a few records and avoid pulling large data sets unnecessarily.
C. Because SQL Server does not support retrieving all records.
D. To enforce alphabetical sorting of records.

Q7. If you want to edit an existing query in the SQL Server graphical designer and have written multiple SQL statements in the editor, what must you do before choosing "Design Query in Editor"?
A. Edit the table directly.
B. Select the text of the SQL statement you want to edit.
C. Copy all statements to a new file.
D. Close and reopen SSMS.

Q8. In SQL Server, what happens if you include ORDER BY inside a view definition?
A. The results will always be sorted as specified.
B. The ORDER BY is ignored and never affects the data.
C. The view may use ORDER BY only to determine which rows show up with TOP, but does not guarantee output order when the view is used.
D. SQL Server will reject the view with an error.

Q9. What advantage does using views in SQL Server provide when linking them to Microsoft Access?
A. Access will process all data and then apply filters locally.
B. The server processes the filtering and only returns the needed data to Access, reducing network traffic.
C. The views allow Access to update server settings remotely.
D. There are no real benefits; it's the same as linking tables.

Q10. What happens if a view or query in SQL Server becomes too complex when you try to edit data in Access?
A. Access will always allow edits, regardless of complexity.
B. The view will not be accessible.
C. Sometimes views are not editable due to complexity, similar to complex queries in Access.
D. The view will convert to a table automatically.

Q11. When pulling large sets of data over the network or internet, what is discouraged in SQL statement design?
A. Selecting only the fields you need.
B. Using WHERE clauses.
C. Using SELECT * to retrieve all columns.
D. Using parameterized queries.

Q12. What is IntelliSense in SQL Server Management Studio?
A. A tool to check spelling.
B. A feature that auto-suggests and checks SQL code for errors and object names.
C. A way to backup databases automatically.
D. A feature that sorts records.

Q13. If you see a red X in SSMS after executing your query, but the Messages tab says "Query executed successfully," what should you do?
A. Fix the error immediately before proceeding.
B. Ignore the red X if the query runs and the error list is empty.
C. Restart SSMS.
D. Contact technical support.

Q14. When you access a SQL Server view from Access, how does it generally appear?
A. As a table.
B. As a report.
C. As a macro.
D. As a form.

Q15. What is the key paradigm shift for Access users when moving to SQL Server and using views?
A. Always process all data locally on your PC.
B. Rely on Access to perform all calculations.
C. Move the logic and data processing to the server; Access just displays the finished result set.
D. Never use views for filtering.

Answers: 1-C; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-C; 9-B; 10-C; 11-C; 12-B; 13-B; 14-A; 15-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on building your first queries and views in SQL Server, understanding how the graphical query designer operates, and how behaviors like sorting and filtering differ from what you might be used to in Access. I also show you how to save a query's logic as a view on the server, and then pull that view back into Access so the server handles the intensive processing, sending you only the data you really need.

To get started, you want to connect to your database in SQL Server Management Studio (SSMS). By this point, you should be able to connect across your network from your PC instead of having to remote desktop into your server, which really saves time and hassle.

If you're familiar with Access, much of the process in SSMS is going to seem familiar, but there are some differences. To build a new query, open a New Query window. If you find the text too small, you can zoom in with the control key and your mouse scroll wheel for better readability.

SSMS provides a graphical query editor much like Access. You can open this by choosing to design the query in the editor. The interface resembles Access: you add your table and select fields, but instead of dragging and dropping field names, you simply check the boxes for the fields you want.

One key difference you'll notice is the orientation of fields and criteria. In Access, fields are listed left-to-right with AND logic across a row and OR logic down a column. In SSMS, fields are listed top-to-bottom, and the meaning of AND and OR is reversed - here, AND runs down a column, whereas OR is across a row. This takes some getting used to if you come from Access.

Field aliasing is also a bit different. In SSMS, you can give a field an alias using the AS keyword (for example, FirstName AS FN), whereas in Access you use a colon (FN: FirstName). Both have similar functionality; it's just a matter of syntax.

Sorting works in a way reminiscent of Access, but the implementation is different. In Access, the left-most field is sorted first. In SSMS, you choose the sort order using sort order numbers next to each sorted field. So, if you want to sort by LastName first and then FirstName, you assign those fields the sort order numbers 1 and 2. Adjusting these numbers changes the sort priority, rather than moving the field positions in the grid.

Filtering works as you'd expect - enter a value in the criteria area to apply WHERE conditions. For character fields, SSMS uses 'N' to indicate NVARCHAR data, which you'll see before string literals, but it is not strictly necessary. Remember that OR conditions run across the row, while AND conditions run down a column. If you filter for multiple first names like "Bob" or "Joe", these go across. To find a particular full name, you would specify both first and last name, one per column.

Once you're satisfied with your selections and filters, you can execute the query and check the results. You might see a red X or "error" warning - don't panic. If your query reports that it executed successfully and you see results, the error is likely just a notification from SSMS's IntelliSense or the editor and doesn't impact your actual query.

A feature unique to SSMS is that your editing window can contain multiple queries. If you enter more than one SELECT statement, executing them both will yield multiple result sets below your script. To edit a particular query using the graphical editor, make sure to select only that part of the text before reopening the query designer.

SSMS also supports comments using two hyphens at the start of a line. This makes experimenting with variations much easier without losing previous attempts.

A word of caution when dealing with networked data: try not to return every record while you're still testing your queries, especially if you have large tables. Using the TOP keyword limits the number of rows retrieved (e.g., TOP 10), making development much quicker and preventing accidental performance problems, especially over slow network connections.

Another important distinction: in SSMS, when you save a query, you're saving it as a file to your computer, not into the database itself. You can later open these files to rerun or edit them. Since they aren't stored on the server, they're tied to your local drive.

If you want to persist a query's logic in the database itself, you save it as a view. To do this, copy your query's SQL, go to the Views section, create a new view, and paste your SQL in. SSMS might automatically expand your SELECT * into individual fields. At any time, you can execute the view's SQL to preview results.

Naming conventions are important so you can distinguish between tables and views later in Access. Personally, I name my views with a Q at the end (like CustomerQ), because old Access habits are hard to break. You might see a warning when saving a view that both uses TOP and ORDER BY. SQL Server wants to make sure you understand that ORDER BY in a view's definition does not guarantee sort order on retrieval, unless it's paired with TOP or you sort the results when accessing the view. For presentation or reporting, you'll want to handle sorting in the final query in Access, not in the view's definition.

You can fine-tune your view by removing unnecessary fields or restrictions. For instance, you can filter customers with a credit limit of 1,000 or more by placing that condition in the view. After saving, you can then select from this view using a simple SELECT * FROM CustomerQ statement.

When you switch back to Access, you can link to your SQL Server views as if they were tables. This is another reason to use a clear naming convention. Note that while simple views are often editable, more complex ones will be read-only, similar to how complex queries behave in Access.

The critical paradigm shift here is that, with Access connected to SQL Server, you're no longer pulling down massive data sets to process locally. Instead, filters and processing happen on the server, and only the specific results you need are sent across the network. This is essential for building scalable, professional databases, especially when dealing with large recordsets or slow connections.

As we continue with future lessons, we'll explore dynamic pass-through queries and other advanced techniques, but for now, keep in mind that you want the server handling search, filter, and sort logic whenever possible.

Occasionally, SSMS will show squiggly underlines or warnings from IntelliSense, such as "Invalid object name," even when the object clearly exists. This is just a quirk of SSMS as it tries to keep in sync with your back end database. Sometimes a quick refresh (such as Control Shift R) clears it up, but don't be concerned as long as your queries run successfully.

In closing, remember that while you'll hear me occasionally mix up the terms "query" and "view," it's just old habits from working extensively with Access. What matters most is understanding that in SQL Server, the real processing takes place on the server before Access ever sees the data.

Today you saw how to create and edit queries in SQL Server, save them as views, and link those views into Access, ensuring your server handles the heavy lifting. This is fundamental for fast and scalable applications.

If you're eager for more step-by-step detail on everything covered here, you can find a complete video tutorial on my website at the link below.

Live long and prosper, my friends.

Topic List

Building a basic query in SQL Server Management Studio (SSMS)

Using the graphical query designer in SSMS

Selecting fields for a query using the field checkbox

Understanding AND and OR logic differences between Access and SSMS

Creating field aliases in SSMS queries

Configuring sorting and sort order in the SSMS query designer

Applying filters with WHERE conditions in SSMS queries

Commenting out lines in SQL queries in SSMS

Executing queries and interpreting query results in SSMS

Handling and understanding IntelliSense and the SSMS editor's red X warning

Editing existing queries using the graphical designer

Running multiple SQL statements in one SSMS query window

Saving queries as .sql files locally versus views on the server

Creating and saving views in SQL Server from existing queries

Understanding the use of TOP and ORDER BY in views

Syncing query result changes by executing the modified view

Filtering records in views using WHERE conditions

Linking SQL Server views to Microsoft Access

Differences between editable and non-editable Access-linked views

Performance benefits of server-side filtering with views

Viewing and refreshing server-side changes in SSMS with IntelliSense

Distinguishing between queries saved locally and server views

Best practices for retrieving only the necessary fields from SQL Server

Avoiding SELECT * for large datasets, especially over a network

Article

In this tutorial, we will explore how to build your first queries and views in SQL Server using SQL Server Management Studio (SSMS), and how this workflow differs from Microsoft Access. You will see how the graphical query designer in SSMS operates, how sorting and filtering behaviors change compared to Access, and how to save your query logic to the server as a view. Finally, we will link that view back into Access, allowing the server to process your data for faster, more efficient results.

Start by connecting to your database in SSMS. Once connected, ensure that your tables, such as CustomerT, are ready for querying. To create a new query, click on New Query. If you want to make the editor easier to read, you can zoom in by holding down the control key and using your mouse's scroll wheel.

SSMS offers a graphical query editor like Access. To use it, right-click in your query window and select Design Query in Editor. When the window appears, select your table, such as CustomerT, and click Add. Close the add table dialog. Now, you will see all your table fields listed. Instead of dragging fields as in Access, you check the boxes beside the fields you wish to include - for example, CustomerID, FirstName, LastName, CustomerSince, and CreditLimit. As you select fields, the SQL code is created for you in the lower window.

One key difference to note is how SSMS and Access set up the query grid. In Access, fields go left to right; in SSMS, fields go top to bottom. This change flips your logic for AND and OR conditions. In Access, AND goes across, OR goes down. In SSMS, it is AND down and OR across.

To alias a field - for example, to show FirstName as FN - you can edit it in the Alias column, which writes the SQL as FirstName AS FN. In Access, the syntax is FN: FirstName. SSMS handles this for you, but the location and style are a bit different.

The Output column behaves like the Show checkboxes in Access. You can deselect Output for a field if you want to use it for filtering or sorting but not display it in your results.

Sorting works differently as well. In Access, the sort order depends on where your field is in the grid (leftmost gets sorted first). In SSMS, you assign a Sort Order number to each field in the grid. For example, if you select LastName for sorting and give it order 1, then FirstName as order 2, your results will be sorted first by LastName and then by FirstName. The order is determined by these numbers, not by the position of the columns.

Filtering in SSMS uses the Criteria column. For instance, if you want to filter FirstName to show only "Richard," type Richard in the Criteria. SSMS will generate WHERE [FirstName] = N'Richard'. The N prefix designates an NVARCHAR data type. You do not have to worry about this, but leave it as is if SSMS creates it. For multiple OR conditions, enter values across the grid; for multiple AND conditions, enter them down the grid. For example, placing "Richard" under FirstName and "Rost" under LastName will filter for rows where FirstName is "Richard" AND LastName is "Rost." If you want "Richard," "Bob," or "Joe," put those names across the grid under FirstName; this creates OR conditions.

After designing your query, click OK to return to the main query window, where your SQL is displayed. You might notice excess spaces or formatting; you can tidy this up manually for clarity.

To execute your query, click the Execute button. Your results will display below. Occasionally, you may see a red X that indicates an error, but if the message says "Query executed successfully" and your results appear, there is no actual problem. Often, this X is an SSMS warning from IntelliSense, not a real error from SQL Server. Real errors will stop your query and show up in the Messages tab.

If you want to go back into the graphical editor, highlight the specific SQL statement, right-click, and choose Design Query in Editor. If nothing is selected and you have multiple queries in your window, the designer may not know which one to open. SQL Server allows you to have multiple SELECT statements in one editor. Each execution runs all statements, giving multiple sets of results.

You can comment out lines of SQL with two hyphens at the start of the line. Use the Comment button or type -- before the line. This is helpful while testing different variations of your query.

When practicing or creating queries, use the TOP keyword to limit results, such as SELECT TOP 10 * FROM CustomerT. This way, you avoid accidentally pulling large amounts of data across the network, which can be slow or resource intensive. Remove the TOP restriction once your query is finalized.

It is common at first to use SELECT * to retrieve all fields during design, but as your query matures, specify only the fields you need. This keeps data sent over the network minimal and makes your application faster and more efficient.

One important difference from Access is how queries are saved. In SSMS, if you save your query with Control S, you are saving a local .sql file on your own computer. This is not part of the database; it is simply a script you can reload and execute later. It does not live on the server.

If you want to save your query logic to the server, you need to create a "view". To do this, copy the SQL of your query, right-click Views in SSMS Object Explorer, and select New View. When prompted to add tables, click Close if you already have your SQL ready. Paste your query into the designer window and click away to confirm it. SSMS may convert a SELECT * into a list of named fields, which is fine. To preview results, right-click and select Execute SQL.

To save your view, press Control S. Name your view, such as CustomerQ. Many developers use Q to mean "query" (even though the object is a view), making it easier to tell views from tables when linking back into Access. You might get a warning if your query uses both TOP and ORDER BY. SQL Server warns that ORDER BY in views does not guarantee the sort order when you read from the view later, unless you use TOP in tandem. Normally, you should not rely on ORDER BY inside a view for display order. Sorting should be applied when selecting from the view.

Once your view is saved, you can right-click and Execute to see your filtered and selected results. For example, you might filter the CreditLimit column to show only customers with limits of $1,000 or more by entering >= 1000 in the filter box. Save the view again after making changes.

To use the view, simply write a new query such as SELECT * FROM CustomerQ and execute it. Apply additional ORDER BY clauses here to get your results in the right order.

Now, let us bring this view into Access. In Access, go to the External Data tab, select New Data Source, then From Database, SQL Server, and click Link. Choose your Data Source Name (DSN) and connect. Access will list your tables and views; choose CustomerQ. After linking, CustomerQ appears as a table in Access. It functions just like a table and, depending on the view's complexity, may or may not be editable.

The real power of this workflow comes from the fact that the view is executed on the SQL Server, not inside Access. In the classic Access model, when you filter or sort, Access pulls down large blocks of data from the server and processes them locally. This works for small to medium databases but quickly becomes inefficient as the amount of data grows - especially over a network or the internet.

With SQL Server, your logic in views does the work before any data crosses the network. For example, a view filtering customers with a CreditLimit above $1,000 sends only qualifying records to Access, saving you bandwidth and time. Reports and forms load faster because your PC receives only the finished, filtered result set. As you build more forms and reports in Access that point to views on SQL Server, most of the heavy processing is performed by the server, not your workstation. This approach is critical for building scalable, professional databases, especially as your data set grows.

An important note about SSMS: sometimes, you will see red underlines or squiggles beneath object names, like CustomerQ, even though those objects exist and your queries run fine. This is often an IntelliSense sync issue. You can refresh IntelliSense with Control Shift R. If it persists, you can disable IntelliSense to avoid these distractions. Case does not matter in SQL keywords or object names, so SELECT * FROM customerq is just as valid as using CustomerQ with the exact correct case.

Remember, when you save a query in SSMS, it stays as a local file on your machine, not on the server, while views are part of the database itself. Throughout your database work, you may hear the terms "query" and "view" used interchangeably - especially by people coming from Access. Just remember, in SQL Server, views are the server-resident saved queries, while local .sql files are just scripts on your computer.

As a beginner, focus on understanding this division of labor. Let SQL Server do as much filtering and processing as possible through views, sending only the data you need to Access on the front end. The result is a faster, more efficient, and robust application that scales well as your database grows.

This is the foundation for professional Access plus SQL Server application design. As you continue, you will learn even more advanced techniques, such as dynamic pass-through queries, that exploit the power of the server even further. For now, practice designing your queries, saving them as server-side views, and linking them to Access. Let the server do the heavy lifting and enjoy a smoother, faster Access experience.