Want to know about "Select Top 1000 Rows"

[Ashfaque]

Hi,

Since SQL Server commands are not that much familier I am with, I like to know about below to menu when I right click on my sql server tbl.

Select Top 1000 Rows
Edit Top 200 rows

In second menu (Edit Top 200 rows) I can make changes in records as requires but records are more that 200 so when I am using Select Top 1000 rows menu, it showing all records but also showing the SQL Query and all records are Non-updatable.

I need to update all records that displays. How can I ?

Also I dont want to see the query code over the actual records..I mean I want to close the query window and just want all records to display

Thanks,

 

[Minty]

SSMS is not supposed to be a data interaction tool and is not good at it, it's a database management tool. (The clue is in the name...)

If you need to update records you either need to do it with a query, or a separate front end application, maybe like erm... Access.

You can (if push comes to shove) edit the underlying query that brings up the top 200 rows, but I can assure you editing data in SSMS is not a pleasant experience. To do this press the SQL or Criteria button on the menu bar:

But remeber, it's not what SSMS is designed to do.

 

[GPGeorge]

Hi,

Since SQL Server commands are not that much familier I am with, I like to know about below to menu when I right click on my sql server tbl.

Select Top 1000 Rows
Edit Top 200 rows

In second menu (Edit Top 200 rows) I can make changes in records as requires but records are more that 200 so when I am using Select Top 1000 rows menu, it showing all records but also showing the SQL Query and all records are Non-updatable.

I need to update all records that displays. How can I ?

Also I dont want to see the query code over the actual records..I mean I want to close the query window and just want all records to display

Thanks,

While Minty's comments are true, they don't tell the full story. Once the query results display for either of those choices, you CAN open the SQL and remove the Top xxxxx keyword to display all of the records, either for straight select or for edit. I wouldn't normally recommend that, for the reasons Minty lists, but it can be done.

Keep in mind that SQL Server tables often contain many thousands, hundreds of thousands, or even more, records. It's impractical to show them all or <shudder> attempt to edit them one record at a time.

Also, there is an option in SSMS to change the number of records in those two choices, which are just the defaults. Again, depending on the size of the recordset in the table, that may or may not be practical.

 

[WayneRyan]

Tools --> options

This is a settable option, near the bottom.
Change the 200/1000 to 0 and you get all.

They set those so you don't clobber the network by selecting all from LARGE tables ...

They want to protect you

When you drag names the "[]" are also optional in newer versions.

Hth,
Wayne

 

[GPGeorge]

Tools --> options

This is a settable option, near the bottom.
Change the 200/1000 to 0 and you get all.

They set those so you don't clobber the network by selecting all from LARGE tables ...

They want to protect you

When you drag names the "[]" are also optional in newer versions.

Hth,
Wayne

*Large tables, meaning potentially millions of records all at once.....

Trust me, you don't want that, you REALLY don't want that.

 

[Minty]

I often forget (in an Access FE I frequently work in) that one of the tables has 11 Million + rows.

I often open it directly in Access, without thinking, to look at something and then pause whilst it loads, or worse still I right-click to filter for a value... Not a good idea

 

[GPGeorge]

I often forget (in an Access FE I frequently work in) that one of the tables has 11 Million + rows.

I often open it directly in Access, without thinking, to look at something and then pause whilst it loads, or worse still I right-click to filter for a value... Not a good idea

My favorite oopsy story involved a Make Table query, followed by a DELETE * query, followed by the SQL Server grinding to a halt because the log file generated by those two actions had grown to fill the entire allocated hard disk space for that SQL Server instance (it was on a VM with a limited capacity). The developer who did that (not me, thank goodness), learned about the TRUNCATE option the next day.

 

[Minty]

Truncate is very handy, especially if you make a lot of "static" reporting tables that get recreated frequently and can have 10,000s of rows.
I picked up on it about 3 years ago when a client was complaining of sluggish performance.
When I investigated the delete was taking longer to run than the processes and queries driving the results table creation.

 

[Isaac]

Hi,

Since SQL Server commands are not that much familier I am with, I like to know about below to menu when I right click on my sql server tbl.

Select Top 1000 Rows
Edit Top 200 rows

In second menu (Edit Top 200 rows) I can make changes in records as requires but records are more that 200 so when I am using Select Top 1000 rows menu, it showing all records but also showing the SQL Query and all records are Non-updatable.

I need to update all records that displays. How can I ?

Also I dont want to see the query code over the actual records..I mean I want to close the query window and just want all records to display

Thanks,

Agreeing with others, you are not supposed to edit records using management studio.
Take this opportunity to learn how to write T-SQL as appropriate for your needs.

One idea is first test which records to update based on a Select only:

select
*
from
tablename
where
col1='something'

If you get the right # of records and sure you want to update, then update:

Update tablename
set col1='somethingelse'
where
col1='something'

 

[Isaac]

My favorite oopsy story involved a Make Table query, followed by a DELETE * query, followed by the SQL Server grinding to a halt because the log file generated by those two actions had grown to fill the entire allocated hard disk space for that SQL Server instance (it was on a VM with a limited capacity). The developer who did that (not me, thank goodness), learned about the TRUNCATE option the next day.

there's a free SSMS add-in, I forgot the name, that allows you to check an option that prevents DML statements with no where clause
very handy!
I used to test it and close my eyes hold my breath, whenever i wanted a thrill but wasn't allowed to drink any more!

 

[The_Doc_Man]

Just as a side comment, regarding trying to remove the implied limit of how many records you try to show after a SELECT from a really large SQL Server table...

From Wayne Ryan:

This is a settable option, near the bottom.
Change the 200/1000 to 0 and you get all.

If the DB was large enough to require jumping to SQL Server, then when you try to retrieve records, just remember that the 2 GB limit that applies to a given DB file ALSO applies to the virtual memory area involved in queries. If you push this hard enough, do NOT be surprised to get an "Out of Memory" error or some similar error describing resource depletion.

Because the Access memory layout was not significantly altered during the move to the 64-bit memory model, your 32-bit memory limits still apply to most things in Access. They split the 32-bit memory (= 4 GB) between FE and BE. Which means the FE file gets 2 GB and each linked BE file gets 2 GB, and if you have multiple BE files they remap one at a time dynamically. But the temporary results of a SELECT query go to the FE virtual area, so that is the one you blow out.

 

[SQL_Hell]

Agreeing with others, you are not supposed to edit records using management studio.
Take this opportunity to learn how to write T-SQL as appropriate for your needs.

One idea is first test which records to update based on a Select only:

select
*
from
tablename
where
col1='something'

If you get the right # of records and sure you want to update, then update:

Update tablename
set col1='somethingelse'
where
col1='something'

This is good advice, I do select statements before update / delete... religiously. I also put any updates or deletes into a user defined transaction, then if I do make a mistake and the row count doesn't match what I am expecting then I can roll back the transaction, without having to do some form of restore operation.