SQL vs Access Query Overview (1 Viewer)

ECEK

Registered User.
Local time
Today, 15:06
Joined
Dec 19, 2012
Messages
717
I am just learning to query my data in an SQL Database.

I am totally familiar with the graphic interface of Access to create queries but need your advice.

I can end up creating several queries within queries to get my result.

How do I look to do this in SQL?
Do I build it in one complex query (Not dissimilar to VBA style or do I build views and then reference these (as one would do in Access).

Your direction is much appreciated
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:06
Joined
Jan 20, 2009
Messages
12,849
Do I build it in one complex query (Not dissimilar to VBA style or do I build views and then reference these (as one would do in Access).

Put simply "Yes".;)

What you use depends on the context. A View may be good where there are a number of queries built on the same foundation. Provided they are not too complex they work pretty much like tables.

TSQL is a full programming language that supports multiple commands within one query. You have to use the SQL editor to do this because the designer doesn't support this functionality.

Don't be put off writing SQL just because you might have struggled with the SQL editor in Access. It always was hopeless. The Editor in the Management Studio is very powerful with Intellisense and on-the-fly syntax checking. It is much easier to build far more complex queries including subqueries.

The joins are much simpler to write than in Access without the need for nesting joins. The joins are far more diverse than in Access, with Full Outer Join, Cross Apply, Outer Apply as well as the familiar ones.

The learning curve to get into SQL Server through Management Studio is not especially steep but "the mountain is high" if you get my drift. You can get working with it fairly quickly but keep finding new stuff for years. I've always found a capability to do anything I have been able to imagine wanting to do (and I have a good imagination).

Learn about Stored Procedures early in your exploration. They are one of the most useful features.

Start your query coding using the Select TOP 1000 records on the right click of a table. This will load the SELECT for its columns and the FROM clause which you can start editing from.

Save time entering column names for other tables by Dragging the Column heading of the table from the Object explorer tree view into the query editor. This will paste a list of the columns into the SQL text.

The key to building complex queries is the formatting. Use new lines for the key words and indent the subordinate sections. Vertically align the open and close parentheses of the subsections.

Highlight a section of code in the editor and press run to get the results from that section. This is handy for testing.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:06
Joined
Aug 30, 2003
Messages
36,118
I love this:

The learning curve to get into SQL Server through Management Studio is not especially steep but "the mountain is high"

I'll add that temp tables in a stored procedure can do your nesting, as can table valued functions.
 

AlexDoran

Registered User.
Local time
Today, 15:06
Joined
Feb 25, 2014
Messages
20
Im soon to be climbing that mountain and am keen to learn about SQL, is there any good recommended material / tutorials?
 

Minty

AWF VIP
Local time
Today, 15:06
Joined
Jul 26, 2013
Messages
10,354
If you are used to the Query builder in Access, use the New View as a starting point to just building queries and seeing the SQL used.
I frequently use the view editor, as you can see the criteria, results, pictorial joins and SQL all in one place. I use it to build the basics then simply paste the sql into the editor and refine it for further use, either in a stored procedure or function.

It's actually what the Access Query designer should be.

As already started the join types and general flexibility are far greater in SSMS.
 

Bullschmidt

Freelance DB Developer
Local time
Today, 10:06
Joined
May 9, 2019
Messages
40
Im soon to be climbing that mountain and am keen to learn about SQL, is there any good recommended material / tutorials?
Here's one I like:
SQL Tutorial
w3schools.com/sql/
 
Last edited:

Users who are viewing this thread

Top Bottom