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

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 ...


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: ...


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

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 ...


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 ...


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

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 ...


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.


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

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 ... ...


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

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

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

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

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

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

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 ...


8

If you are licensed to use the full Management Studio, I would install that. It has more features that will come in handy if you ever need to manage other SQL Server instances other than your local development/Express version. If you're not licensed or you know this is not a requirement, you can download Management Studio Express from here: ...


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 ...


8

I recommend cautious use of this tuning technique since I have found missing index suggestions popped up by query plans to be consistently less reliable as queries and DB schemas become progessively more complex. This has been due to a variety of reasons in my experience: 1) The "percent improvement" can be way off for all but the simplest queries/most ...


8

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 ...


8

Have you looked at SQL Sentry Plan Explorer? There is a free and a PRO version, and one of the benefits common to both is that they can handle humongous plans that Management Studio chokes on. You can also generate both actual and estimated plans from within the tool, so you don't even have to bother with SSMS. disclaimer: I work for SQL Sentry



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