Hot answers tagged ssms
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:
...
19
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 ...
15
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
13
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 ...
12
Use CREATE TABLE, not the GUI.
This is a bug in SSMS, already reported here as well as here on Connect.
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
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 ...
11
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 ...
11
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 ...
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
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 ...
10
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
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
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
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.
...
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
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 ...
8
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. ...
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
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
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
7
If you use "Detach/Attach" function you'll be able to transfert the database with all triggers and objects that are inside the DB. You must know that only the database will be tranfert. If you need to have all the logins for the old server, you can use this link from Microsoft. After you run the procedure, you can have some orphaned users. If you read ...
7
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 ...
7
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 ...
7
From your reply to my question, then it does appear to be too much backup history. For a one off cleardown, something along these lines will do it:
USE msdb
GO
DECLARE @days DATETIME
SET @days = DATEADD(day,-30,current_timestamp)
EXEC sp_delete_backuphistory @days
GO
That example gets rid of everything over 30 days old, so you may need to change the -30 ...
6
Yes, it is possible to use the SSMS from R2 to connect to a database server from any of the previous versions (I'm not sure about version pre SQL 2000, but it's not your case anyways).
You won't have all possible features activated (like Utility Explorer), but that shouldn't stop you to use it properly.
5
I use Visual Studio extensively (well, BIDS) for SSRS report design, and SSIS packages. I couldn't do either very well, if at all, in Management Studio. Visual Studio is a much more complete and integrated development environment, and it hooks into Source Control systems much better too. And that's all reflected in the price!
5
Just to augment the other answers: a table is, by definition, an unordered set of rows. If you don't specify an ORDER BY clause, SQL Server is free to return the rows in whatever order it deems most efficient. This will often just happen to coincide with the order of insert, since most tables have a clustered index on identity, datetime or other ...
5
At our office we have two firewalls one firewall to the public internet and one to the internal network. If you are on the internal network, you can connect to the SQL server directly. If you on the external Internet you cannot, because the port is not open on the firewall. If you want to connect through the Internet, you have to login through VPN.
5
VS may appear great if you don't know SSMS or have used 3rd party tools. The gaps in VS stand out if you use Red Gate tools for schema comparison etc. And the free SSMS plug-ins too.
The source control bits is misleading: what is in the production database is your reference copy. Not what the developer is using. See
...
Only top voted, non community-wiki answers of a minimum length are eligible
