If you are like I used to be for YEARS, anytime you want to copy a table, index, etc... you probably right click on that object in SQL Server Management Studio and click "Script
as" > "CREATE To":
An inefficient way of generating change scripts
This is a pretty easy way to quickly script database objects, however it's incomplete.
For starters, I'm forced to generate the scripts for tables one at a time. Not fun.
Additionally, if I want to script associated objects for that table, like indexes, I have to go to each index and then right-click and select "Script Index as". Ugh.
There Is A Better Way
Instead of using "Script Table as", you can right click on your database and choose "Tasks" > "Generate Scripts...":
This option brings up a GUI that will allow you to script multiple objects at the same time:
Look ma, multiple objects at once!
Additionally if you click the "Advanced" button in the final page of the dialog, you will receive many more options for how your objects will get scripted, including the ability to script out the indexes!
Ooooooo, ahhhh - everything scripted in one fell swoop!
Using the Generate Scripts Task it's easy to generate table, index, stored procedure, etc... change scripts all in one step - nice!
NOTE: If your SQL User receives an error when trying to generate the scripts, make sure they have the following access:
1. Add results of one query into the IN() statement
Sometimes when writing an ad hoc query you might want to take the results of one query and put them into an IN() statement of another query.
Sure, you can write a subquery to put into your IN() statement...but that's too much work for a one-time use disposable query.
What you can do instead is:
Copy your values of interest
Paste them into your IN() statement
Hold down the ALT key while dragging the mouse down in front of all of your pasted values
Type a comma (see video above for an easier demonstration).
This allows you to quickly turn pasted values into a list suitable for an IN() statement.
2. Paste values into a template of commands
Sometimes you might have to do something repetitive, like insert a bunch of values into a table. You could script out multiple value insertion with a UNION ALL and and INSERT INTO statement, but once again that's a lot of work for a one-time query.
Instead you can copy the values you want to insert by highlighting them while holding down the ALT key then positioning the cursor on the first location where you want to paste the values. Then once you paste, SSMS will automatically multiline paste the values.
Once again, watch the video above to see a demo - a picture (video!) is worth a thousand words.
3. Quickly modify multiple rows
Finally, you might have ran into a situation where you already have a set of queries written, but they all need to be slightly modified in order to work. For example, maybe the table name changed and now you need to append a prefix to each table name.
While holding down the ALT key, you can simply drag in front of the text you want to prepend to and then start typing.
To use it, highlight a command or function that you want to know more information about and then press F1. Simple as that.
Pressing F1 brings up the Microsoft online documentation for that keyword/function, making it the fastest way of getting to Microsoft's online documentation. You'll solve your own questions faster than a coworker can tell you "to google it."
Most recently I've been using the F1 shortcut in the following scenarios:
Can't remember the date/time style formats when using CONVERT? Highlight CONVERT and press F1: BOOM! All date and time style codes appear before you.
Need to use some option for CREATE INDEX and don't remember the syntax? Just highlight CREATE INDEX and press F1! Everything you need is there.
Do you remember if BETWEEN is inclusive or exclusive? F1 knows. Just press it.
You get the idea.
Assuming you use the online Microsoft docs 10 times per day, 250 days a year, and each time it takes you 10 seconds to open a browser and search for the doc...
( 10/day * 250/year * 10 sec ) / 60 sec / 60 min = 6.94 hours saved. Your welcome.
Here's the scenario: you copy and paste some code into a query you are building. A few minutes later you need that same snippet again, but you've already copied and pasted something else onto the clipboard.
The next five minutes of your life are spent searching across the twenty query editor tabs you have open looking for that original piece of code.
However, SQL Server Management Studio goes above and beyond the regular copy and paste feature set — it has a clipboard ring.
What's a clipboard ring you ask?
The clipboard ring let's you cycle through the last 20 things you copied onto your clipboard when you go to paste in SSMS. It can be accessed in the Edit menu (like in the screenshot above) or by using the keyboard shortcut CTRL + SHIFT + V.
You can make the query easier to read by putting each column name onto its own line.
Simply open the Find and Replace window (CTRL + H) and type in ,(:Wh)* for the Find value and ,nt for the Replace value (in some versions of SSMS you may have better luck using ,(:Wh|t| )* in the Find field). Make sure "Use Regular Expressions" is checked and press Replace All: