Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Management Studio behavior question 1

Status
Not open for further replies.

Andrzejek

Programmer
Jan 10, 2006
8,488
5
38
US
I have this simple query made in SQL Server Management Studio, in Views - New View...

[pre]SELECT CountyID, CountyName
FROM dbo.Counties[/pre]

But as soon as I add: [tt]ORDER BY CountyID[/tt] at the end, the Management Studio adds

[pre]SELECT [red]TOP (100) PERCENT[/red] CountyID, CountyName
FROM dbo.Counties
ORDER BY CountyID[/pre]

And I cannot get rid of TOP (100) PERCENT, unless I delete ORDER BY CountyID
I can change it to TOP (10) and get rid of PERCENT, but as soon as I delete all of that, Studio adds it anyway.

Why? [ponder]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 


ORDER BY should be used in the most outer query for present result in desired order, not within the view itself. There is a good chance that the TOP/ORDER BY will not return the data in the order specified from the view result anyway.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach
 
I see you point, Mark.
I just started using SSMS and tried 'New View...' as a query builder to create a query for my coding. It does have a nice GUI, shows me all tables, connections between them, etc.

Is there other place in SSMS as a 'query builder'?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 

I don't use the view builder, or the table designer. I'm more apt to create and test the query I want and then preface it with CREATE VIEW dbo.[view_name] AS to actually create the view. Same for tables. I'd rather create and name the objects like I want via script rather than have to go back and rename the names for foreign keys, indexes, etc.



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top