Intellisense

Question

Intellisense

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Bart Read SSCommitted Points: 1628 More actions · Answer 1

Steve (6/5/2008)
Tanya, I don't recall the version of SQL Prompt I was running. It was at least a year ago. I was using the version that I "won" from SQL Server Central for xx number of posts. If Steve J. can recall when this promotion occured you would have a better idea. I say I don't really have too much patience with tools, so if they don't add value to what I'm doing, I just move on. Your "sql compare" products work great!
Now if you could sort of copy what PLSQL Developer by All Round Automations does for Oracle, then I'd totally be into the product. That product has been an awesome tool for Oracle development.
Steve

Hi Steve,

If, having used the latest released version (3.8), you're still experiencing performance problems, you may find the following post helpful:

http://www.red-gate.com/messageboard/viewtopic.php?t=7141

I'd also echo Grant's comment about spending some time tweaking the options to get it to behave the way you want. Due to the fact that our users have such wildly differing requirements, and legitimately so, I'd suggest this is a sensible first step for anyone working with SQL Prompt because there's no way the defaults will be right for everybody.

Thanks,

Bart

Bart Read SSCommitted Points: 1628 More actions · Answer 2

Grant Fritchey (6/6/2008)
That's what I mean when I say you have to learn how to work with the tool. I generally type 'SELECT * FROM' and then start building the FROM clause and after that's done, go back up & type the select list. It's a bit of a pain, but it makes the tool work for me, even though I'm sort of working for it. :hehe:

Hi Grant,

That's the way I do it as well, however, you don't have to do it that way. For example, you can qualify with the table name if you want (on SQL Server 2005+ this means you're going to need to qualify the table name as well, unless it's in your default schema). I believe the standard Intellisense in SQL Server 2008 behaves similarly. Anyway, back to the example; using the AdventureWorks database, you could enter something like:

SELECT Production.Product.

and you'd get a list of columns after the second '.'. This is rather long winded though, and if you later alias Product in the FROM clause, SQL Prompt won't automatically substitute the full name in the column list, although you may not want it to anyway.

The other thing you can do, and before I go any further I need to warn you:

DON'T DO THIS IF YOU'RE WORKING WITH A REALLY BIG DATABASE (TENS OF THOUSANDS OF TABLES, VIEWS, SPS ETC)!

Well, you can if you want, but don't say I didn't warn you.

Go into SQL Prompt > Options via the main menu bar in your editor, then click on the "Listed Candidates" tab, and go to the "Candidate Types and Filters" screen. Now check "List all columns in database after SELECT". This does exactly what it sounds like, although it will list any columns for tables you're already using in your SELECT statement first, followed by all the rest.

Let me make a couple of points about this, related to my warning above.

1) This will slow down meta-data retrieval to some degree. For small to medium sized databases--say up to a couple of thousand objects or so--this may not even be noticeable, or be so insignificant as to cause no inconvenience. For very very large databases, this can slow meta-data retrieval by 30 seconds or so, possibly more; with, say, 25,000 tables, and 25,000 views this is the kind of additional overhead I'd see on my machine (2.6GHz Intel Core 2 Duo).

2) The additional filter list used for storing all columns, which is normally empty when this option is unchecked, can often be as large or larger than the filter lists for all the other object types in your database combined. For very large databases this can lead to a noticeable increase in memory usage, possibly around 10 - 20%. Note that it won't double because the filter lists certainly don't account for all the memory used, or even the majority.

3) If a column name is used in multiple tables, which is often the case for columns involved in foreign keys, you'll see that column multiple times in the list, once for each table or view where it occurs. There are a couple of reasons for this, but one of them is that if you choose to qualify column names then SQL Prompt needs to know which table the column comes from. In many cases you probably won't want to do this so we could collapse duplicate column names, but for now we don't.

Hope that helps.

Thanks,

Bart

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I just checked SQL 2008 RC0 and the intellisense (and syntax checking) only works to a SQL 2008 server. :angry:

This was one of the features I was really, really looking forward to. Guess I carry on using SQL prompt...

Wish it had intellisense for the DMVs and system views.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 4

You are quick, Gail! I haven't even been able to download the bloody thing yet, let alone get my virtual's upgraded.

Thanks for the post. One less thing to test.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 5

Thanks Bart. Very useful information. I think I'll be staying away from the list all columns option though. I do like David Poole's tip up above. I need to remember to put it into practice. Based on Gail's report, you guys are staying well in front. Good job.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bart Read SSCommitted Points: 1628 More actions · Answer 6

Grant Fritchey (6/11/2008)
Thanks Bart. Very useful information. I think I'll be staying away from the list all columns option though. I do like David Poole's tip up above. I need to remember to put it into practice. Based on Gail's report, you guys are staying well in front. Good job.

No problem Grant, you're welcome!

Bart Read SSCommitted Points: 1628 More actions · Answer 7

GilaMonster (6/11/2008)
I just checked SQL 2008 RC0 and the intellisense (and syntax checking) only works to a SQL 2008 server. :angry:
This was one of the features I was really, really looking forward to. Guess I carry on using SQL prompt...
Wish it had intellisense for the DMVs and system views.

Hi Gail,

SQL Prompt does support this, but you need to switch it on. Open the options dialog using SQL Prompt > Options from your editor's main menu bar. Then go to "Listed Candidates" > "Candidate Types and Filters", and check "System objects" under "Candidate Types". You should now find you get auto-complete help with all system views etc, although you may possibly need to clear out your cache to force a refresh.

Thanks,

Bart

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Grant Fritchey (6/11/2008)
You are quick, Gail! I haven't even been able to download the bloody thing yet, let alone get my virtual's upgraded.

Not me actually. I'll be installing it tonight. A colleague used the office internet connection this morning and downloaded it, then installed it on his Server 2008 test server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Bart Read (6/11/2008)
SQL Prompt does support this, but you need to switch it on.

:blush:

Thanks. Somehow I've missed that every time I looked at the options....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tanya.joseph SSC Rookie Points: 28 More actions · Answer 10

Hi,

I did give RC0 a quick try yesterday and noticed that neither the new error underlining nor intellisense works for SQL Server 2005 connections from SQL Server 2008 client.

Gail - As Bart has mentioned SQL Prompt does help you with system object support once you turn them on the options menu. If you have any troubles with this, please do let us know!

Thanks,

Tanya

Project Manager

Red Gate Software Ltd

Bart Read SSCommitted Points: 1628 More actions · Answer 11

GilaMonster (6/11/2008)
Bart Read (6/11/2008)
SQL Prompt does support this, but you need to switch it on.
:blush:
Thanks. Somehow I've missed that every time I looked at the options....

You're welcome, and I wouldn't worry about missing it: I think we must have getting on for a hundred options in there nowadays, with around 60 in the EngineOptions.xml file alone. This is far more than we'd like, because it can cause confusion, but I guarantee that if we started taking them out we'd get a barrage of complaints coming through our support desk.