Intellisense in SSMS 2008 Lists the Objects Even Before Creating them – Pretty Intelligent

As most of you us know that Intellisense feature was introduced as part of SQL Server Management Studio (SSMS) which shipped with SQL Server 2008. There after it is getting better in subsequent releases with a whole lot of new features in Denali SSMS.
Basically Intellisense assists the user while writing queries by providing necessary suggestions on the Object Names, Object Properties (like column names etc.) etc. Usually people say that SSMS provides suggestions for those objects which exist on the database/server. However, there is an interesting fact about SSMS which many people do not know and that is, Intellisense provides suggestions even on those objects for which the DDL has just been written (not executed yet!) in the same window in which it is being accessed.
Let us take a look a simple example.
1. Go to SSMS and Open a New Query Window
2. Type in the following statements – one to change the database context to tempdb and the next one to create a new table SampleTable:
USE tempdb
GO

CREATE TABLE [dbo].[SampleTable] (
ID INT IDENTITY(1,1),
Value1 NVARCHAR(255),
Value2 NVARCHAR(255)
)
GO
3. Do not execute any of the above two statements.
4. Now start typing the below statement after the above two statements:

INSERT INTO dbo.S

As soon as you type in INSERT INTO dbo., it will list the SampleTable in the list of suggestions even though we have just written the script for SampleTable creation but have not created it yet. Below screenshot shows this in action.

Here is another instance of using a SEELECT * FROM query on the SampleTable before creating it.

Now write the INSERT INTO Statement or SELECT * FROM Statement completly to make it a valid SQL Statement and try to execute the completed statement without executing the CREATE TABLE Statement and you will see that you will get an error like the one below which shows that the object does not exist.

Sounds Simple but is Really Interesting πŸ™‚

If you guys have come across any such interesting things, do share it by leaving a comment below.
Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on August 6, 2011, in SQL Server, SSMS. Bookmark the permalink. Leave a comment.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s