Tag Info

Hot answers tagged

24

Actually I was a bit underwhelmed with VS2010, to be honest. I think an old-school create table script and files for stored procedures are easier to work with. If you need schema management then you can get Redgate SQL Compare Pro for a few hundred dollars. If you really need a database modelling tool then Powerdesigner or even Erwin does a much better ...


22

The database you are querying probably has "auto close" enabled. The connection from SSMS stops the database closing. When you close SSMS, the database closes. A subsequent call requires it to be opened. Auto close is the default for SQL Server Express and is not a good idea: ...


22

Many reasons. One of the biggest that I can think of is that the missing index DMVs don't take into account existing indexes. Example: You have a table with ColA, ColB, ColC. Currently you have an index on ColA. The missing index DMV will suggest you add an index on (ColA, ColB). This may be correct, but the smart thing to do is to add ColB as a ...


18

You need to use this key combination: CTRL + SHIFT + R Alternatively, use the menu Edit > IntelliSense > Refresh Local Cache. This article might also be useful (for future readers who have more perplexing IntelliSense issues): Troubleshooting IntelliSense in SQL Server Management Studio


17

That's because you are clicking on the wrong thing. Don't right-click Databases, but you need to actually right-click the database that you want to copy, and under Tasks there is an option for Copy Database.... In other words, right-click on Database1 and then you'll see this option under Tasks. As per @AaronBertrand's comment, though, it is highly ...


15

In the RTM release of SQL Server 2012 (and in previous versions), yes, they are quite different. The Express version was missing a bunch of functionality, most notably the ability to manage SQL Server Agent (even on non-Express instances). There are other things missing like Profiler but technically that is not part of Management Studio itself, just part of ...


15

SSMS2012 is backwards compatible: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/0151c2e8-2281-4a78-86b0-20cc1bfd57ac Edit Giving out Aaron's link instead of the old one! Thanks Aaron. microsoft.com/en-us/download/details.aspx?id=35579 As Kenneth mentioned, you could copy the 2008 BOL (Books online) so when you search the help it'll be ...


14

The query cost is reported in execution plans as "estimated subtree cost". This is an absolute figure such as 1.5. Conor Cunningham mentioned in a SQLBits presentation that it originally referred to the number of seconds taken to execute on a particular Microsoft employee's machine ("Nick's Machine") in the SQL Server 7 days but now should be interpreted as ...


14

I've been toying with how to structure an answer to this question since it was originally posted. This is difficult as the case for VS2010 isn't about describing the features and benefits of the tool. This is about convincing the reader to make a fundamental shift in their approach to database development. Not easy. There are answers to this question from ...


14

The keyboard combination you're after (Ctrl+K, Ctrl+D) is for "formatting" - but not to the extent you seem to expect. It is not a prettifier, it is just used to insert correct spacing and tabs as found in Tools > Options > Text Editor > Transact-SQL > General/Tabs - so if you highlight a portion of text and hit the keyboard combination, it is ...


13

Use CREATE TABLE, not the GUI. This is a bug in SSMS, already reported here as well as here on Connect.


12

There isn't a way to prevent this. Microsoft feels that it is better to prevent you from shooting yourself in the foot, probably since the number of people out there who create databases, objects or columns with bad names (e.g. my table or date) or reserved keywords (e.g. log or timestamp) outnumber those who never do so. There was a Connect item asking for ...


11

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause. From Craig Freedman: Combining TOP with ORDER BY adds determinism to the set of rows returned. ...


11

In SSMS you need to change some options: Tools - Options - Query results - sql server - results to grid (or text) -> Include column headers when copying or saving the results.


11

No way to do this as part of a script from SSMS, but you do have two options. One thing you can do is use SQLCMD mode and the ::connect command in order to have a script that will connect to multiple servers and run the script. This works well if you save the script for the user and use the :r command to load the script from a file. Another thing you can ...


10

The way that I know of to do this is to establish a VPN connection to the other site, thus eclipsing the need to open a port and maintaining the security of the setup, while eliminating the need to RDP into the other server. Granted, you run the risk of dropping a connection while you're working, thus possibly ending queries prematurely, but otherwise ... ...


10

I'm seriously doubting that it should be anywhere near this. Run sp_spaceused to analyze the actual space in use in the MDF. Run DBCC LOGINFO to analyze the space used in the LDF. Measurement beats guess every single time. I suspect it's because the db has been backed up by my host with the "Append to the existing backup set" option checked. ...


10

Don't just use the whole table. Instead choose the option to write a query. Then simply write a select statement that doesn't use that Identity Column. Alternatively, when you map columns, if you click "Delete rows in destination table", it will truncate the target table before loading data. If the opportunity exists for data to be removed from the ...


10

Client processing time is the amount of time spent in milliseconds between the first received response packet and the last received response packet by the client. When you run the query in SSMS it render the data row by row to the GUI. Your application doesn't do that. Setting the SSMS option to Discard results after execution will eliminate the time ...


9

At first I thought it came from Sybase (which is where SQL Server originated of course), which has a lookup function, but this is PowerBuilder-related. And then I checked SQL Server 2000 and it doesn't light up in pink in Query Analyzer... ...if it was legacy from Sybase I would have expected it to be in the list of color-coded words all along. It's ...


9

Opening the SQL server to direct outside connections is an idea to avoid if you can - you are significantly increasing your visible surface area for attack and any communication between you and the server (aside from login credentials in the authentication phase, perhaps) will not be encrypted. If you must open the port, make sure it is open only to your ...


9

Yes, I have done it. My website http://www.rocketclubs.com is on SQL server 2000 and I updated it last night in full windows 8, SSMS 2012 setup.


9

Hit ESC to close the window when done typing the word before hitting space, but if you hit ESC after 'fo' and then type 'o' it will kick in again. Another option if you don't use it often would be to disable it (Tools->Options->Text Editor->Transact-SQL->General->Auto list members) and use CTRL+J to bring it up manually when you want to use ...


9

I see that you clearly stated your looking for a solution in SSMS but I thought I would provide a PowerShell solution in case it helps. (SQLPS is accessible from inside of SSMS 2008 & SSMS 2008R2) You can use SQLPS (or regular PowerShell with the SQL cmdlet snapin) to run something like this: Invoke-Sqlcmd -Query "sp_databases" -ServerInstance ...


9

Shrinking your transaction log should not be part of your routine. A transaction log backup clears the log file automatically; shrinking it afterward just causes it to have to grow again afterward. Read this for more information. Yes, you should be able to perform point-in-time restores using your latest full backup and any subsequent transaction log ...


9

Add an index if you'll use your temp table and its index twice or more during the query run. Or to maintain usual index tasks, like uniqueness If your data loaded into temp table are already sorted, the to create temp table with the same clustered index as sort of data BUT taking into account sql server's feature of temp tables reuse- if you decide to ...


9

This doesn't look like a permissions issue at all, but rather a bug in the UI. If you are using Management Studio Express, then you should consider two options: Ensuring you are on the most recent service pack (yes they apply to client tools as well), and perhaps the most recent cumulative update as well. Many of the bugs in the 2008/2008 R2 version of ...


9

Well, if you have a legal copy of SQL Server, you should be able to install Management Studio from that media wherever you need to manage SQL Server from. That said, the Express version of Management Studio, since 2012 Service Pack 1, has been a fully functional product with absolutely no differences (except you can download it for free instead of finding ...


8

In SSMS Tools Options "SQL Server Object Explorer" "Script full text catalogs" Default is "false"...


8

Those commands do two things: Clear the page cache, which stores data pages that have already been retrieved from disk (normally the biggest factor in time in a query is disk access) Clear the query plan cache, which means the server needs to create a new query plan. This is normally not significant except for very high transaction volumes. You are ...



Only top voted, non community-wiki answers of a minimum length are eligible