Management Studio behavior question 1

[Andrzejek]

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?

---- Andy

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

 

[MarkSweetland]

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

 

[Andrzejek]

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

 

[MarkSweetland]

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